Bird
Raised Fist0
Snowflakecloud~5 mins

Integration with dbt and Airflow in Snowflake - Commands & Configuration

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction
When you want to automate data transformations and workflows, you can connect dbt with Airflow. This helps run your data models in Snowflake on a schedule without manual steps.
When you want to run dbt models automatically every day without manual commands.
When you need to orchestrate multiple data tasks in order, like loading data then transforming it.
When you want to monitor and retry failed data jobs easily.
When you want to keep your data warehouse in Snowflake updated with fresh transformed data.
When you want to combine SQL-based transformations (dbt) with workflow automation (Airflow).
Config File - dbt_airflow_dag.py
dbt_airflow_dag.py
from airflow import DAG
from airflow.operators.bash import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'airflow',
    'depends_on_past': False,
    'start_date': datetime(2024, 1, 1),
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

dag = DAG(
    'dbt_snowflake_workflow',
    default_args=default_args,
    description='Run dbt models in Snowflake using Airflow',
    schedule_interval='0 2 * * *',  # daily at 2 AM
    catchup=False,
)

run_dbt = BashOperator(
    task_id='run_dbt_models',
    bash_command='cd /opt/dbt_project && dbt run --profiles-dir .',
    dag=dag,
)

run_dbt

This Airflow DAG file defines a workflow to run dbt models daily at 2 AM.

default_args sets retry and start date settings.

schedule_interval controls when the DAG runs.

BashOperator runs the dbt command in the project directory.

Commands
Check that Airflow recognizes the new DAG file and lists it.
Terminal
airflow dags list
Expected OutputExpected
dbt_snowflake_workflow example_bash_operator example_branch_dop_operator example_subdag_operator
Manually start the dbt workflow DAG to test it runs correctly.
Terminal
airflow dags trigger dbt_snowflake_workflow
Expected OutputExpected
Created <DagRun dbt_snowflake_workflow @ 2024-06-01 10:00:00: manual__2024-06-01T10:00:00+00:00, externally triggered: True>
List all tasks in the dbt workflow DAG to verify the task names.
Terminal
airflow tasks list dbt_snowflake_workflow
Expected OutputExpected
run_dbt_models
Run the dbt task manually for the given date to check it executes without errors.
Terminal
airflow tasks test dbt_snowflake_workflow run_dbt_models 2024-06-01
Expected OutputExpected
[2024-06-01 10:05:00,000] {bash.py:123} INFO - Running command: cd /opt/dbt_project && dbt run --profiles-dir . Running with dbt=1.5.0 Found 5 models, 0 tests, 0 snapshots, 0 analyses, 123 macros, 0 operations, 0 seed files, 0 sources 17:05:00 | Concurrency: 1 threads (target='dev') 17:05:00 | 17:05:00 | 1 of 5 START table model my_model................................ [RUN] 17:05:05 | 1 of 5 OK created table model my_model............................ [OK in 5.0s] Completed successfully Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5
Key Concept

If you remember nothing else from this pattern, remember: Airflow runs dbt commands on schedule to automate Snowflake data transformations.

Common Mistakes
Not setting the correct working directory in the BashOperator bash_command.
dbt commands fail if run outside the project folder because they can't find the dbt project files.
Use 'cd /opt/dbt_project && dbt run --profiles-dir .' to ensure the command runs in the right folder.
Forgetting to set 'catchup=False' in the DAG schedule.
Airflow tries to run all past missed schedules, causing many unwanted runs.
Set 'catchup=False' to run only from the current schedule forward.
Not verifying the DAG is loaded with 'airflow dags list' before triggering.
Triggering a DAG that Airflow does not recognize will fail silently or cause confusion.
Always check the DAG list to confirm your DAG is active.
Summary
Create an Airflow DAG file that runs dbt commands in the correct project directory.
Use Airflow CLI commands to list, trigger, and test the DAG and its tasks.
Set proper DAG arguments like schedule and catchup to control execution timing.

Practice

(1/5)
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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard