This package will add dbt support for database features which are not yet supported natively in dbt-core.
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
- 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
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.
- Supported model configs: none
- docs
- Materialized views are registered in
pg_matviews
. Because dbt's current caching only checkspg_tables
andpg_views
for existing relations, the current approach is to work around the cache and checkpg_matviews
from within the materialization. - dbt only allows 'materializedview' as a
RelationType
. (See here). When we try to useadapter.rename
oradapter.drop
, the database is expectingdrop materialized view ...
oralter materialized view ... rename
, notdrop materializedview ...
oralter materializedview ... rename
.
- Supported model configs:
sort
,dist
- docs
- Anecdotally,
refresh materialized view ...
is very slow to run
- 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
- 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.
- 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.
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.
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.
This labs demonstrates how to implement dynamic data masking on Redshift.
Check out this discourse article for more information.
- 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