[CT-427] When cloning an entire database, I'd like to recreate all views to redirect references from self to the newly cloned self

[dbt-labs]

[CT-427] When cloning an entire database, I'd like to recreate all views to redirect references from self to the newly cloned self

[jtcohen6]

@ybressler Thanks for opening, and sorry for the delay getting back to you!

Bad solution

As you mention, this would do the trick:

1. Clone the entire database
2. dbt run --select config.materialized:view --threads 100

It's just not the most savory of solutions :)

Better solution

This is exactly the question you raised in dbt-labs/dbt-core#4959. It's possible (and pretty easy) to override ref to exclude the database identifier.

Docs: https://docs.getdbt.com/reference/dbt-jinja-functions/builtins
Discourse: https://discourse.getdbt.com/t/performing-a-blue-green-deploy-of-your-dbt-project-on-snowflake/1349

The trade-off is, if you do this all the time, you can't create your objects across multiple databases. I remember working on a project that wanted it both ways, and ended up setting a rule like:

• Views can only select from tables in the same database
• Tables can select from objects in other databases

-- Render identifiers without a database if the current model is materialized as a view.
-- Otherwise, include the database.

{% macro ref(model_name) %}

  {% set rel = builtins.ref(model_name) %}
  {% set not_a_view = (config.get('materialized') != 'view') %}
  {% do return(rel.include(database=not_a_view)) %}

{% endmacro %}

Best solution???

I completely buy the thing you're saying here:

As an aside, Snowflake's zero copy cloning is one of its features which sets it apart from its competitors – it would be incredibly valuable to have a more DBT-native solution available

I think we'll see cloning supported by more databases, and it will be all-the-more important for dbt to support a built-in clone method/macro that can abstract across their differences.

I've also long wanted to support a "clone" mode of --defer, rather than just rewriting upstream references: dbt-labs/dbt-core#5095. (This is important for feeding "Slim CI" schemas into BI tools. With traditional --defer, unbuilt upstream models are totally missing.)

Following that approach, dbt could clone upstream objects "as needed." Cloning a table would look like cloning a table. Cloning a view may look like cloning + rewriting refs, or (much simpler) just running that view.

If you're running hundreds of models, the accumulated time of doing this is probably more than a one-shot create database clone .... If you're just running a handful of models, though, you can skip a lot of unneeded cloning. If you've changed a number of models, and you want to inter-weave the running of changed models and the cloning of unchanged models, all in DAG order—such a construct would give dbt the ability to do it.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.