dbt (data build tool) : la transformation SQL industrialisée, MBA Training, MBA Training

Dbt in practice: the core concepts

Models: A dbt model is a SQL file that defines a transformation. The file name becomes the table or view name in your warehouse. You write SELECT statements; dbt handles CREATE TABLE AS.

Refs: Models reference each other using the ref() function (e.g., FROM {{ ref('stg_customers') }}). This creates the dependency graph and allows dbt to run models in the right order.

Tests: Two types: generic tests (not_null, unique, accepted_values, relationships) and singular tests (custom SQL that should return zero rows if data is correct). Tests run after each build.

Sources: External tables (not built by dbt) referenced using source() function. dbt can test source freshness automatically.

Materializations: How dbt creates the output. Table (full rebuild), View (no data stored, query runs on access), Incremental (only processes new/changed records, critical for large datasets).

The incremental model pattern

For large datasets, full refreshes become impractical. An incremental model processes only new records:

The pattern: filter the source to only records newer than the most recent record in the target table. On first run, process everything. On subsequent runs, process only the delta. This reduces computation cost from O(total rows) to O(new rows per run).

Incremental models require careful design: how do you handle late-arriving data? Updated records? Deletes? These edge cases are where dbt incremental model design gets complex, and where getting it wrong causes data quality issues.

Organizing dbt at Scale

A mature dbt project follows a layer pattern:

Staging (stg_): One model per source table. Minimal transformation, rename columns, cast types, remove test data. No business logic.
Intermediate (int_): Business logic applied. Joins, aggregations, derived fields. Not directly consumed by BI tools.
Marts (dim_, fct_, agg_): Dimensional models and fact tables. These are the Gold layer, consumed by BI tools and data consumers.

This organization separates concerns: staging handles source-specific nuances, marts expose business logic, intermediate connects them.

At Gitlab (which open-sourced their entire dbt project), this structure lets any contributor understand where to look for any transformation, and prevents business logic from leaking into staging models.

Quiz Questions

1. Quelle est la principale innovation de dbt par rapport aux transformations SQL traditionnelles (stored procedures) ?

A) dbt est plus rapide

B) dbt ajoute version control, tests automatiques, documentation et lignage de données, les pratiques d'ingénierie logicielle appliquées aux transformations SQL

C) dbt supporte plus de bases de données

D) dbt génère automatiquement le SQL

Réponse: B

2. Qu'est-ce qu'une materialisation "incremental" dans dbt ?

A) Un modèle qui ne traite qu'un échantillon des données

B) Un modèle qui reconstruit la table entière à chaque run

C) Un modèle qui ne traite que les enregistrements nouveaux ou modifiés depuis le dernier run

D) Un modèle qui s'exécute automatiquement toutes les heures

Réponse: C

3. Dans l'organisation d'un projet dbt mature, que contient la couche "Staging" ?

A) Les tables dimensionnelles et de faits consommées par les outils BI

B) La logique métier complexe et les jointures

C) Un modèle par table source avec transformation minimale, renommage, casting, sans logique métier

D) Les agrégations pour les dashboards exécutifs