DBT full refresh drops all my materialized views. Postgres

user_2491463 0
Viewed 2380

We're using DBT to manage our data pipeline. We're also using postgres as our db. I'm creating some materialized views through a query (not in dbt) and it looks like whenever we run dbt run --full-refresh it drops those materialized views. Any idea why, and how to not drop the materialized views?

dbt postgresql sql
2 Answers

This answer came from Claire at DBT.

"If the materialized views depend on the upstream table, they will get dropped by the drop table my_table cascade statement"

This came from Jake at DBT. "postgres views/materialized views are binding. There’s no opt-out and recreating them even in the same dbt run will result in periods where it’s not available."

https://www.postgresql.org/docs/9.3/rules-materializedviews.html https://docs.getdbt.com/

user_107974280

As the previous answer stated, materialised views are dropped when a table is dropped because of the cascade.

A bridge towards getting higher uptime is to have tables which act as copies of the dbt tables which are being rebuilt which are then deleted and updated on rebuild.

The downtime whilst the tables are being rebuilt is probably worth the deterministic behaviour of knowing when the tables are being rebuilt, rather than having the materialised views disappearing during a long rebuild.

This is the macro I wrote to solve this problem. It creates a new table with a slightly different name within a single transaction, allowing for 100% uptime.

{% macro create_table(table_name) %}

{% set sql %}
    BEGIN;
    DROP TABLE IF EXISTS {{ table_name[:-4]}};
    CREATE TABLE {{ table_name[:-4]}} AS SELECT * FROM {{ table_name }};

    COMMIT;
{% endset %}

{% do run_query(sql) %}
{% do log(table_name+" table created", info=True) %}
{% endmacro %}
user_45729220
Related