Integration with dbt and Airflow in Snowflake - Step-by-Step Execution

Process Flow - Integration with dbt and Airflow

Start Airflow DAG

↓

Trigger dbt run command

↓

dbt compiles and runs models

↓

dbt loads data into Snowflake

↓

Airflow checks dbt run status

↓

If success -> continue DAG

↓

If failure -> alert or retry

↓

End DAG

Airflow starts a workflow that triggers dbt to build data models in Snowflake, then monitors success or failure to continue or alert.

Execution Sample

Snowflake

dag = DAG('dbt_snowflake', schedule_interval='@daily')
run_dbt = BashOperator(task_id='run_dbt', bash_command='dbt run --profiles-dir ./profiles')
run_dbt

This Airflow DAG runs dbt commands daily to build models in Snowflake.

Process Table

Step	Action	Command/Check	Result	Next Step
1	Start DAG	Airflow scheduler triggers DAG	DAG started	Trigger dbt run
2	Trigger dbt run	Execute 'dbt run' command	dbt starts compiling models	Wait for dbt completion
3	dbt compiles models	dbt parses SQL and YAML files	Models compiled successfully	Run models in Snowflake
4	Run models	dbt runs SQL in Snowflake	Data loaded into Snowflake tables	Check dbt run status
5	Check status	dbt run exit code	Success (0)	Continue DAG tasks
6	DAG continues	Next Airflow tasks execute	Workflow proceeds	End DAG
7	DAG ends	All tasks complete	DAG finished successfully	Stop

💡 DAG ends after dbt run completes successfully and all tasks finish.

Status Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	Final
DAG Status	Not started	Running	Running	Success	Finished
dbt Run Status	Not started	Running	Running	Success	Success
Snowflake Data	Old data	Old data	New data loaded	New data loaded	New data loaded

Key Moments - 3 Insights

Why does Airflow wait after triggering the dbt run command?

Airflow waits to ensure dbt finishes compiling and running models before moving on, as shown in steps 2 to 5 in the execution_table.

What happens if dbt run fails during execution?

If dbt run fails, Airflow detects a non-zero exit code at step 5 and can trigger alerts or retries instead of continuing the DAG.

How does dbt load data into Snowflake?

dbt runs SQL models that create or update tables in Snowflake, shown in step 4 where data is loaded into Snowflake tables.

Visual Quiz - 3 Questions

Look at the execution_table, what is the DAG Status after step 4?

A Running

B Success

C Not started

D Failed

💡 Hint

Check the variable_tracker row for DAG Status after Step 4.

At which step does Airflow check the dbt run exit code?

A Step 3

B Step 2

C Step 5

D Step 6

💡 Hint

Look at the 'Check status' action in the execution_table.

If dbt run fails, what would change in the execution_table?

A Step 4 would not run

B Step 5 result would be 'Failure' and DAG would alert or retry

C DAG would skip dbt run

D DAG would finish successfully anyway

💡 Hint

Refer to key_moments about failure handling and step 5 in execution_table.

Concept Snapshot

Integration with dbt and Airflow:
- Airflow DAG triggers dbt commands
- dbt compiles and runs models in Snowflake
- Airflow monitors dbt run status
- On success, DAG continues; on failure, alerts or retries
- Enables automated, scheduled data transformations

Full Transcript

This visual execution shows how Airflow and dbt work together to build data models in Snowflake. Airflow starts a DAG that runs the dbt command. dbt compiles SQL models and runs them in Snowflake, loading data into tables. Airflow waits for dbt to finish and checks the exit status. If successful, the DAG continues; if not, it can alert or retry. Variables like DAG Status and dbt Run Status change from not started to running to success as the workflow progresses. This integration automates data transformations on a schedule.