Naming Conventions

Consistent naming makes fact tables intuitive for analysts to query, clearly separates quantitative metrics from descriptive context and helps communicate the grain.

Table Prefixes

Fact tables are typically prefixed with fact_ to instantly distinguish them from dimension tables. Some data teams use fct_ instead, which is common in dbt projects. Either works, provided it is applied consistently across the warehouse.

Grain in the Name

Because a fact table's grain is its most important property, the name should ideally communicate it. Names like fact_order_item and fact_inventory_daily_snapshot communicate the intended grain, whereas a generic fact_sales leaves the grain open to interpretation.

Foreign Keys

Foreign keys to dimensions should normally use the exact same column name as the dimension's surrogate key. If the customer dimension uses customer_key, the fact table should also use customer_key. Matching names make the join path self-evident and can help BI tools identify relationships more easily.

Role-Playing Keys

When the same dimension is joined to a fact table more than once, the column name should describe the logical role rather than the dimension name. A sales fact referencing dim_date three times should use order_date_key, ship_date_key and delivery_date_key instead of a single, ambiguous date_key.

Measures

Measure names should state exactly what is being measured and its unit, avoiding bare words like amount or total. Use sales_amount, quantity_sold, or discount_amount. Where multiple currencies or units exist, make this explicit with names like sales_amount_usd and sales_amount_local.

Primary Keys

While some fact tables rely on a combination of foreign keys to identify a row, those that use a single generated primary key typically combine the event name with _id, such as sales_id or transaction_id.

Non-Additive and Semi-Additive Measures

Names for semi-additive and non-additive measures should make their meaning clear and avoid implying that they can be freely summed across every dimension. Names like account_balance and inventory_quantity read naturally as point-in-time values, whereas calling them total_balance invites incorrect aggregation.

Ratios and percentages should generally not be summed directly. Instead, when possible, store the underlying numerator and denominator and calculate the ratio at query time.