DROP statements are executed after CREATE statements for objects with same name.

[CM000n]

Describe the bug
We have the case where we need to replace dynamic tables with normal tables of the same name in the same schema.
However, SnowDDL tries to execute the CREATE of the new tables before the DROP of the dynamic tables.
This leads to name conflicts and the pipeline does not run successfully. The only workaround is to manually delete the dynamic tables before executing SnowDDL

Expected behavior
SnowDDL should perform the DROP of the dynamic tables before creating the new tables.

Attach log

$ snowddl -c config/ -a ${SNOWFLAKE_ACCOUNT} --env-prefix ${ENV} --placeholder-path ${ENV_VARS} -r ${ROLE} -u ${USER} -k ${KEYFILE} --passphrase ${PASSWORD} -w ${ENV}__SNOWDDL_WH --show-sql plan
2025-02-13 10:44:26.973 - INFO - Snowflake version = 9.2.7 (BUSINESS_CRITICAL), SnowDDL version = 0.44.0
2025-02-13 10:44:26.973 - INFO - Account = ****, Region = GCP_EUROPE_WEST4
2025-02-13 10:44:26.973 - INFO - Session = ****, User = [MASKED]
2025-02-13 10:44:26.973 - INFO - Role = ****, Warehouse = ****
2025-02-13 10:44:26.973 - INFO - Roles in session = ACCOUNTADMIN,SYSADMIN,SECURITYADMIN
2025-02-13 10:44:26.974 - INFO - ---
2025-02-13 10:44:28.926 - INFO - Resolved TABLE [DEV__MSTR.CLEANED.DIM_MOBILE_EVENT_V20241119]: CREATE
2025-02-13 10:44:28.926 - INFO - Resolved TABLE [DEV__MSTR.CLEANED.DIM_SEARCH_PHRASE_V20241104]: CREATE
2025-02-13 10:44:28.927 - INFO - Resolved TABLE [DEV__MSTR.CLEANED.DIM_VISIT_V20241104]: CREATE
2025-02-13 10:44:28.932 - INFO - Resolved TABLE [DEV__MSTR.CLEANED.UNIQUE_USERS_DAILY_V20241030]: CREATE
2025-02-13 10:44:28.933 - INFO - Resolved TABLE [DEV__MSTR.CLEANED.UNIQUE_USERS_MONTHLY_V20241030]: CREATE
2025-02-13 10:44:28.933 - INFO - Resolved TABLE [DEV__MSTR.CLEANED.UNIQUE_USERS_YEARLY_V20241030]: CREATE
2025-02-13 10:44:29.684 - INFO - Resolved DYNAMIC_TABLE [DEV__MSTR.CLEANED.DIM_MOBILE_EVENT_V20241119]: DROP
2025-02-13 10:44:29.684 - INFO - Resolved DYNAMIC_TABLE [DEV__MSTR.CLEANED.DIM_SEARCH_PHRASE_V20241104]: DROP
2025-02-13 10:44:29.684 - INFO - Resolved DYNAMIC_TABLE [DEV__MSTR.CLEANED.DIM_VISIT_V20241104]: DROP
2025-02-13 10:44:29.684 - INFO - Resolved DYNAMIC_TABLE [DEV__MSTR.CLEANED.UNIQUE_USERS_DAILY_V20241030]: DROP
2025-02-13 10:44:29.684 - INFO - Resolved DYNAMIC_TABLE [DEV__MSTR.CLEANED.UNIQUE_USERS_MONTHLY_V20241030]: DROP
2025-02-13 10:44:29.685 - INFO - Resolved DYNAMIC_TABLE [DEV__MSTR.CLEANED.UNIQUE_USERS_YEARLY_V20241030]: DROP
2025-02-13 10:44:31.520 - INFO - Resolved ROLE [DEV__ACCOUNT_MONITORING__T_ROLE]: GRANT
2025-02-13 10:44:31.823 - INFO - Executed 0 DDL queries, Suggested 18 DDL queries

[littleK0i]

It is in an interesting topic for sure.

In theory, this specific issue can be fixed by executing DROP commands before CREATE commands. But many other similar changes cannot be implemented easily. For example, if we want to convert DYNAMIC_TABLE to normal TABLE, we probably do not want to do DROP + CREATE and lose all data. We probably want to run CREATE OR REPLACE TABLE ... AS SELECT ... FROM dynamic_table.

I suspect such cases cannot be handled declaratively, and we may need to add some occasional imprative-style migrations, which should be applied manually.

How we usually do it:

1. Add changes to SnowDDL config.
2. Write raw SQL file to perform transformation of objects.
3. Apply SQL file manually during deployment process.
4. Apply SnowDDL config.

Interestingly enough, this is only needed for PROD environment. DEV environment can be destroyed and re-created at any moment, and losing data is normally not an issue.

Some other notable use cases for SQL files:

• RENAME commands;
• UPDATE / MERGE commands to transform actual data inside table;
• Creation of global objects managed outside of SnowDDL, like INTEGRATIONs;