Integration with dbt and Airflow in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Airflow DAG Scheduling with dbt Tasks

You have an Airflow DAG that runs dbt models on Snowflake every day at midnight. The DAG has three tasks: extract, transform, and load. The transform task runs dbt models using the dbt run command. What will happen if the extract task fails?

Snowflake

dag = DAG('daily_dbt', schedule_interval='0 0 * * *')
extract = BashOperator(task_id='extract', bash_command='python extract.py', dag=dag)
transform = BashOperator(task_id='transform', bash_command='dbt run --profiles-dir ./profiles', dag=dag)
load = BashOperator(task_id='load', bash_command='python load.py', dag=dag)
extract >> transform >> load

A All tasks will run in parallel ignoring dependencies.

B The transform task will not run because Airflow stops the DAG on task failure.

C The load task will run but the transform task will be skipped.

D The transform task will run regardless of the extract task status.

💡 Hint

Think about how Airflow handles task dependencies and failures.

In Airflow, if a task fails, downstream tasks that depend on it will not run. Since transform depends on extract, it will not run if extract fails.

❓ Architecture

intermediate

Designing a Reliable dbt and Airflow Integration on Snowflake

You want to build a data pipeline using Airflow to orchestrate dbt models on Snowflake. Which architecture ensures that dbt models only run after the raw data is fully loaded into Snowflake?

A Use Airflow to run dbt models and load data in parallel to save time.

B Schedule dbt models to run on a fixed time daily without checking data load status.

C Create an Airflow DAG where the load task runs first, then the transform task runs dbt models, with explicit task dependencies.

D Run dbt models manually after loading data into Snowflake.

💡 Hint

Think about how to enforce order in task execution.

Setting explicit task dependencies in Airflow ensures that dbt models run only after the data load task completes successfully.

❓ security

advanced

Securing Credentials for dbt and Airflow Integration

You need to securely manage Snowflake credentials used by dbt in an Airflow environment. Which approach follows best security practices?

A Store Snowflake credentials in Airflow Variables encrypted with a key and reference them in dbt profiles.

B Hardcode Snowflake credentials directly in the dbt profiles.yml file checked into Git.

C Pass Snowflake credentials as plain text environment variables in Airflow tasks.

D Use the same Snowflake user credentials for all Airflow and dbt tasks without rotation.

💡 Hint

Consider encryption and avoiding hardcoding secrets.

Storing credentials encrypted in Airflow Variables and referencing them securely in dbt profiles avoids exposing secrets in code or logs.

❓ Configuration

advanced

Configuring dbt Profiles for Airflow Execution on Snowflake

You want to run dbt models from Airflow using the dbt run command. Which profiles.yml configuration snippet correctly sets up a Snowflake connection for Airflow?

Snowflake

profiles.yml snippet:

A

my_profile:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: 'xy12345'
      user: '{{ var.value.snowflake_user }}'
      password: '{{ var.value.snowflake_password }}'
      role: 'ANALYST'
      database: 'MY_DB'
      warehouse: 'MY_WH'
      schema: 'PUBLIC'
      threads: 1
      client_session_keep_alive: false

B

my_profile:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: 'xy12345'
      user: 'airflow'
      password: 'password123'
      role: 'ANALYST'
      database: 'MY_DB'
      warehouse: 'MY_WH'
      schema: 'PUBLIC'
      threads: 4
      client_session_keep_alive: true

C

my_profile:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: 'xy12345'
      user: 'dbt_user'
      password: '{{ var.value.snowflake_password }}'
      role: 'ANALYST'
      database: 'MY_DB'
      warehouse: 'MY_WH'
      schema: 'PUBLIC'
      threads: 1
      client_session_keep_alive: true

D

my_profile:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: 'xy12345'
      user: '{{ env_var("SNOWFLAKE_USER") }}'
      password: '{{ env_var("SNOWFLAKE_PASSWORD") }}'
      role: 'ANALYST'
      database: 'MY_DB'
      warehouse: 'MY_WH'
      schema: 'PUBLIC'
      threads: 2
      client_session_keep_alive: false

💡 Hint

Consider how Airflow passes environment variables securely to tasks.

Using env_var in dbt profiles allows Airflow to inject credentials securely as environment variables, which is a best practice.

✅ Best Practice

expert

Optimizing Airflow and dbt Pipeline for Snowflake Cost Efficiency

You want to optimize your Airflow orchestrated dbt pipeline on Snowflake to reduce compute costs without sacrificing data freshness. Which strategy is best?

A Use Snowflake warehouses with auto-suspend enabled and set Airflow schedules to run only when new data arrives using sensors.

B Keep Snowflake warehouses running 24/7 to avoid startup delays and schedule dbt runs every 15 minutes.

C Run all dbt models daily regardless of data changes to ensure freshness.

D Use the largest Snowflake warehouse size to speed up dbt runs and reduce runtime.

💡 Hint

Think about balancing cost and data freshness with scheduling and warehouse settings.

Auto-suspend warehouses save costs by shutting down when idle. Using Airflow sensors to trigger runs only on new data avoids unnecessary compute usage.

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