Integration with dbt and Airflow in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to specify the Airflow operator that runs a dbt command.

Snowflake

dbt_task = [1](task_id='run_dbt', bash_command='run')

Drag options to blanks, or click blank then click option'

ASnowflakeOperator

BPythonOperator

CDummyOperator

DBashOperator

Attempts:

3 left

💡 Hint

Common Mistakes

Using PythonOperator instead of BashOperator for shell commands.

Confusing DummyOperator as it does not run commands.

The BashOperator in Airflow is used to run shell commands like dbt CLI commands.

2fill in blank

medium

Complete the code to set the Snowflake connection ID in Airflow for dbt.

Snowflake

snowflake_conn_id = '[1]'

Drag options to blanks, or click blank then click option'

Asnowflake_default

Bdbt_snowflake_conn

Cairflow_snowflake

Dsnowflake_conn

Attempts:

3 left

💡 Hint

Common Mistakes

Using a connection ID that does not exist in Airflow connections.

Confusing the connection ID with dbt profile names.

The default Snowflake connection ID in Airflow is usually snowflake_default, which dbt can use.

3fill in blank

hard

Fix the error in the Airflow DAG code to correctly trigger a dbt run command.

Snowflake

dbt_run = BashOperator(task_id='dbt_run', bash_command='dbt [1]')

Drag options to blanks, or click blank then click option'

Arun

Bbuild

Ctest

Dcompile

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'build' which is not a dbt CLI command.

Using 'test' which runs tests but does not build models.

The dbt run command executes models and is the correct command to trigger a dbt run in Airflow.

4fill in blank

hard

Fill both blanks to define a dbt profile for Snowflake with correct keys.

Snowflake

"""
profiles:
  my_profile:
    target: dev
    outputs:
      dev:
        type: snowflake
        account: [1]
        user: [2]
"""

Drag options to blanks, or click blank then click option'

Aaccount_name

Busername

Cmy_account

Dmy_user

Attempts:

3 left

💡 Hint

Common Mistakes

Using generic keys like 'account_name' instead of actual account identifier.

Confusing keys with values.

The account key should be set to your Snowflake account identifier (e.g., my_account), and user should be your Snowflake username (e.g., my_user).

5fill in blank

hard

Fill in the blank to complete the Airflow DAG task dependencies for dbt run and test.

Snowflake

with DAG('dbt_dag', start_date=days_ago(1)) as dag:
    dbt_run = BashOperator(task_id='dbt_run', bash_command='dbt run')
    dbt_test = BashOperator(task_id='dbt_test', bash_command='dbt test')
    [1]

Drag options to blanks, or click blank then click option'

A>>

Bset_downstream

Cdbt_run >> dbt_test

Ddbt_test

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong operators like << or missing dependency lines.

Not placing the dependency line inside the DAG context.

In Airflow, you can set task dependencies using the bitshift operator >> or the method set_downstream. The line dbt_run >> dbt_test sets dbt_test to run after dbt_run.

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