# 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.

> [!NOTE]
>
> **Before reading**
>
> [Medallion layers: bronze, silver and gold](<https://wiki.4tm0.net/data/medallion-layers.html>) · [dbt](<https://wiki.4tm0.net/data/dbt.html>)

## 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:

- `_id` for source identifiers, `_key` for surrogate keys;
- `_at` for timestamps, always in UTC unless the name says otherwise; `_date` for 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](<https://wiki.4tm0.net/data/schema-evolution.html>).

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 `_at` or `_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.

## Learn more

- [dbt documentation, how we structure our dbt projects](<https://docs.getdbt.com/best-practices/how-we-structure/1-guide-overview>)
- [SQLFluff documentation](<https://docs.sqlfluff.com/>)

## Related

[Dimensional modelling](<https://wiki.4tm0.net/data/dimensional-modeling.html>) [Environments and CI](<https://wiki.4tm0.net/data/environments-and-ci.html>) [Governance as code](<https://wiki.4tm0.net/data/governance-as-code.html>) [Catalog and lineage](<https://wiki.4tm0.net/data/catalog-and-lineage.html>)