coop-schema-compare

Deterministic schema compare between two SQL schemas, live or as code, with an ordered promotion script. Built for Microsoft Fabric Warehouse, where the built-in deployment pipeline gives you no script to read; it works the same on Azure SQL Database, Fabric SQL database and SQL Server. A side is a live database (by SQL endpoint or by Fabric workspace and warehouse name), a committed snapshot, or a repo folder of .sql files, in any pairing: repo against repo, live against live, repo against live. It reads only and applies nothing: the output is a report and a script a person reviews and runs through their own deployment path.

Part of the coop suite (see coop-agent). coop calls it as its schema_compare tool; you can run it yourself.

Install

pipx install coop-schema-compare

Needs Python 3.10+, the Microsoft ODBC Driver 18 for SQL Server, and either an access token in SQL_ACCESS_TOKEN or the Azure CLI signed in (az login).

Use

# dev against prod, live on both sides, named by workspace and warehouse
coop-schema-compare compare "fabric://Contoso Dev/QE_WH" "fabric://Contoso Prod/QE_WH" --source-environment dev --target-environment prod

# the same by SQL endpoint host
coop-schema-compare compare contoso-dev.datawarehouse.fabric.microsoft.com/QE_WH contoso-prod.datawarehouse.fabric.microsoft.com/QE_WH --source-environment dev --target-environment prod

# the repo (schema as code) against the live prod Warehouse
coop-schema-compare compare .\warehouse\QE_WH.Warehouse "fabric://Contoso Prod/QE_WH" --target-environment prod

# two branches of the repo, checked out side by side: no connection
coop-schema-compare diff ..\QE_WH-main\warehouse ..\QE_WH-feature\warehouse

# commit a snapshot of each side, then diff the folders offline (no connection)
coop-schema-compare snapshot "fabric://Contoso Dev/QE_WH" --out catalog/dev --environment dev
coop-schema-compare snapshot "fabric://Contoso Prod/QE_WH" --out catalog/prod --environment prod
coop-schema-compare diff catalog/dev catalog/prod

A side is one of:

The snapshot layout is the one coop's coop catalog snapshot writes, so either tool's folder is a valid side for the other.

The repo side

A repo side is a folder of T-SQL source: a Fabric Warehouse Git export or SQL database project (one file per object under folders such as dbo/Tables/), or a hand-kept sql/ folder. Files are read in path order and split on GO; a batch counts when its first statement is CREATE SCHEMA, CREATE TABLE, or CREATE [OR ALTER] VIEW | PROCEDURE | FUNCTION. Everything else (post-deployment scripts, ALTER, INSERT, .sqlproj, the bin/ and obj/ folders) is ignored, and the report notes how many files held no object, so a mis-pointed folder is visible. Table columns are read from the column list with the type rendered as the live catalog renders it (decimal(18, 2), varchar(max); a length or precision the source leaves out gets the engine default, varchar(1) or datetime2(7), as the engine itself would store it; write them out for an exact compare). An IDENTITY column is read as identity on both sides (the live side from sys.identity_columns); constraints, defaults and computed-column expressions are out of scope, as on the live side. A view, procedure or function compares by its text with a leading comment banner, OR ALTER and a trailing ; ignored, so a file and the catalog's copy of the same object match.

compare and diff write two files under --out (default ./schema-compare/<source>-to-<target>/):

• report.md: both sides' object counts, the choices the run used, and one entry per object: its kind, the detected change, the deployment action and each column change, with destructive ones in bold.
• deploy.sql: the promotion script, built from the same per-object record so the two files agree. Sections run in order: new schemas, new tables, table changes, then functions, views and procedures as CREATE OR ALTER in dependency order across kinds (schema.name references resolve exactly, a bare name only when one object has it), modules waiting on a manual table rebuild, objects replaced by another kind, removals, and what a person must do. Every object gets its own comment-only note batch, so notes never end up inside a saved view or procedure.

Destructive statements (a DROP, a dropped or retyped column, a NOT NULL column added to an existing table, a table rebuilt by policy) are runnable by default, because the person reviews and runs the script. The report and the note batch mark each one DESTRUCTIVE. --destructive commented prefixes every destructive statement line with exactly --, so removing those two characters restores it byte for byte. The tool never runs the script.

Tables follow the target's ALTER rules. A Fabric Warehouse adds nullable columns and drops columns in place; a retyped column, a nullability change or an identity change needs a rebuild. By default that is a manual rebuild note with the source shape (keep the rows with CREATE TABLE AS SELECT); name the table's schema with --rebuild-tables-in NAME (repeatable) to script DROP TABLE + CREATE TABLE instead, empty, which suits dimension and fact schemas reloaded by their pipelines. A Lakehouse SQL endpoint cannot change tables with T-SQL, so every table change there is a note. Identity is scripted in the target's form: bigint IDENTITY with no seed or increment on Fabric.

deploy.sql ends with a -- coop generated: sha256 ... line. When a rerun finds the file edited since it was written, the edited copy is kept as deploy.edited-<stamp>.sql before the new one is written.

Direction is "what the target needs to match the source". A view or procedure reformatted by an editor is not a change: definitions are compared with line endings and runs of whitespace folded outside string literals and quoted names, a leading comment banner dropped and CREATE, CREATE OR ALTER and ALTER read alike. Whitespace inside a string literal or a quoted name is a change. Comments and string literals never order the script; objects that name each other in a circle go to the manual section for a person to order. The schemas every database has (dbo, guest, sys, INFORMATION_SCHEMA) are never created or dropped; the objects in them still compare.

What is compared

Schemas; tables (each column's name, type, nullability and position); views, procedures and functions by definition. Constraints, indexes, permissions and row data are out of scope and the report says so.

Every schema is compared unless compare or diff is narrowed: --schema NAME keeps only the schemas named and --exclude-schema NAME leaves one out (each repeatable; --exclude-schema wins; names match without regard to case). Both sides are filtered before the diff, so a left-out schema is never added, altered or dropped, and the report's notes name the filter.

coop-schema-compare compare "fabric://Contoso Dev/QE_WH" "fabric://Contoso Prod/QE_WH" --schema sales --schema finance

Exit codes and output

0 the compare ran; --fail-on-diff gives 2 on any difference and --fail-on-destructive gives 3 on a destructive one, for CI gates; either flag gives 2 when a side was not read whole (a list cut at the compare cap, or objects a snapshot skipped): the JSON says "complete": false, and an object such a side may hold is an unknown change, never an add or a drop. 1 on an error, printed as a short state (connection_failed, side_invalid, pyodbc_unavailable, odbc_driver_unavailable, az_unavailable, snapshot_missing, snapshot_incomplete (a file the manifest lists is missing, unreadable, outside the folder or without its snapshot header), output_not_snapshot, workspace_not_found, warehouse_not_found, fabric_forbidden). --json prints the result as one object. No token, credential, server name, driver text or exception text is ever printed or written.

Authentication

The token for https://database.windows.net/ is read from SQL_ACCESS_TOKEN (or the variable --token-env names). When it is empty, the Azure CLI mints one (az account get-access-token, --tenant to pin the tenant). The token goes to the driver as a connection attribute and nowhere else. A fabric:// side also needs a token for https://api.fabric.microsoft.com, from FABRIC_ACCESS_TOKEN (or --fabric-token-env) or the Azure CLI the same way; it is used for two read-only GETs (list workspaces, list the workspace's warehouses) and nothing else. Folder sides need no token.

Why not SqlPackage or a deployment pipeline

Fabric deployment pipelines and Git integration promote a Warehouse through DacFx: no script to read or edit, a column change drops and recreates the table, permissions do not travel, and both sides must be workspaces in one tenant and pipeline. SqlPackage supports the Warehouse but is a separate .NET tool on each machine with a publish you cannot review line by line. This tool gives you the plain script instead. SqlPackage remains the right tool for a full-fidelity dacpac of a large estate.

Develop

python -m pip install -e .
python -m unittest discover -s tests -v

Tests run offline with fake cursors; no database or network is needed.