Using Redshift’s Late Binding Views with dbt
dbt version 0.9.1 adds support for late binding views in Redshift. This post will explain late binding views and explore some potential use cases.
Redshift Views
If you’ve worked with views on Redshift before, you’re probably well acquainted with this error message:
In the above query, public.test_view is “bound” to public.test. If you try to alter or delete the public.test table, Redshift will first check with public.test_view to make sure that’s ok. In this example, test_view was decidedly not ok with the drop, and it was rejected accordingly.
This binding between view and table is sometimes convenient — it makes it impossible to get into a situation where a view is supposed to query a table that no longer exists. Frequently though, analysts only want to “replace” a table by dropping and recreating it. In this instance, the binding between view and table is more of a hassle than a benefit.
Enter Late Binding Views
Late binding views solve this problem by decoupling views from the underlying datasets they select from. We can indicate to Redshift that a view should be late-binding by using the WITH NO SCHEMA BINDING clause at the end of the create view statement.
In the above example, the database happily drops the public.test table! The only caveat here is that our public.test_view is now dangling — it’s tasked with selecting from a table, public.test, which doesn’t exist. If you try to query it, you’ll be confronted with this error message:
As long as we quickly recreate our public.test table, this shouldn’t be a problem:
Late Binding Views + dbt
To configure a dbt model to materialize as a late-binding view, simply configure the model with bind=False. This configuration can either be specified inside of the model, or in your dbt_project.yml file. Here’s what this looks like in practice:
Or in your dbt_project.yml file:
And that’s it! For more information, check out the docs.
What’s next?
While late binding views will certainly alleviate some Redshift headaches, this post only scratches the surface of what you can do with them. The late binding modifier also makes it possible to build views on top of external tables using Redshift Spectrum. This is a powerful workflow, and one which we’ll explore further in a future post :)
Did late binding views change your life? Let us know in Slack!
