Accueil›Blog›Test technique dbt snapshots et SCD : historiser les changements

Guide recrutement data

Test technique dbt snapshots et SCD : historiser les changements

Historiser les changements de données (Slowly Changing Dimensions) est un sujet classique en entretien. dbt snapshots offrent une solution élégante mais qui nécessite une vraie compréhension.

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

Sommaire

Types de SCD
Snapshots dbt
Stratégie timestamp
Stratégie check
Gestion des suppressions
Consommer les snapshots
Grille

1Types de SCD : lequel choisir

Question discriminante

Quelle est la différence entre SCD1, SCD2 et SCD3 ? Quand utilisez-vous SCD2 ?

SCD Type 1 — overwrite. La valeur actuelle écrase l'ancienne. Pas d'historique. Simple, idéal pour les corrections d'erreurs
SCD Type 2 — nouvelle ligne pour chaque changement. Conserve tout l'historique avec des colonnes valid_from/valid_to/is_current. Le plus courant en data warehousing
SCD Type 3 — colonne supplémentaire pour la valeur précédente. Limite : ne conserve que N versions. Rare en pratique
Quand SCD2 — analyse des comportements dans le temps (comment le segment client a évolué), attribution correcte (le prix au moment de l'achat), conformité réglementaire
Coût SCD2 — les jointures avec une table SCD2 nécessitent de filtrer sur is_current=true ou sur la plage de dates. Ajoute de la complexité aux requêtes

Type	Historique	Complexité	Cas d'usage
SCD1	Aucun	Simple	Données de référence, corrections
SCD2	Complet	Élevée	Dimensions clients, produits, géographie
SCD3	Partiel (N versions)	Moyenne	Rare

2Snapshots dbt : SCD2 automatique

Question discriminante

Qu'est-ce qu'un snapshot dbt ? Comment le configurez-vous ?

-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}

{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=True
    )
}}

SELECT 
    customer_id,
    email,
    plan,
    segment,
    country,
    updated_at
FROM {{ source('crm', 'customers') }}

{% endsnapshot %}

-- Colonnes ajoutées automatiquement par dbt :
-- dbt_scd_id     : clé primaire du snapshot (hash)
-- dbt_valid_from : date de début de validité
-- dbt_valid_to   : date de fin (NULL = enregistrement actuel)
-- dbt_updated_at : timestamp de la dernière mise à jour

dbt snapshot run — crée le snapshot si inexistant, sinon détecte les changements et insère de nouvelles lignes
target_schema — stocker les snapshots dans un schema dédié, séparé des modèles marts
unique_key — identifiant naturel de l'entité. Plusieurs colonnes possibles : unique_key=['id', 'source']

3Stratégie timestamp vs check

Question discriminante

Quelle est la différence entre la stratégie timestamp et check dans les snapshots dbt ?

-- Stratégie TIMESTAMP : recommandée
-- Utilise une colonne updated_at pour détecter les changements
-- Rapide : compare juste un timestamp
{{
    config(
        strategy='timestamp',
        updated_at='updated_at'  -- colonne de la source
    )
}}

-- Stratégie CHECK : quand pas d'updated_at
-- Compare la valeur de colonnes spécifiées
-- Plus lente : hash des colonnes à chaque run
{{
    config(
        strategy='check',
        check_cols=['plan', 'segment', 'country']
        -- ou check_cols='all' pour toutes les colonnes
    )
}}

Timestamp — nécessite une colonne updated_at fiable dans la source. Rapide et scalable
Check — dbt compare un hash des colonnes spécifiées. Plus lent sur grandes tables mais fonctionne sans updated_at
Piège timestamp — si la source ne met pas updated_at à jour correctement, des changements seront manqués

4Gérer les suppressions dans les snapshots

Question discriminante

Que se passe-t-il quand une ligne est supprimée dans la source ? Comment le gérer ?

-- Avec invalidate_hard_deletes=True
-- dbt marque les enregistrements supprimés avec dbt_valid_to = current_timestamp
-- La colonne dbt_is_deleted est ajoutée automatiquement

-- Requête pour analyser les suppressions
SELECT
    customer_id,
    email,
    dbt_valid_from,
    dbt_valid_to,
    CASE WHEN dbt_valid_to IS NULL THEN 'actif' ELSE 'inactif/supprimé' END AS statut
FROM {{ ref('customers_snapshot') }}
WHERE customer_id = 12345
ORDER BY dbt_valid_from;

invalidate_hard_deletes=True — recommandé. dbt détecte les suppressions et ferme la ligne courante
Sans cette option — les enregistrements supprimés restent ouverts (dbt_valid_to = NULL) indéfiniment
Soft deletes — si la source a une colonne is_deleted, filtrer dans la requête du snapshot : WHERE is_deleted = false

5Consommer les snapshots dans les modèles dbt

Question discriminante

Comment interrogez-vous un snapshot pour obtenir l'état actuel vs l'état historique ?

-- État ACTUEL : enregistrements dont dbt_valid_to est NULL
-- models/marts/dim_customers.sql
SELECT
    customer_id,
    email,
    plan,
    segment,
    dbt_valid_from AS snapshot_date
FROM {{ ref('customers_snapshot') }}
WHERE dbt_valid_to IS NULL  -- enregistrements actifs uniquement

-- État HISTORIQUE : quel segment avait ce client au moment de sa commande ?
-- Utile pour l'attribution correcte
SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    o.order_date,
    c.segment AS segment_at_order_time  -- segment du client AU MOMENT de la commande
FROM {{ ref('fct_orders') }} o
JOIN {{ ref('customers_snapshot') }} c
    ON o.customer_id = c.customer_id
    AND o.order_date BETWEEN c.dbt_valid_from AND COALESCE(c.dbt_valid_to, CURRENT_DATE)

SCD Type 2 en pratique - la complexite est dans les jointures aval. Toujours joindre avec la condition temporelle : order_date BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, CURRENT_DATE)
Piege timestamp - si la source ne met pas updated_at a jour correctement, des changements seront manques. Verifier la fiabilite de la colonne avant de choisir la strategie timestamp
invalidate_hard_deletes - recommande. dbt detecte les suppressions et ferme la ligne courante avec dbt_valid_to = current_timestamp
Performance - les snapshots sur des tables source de plusieurs millions de lignes peuvent etre lents avec la strategie check. Privilegier timestamp quand possible
Snapshot testing - tester que dbt_valid_to IS NULL pour les enregistrements actifs, dbt_valid_from < dbt_valid_to pour les historiques, pas de chevauchement de periodes

-- Consommer un snapshot dbt correctement
-- Etat ACTUEL : enregistrements dont dbt_valid_to est NULL
SELECT customer_id, email, plan, segment, dbt_valid_from AS depuis
FROM {{ ref('customers_snapshot') }}
WHERE dbt_valid_to IS NULL;

-- Etat HISTORIQUE : quel segment avait ce client au moment de sa commande ?
-- Indispensable pour l attribution correcte
SELECT
    o.order_id, o.amount, o.order_date,
    c.segment AS segment_au_moment_de_la_commande
FROM {{ ref('fct_orders') }} o
JOIN {{ ref('customers_snapshot') }} c
    ON o.customer_id = c.customer_id
    AND o.order_date BETWEEN c.dbt_valid_from AND COALESCE(c.dbt_valid_to, CURRENT_DATE);

-- Analyser l evolution d un client
SELECT customer_id, plan, segment, dbt_valid_from, dbt_valid_to
FROM {{ ref('customers_snapshot') }}
WHERE customer_id = 'CUST-42'
ORDER BY dbt_valid_from;

Jointure temporelle - joindre une table de faits avec un SCD2 via la condition : event_date BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, CURRENT_DATE)
Performance snapshot - les snapshots sur des tables source de plusieurs millions de lignes peuvent etre lents avec la strategie check. Privilegier timestamp quand disponible
Snapshot testing - tester que dbt_valid_to IS NULL pour les enregistrements actifs, que dbt_valid_from est anterieur a dbt_valid_to pour les historiques

6Grille par niveau

Niveau	Maîtrise	Signal GO	NO-GO
Confirmé	SCD1 vs SCD2, configuration snapshot dbt, stratégie timestamp	Explique les 3 types de SCD, a configuré un snapshot avec stratégie timestamp, sait requêter l'état actuel	Ne sait pas ce qu'est un SCD2, n'a jamais utilisé les snapshots dbt
Senior	Gestion des suppressions, jointures historiques, check vs timestamp, performance	Gère les hard deletes, fait des jointures temporelles pour l'attribution historique, connaît les limites de performance	Ne sait pas comment joindre une table de faits avec un SCD2 au bon moment

1SCD Types: which one to choose

Discriminating question

What is the difference between SCD1, SCD2 and SCD3? When do you use SCD2?

SCD Type 1 — overwrite. The current value overwrites the old one. No history. Simple, ideal for error corrections
SCD Type 2 — new row for each change. Preserves full history with valid_from/valid_to/is_current columns. Most common in data warehousing
SCD Type 3 — additional column for the previous value. Limitation: only keeps N versions. Rare in practice
When SCD2 — behavioral analysis over time (how the customer segment evolved), correct attribution (price at time of purchase), regulatory compliance
SCD2 cost — joins with an SCD2 table require filtering on is_current=true or on the date range. Adds complexity to queries

Type	History	Complexity	Use case
SCD1	None	Simple	Reference data, corrections
SCD2	Full	High	Customer, product, geography dimensions
SCD3	Partial (N versions)	Medium	Rare

2dbt snapshots: automatic SCD2

Discriminating question

What is a dbt snapshot? How do you configure it?

-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}

{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=True
    )
}}

SELECT 
    customer_id,
    email,
    plan,
    segment,
    country,
    updated_at
FROM {{ source('crm', 'customers') }}

{% endsnapshot %}

-- Columns automatically added by dbt:
-- dbt_scd_id     : snapshot primary key (hash)
-- dbt_valid_from : validity start date
-- dbt_valid_to   : end date (NULL = current record)
-- dbt_updated_at : last update timestamp

dbt snapshot run — creates the snapshot if it doesn't exist, otherwise detects changes and inserts new rows
target_schema — store snapshots in a dedicated schema, separate from marts models
unique_key — natural identifier of the entity. Multiple columns possible: unique_key=['id', 'source']

3Timestamp vs check strategy

Discriminating question

What is the difference between the timestamp and check strategy in dbt snapshots?

-- TIMESTAMP strategy: recommended
-- Uses an updated_at column to detect changes
-- Fast: just compares a timestamp
{{
    config(
        strategy='timestamp',
        updated_at='updated_at'  -- source column
    )
}}

-- CHECK strategy: when no updated_at
-- Compares the value of specified columns
-- Slower: hashes columns on each run
{{
    config(
        strategy='check',
        check_cols=['plan', 'segment', 'country']
        -- or check_cols='all' for all columns
    )
}}

Timestamp — requires a reliable updated_at column in the source. Fast and scalable
Check — dbt compares a hash of the specified columns. Slower on large tables but works without updated_at
Timestamp pitfall — if the source does not update updated_at correctly, changes will be missed

4Handling deletions in snapshots

Discriminating question

What happens when a row is deleted in the source? How do you handle it?

-- With invalidate_hard_deletes=True
-- dbt marks deleted records with dbt_valid_to = current_timestamp
-- The dbt_is_deleted column is added automatically

-- Query to analyze deletions
SELECT
    customer_id,
    email,
    dbt_valid_from,
    dbt_valid_to,
    CASE WHEN dbt_valid_to IS NULL THEN 'active' ELSE 'inactive/deleted' END AS status
FROM {{ ref('customers_snapshot') }}
WHERE customer_id = 12345
ORDER BY dbt_valid_from;

invalidate_hard_deletes=True — recommended. dbt detects deletions and closes the current row
Without this option — deleted records remain open (dbt_valid_to = NULL) indefinitely
Soft deletes — if the source has an is_deleted column, filter in the snapshot query: WHERE is_deleted = false

5Consuming snapshots in dbt models

Discriminating question

How do you query a snapshot to get the current state vs the historical state?

-- CURRENT state: records where dbt_valid_to is NULL
-- models/marts/dim_customers.sql
SELECT
    customer_id,
    email,
    plan,
    segment,
    dbt_valid_from AS snapshot_date
FROM {{ ref('customers_snapshot') }}
WHERE dbt_valid_to IS NULL  -- active records only

-- HISTORICAL state: what segment did this customer have at the time of their order?
-- Useful for correct attribution
SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    o.order_date,
    c.segment AS segment_at_order_time  -- customer segment AT THE TIME of the order
FROM {{ ref('fct_orders') }} o
JOIN {{ ref('customers_snapshot') }} c
    ON o.customer_id = c.customer_id
    AND o.order_date BETWEEN c.dbt_valid_from AND COALESCE(c.dbt_valid_to, CURRENT_DATE)

SCD Type 2 in practice - the complexity lies in downstream joins. Always join with the temporal condition: order_date BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, CURRENT_DATE)
Timestamp pitfall - if the source does not update updated_at correctly, changes will be missed. Verify the reliability of the column before choosing the timestamp strategy
invalidate_hard_deletes - recommended. dbt detects deletions and closes the current row with dbt_valid_to = current_timestamp
Performance - snapshots on source tables of several million rows can be slow with the check strategy. Prefer timestamp when possible
Snapshot testing - test that dbt_valid_to IS NULL for active records, dbt_valid_from < dbt_valid_to for historical ones, no overlapping periods

-- Consuming a dbt snapshot correctly
-- CURRENT state: records where dbt_valid_to is NULL
SELECT customer_id, email, plan, segment, dbt_valid_from AS since
FROM {{ ref('customers_snapshot') }}
WHERE dbt_valid_to IS NULL;

-- HISTORICAL state: what segment did this customer have at the time of their order?
-- Essential for correct attribution
SELECT
    o.order_id, o.amount, o.order_date,
    c.segment AS segment_at_order_time
FROM {{ ref('fct_orders') }} o
JOIN {{ ref('customers_snapshot') }} c
    ON o.customer_id = c.customer_id
    AND o.order_date BETWEEN c.dbt_valid_from AND COALESCE(c.dbt_valid_to, CURRENT_DATE);

-- Analyze a customer's history
SELECT customer_id, plan, segment, dbt_valid_from, dbt_valid_to
FROM {{ ref('customers_snapshot') }}
WHERE customer_id = 'CUST-42'
ORDER BY dbt_valid_from;

Temporal join - join a fact table with an SCD2 using the condition: event_date BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, CURRENT_DATE)
Snapshot performance - snapshots on source tables of several million rows can be slow with the check strategy. Prefer timestamp when available
Snapshot testing - test that dbt_valid_to IS NULL for active records, that dbt_valid_from is earlier than dbt_valid_to for historical ones

6Level grid

Level	Mastery	GO signal	NO-GO
Mid-level	SCD1 vs SCD2, dbt snapshot configuration, timestamp strategy	Explains the 3 SCD types, has configured a snapshot with timestamp strategy, knows how to query the current state	Does not know what an SCD2 is, has never used dbt snapshots
Senior	Deletion handling, historical joins, check vs timestamp, performance	Handles hard deletes, performs temporal joins for historical attribution, knows performance limitations	Does not know how to join a fact table with an SCD2 at the right point in time

Vous recrutez un Analytics Engineer ?

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

Tester gratuitement Reserver un appel