DBT Marts DAG

[lprzychodzien]

Airflow and DBT

Currently we use Airflow to orchestrate data ETL jobs for our Postgres Database. DBT is used as the business logic layer onto of the airflow end state data. Both work together to ensure that SageRx delivers reliable data to its desired end state.

Problem

Each Airflow DAG is scheduled to run on various predefined timeframes. As the data becomes more intertwined we have DBT marts that are reliant on data from multiple DAGs. This brings up the issue of aligning DAGs so that the marts are updated with the latest data while not needlessly rerunning things (cost issue).

Purpose

The goal of this discussion is to come to a consensus on which approach is best for where the product is right now. This approach will most likely change as SageRx grows and matures.

Approaches

Approach 1 - Logging Changes

DBT Mart is a stand alone DAG.

Each source DAG logs data changes which are then used to determine what downstream marts should be ran. We only run mart updates on data that has changed.

Example, if FDA exclusions DAG is run but no new data comes in then no need to rebuild the downstream marts.

Pros

• Aligns with the other needs for logging data changes
• Lowest operarting costs due to running updates only when needed
• Can run marts DAG at most frequent cadence with no fear of unnecessary additional costs

Cons

• Timing can be tricky. If an error occurs then the mart updates DAG could skip updating the data, causing the mart data to be stale
• Data checks can be expensive if not built correctly

Approach 2 - Airflow Sensors

DBT Mart is a stand alone DAG.

With this approach we utilize Airflow ExternalTaskSensor to make a task run only after another DAG is completed. This works by having Mart sensors check for a final completion task of the source DAG.

Example, if the FDA Excluded DAG had multiple source tables then that DAG would have a final Airflow EmptyOperator (such as FDA_Excluded_Complete) that the marts DAG would be checking for.

Pros

• Precise control of what mart updates run and when, since marts dependent on multiple DAGs would only be triggered once all sources update
• Only run what is needed

Cons

• Need to have the Marts DAG scheduled to check for completed tasks at the most frequent cadence of the other DAGs
• Would need to run on a regular cadence to align the timing of DAGs
• Need to build out sensors for all tables

Approach 3 - DBT within each dependent DAG

DBT work is part of each DAG that relies on it.

This is a simple approach, run the DBT jobs that are dependent on a DAG at the end of said DAG.

This would look like the following: extract -> load -> transform -> DBT tasks.

Pros:

• simple to do, just run the DBT command for all upstream commands for each mart or all downstream tasks of a source

Cons:

• Potentially expensive if we need to rebuild tables multiple times if table is dependent on multiple DAGs
• Selecting the correct resources to run might be a maintenance issue once scaled

Approach 4 - Full Refresh DAG

Separate DAG that runs a DBT ad-hoc full refresh every defined period of time, such as daily (overnight). This is the simplest approach since all DBT logic is self contained and not dependent on upstream Airflow work.

Pros

• Standardized approach

Cons

• No control so needs to be run at the most frequent cadence
• Potentially expensive since rerunning marts on data that hasn't changed

Approach 5 - Utilize DBT incremental models

DBT Mart is a standalone DAG.
DBT has built in functionality around DBT building/updating models incrementally (DBT documentation link). This allows for rows that have been updated to drive the downstream DBT table changes, making sure that only the required rows are impacted.

Pros

• Standardized and validated functionality
• Changes only necessary rows, keeping costs controlled

Cons

• Rewrite of all DAGs to incremental methodology including additional columns for change tracking
• Requires each table to have some defined unique row identifier for updating
• Full refreshes force dbt to rebuild the entire incremental model from scratch, losing any change logging

Conclusion and Personal Recommendation

Utilizing Approach 5 makes the most sense since the functionality is already built into DBT. However, with the lack of persistent change logging, combining it with approach 1 is the most comprehensive solution. It satisfies both logging from a quality and refresh perspective. It also can help keep operating costs low while SageRx scales.