Evolving a Published Model

## What "published" means here A model is published once something you do not control reads it: a BI dashboard, a scheduled extract, a downstream transformation owned by another team, a spreadsheet someone refreshes monthly. From that moment the table's shape and the meaning of its columns are a contract. The useful skill in an interview is not reciting rules but classifying a proposed change by what it does to that contract. ## Bucket 1 — additive, ship it Additive changes only append surface area: - a new attribute column on an existing dimension (`dim_customer.industry_code`), - a new dimension table plus its foreign key on a fact table, - a new measure column on a fact table, - an entirely new fact table for a new business process. Existing queries name the columns they want, so a new column is invisible to them. The two traps are `SELECT *` consumers (a new column can widen an extract or shift positional references) and adding a column with a definition that only holds for recent rows — history is null or wrong until backfilled, and a report that averages over all time silently mixes populated and unpopulated periods. ## Bucket 2 — structurally breaking, loud Dropping a column, renaming a column or table, narrowing a type (`DECIMAL(18,4)` to `INTEGER`), changing nullability, or regenerating surrogate keys so that yesterday's `customer_sk = 501` is somebody else any of these makes downstream queries fail, or worse, makes saved filters and hard-coded key lists match nothing. These are unpleasant but self-announcing: someone sees an error. They still require finding consumers and giving them a migration window, because "it errored in production at 6am" is not a migration plan. Key regeneration deserves special mention. Warehouse surrogate keys are frequently persisted outside the warehouse — in a BI tool's cached extract, in a saved filter, in another team's model. A full rebuild that re-issues sequence values is technically "the same data" and practically a silent corruption. ## Bucket 3 — semantically breaking, silent The schema is byte-identical and the numbers change: - **The measure's definition moves.** `revenue` starts including shipping, or excluding cancelled orders, or is now net of returns. Every report keeps running and every historical comparison quietly shifts. - **A Type 1 dimension becomes Type 2.** Previously one row matched each customer; now several history rows do. Any report that joins on the natural business key rather than the surrogate key, or that forgets the current-row predicate, multiplies its fact rows and inflates sums. - **Membership changes.** A row that used to be excluded by an ETL filter is now included; test accounts start appearing; an "Unknown" member is introduced for orphan keys and shows up in every breakdown. - **A dimension attribute changes cardinality or spelling.** `segment = 'SMB'` becomes `'Small Business'` and every dashboard filter pinned to the old literal returns zero. Silent changes are the ones that destroy trust in the warehouse, because the failure mode is a number, not an error. ## Grain changes are a category of their own Changing a fact table's grain — one row per order becoming one row per order line — is not a modification of the table; it is a different table that happens to reuse the name. Every `COUNT(*)`, every `AVG`, every join cardinality changes. The safe pattern is to build the new grain alongside the old and let the old table become a derived aggregate over the new one until consumers move. ## A practical protocol 1. Classify the change into the three buckets above before writing any DDL. 2. For buckets 2 and 3, enumerate consumers from warehouse query logs and BI lineage — never from memory. 3. Reconcile: run old and new side by side over the same period and diff the totals. A change you intended to be additive that moves any total is not additive. 4. Announce semantic changes with a dated note in the catalog and, where the old definition still has users, keep both measures under distinct names rather than overwriting one. 5. Expose consumers a view rather than the base table, so that renames and reshapes can be absorbed behind the facade instead of being pushed onto every dashboard.