[CT-2348] [Feature] dbt clone command

[jtcohen6]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Big idea: Clone my production state into my development schema, please!

Implementation details:

• Sorta similar to --defer. Using the --state manifest, but actually create zero-copy clones/pointers to the "prod" versions
• If the data platform supports create or replace table ... clone, use it!
• If it doesn't, or if the prod version isn't a table, just create a simple view instead: select * from {prod_version}
• By default, we shouldn't recreate already-existing relations in the current target, but users could override this via --full-refresh
• Support a ton of threads! No need to go in DAG order for this.

Describe alternatives you've considered

Previously, I had thought (& written) that this should happen as part of dbt run --defer, as another deferral mode (instead of rewriting upstream references).

But I think that "cloning" should support node selection, too. I don't think there's a nice way to implicitly clone unselected resources and build selected resources, even if it's the ultimate UX we want to achieve.

Instead, you could do something like:

$ dbt clone --exclude state:modified+ --state path/to/manifest
$ dbt build --select state:modified+ --state path/to/manifest

Who will this benefit?

Anyone who wants to avoid rerunning lots of models in dev/CI — but who still want to have a "full" dev/CI schema, with a pointer/clone of every relational object, in order to hook up to downstream querying (e.g. BI)

Are you interested in contributing this feature?

I'll push up a draft, and we can start some conversation there :)

Anything else?

Lengthy previous discussion in:

• Review UX for --defer #5095

Specifically, from #5095 (comment):

You could picture it as something like:

$ dbt clone -s -exclude state:modified+ --threads 10
$ dbt build -s state:modified+

Except, what if dbt did the first step for you implicitly?

Except, what if it actually didn't? And the pseudo-command I wrote above, back in December, is exactly how it should work? :)

[pratik60]

This is really cool, and could immediately see it useful to us as we're just migrating from Redshift to Snowflake!

I was reading this where it inferred that its much fast faster to copy individual tables over copying databases: https://select.dev/posts/snowflake-clones

As we're looking at manifest.json, I'm assuming that's what you're doing anyways underneath the hood, and leveraging tonnes of threads?

[jtcohen6]

@pratik60 Exactly right, on all counts!

To get a sense for feasibility, and identify risks, I've drafted a first stab in #7258. I dropped a whole bunch of questions inline. Most of the questions are around technical implementation decisions, rather than the product/user experience, so I think it's appropriate to start chatting about this with more folks.

[iknox-fa]

Per BLG 4/1/23
@jtcohen6
We need this to be broken out into separate tickets for each adapter (and possibly further broken down although we don't know enough about the method of implementation to say exactly how)

[jtcohen6]

Separate tickets for each adapter makes sense! We can do that as part of fleshing out the epic tasklist (#7301)

It might make sense to dig into the comments/questions I left in #7258 to understand other ways we'd want to break this down. I'm perfectly happy if we use that draft PR as proof of feasibility, and to align on where the gotchas are here — and then throw all the code away and start fresh.

[sungchun12]

I can't wait to throw this demo away in favor of this native feature: https://www.loom.com/share/bcfd2cf3b4b5471683bfc5b24587db3d

Let me know how I can help. Got a reference jinja macro in there too!

[Fleid]

We need to talk about MVs in that context. Sorry I'm just planting a flag at the moment ><

[jtcohen6]

@Fleid on discutera dans 3 minutes :)

[jtcohen6]

Supporting issues split out from this one:

• [CT-2543] Execution <> Adapters: Cloning from production #7549
• [CT-2546] Create classes for dbt clone to run from the cli #7552
• [SPIKE] [CT-2650] Materialization macros should support dispatch #7799