Alembic Against a Lakehouse: Making Schema Migrations Work on Unity Catalog

Background: What a Migration Actually Is

A migration is a versioned, ordered, reversible change to a schema — one file that says exactly how to get a database from state N to state N+1, and, ideally, back again. Instead of "someone ran an ALTER TABLE by hand in July and nobody wrote it down," a migration tool keeps every schema change as code: reviewed like any other pull request, applied in a known sequence, and re-runnable against any environment that needs to reach the same state. Alembic is the migration tool for the SQLAlchemy ecosystem, and on a traditional relational database — Postgres is the common case — its job is well-worn: diff your Python model classes against the live database, generate the DDL that reconciles the difference, and track which migration a given database is currently at via a small bookkeeping table it manages itself, conventionally named alembic_version. I've written about that traditional case already: Alembic against Postgres, tracking schema history for a FastAPI service, is the straightforward version of this story.

None of that tooling assumed a lakehouse would ever be the target. Alembic talks to a database through SQLAlchemy's dialect abstraction — a layer that translates generic schema operations into the specific SQL a given engine understands — and until relatively recently there was no dialect that let SQLAlchemy, and therefore Alembic, speak to Databricks at all. Databricks' own answer to that gap is databricks-sqlalchemy, the official dialect that lets SQLAlchemy issue queries and DDL against Delta tables through Unity Catalog. It's what makes pointing Alembic at a lakehouse possible in the first place, rather than a resourceful workaround. It does not, on its own, make Alembic's deeper assumptions about what "a database" guarantees suddenly true for Unity Catalog — that gap is what this article is actually about.

Why This Was Worth Doing

The pitch for migrations-as-code on a lakehouse sounds like the same pitch it is everywhere else: version history in Git instead of tribal knowledge, a reviewable diff instead of a notebook cell someone ran once, an audit trail of who changed what schema and when. All true, and all beside the actual point that made it worth doing here. The real pain point was narrower and more concrete: recreating the exact same set of tables, columns, and constraints across workspaces, on demand, without it turning into a person re-running a folder of DDL notebooks by hand and hoping nothing in that folder was stale. That need shows up in a handful of recurring situations:

• A fresh dev workspace, stood up for someone new joining the project.
• A disaster-recovery rebuild, where the schema needs to exist again before anything else can be tested.
• A brand-new environment for a project that didn't exist six months earlier.

A notebook someone forgot to update, run against a brand-new workspace, doesn't fail loudly — it just produces a schema that's subtly different from every other environment, and nobody notices until a job breaks against it weeks later. Alembic's actual job here was making "stand up an identical schema somewhere new" a single deterministic command instead of a manual checklist.

Getting that cross-workspace guarantee on a lakehouse meant pointing Alembic at Databricks Unity Catalog through the official dialect — and finding that roughly half of what Alembic assumes about "a database" turns out not to hold there.

The Problem

The plan was straightforward on paper: define tables as SQLAlchemy models, let Alembic autogenerate the DDL, apply it to a Databricks SQL warehouse, and get the same migrations-as-code discipline on the lakehouse that a relational backend gets for free. The first migration ran cleanly. The second one — 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 supplying that table sometimes doesn't have a value for it yet (a late-arriving dimension key, a field that's genuinely optional in Delta but declared as part of the "primary key" for merge-matching purposes).

That single failure pointed at a bigger mismatch. Alembic's autogenerate logic — and SQLAlchemy's PrimaryKeyConstraint underneath it — encodes an assumption specific to systems like Postgres: a primary key column is, definitionally, NOT NULL and enforced by the engine. Unity Catalog's primary key constraints are informational. They document intent — this is the natural key, this is what a merge should match on — but the platform doesn't reject a null value or a duplicate at write time. Treating a Unity Catalog "primary key" as if it carried the same enforcement guarantee as a Postgres one was the root cause, not a one-off column problem.

Where Else "Database" Stopped Being a Safe Assumption

Once the nullability issue was understood, three more places where Alembic's Postgres-shaped assumptions didn't transfer showed up in quick succession:

• 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, not just a schema, and the naming convention had to encode environment: 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 — a normal sys.path. Running inside a Databricks job, the source tree wasn't installed as a package; it lived in a workspace path that Python's import machinery didn't know about. The first fix was a hardcoded sys.path.append(...) straight into env.py, pointing at a specific user's workspace folder — it worked on one machine and broke the moment anyone else tried to run it. The durable fix was resolving the source path relative to the repo root at runtime instead of hardcoding an absolute one, so env.py worked identically for any user or job cluster that checked out the same repo.
• Config validation drift. A config model gating which tables were eligible for migration relied on Pydantic's v1 behavior for a field with default=None — under v1, an explicitly-passed None and an omitted field were indistinguishable, and the validation logic depended on that. Pydantic v2 tightened this. A field that used to silently accept "not provided" started rejecting configs that had always worked, and the fix was making the "not provided" case explicit instead of relying on a default value to paper over it.

The Alternatives

Before settling on "make Alembic work correctly against Unity Catalog's real semantics," two other paths were considered.

Drop Alembic, apply DDL by hand. Databricks notebooks can run CREATE TABLE and ALTER TABLE directly, and plenty of lakehouse pipelines are built exactly that way — DDL as a one-off notebook cell, no migration history. It avoids the whole class of Alembic/Unity Catalog mismatch entirely, at the cost of losing everything migrations-as-code buys: no ordered history of schema changes, no repeatable upgrade path across environments, no single source of truth for what a table's current shape is supposed to be.

Keep Alembic, but never trust autogenerate. Write every migration by hand, using autogenerate only as a rough first draft to edit. This sidesteps the nullability bug specifically — a human reviewing the diff would catch a wrongly-inferred NOT NULL before applying it — but it throws away autogenerate's actual value: catching the migrations a human forgot to write in the first place, not just formatting the ones they remembered.

The Decision

Keep Alembic, keep autogenerate, and fix the model layer so its assumptions matched Unity Catalog's actual behavior instead of Postgres's. Concretely, that meant a shared BaseColumn class explicit about nullability regardless of primary-key status:

class BaseColumn:
    """Column defaults for tables mapped onto Unity Catalog.

    Unlike a Postgres primary key, a Unity Catalog PRIMARY KEY
    constraint is informational only — it documents the natural
    key for merges, but isn't enforced at write time. Modeling a
    PK column as SQLAlchemy's default NOT NULL produces DDL that
    rejects perfectly valid Delta writes.
    """
    nullable: bool = True

    def __init__(self, *args, **kwargs):
        kwargs.setdefault("nullable", self.nullable)
        super().__init__(*args, **kwargs)

Every table's columns inherit this default, so nullability has to be explicitly overridden to False for the rare column that genuinely can't be absent, instead of implicitly inherited from primary-key status the way SQLAlchemy does it for a traditional relational engine. That single change was a bigger fix than it looks — it meant every existing table definition using the default primary-key behavior needed a migration to relax its NOT NULL constraints to match what Unity Catalog was actually willing to store.

The three-level namespace was handled by making catalog and schema both first-class, environment-resolved values rather than a schema string with an implicit catalog:

def target_metadata_for(environment: str) -> MetaData:
    """Resolve the fully-qualified catalog.schema for a given environment.

    Table objects declare their table name only; catalog and schema
    are injected here so the same model definitions work unmodified
    across dev, staging, and production.
    """
    catalog = f"analytics_{environment}"
    schema = "core_serving"
    return MetaData(schema=f"{catalog}.{schema}")

And the sys.path hack was replaced with a path resolved relative to the repository root inside env.py, computed once at import time rather than hardcoded per developer:

# env.py
import sys
from pathlib import Path

REPO_ROOT = Path(__file__).resolve().parents[2]
sys.path.insert(0, str(REPO_ROOT / "src"))

Autogenerate diffing your models against the live catalog is only trustworthy once the models tell the truth about what the catalog actually enforces. Fix the model, and the diff becomes reliable; fix the diff by hand every time, and you've quietly opted back into writing every migration manually.

The First Real Migration, and the Table Alembic Didn't Know Existed

The very first time this ran against a target environment, Alembic looked for its own bookkeeping table — alembic_version, the single row that tracks which migration a database is currently at — and didn't find one, because nothing had ever run Alembic against that catalog before. Creating it explicitly, once, as its own migration, was the unglamorous but necessary first step before any table-level migration could be trusted to run in the right order on a fresh environment:

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

After that, reconciling the models against the live catalog was iterative rather than a single clean pass — several rounds of running the migration, comparing the resulting DDL against what Unity Catalog actually created, and correcting a model field that still didn't match. Delta's type system and constraint model don't map onto SQLAlchemy's dialect abstractions as completely as Postgres's do, and the gap only closes by testing against the real catalog, not by reading the SQLAlchemy documentation more carefully.

The Outcome

Schema changes on the lakehouse side now go through the same migrations-as-code discipline as the Postgres-backed services — versioned, reviewed, and replayable across environments — instead of being one-off DDL run by hand from a notebook. But the payoff that actually mattered was the one from the opening: standing up a new workspace, or rebuilding an environment from nothing, now means pointing Alembic at an empty catalog and running upgrade head — not a person working through a folder of DDL notebooks in the right order and hoping none of them had drifted since the last time.

None of that cost came from wiring Alembic up. It came from correcting every place the tool's Postgres-shaped defaults silently assumed guarantees Unity Catalog doesn't provide:

• Nullability — the expensive one, requiring a migration to relax NOT NULL across every existing table using the default primary-key behavior.
• Namespace depth — two levels assumed, three actually needed.
• Import resolution — a hardcoded path that worked on one machine and nowhere else.
• A validation library's major-version change — silent until it wasn't.

Each of those, left uncorrected, was individually capable of producing a migration that looked correct and wasn't — and a migration that looks correct and isn't is exactly what turns "recreate this schema somewhere new" back into a manual, error-prone exercise. Closing each gap is what migrations-as-code exists to prevent in the first place.