Projet dbt : modèles, sources et ref

Exercices

Les solutions et les tests restent sur le serveur. Dans l'espace étudiant, ton code est exécuté et corrigé, et le tuteur donne des indices avant la solution.

1. CodeDifficulté 2SQL (SQLite) dans le navigateur2 indices

   Modèle stg_shop__orders compilé

   Tables sources (déclarées dans dbt comme source('shop', 'orders') et source('shop', 'customers')) : raw_orders(id, user_id, status, amount_cents, created_at) et raw_customers(id, first_name, last_name, email). Dans l’exercice, écrivez directement les noms de tables : c’est le SQL compilé par dbt.

   Écrivez la requête du modèle de staging des commandes, avec les colonnes :

   • order_id (depuis id) et customer_id (depuis user_id) ;
   • status en minuscules ;
   • amount : le montant en euros (amount_cents / 100.0) ;
   • order_date : la date seule, extraite de created_at avec date().
   Données de l'exercice (SQL)

   -- Tables brutes chargées par l'outil d'ingestion (schéma raw_shop)
   CREATE TABLE raw_orders (id INTEGER, user_id INTEGER, status TEXT, amount_cents INTEGER, created_at TEXT);
   INSERT INTO raw_orders VALUES
    (1, 1, 'COMPLETED', 4990, '2026-03-01 09:15:00'),
    (2, 2, 'completed', 12000, '2026-03-01 14:02:00'),
    (3, 1, 'Returned', 1990, '2026-03-02 10:30:00'),
    (4, 3, 'placed', 3450, '2026-03-03 18:45:00');
   CREATE TABLE raw_customers (id INTEGER, first_name TEXT, last_name TEXT, email TEXT);
   INSERT INTO raw_customers VALUES
    (1, 'Amina', 'Diallo', ' AMINA@EX.FR'), (2, 'Bastien', 'Morel', 'bastien@ex.fr'),
    (3, 'Camille', 'Roux', 'camille@ex.fr'), (4, 'Dina', 'Petit', 'dina@ex.fr');

   with source as (
       select * from raw_orders   -- {{ source('shop', 'orders') }} une fois compilé
   )
   select ...
   from source

2. CodeDifficulté 2SQL (SQLite) dans le navigateur2 indices

   Modèle marts customers

   Modèles disponibles (compilés en tables) : stg_customers(customer_id, first_name, email) et stg_orders(order_id, customer_id, status, amount, order_date).

   Écrivez le modèle customers : pour chaque client (y compris ceux sans commande), affichez customer_id, first_name, nb_orders (commandes dont le statut n’est pas returned), total_amount (0 si aucune commande) et first_order_date.

   Données de l'exercice (SQL)

   CREATE TABLE stg_customers (customer_id INTEGER, first_name TEXT, email TEXT);
   INSERT INTO stg_customers VALUES (1, 'Amina', 'amina@ex.fr'), (2, 'Bastien', 'bastien@ex.fr'), (3, 'Camille', 'camille@ex.fr'), (4, 'Dina', 'dina@ex.fr');
   CREATE TABLE stg_orders (order_id INTEGER, customer_id INTEGER, status TEXT, amount REAL, order_date TEXT);
   INSERT INTO stg_orders VALUES
    (1, 1, 'completed', 49.9, '2026-03-01'), (2, 2, 'completed', 120.0, '2026-03-01'),
    (3, 1, 'returned', 19.9, '2026-03-02'), (4, 3, 'placed', 34.5, '2026-03-03');

   select c.customer_id, c.first_name,
          count(o.order_id) as nb_orders,
          ... as total_amount,
          ... as first_order_date
   from stg_customers as c   -- {{ ref('stg_shop__customers') }}
   left join stg_orders as o on ...
   group by c.customer_id, c.first_name

3. QCMDifficulté 11 indice

   Le rôle de ref()

   Que se passe-t-il si un modèle lit analytics.stg_shop__orders écrit en dur au lieu de {{ ref('stg_shop__orders') }} ?

   • Rien, c'est équivalent
   • dbt ne connaît pas la dépendance : l'ordre d'exécution peut être faux et le développement lit la table de production
   • dbt refuse de compiler le projet

4. TâcheDifficulté 22 indices

   Déclarer les sources et le premier modèle

   Dans un projet dbt nouvellement créé (dbt init), les tables raw_shop.orders et raw_shop.customers existent dans l’entrepôt. Créez :

   1. le fichier models/staging/shop/_shop__sources.yml qui déclare la source shop (schéma raw_shop) et ses deux tables ;
   2. le modèle models/staging/shop/stg_shop__customers.sql qui renomme id en customer_id et normalise l’email (minuscules, sans espaces) ;
   3. exécutez dbt run --select stg_shop__customers, puis dbt compile et ouvrez le SQL compilé.
   • Le fichier YAML commence par version: 2 et déclare sources > shop > tables orders et customers
   • Le modèle lit la table avec {{ source('shop', 'customers') }} et non avec un nom en dur
   • Le modèle ne contient qu'une requête SELECT, sans CREATE
   • dbt run se termine avec 1 modèle réussi (PASS=1)
   • Le fichier compilé dans target/compiled/ contient raw_shop.customers à la place de l'appel source()