Alter Columns in Unity Catalog via Data Contract YAML.

[khushbookaul]

Hi ,

I am new to using ODCS .

When I run the contract export to SQL, I always see

CREATE OR REPLACE ........

How about if there is an ALTER needed for addition of a new column or metadata in the datacontract , we would ofcourse not like the table to be re-created.

Thanks

[Auguronomics]

Hello, the ALTER TABLE ALTER COLUMN feature necessitates a more complex process for comparing the DDL object (SQL file) with the schema version running on the system of records. datacontract-cli is not a schemachange \ schemadiff CLI utils, your deployment strategy carefully, the best is the declarative way (like ODCS) and as sqlpackage.exe with DACPAC or BACPAC artifacts.

For updating schema structure and content while preserving history, you can refer to AWS Delta Lake's documentation. Instead of replacing the entire schema and data, it maintains history. However, compared to Liquibase or an incremental approach, after 15 years you may not continue with this method.

You can create a command-line tool using simpleddl parser or sqlglot to convert SQL DDL scripts into objects and apply rules to detect changes (ALTER COLUMN, DROP COLUMN, RENAME using fuzzy lookup, ADD COLUMN, ADD TAG, ADD COMMENT etc.). A large language model like Claude LLM or Copilot can assist you in implementing this.

[dmaresma]

Try this cli tool https://github.com/joncrlsn/pgdiff databricks follow the postgresql or ANSI SQL.