Integration with dbt and Airflow in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is dbt and how does it help with Snowflake?

dbt (data build tool) helps transform raw data in Snowflake by writing simple SQL models. It organizes and tests data transformations, making data easier to manage and understand.

beginner

What role does Airflow play when working with dbt and Snowflake?

Airflow schedules and runs dbt jobs automatically. It helps manage workflows by running tasks in order, like refreshing data models in Snowflake at set times.

intermediate

How do you connect dbt to Snowflake?

You configure dbt with Snowflake credentials like account, user, password, warehouse, database, and schema in the dbt profile file. This lets dbt run SQL commands directly on Snowflake.

intermediate

What is a common way to run dbt models in Airflow?

Use Airflow's BashOperator or a custom operator to run dbt commands like 'dbt run' or 'dbt test'. This runs dbt models as part of an automated workflow.

intermediate

Why is it important to test dbt models in an Airflow workflow?

Testing ensures data quality and catches errors early. Running tests in Airflow helps keep data reliable by automatically checking models after they run.

What does dbt primarily do with Snowflake data?

A Visualizes data

B Transforms raw data using SQL models

C Schedules workflows

D Stores data in tables

dbt transforms raw data by running SQL models inside Snowflake.

Which tool is best for scheduling dbt jobs automatically?

A Airflow

B Snowflake UI

C Excel

D GitHub

Airflow is designed to schedule and manage workflows like dbt jobs.

What information is NOT needed to connect dbt to Snowflake?

A Snowflake account name

B User credentials

C Warehouse name

D Airflow DAG ID

Airflow DAG ID is unrelated to dbt's Snowflake connection.

How can Airflow run dbt commands?

A By writing SQL in Airflow

B Directly inside Snowflake UI

C Using BashOperator to run shell commands

D Using Excel macros

Airflow's BashOperator can run shell commands like 'dbt run'.

Why include dbt tests in Airflow workflows?

A To improve data quality automatically

B To slow down the workflow

C To delete old data

D To create dashboards

Running tests helps catch data issues early and keeps data reliable.

Explain how dbt and Airflow work together to manage data in Snowflake.

Think about how each tool's role fits in a data pipeline.

Describe the steps to set up a workflow that runs dbt models on Snowflake using Airflow.

Consider configuration, automation, and testing.

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