jgrubb/dbt-labs-experimental-features

dbt support for database features which are not yet supported natively in dbt-core

dbt Labs: Experimental Features

This package will add dbt support for database features which are not yet supported natively in dbt-core.

Installation

This repository contains multiple dbt projects. To use the code from one in your own project, clone the repo and install it locally, e.g.:

packages:
  - local: /Users/you/dbt-labs-experimental-features/materialized-views

BigQuery Incremental Strategies

• These features shipped in dbt v0.16.0! See changelog and docs
• The project here provided the substrate for a discourse post benchmarking different incremental strategies on BigQuery

Materialized views

This package adds support for materialized_view as a dbt materialization. It takes an approach similar to that of the existing incremental materialization:

• In a "full refresh" run, drop and recreate the MV from scratch.
• Otherwise, "refresh" the MV as appropriate. Depending on the database, that could be DML (refresh) or noop.

At any point, if the database object corresponding to a MV model exists instead as a table or standard view, dbt will attempt to drop it and recreate the model from scratch as a materialized view.

Postgres

• Supported model configs: none
• docs

Current issues

• Materialized views are registered in pg_matviews. Because dbt's current caching only checks pg_tables and pg_views for existing relations, the current approach is to work around the cache and check pg_matviews from within the materialization.
• dbt only allows 'materializedview' as a RelationType. (See here). When we try to use adapter.rename or adapter.drop, the database is expecting drop materialized view ... or alter materialized view ... rename, not drop materializedview ... or alter materializedview ... rename.

Redshift

• Supported model configs: sort, dist
• docs
• Anecdotally, refresh materialized view ... is very slow to run

Current issues

• MVs do not support late binding. If the base table is cascade dropped, the materialized view seems to stick around in the cache. We need some way to "hard refresh" the cache or check the database after running parents.
• If the column is renamed or removed + readded (e.g. varchar widening), the materialized view cannot be refreshed.

Database Error in model test_mv (models/test_mv.sql)
  Materialized view test_mv is unrefreshable as a column was renamed for a base table.
  compiled SQL at target/run/dbt_labs_experimental_features_integration_tests/test_mv.sql

BigQuery

• Supported model configs: enable_refresh, refresh_interval_minutes
• docs
• Although BQ does not have drop ... cascade, if the base table of a MV is dropped and recreated, the MV also needs to be dropped and recreated

Materialized view dbt-dev-168022:dbt_jcohen.test_mv references table dbt-dev-168022:dbt_jcohen.base_tbl which was deleted and recreated. The view must be deleted and recreated as well.

Snowflake

• Supported model configs: secure, cluster_by, automatic_clustering, persist_docs (relation only)
• docs
• Note: Snowflake MVs are only enabled on enterprise accounts
• Although Snowflake does not have drop ... cascade, if the base table table of a MV is dropped and recreated, the MV also needs to be dropped and recreated, otherwise the following error will appear:

Failure during expansion of view 'TEST_MV': SQL compilation error: Materialized View TEST_MV is invalid.

Lambda views

This lab demonstrates a number of options for lambda views, as discussed in this discourse article. Additional details about the various approaches can be found in at lambda-views/README.md.

Snapshot testing

This lab demonstrates how to use snapshots to detect dbt model regressions, as discussed in this discourse article. Additional details on how to test this code for yourself can be found at snapshot-testing/README.md.

dynamic-data-masking-redshift

This labs demonstrates how to implement dynamic data masking on Redshift.

Check out this discourse article for more information.

Resources:

• Learn more about dbt in the docs
• Check out Discourse for commonly asked questions and answers
• Join the chat on Slack for live discussions and support
• Find dbt events near you
• Check out the blog for the latest news on dbt's development and best practices