Using dbt deferral to simplify development

As data irrevocably grows in volume, complexity and value, so do the demands from business stakeholders, who need visibility in good time, whilst minimising cost.

This means that as a developer, you are constantly looking for ways to reduce development time, save money and simplify every iteration in your dbt project. If you are not doing that already, you will need to start soon.

dbt is a popular and highly effective tool to manage data transformation efficiently. dbt deferral, one of dbt’s most powerful features, is a great way to reduce both costs and run time which you will want to consider.

Making changes to a single model

Imagine a dbt project where you want to make a change to a single model, model_a, which is dependent on, say, 20 upstream models.

After you have made the required modifications to the model in your development branch, you need to validate your changes. Here, you might execute a command such as:

dbt run --select +model_a

This would instruct your chosen data warehouse (say, Snowflake) to compute and materialise 21 models in your development environment: 20 upstream dependencies plus model_a itself.

You then validate model_a, tweak the code and iterate until you are certain that it is correct. Finally, you would commit and push your changes, create a pull request, seek a peer review and merge to the production branch. Job done.

Hang on.

You have just run 20 other models, incurring added expense in terms of time, computation and storage, just to look at the results of your (possibly very trivial) changes to model_a.

Each of these 20 models exists in production and will have been computed and materialised fairly recently, assuming models in the production branch are built on a regular schedule. So, why not use them?

This is where dbt deferral becomes your friend.

What is dbt deferral

dbt deferral is a powerful feature which allows the developer to run modified models, without needing to also run all of the upstream dependencies.

Following on from the example above, imagine I start with no models materialised in my development environment but this time I provide the selector --select model_a in a dbt run command as well as the option to defer to my production environment. dbt would instruct Snowflake to compute and materialise model_a, and get any upstream data required from production.

This makes a huge difference:

• Without deferral, 21 models are computed and materialised, to validate changes to 1 model;
• With deferral, 1 model is computed and materialised, to validate changes to 1 model.

As a result of using deferral, the number of models computed and materialised is dramatically reduced.

Why use dbt deferral?

Optimising resources

Using dbt deferral simplifies every iteration in your project. As shown above, it helps to minimise the number of models that are materialised in your development environment. This reduces compute and storage costs, and saves time. The larger your project, the bigger the savings.

Keeping a tidy environment

Furthermore, this helps you avoid clutter and/or stale tables in your development environment.

Validation

dbt deferral is also great for validation and measuring the impact of code changes. Let’s assume that you modify a single model. If you defer to production, then at the moment you materialise that model, the resulting table will be based upon precisely the same data as the corresponding table in production.

For those two tables, any differences between production and development will exist solely due to code changes and not differences in data freshness. This saves more time as the impact of your changes is easier to understand and demonstrate.

 

A step-by-step example of using deferral

Let’s go through a more detailed example to see how you would practically apply deferral as part of your development process.

 

Background

Consider this purely illustrative dbt project, which takes a set of raw ingredients and assembles them into a product: a delicious burger in our case.

Here is dbt's Directed Acyclic graph (DAG) for the project:

I have 12 configured sources (the ingredients); and 17 models that either reference those sources or reference each other. To create the final product, the model burger, I need to compute and materialise 16 models (the other one, ing_box, is not a dependency of burger, not yet anyway).

My chosen data warehouse is Snowflake and my source data (green in the DAG above) resides in a schema named RAW. In my production environment, the 17 models downstream of my sources, are materialised in a schema named PROD on a regular schedule. I also have a schema named DEV, which is empty at the moment.

This is the state of things:

 

We need a change

Now, it is development time. I want to make some changes to the model burger. Instead of using ing_wrapper, I want to use ing_box.

First, I create a new branch from the production (main) branch of the dbt project. Then, I checkout the branch and make the required changes to burger, replacing the use of ing_wrapper with ing_box. Next, I need to run burger to validate my changes. I am working locally, using the dbt Core command-line interface (CLI), and I execute the following:

dbt run --select +burger

This command, with the selector: --select +burger, instructs Snowflake to compute and materialise all 15 upstream dependencies of burger, plus burger itself in my default environment, which is development.

And sure enough, we get 16 models materialised in 22.14 seconds:

After running that, RAW, PROD and DEV look like this:

Save time and budget

Above, I only modified one model. It would be a waste of time, compute cost and storage cost to run 15 other models that have not changed and already exist in my production environment.

If I run this using dbt deferral (and I am going to explain the syntax of the command later), here is what happens, starting again from an empty DEV schema:

dbt run --select burger --defer --state prod-run-artifacts

This time, I am kindly asking dbt: please run burger, and if you cannot find anything you need, then defer to the relevant data in my production environment, viz. four models directly referenced by burger: sa_layer_1, sa_layer_2, ing_top_bun and ing_box.

Here it is:

This time, only the burger model is run and it takes 9.39 seconds, which is much quicker.

In the previous, non-deferred run, we ran 16 models, so you might expect it to have taken about one 16th of the time. However, I have dbt configured to use 8 threads, meaning much of the work was done in parallel.

In addition, I have saved the compute and storage cost on 15 models. The more numerous and complicated those models (and their dependencies), the more costs we avoid, and the more time we save.

This is now the state of RAW, PROD and DEV. The arrows show you where DEV.BURGER has sourced its data from:

What is not to like? By applying just a couple of extra arguments to the dbt run command, I have made the development environment much tidier, avoided unnecessary cost and enabled faster materialisation and validation for the modified models.

How does dbt deferral work?

The ref function

How to request deferral

First, you need to add an argument to your dbt run command: --defer. According to dbt’s own documentation:

“When the --defer flag is provided, dbt will resolve ref calls differently depending on two criteria:

As mentioned previously, my burger model contains four ref calls to the following models: sa_layer_1, sa_layer_2, ing_top_bun and ing_box.

For those four models, let’s consider the two criteria:

1. Is the referenced node included in the model selection criteria of the current run? The selector used in my command is --select burger, and so the answer is no.  (If the selector were --select +burger, the answer would have been yes).
2. Does the reference node exist as a database object in the current environment? Let’s assume this is the first time I run my modified burger model. I’m starting with a clean environment and there are no tables or views in my DEV schema, so the answer is no.

If the answer to both is no, the references to sa_layer_1, sa_layer_2, ing_top_bun, and ing_box will use the namespaces defined in the location specified in my --state argument, viz. prod-run-artifacts/manifest.json. And that location, as we have seen, is: DAN_DEFER_DEMO.PROD.

A few additional points of interest

You can use environment variables

Deferral requires both --defer and --state to be set, either by passing flags explicitly in your dbt command, as I showed above; or you can achieve the same by setting environment variables in your dbt_project.yml. These are DBT_DEFER and DBT_STATE respectively.

Not just dbt run

Above, I focussed on the use of deferral with dbt run. However, keep in mind that deferral may also be used with other commands for which the concept of deferral makes sense, like dbt test, dbt build and dbt show.

Slim CI uses dbt deferral

In dbt Cloud, Slim CI, which by definition runs only modified models, uses deferral.  It is used for testing the contents of pull requests before allowing a merge.  There’s some documentation about this here.  Also, you can see this being configured, including some discussion of dbt deferral, in this learning video from dbt:  Set up Slim CI dbt cloud (you will need to register first and it’s free).

Watch out for stale data

Remember, criterion #2 asks if the reference node exists as a database object in the current environment. What if the answer is yes?

This is one of the big ‘gotchas’ with deferral. By default, if a given dependent model or source of the selector specified in your dbt run command exists already in your target environment (e.g. development), then that table/view will be used.

The danger is that data is ‘stale’, or, in other words, not materialised recently, and possibly not as part of the work done in the current branch.

You need to be careful here to avoid confusion. To eliminate the risk of unexpectedly using stale data, I recommend that as soon as you create a new branch from production (main), you clear down your development environment, manually or using a script.

Ultimately, doing this before starting work on a new branch is an excellent practice, regardless of whether or not you plan to use dbt deferral.

The other way to manage this risk is to add an extra argument to your dbt command --favor-state or add an environment variable called DBT_FAVOR_STATE so that if a given model exists in both the current environment and the defined --state, it will always use the latter.

Slim CI does not have this problem, as each time it is triggered, a brand new and empty temporary schema is created hence there is no chance of having any stale data lying around.

Environment setup

There are many different ways of setting up production and development environments. Here are some common examples.

1. One organisation. One account. One database:
   • One schema for production;
   • One or many schemas for development work.
2. One organisation. One account:
   • One database for production (with any number of schemas);
   • One or many databases for development work (each with any number of schemas).
3. One organisation. Two different accounts:
   • One account for production databases/schemas;
   • One account for development databases/schemas.

In this demo, I worked with Snowflake, and environment setup number 1.

In manifest.json, there is no reference to the account; only databases, schemas and table/view names. Therefore, dbt deferral only works if the chosen state references databases on the same account. So, in the environment setup examples above, 1. and 2. would work for deferral, while 3. would require some additional overhead.

If your environment setup is like 3. i.e. production and development environments span multiple accounts, you could still use dbt deferral by making data from the production account available in the development account by using Snowflake Data Sharing for example.

Furthermore (and this concerns all three setups), the user and the role you use in the development environment must have permission to read from whatever databases(s) are referenced in the chosen state (let’s say they are in production).

If role security is such that development users cannot read from production, there may be some extra overhead to enable this. Or, you could consider creating a copy of production with different permissions, for example a clone in Snowflake.

Automatic replication of the production manifest

I have described an approach where the manifest.json from the latest production run is copied manually to a folder of your choice, in order to enable dbt deferral during development.

If you use deferral frequently, I’d recommend automating this, so it’s always available and up to date, if you need it. For instance, you could copy the production manifest to a configured location on a regular schedule, or when certain events occur, like a merge or the creation of a new branch.

dbt Labs demonstrate how you might do this using dbt Cloud, in this short demo. They use Github actions to trigger a dbt job whenever a change is merged to production.  Once that has completed, the resulting manifest.json is copied into a specified folder in the production code base.  This way, all new branches created for development automatically have access to the latest production manifest, making dbt deferral a piece of cake.

Summary

dbt deferral is a powerful and easy-to-implement feature that reduces development time, saves money, and simplifies every iteration in your dbt project.

Head to Infinite Lambda’s blog for more tips and tricks on making your life in analytics easier.

Infinite Lambda is a Platinum dbt Partner. We will be happy to answer your questions about leveraging the tool, so feel free to reach out.

Useful resources

For further reading, I have provided some useful references, below:

1. dbt documentation on Deferral 
2. dbt documentation on Artifacts 
3. dbt documentation on manifest.json 
4. dbt documentation: The ref function 
5. dbt documentation on Directed Acyclic Graph (DAG)
6. Configuring a Slim CI Job
7. dbt learning: Set Up Slim CI in dbt Cloud 
8. Snowflake documentation on Data Sharing 
9. Snowflake documentation on Clone 
10. Upgrading to dbt v1.5 (latest)
11. JSON Crack: navigate your json files