How would you enforce column and table naming standards across a warehouse many teams publish into?
answer
- review comments do not scale
- make the rule machine-checkable
- fail the build, not the reviewer
- rename existing tables behind aliasing views
basics
~20 sWrite the standard as a short, mechanical rule set, enforce it with an automated check in the build rather than in code review, apply it strictly to new models, and retrofit existing ones only behind aliasing views with an owned exception register.
solid answer
~50 sA naming standard only works if it is mechanical enough to be checked by a program. Write rules a linter can evaluate — snake_case, singular dimension names, `_sk` for surrogate and `_id` for business keys, `_at` for timestamps and `_date` for dates, `is_`/`has_` for booleans, units and currency in measure names, no reserved words, no abbreviations outside an approved list — and run that check over the model metadata in CI so violations fail the build instead of depending on whoever reviews. Then split the problem in two: new models comply from day one, and existing tables are renamed only when someone is already changing them, with a view carrying the old name so consumers are not broken by a cosmetic change. Governance is a small owning group, a written exception register, and the rule that exceptions are recorded rather than argued each time.
go deeper
Learn the common suffix conventions — _sk for a warehouse surrogate key, _id for a source business key, at for a timestamp, is for a boolean — and follow whatever your team has written down.
Explain why units belong in a measure's column name and why quoted mixed-case identifiers cause trouble, and be able to state a standard precisely enough for a program to check it.
Show how you make a standard survive a growing team: automated checks in the build, a warning-then-failing rollout, and renaming existing models behind aliasing views instead of breaking consumers.
Own naming as a consumer-facing interface: who decides, how exceptions are recorded, what a rename costs across BI content you do not control, and which rules justify that cost.
## What a naming standard actually contains The useful standard is short and boring. It fixes the things that cost real time when they vary: - **Case and separator.** `snake_case` everywhere, no quoted identifiers, no mixed case — because identifier case folding differs across engines and clients, and a quoted mixed-case name is a permanent tax on everyone writing SQL. - **Number.** Singular for dimensions (`dim_customer`), one convention for facts. Which one does not matter; mixing does. - **Key suffixes.** `_sk` for the warehouse surrogate key, `_id` for the source business key, `_key` reserved or banned because it means both to different people. This one pays for itself: a reader can tell from `customer_sk` versus `customer_id` which side of the model they are on. - **Type-carrying suffixes.** `_at` for a timestamp, `_date` for a date, `_amt` / `_qty` / `_pct` for measures, `is_` or `has_` prefix for booleans. A column called `active` could be a flag, a status string or a count; `is_active` cannot. - **Units and currency in the name.** `duration_seconds`, `revenue_usd`, `weight_kg`. Ambiguous units are the single most expensive naming defect, because the mistake is invisible until a number is wrong. - **Reserved and awkward words.** No `order`, `group`, `user`, `date` as bare column names. - **Abbreviations.** Allowed only from a published list. `cust`, `qty`, `amt` are fine if written down; ad-hoc shortening is not. ## Why review-time enforcement fails Naming is exactly the kind of rule humans enforce inconsistently. It is low-stakes in the moment, reviewers vary, and pushing back on a colleague's column name feels petty — so compliance decays, and the decay is invisible until someone tries to write a query across two teams' marts. The fix is to remove the judgment: a check that reads the model metadata (table names, column names, types, tests) and fails on a violation is impersonal, instant and applies identically to everyone including the person who wrote the standard. ```yaml # a naming rule set is only useful if it is machine-checkable rules: table_pattern: "^(stg|int|dim|fct|agg)_[a-z0-9_]+$" column_case: snake_case surrogate_key_suffix: _sk business_key_suffix: _id boolean_prefixes: [is_, has_] timestamp_suffix: _at banned_words: [order, group, user, date, value] ``` Keep the rule set in the repository next to the models so a change to the standard is a reviewable diff, and let the check emit warnings for a grace period before it fails builds — a standard introduced as a hard error on day one against a legacy warehouse just gets switched off. ## Retrofitting what already exists This is where the judgment lives. A rename is a breaking change for every dashboard, extract, saved query and downstream pipeline that references the old name, and none of them are in your repository. Three defensible positions: 1. **New models comply, old ones are grandfathered.** Cheapest, and leaves the warehouse permanently bilingual. 2. **Rename on touch.** When a model is being changed anyway, bring it up to standard in the same change and publish a view under the old name that selects the new columns with the old aliases. Consumers keep working; the view is dated and removed on an announced schedule. 3. **Big-bang rename.** Almost never worth it. The coordination cost across BI content you do not own exceeds the benefit, and the payoff is aesthetic. Option 2 is the usual answer. The point to make in an interview is that you priced the rename in *consumer* terms rather than in modeller-convenience terms. ## Governance without a committee - **One small owning group** with the authority to decide, not a forum that debates each case. - **A written exception register**: the object, the rule it breaks, why, and who approved it. Exceptions are inevitable — a regulatory column name, a source field that must be preserved verbatim — and recording them stops the same argument recurring. - **A single change path**: standard lives in one file, changes go through review, the check follows automatically. - **Onboarding**: the standard is short enough to read in five minutes, or it will not be read. ## The judgment an interviewer is testing Not whether you prefer `_sk` to `_key`. They want to see that you treat published names as an interface with consumers you do not control, that you know enforcement has to be automated to survive contact with a growing team, and that you can distinguish a standard worth breaking things for (units on a measure, key suffixes) from one that is pure preference (singular versus plural). Uniformity is the value; the specific choice mostly is not.
- Which naming rule is worth breaking a consumer's query for, and which is not?Units and currency on a measure are worth it — an ambiguous `revenue` or `duration` column produces silently wrong numbers, and that risk outlives any rename cost. Singular versus plural table names is pure preference: enforce it on new models, but do not rename a published table for it. The test is whether the current name can cause a wrong answer or only an inconsistent one.
- How do you introduce a naming standard into a warehouse that already has hundreds of non-compliant models?Run the check in warning mode first so teams see their debt without being blocked, make it a hard failure for new models immediately, and fix existing ones on touch — with a view under the old name aliasing the new columns so downstream content keeps working. Publish a removal date for those views and hold to it.
saying these in an interview costs you the question
- Relies on code review to keep naming consistent as teams grow
- Proposes a big-bang rename of published tables for consistency
- Writes rules too subjective for a linter to evaluate
- Omits units or currency from measure column names
- Treats an exception as a debate rather than a recorded decision