support "with no schema binding" on redshift views

[drewbanin]

http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_VIEW.html

This should almost certainly be the default in production, but there may be some benefit to turning it off in development. It's convenient that view creation currently fails if a nonexistent column/table is referenced

[drewbanin]

There is currently a bug in with no schema binding that causes views creation to fail if the view sql contains CTEs. Bug Report

[izdi]

Hey @drewbanin, this is a nice feature to add.
Is it currently in development ?

[drewbanin]

hey @izdi - it looks like there's a bug on Amazon's side, and they'll be fixing it in the next couple of weeks. More info here

We can add this to dbt in pretty short order once the bug is fixed

[HarlanH]

Just a note -- I'm experimenting using Spectrum to reference external data with dbt. It turns out that you can't create VIEWs in Redshift that reference Spectrum tables unless with no schema binding is supplied. (CTEs are not the issue.) If I force the models to be materialized tables, it works great, but in this case, I'd rather have them be views. It would be great to support this, at least as a non-default option, sooner rather than later!

[drewbanin]

@HarlanH totally on the same page here - we just created this issue to investigate Spectrum in dbt. I think this should be relatively easy to add to the materialization.

At this point, we really want to get 0.9.0 out, then I think it makes sense to turn our attention to features like this for an 0.9.1 release shortly thereafter.

Another compelling idea is to instead add this materialization to the redshift package. This would allow us to iterate on the Redshift view materialization (and build other spectrum helpers) without needing to do a full-fledged release of dbt. I'll have a chat with @cmcarthur, but know that this is definitely on our radar.

[jthandy]

FYI, this redshift bug should be fixed in a coming-soon release:

https://forums.aws.amazon.com/ann.jspa?annID=5149

*Support for a WITH clause subquery in late binding views

[drewbanin]

This is pretty close to being done... Waiting for our Redshift cluster to be patched with the latest release to do some testing!

[izdi]

Hey guys, what's an ETA for the fix ?
Thanks!

[drewbanin]

Hey @izdi - the code for this is written here: #614

We're planning on releasing 0.9.1 this week, but this particular PR should be merged in the next ~24 hours. We'd love it if you could install from development tomorrow and give it a spin before the prod release :)

[izdi]

Hey @drewbanin, I'd like to help, installed from the development branch.
Is adding bind: false on a project level (dbt_project.yml) going be enough ?

For the record -- I usually do not specify any config in my views, so it's a plain SQL query wrapped later in a view with the help of your tool.
Is there any way to tell if it's a view with no schema binding besides altering parent tables ?

[drewbanin]

@izdi this hasn't been merged yet (but will be today -- cc @cmcarthur). When that happens, you'll be able to use bind: false exactly how you've specified here!

The compiled code in target/run/{project_name}/.../model.sql should look like:

create view model as (
  select ...
) with no schema binding;

To see a list of the late-binding views in your Redshift cluster, you can use this query:

select distinct view_schema, view_name
from pg_get_late_binding_view_cols()
cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)

[drewbanin]

This is going out in 0.9.1, closing

[izdi]

Release ETA ?

[drewbanin]

Hey @izdi - we had a bit of a delay over the holiday. All 0.9.1 PRs are merged into development (including this one) at this point. We're doing some final testing, and we're planning to release this Friday. We'd love it if you could install from development and give it a spin! You can find the new docs for this feature here: https://docs.getdbt.com/docs/warehouse-specific-configurations#section-late-binding-views

[drewbanin]

@izdi dbt 0.9.1 was released to pypi today. We're building the homebrew and conda releases presently, and they should be available sometime over the weekend. Feel free to test them out with pip install dbt :)