Hi,
We needed to perform full refresh on the table, let’s call it table_x.
Table table_x was also used in one view which was not generated by dbt.
After full refresh, view which was using table_x was dropped.
Is there any option in dbt that will do similar like full refresh, but it will preserve indexes, partitions and keys of the table and also, most importantly it will not delete views which are using that table?
Specific behavior is database-dependent, but usually views are dropped when the table is dropped, and need to be re-created after. In some dbs dropping a table fails if CASCADE is not used to drop the view; in others the View is not dropped but it’s underlying reference is to a table that no longer exists, so the View no longer works. (even if the new table has the same name, the underlying ref is different)
Indexes, partitions, keys on the table itself should all be defined in the table config so that when you full-refresh they are re-created, otherwise they are lost.
For the View, you would have to either add creation of it as a dbt model, or as a post-hook on the table.
The other option is to add the flag to the config to not full refresh when full-refresh is done, and handle it manually so that you can manage config & dependencies.
Note: @Renee originally posted this reply in Slack. It might not have transferred perfectly.
If you’re using redshift, you can prevent views from being dropped by changing to late-binding views: Redshift configurations | dbt Developer Hub
Thank you guys for answers.
I am also interested what is the motive for full refresh doing dropping instead of truncate?
It’s often used for schema changes, which truncate does not accommodate.
Note: @Renee originally posted this reply in Slack. It might not have transferred perfectly.