Alembic Meets the Lakehouse: Schema Migrations with Databricks SQL and Unity Catalog

Why databricks-sqlalchemy Is the Interesting Part

Alembic is the standard migration framework for SQLAlchemy: a versioned chain of migration files, upgrade/downgrade execution, and a bookkeeping table (alembic_version) that tracks which migration a database is currently at. autogenerate — the part that diffs your Python model classes against the live database to draft a migration — is a convenience built on top of that, not what Alembic fundamentally is. None of it ever assumed a lakehouse as a target, because until relatively recently there was no SQLAlchemy dialect that could speak to Databricks at all. Databricks' own databricks-sqlalchemy is what changes that: it lets SQLAlchemy issue queries and DDL against Delta tables through Unity Catalog, which means a relational migration tool that has always assumed Postgres-shaped rows-in-tables semantics can, for the first time, treat a Delta table as something it knows how to version and diff at all. That's genuinely useful — it's what makes "define a table as a model, let a tool draft and track its schema history" possible for a lakehouse instead of being a thing you only get with a traditional OLTP database.

What the dialect doesn't do is make Alembic's deeper assumptions about what "a database" guarantees suddenly true for Unity Catalog. It solves the transport problem — SQLAlchemy can now speak SQL that Databricks understands. It doesn't solve the semantic problem: whether a construct that means one thing in a SQLAlchemy model still means the same thing once it lands in Delta.

You aren't really "making Alembic work with Databricks." You're reconciling a relational migration tool with a lakehouse whose schema and constraint semantics are different.

The motivation for doing this at all was narrow and concrete: recreate the exact same tables, columns, and constraints across workspaces on demand — a fresh dev environment, a disaster-recovery rebuild, a brand-new project — without it turning into a person re-running a folder of DDL notebooks by hand and hoping nothing in that folder was stale. A notebook someone forgot to update doesn't fail loudly against a new workspace; it just produces a schema that's subtly different, and nobody notices until a job breaks weeks later. Getting Alembic pointed at Unity Catalog through the official dialect meant finding out that roughly half of what Alembic assumes about "a database" doesn't hold there.

Where It Broke

The first migration ran cleanly. The second — adding a column to a table with a declared primary key — didn't. The generated column came back NOT NULL, and the write that populated it failed, because the pipeline feeding that table sometimes doesn't have a value for it yet (a late-arriving dimension key, genuinely optional in Delta despite being part of the "primary key" used for merge-matching). SQLAlchemy's PrimaryKeyConstraint encodes an assumption specific to systems like Postgres: a primary key column is, definitionally, enforced and therefore non-null. Unity Catalog's primary key constraints are informational — they document the natural key a merge should match on, but the platform doesn't reject a null or a duplicate at write time.

It's worth being precise about what kind of bug that was. Alembic did exactly what it was told: it generated a migration that correctly described a table with a primary key, and applied it without error. The migration was correct. What was wrong was the assumption, baked into the model, that a declared primary key also bought data-integrity enforcement — that Unity Catalog would reject the bad write the way Postgres would have. Schema migration correctness and data integrity enforcement are two different guarantees that Postgres happens to bundle tightly enough to stop noticing they're separate. Unity Catalog unbundles them: it will faithfully persist a schema that describes a primary key, and just as faithfully let you write a null into it.

Two more Postgres-shaped assumptions didn't transfer:

• Namespace depth. SQLAlchemy's metadata model is schema.table — two levels. Unity Catalog is catalog.schema.table — three. Every model needed an explicit catalog reference threaded through, and the naming convention had to encode environment, since a table's fully-qualified name changes across dev, staging, and production catalogs, not just its schema.
• Where Alembic runs from. Alembic expects to import your models the way any Python package does, via a normal sys.path. Inside a Databricks job the source tree isn't installed as a package — it lives in a workspace path Python's import machinery doesn't know about. A hardcoded sys.path.append(...) in env.py worked on one machine and broke everywhere else; the durable fix resolved the source path relative to the repo root at runtime instead.

A third bug surfaced in the same stretch of work but wasn't actually about Unity Catalog: a config model gating which tables were eligible for migration relied on Pydantic v1's behavior for a field with default=None, where an explicit None and an omitted field were indistinguishable. Pydantic v2 tightened that, and configs that had always worked started getting rejected. A dependency bump biting an unrelated assumption, not a lakehouse semantic gap — it would have broken identically against Postgres.

The Fix: One Column Definition, Two Type Systems

Two other paths were considered first. Dropping Alembic for hand-run DDL — plenty of lakehouse pipelines are built exactly that way — avoids the whole class of mismatch, at the cost of losing everything migrations-as-code buys: ordered history, a repeatable upgrade path, one source of truth for a table's current shape. Keeping Alembic but never trusting autogenerate, writing every migration by hand and using autogenerate only as a rough draft, sidesteps the nullability bug specifically, but throws away autogenerate's real value — catching the migrations someone forgot to write, not just formatting the ones they remembered.

What actually happened instead was keeping both, and pushing the fix down into the model layer that both PySpark and Alembic read from. That layer already had to solve a problem that has nothing to do with Unity Catalog specifically: the same table definition is used twice, for two different jobs. PySpark needs it as a StructType to validate and write Delta data at runtime. Alembic needs it as a sa.Table to diff against the live catalog and generate DDL. Maintaining those as two hand-written schemas — one PySpark, one SQLAlchemy — for every table is exactly the kind of drift that migrations-as-code is supposed to eliminate in the first place: nothing stops them from silently disagreeing.

The pattern that closes that gap is a single column description, keyed by an internal enum rather than either type system's native types, with a mapping dict for each target. Simplified from the model layer this project used, it looks like this:

class ColumnDataType(Enum):
    integer = "integer"
    long = "long"
    string = "string"
    decimal = "decimal"
    datetime = "datetime"
    # ...

# One dtype, two independent translations:
PYSPARK_TYPE_MAPPING = {
    ColumnDataType.integer: pt.IntegerType,
    ColumnDataType.long: pt.LongType,
    ColumnDataType.string: pt.StringType,
    ColumnDataType.decimal: pt.DecimalType,
    ColumnDataType.datetime: pt.TimestampType,
}

SQLALCHEMY_TYPE_MAPPING = {
    ColumnDataType.integer: sa.Integer,
    ColumnDataType.long: sa.BigInteger,
    ColumnDataType.string: sa.String,
    ColumnDataType.decimal: sa.Numeric,
    ColumnDataType.datetime: sa.TIMESTAMP,
}

A column is declared once, against ColumnDataType, and a BaseColumn dataclass knows how to render itself into either world on demand:

@dataclass
class BaseColumn:
    name: str
    dtype: ColumnDataType
    nullable: bool = False
    primary_key: bool = False

    def __post_init__(self):
        if self.nullable and self.primary_key:
            raise ValueError(f"'{self.name}' is both nullable and primary key")

    def as_struct_field(self) -> pt.StructField:
        """Render for PySpark: used to validate/write Delta data."""
        pyspark_type = PYSPARK_TYPE_MAPPING[self.dtype]
        return pt.StructField(self.name, pyspark_type(), nullable=self.nullable)

    def as_sqlalchemy_column(self) -> sa.Column:
        """Render for SQLAlchemy: used by Alembic to diff and migrate DDL."""
        sqlalchemy_type = SQLALCHEMY_TYPE_MAPPING[self.dtype]
        return sa.Column(
            self.name, sqlalchemy_type(),
            nullable=self.nullable, primary_key=self.primary_key,
        )

This is the actual leverage of pairing databricks-sqlalchemy with Alembic on a lakehouse: it isn't just that Alembic can now reach Unity Catalog, it's that the same column model already driving the Delta writes can also drive the migration, from one declaration instead of two. That's also where the nullability bug actually got fixed — not by defaulting nullability one way or the other, but by making it a plain, required-in-practice field on BaseColumn, fully decoupled from primary_key. SQLAlchemy's own PrimaryKeyConstraint would still happily infer NOT NULL from primary_key=True if you let it get that far, so as_sqlalchemy_column() never hands SQLAlchemy a bare primary-key flag without an explicit nullability value alongside it. The __post_init__ check above is a smaller, related guard: this model treats "primary key" as informational metadata, the same way Unity Catalog does, so declaring a column both nullable=True and primary_key=True is a contradiction worth catching at definition time rather than at migration time.

Worth being clear about what's illustrative here versus load-bearing: the exact class and dict names above are simplified for this write-up, and any codebase adopting this pattern will shape its version differently depending on its own conventions — some will fold the two mappings into one dict of tuples, some will generate the SQLAlchemy side from the PySpark side directly. What's load-bearing is the principle: one internal type per column, with independent, explicit renderers for each consumer, so a lakehouse table's Delta schema and its migration DDL are provably the same column model rather than two documents someone has to remember to keep in sync.

The three-level namespace was a related but separate problem, handled by making catalog and schema both first-class, environment-resolved values passed in when a BaseTable is rendered into a sa.Table, rather than a schema string with an implicit catalog baked into the model:

def target_metadata_for(environment: str) -> MetaData:
    """Resolve the fully-qualified catalog.schema for a given environment."""
    catalog = f"analytics_{environment}"
    schema = "core_serving"
    return MetaData(schema=f"{catalog}.{schema}")

Worth flagging honestly: this leans on SQLAlchemy's schema parameter accepting a single string, which the Databricks dialect happens to parse as catalog.schema. That isn't a documented part of SQLAlchemy's core metadata model — it's a dialect-specific accommodation, worth re-verifying rather than assuming it generalizes to other multi-level-namespace databases.

And the very first time this ran against a real environment, Alembic looked for alembic_version and didn't find one — nothing had ever run Alembic against that catalog before. On Postgres that's a non-event: Alembic creates the table itself, silently. Unity Catalog's DDL permission model is stricter about implicit versus explicit table creation, so alembic_version was bootstrapped as its own tracked migration instead, meaning the very first run against a brand-new catalog looked identical to the thousandth:

CREATE TABLE IF NOT EXISTS analytics_prd.core_serving.alembic_version (
    version_num STRING NOT NULL
);

Autogenerate diffing your models against the live catalog is only trustworthy once the models tell the truth about what the catalog actually enforces.

The Outcome

• Lakehouse schema changes now go through the same migrations-as-code discipline as the Postgres-backed services — versioned, reviewed, replayable across environments — instead of one-off DDL run by hand from a notebook.
• Standing up a new workspace, or rebuilding one from nothing, now means pointing Alembic at an empty catalog and running upgrade head, not a person working through a folder of notebooks in the right order.
• None of that cost came from wiring Alembic up; it came from correcting every place its Postgres-shaped defaults silently assumed guarantees Unity Catalog doesn't provide — nullability, namespace depth, and import resolution, each individually capable of producing a migration that looked correct and wasn't.
• A tool built for traditional OLTP databases — one that expects the engine itself to verify constraints like primary keys and nullability — still works as a real migration tool for a lakehouse, despite Unity Catalog never enforcing those constraints the way Alembic's origins assume. It just can't be pointed at Unity Catalog and trusted blindly the way it can be pointed at Postgres; the model layer has to do, explicitly, the enforcement work the target platform doesn't do for you.