Getting Started DBT with Apache Airflow: A Modern Workflow Orchestration Tool

Introduction: The Dynamic Duo — dbt and Apache Airflow

In the modern data stack, dbt (data build tool) has revolutionized how data teams transform data in their warehouses. It empowers analytics engineers to build modular, tested, and documented data models using SQL, applying software engineering best practices directly to data transformation. On the other hand, Apache Airflow stands as the king of workflow orchestration, scheduling, and monitoring complex data pipelines.

While dbt excels at what to transform and how, Airflow excels at when and where those transformations should run. Combining these two powerful tools, especially within a managed environment like AWS Managed Workflows for Apache Airflow (MWAA), creates a robust, scalable, and efficient data transformation pipeline.

This blog post will explore the synergy between dbt and Airflow, and how AWS MWAA provides the perfect platform to orchestrate your dbt models, streamlining your analytics engineering workflows.

Why Combine dbt and Airflow?

Separately, dbt and Airflow are formidable. Together, they unlock new levels of efficiency and reliability:

1. Orchestration and Scheduling: Airflow’s primary strength is scheduling. It ensures your dbt models run exactly when needed, after upstream data sources are ready, and before downstream consumers require the transformed data.
2. Dependency Management: Airflow visually represents and enforces dependencies between your dbt runs and other tasks (e.g., data ingestion). You can ensure your dbt run only kicks off after your ETL process has landed raw data.
3. Monitoring and Alerting: Airflow’s UI provides a centralized view of your dbt job status, allowing you to easily monitor runs, view logs, and configure alerts for failures.
4. Error Handling and Retries: Airflow’s robust retry mechanisms and error handling capabilities ensure your dbt transformations are resilient to transient issues.
5. Data Lineage (Extended): While dbt provides excellent intra-model lineage, Airflow extends this to the entire pipeline, showing how dbt transformations fit into the broader data flow from source to dashboard.
6. Resource Management: Airflow can manage the computational resources for dbt runs, especially when leveraging KubernetesPodOperator or similar for isolated execution.

Why MWAA for dbt Workloads?

Leveraging MWAA to orchestrate dbt models offers significant advantages over self-hosting Airflow:

• Fully Managed: AWS handles the underlying infrastructure, scaling, patching, and backups, allowing you to focus on developing dbt models and Airflow DAGs.
• Scalability: MWAA automatically scales Airflow workers, ensuring your dbt runs have the compute capacity they need without manual intervention.
• High Availability: Your Airflow environment is distributed across multiple Availability Zones, providing resilience and minimizing downtime.
• Seamless AWS Integration: MWAA lives within your AWS VPC, offering secure, private network access to your data warehouse (e.g., Redshift, Snowflake, Databricks SQL endpoint), S3, Secrets Manager (for dbt credentials), and CloudWatch (for logs and metrics).
• Security: Benefit from AWS IAM for fine-grained access control to your Airflow UI and integration with Secrets Manager for managing sensitive dbt credentials.
• Cost-Effective: Pay only for what you use, avoiding the upfront costs and ongoing operational overhead of managing your own Airflow infrastructure.

How to Integrate dbt with MWAA

Integrating your dbt project with MWAA involves a few key steps:

1. Structure Your dbt Project for Airflow

Ensure your dbt project (dbt_project.yml, models/, macros/, analyses/, tests/, seeds/) is self-contained. You will typically upload the entire dbt project directory to your MWAA S3 bucket.

2. Manage Python Dependencies

Your Airflow environment needs dbt-core and the specific database adapter (e.g., dbt-redshift, dbt-snowflake). You specify these in a requirements.txt file placed in the root of your MWAA S3 bucket.

Example requirements.txt:

dbt-core==1.x.x
dbt-redshift==1.x.x # Or dbt-snowflake, dbt-bigquery etc.

3. Define dbt Profiles and Connections

dbt uses profiles.yml to connect to your data warehouse. You should NEVER hardcode credentials in this file, especially not when deploying to production. Instead, leverage AWS Secrets Manager and Airflow Connections.

You can configure an Airflow connection (e.g., my_dwh_connection) in the Airflow UI or via environment variables in MWAA. Your profiles.yml can then reference these details, often by reading environment variables:

Example profiles.yml (within your dbt project):

mwaa_dbt_profile:
  target: dev
  outputs:
    dev:
      type: redshift # or snowflake, bigquery, etc.
      host: "{{ env_var('DBT_HOST') }}"
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5439
      database: "{{ env_var('DBT_DATABASE') }}"
      schema: "{{ env_var('DBT_SCHEMA') }}"
      # Add other specific configurations like role, warehouse for Snowflake etc.

Then, in your MWAA environment, you can set these DBT_HOST, DBT_USER, etc., as environment variables, or retrieve them from AWS Secrets Manager using a custom Airflow plugin/hook or by having the Airflow task fetch them directly. The most common way for BashOperator to work is to set these as MWAA environment variables.

4. Create Your Airflow DAG

Your DAG will use operators to execute dbt commands. The BashOperator is often the simplest and most common way to run dbt commands within Airflow.

Key considerations for the DAG:

• Working Directory: Your Bash commands need to navigate to your dbt project directory.
• dbt Commands: dbt deps, dbt seed, dbt run, dbt test, dbt docs generate, dbt snapshot are the most frequent commands.
• Virtual Environment: MWAA provides a Python environment. Ensure your dbt commands run within the context where dbt is installed.

Example MWAA dbt Orchestration DAG

Here’s a simple DAG that demonstrates running dbt deps, dbt seed, dbt run, and dbt test for a dbt project located in your MWAA S3 bucket's dags/dbt_project/ subfolder.

# Import necessary Airflow modules
from airflow import DAG
from airflow.operators.bash import BashOperator
from airflow.utils.dates import days_ago

# Define default arguments for the DAG
default_args = {
    'owner': 'airflow',
    'start_date': days_ago(1),
    'depends_on_past': False,
    'email_on_failure': False,
    'email_on_retry': False,
    'retries': 1,
}

# Define the path to your dbt project within the S3 DAGs folder
# Assuming your dbt project is uploaded to s3://your-mwaa-bucket/dags/dbt_project/
DBT_PROJECT_DIR = '/usr/local/airflow/dags/dbt_project' # MWAA's internal path to your dbt project

with DAG(
    dag_id='mwaa_dbt_orchestration_example',
    default_args=default_args,
    description='Orchestrates dbt models using BashOperator on MWAA.',
    schedule_interval='@daily', # Runs once a day
    tags=['dbt', 'mwaa', 'data-transformation'],
    catchup=False,
) as dag:
    # Task 1: Check dbt version (optional, for debugging)
    check_dbt_version = BashOperator(
        task_id='check_dbt_version',
        bash_command=f'cd {DBT_PROJECT_DIR} && dbt --version',
    )

    # Task 2: Install dbt dependencies
    # This ensures any new packages in packages.yml are installed
    dbt_deps = BashOperator(
        task_id='dbt_dependencies',
        bash_command=f'cd {DBT_PROJECT_DIR} && dbt deps',
    )

    # Task 3: Load dbt seeds (if you have them)
    # Seeds are CSV files loaded directly into your data warehouse
    dbt_seed = BashOperator(
        task_id='dbt_seed',
        bash_command=f'cd {DBT_PROJECT_DIR} && dbt seed --profiles-dir .',
    )

    # Task 4: Run dbt models
    # This is the core transformation step
    dbt_run = BashOperator(
        task_id='dbt_run',
        bash_command=f'cd {DBT_PROJECT_DIR} && dbt run --profiles-dir .',
    )

    # Task 5: Test dbt models
    # It's crucial to test your data after transformation
    dbt_test = BashOperator(
        task_id='dbt_test',
        bash_command=f'cd {DBT_PROJECT_DIR} && dbt test --profiles-dir .',
    )

    # Define task dependencies
    check_dbt_version >> dbt_deps >> dbt_seed >> dbt_run >> dbt_test

5. Upload to S3

1. Create a folder named dbt_project (or any name you prefer) within your MWAA S3 DAGs folder.
2. Upload your entire dbt project (excluding dbt_modules, logs, target) into this dbt_project folder.
3. Upload the above Airflow DAG (e.g., mwaa_dbt_dag.py) directly into your MWAA S3 DAGs folder (e.g., s3://your-mwaa-bucket/dags/).
4. Ensure your requirements.txt (with dbt-core and your adapter) is in the root of your MWAA S3 bucket (e.g., s3://your-mwaa-bucket/requirements.txt).
5. Set up the necessary environment variables in your MWAA environment for dbt to connect to your data warehouse (e.g., DBT_HOST, DBT_USER, DBT_PASSWORD, DBT_DATABASE, DBT_SCHEMA).

Best Practices for dbt on MWAA

• Version Control: Always keep your dbt project and Airflow DAGs under version control (e.g., Git). Use CI/CD pipelines to deploy changes to S3.
• Modular DAGs: For very large dbt projects, consider breaking your dbt run into multiple Airflow tasks (e.g., dbt run --select tag:daily_models, dbt run --select tag:weekly_models) to allow for more granular retries and monitoring.
• Environment Variables & Secrets: Use AWS Secrets Manager in conjunction with MWAA environment variables to securely manage all dbt credentials. Avoid hardcoding.
• Logging and Monitoring: Leverage Amazon CloudWatch for detailed logs from your dbt runs and Airflow tasks. Set up CloudWatch alarms for dbt task failures.
• Resource Allocation: Choose an appropriate MWAA environment class (mw1.small, mw1.medium, etc.) based on the expected workload and complexity of your dbt runs. Monitor resource utilization.
• Testing: Implement comprehensive dbt tests (schema.yml) and ensure dbt test is an integral part of your Airflow DAG to catch data quality issues early.

Conclusion

The combination of dbt for data transformation and Apache Airflow for orchestration is a powerful paradigm in the modern data landscape. When hosted on AWS Managed Workflows for Apache Airflow (MWAA), this duo provides a highly available, scalable, and secure platform to automate your data modeling workflows.

By following the steps outlined in this guide, you can confidently deploy and manage your dbt projects on MWAA, empowering your analytics engineering team to deliver high-quality, trusted data at scale. Embrace this synergy to build more efficient, resilient, and observable data pipelines in the cloud.

Happy dbt-ing and Orchestrating!