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.