Airflow with DBT tutorial - The best way!

Summary

In this video, Mokamati, head of customer education at Astronomer, demonstrates how to integrate DBT (Data Build Tool) with Airflow using Cosmos, offering an optimized and user-friendly approach. The video covers DBT's role in transforming raw data through SQL statements and highlights integration challenges using the Bash operator. Mokamati introduces Cosmos as the best solution, explaining how it parses and renders workflows from DBT into Airflow tasks, providing full observability and simplifying task management. The tutorial also walks through setting up Airflow locally using the Astro CLI, ensuring an efficient, error-free DBT-Airflow integration.

Takeaways

• 😀 DBT (Data Build Tool) simplifies data transformation by allowing SQL-based workflows that can be turned into tables and views.
• 😀 DBT is popular due to its support for multiple databases, ease of defining dependencies between SQL models, and built-in documentation and data quality checks.
• 😀 Traditional methods for integrating DBT with Airflow, like using the Bash operator, are limited and can be costly and difficult to debug.
• 😀 DBT Cloud provides some improvements with specialized operators for Airflow, but still lacks complete integration and observability.
• 😀 Cosmos offers the best integration method by parsing DBT projects and rendering them as Airflow DAGs, task groups, or individual tasks.
• 😀 With Cosmos, updates to DBT projects automatically sync without needing to restart Airflow, improving workflow management.
• 😀 Cosmos provides full observability of DBT workflows directly within Airflow, removing the need to switch between DBT and Airflow for debugging.
• 😀 Airflow DAGs generated by Cosmos allow easy management of DBT dependencies and give better control over execution.
• 😀 The tutorial demonstrates how to set up Cosmos with the Astro CLI to integrate DBT with Airflow locally using a sample DBT project (Jaffle Shop).
• 😀 The process involves creating a development environment, cloning the DBT project, configuring dependencies, and building custom macros and tasks for Airflow.
• 😀 After configuring the integration, you can trigger and monitor DBT models and tasks within Airflow, with real-time data updates and task dependencies clearly visible.

Q & A

What is DBT, and how does it simplify data transformation?

DBT (Data Build Tool) simplifies data transformation by allowing data analysts and engineers to write SQL statements, which are then transformed into tables and views. It supports multiple databases like Postgres, Redshift, and BigQuery, and lets users create dependencies between SQL models using Jinja templating, improving data workflow management.

Why is using the Bash operator for DBT integration with Airflow not recommended?

The Bash operator is not ideal because it runs the entire DBT project in a single task. This means if the task fails, the entire project needs to be rerun, which can be costly and inefficient. Additionally, it provides minimal observability, requiring users to switch between DBT and Airflow to debug tasks, which complicates the process.

What is DBT Cloud's integration with Airflow like, and how does it compare to using Cosmos?

DBT Cloud provides a better integration with Airflow than the Bash operator by offering DBT Cloud-specific operators like `DBTCloudRunJobOperator`, `DBTCloudHook`, and `DBTCloudJobRunSensor`. However, it still requires users to switch between DBT Cloud and Airflow for debugging, and it works only with DBT Cloud, making it less flexible than Cosmos, which provides more comprehensive integration.

How does Cosmos improve the integration of DBT with Airflow?

Cosmos improves DBT and Airflow integration by parsing DBT workflows and converting them into Airflow DAGs, task groups, or individual tasks. It allows for better observability, so users can manage and debug DBT tasks directly within Airflow, eliminating the need to switch between different interfaces. It also supports real-time updates, meaning no need to restart Airflow when the DBT project is updated.

What are the two key components of Cosmos, and what do they do?

Cosmos has two main components: Parsers and Operators. Parsers are responsible for converting DBT workflows into Airflow DAGs, task groups, or individual tasks. Operators define the behavior of these tasks, such as running DBT commands like `DBT run`, `DBT test`, and `DBT seed`, helping to automate the execution of DBT models within Airflow.

What are the benefits of using Cosmos over other DBT integration methods?

The main benefits of using Cosmos are enhanced observability, easy management of DBT workflows, and seamless integration of DBT models with Airflow. It allows for the rendering of DBT projects as DAGs, enables real-time updates, and provides a more intuitive debugging experience directly within Airflow, compared to methods like Bash or DBT Cloud integration.

How does Cosmos handle DBT connections, and why is it advantageous?

Cosmos handles DBT connections within Airflow itself, meaning you can manage all connections (e.g., Postgres) directly in the Airflow UI, rather than in the DBT project's `profiles.yml` file. This makes connection management simpler and more centralized, avoiding potential conflicts between DBT and Airflow configurations.

What is the role of the Astro CLI in setting up Airflow with DBT and Cosmos?

The Astro CLI is used to quickly set up and run Airflow locally, making it easier to get started with Cosmos and DBT integration. It simplifies the setup process by generating necessary files, installing dependencies, and creating a local development environment for testing and running Airflow workflows.

How do you set up the environment to run Airflow with DBT and Cosmos using Astro CLI?

To set up the environment, you first initialize the local development environment using Astro CLI, then configure the `packages.txt` file to install system dependencies and the `requirements.txt` file to install necessary Python packages like Cosmos and DBT. You also clone a DBT project (e.g., 'Jaffle Shop'), set up Docker configurations, and install DBT-related dependencies in a Python virtual environment.

What is the Jaffle Shop DBT project, and how is it used in this tutorial?

The Jaffle Shop DBT project is a sample e-commerce store that transforms raw data into models for analytics. It is used in this tutorial to demonstrate how DBT models are integrated into Airflow using Cosmos. The project includes SQL models, staging models, and seed data, which are parsed and rendered as Airflow tasks to showcase the Cosmos-Airflow-DBT integration.