dbt (data build tool): industrialized SQL transformation

dbt (data build tool) has become the lingua franca of modern data transformation. In the space of five years, it went from an open-source experiment to the standard tool used by thousands of data teams worldwide. Understanding dbt, what it does, why it works, and how to use it strategically, is essential for any CDO overseeing a modern data platform.

What dbt actually does

dbt is a transformation tool. It sits in the T of ELTELTELT (Extract, Load, Transform) is a data integration pattern where raw data is loaded into a target system first, then transformed inside it using the platform's compute power.View full definition →: after data is loaded into your warehouse, dbt transforms it using SQL.

Before dbt, transformations were typically written as stored procedures, custom Python scripts, or embedded in ETLETLETL (Extract, Transform, Load) is a data integration process that pulls data from sources, reshapes it into a consistent format, and writes it into a target system.View full definition → tools. These approaches shared problems: no version control, no testing, no documentation, no lineage.

dbt solves all of these in one tool:

• SQL-native: Transformations are SQLSQLSales Qualified Lead: a prospect the sales team has validated as ready for direct outreach and a proposal, having passed clear qualification criteria.View full definition → SELECT statements. No new language to learn.
• Version control: dbt models are files in Git. Every change is tracked, reviewable, and reversible.
• Testing: Define expected data qualitydata qualityThe degree to which data is fit for purpose: accurate, complete, consistent, timely, valid and unique. Poor quality data undermines analytics, reporting and AI.View full definition → properties in YAML. dbt tests them automatically.
• Documentation: Auto-generated from column descriptions in YAML config.
• Lineage: dbt builds a DAG of all model dependencies, giving you automatic data lineagedata lineageData lineage maps how data moves and transforms across systems, from origin to consumption, showing where it came from, what changed it, and where it goes.View full definition →.

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 BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.View full definition → 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

1. 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

1. 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

Réponse: C