how-to-schema-consolidation

Input - an estate that grew by cloning

Everrest scaled by standing up a new server for each region and workload - and every server setup cloned the entire schema. Years later the same logical table exists as up to nine physical copies across nine server-schemas, and nobody documented the clones. Three inputs describe the mess.

Generate sample data - the estate at real scale

A seeded generator builds 1,300 logical tables, replicates each across a random subset of the 9 servers (avg ~6.6 copies), and drifts every copy - dropping optional columns, adding server-unique ones, occasionally changing a dtype. The result is ~8,600 physical tables that look identical and are not.

# replicate each logical table across a random subset of 9 servers
present = rng.random(N_SERVERS) < 0.735          # avg ~6.6 copies -> ~8,600 physical

# drift each copy: optional columns come and go, server-unique columns appear
for c in base_opt[lid]:
    if rng.random() < 0.85: cols.append(c)     # 15% chance a column is missing here
if rng.random() < 0.12: cols.append(f"{schema}_ext_{...}")  # a singleton column

Objective - merge to one truth, without losing anything

The goal is one governed copy of each table. The trap is that the copies are not identical, so any careless merge either drops columns or fuses incompatible types.

Find-skills - the consolidation toolbox

Cataloging at this scale is a solved problem - reuse the tools. The one piece worth writing is the schema-diff engine that turns "these look the same" into a column-by-column truth.

Build - audit the estate, then diff before you merge

First size the problem across all 9 servers, then zoom into one table and prove exactly how its copies differ. Grab the real code below.

Size the sprawl

The documentation gap. The dictionary covers 1,300 logical tables - 15% of the 8,599 physical. The other 7,299 are undocumented clones.

Redundancy. Most logical tables exist on 6-8 servers; 7,299 physical tables are pure clones that consolidation can retire.

Cloned wholesale. Each server-schema holds ~950 tables - every new server copied the whole estate, not a subset.

They are not identical. Almost every multi-server table has drifted; 56% have a column on one server only, 63% have a dtype conflict. A blind UNION loses data.

Diff one table across all 9 servers

The step you cannot skip before merging. For logical table L0646, compare every column across the 9 servers that host it. Amber = a column that exists on only one server, or whose dtype disagrees.

The amber diagonal at the bottom is five server-unique columns - each present on exactly one server. A naive intersection would delete all of them.

example_merged.sql ↗Copy

CREATE TABLE l0646_merged (
  server_source VARCHAR,   -- lineage
  id BIGINT,
  risk_score VARCHAR,   -- TYPE CONFLICT DATE/TIMESTAMP
  srv03_ext_3 VARCHAR, -- only 1 server
  srv09_ext_3 VARCHAR, -- only 1 server
  warehouse_id VARCHAR, -- TYPE CONFLICT TEXT/VARCHAR
  ...
);

Merge safely. The intersection keeps 6 columns; the superset keeps all 20. The generated DDL unions every column, tags each singleton, and widens type conflicts to a safe type - nothing dropped.

Expert review - a check that changes the merge

A panel of five senior reviewer agents - each with 10+ years in data platform, governance and migration - reviewed the first consolidation approach. The naive plan (a UNION of the common columns) ran and looked done, but it would have quietly deleted every server-unique column. Every fix below is in the engine above.

# v1 (before): merge on columns common to every server
common = set.intersection(*[cols_on(s) for s in servers])   # L0646 -> 6 columns
#   -> silently drops 14 columns, incl. 5 that exist on one server only

# v2 (after): merge on the superset, tag singletons, widen type conflicts
d = diff_table(columns, "L0646")
ddl = merged_ddl(d)                              # -> 20 columns, nothing lost