sql-server-table-reconciliation
/sql-server-table-reconciliation
Use when: comparing SQL Server tables across instances, data migration validation, ETL verification, row mismatch detection, schema drift, reconciliation report, production vs staging comparison. Uses mssql-python driver with Apache Arrow for fast columnar data transfer and comparison.
Assessment and security
- Files in this package
- 4
- Scripts
- 3
- HTML comments
- 0
- URLs in SKILL.md
- 1
No signals detected. Repository changed on 25.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
- 3 scripts in this package. Review them before installing, as Claude may execute them.
Scripts: scripts/reconcile.py, tests/test_reconcile_sql.py, tests/test_tls_connection.py
What it does and when to use it
Use when: comparing SQL Server tables across instances, data migration validation, ETL verification, row mismatch detection, schema drift, reconciliation report, production vs staging comparison. Uses mssql-python driver with Apache Arrow for fast columnar data transfer and comparison.
Original author description
Use when: comparing SQL Server tables across instances, data migration validation, ETL verification, row mismatch detection, schema drift, reconciliation report, production vs staging comparison. Uses mssql-python driver with Apache Arrow for fast columnar data transfer and comparison.
How it works
- Workflow
- Collect Inputs
- Bundled Script
- Comparison Rules
- Hash-Based Optimization (for large tables)
- Report Format
- Performance Considerations
- Constraints
SKILL.md preview
---
name: sql-server-table-reconciliation
description: "Use when: comparing SQL Server tables across instances, data migration validation, ETL verification, row mismatch detection, schema drift, reconciliation report, production vs staging comparison. Uses mssql-python driver with Apache Arrow for fast columnar data transfer and comparison."
---
# SQL Server Table Reconciliation
Compare identical tables across two SQL Server instances using Python with `mssql-python` driver and Apache Arrow. Detect missing rows, column mismatches, schema drift, and produce a reconciliation report.
## Workflow
1. Collect connection details for source and target
2. Identify primary key / composite key
3. Detect schema differences
4. Extract data via Arrow for efficient columnar transfer
5. Compare rows and columns
6. Generate reconciliation report
## Collect Inputs
| Parameter | Required | Description |
|-----------|----------|-------------|
| Source server | Yes | Source SQL Server (e.g. `prod-server.database.windows.net`) |
| Source database | Yes | Source database name |
| Target server | Yes | Target SQL Server (e.g. `staging-server.database.windows.net`) |
| Target database | Yes | Target database name |
| Tables | Yes | Comma-separated `schema.table` names, or `schema.*` wildcard (e.g. `dbo.Orders,dbo.Items` or `dbo.*`) |
| Auth mode | Yes | `sql` (user/password) or `entra` (Azure AD/token) |
| Primary key | Auto-detect | Column(s) forming the row identity. Auto-detect from metadata if not provided. |
| Columns to compare | All | Subset of columns, or all non-PK columns |
| Chunk size | `100000` | Rows per batch for large tables |
| Output format | `console` | `console`, `csv`, `parquet`, or `json` |
For SQL safety, schema, table, primary-key, and column names must be simple
identifiers: ASCII letters, digits, and underscores, starting with a letter or
underscore, up to 128 characters. The script quotes accepted identifiers in
generated SQL and rejects other input before executing a query. SQL Server
names containing spaces or punctuation are not supported by this script.
Connections require TLS and validate the SQL Server certificate by default.
Use a trusted certificate on production and staging servers. For a controlled
local development server with a self-signed certificate only, pass
`--trust-source-self-signed-certificate` or
`--trust-target-self-signed-certificate` for that endpoint only. Each option
disables certificate validation for its named endpoint and prints a warning.
Never use either option for production data.
## Bundled Script
The reconciliation logic is provided as a standalone script at `scripts/reconcile.py`. Invoke it with the appropriate arguments based on user inputs:
```bash
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
| Argument | Description |
|----------|-------------|
| `--primary-key` | Comma-separated PK column(s). Omit to auto-detect. |
| `--columns` | Comma-separated columns to compare. Omit to compare all non-PK columns. |
| `--trust-source-self-signed-certificate` | Development only; skip source certificate validation for a controlled self-signed server. |
| `--trust-target-self-signed-certificate` | Development only; skip target certificate validation for a controlled self-signed server. |
### Example invocations
Single table with SQL auth:
```bash
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
```
Wildcard with Entra auth and CSV output:
```bash
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
```
### Prerequisites
Install required packages before running:
```bash
pip install mssql-python pyarrow pandas
```
## Comparison Rules
- **Normalize types before comparing**: cast decimals to same precision, trim strings, normalize datetime to UTC
- **NULL handling**: `NULL == NULL` is considered a match (both sides missing = no diff)
- **Ignore row order**: always compare by PK join, never positional
- **Large tables**: chunk extraction with `OFFSET/FETCH` or `ROW_NUMBER()` partitioning
## Hash-Based Optimization (for large tables)
When table has >1M rows, generate a hash pre-check:
```sql
SELECT {pk_cols},
HASHBYTES('SHA2_256', CONCAT_WS('|', col1, col2, ...)) AS row_hash
FROM {table}
```
Compare hashes first; only fetch full rows for mismatched hashes. This reduces data transfer significantly.
## Report Format
```
Reconciling dbo.EMPLOYEES...
Reconciling dbo.DEPARTMENTS...
Reconciling dbo.JOBS...
--- dbo.EMPLOYEES ---
Source: 107 Target: 107
Missing: 0 Extra: 0 Mismatches: 0
Result: ✓ IDENTICAL
--- dbo.DEPARTMENTS ---
Source: 27 Target: 27
Missing: 0 Extra: 0 Mismatches: 3
Result: ✗ DIFFERENCES FOUND
--- dbo.JOBS ---
Source: 19 Target: 19
Missing: 0 Extra: 0 Mismatches: 0
Result: ✓ IDENTICAL
=== Summary: 2 passed, 1 failed, 0 skipped / 3 tables ===
```
When a single table is provided, include full detail (schema drift, sample rows, mismatches). When multiple tables, use the compact per-table format above with full detail only for tables with `FAIL` status.
## Performance Considerations
| Scenario | Strategy |
|----------|----------|
| < 100K rows | Single Arrow fetch, in-memory pandas compare |
| 100K–1M rows | Chunked extraction (100K batches), streaming comparison |
| > 1M rows | Hash pre-check → only fetch mismatched rows |
| Wide tables (100+ cols) | Compare PK + hash first, drill into specific columns on mismatch |
| Network-constrained | Use Arrow columnar format (10-50x smaller than row-by-row) |
## Constraints
- Always use `mssql-python` driver (not pyodbc, pymssql)
- Always use Apache Arrow via cursor (`cursor.arrow()`) for data extraction
- Use `mssql-python` connection keyword values as documented by Microsoft, for example `encrypt="yes"` and `trust_server_certificate="no"`; do not pass Boolean values such as `encrypt=True`. Keyword values keep SQL passwords containing semicolons out of an interpolated connection string. See [mssql-python connection strings](https://learn.microsoft.com/en-us/sql/connect/python/mssql-python/connection-strings).
- Never compare without identifying PK first — ask user if auto-detect fails
- Handle connection failures gracefully with retry logic
- **Never hardcode credentials** in generated scripts — use `os.environ` / `getpass` (env vars: `MSSQL_USER`, `MSSQL_PASSWORD`)
- Do not print credentials in output or logs
- Use parameterized queries (`?` placeholders) for metadata lookups — never f-string interpolate user input into SQL
Files in this package (4)
- SKILL.md 7 KB
- scripts/reconcile.py 15 KB
- tests/test_reconcile_sql.py 3 KB
- tests/test_tls_connection.py 5 KB
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · OKHP3/skillz | 100 | MIT |
| github/awesome-copilot | 100 | MIT |
Database snapshot: 13.11.2026 · package version 8b214443ab35 (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 96write-script-mssql
MUST use when writing MS SQL Server queries. - 100data-formats
Use this skill when asked about Apache Arrow, Parquet, Avro, ORC, Arrow Flight, columnar storage, row-oriented storage, compression, schema evolution, data file format, columnar vs row-oriented, file format comparison, or data serialization. This skill enforces: columnar format… - 100mssql
Execute read-only SQL queries against multiple Microsoft SQL Server databases. Use when: (1) querying MSSQL/SQL Server databases, (2) exploring database schemas/tables, (3) running SELECT queries for data analysis, (4) checking database contents. Supports multiple database… - 100mssql-insights-ops
Operate the MSSQL MCP server (`MssqlMcp.exe`) effectively: introspect schema, query data safely, and use the AI Insights layer (cached object summaries + DDL audit) to investigate a database with low-token, repeatable workflows. Use when the user asks to "investigate",… - 100azuresql-db-from-sql-server
Migrates a local SQL Server setup to the Azure SQL Database container for Azure-faithful local development. Use when a project already uses mcr.microsoft.com/mssql/server, mssql/server, an sqlcmd plus SA password docker setup, a "SQL Server in docker" or "local mssql container",… - 95sql-server
SQL Server and Azure SQL Database with T-SQL, indexes, stored procedures, and performance optimization patterns. USE WHEN: user mentions "SQL Server", "T-SQL", "MSSQL", "Azure SQL", "SQL Server indexes", "stored procedures", "SQL Server performance" DO NOT USE FOR: PostgreSQL -…