Accueil›Blog›Stack dbt + Snowflake : architecture complète en production

Guide recrutement data

Stack dbt + Snowflake : architecture complète en production

dbt et Snowflake forment la stack analytique la plus répandue en 2025. En entretien Senior, on évalue la capacité à architecturer cette stack de manière robuste, testée et économique.

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

Sommaire

Architecture de référence
Organisation des modèles dbt
Optimisation Snowflake
CI/CD slim
Maîtrise des coûts
Gouvernance et documentation
Grille

1Architecture de référence dbt + Snowflake

Question discriminante

Décrivez l architecture complète d une stack dbt + Snowflake bien structurée.

# Architecture dbt + Snowflake de référence

SNOWFLAKE
├── RAW_DB           # données brutes, jamais modifiées
│   ├── FIVETRAN/    # ingestion via Fivetran
│   └── AIRBYTE/     # ingestion via Airbyte
├── DEV_DB           # environnement de développement
│   └── ANALYTICS/   # schémas dbt dev (par développeur)
└── PROD_DB          # production
    ├── STAGING/     # modèles staging dbt
    ├── INTERMEDIATE/# modèles intermédiaires
    └── MARTS/       # tables consommées par la BI

# dbt profiles.yml
my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: mon_compte
      database: DEV_DB
      schema: '{{ env_var("DBT_SCHEMA", "analytics_" ~ env_var("USER")) }}'
      warehouse: DEV_WH
    prod:
      database: PROD_DB
      schema: MARTS
      warehouse: PROD_WH

Séparation RAW/DEV/PROD — les données brutes ne sont jamais modifiées. Les développeurs travaillent dans leur propre schéma
Schema par développeur — chaque dev a son schéma isolé en DEV. Évite les conflits

2Organisation des modèles dbt

Question discriminante

Comment organisez-vous staging, intermediate et marts dans un projet dbt ?

# dbt_project.yml - configuration par layer
models:
  mon_projet:
    staging:
      +schema: staging
      +materialized: view          # vues légères
      +tags: ['staging']
    intermediate:
      +schema: intermediate
      +materialized: view
      +tags: ['intermediate']
    marts:
      +schema: marts
      +materialized: table         # tables pour la BI
      finance:
        +materialized: table
      marketing:
        +materialized: table
      +tags: ['marts']
    seeds:
      +schema: seeds
      +tags: ['seed']

Staging — 1 modèle par table source, renommage, cast, pas de logique métier
Intermediate — jointures et logique métier partagée entre plusieurs marts
Marts — tables finales orientées métier, consommées par Power BI/Tableau/Looker
Matérialisations — vues pour staging/intermediate (pas de stockage), tables pour les marts (performance BI)

3Optimisation Snowflake pour dbt

Question discriminante

Quels paramètres Snowflake optimisez-vous pour vos modèles dbt ?

-- Cluster Key sur les grandes tables marts
ALTER TABLE PROD_DB.MARTS.FCT_ORDERS
CLUSTER BY (order_date, region);

-- Incremental model dbt optimisé
{{ config(
    materialized='incremental',
    unique_key='order_id',
    cluster_by=['order_date'],
    on_schema_change='append_new_columns',
    snowflake_warehouse='TRANSFORM_WH_M'
) }}

SELECT *
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

Incremental models — ne retraiter que les nouvelles données. Réduit le compute de 90%+ sur les grandes tables
Warehouse sizing — utiliser un petit warehouse (XS-S) pour les modèles légers, un plus grand (M-L) pour les transformations lourdes
Query tags — taguer les requêtes dbt avec le nom du modèle pour l attribution des coûts

4CI/CD : slim CI dbt sur Snowflake

Question discriminante

Comment mettez-vous en place le slim CI dbt pour limiter les coûts Snowflake en CI ?

# .github/workflows/dbt-ci.yml
jobs:
  dbt_slim_ci:
    steps:
      - name: dbt build (models modifiés uniquement)
        run: |
          dbt build \
            --select state:modified+ \
            --defer \
            --state ./prod-artifacts \
            --target ci \
            --exclude tag:slow
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SF_ACCOUNT }}
          SNOWFLAKE_USER: ${{ secrets.SF_USER }}
          SNOWFLAKE_PASSWORD: ${{ secrets.SF_PASSWORD }}
          
      - name: Upload artifacts
        uses: actions/upload-artifact@v4
        with:
          name: dbt-artifacts
          path: target/manifest.json

state:modified+ — rebuild uniquement les modèles changés et leurs descendants
--defer — utiliser les relations de PROD pour les modèles non modifiés. Pas besoin de rebuilder toute la stack
CI warehouse — utiliser un XS warehouse dédié pour la CI. Arrêter après chaque run

5Maîtrise des coûts Snowflake + dbt

Question discriminante

Comment réduisez-vous les coûts Snowflake de vos pipelines dbt ?

Incremental models — premier levier. Éviter les full refresh sur les grandes tables
Auto-suspend agressif — 1 minute de suspension pour les warehouses dbt (les jobs sont courts)
Warehouse par use case — un warehouse pour la CI (XS), un pour les transformations quotidiennes (S-M), un pour la BI (XS avec cache)
INFORMATION_SCHEMA monitoring — suivre les credits consommés par modèle dbt via query_tag
Éviter SELECT * — toujours sélectionner les colonnes nécessaires dans les modèles dbt

6Gouvernance : documentation et lineage

Question discriminante

Comment assurez-vous que votre projet dbt reste maintenable dans le temps ?

Descriptions obligatoires — configurer dbt pour forcer la documentation des colonnes clés via pre-commit hooks
Exposures — documenter qui consomme chaque modèle (dashboards Power BI, APIs, rapports automatiques)
dbt docs — générer et publier la documentation automatiquement à chaque merge sur main
Elementary — package de data observabilité pour monitorer la qualité des données en production
Conventions de nommage — stg_ pour staging, int_ pour intermediate, fct_ et dim_ pour les marts

# profiles.yml Snowflake multi-env
my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: myorg.snowflakecomputing.com
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      role: TRANSFORMER_DEV
      database: DEV_DB
      warehouse: TRANSFORM_WH_XS
      schema: "dbt_{{ env_var('USER', 'dev') }}"
      threads: 4
    prod:
      type: snowflake
      account: myorg.snowflakecomputing.com
      user: dbt_prod_svc
      private_key_path: /secrets/snowflake_pk.p8
      role: TRANSFORMER_PROD
      database: PROD_DB
      warehouse: TRANSFORM_WH_M
      schema: analytics
      threads: 16

# dbt_project.yml optimisations Snowflake
models:
  my_project:
    staging:
      +materialized: view
    marts:
      +materialized: table
      +snowflake_warehouse: TRANSFORM_WH_L
      +post-hook: "GRANT SELECT ON {{ this }} TO ROLE REPORTER"

Key pair authentication - privilegier la cle privee RSA plutot que mot de passe pour les comptes de service. Plus securise, supporte par les secrets managers
Warehouse sizing par couche - staging/intermediate sur XS (views legeres), marts sur M ou L pour les tables materialisees
Dynamic tables vs dbt incremental - pour les agregations simples, les Snowflake Dynamic Tables remplacent avantageusement dbt incremental : zero orchestration externe
Cloning pour les tests - CREATE DATABASE dev_clone CLONE production avant les tests de migration. Zero-copy, instantane, sans impacter la prod
COPY INTO vs INSERT - charger de gros volumes dans Snowflake via COPY INTO depuis S3/GCS. 10-100x plus rapide que des INSERT en batch

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Confirmé	Structure staging/marts, tests, déploiement manuel	A structuré un projet dbt en 3 layers, a configuré Snowflake warehouses	Met toute la logique dans les marts sans staging
Senior	Incremental models, slim CI, coûts maîtrisés, gouvernance	A mis en place le slim CI, utilise des incremental models, monitore les coûts	Ne sait pas ce qu est un incremental model, ne connaît pas le slim CI

1dbt + Snowflake reference architecture

Discriminating question

Describe the complete architecture of a well-structured dbt + Snowflake stack.

# dbt + Snowflake reference architecture

SNOWFLAKE
├── RAW_DB           # raw data, never modified
│   ├── FIVETRAN/    # ingestion via Fivetran
│   └── AIRBYTE/     # ingestion via Airbyte
├── DEV_DB           # development environment
│   └── ANALYTICS/   # dbt dev schemas (per developer)
└── PROD_DB          # production
    ├── STAGING/     # dbt staging models
    ├── INTERMEDIATE/# intermediate models
    └── MARTS/       # tables consumed by BI

# dbt profiles.yml
my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: my_account
      database: DEV_DB
      schema: '{{ env_var("DBT_SCHEMA", "analytics_" ~ env_var("USER")) }}'
      warehouse: DEV_WH
    prod:
      database: PROD_DB
      schema: MARTS
      warehouse: PROD_WH

RAW/DEV/PROD separation — raw data is never modified. Developers work in their own schema
Schema per developer — each dev has their own isolated schema in DEV. Avoids conflicts

2dbt model organization

Discriminating question

How do you organize staging, intermediate and marts in a dbt project?

# dbt_project.yml - configuration per layer
models:
  my_project:
    staging:
      +schema: staging
      +materialized: view          # lightweight views
      +tags: ['staging']
    intermediate:
      +schema: intermediate
      +materialized: view
      +tags: ['intermediate']
    marts:
      +schema: marts
      +materialized: table         # tables for BI
      finance:
        +materialized: table
      marketing:
        +materialized: table
      +tags: ['marts']
    seeds:
      +schema: seeds
      +tags: ['seed']

Staging — 1 model per source table, renaming, cast, no business logic
Intermediate — joins and business logic shared across multiple marts
Marts — final business-oriented tables, consumed by Power BI/Tableau/Looker
Materializations — views for staging/intermediate (no storage), tables for marts (BI performance)

3Snowflake optimization for dbt

Discriminating question

Which Snowflake parameters do you optimize for your dbt models?

-- Cluster Key on large marts tables
ALTER TABLE PROD_DB.MARTS.FCT_ORDERS
CLUSTER BY (order_date, region);

-- Optimized dbt incremental model
{{ config(
    materialized='incremental',
    unique_key='order_id',
    cluster_by=['order_date'],
    on_schema_change='append_new_columns',
    snowflake_warehouse='TRANSFORM_WH_M'
) }}

SELECT *
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

Incremental models — only reprocess new data. Reduces compute by 90%+ on large tables
Warehouse sizing — use a small warehouse (XS-S) for lightweight models, a larger one (M-L) for heavy transformations
Query tags — tag dbt queries with the model name for cost attribution

4CI/CD: dbt slim CI on Snowflake

Discriminating question

How do you set up dbt slim CI to limit Snowflake costs in CI?

# .github/workflows/dbt-ci.yml
jobs:
  dbt_slim_ci:
    steps:
      - name: dbt build (modified models only)
        run: |
          dbt build \
            --select state:modified+ \
            --defer \
            --state ./prod-artifacts \
            --target ci \
            --exclude tag:slow
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SF_ACCOUNT }}
          SNOWFLAKE_USER: ${{ secrets.SF_USER }}
          SNOWFLAKE_PASSWORD: ${{ secrets.SF_PASSWORD }}
          
      - name: Upload artifacts
        uses: actions/upload-artifact@v4
        with:
          name: dbt-artifacts
          path: target/manifest.json

state:modified+ — rebuild only changed models and their descendants
--defer — use PROD relations for unmodified models. No need to rebuild the entire stack
CI warehouse — use a dedicated XS warehouse for CI. Suspend after each run

5Snowflake + dbt cost management

Discriminating question

How do you reduce Snowflake costs for your dbt pipelines?

Incremental models — first lever. Avoid full refresh on large tables
Aggressive auto-suspend — 1 minute suspension for dbt warehouses (jobs are short)
Warehouse per use case — one warehouse for CI (XS), one for daily transformations (S-M), one for BI (XS with cache)
INFORMATION_SCHEMA monitoring — track credits consumed per dbt model via query_tag
Avoid SELECT * — always select only the necessary columns in dbt models

6Governance: documentation and lineage

Discriminating question

How do you ensure your dbt project remains maintainable over time?

Mandatory descriptions — configure dbt to enforce documentation of key columns via pre-commit hooks
Exposures — document who consumes each model (Power BI dashboards, APIs, automated reports)
dbt docs — automatically generate and publish documentation on every merge to main
Elementary — data observability package to monitor data quality in production
Naming conventions — stg_ for staging, int_ for intermediate, fct_ and dim_ for marts

# profiles.yml Snowflake multi-env
my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: myorg.snowflakecomputing.com
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      role: TRANSFORMER_DEV
      database: DEV_DB
      warehouse: TRANSFORM_WH_XS
      schema: "dbt_{{ env_var('USER', 'dev') }}"
      threads: 4
    prod:
      type: snowflake
      account: myorg.snowflakecomputing.com
      user: dbt_prod_svc
      private_key_path: /secrets/snowflake_pk.p8
      role: TRANSFORMER_PROD
      database: PROD_DB
      warehouse: TRANSFORM_WH_M
      schema: analytics
      threads: 16

# dbt_project.yml Snowflake optimizations
models:
  my_project:
    staging:
      +materialized: view
    marts:
      +materialized: table
      +snowflake_warehouse: TRANSFORM_WH_L
      +post-hook: "GRANT SELECT ON {{ this }} TO ROLE REPORTER"

Key pair authentication - prefer RSA private key over password for service accounts. More secure, supported by secrets managers
Warehouse sizing per layer - staging/intermediate on XS (lightweight views), marts on M or L for materialized tables
Dynamic tables vs dbt incremental - for simple aggregations, Snowflake Dynamic Tables advantageously replace dbt incremental: zero external orchestration
Cloning for tests - CREATE DATABASE dev_clone CLONE production before migration tests. Zero-copy, instant, without impacting prod
COPY INTO vs INSERT - load large volumes into Snowflake via COPY INTO from S3/GCS. 10-100x faster than batch INSERT

7Level grid

Level	Mastery	GO signal	NO-GO
Confirmed	staging/marts structure, tests, manual deployment	Has structured a dbt project in 3 layers, has configured Snowflake warehouses	Puts all logic in marts without staging
Senior	Incremental models, slim CI, cost management, governance	Has set up slim CI, uses incremental models, monitors costs	Does not know what an incremental model is, does not know slim CI

Vous recrutez un Analytics Engineer dbt + Snowflake ?

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

Tester gratuitement Reserver un appel