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 typically labeled with _id, such as customer_id, to distinguish it from the data warehouse's own surrogate keys. This source identifier is commonly referred to as a natural key or business key.

This should not be confused with a durable supernatural key, which is created and managed by the data warehouse itself. A durable supernatural key provides a permanent, stable identifier for the underlying entity, even when the source system changes or recycles its natural keys, or when the entity's dimension row is updated or replaced. This allows the same underlying entity to be tracked consistently across SCD versions.

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

Temporal attributes that define when a dimension row or attribute is valid 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.