[FEATURE REQUEST] Support CREATE OR ALTER for declarative scripts

[NemSimpraga]

Support CREATE OR ALTER for declarative scripts

Summary

Snowflake's CREATE OR ALTER syntax (currently in Preview) is a less destructive alternative to CREATE OR REPLACE. Supporting it in DLSync could allow several object types that are currently migration-only to be managed declaratively, which is simpler for users.

Why this matters

CREATE OR REPLACE drops and recreates objects, which loses data, grants, tags, and policies. This is likely why objects like databases, schemas, and roles are forced into migration mode — CREATE OR REPLACE would be too destructive for them.

CREATE OR ALTER modifies an existing object in place, preserving all of the above. This makes it safe for declarative management of many more object types.

What could change

With CREATE OR ALTER support, these migration-only objects could become declarative:

Object	Current	Could Be	Notes
DATABASES	Migration	Declarative	CREATE OR REPLACE drops the entire DB; CREATE OR ALTER is safe
SCHEMAS	Migration	Declarative	Same reasoning as databases
TASKS	Migration	Declarative	CREATE OR ALTER preserves schedule/state
ALERTS	Migration	Declarative	Similar to tasks
DYNAMIC_TABLES	Migration	Declarative	Avoids re-materialization
ROLES	Migration	Partially	Role definition could be declarative, but GRANT/REVOKE remains incremental
TABLES	Migration	Migration	Holds data — migration is correct regardless
SEQUENCES	Migration	Migration	Holds state (counter value)
STREAMS	Migration	Migration	Holds state (offset)
STAGES	Migration	Migration	Internal stages hold files

Current blocker in the code

Even if users wanted to use CREATE OR ALTER in their declarative scripts today, the verify module would break. The compareScripts method in SqlTokenizer.java uses regexes that hardcode CREATE OR REPLACE:

VIEW_BODY_REGEX = "(CREATE\\s+OR\\s+REPLACE\\s+VIEW\\s+)..."
FUNCTION_BODY_REGEX = "(CREATE\\s+OR\\s+REPLACE\\s+FUNCTION\\s+)..."
PROCEDURE_BODY_REGEX = "(CREATE\\s+OR\\s+REPLACE\\s+PROCEDURE\\s+)..."
FILE_FORMAT_BODY_REGEX = "(CREATE\\s+OR\\s+REPLACE\\s+FILE FORMAT\\s+)..."

The DDL_REGEX built in buildDdlRegex() also hardcodes CREATE\\s+OR\\s+REPLACE.

These would need to accept both CREATE OR REPLACE and CREATE OR ALTER patterns.

Suggested approach

1. Update the regex patterns in SqlTokenizer.java to accept CREATE\s+OR\s+(REPLACE|ALTER)
2. Update documentation to mention CREATE OR ALTER as a supported (and recommended) alternative
3. Optionally, allow object types like DATABASES and SCHEMAS to be classified as declarative when using CREATE OR ALTER

This could be gated behind Snowflake's CREATE OR ALTER reaching GA status, or offered as an opt-in feature now.