Orchestrating dbt with Airflow - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this Airflow DAG snippet?

Given this Airflow DAG code snippet that runs a dbt command, what will be the output in the Airflow logs when the task runs successfully?

dbt

from airflow import DAG
from airflow.operators.bash import BashOperator
from datetime import datetime

with DAG('dbt_run_dag', start_date=datetime(2024, 1, 1), schedule_interval='@daily') as dag:
    run_dbt = BashOperator(
        task_id='run_dbt_models',
        bash_command='dbt run --profiles-dir /usr/local/dbt_profiles'
    )

A Logs show dbt command not found error

B Logs show a Python syntax error due to missing import

C Logs show Airflow task failure due to missing DAG start_date

D Logs show dbt running models and completing with 'Completed successfully' message

💡 Hint

Check if the DAG has a valid start_date and the bash command is correct.

The DAG is correctly defined with a start_date and the BashOperator runs the dbt command. If dbt is installed and profiles directory is correct, logs will show dbt running models successfully.

❓ data_output

intermediate

What is the value of the Airflow task instance state after dbt run succeeds?

If an Airflow task runs a dbt command using BashOperator and the dbt run finishes without errors, what will be the task instance state in Airflow's metadata database?

A failed

B success

C up_for_retry

D skipped

💡 Hint

Think about what Airflow marks a task when the command it runs exits with code 0.

Airflow marks a task as 'success' when the command it runs completes without errors (exit code 0).

🔧 Debug

advanced

Why does this Airflow DAG fail to run dbt models?

This Airflow DAG fails with an error: 'bash: dbt: command not found'. What is the most likely cause?

dbt

from airflow import DAG
from airflow.operators.bash import BashOperator
from datetime import datetime

with DAG('dbt_fail_dag', start_date=datetime(2024, 1, 1), schedule_interval='@daily') as dag:
    run_dbt = BashOperator(
        task_id='run_dbt',
        bash_command='dbt run'
    )

A dbt is not installed or not in the PATH of the Airflow worker environment

B The DAG is missing a schedule_interval

C The start_date is in the past

D The task_id is invalid

💡 Hint

Check the environment where Airflow runs the bash command.

The error 'command not found' means the dbt executable is not available in the PATH of the Airflow worker running the task.

🧠 Conceptual

advanced

What is the best way to pass dynamic variables to dbt models in Airflow?

You want to run dbt models in Airflow and pass a variable that changes daily, such as a date. Which approach is best to pass this variable to dbt?

A Use the --vars flag with a JSON string in the bash_command, e.g., dbt run --vars '{"date": "2024-01-01"}'

B Set environment variables in Airflow and access them directly in dbt without passing --vars

C Modify the dbt profiles.yml file daily to include the variable

D Hardcode the variable inside the dbt model SQL files

💡 Hint

Think about how dbt accepts variables at runtime.

dbt accepts variables via the --vars flag as a JSON string, which allows passing dynamic values from Airflow bash commands.

🚀 Application

expert

How to orchestrate dbt model runs with dependencies in Airflow?

You have two dbt models: model_a and model_b. model_b depends on model_a. You want to run them in Airflow ensuring model_b runs only after model_a completes successfully. How do you set this up?

A Use Airflow's PythonOperator to run dbt models in parallel without dependencies

B Run both models in a single dbt run command in one BashOperator task

C Create two BashOperator tasks in Airflow, one for each model, and set run_model_b.set_upstream(run_model_a)

D Create one BashOperator task that runs model_b only, since dbt handles dependencies internally

💡 Hint

Consider how Airflow manages task dependencies explicitly.

Airflow manages task dependencies via task relationships. Creating separate tasks and setting dependencies ensures order.

Practice

1. What is the main purpose of using Airflow with dbt in data workflows?

easy

A. To automate and schedule dbt commands in a specific order

B. To replace dbt's SQL models with Python scripts

C. To visualize dbt models as charts

D. To manually run dbt commands from the command line

Solution

1. Step 1: Understand Airflow's role

   Airflow is a tool to automate and schedule tasks, including dbt commands.

2. Step 2: Connect Airflow with dbt

   Using Airflow, you can run dbt commands automatically and in order without manual intervention.

3. Final Answer:

   To automate and schedule dbt commands in a specific order -> Option A

4. Quick Check:

   Airflow automates dbt runs = C [OK]

Hint: Airflow schedules tasks; dbt runs models. Together automate workflows [OK]

Common Mistakes:

• Thinking Airflow replaces dbt's SQL modeling
• Confusing manual and automated runs
• Assuming Airflow visualizes dbt models

2. Which Airflow operator is commonly used to run dbt commands inside a DAG?

easy

A. BashOperator

B. PythonOperator

C. HttpOperator

D. EmailOperator

Solution

1. Step 1: Identify how dbt commands run

   dbt commands run in the shell or terminal, so Airflow needs an operator that runs shell commands.

2. Step 2: Match operator to shell commands

   BashOperator runs bash shell commands, perfect for running dbt CLI commands inside Airflow.

3. Final Answer:

   BashOperator -> Option A

4. Quick Check:

   BashOperator runs shell commands = A [OK]

Hint: Use BashOperator for shell commands like dbt runs [OK]

Common Mistakes:

• Choosing PythonOperator which runs Python code, not shell commands
• Confusing HttpOperator with web requests
• Thinking EmailOperator runs commands

3. Given this Airflow DAG snippet, what is the order of task execution?

dbt_run = BashOperator(task_id='dbt_run', bash_command='dbt run')
dbt_test = BashOperator(task_id='dbt_test', bash_command='dbt test')
dbt_run >> dbt_test

medium

A. dbt_run and dbt_test run in parallel

B. dbt_run runs first, then dbt_test

C. dbt_test runs first, then dbt_run

D. Only dbt_test runs

Solution

1. Step 1: Understand the dependency operator

   The '>>' operator in Airflow means the left task runs before the right task.

2. Step 2: Apply to given tasks

   dbt_run >> dbt_test means dbt_run runs first, then dbt_test runs after it finishes.

3. Final Answer:

   dbt_run runs first, then dbt_test -> Option B

4. Quick Check:

   dbt_run >> dbt_test means run then test = A [OK]

Hint: >> means left task runs before right task [OK]

Common Mistakes:

• Thinking >> means parallel execution
• Reversing the order of tasks
• Assuming only one task runs

4. Identify the error in this Airflow task setup for running dbt tests:

dbt_test = BashOperator(task_id='dbt_test', bash_command='dbt test')
dbt_test.set_upstream('dbt_run')

medium

A. bash_command should be a list, not a string

B. task_id cannot be 'dbt_test'

C. set_upstream expects a task object, not a string

D. BashOperator cannot run dbt commands

Solution

1. Step 1: Check set_upstream usage

   set_upstream expects another task object, not a string task id.

2. Step 2: Identify correct usage

   It should be dbt_test.set_upstream(dbt_run) where dbt_run is the task object, not 'dbt_run' string.

3. Final Answer:

   set_upstream expects a task object, not a string -> Option C

4. Quick Check:

   set_upstream needs task object = B [OK]

Hint: set_upstream needs task object, not string name [OK]

Common Mistakes:

• Passing string instead of task object to set_upstream
• Thinking bash_command must be a list
• Believing task_id has naming restrictions
• Assuming BashOperator can't run dbt

5. You want to create an Airflow DAG that runs dbt run, then dbt test, and finally sends an email notification only if tests pass. Which sequence correctly sets task dependencies?

hard

A. dbt_run >> email_notify >> dbt_test

B. email_notify >> dbt_run >> dbt_test

C. dbt_test >> dbt_run >> email_notify

D. dbt_run >> dbt_test >> email_notify

Solution

1. Step 1: Define correct task order

   First, run models with dbt run, then test them with dbt test, and finally notify if tests pass.

2. Step 2: Set dependencies accordingly

   Use '>>' to chain tasks in order: dbt_run >> dbt_test >> email_notify.

3. Final Answer:

   dbt_run >> dbt_test >> email_notify -> Option D

4. Quick Check:

   Run then test then notify = D [OK]

Hint: Chain tasks in order with >>: run, test, then notify [OK]

Common Mistakes:

• Placing email notification before tests
• Reversing run and test order
• Skipping dependencies causing parallel runs