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:

create table public.test as (
  select 1 as id
);

create view public.test_view as (
  select * from public.test
);

drop table public.test;

ERROR:  cannot drop table public.test because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

View on GitHub

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.

create table public.test as (
  select 1 as id
);

-- NOTE: This is a late binding view. Note the
--       "WITH NO SCHEMA BINDING" clause at the
--       end of the `create view` statement.

create view public.test_view as (
  select * from public.test
) WITH NO SCHEMA BINDING;

drop table public.test;

-- DROP TABLE ✓

View on GitHub

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:

select * from public.test_view;

ERROR:  relation "public.test" does not exist

View on GitHub

As long as we quickly recreate our public.test table, this shouldn't be a problem:

create table public.test as (
    select 2 as id
);

select * from public.test_view;

--SELECT (it worked!)

View on GitHub

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:

/*
  This config will materialize this dbt model as a late-binding view.
  Note that dbt models are by-default materialized as views, so it's
  not always necessary to specify `materialized='view` here.
*/

{{ config(materialized='view', bind=False) }}

select * from public.test

View on GitHub

Or in your dbt_project.yml file:

name: 'my_dbt_project'
version: '1.0'

...

# This will make _all_ view models in your project
# use the late-binding modifier. The `bind` configuration
# can be specified anywhere in the configuration hierarchy
models:
    bind: false

View on GitHub

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!