SQL Server Table Reconciliation

SQL Server table reconciliation

Compare identical SQL Server tables across two instances with Python, mssql-python, Apache Arrow, and primary-key based row matching, then produce a reconciliation report for schema drift, missing rows, extra rows, and mismatched values.

When to invoke

• "Compare these SQL Server tables between production and staging."
• "Validate a data migration with a reconciliation report."
• "Find row mismatches after this ETL load."
• "Detect schema drift between two SQL Server databases."
• "Use mssql-python and Arrow to compare large tables."

Inputs

Collect these before execution:

Prerequisites and context

Install runtime packages only when the environment lacks them:

pip install mssql-python pyarrow pandas

The bundled script lives at scripts/reconcile.py and must be run from the skill package or with the correct relative path.

Procedure

1. Collect connection details for source and target.
2. Identify the primary key or composite key; auto-detect from metadata, then ask the user if no key is found.
3. Detect schema differences before row comparison.
4. Extract data through Apache Arrow with cursor.arrow().
5. Compare by primary-key join, never by positional row order.
6. Normalize types, compare columns, and generate the requested report format.

python scripts/reconcile.py     --source-server <source_server>     --source-database <source_database>     --target-server <target_server>     --target-database <target_database>     --tables "<table_spec>"     --auth <sql|entra>     --chunk-size <chunk_size>     --output <console|csv|json>

Optional arguments:

Example with SQL auth:

python scripts/reconcile.py     --source-server prod-server.database.windows.net     --source-database ProdDB     --target-server staging-server.database.windows.net     --target-database StagingDB     --tables "dbo.Orders"     --auth sql     --output console

Example wildcard with Entra auth and CSV:

python scripts/reconcile.py     --source-server prod-server.database.windows.net     --source-database ProdDB     --target-server staging-server.database.windows.net     --target-database StagingDB     --tables "dbo.*"     --auth entra     --output csv

Comparison rules

For tables above 1M rows, pre-check row hashes and fetch full rows only for mismatches:

SELECT {pk_cols},
       HASHBYTES('SHA2_256', CONCAT_WS('|', col1, col2, ...)) AS row_hash
FROM {table}

Performance strategy

Gotchas

• Use mssql-python, not pyodbc or pymssql; the workflow depends on Arrow support.
• Use connection string format; keyword arguments such as encrypt=True throw errors.
• Never compare without a primary key; positional comparison produces false mismatches.
• Do not print credentials in reports, errors, or logs.
• Handle connection failures with retry logic instead of failing silently.

Progressive disclosure and bundled resources

• scripts/reconcile.py: standalone reconciliation script that accepts the source, target, table, auth, chunking, and output arguments described above.

Report format

For multi-table runs, use compact per-table rows and expand detail only for tables with FAIL or DIFFERENCES FOUND status. Include examples such as dbo.JOBS when showing multiple tables.

Reconciling dbo.EMPLOYEES...
Reconciling dbo.DEPARTMENTS...
Reconciling dbo.JOBS...

=== Summary: 2 passed, 1 failed, 0 skipped / 3 tables ===

MUST requirements in this skill are hard constraints, not preferences.

Output template

## SQL Server reconciliation

**Status:** identical | differences found | blocked
**Source:** `<source server>/<source database>`
**Target:** `<target server>/<target database>`

| Table | Source rows | Target rows | Missing | Extra | Mismatches | Result |
| --- | ---: | ---: | ---: | ---: | ---: | --- |
| `dbo.EMPLOYEES` | 107 | 107 | 0 | 0 | 0 | IDENTICAL |
| `dbo.DEPARTMENTS` | 27 | 27 | 0 | 0 | 3 | DIFFERENCES FOUND |

### Detail for failed tables
- `<schema.table>`: <schema drift, sample missing PKs, sample mismatched columns>

### Validation
- Primary key detection: <pass|fail>
- Extraction mode: <single Arrow|chunked|hash pre-check>
- Credentials handling: <environment|getpass|blocked>

Quality gate

• mssql-python, Apache Arrow via cursor.arrow(), and pandas comparison are used.
• Source and target schemas are checked before row comparison.
• Every table is compared by primary key or explicitly blocked when no key exists.
• Decimal, string, datetime, and NULL comparison rules are applied.
• Large tables use chunking or HASHBYTES('SHA2_256', CONCAT_WS(...)) optimization.
• Credentials come from MSSQL_USER, MSSQL_PASSWORD, or getpass and are never logged.