Naming conventions#
Optional
Agreed rules for naming schemas, tables, columns and keys, so that a name alone says what a dataset is, which layer it belongs to and how it can be used.
Before reading
Why it matters#
In a platform with hundreds of tables, names are the first documentation people see. Consistent names make data findable, make SQL readable, and prevent mistakes such as joining a timestamp in local time with one in UTC. Inconsistent names cost time in every query and every review.
How it works#
Good conventions cover a few levels.
Layers and schemas. The layer is visible in the name, either as a schema (raw, clean, gold) or as a table prefix (raw_, clean_). Domain or product grouping is added where the platform has several domains, such as orders or payments.
Tables. Gold tables say what kind of table they are with a prefix: fct_ for facts, dim_ for dimensions, and sometimes agg_ for pre-aggregated tables. The rest of the name says what one row is, ideally including the grain: fct_order_line, fct_daily_sales. Pick one singular-or-plural rule per kind of table. The shop keeps the source's plural name for raw and clean entity tables (raw_orders, clean_customers) and names facts, dimensions and one big tables in the singular, after one row (fct_order_line, dim_customer, obt_order_line).
Columns. snake_case, lowercase, no spaces or abbreviations that only one team understands. Prefixes and suffixes carry type and meaning:
_idfor source identifiers,_keyfor surrogate keys;_atfor timestamps, always in UTC unless the name says otherwise;_datefor dates;- is_ and has_ for booleans;
- amounts with their unit or currency when ambiguous, such as amount_usd or weight_kg.
Columns that mean the same thing have the same name in every table: customer_id is customer_id everywhere, never cust_id in one table and customer in another. Metadata columns added by the platform start with an underscore, such as _source and _ingested_at, so they never clash with source columns.
Names describe what the data is, not where it runs. A table should not be called orders_prod or orders_v2_new. Environments belong in configuration, such as separate catalogs or schemas per environment, so that the same code and names run in development and production. Versions belong in contracts and release notes; when a breaking version must exist alongside the old one, a version suffix such as fct_orders_v2 is acceptable as a temporary, documented exception.
Published names are contracts. Renaming a gold table or column breaks every consumer, so names should be right before publishing, and changes follow the expand-and-contract steps described in Schema evolution.
Conventions only hold when they are checked. A linter in continuous integration can reject models whose names or columns break the rules: a missing prefix, a timestamp without _at, a column in camelCase. Tools such as SQLFluff check SQL style, and dbt projects can check model names and column names with packages or custom tests.
Key terms#
- Naming convention An agreed rule for names, enforced in review or CI.
- Prefix A name start that marks a layer or table type, such as clean_ or fct_.
- Suffix A name end that marks a type or meaning, such as
_ator_key. - Grain in the name Naming a table after what one row represents.
- Metadata column A column added by the platform, such as
_ingested_at.
Example#
The shop's naming rules, as they appear in its guidelines:
| Object | Rule | Example |
|---|---|---|
| Raw table | raw_ + source entity | raw_orders |
| Staging model | stg_ + source entity, rename and cast only | stg_orders |
| Clean table | clean_ + entity | clean_orders |
| Fact | fct_ + grain | fct_order_line, fct_daily_sales |
| Dimension | dim_ + entity | dim_customer |
| Aggregate | agg_ + grain | agg_monthly_sales |
| One big table | obt_ + grain | obt_order_line |
| Feature table | feat_ + entity and period | feat_product_daily |
| Model output | ml_ + model output | ml_churn_scores |
| Source ID | entity + _id |
order_id |
| Surrogate key | entity + _key |
customer_key |
| Timestamp | event + _at, UTC |
ordered_at |
| Date | event + _date |
order_date |
| Boolean | is_ or has_ | is_current, is_crm_contact_allowed |
| Metadata | leading underscore | _ingested_at |
A model named orderLines_final with a column createdTime is rejected by the CI check before review.
Pitfalls#
- Environment or version words in names. orders_prod and orders_new outlive their meaning.
- Timestamps without a stated time zone. Every query then guesses.
- The same concept under several names. Joins and searches miss half of the data.
- Rules nobody checks. Conventions drift within weeks without CI enforcement.
- Renaming published tables for style. The cost to consumers is real; fix names before publishing.