Accueil›Blog›Test technique dbt testing avancé : singular tests, macros de test, coverage

Guide recrutement data

Test technique dbt testing avancé : singular tests, macros de test, coverage

Les tests dbt vont bien au-delà de not_null et unique. En entretien Senior, on évalue la capacité à écrire des tests sophistiqués qui garantissent réellement la qualité des données.

Data Builder·Juin 2025·6 min de lecture·Analytics Engineer

Sommaire

Tests génériques avancés
Singular tests
Macros de test custom
Stratégie de coverage
Tests en CI
Debugger les tests
Grille

1Tests génériques avancés avec dbt-expectations

Question discriminante

Comment allez-vous au-delà des 4 tests dbt de base ?

# schema.yml - tests avancés avec dbt-expectations
models:
  - name: fct_orders
    columns:
      - name: amount
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 1000000
              config:
                severity: error
          - dbt_expectations.expect_column_proportion_of_unique_values_to_be_between:
              min_value: 0.99
      - name: order_date
        tests:
          - dbt_expectations.expect_column_values_to_be_of_type:
              column_type: date
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: '2020-01-01'
              max_value: '{{ modules.datetime.date.today() }}'
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
      - dbt_expectations.expect_table_columns_to_match_set:
          column_list: ['order_id', 'customer_id', 'amount', 'status']

2Singular tests : logique SQL custom

Question discriminante

Quand écrivez-vous un singular test plutôt qu un test générique ?

-- tests/fct_orders_no_future_dates.sql
-- Un singular test retourne les LIGNES QUI ÉCHOUENT
-- Si la requête retourne 0 lignes -> test passé
SELECT
    order_id,
    order_date,
    'order_date cannot be in the future' AS test_name
FROM {{ ref('fct_orders') }}
WHERE order_date > CURRENT_DATE

UNION ALL

-- Test de cohérence métier : total ne peut pas être négatif
SELECT
    order_id,
    amount,
    'amount cannot be negative for completed orders' AS test_name
FROM {{ ref('fct_orders') }}
WHERE status = 'completed'
  AND amount < 0

Singular vs générique — singular pour la logique métier complexe qui ne s exprime pas en YAML
Multiple checks — UNION ALL pour regrouper plusieurs vérifications dans un seul test

3Macros de test custom

Question discriminante

Comment créez-vous une macro dbt réutilisable pour tester la fraîcheur d un modèle ?

{# macros/test_freshness_sla.sql #}
{% macro test_freshness_sla(model, column_name, hours=24) %}

SELECT
    MAX({{ column_name }}) AS last_update,
    CURRENT_TIMESTAMP AS checked_at,
    DATEDIFF('hour', MAX({{ column_name }}), CURRENT_TIMESTAMP) AS hours_stale
FROM {{ model }}
HAVING DATEDIFF('hour', MAX({{ column_name }}), CURRENT_TIMESTAMP) > {{ hours }}

{% endmacro %}

{# Utilisation dans schema.yml #}
models:
  - name: fct_orders
    tests:
      - freshness_sla:
          column_name: updated_at
          hours: 6
          config:
            severity: error

4Stratégie de coverage des tests

Question discriminante

Comment évaluez-vous si votre projet dbt est suffisamment testé ?

Coverage minimal — 100% des PKs testés avec unique + not_null. 100% des FKs avec relationships
Colonnes critiques — toutes les colonnes utilisées dans les KPIs métier ont au moins 1 test
Script de coverage — requêter INFORMATION_SCHEMA pour identifier les colonnes sans test
dbt-coverage — package qui génère un rapport de coverage documentation + tests par modèle
Pre-commit hook — bloquer le commit si un nouveau modèle n a pas au moins not_null sur sa PK

5Tests en CI : vitesse et fiabilité

Question discriminante

Comment rendez-vous vos tests dbt rapides en CI sans les désactiver ?

# Ne tester que les modèles modifiés (slim CI)
dbt test \
  --select state:modified+ \
  --defer \
  --state ./prod-artifacts

# Séparer les tests par sévérité
# En CI sur PR : uniquement les tests error
dbt test --select state:modified+ --warn-error

# En CI sur main : tous les tests
dbt test

# Tags pour catégoriser les tests
tests:
  - name: fct_orders
    tests:
      - not_null:
          config:
            tags: ['critical']
            severity: error
      - dbt_expectations.expect_table_row_count_to_be_between:
          config:
            tags: ['non_critical']
            severity: warn

6Debugger les tests qui échouent

Question discriminante

Comment investiguer un test dbt qui échoue en production ?

dbt test --store-failures — stocker les lignes en échec dans une table de debug dans le warehouse
--select model_name — relancer uniquement le test qui pose problème
dbt compile --select test_name — voir le SQL généré par le test sans l exécuter
Requêter la table failures — SELECT * FROM dbt_test__audit.not_null_fct_orders_order_id

# packages.yml - extensions de test
packages:
  - package: calogica/dbt_expectations
    version: [">=0.9.0", "<0.10.0"]
  - package: elementary-data/elementary
    version: [">=0.10.0"]

# models/marts/schema.yml - tests avancés
models:
  - name: fct_orders
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
          max_value: 10000000
    columns:
      - name: amount
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 40
              max_value: 200
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: ".+@.+\..+"

# Lancer uniquement les tests des modèles modifiés (CI efficace)
dbt test --select state:modified+ --defer --state ./prod-artifacts

dbt-expectations — 50+ tests avancés : distributions, regex, comparaisons entre tables, pourcentages de valeurs nulles. Le package le plus utile après le core dbt
Severity — error (bloque le pipeline) vs warn (alerte sans bloquer). Règle : warn pour les métriques métier qui peuvent fluctuer, error pour les contraintes techniques absolues
Elementary — package dbt qui génère un rapport de qualité et détecte automatiquement les anomalies statistiques (volume, distributions) sans écrire de tests manuellement
Store failures — store_failures: true dans dbt_project.yml stocke les lignes échouées dans une table pour diagnostic. Indispensable en production
Source freshness — configurer freshness dans sources.yml pour alerter si la source n'a pas été mise à jour depuis N heures. Premier signal d'un pipeline upstream en panne

dbt-expectations - 50+ tests avances : distributions, regex, comparaisons entre tables. Le package le plus utile apres le core dbt
Severity - error (bloque le pipeline) vs warn (alerte sans bloquer). Regle : warn pour les metriques metier qui peuvent fluctuer, error pour les contraintes techniques absolues
Elementary - package dbt qui genere un rapport de qualite et detecte automatiquement les anomalies statistiques sans tests manuels
Store failures - store_failures: true stocke les lignes echouees dans une table pour diagnostic. Indispensable en production pour deboguer rapidement
Source freshness - configurer freshness dans sources.yml pour alerter si la source n a pas ete mise a jour depuis N heures. Premier signal d un pipeline upstream en panne

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Confirmé	Tests YAML, dbt-expectations, singular tests	A écrit des singular tests, utilise dbt-expectations	N utilise que not_null et unique
Senior	Macros de test, coverage strategy, CI optimisé	A créé des macros de test réutilisables, stratégie de coverage documentée	Ne sait pas écrire une macro de test

1Advanced generic tests with dbt-expectations

Discriminating question

How do you go beyond the 4 basic dbt tests?

# schema.yml - advanced tests with dbt-expectations
models:
  - name: fct_orders
    columns:
      - name: amount
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 1000000
              config:
                severity: error
          - dbt_expectations.expect_column_proportion_of_unique_values_to_be_between:
              min_value: 0.99
      - name: order_date
        tests:
          - dbt_expectations.expect_column_values_to_be_of_type:
              column_type: date
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: '2020-01-01'
              max_value: '{{ modules.datetime.date.today() }}'
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
      - dbt_expectations.expect_table_columns_to_match_set:
          column_list: ['order_id', 'customer_id', 'amount', 'status']

2Singular tests: custom SQL logic

Discriminating question

When do you write a singular test rather than a generic test?

-- tests/fct_orders_no_future_dates.sql
-- A singular test returns the ROWS THAT FAIL
-- If the query returns 0 rows -> test passed
SELECT
    order_id,
    order_date,
    'order_date cannot be in the future' AS test_name
FROM {{ ref('fct_orders') }}
WHERE order_date > CURRENT_DATE

UNION ALL

-- Business consistency test: total cannot be negative
SELECT
    order_id,
    amount,
    'amount cannot be negative for completed orders' AS test_name
FROM {{ ref('fct_orders') }}
WHERE status = 'completed'
  AND amount < 0

Singular vs generic — singular for complex business logic that cannot be expressed in YAML
Multiple checks — UNION ALL to group multiple verifications into a single test

3Custom test macros

Discriminating question

How do you create a reusable dbt macro to test model freshness?

{# macros/test_freshness_sla.sql #}
{% macro test_freshness_sla(model, column_name, hours=24) %}

SELECT
    MAX({{ column_name }}) AS last_update,
    CURRENT_TIMESTAMP AS checked_at,
    DATEDIFF('hour', MAX({{ column_name }}), CURRENT_TIMESTAMP) AS hours_stale
FROM {{ model }}
HAVING DATEDIFF('hour', MAX({{ column_name }}), CURRENT_TIMESTAMP) > {{ hours }}

{% endmacro %}

{# Usage in schema.yml #}
models:
  - name: fct_orders
    tests:
      - freshness_sla:
          column_name: updated_at
          hours: 6
          config:
            severity: error

4Test coverage strategy

Discriminating question

How do you evaluate whether your dbt project is sufficiently tested?

Minimal coverage — 100% of PKs tested with unique + not_null. 100% of FKs with relationships
Critical columns — all columns used in business KPIs have at least 1 test
Coverage script — query INFORMATION_SCHEMA to identify columns without tests
dbt-coverage — package that generates a documentation + test coverage report per model
Pre-commit hook — block the commit if a new model does not have at least not_null on its PK

5Tests in CI: speed and reliability

Discriminating question

How do you make your dbt tests fast in CI without disabling them?

# Test only modified models (slim CI)
dbt test \
  --select state:modified+ \
  --defer \
  --state ./prod-artifacts

# Separate tests by severity
# In CI on PR: error tests only
dbt test --select state:modified+ --warn-error

# In CI on main: all tests
dbt test

# Tags to categorize tests
tests:
  - name: fct_orders
    tests:
      - not_null:
          config:
            tags: ['critical']
            severity: error
      - dbt_expectations.expect_table_row_count_to_be_between:
          config:
            tags: ['non_critical']
            severity: warn

6Debugging failing tests

Discriminating question

How do you investigate a dbt test that fails in production?

dbt test --store-failures — store failing rows in a debug table in the warehouse
--select model_name — rerun only the problematic test
dbt compile --select test_name — view the SQL generated by the test without executing it
Query the failures table — SELECT * FROM dbt_test__audit.not_null_fct_orders_order_id

# packages.yml - test extensions
packages:
  - package: calogica/dbt_expectations
    version: [">=0.9.0", "<0.10.0"]
  - package: elementary-data/elementary
    version: [">=0.10.0"]

# models/marts/schema.yml - advanced tests
models:
  - name: fct_orders
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
          max_value: 10000000
    columns:
      - name: amount
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 40
              max_value: 200
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: ".+@.+\..+"

# Run only tests for modified models (efficient CI)
dbt test --select state:modified+ --defer --state ./prod-artifacts

dbt-expectations — 50+ advanced tests: distributions, regex, cross-table comparisons, null value percentages. The most useful package after core dbt
Severity — error (blocks the pipeline) vs warn (alerts without blocking). Rule: warn for business metrics that may fluctuate, error for absolute technical constraints
Elementary — dbt package that generates a quality report and automatically detects statistical anomalies (volume, distributions) without writing manual tests
Store failures — store_failures: true in dbt_project.yml stores failing rows in a table for diagnosis. Essential in production
Source freshness — configure freshness in sources.yml to alert if the source has not been updated in N hours. First signal of an upstream pipeline failure

dbt-expectations - 50+ advanced tests: distributions, regex, cross-table comparisons. The most useful package after core dbt
Severity - error (blocks the pipeline) vs warn (alerts without blocking). Rule: warn for business metrics that may fluctuate, error for absolute technical constraints
Elementary - dbt package that generates a quality report and automatically detects statistical anomalies without manual tests
Store failures - store_failures: true stores failing rows in a table for diagnosis. Essential in production for quick debugging
Source freshness - configure freshness in sources.yml to alert if the source has not been updated in N hours. First signal of an upstream pipeline failure

7Level grid

Level	Mastery	GO signal	NO-GO
Confirmed	YAML tests, dbt-expectations, singular tests	Has written singular tests, uses dbt-expectations	Only uses not_null and unique
Senior	Test macros, coverage strategy, optimized CI	Has created reusable test macros, documented coverage strategy	Does not know how to write a test macro

Vous recrutez un Analytics Engineer dbt Senior ?

Premier entretien gratuit. Rapport GO/NO-GO sous 48h.

Tester gratuitement Reserver un appel