Exercice dbt d'entretien : passer d'une CTE à une architecture staging/mart

Écrire une requête avec des CTE est à la portée d'un junior. Structurer un projet dbt en couches réutilisables est une compétence senior. Cet exercice donne un modèle monolithique et demande de le découper proprement — un excellent révélateur de maturité.

Data Builder·Juillet 2025·10 min de lecture·Test dbt · architecture · entretien

Sommaire

Le modèle monolithique de départ
Le corrigé (staging / mart)
Grille par niveau

Le principe attendu : isoler le nettoyage de chaque source dans des modèles de staging, puis composer la logique métier dans un modèle de mart. Cela rend le projet lisible, testable et réutilisable.

1Le modèle monolithique de départ

La consigne

« Ce modèle empile 4 CTE. Découpe-le en une architecture propre. »

Un seul fichier fait tout : nettoyer les commandes, nettoyer les clients, joindre, agréger. Ça marche, mais rien n'est réutilisable ni testable isolément.

-- models/ca_par_pays.sql (monolithe)
WITH commandes AS (SELECT ... FROM raw.commandes WHERE ...),
     clients   AS (SELECT ... FROM raw.clients   WHERE ...),
     jointure  AS (SELECT ... FROM commandes JOIN clients ...),
     final     AS (SELECT pays, SUM(montant) FROM jointure GROUP BY pays)
SELECT * FROM final

2Le corrigé (staging / mart)

La consigne

Chaque source nettoyée dans son staging, la logique métier dans le mart.

Le candidat crée un modèle de staging par source (nettoyage isolé, testable) puis un modèle de mart qui les compose avec ref(). C'est la structure standard d'un projet dbt mûr.

-- models/staging/stg_commandes.sql
SELECT id, client_id, montant FROM {{ source('raw','commandes') }} WHERE statut = 'payee'

-- models/staging/stg_clients.sql
SELECT id, pays FROM {{ source('raw','clients') }}

-- models/marts/ca_par_pays.sql
SELECT c.pays, SUM(o.montant) AS ca
FROM {{ ref('stg_commandes') }} o
JOIN {{ ref('stg_clients') }}  c ON c.id = o.client_id
GROUP BY c.pays

Un modèle stg_ par source (commandes, clients).
Un modèle mart qui compose via ref().
Chaque couche est testable et réutilisable.

Signal d'alerte : le marqueur senior : le candidat isole le nettoyage de chaque source dans un staging réutilisable, au lieu de tout empiler. Un junior « fait marcher » ; un senior « rend maintenable et réutilisable ».

3Grille par niveau

Niveau	Maîtrise attendue	Signal GO	NO-GO
Junior	Écrit des CTE qui fonctionnent	Obtient le bon résultat	Empile tout dans un seul modèle
Confirmé	Découpe en staging et mart	Isole le nettoyage par source	Duplique la logique de nettoyage
Senior	Rend chaque couche réutilisable	Compose proprement avec ref()	Ne pense pas à la réutilisation
Lead	Définit des conventions de couches	Structure un projet dbt mûr	Confond 'ça marche' et 'c'est maintenable'

Home›Blog›dbt interview exercise

Ready-to-use tests

dbt interview exercise: from a CTE to a staging/mart architecture

Writing a query with CTEs is within a junior's reach. Structuring a dbt project into reusable layers is a senior skill. This exercise gives a monolithic model and asks to split it cleanly — an excellent maturity revealer.

Data Builder·July 2025·10 min read·dbt test · architecture · interview

Contents

The starting monolithic model
The answer (staging / mart)
Level grid

The expected principle: isolate each source's cleaning in staging models, then compose the business logic in a mart model. This makes the project readable, testable and reusable.

1The starting monolithic model

The task

‘This model stacks 4 CTEs. Split it into a clean architecture.’

One file does everything: clean orders, clean customers, join, aggregate. It works, but nothing is reusable or testable in isolation.

-- models/ca_par_pays.sql (monolithe)
WITH commandes AS (SELECT ... FROM raw.commandes WHERE ...),
     clients   AS (SELECT ... FROM raw.clients   WHERE ...),
     jointure  AS (SELECT ... FROM commandes JOIN clients ...),
     final     AS (SELECT pays, SUM(montant) FROM jointure GROUP BY pays)
SELECT * FROM final

2The answer (staging / mart)

The task

Each source cleaned in its staging, the business logic in the mart.

The candidate creates one staging model per source (isolated, testable cleaning) then a mart model composing them with ref(). It's the standard structure of a mature dbt project.

-- models/staging/stg_commandes.sql
SELECT id, client_id, montant FROM {{ source('raw','commandes') }} WHERE statut = 'payee'

-- models/staging/stg_clients.sql
SELECT id, pays FROM {{ source('raw','clients') }}

-- models/marts/ca_par_pays.sql
SELECT c.pays, SUM(o.montant) AS ca
FROM {{ ref('stg_commandes') }} o
JOIN {{ ref('stg_clients') }}  c ON c.id = o.client_id
GROUP BY c.pays

One stg_ model per source (orders, customers).
A mart model composing via ref().
Each layer is testable and reusable.

Warning signal : the senior marker: the candidate isolates each source's cleaning in a reusable staging, instead of stacking everything. A junior ‘makes it work’; a senior ‘makes it maintainable and reusable’.

3Level grid

Level	Expected proficiency	Signal GO	NO-GO
Junior	Writes working CTEs	Gets the right result	Stacks everything in one model
Mid-level	Splits into staging and mart	Isolates cleaning per source	Duplicates the cleaning logic
Senior	Makes each layer reusable	Composes cleanly with ref()	Doesn't think reuse
Lead	Defines layer conventions	Structures a mature dbt project	Confuses ‘it works’ and ‘it's maintainable’