Reference architecture · modern ELT stack

dbt + Airflow + Snowflake

Data is loaded raw into Snowflake, transformed inside Snowflake by dbt, and the whole run is scheduled and monitored by Airflow. Each tool does one job: Snowflake stores and computes, dbt defines the transformations, and Airflow decides when things run.

Airflow: orchestration dbt: transformation Snowflake: storage + compute Data flow
SOURCES INGESTION (EL) CONSUMERS Apache Airflow Schedules, retries, alerts, backfills sensor → trigger load → dbt build Cosmos: one task per dbt model dbt Core SQL + Jinja models, tests, docs ref() / source() → DAG compiles SQL, runs in Snowflake triggers / waits SQL on TRANSFORM_WH OLTP databases PostgreSQL, SQL Server SaaS apps / APIs Salesforce, Stripe Files CSV, JSON, Parquet Event streams Kafka, clickstream, IoT ELT connectors Fivetran / Airbyte or custom Python in Airflow CDC + API pulls → RAW schemas S3 stage + Snowpipe COPY INTO / auto-ingest Kafka connector Snowpipe Streaming Snowflake database DW · storage and compute separated RAW landed as-is VARIANT JSON dbt source() transient freshness checks STAGING 1:1 with source rename, cast, dedupe stg_* views + snapshots INTERMED. joins and business logic reusable int_* ephemeral or views MARTS facts and dimensions tested + docs fct_* dim_* tables / incremental VIRTUAL WAREHOUSES LOAD_WH · XS TRANSFORM_WH · M BI_WH · multi Time Travel · zero-copy clones · RBAC · resource monitors BI dashboards Power BI, Tableau, Looker Data science / ML Snowpark, notebooks Reverse ETL Hightouch, Census → CRM Secure data sharing partners, other accounts 1 2 3 4 5 CROSS-CUTTING Git + CI/CD GitHub Actions, PR review Slim CI on a zero-copy clone Security and secrets key-pair auth, Airflow connections roles: LOADER, TRANSFORMER, REPORTER Observability dbt tests + artifacts, Airflow alerts source freshness, lineage docs Cost control auto-suspend, right-sized warehouses resource monitors, query tags
Figure 1 — Logical architecture. Numbered markers match the steps below. On a phone, scroll the diagram sideways or use full screen.

How a daily run flows

  1. Extract and load. Fivetran or Airbyte copy database and SaaS data into Snowflake RAW schemas. Files land in S3 and Snowpipe loads them, and Kafka events arrive through Snowpipe Streaming. Nothing is transformed yet.
  2. Land in RAW. Data arrives as-is, with JSON kept in VARIANT columns. dbt declares these tables as sources with freshness rules.
  3. Airflow starts the transformation. The DAG waits for loads to finish (sensor or connector API), runs dbt source freshness, then dbt build. With Cosmos, each model becomes its own Airflow task with its own retry.
  4. dbt transforms inside Snowflake. dbt compiles models to SQL and runs them on TRANSFORM_WH, building staging → intermediate → marts in DAG order. Tests run right after each model, and a failed test stops everything downstream of it.
  5. Serve. BI tools query the marts through BI_WH (multi-cluster for the morning rush). Snowpark notebooks, reverse ETL and data shares read the same tables.
  6. Monitor. Airflow alerts on failures. dbt artifacts (run_results.json) record timings and test results, and Snowflake ACCOUNT_USAGE plus resource monitors track cost.

Components and responsibilities

ComponentResponsibilityKey design choices
ELFivetran / AirbyteCopy source data into Snowflake with no business logicCDC for databases, incremental API syncs, one RAW schema per source
SFSnowpipeContinuous file loading from an S3 external stageStorage integration (no keys in SQL), auto-ingest via SQS, file sizes of 100–250 MB
SFSnowflakeStores all layers; supplies compute for loads, dbt and BISeparate warehouses per workload, transient RAW/staging tables, Time Travel on marts
dbtdbt CoreDefines every transformation, test and document as codeStaging/intermediate/marts layers, incremental facts, snapshots for SCD2, contracts on public marts
AFAirflowDecides when things run, in what order, and what happens on failureCosmos for per-model tasks, retries, SLAs, Slack/email alerts, backfills by date
GitGitHub + CIVersion control and automated checks before mergestate:modified+ with --defer, SQLFluff linting, deploy on merge
BIBI / consumersRead only from martsRead-only REPORTER role, dedicated BI warehouse, exposures declared in dbt

Snowflake layout

Databases and schemas

RAW                 -- loaded by Fivetran / Snowpipe
  ├─ ERP
  ├─ SALESFORCE
  └─ EVENTS
ANALYTICS           -- built by dbt
  ├─ STAGING
  ├─ INTERMEDIATE
  ├─ MARTS
  └─ SNAPSHOTS
DBT_DEV_<USER>      -- each developer's sandbox
CI_PR_<N>           -- zero-copy clone per pull request

Roles and warehouses

role LOADER       → write RAW       → LOAD_WH (XS)
role TRANSFORMER  → read RAW,
                    write ANALYTICS → TRANSFORM_WH (M)
role REPORTER     → read MARTS      → BI_WH (S, 1–4 clusters)

all custom roles roll up to SYSADMIN
service users: key-pair auth, no passwords
auto_suspend = 60 on every warehouse

Airflow DAG and dbt project

DAG with Cosmos

from cosmos import DbtTaskGroup, ProjectConfig, ProfileConfig
from cosmos.profiles import SnowflakePrivateKeyPemProfileMapping

profile = ProfileConfig(
    profile_name="analytics", target_name="prod",
    profile_mapping=SnowflakePrivateKeyPemProfileMapping(
        conn_id="snowflake_transformer"),
)

with DAG("daily_elt", schedule="0 5 * * *",
         start_date=datetime(2026, 1, 1), catchup=False,
         default_args={"retries": 2}) as dag:

    wait_load = FivetranSensor(task_id="wait_fivetran", ...)
    freshness = BashOperator(task_id="freshness",
        bash_command="dbt source freshness")
    transform = DbtTaskGroup(group_id="dbt",
        project_config=ProjectConfig("/opt/airflow/dbt/analytics"),
        profile_config=profile)

    wait_load >> freshness >> transform

dbt project structure

analytics/
├─ dbt_project.yml
├─ models/
│  ├─ staging/
│  │  ├─ erp/_erp__sources.yml
│  │  └─ erp/stg_erp__employees.sql
│  ├─ intermediate/
│  │  └─ int_dimemployee_prepared.sql
│  └─ marts/
│     ├─ dim_employee.sql
│     ├─ fct_payroll.sql   -- incremental
│     └─ _marts.yml        -- tests, contracts
├─ snapshots/employee_snapshot.yml
├─ macros/generate_schema_name.sql
├─ seeds/
└─ packages.yml            -- dbt_utils

Change flow from laptop to production

Develop in DBT_DEV_<USER>→ Open a pull request→ CI clones prod, runs dbt build -s state:modified+ --defer→ Review and merge→ New dbt image deployed to Airflow→ Next scheduled run builds prod

Explaining it in an interview

The two-minute answer

“We use an ELT pattern. Fivetran and Snowpipe land raw data in Snowflake, and dbt transforms it in layers: staging cleans each source, intermediate holds the business logic, and marts publish tested facts and dimensions. Airflow orchestrates the run. It waits for the loads, checks source freshness, then runs dbt through Cosmos so each model is its own task with retries and alerts.

Snowflake gives us a separate warehouse for loading, transforming and BI, so the workloads never compete. Every change goes through a pull request, and CI builds only the modified models against a zero-copy clone of prod.”

  • Why ELT and not ETL? Snowflake compute is elastic, so transforming inside it is simpler and keeps the raw data available for replay.
  • Why Airflow if dbt has its own DAG? dbt only orders SQL models. Airflow coordinates the whole pipeline: loads, dbt, ML jobs, reverse ETL, alerts and backfills.
  • Why Cosmos over a single BashOperator? You get per-model visibility and retries, so a failure reruns one model instead of the whole project.
  • Alternatives worth naming: dbt Cloud's scheduler instead of Airflow; Snowflake tasks or dynamic tables for simple in-warehouse pipelines; Dagster for asset-based orchestration.