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.

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.

Measures

Measure names must state exactly what is being measured and its unit, avoiding vague words like amount or total. Use precise names like sales_amount, quantity_sold or discount_amount. If the data includes multiple currencies or units, make this explicit with names like sales_amount_usd and sales_amount_local.

For semi-additive and non-additive measures, names should also help users understand their meaning and aggregation limits:

• Semi-additive measures: Use names such as closing_account_balance or inventory_on_hand_quantity to identify point-in-time values.
• Non-additive measures: Use names such as profit_margin_pct or conversion_rate to identify ratios and percentages. Document whether values are stored as fractions (such as 0.25) or percentages (such as 25).

Names alone cannot prevent incorrect aggregation, so document the aggregation rules for each measure. For ratios and percentages, store the underlying numerator and denominator where possible, and calculate the ratio at query time.