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 across kinds (a function that reads a view follows the view; schema.name references resolve exactly, a bare name only when one object has it). 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, and a view, function or procedure that names a column the rebuild adds follows the note there, with everything that depends on it, instead of failing in the live part.

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.