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.

Practice

1. What is the main role of dbt when integrated with Snowflake?

easy

A. Transforming raw data into clean, modeled tables using SQL

B. Scheduling data pipeline tasks automatically

C. Storing data securely in Snowflake

D. Monitoring Snowflake warehouse usage

Solution

1. Step 1: Understand dbt's purpose

   dbt is designed to transform raw data into clean, modeled tables using SQL scripts.

2. Step 2: Differentiate from Airflow's role

   Airflow handles scheduling and automation, not data transformation itself.

3. Final Answer:

   Transforming raw data into clean, modeled tables using SQL -> Option A

4. Quick Check:

   dbt = data transformation [OK]

Hint: Remember: dbt transforms data, Airflow schedules tasks [OK]

Common Mistakes:

• Confusing Airflow's scheduling role with dbt's transformation role
• Thinking dbt stores data instead of transforming it
• Assuming dbt monitors Snowflake usage

2. Which file in dbt contains the Snowflake connection details for integration?

easy

A. airflow.cfg

B. dbt_project.yml

C. profiles.yml

D. snowflake.sql

Solution

1. Step 1: Identify dbt configuration files

   dbt uses profiles.yml to store connection details like Snowflake credentials.

2. Step 2: Exclude unrelated files

   dbt_project.yml configures project settings, airflow.cfg is for Airflow, and snowflake.sql is not a config file.

3. Final Answer:

   profiles.yml -> Option C

4. Quick Check:

   Connection details = profiles.yml [OK]

Hint: Connection info in dbt is always in profiles.yml [OK]

Common Mistakes:

• Confusing dbt_project.yml with profiles.yml
• Mixing Airflow config with dbt config
• Assuming SQL files hold connection info

3. Given this Airflow DAG snippet to run a dbt command:

dbt_run = BashOperator(
    task_id='dbt_run',
    bash_command='dbt run --profiles-dir /home/airflow/.dbt',
    dag=dag
)

What happens when this task runs?

medium

A. Airflow executes dbt models transforming data in Snowflake

B. Airflow creates a new Snowflake warehouse

C. dbt schedules the Airflow DAG

D. Airflow deletes old Snowflake tables

Solution

1. Step 1: Analyze the BashOperator command

   The command dbt run triggers dbt to execute SQL models and transform data in Snowflake.

2. Step 2: Understand Airflow's role

   Airflow runs this command as a task; it does not create warehouses or delete tables itself.

3. Final Answer:

   Airflow executes dbt models transforming data in Snowflake -> Option A

4. Quick Check:

   dbt run = data transformation task [OK]

Hint: dbt run transforms data; Airflow just runs commands [OK]

Common Mistakes:

• Thinking Airflow creates or deletes Snowflake resources
• Confusing dbt scheduling with Airflow scheduling
• Assuming dbt controls Airflow DAGs

4. You configured Airflow to run dbt commands but the tasks fail with authentication errors. What is the most likely cause?

medium

A. Airflow DAG file missing task_id

B. Snowflake warehouse is paused

C. dbt models have syntax errors

D. Incorrect Snowflake credentials in dbt's profiles.yml

Solution

1. Step 1: Identify cause of authentication errors

   Authentication errors usually mean wrong or missing credentials in profiles.yml used by dbt.

2. Step 2: Exclude other causes

   Missing task_id causes Airflow errors but not authentication errors; syntax errors cause runtime failures; paused warehouse causes query delays, not auth errors.

3. Final Answer:

   Incorrect Snowflake credentials in dbt's profiles.yml -> Option D

4. Quick Check:

   Auth error = wrong credentials [OK]

Hint: Check profiles.yml credentials first on auth errors [OK]

Common Mistakes:

• Blaming Airflow config instead of dbt credentials
• Assuming syntax errors cause authentication failures
• Ignoring warehouse state impact

5. You want to automate a data pipeline that runs dbt transformations daily and sends email alerts on failure using Airflow. Which setup best achieves this?

hard

A. Schedule dbt runs in Snowflake and use Airflow only for monitoring

B. Create an Airflow DAG with dbt BashOperator tasks and EmailOperator for alerts

C. Use only dbt Cloud scheduler with no Airflow integration

D. Manually run dbt commands and check logs for errors

Solution

1. Step 1: Identify automation components

   Airflow DAG can schedule dbt commands using BashOperator and send emails on failure with EmailOperator.

2. Step 2: Evaluate other options

   Scheduling dbt in Snowflake alone lacks alerting; dbt Cloud scheduler excludes Airflow's flexibility; manual runs lack automation.

3. Final Answer:

   Create an Airflow DAG with dbt BashOperator tasks and EmailOperator for alerts -> Option B

4. Quick Check:

   Airflow DAG + dbt + email = automation [OK]

Hint: Use Airflow DAG with dbt tasks and email alerts for automation [OK]

Common Mistakes:

• Ignoring Airflow's alerting capabilities
• Relying solely on manual or Snowflake scheduling
• Confusing dbt Cloud scheduler with Airflow