how-to-schema-consolidationlisted

Audit a sprawling multi-server data estate (many cloned schemas that have drifted) and safely consolidate the copies of a table into one, using a column-level schema diff. Use when handed many servers/schemas that each cloned the same tables, or asked to "consolidate schemas", "merge duplicate tables", "compare schemas across servers", "schema diff", "which columns differ across environments", "dedupe tables", "measure data dictionary coverage", or before merging any table that exists in more than one database. Walks the 6-step pipeline - input, sample data, objective, find-skills, build (coverage + redundancy + drift audit, schema-diff matrix, merged superset DDL), expert review - and never merges on the intersection.
phoebefu6/phoebe-data-skills · ★ 1 · API & Backend · score 72

Install: claude install-skill phoebefu6/phoebe-data-skills

# how-to-schema-consolidation Infrastructure-layer skill (sibling to how-to-schema-and-warehouse). The realistic enterprise problem: one server per region/workload, each cloning the whole schema, copies drifting over time, and a data dictionary that only ever covered the logical tables. Output is an honest audit of the sprawl plus a safe, column-complete merge - never a blind UNION that drops server-unique columns. Showcase walkthrough (Everrest estate: 9 servers, 8,600 tables): https://github.com/phoebefu6/phoebe-data-skills - `docs/how-to-schema-consolidation/` ## The 6 steps ### 1. Input Collect a catalog of every physical table across every server/schema, a column inventory (schema, table, column, dtype), and whatever data dictionary exists. Note the mapping from physical copy to logical table. ### 2. Sample data (only when real data isn't available yet) Simulate the estate: N logical tables replicated across S servers with partial replication, drifted per copy (optional columns present/absent, server-unique singleton columns, occasional plausible dtype drift), and a dictionary that documents only the logical set. Document the planted realities. With real data, skip - export the catalog from information_schema across servers. ### 3. Objective Frame the consolidation decision: "merge every table's copies into one truth without losing a column that lives on only one server." Sub-questions on coverage, redundancy, per-table column drift, singletons, and dtype conflicts