What do the dim_, fct_, stg_ and int_ prefixes on warehouse table names mean?

## What the prefixes say A warehouse contains tables from several stages of the same pipeline, and a consumer browsing a schema list cannot tell them apart from column names alone. Prefixing the table name with its **role** answers the two questions that matter immediately: is this safe to build a report on, and what shape are its rows? - **`stg_`** — *staging*. A near-one-to-one view of a single source object with columns renamed to house standards, types cast, and obvious junk removed. No joins to other sources, no business rules, no aggregation. `stg_stripe__payments` describes one source object, and its grain is that source's grain. - **`int_`** — *intermediate*. A working step that exists so a long transformation stays readable: a deduplicated event stream, a pre-joined set of attributes, a pivoted flag table. It is scaffolding. Nothing outside the transformation layer should read it, and it may be renamed or deleted whenever the model behind it changes. - **`dim_`** — *dimension*. One row per entity, or per version of an entity when history is kept. Descriptive attributes, a surrogate key, the business key. This is what report filters and group-bys hang off. - **`fct_`** — *fact*. One row per event or measurement at a declared grain, holding numeric measures plus the dimension keys. This is what gets aggregated. ## Why the published layer is marked differently from the rest The distinction that actually costs money when it is missing is *contract versus scaffolding*. A `dim_`/`fct_` table is published: consumers may join to it, a BI tool may reference it, and changing its grain or dropping a column breaks someone. A `stg_`/`int_` table is internal: it exists at the modeller's convenience. Analysts who cannot tell the difference build dashboards on intermediate tables, and the next refactor silently breaks them. The prefix is the cheapest available signal, reinforced in practice by putting the layers in separate schemas and granting read access only to the published one. ```text stg_shopify__orders -- one source object, renamed and cast stg_shopify__customers int_orders_deduplicated -- scaffolding, not a contract int_customer_attributes dim_customer -- published: one row per customer version dim_date fct_order_line -- published: one row per order line ``` ## Rules that keep the convention useful - **Apply it to every object, including views.** One unprefixed table in the list and readers stop trusting the scheme. - **Prefix, don't suffix.** Catalogs and BI object pickers sort alphabetically, so a prefix groups the layers and a suffix scatters them. - **Encode the role, not the refresh or the owner.** `daily_`, `tmp_`, `new_`, or a team name in the table name all go stale; the role does not. A table named `fct_orders_v2_final` is a warning sign, not a convention. - **Singular or plural — pick one and never mix.** Most shops use singular for dimensions (`dim_customer`) and either singular or plural for facts (`fct_order_line`). The argument does not matter; the inconsistency does. - **Say the grain in the fact's name where you can.** `fct_order_line` is worth more than `fct_orders` because it tells a reader what one row is before they open it. ## Common variants Shops differ on the exact tokens: `d_`/`f_`, `dim`/`fact` without an underscore, or no prefixes at all with the layers separated purely by schema (`staging.orders`, `marts.dim_customer`). Some pipelines add `rpt_` or `agg_` for pre-aggregated reporting tables built on the star, and `mart_` or a business-domain prefix such as `finance_` for grouping within the published layer. None of these is more correct than another. In an interview the safe answer is to name the roles the tokens stand for, note that the layer boundary is the point, and say what your team standardized on and why. ## What not to encode Do not put physical or operational facts in the name: materialization (`vw_`, `tbl_`), storage format, refresh frequency, or environment. All of them change without the model changing, and every one produces a rename that breaks every consumer. The name should describe what the rows *are*; how they are stored is metadata the catalog already carries.