How to Build a Fault‑Tolerant ETL Pipeline on Snowflake with dbt and Airflow

When a nightly job crashes and your dashboard stays blank, the whole team feels the sting. A reliable pipeline isn’t a nice‑to‑have; it’s the backbone of any data‑driven product. In this post I’ll walk you through a simple, yet rock‑solid way to stitch Snowflake, dbt, and Airflow together so that a single hiccup doesn’t bring the whole system down.

Why Fault Tolerance Matters Today

Data teams are under pressure to deliver fresh insights every day. At the same time, cloud costs are rising and the margin for error is shrinking; for strategies on low‑latency cloud deployments, see our guide on optimizing real‑time data streams in Kafka using Flink. A fault‑tolerant design gives you three things:

Confidence – you know a failure will be caught and retried, not silently ignored.
Speed – you spend less time firefighting and more time building new features.
Cost control – retries happen only where needed, avoiding wasteful full‑pipeline reruns.

I learned this the hard way during a migration last year. One mis‑typed column name caused an entire Snowflake load to fail, and because the Airflow DAG had no retry logic, the downstream models never ran. We spent a whole afternoon digging through logs. The lesson? Build resilience from day one.

Core Ingredients

Before we dive into code, let’s clarify the three pieces we’ll be using.

Snowflake – a cloud data warehouse that separates compute and storage. Think of it as a giant spreadsheet that can scale up or down in seconds.
dbt (data build tool) – a framework that lets you write SQL transformations as modular “models”. It handles dependency graphs, testing, and documentation.
Airflow – an orchestrator that runs tasks on a schedule, tracks their state, and can retry on failure. It’s the conductor of our data symphony.

High‑Level Architecture

Source → Airflow (extract) → Snowflake (raw) → dbt (transform) → Snowflake (analytics) → Airflow (load)

Airflow pulls data from APIs, files, or other warehouses and lands it in a raw schema in Snowflake.
dbt reads from the raw schema, applies clean‑ups, joins, and aggregates, then writes to an analytics schema.
Airflow monitors the dbt run, handles retries, and alerts on persistent failures.

Step 1: Set Up Snowflake Securely

Create a dedicated role for the pipeline. Grant it only the privileges it needs: USAGE on the database, CREATE SCHEMA on the raw and analytics schemas, and INSERT/SELECT on the tables.
Enable Snowflake’s query tagging. Tag each query with a pipeline identifier; this makes debugging easier later on.
Turn on automatic clustering for large tables. It reduces the chance of performance‑related failures.

Step 2: Build the dbt Project

Project Layout

my_project/
├─ models/
│  ├─ raw/
│  │  └─ source_*.sql
│  └─ analytics/
│     └─ fact_*.sql
├─ tests/
│  └─ schema.yml
└─ dbt_project.yml

Keep raw models as simple SELECT * FROM source_table statements.
Analytics models contain the business logic – joins, filters, window functions.

Adding Fault‑Tolerance Features

Tests – In schema.yml define not_null, unique, and custom SQL tests. dbt will fail the run if any test breaks, preventing bad data from propagating.
Snapshots – Use dbt snapshots for slowly changing dimensions. If a source table suddenly drops a column, the snapshot will capture the change instead of breaking downstream models.
Hooks – Add a post-hook that writes a row into a “pipeline_audit” table with the run timestamp and status. This gives you a quick health check without digging into logs.

Step 3: Wire Airflow to Snowflake and dbt

Connection Setup

In the Airflow UI, add a Snowflake connection (snowflake_default). Store the username, password, and role in a secret manager – never hard‑code them. Also add a dbt connection that points to the virtual environment where dbt lives.

DAG Design

from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakeOperator
from airflow.operators.bash import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'maya',
    'retries': 3,
    'retry_delay': timedelta(minutes=5),
    'email_on_failure': True,
    'email': ['[email protected]'],
}

with DAG(
    dag_id='snowflake_etl',
    schedule_interval='@daily',
    start_date=datetime(2023, 1, 1),
    default_args=default_args,
    catchup=False,
) as dag:

    # 1. Extract – simple COPY into raw schema
    extract = SnowflakeOperator(
        task_id='extract_raw',
        sql='CALL my_etl.sp_load_raw();',
        snowflake_conn_id='snowflake_default',
    )

    # 2. Transform – run dbt
    transform = BashOperator(
        task_id='run_dbt',
        bash_command='cd /opt/dbt && dbt run --profiles-dir .',
    )

    # 3. Load – move analytics tables to final schema (optional)
    load = SnowflakeOperator(
        task_id='final_load',
        sql='CALL my_etl.sp_publish_analytics();',
        snowflake_conn_id='snowflake_default',
    )

    extract >> transform >> load

Why This Works

Retries – Airflow will automatically retry any failed task up to three times, with a five‑minute pause. This catches transient network glitches or Snowflake “warehouse busy” errors.
Task Isolation – Each step runs in its own container. If the dbt run fails, the extract step is not re‑executed, saving compute credits.
Alerting – The email_on_failure flag sends a quick note to the team, so we can act before the next run.

Step 4: Test the Whole Flow

Unit test each dbt model with dbt test.
Run the DAG manually in Airflow’s UI. Watch the logs; you’ll see Snowflake query tags appear, and the audit table get a new row after each successful step.
Simulate a failure – for example, rename a column in the source table. The dbt test should catch the issue, the task will fail, and Airflow will retry. After the third attempt, the DAG will be marked failed and you’ll get an email.

Step 5: Operate and Iterate

Monitoring – Set up a simple dashboard that reads from the pipeline_audit table. A green light means “last run succeeded”.
Scaling – If the raw load starts taking longer, increase the Snowflake warehouse size for that task only. Airflow’s SnowflakeOperator lets you pass a warehouse parameter per task.
Version control – Keep the dbt project and the DAG code in the same Git repo. When you add a new source, create a migration script that updates both the Snowflake schema and the dbt model together; for incremental ELT patterns on other platforms, see our guide on designing incremental ELT pipelines with Delta Lake and Azure Data Factory. This keeps the pipeline in sync.

Personal Takeaway

Building a fault‑tolerant pipeline feels a lot like building a good house. You start with a solid foundation (Snowflake roles, secure connections), add sturdy walls (dbt tests, snapshots), and finish with a roof that can weather storms (Airflow retries, alerts). The effort you put in early saves you from endless midnight calls later.

If you follow the steps above, you’ll have a pipeline that not only survives hiccups but also tells you exactly where it stumbled. That kind of visibility turns data engineering from a firefighting job into a craft you can be proud of.