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  Mismat