williamlimasilva/sql-server-table-reconciliation

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 |

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:

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. |

Example invocations

Single table 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

Wildcard with Entra auth and CSV output:

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:

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:

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
• Connection MUST use connection string format, not keyword arguments (kwargs like encrypt=True throw errors)
• 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