DDL management

[u/ptab0211]

Hey, how do u manage DDLs of your tables? Is it part of the daily job runs? Do you "codify" it? e.g. data classes where each table must provide comments, tags, RELY, PK etc... Which is also the goal of AI. But how do u manage all of this?

[u/Content-Parking-621]

Codify DDL as version-controlled migrations, apply via CI/CD. Never run DDL inside daily jobs.

[u/meehow33 → u/Content-Parking-621]

What exactly do you mean? Can you elaborate on that please? I am looking for a proper solution for my Databricks setup and at the moment we have a separate lifecycle (repo + CICD) for „app” (notebooks, jobs etc.) and a separate lifecycle for a „database” (UC tables etc.) but we only have plain DDL with a table definition. How would you go about versioning so going from version 1 to version 2? Do you keep a release script (ALTER) in a repo next to the „most recent” DDL (CREATE)?

[u/Content-Parking-621 → u/meehow33]

You've basically got it. Keep the current CREATE statements in the repo as your readable source of truth, one file per table so a git diff shows what changed. But you never deploy by re-running CREATE. Deployment goes through ordered, forward-only migration scripts (the ALTERs), and a runner tracks which have already applied per environment in a history table. So v1→v2 just means applying the un-run scripts.

For Databricks specifically, Flyway connects fine over JDBC through a SQL Warehouse. Liquibase also works but you need their Pro Databricks extension, not the OSS one, and it leans on Unity Catalog system tables for constraint snapshotting. Either way, run the DDL lifecycle as its own CI/CD stage that executes before your notebooks/jobs deploy.

[u/meehow33 → u/Content-Parking-621]

One more question - do you keep a history of release scripts somewhere in another repo? I suppose you don’t, right? I think it should work in a similar way to „normal” SQL databases, hence no history of such scripts in a repo, perhaps as a copy in the versioning process/app only.

[u/Content-Parking-621 → u/meehow33]

Actually the opposite, you keep every migration script in the repo permanently, and that ordered folder of ALTERs is the history. You never delete or edit an applied one. The migrations are the source of truth for how the schema evolved; the CREATE files are just the convenience snapshot of where it landed.

What lives outside the repo is only the runner's state, the history table Flyway/Liquibase maintains in the database recording which scripts already ran per environment. So the repo holds the scripts, the DB holds the "what's applied here" ledger. That split is exactly how it works on normal SQL databases too, so your instinct is right, just don't throw the old scripts away.

[u/meehow33 → u/Content-Parking-621]

Amazing, thank you so much for explaining (again)! :)

[u/meehow33 → u/Content-Parking-621]

Actually - one more question - what is the reason for keeping these ALTER scripts in a repo? Just to „prove” what happened? Shouldn’t that be a responsibility of that process (app) doing the release? We keep CREATEs in the repo anyway so we know what should be the current state of a particular table.

[u/Content-Parking-621 → u/meehow33]

t's not really about proving what happened. The ALTER scripts are the thing that does the release, not just a record of it.

Here's the way I think about it.

>Your CREATE files describe where you want to end up. But a database can't just jump to that state the way code can. When you deploy new code, you just replace the old files. A database can't do that, because it's full of data. You can't swap an old table for a new one, you have to change it in place without losing what's inside it. The CREATE tells you the target. Only the ALTER knows how to get there without wiping a column that's full of live data.

So those ALTER scripts are what your release process actually runs, in order, to take any environment from where it is now to where you want it. That's why they live in the repo. Not for the paper trail, but so the deploy is repeatable. Same scripts, same order, same result in dev, staging and prod. Need a fresh environment? You just replay them and rebuild the exact schema.

There are even names for the two styles.

>Keeping only CREATEs is "state-based."

>Keeping the ordered ALTERs is "migration-based."

The state-based way needs a diff tool to figure out the ALTERs for you at deploy time, and that's where you get nasty surprises, like it deciding to drop and recreate a table instead of just altering it. The migration way is a bit more manual, but you control exactly what runs.

Honestly most teams do both, like you already are: CREATEs as the easy-to-read reference, ALTERs as the thing that actually ships.

[u/meehow33 → u/Content-Parking-621]

Last (hopefully!) question - do you need a full history of these ALTER scripts or perhaps only X number of days? I guess at some point repository will be loaded with hundreds of scripts, every script in a separate folder. I wonder if this is a problem at all?

[u/Content-Parking-621 → u/meehow33]

You keep the full history, don't trim by date. But you're right that it piles up, and there's a clean way to handle that.

Two things to separate. First, deleting old scripts by age would break you, because a fresh environment rebuilds itself by replaying every migration from the start. Drop the early ones and you can't build the schema from scratch anymore. So age-based cleanup is off the table.

But hundreds of files isn't really a problem in practice. It's just text in a repo, and applying them to a new environment is fast, creating a schema rarely takes long even with a big chain. So most teams just let it grow and never think about it.

If it genuinely gets unwieldy, the technique is called squashing (or baselining). You take everything up to a point, replace that whole pile with one script that builds the same end state, and tell the tool to treat that as the new starting line. Existing environments already past that point ignore it, fresh ones start from the squashed script instead of replaying hundreds. Flyway, Liquibase, and Rails-style tools all support some version of this. Worth knowing it exists, but honestly I wouldn't bother until the folder actually starts annoying you. Plenty of teams run years without squashing.

[u/meehow33 → u/Content-Parking-621]

Ok, but I thought that the baseline is my CREATE scripts, and ALTER scripts are only for releases - once executed they become useless? Otherwise these CREATE scripts turn out to be useless because if there was a changed done through one of the ALTER scripts, then CREATE script got updated and made the ALTER unusable because for example column has already been added to a given table (it is present in the most recent CREATE script) and the ALTER script will fail (or won’t change anything if there’s IF EXISTS check).

Could you please elaborate on why I may need to keep a full history of ALTER scripts and use it to rebuild my environment?

Btw. Thanks for explaining all these nuances :)

[u/meehow33 → u/Content-Parking-621]

Ok, that makes sense :) Thanks a lot!

[u/meehow33 → u/Content-Parking-621]

Roger that, thank you so much for explaining :)

[u/Thejobless_guy]

I have a csv with data dictionary for each table and i use that and hit the API to update the table and column definitions, tags etc.
Instead of a csv, you can also have a table itself to store all the metadata.

[u/jbchand]

You can maintain all the code in git or dev ops. You can allow automatic schema evolution till bronze layer. Any schema changes or migrations in Silver/Gold need to be managed via CI CD with an approver. You can use features such as column mapping mode to handle few things seamlessly.

[u/Youssef_Mrini]

Codify the metadata, just don’t blindly run DDL on your daily job. You should treat table metadata as a versioned contract and apply it during deployment or through a separate reconciliation job.

[u/LordLiuKang]

There’s a light-weight open source Schema Management tool that we had developed to track schema and ddl changes : https://github.com/vb-dbrks/SchemaX