Where RetailPulse is tonight
Recap: RetailPulse v0.3 is an orchestrated, quality-gated pipeline - it cleans sales daily, validates them against a Great Expectations suite, and enforces a data contract in CI. So far the output has been a parquet file. Tonight it graduates to a real Postgres table that dashboards and apps can query. And the instant a database is involved, a new risk appears: the schema is state that lives outside your repo and drifts by hand. DBOps is version control pointed at that schema.
sales table, with an Alembic migration that creates the schema and is checked into Git. Migrations that run automatically in CI/CD before the load. And a rehearsed expand-contract rename - price to unit_price - done in safe, reversible steps with a working downgrade, so a schema change in prod never means downtime or a 2am rollback panic.
Why version the database 6 min live
You would never edit production code by SSH-ing into a server and changing a file by hand. Yet that is exactly how most database schemas evolve: someone runs an ALTER in a prod console, the change lives nowhere in Git, and the code and the database silently disagree until a deploy blows up. Versioned migrations end that.
LiveThe problem with un-versioned schema3 min▶
An un-versioned schema fails in ways that are painful precisely because they are invisible until deploy time:
- No source of truth: what does the prod schema actually look like right now? Nobody knows for sure - you have to go query the database and hope dev matches.
- Dev/prod skew: someone adds an index in prod to fix a slow query and never replicates it to dev or staging. The environments quietly diverge.
- No reproducibility: you cannot stand up a fresh environment from Git, because the schema history is not in Git - it is a sequence of manual ALTERs nobody wrote down.
- No review, no rollback: a hand-run ALTER gets no pull request and no CI check, and there is no clean way to undo it.
Versioned migrations fix all four: the schema's entire history becomes a series of ordered files in the repo, reviewed like code, run automatically, and reproducible from scratch. The database stops being the one component that lives outside your DataOps discipline.
The column that existed in prod but not in Git. An analytics team had a reporting column that a DBA had added directly in production months earlier. When they finally automated their deploys, every fresh environment was missing that column and half the dashboards broke - and no one could explain where the column had come from. The schema had a secret history that lived only in one database. Versioned migrations would have made that column a reviewed file with a name and a date, not a mystery.
Self-studyDBOps is just DataOps for the schema2 min read▶
Everything you have built this track applies to the schema too. Migrations go in Git (b1's version-everything rule). They run in CI/CD (b1's automated gate, b2's orchestration). They can be tested and rolled back. DBOps is not a new discipline - it is the same four DevOps moves from session b1, finally pointed at the one component teams most often leave out: the database. If it feels familiar, that is the point. The schema is code now.
Migration tools 6 min live
Three tools dominate schema migrations. They differ in how you author the change - raw SQL, Python, or multi-format - but they all deliver the same core promise: ordered, versioned, forward-only files, checked into Git, run in CI/CD.
LiveFlyway, Alembic, Liquibase - and what they share3 min▶
The three you will meet:
| Tool | You author changes as | Best when |
|---|---|---|
| Flyway | SQL-first, versioned V1__*.sql files | Your team thinks in SQL and wants zero magic - the file is the change |
| Alembic | Python, via SQLAlchemy; can autogenerate | Python shops (RetailPulse) - it can diff your models and draft the migration |
| Liquibase | SQL, XML, YAML, or JSON changesets | Multi-language teams, complex enterprise rollout, DB-agnostic changelogs |
We use Alembic tonight because RetailPulse is a Python project and Alembic can autogenerate a first draft of a migration by comparing your SQLAlchemy models to the live database - then you review and edit it, never trusting it blindly. Whatever you pick, the discipline underneath is identical:
- Ordered: every migration has a version and a parent, so they apply in a strict sequence. The database tracks which version it is at.
- Versioned in Git: each migration is a file, reviewed in a pull request like any code.
- Forward-only in spirit: in prod you roll forward. Downgrades exist for dev and emergencies, but you design changes so you rarely need them.
- Run in CI/CD: migrations apply automatically as a deploy step, before the code that needs the new schema - never by hand in a console.
--autogenerate is a fantastic first draft, not a final answer. It can miss column renames (it sees a drop + an add) and server defaults. Always open the generated file and confirm it does what you meant before committing. A migration is code; review it like code.
Self-studyHow a migration tool tracks state2 min read▶
Every migration tool keeps a small bookkeeping table in the database itself - Alembic calls it alembic_version, Flyway uses flyway_schema_history. It records which migrations have been applied. When you run upgrade, the tool compares that table to the migration files on disk and applies only the ones that are missing, in order. That is the whole trick: the database knows what version it is at, the files describe every version, and the tool reconciles them. It is exactly how git knows which commits you are missing - version control for schema, with the same mental model.
Expand-contract and rollback 4 min live
Here is the migration that scares people: renaming or dropping a column in a live database that running code depends on. A big-bang ALTER ... RENAME breaks every query still using the old name the instant it runs. The expand-contract pattern turns that cliff into a safe staircase.
LiveWhy big-bang ALTERs break prod, and how rollback works3 min▶
The reason a naive rename is dangerous: in a real deploy the code and the schema do not change at the same instant. For a window of seconds to minutes, old code runs against the new schema or vice versa. A single ALTER TABLE sales RENAME price TO unit_price means every query still asking for price - including the app version currently serving traffic - errors immediately. Expand-contract removes that window by never having the old and new worlds be mutually exclusive:
- Expand: add
unit_pricealongsideprice. Old code still works - its column is untouched. - Backfill: copy existing values across and dual-write both columns for new rows, so the two stay in sync.
- Switch reads: deploy code that reads
unit_price. Now nothing depends onprice. - Contract: once you are sure nothing reads the old column, drop it. The rename is complete, and no request ever hit a broken schema.
Rollback is the mirror image. Every Alembic migration has an upgrade() and a downgrade(). If a step goes wrong, alembic downgrade -1 reverses exactly that step. Because each expand-contract step is small and reversible, rollback is a one-liner, not an incident - the opposite of unwinding a big-bang ALTER by hand at 2am.
Self-studyThe rollback that a destructive migration cannot give you2 min read▶
A subtlety worth internalizing: some migrations are not truly reversible, no matter how good your tool is. If a migration drops a column and you have not backed the data up, its downgrade() can recreate the empty column but not the values - they are gone. This is exactly why expand-contract defers the destructive step (the drop) to the very end, after you are certain nothing needs it. The lesson: design migrations so the irreversible part happens last and alone, and never in the same deploy as the change that depends on it. Safe schema change is less about the tool and more about the order.
Load RetailPulse to Postgres plus a first migration ★ 12 min · everyone builds
Give RetailPulse a real database. You will define the sales table as an Alembic migration, apply it to Postgres, and change the load step so the cleaned data lands in the table instead of only a parquet file. The schema is now a versioned file in the repo.
Start Postgres and alembic init migrations from the Try-it command above. Point sqlalchemy.url at your database (via env var, not hardcoded - the b3 config lesson holds).
Create the first migration with alembic revision -m "create sales table". Fill in upgrade() and downgrade() from Prompt A: the seven RetailPulse columns with types and a primary key.
Apply it with alembic upgrade head. Confirm the table exists (\d sales in psql) and that alembic_version now records the revision.
Update the load task to write the cleaned DataFrame into the sales table (df.to_sql("sales", engine, if_exists="append") or an upsert). The parquet can stay as a staging artifact.
Commit the migration file. Your schema history now lives in Git next to the pipeline that fills it.
Migrations in CI/CD plus a safe rename ★ 10 min · build your own
A migration that only runs when you remember it is drift waiting to happen. Now you make migrations run automatically before the load, then rehearse the real thing: renaming price to unit_price with expand-contract, and reversing a step with a downgrade.
Add alembic upgrade head as a step in ci.yml and as the first task in the Airflow DAG, before load. Now schema and code always deploy together, in the right order (Prompt B).
Expand: create migration 0002 that adds a nullable unit_price column. Apply it. Old code that reads price keeps working untouched.
Backfill: in the same or a follow-up migration, copy price into unit_price (Prompt C), and update the load to write both columns for new rows.
Switch reads: point the pipeline and any queries at unit_price, deploy, and verify nothing references price anymore.
Contract + rollback drill: create the migration that drops price, then practice reversing a step with alembic downgrade -1. RetailPulse is now v0.4: schema in version control, migrated in CI/CD, renamed with zero downtime.
The rename that took a checkout down - and the one that did not. One team renamed a column with a single ALTER during a deploy; the still-running app version queried the old name and the checkout page threw 500s for eleven minutes until they rolled back by hand. A second team did the identical rename with expand-contract across three small migrations over two days - add, backfill, switch, drop - and not one request ever failed. Same change, same database. The only difference was the order, and it was the difference between an incident report and a boring Tuesday.
Try it yourself - this week ◐ 45-60 min total
- Finish both demos if you did not live - especially the full expand-contract rename and at least one
alembic downgrade -1. Everyone should reverse a migration once. - Add an index migration: create a migration that indexes
order_date, apply it, then downgrade it. Watch thealembic_versiontable move. - Break it on purpose: write a migration that drops a column with no backfill, downgrade it, and confirm the values do not come back. Feel why the destructive step goes last.
- Try
alembic revision --autogenerateagainst a changed SQLAlchemy model, then read every line of what it produced before trusting it. - Optional reading: the Alembic tutorial and Flyway's "Why database migrations" page - now a review of what you already built.
What this session covers
This track teaches DataOps on an open-source stack from the tools' official docs, with AWS mapping notes. This page covers:
Three questions before you go 🎯 ◐ 90 seconds
1 · Why is an un-versioned schema a DataOps problem?
The schema is state outside your repo. Without versioned migrations it changes by hand with no source of truth, no review, no rollback, and no way to stand up a fresh environment from Git.
2 · What do Flyway, Alembic, and Liquibase all share, despite different authoring formats?
SQL-first, Python, or multi-format - the authoring differs, but the discipline is identical: ordered versioned files in Git, applied in sequence by a tool that records the current version in the database.
3 · Why rename a column with expand-contract instead of a single ALTER?
A big-bang rename breaks every query still using the old name the instant it runs. Expand-contract keeps both worlds valid at once and defers the destructive drop to the very end, so there is no downtime and rollback stays a one-liner.