Naming Conventions

Consistent naming makes dimension tables easier for business users to understand and navigate in reporting tools.

Table Prefixes

Dimension tables are typically prefixed with dim_ to instantly distinguish them from fact tables. For example, a customer table becomes dim_customer.

Surrogate Keys

The primary key of the dimension usually combines the entity name with _key, such as customer_key or product_key. This makes SQL joins clear and predictable.

Dimension surrogate keys are typically integer values assigned by the data warehouse, with each new dimension member receiving a unique key. They usually start at 1 and increment as new keys are generated.

The date dimension is a common exception. Because dates are highly predictable and stable, it can use a meaningful key such as YYYYMMDD (e.g., 20260911) instead of an arbitrary surrogate key.

Source System Keys

The original identifier from the source database is usually labeled with an _id suffix, such as customer_id, to distinguish it from the data warehouse's own surrogate keys. This source identifier is commonly known as a natural key or business key.

Do not confuse this with a durable key (often called a durable supernatural key), which the data warehouse creates and manages itself. A durable key acts as a permanent, stable identifier for the underlying entity. It allows you to track the exact same entity consistently across all its historical versions, even if the source system recycles its natural keys or the dimension row gets replaced.

Booleans and Flags

Columns that represent a true/false state should be prefixed with is_ or has_. This makes the column's binary nature instantly recognizable to users. For example, use is_active or has_children rather than ambiguous names such as active or children.

Temporal Columns

NOTE

A temporal column is simply a column that stores dates or timestamps to track when a record was valid, active or occurred.

Temporal attributes should be named consistently across the entire warehouse. Whether your team prefers valid_from / valid_to or effective_date / expiration_date or start_date / end_date, pick one pair and apply it universally.

Role-Playing Dimension Names

When the same dimension is used for multiple business roles, it may be exposed through role-specific views or aliases when this improves readability. For example, dim_date can be exposed as dim_order_date, dim_ship_date and dim_delivery_date to make each role explicit.

Degenerate Dimension Names

Identifiers that live directly on the fact table with no dimension of their own - such as order numbers, ticket numbers, or transaction IDs - are typically named with _id or _number (for example, order_number). They should not use _key, which is reserved for surrogate keys that reference dimensions.

Clear Attributes

Because business intelligence users interact directly with these columns, you should avoid cryptic abbreviations. Spell out words completely, using product_category instead of prod_cat. Cryptic abbreviations, true/false flags, and operational indicators should be supplemented with full text words that have meaning when independently viewed in dimension tables.