Names are the cheapest documentation available and the only kind that is always up to date. Two rules — a prefix that carries the contract, and an identifier standard — cover almost everything.
The prefix tells a reader what the object is and how to treat it, before they open it. This is the full set for this warehouse — deliberately small, because a prefix scheme nobody can recite is a prefix scheme nobody follows.
Prefix
Means
Example
Rule
dim_
dimension
gold.dim_customer
has a _sk; may be SCD2
fct_
fact
gold.fct_order_line
declared grain; FKs to dimensions only
agg_
aggregate
gold.agg_revenue_month
derived from a fct_; never a source
brg_
bridge
gold.brg_product_group
resolves many-to-many; no measures
stg_
staging
stg_customer_delta
transient; dropped after the run
mv_
metric view
gold.mv_sales
semantic layer; read with MEASURE()
_tech_
technical column
_tech_valid_from
never business meaning; never exposed to BI
Why
A reader who sees gold.agg_revenue_month knows immediately that it is derived, that it must not be a source for anything else, and that if the number is wrong the bug is upstream in fct_order_line. That is three facts obtained from a name, and it is why a report writer stops asking you where numbers come from.
The _tech_ prefix earns its place separately: it is the single filter that separates plumbing from business meaning. Everything a BI tool should never see starts with it.
Doing it
Apply the prefix at creationRenaming a table that a dashboard reads is a migration, not a tidy-up.
Filter _tech_ columns out of the gold views exposed to BIDo it mechanically rather than by hand-listing columns.
Use stg_ for anything transient and actually drop itA staging table that survives the run is a table someone will start depending on.
Embedding it
Ask the team to name three new tables at the whiteboard before they build themDisagreements surface the modelling question hiding underneath.
Add prefixes to the review checklist so it is caught at review, when renaming is free
When someone proposes a new prefix, make them argue for itUsually the object belongs to an existing category and the urge to invent a prefix is really an unresolved modelling question.
Defaults
Seven prefixes, no more. A scheme people can recite is a scheme people apply
The prefix is part of the contractCI can check that anything named fct_ has a declared grain comment.
Never prefix with the source systemgold.sap_customer means the conformance never happened.
Gotchas
mv_ is a genuine collision on this platformDatabricks has materialized views AND Unity Catalog metric views, and they are different objects with different refresh semantics and different costs. Pick one meaning and write it down. This handbook uses mv_ for metric views and spells out 'materialized view' in full every time it means the other thing.
Leaving a fact table unprefixed 'because it's obvious'Then the aggregate is indistinguishable from the fact in an autocomplete list, and someone builds a report on the aggregate thinking it is the detail.
Prefixing by source (sap_, shop_) in goldIt reads as tidy and it means every downstream consumer has to know which system a customer came from — which is precisely the knowledge gold exists to remove.
One standard, applied without exception: identifiers are English, lower_snake_case, ASCII only. Business terminology in the local language belongs in comments, in the metric view's display names, and in the glossary — not in column names.
Why
The precise failure mode is Unicode normalisation, and it is nastier than it sounds. ö can be encoded as one code point (NFC, U+00F6) or as o plus a combining diaeresis (NFD, U+006F U+0308). They render identically in every editor and terminal. macOS filesystems historically hand back NFD; Windows and most Linux tooling produce NFC.
So a column created from a macOS laptop and referenced from a Windows CI runner can be two different strings that look the same, and the error message is column not found naming a column you can see in the table. Engineers lose a day to this, and they lose it while doubting their own eyes.
The second reason is duller and matters more often: a mixed international team cannot type these names quickly, so they copy-paste them, so they stop reading them.
How
sqlThe rule in practice
-- yes
CREATE TABLE gold.dim_customer (
customer_sk STRING COMMENT 'Surrogate key, sha2 of customer_id',
customer_segment STRING COMMENT 'Kundengruppe: A | B | C',
postal_code STRING COMMENT 'PLZ'
)
COMMENT 'One row per customer per validity interval (SCD2).';
-- no
CREATE TABLE gold.dim_kunde (
kunden_schlüssel STRING, -- umlaut in an identifier
Kundengruppe STRING, -- mixed case
PLZ STRING -- upper case
);
Delta and Unity Catalog will accept kunden_schlüssel. That is not the same as it being safe.
Doing it
Set the rule in week one and enforce it in CI with a regex over DDL^[a-z][a-z0-9_]*$ is the whole check.
Put the local-language term in the COMMENTThere it helps the business reader and breaks nothing.
Use the metric view's display metadata for anything a BI user seesThat way the German label and the ASCII identifier can coexist. It is no longer only a label: display_name, format and up to ten synonyms per field are what Genie resolves a natural-language question against, so the German business term belongs in synonyms rather than only in a comment — that is what makes "Umsatz" and "revenue" reach the same measure.
Keep SAP's own table names (KNA1, VBAK, VBAP) in bronzeThose are the literal source names and renaming them destroys traceability.
Embedding it
Show the NFC/NFD failure once, live, rather than asserting itPaste two visually identical column names into a query and let it fail. Nobody forgets it afterwards.
Expect pushback that English names distance the business from the modelAnswer it concretely: the business sees the metric view and the comments, not the DDL.
Let the team write the regex check themselvesIt is a ten-line CI job and it teaches that a standard without a check is a preference.
Defaults
^[a-z][a-z0-9_]*$ for every identifier, checked in CI
Local-language business terms live in COMMENT and in metric-view labels
Source table names preserved verbatim in bronze; conformed English names from silver onward
No abbreviations that are not already universal in the domaincust_seg costs more in confusion than customer_segment costs in typing.
Gotchas
Two identifiers that render identically but compare unequal — Unicode normalisation, as aboveThe pair produces a 'column not found' error naming a column you can plainly see.
Reserved words — but check which ones, because the intuitive answer is wrong hereDatabricks does not formally disallow any literal as an identifier, and the ANSI keyword list is explicitly informational and unenforced. What actually needs backticks is a short list of 17 words used as a table alias: ANTI, CROSS, EXCEPT, FULL, INNER, INTERSECT, JOIN, LATERAL, LEFT, MASK, MINUS, NATURAL, ON, RIGHT, SEMI, UNION, USING. Alias a subquery left and the parser reads a join; the error names the join syntax, not your alias, so the fix is not where the message points.
Assuming ANSI reserved words are unusableThey are usable, which is a trap in the other direction: order is a legal Databricks table name. This handbook still calls the table sales_order — FROM order beside ORDER BY is genuinely hard to read, and engines you may federate to do reserve it — but that is a readability and portability argument, not a platform constraint. Anyone who tells a client ORDER is reserved on Databricks will be corrected live.
Mixed case identifiersDelta is case-insensitive for resolution but case-PRESERVING in the metadata, so Kundengruppe and kundengruppe resolve to the same column while tools that compare schema strings report a difference. Schema-drift monitors then alarm on a change that did not happen.
Renaming identifiers after BI is connectedThe rename is one statement; the coordination with every report, every metric view and every downstream extract is a fortnight.