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, removals, and what a person must do. An object replaced by another kind (a view that became a table, or the reverse) is dropped and recreated inside the section its new kind belongs to, so dependents still find it. 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 create, change or drop tables with T-SQL, so every table change there, a new or removed table included, is a note for a person. 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. When a repo side could not be read whole (more files than the limit, an oversized or unreadable file), an object it lacks compares as unknown, never as removed, and the report's notes say which files were skipped.

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.