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). Constraints, defaults, identity 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 and a sorted table of every difference (kind, object, change, detail), destructive ones marked.
• deploy.sql: the promotion script in run order: new schemas, new tables, in-place column additions, then functions, views and procedures as CREATE OR ALTER in dependency order. Every DROP and every change that can lose data (a dropped or retyped column, a NOT NULL column added to an existing table, a name whose kind differs between the sides) is at the end, commented out under a DESTRUCTIVE banner. On a Fabric Warehouse or Lakehouse a column change is a rebuild note with the source shape, because those targets do not alter columns in place.

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.

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.

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; 1 on an error, printed as a short state (connection_failed, side_invalid, pyodbc_unavailable, odbc_driver_unavailable, az_unavailable, snapshot_missing, 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.