Use --env-prefix to create developer/feature environment as a zero-copy clone of some source database (DEV, UAT or PROD)

[zhyzhkea]

Usually, developers start to work on new assignments by creating a feature environment but they need the latest data from some official SDLC environment as a starting point. It would be beneficial to have another option to clone vs. just create table?

CREATE <object_type> <object_name> CLONE <source_object_name>

[littleK0i]

@zhyzhkea , thank you for mentioning cloning as a possible feature for SnowDDL.

I considered it initially, but cloning has too many limitations and various issues associated with it. It's nice feature for marketing, but not so good for an actual production usage.

FIrst of all, please check the long list of official limitations here: https://docs.snowflake.com/en/user-guide/object-clone

The most problematic point from this list is the "clone" operation potentially failing mid-way due to issues with data retention. Unfortunately, this operation is not atomic.

Additionally, cloning conflicts with SnowDDL access management features related to SCHEMA ROLES (read more about role hierarchy). Cloned objects inherit privileges and ownership, which must be completely re-assigned afterwards.

---

We tend to recommend a completely different approach.

1. Never mix PROD and DEV environments. Create separate Snowflake accounts within your organization for PROD and DEV: https://docs.snowflake.com/en/sql-reference/sql/create-account
2. If some data must be transferred from PROD to DEV, use pre-configured one-way SHARES.
3. Use SnowDDL with --env-prefix on DEV Snowflake account to create separate objects for each developer.
4. Run ETL jobs with --env-prefix and sampling on DEV Snowflake account.

Basically, you should add --env-prefix support to your ETL jobs and run such jobs for each developer separately. But instead of running a full job loading 100M rows, you should apply sampling and load only 1000 rows. Instead of loading thousands of files, just load a few dozen files.

With this approach each developer or each CI/CD script can test the whole tree of ETL jobs simultaneously and independently from each other, which leads to very high confidence when finally pushing changes to PROD Snowflake account.

Also, you'll have zero chance of PROD breaking DEV or vice versa.

[littleK0i]

Alternatively, we could potentially add a limited support for cloning, but only if:

• scope is very narrow (for example, clone normal tables only, 1-by-1, never clone the whole databases);
• all known issues are fixed, every limitation has a workarounds or does not matter;

[zhyzhkea]

I was proposing to do it only for tables. The rest of the objects could be created using .yml files. If cloning is done at the schema level, schema objects would be owned by the source owner role but if tables are cloned individually in a loop, then tables would be owned by the active role (developer role). Zero-copy clone does not require ETL or sampling. It is fast and represents actual data from the upper environment. This scenario will work only for teams that do not have access to account-level privileges and all SDLC environments are databases under one enterprise account. Cloning from another account is not supported and databases built from share are read-only which defeats the purpose.

[littleK0i]

@zhyzhkea , I'll make a few tests and hopefully push an initial MVP-version with cloning support.

I am currently thinking about the following implementation details:

• Add new CLI option --clone-table;
• If this option is specified, an additional cloning step will be added before TABLE;
• For each table in config, if the same table exists in source database (without prefix), it will be cloned;
• If table does not exist in source database it will not be cloned and empty table will be created normally instead;
• If table already exists in destination database (with prefix), it will not be cloned again. To get an another clone, you should run snowddl destroy first or DROP this table manually.
• Cloning can work only with snowddl apply, it cannot work with snowddl plan;

With this approach tables will be cloned with proper future grants.

With this approach SEQUENCEs will be correctly re-assigned right after cloning.

Other objects will not be cloned and will be created from scratch instead. Sometimes it may lead to extra costs, if you have MATERIALIZED_VIEW or DYNAMIC_TABLE pointing to the large cloned table.

Does it sound good for you? Do I miss any other essential features?

[littleK0i]

@zhyzhkea , I've pushed an initial version of cloning. You may give it a try since 0.18.0.

Add CLI argument --clone-table together with --env-prefix. Tables from "source databases" (without prefix) will be cloned to "destination databases" (with prefix).

Cloning happens after creation of SCHEMA, SCHEMA_ROLE, but before TABLE.

Only tables which currently do not exist in destination databases are cloned. If you want to clone a table one more time, you should run snowddl --env-prefix=XXX destroy to cleanup and re-create databases with prefix again. Alternatively, you may drop specific tables from destination database manually.

You'll see some ALTER TABLE commands being applied or suggested by SnowDDL. It happens because cloned tables inherit a lot of links to objects in source database. SnowDDL tries to fix it and switch these links to destination database. It is especially noticeable for SEQUENCE and constraints like FOREIGN_KEY.

Only tables are cloned. All other types of objects are created from scratch, which may or may not cause some undesirable effects. We'll see how it goes.

Please let me know if it works for you. If it goes well, I'll make it an official feature.

Thank you!

[zhyzhkea]

It sounds very good. Thank you.

…

On Tue, Jul 18, 2023 at 12:50 PM littleK0i ***@***.***> wrote: @zhyzhkea <https://github.com/zhyzhkea> , I've pushed an initial version of cloning. You may give it a try since 0.18.0. Add CLI argument --clone-table together with --env-prefix. Tables from "source databases" (without prefix) will be cloned to "destination databases" (with prefix). Cloning happens after creation of SCHEMA, SCHEMA_ROLE, but before TABLE. Only tables which currently do not exist in destination databases are cloned. If you want to clone a table one more time, you should run snowddl --env-prefix=XXX destroy to cleanup and re-create databases with prefix again. Alternatively, you may drop specific tables from destination database manually. You'll see some ALTER TABLE commands being applied or suggested by SnowDDL. It happens because cloned tables inherit a lot of links to objects in source database. SnowDDL tries to fix it and switch these links to destination database. It is especially noticeable for SEQUENCE and constraints like FOREIGN_KEY. Only tables are cloned. Everything else is created from scratch, which may or may not cause some undesirable effects. We'll see how it goes. Please let me know if it works for you. If it goes well, I'll make it an official feature. Thank you! — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIEPEM56R5B4GYLOCRGHW3TXQ3SIZANCNFSM6AAAAAA2J3ZZ74> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[MaRo99382]

Hi @littleK0i,

I am currently testing the --clone-table CLI argument.
Very nice addition to SnowDDL, I have use cases where this fits perfectly. So thank you for the work! :)
So far I have not encountered any errors or problems when using it.

Not sure if this was done on purpose, but I am wondering why when I re-run the --clone-table commandline for a given --env-prefix it is not re-cloning the source tables?
I can see that it is running CREATE TABLE IF NOT EXISTS .... CLONE .... and since the table already exists, it won't be cloned again.
If I want to re-create an environment I thus have to destroy it first and then apply it with the --clone-table option again.
I think it would be great if the tables are re-cloned automatically everytime one runs the commandline.
What do you think?

[littleK0i]

@MaRo99382 , originally it was done on purpose, since full cloning for large accounts adds significant overhead. In theory, we want to avoid accidental or unnecessary full cloning to save time & credits.

"Destroy & re-create" approach seems more explicit and robust. Also, it ensures all objects with dependencies on cloned tables can be successfully re-created from scratch, e.g. PIPES. If we only "replace" all clones, but keep other objects unchanged, it may potentially cause some underlying issues. Even if "definition" of unchanged objects is valid.

---

In general, we suggest to avoid clones, create empty tables normally and load a tiny sample of data instead of full data set for DEV purposes. It would allow you to perform and test transformations requiring "CREATE OR REPLACE TABLE" (e.g. change of data type) very cheaply.