I have a fact table that stores metrics that are loaded on a monthly basis, reflecting over the previous month. The metrics are many and varied, most are sourced from complex views that can be expensive to query, and data for the previous month becomes available at various points in the current month. Some will be ready on day 1, some not until mid month or later. As such the metrics must be modular, so they can be loaded independently.
Our legacy method for dealing with this was for each metric to be loaded using its own stored procedure with common logic that goes something like this, in pseudocode…
Assign MaxDateAtTarget = <SQL to get metric’s latest published date>
If MaxDateAtTarget not up to date with previous month
Assign MaxDateAtSource = <SQL to get metric’s latest source date from pre-populated freshness table>
If MaxDateAtSource up to date with previous month
Extract the metric data and load fact table
This minimised the expense of querying complex views by
- skipping it entirely if the target table already has the latest data
- checking a lightweight freshness table to check if the data is ready, rather than querying the view directly
This meant we could fire off all of the procedures once a day and we would only incur the expense of hitting the views when data was needed and available, the rest of the time there was minimal footprint.
I’m trying to figure out an equivalent pattern in dbt using built-in capabilities as far as possible, but can’t quite figure out how. Using loaded_at_query might help with the second bullet, but freshness checks on their own don’t seem able to help with the first bullet, which is where a lot of the unnecessary expense is likely to be incurred if the model runs daily.
I’ve found this thread which talks about using a macro to conditionally inject ‘limit 0’ into queries, which would do the trick and I’m happy to go down that route, but feel like I might be missing something and there could be a less hacky pattern out there.