Home / Learn / Courses / Data modelling / Glossary
All lessons

Data warehouse glossary

133 words from the course, in plain English. "Also called" lists the other names you will meet in Power BI, dbt, Azure, Databricks, Snowflake and job ads. Each term links to the lesson that explains it.

ABCDEFGHIJKLMNOPRSTUV

A

Accepted values test
Also called: accepted_values (dbt). A test that a column holds only the values you expect, such as tiers Bronze, Silver, Gold or None. NULLs need care, because NOT IN skips them. Lesson 7.4
Accumulating snapshot fact table
Also called: accumulating snapshot, pipeline fact. A fact table with one row per thing moving through a process, such as an order, updated as it reaches each milestone. It has one date column per milestone. Lesson 3.3
Active relationship
Also called: active vs inactive relationship (Power BI). In Power BI only one relationship between two tables can filter at a time. Use a separate date table per role rather than juggling inactive ones. Lesson 7.5
Additive measure
Also called: fully additive fact, Sum (Power BI). A measure you can add up across every dimension and still get a true total, such as sales amount or quantity. Lesson 3.5
As it is
Also called: current view, type 1 view. Reporting every fact under the dimension values that are true today, such as all past sales under the customer's current tier. Lesson 5.4
As it was
Also called: historical view, type 2 view. Reporting each fact under the dimension values that were true when it happened, by joining to the version the fact points at. Lesson 5.4
As of a date
Also called: as-of query, point-in-time query. Reporting under the dimension values that were true on one chosen date, by picking the version whose valid range contains that date. Lesson 5.4
Atomic grain
Also called: lowest grain, finest grain. The finest level of detail the source records, such as one order line. Build the first fact table here: you can always roll up, never down. Lesson 2.2
Attribute
Also called: column, descriptive field. A property that describes something, such as a product's brand or a customer's city. In a star, attributes live in dimension tables and are what people filter and group by. Lesson 2.3
Audit column
Also called: metadata column, load column. A column the load adds to every row to record where and when it came from, such as load_id, loaded_at and record_source. Lesson 7.3
Average of averages
The mistake of averaging values that are already averages or ratios. Small groups count as much as big ones, so the answer is wrong; use a ratio of sums instead. Lesson 3.5

B

Back-fill
Also called: backfill. Going back to fix rows loaded earlier, such as replacing a placeholder customer key on old facts once the real customer arrives. Lesson 5.3
Batch window
Also called: load window, delete-insert window. The range of dates a load covers. An idempotent load deletes whatever is already in the window, then inserts the batch. Lesson 7.3
Bi-directional relationship
Also called: both-directions filter, cross-filter (Power BI). A relationship that lets filters flow from the fact back to the dimension as well. It can give surprising totals, so keep relationships single-direction unless you must. Lesson 7.5
Bridge table
Also called: associative table, junction table, factless link. A table that sits between two others to model a many-to-many relationship, one row per pair, sometimes with an allocation weight. Lesson 1.4
Bronze layer
Also called: raw layer, landing zone. The first medallion layer: data exactly as it arrived, plus where and when it came from. Nothing is cleaned or dropped. Lesson 6.4
Bus matrix
Also called: enterprise bus matrix, data warehouse bus. A grid with business processes as rows and dimensions as columns, ticked where a process uses a dimension. It plans a whole warehouse and shows which dimensions must be conformed. Lesson 2.5
Business key
Also called: natural key, source key. The identifier a source system uses for something, such as a customer number or product code. Data Vault hubs are built from business keys. Lesson 6.2
Business process
Also called: process. Something the organisation does that creates events you can measure, such as taking orders, receiving stock or handling returns. Each one gets its own fact table. Lesson 2.1

C

Cardinality
Also called: relationship cardinality. How many rows on one side of a relationship match each row on the other: one-to-one, one-to-many or many-to-many. Also the number of distinct values in a column. Lesson 1.4
Clustering
Also called: clustering key (Snowflake), clustered table (BigQuery), Z-ordering (Databricks). Keeping rows with similar values of chosen columns together in storage, so filters on those columns can skip blocks. Lesson 7.6
Columnar storage
Also called: column store, columnstore index (SQL Server), Parquet. Storing each column separately and compressed, instead of whole rows together. A query reads only the columns it uses, which suits analytics. Lesson 7.6
Conceptual, logical and physical model
Also called: levels of data model. Three levels of design: the conceptual model names the things and how they relate, the logical adds every attribute and key, and the physical adds the real tables, types and indexes. Lesson 1.4
Conformed dimension
Also called: shared dimension, common dimension. A dimension with the same keys, names and values everywhere it is used, so facts from different processes can be compared side by side. Lesson 4.3
Core layer
Also called: warehouse layer, integration layer, silver (lakehouse). The middle layer of a warehouse that holds the cleaned, conformed star: dim_ and fact_ tables built from staging. Lesson 7.1
Crow's-foot notation
Also called: IE notation, crow's foot. The ER diagram style where line ends show cardinality: a bar for one, a three-pronged foot for many, and a circle for optional. Lesson 1.4

D

DAG
Also called: directed acyclic graph, lineage graph. A picture of which models are built from which, with arrows that never loop back. dbt and orchestrators use it to run models in the right order. Lesson 7.1
Data lake
Also called: lake, ADLS, S3 bucket. Files of any kind kept in cheap object storage, read with a schema only when used. Flexible, but easy to turn into a mess without rules. Lesson 6.4
Data mart
Also called: mart, subject area. A set of tables shaped for one team or one dashboard, such as monthly sales for finance, built from the core layer. Lesson 7.1
Data test
Also called: dbt test, data quality check. A query that returns the rows that break a rule, such as duplicate keys or orphans. Zero rows means the test passes. Lesson 7.4
Data Vault
Also called: Data Vault 2.0, DV2. A modelling method for the integration layer that splits data into hubs (keys), links (relationships) and satellites (history). Loads are insert-only and easy to audit. Lesson 6.2
Data warehouse
Also called: DWH, EDW, analytical database. A database built for analysis: data from many sources, cleaned, kept with history and shaped for fast reading rather than fast updates. Lesson 1.1
Date dimension
Also called: calendar table, date table (Power BI), dim_date. A dimension with one row per day and columns such as year, quarter, month name, weekday and fiscal year, so reports can group and filter by any of them. Lesson 4.2
Date key
Also called: date_key, smart key. The key of the date dimension, often an integer such as 20250307 (YYYYMMDD), so it is readable and still sorts correctly. Lesson 4.2
Degenerate dimension
Also called: DD, transaction number on the fact. A dimension key, such as an order number, kept on the fact table with no dimension table of its own because it has no other attributes. Lesson 4.5
Denormalise
Also called: flatten, pre-join. Copying attributes into fewer, wider tables on purpose, so queries need fewer joins. It trades storage and update effort for simple, fast reads. Lesson 1.3
Dimension
Also called: dimension table, lookup table, dim. A table of descriptive context, such as product, customer, store or date, with one row per thing. Facts point to it, and reports filter and group by its columns. Lesson 2.3
Dimensional modelling
Also called: Kimball method, star schema design. Designing tables around business processes as facts surrounded by dimensions, so analysis is simple and fast. Popularised by Ralph Kimball. Lesson 2.1
Drill across
Also called: drill-across query. Comparing facts from two processes, such as sales and returns, by summarising each to the shared conformed dimensions and then joining the results. Lesson 4.3
Durable key
Also called: durable supertype key, stable id. An identifier that stays the same for a thing across all its type 2 versions, such as the customer id. Use it to step from any version to the current one. Lesson 5.4

E

Early-arriving fact
Also called: late-arriving dimension. A fact that turns up before the dimension row it needs, such as a sale for a customer the CRM has not sent yet. Lesson 5.3
Enterprise data warehouse
Also called: EDW. One central, integrated warehouse for the whole organisation. In Inmon's design it is normalised, and marts are built from it. Lesson 6.1
ER diagram
Also called: ERD, entity-relationship diagram. A drawing of entities as boxes and relationships as lines, with marks for cardinality. It shows how tables connect before you build them. Lesson 1.4
Expire a row
Also called: close a version, end-date. In a type 2 load, closing the current version by setting valid_to to the change date and is_current to 0, before inserting the new version. Lesson 5.2
Extended amount
Also called: line amount, extended price. Quantity times unit price (less discount) for one line, stored on the fact so it can be summed. Unit price itself is not additive. Lesson 2.3

F

Fact
Also called: measure, metric. A number measured by a business event, such as quantity sold or sales amount. Facts live in fact tables. Lesson 2.3
Fact table
Also called: fact, fct_ (dbt). The table at the centre of a star: one row per event at a declared grain, holding foreign keys to dimensions and numeric measures. Lesson 2.4
Factless fact table
Also called: event table, coverage table. A fact table with keys but no measures. It records that something happened, or could have, such as a sign-up or a product on promotion; you count its rows. Lesson 3.4
Fan-out
Also called: row explosion, join duplication. When a join to a table with several matches per row copies rows, so sums come out too big. A dimension must hold each key once to avoid it. Lesson 1.3
Filter context
The set of filters in force when Power BI works out a measure: slicers, rows and columns of a visual. They flow from dimensions to the fact. Lesson 7.5
First normal form
Also called: 1NF. Every column holds one value and there are no repeating groups such as product1, product2, product3. Each row is identified by a key. Lesson 1.2
Fiscal year
Also called: financial year, FY. The organisation's own accounting year, which may not start in January, such as April to March. The date dimension carries it as its own column. Lesson 4.2
Four-step design
Also called: Kimball four steps. The order of every dimensional design: choose the business process, declare the grain, identify the dimensions, identify the facts. Lesson 2.1
Full reload
Also called: truncate and load, full refresh (dbt). Emptying a table and loading everything again. Simple and always consistent, but slow on big tables and loses history kept only in the target. Lesson 7.2
Functional dependency
Also called: FD, A → B. Column B depends on column A when each value of A always goes with one value of B, such as product_id → product_name. Normal forms are built on this idea. Lesson 1.2

G

Gold layer
Also called: curated layer, presentation layer. The last medallion layer: business-ready tables such as stars, aggregates and features, built from silver. Lesson 6.4
Grain
Also called: granularity, level of detail. Exactly what one row of a fact table stands for, such as one product on one order. Declare it before choosing any columns. Lesson 2.2
Grain statement
Also called: grain declaration. One plain sentence saying what a row is, in business words, such as "one row per product on a completed order". Lesson 2.2

H

Half-open range
Also called: [valid_from, valid_to). A date range that includes its start but not its end, so one version ends on the same day the next begins and every date matches exactly one row. Lesson 5.1
Hash diff
Also called: hashdiff, row hash. A fingerprint of all the descriptive values in a row. If the new row's hash differs from the latest one, something changed and a new satellite row is added. Lesson 6.2
Hash key
Also called: hashed business key. A fixed-length code, such as an MD5 of the cleaned business key, used as the key of a Data Vault hub or link so loads can run in parallel. Lesson 6.2
High-water mark
Also called: watermark, incremental cursor. The latest value already loaded, such as MAX(order_date), stored so the next load takes only newer rows. Late rows can slip past it. Lesson 7.3
Hub
Also called: hub table. A Data Vault table listing the business keys of one core thing, one row per key, with a hash key, load date and record source. Lesson 6.2

I

Idempotent load
Also called: idempotent, re-runnable load. A load you can run twice with the same batch and get the same result as running it once: no duplicates, no lost rows. Lesson 7.3
Incremental load
Also called: delta load, incremental model (dbt). A load that applies only what is new or changed since last time, instead of reloading everything. Lesson 7.2
Inferred member
Also called: placeholder row, late-arriving dimension row. A dimension row the fact load creates for a natural key it has not seen yet, so the fact gets a real key. It is filled in when the real data arrives. Lesson 5.3
Inmon approach
Also called: top-down, CIF. Building one normalised enterprise warehouse first and the team marts from it, as Bill Inmon proposed. Lesson 6.1
Intermediate layer
Also called: int_ models (dbt). An optional layer between staging and core for reusable steps, such as order totals, that several core tables need. Lesson 7.1
IS DISTINCT FROM
Also called: IS NOT (SQLite), NULL-safe comparison. A comparison that treats two NULLs as equal and a NULL as different from any value, so change detection does not miss a NULL turning into a value. Lesson 5.2

J

Junk dimension
Also called: garbage dimension, mini flags dimension. One small dimension that gathers several low-cardinality flags, such as payment type and channel, into all their combinations, so the fact needs one key instead of many. Lesson 4.5

K

Key lookup
Also called: surrogate key lookup, key pipeline. The load step that swaps each natural key on incoming facts for the matching surrogate key from the dimension, or the unknown member if there is none. Lesson 4.1
Kimball approach
Also called: bottom-up, dimensional bus. Building star schemas one business process at a time, tied together by conformed dimensions and the bus matrix, as Ralph Kimball proposed. Lesson 6.1

L

Lakehouse
Also called: Databricks lakehouse, Microsoft Fabric lakehouse. A data lake with a table format such as Delta Lake or Iceberg on top, adding transactions, schemas and time travel so the lake can act like a warehouse. Lesson 6.4
Late-arriving fact
Also called: late fact. A fact that turns up long after the event, such as a receipt from last month. Its key lookup must use the dimension version that was true on the event date. Lesson 5.3
Load log
Also called: audit table, batch log. A table with one row per load run: which table, which window, rows read, deleted and inserted, and whether it finished. Lesson 7.3

M

Materialised view
Also called: indexed view (SQL Server), materialized view. A view whose result is stored and refreshed, so common summaries answer from far fewer rows. Lesson 7.6
Measure
Also called: fact, DAX measure (Power BI), metric (dbt). In a fact table, a numeric column you add up or average. In Power BI, a named calculation such as Total Sales that is worked out under the current filters. Lesson 2.3
Medallion architecture
Also called: bronze, silver, gold; multi-hop. Organising a lakehouse into three layers: bronze (raw), silver (cleaned and conformed) and gold (business-ready). Lesson 6.4
MERGE
Also called: MERGE INTO, upsert. One SQL statement that compares a source with a target and inserts new rows, updates changed ones and optionally deletes or flags missing ones. SQL Server, Snowflake, BigQuery and Databricks have it. Lesson 7.2
Mini-dimension
Also called: SCD type 4. A small separate dimension for fast-changing attributes, such as age band or income band, with its own key on the fact, so the main dimension does not grow a new version every month. Lesson 5.1
Mixed grain
Also called: grain mismatch. A fact table whose rows mean different things, or a measure stored at a coarser grain than the rows, such as order shipping on every line. Sums double count. Lesson 2.2

N

Natural key
Also called: business key, source key. The identifier the source system uses, such as customer_id. It can be reused, change or clash between systems, so the warehouse keys on a surrogate instead. Lesson 4.1
Non-additive measure
Also called: non-additive fact. A measure that can never be summed, such as a percentage, ratio or unit price. Store its parts and divide after summing. Lesson 3.5
Normal forms
Also called: normalisation, 1NF/2NF/3NF. Rules for splitting tables so each fact is stored once, which prevents update, insert and delete anomalies. Used for OLTP and Inmon-style cores. Lesson 1.2
Not-null test
Also called: not_null (dbt). A test that a column has no NULLs, such as fact foreign keys, which should point to the unknown member instead. Lesson 7.4

O

OLAP
Also called: online analytical processing, analytics workload. The analytics kind of workload: few users reading and summarising millions of rows. Data warehouses are built for it. Lesson 1.1
OLTP
Also called: online transaction processing, operational database. The kind of database that runs the business: many users making small, quick reads and writes, such as tills recording sales. Usually normalised. Lesson 1.1
One big table
Also called: OBT, wide table, flat table. A fact table with every dimension attribute already joined on. Easy to query and fast on columnar warehouses, but repeats text and is costly to change. Lesson 6.3
Orphan
Also called: orphan row, missing dimension row. A fact row whose key has no matching dimension row. Inner joins drop it silently, so totals shrink without warning. Lesson 7.4
Outrigger
Also called: outrigger dimension. A dimension attached to another dimension rather than to the fact, such as a date dimension joined from customer for their first-purchase date. Lesson 4.6

P

Partition
Also called: table partition, partitioned table. A chunk of a table holding rows with one range of a column, usually one day or month. Queries that filter on that column read only the chunks they need. Lesson 7.6
Partition pruning
Also called: partition elimination. The warehouse skipping partitions that cannot match a filter. It works best with a plain range on the partition column itself. Lesson 7.6
Periodic snapshot fact table
Also called: snapshot fact, balance table. A fact table with one row per thing per period, such as stock on hand per product per day, recording a level at a regular time. Lesson 3.2
Point-in-time join
Also called: as-of join, range join. Joining a fact to the dimension version whose valid_from and valid_to range contains the fact's date. Lesson 5.4

R

Ratio of sums
Also called: sum then divide. Working out a ratio after adding up its parts, such as SUM(profit) ÷ SUM(sales), which gives the true overall figure. The fix for an average of averages. Lesson 3.5
Reconciliation
Also called: reconciliation test, row count check. Checking the warehouse against the source, such as fact rows against source lines, and that joins do not change the row count. Lesson 7.4
Referential integrity
Also called: RI, foreign key integrity. The rule that every foreign key points to a row that exists. Warehouses often check it with tests instead of enforcing it with constraints. Lesson 7.4
Relationships test
Also called: relationships (dbt). A test that every value of a foreign key exists in the table it points to; in other words, that there are no orphans. Lesson 7.4
Reversing row
Also called: reversal, contra entry. A correction added as a new row with negative amounts, instead of editing the original, so the fact table stays append-only and history is kept. Lesson 3.1
Role-playing dimension
Also called: role-playing, multiple date roles. One dimension used several times by a fact in different roles, such as the date dimension as order date and as return date, through views or aliases. Lesson 4.4
Roll up
Also called: aggregate up, summarise. Adding fine rows up to a coarser grain, such as order lines to store per day, with GROUP BY. Lesson 2.2
Row-level security
Also called: RLS. Rules in the semantic model that limit which rows each viewer sees, such as a store manager seeing only their own store. Lesson 7.5

S

Satellite
Also called: satellite table, sat. A Data Vault table holding the descriptive attributes of a hub or link with full history, a new row each time something changes. Lesson 6.2
Schema-on-read
Storing data as it comes and deciding its columns and types only when reading it, as a data lake does. Lesson 6.4
Schema-on-write
Checking data against a fixed set of columns and types as it is loaded, as a warehouse does, so bad rows are rejected at the door. Lesson 6.4
Second normal form
Also called: 2NF. In 1NF, and no column depends on only part of a composite key, such as product_name depending on product_id alone in an order lines table. Lesson 1.2
Semantic layer
Also called: semantic model (Power BI), dbt Semantic Layer, LookML. A layer between the warehouse and reports that gives business names, relationships, measures and security once, so every report uses the same definitions. Lesson 7.5
Semi-additive measure
Also called: semi-additive fact, LASTNONBLANK (Power BI). A measure that adds up across some dimensions but not across time, such as stock on hand or an account balance. Over time, take the last or the average. Lesson 3.5
Silver layer
Also called: cleansed layer, conformed layer. The middle medallion layer: cleaned, typed, de-duplicated and conformed data, one row per business key. Lesson 6.4
Slowly changing dimension
Also called: SCD. A dimension whose attributes change now and then, such as a customer's city. The SCD type says how the change is handled: ignore, overwrite, add a version or add a column. Lesson 5.1
Snowflake schema
Also called: snowflaking, normalised dimensions. A star whose dimensions are split into further tables, such as product → category → department. It saves a little space but adds joins. Lesson 4.6
Soft delete
Also called: is_deleted flag, logical delete. Marking a row as deleted with a flag instead of removing it, so history and old facts stay intact. Lesson 7.2
Staging layer
Also called: staging, stg_ models (dbt), landing. The first layer of a warehouse: one cleaned, renamed copy per source table, with no business logic yet. Lesson 7.1
Staging table
Also called: stage table, change set. A table where a batch lands, cleaned but not yet merged into the warehouse, so the load can compare it with the target. Lesson 5.2
Star schema
Also called: star, dimensional model. A fact table in the middle with dimension tables around it, each joined by one key. Power BI and most BI tools work best with it. Lesson 2.4
Stovepipe
Also called: data silo, stovepipe mart. A mart built on its own with its own versions of shared dimensions, so its numbers cannot be compared with other marts. Lesson 2.5
Summary table
Also called: aggregate table, agg table. A table that stores common totals at a coarser grain, such as sales per store per month, so dashboards read far fewer rows. Lesson 7.6
Surrogate key
Also called: warehouse key, SK, IDENTITY key. A meaningless whole number the warehouse gives each dimension row. It survives source changes and lets one thing have several type 2 versions. Lesson 4.1

T

Table format
Also called: open table format, Delta Lake, Apache Iceberg, Apache Hudi. A layer of metadata over files in a lake that adds transactions, schemas and history, turning folders of files into reliable tables. Lesson 6.4
Third normal form
Also called: 3NF. In 2NF, and no column depends on another non-key column, such as store_city depending on store_id in an orders table. Lesson 1.2
Time travel
Also called: versioned table, AS OF query. Reading a lakehouse or warehouse table as it was at an earlier version or time, kept by the table format. Lesson 6.4
Transaction fact table
Also called: transaction fact, event fact. The most common fact table: one row per event, such as an order line, written once when the event happens. Lesson 3.1
Transitive dependency
A non-key column that depends on another non-key column instead of the key, such as city depending on store_id in an orders table. 3NF removes it. Lesson 1.2
Type 0
Also called: SCD0, fixed attribute. The value never changes once loaded, such as a customer's original sign-up date. Lesson 5.1
Type 1
Also called: SCD1, overwrite. A change overwrites the old value. Simple, but history is lost: past sales move to the new value. Lesson 5.1
Type 2
Also called: SCD2, row versioning, dbt snapshot. A change adds a new row (version) with its own surrogate key and valid_from, valid_to and is_current columns, so facts keep the version true at the time. Lesson 5.1
Type 3
Also called: SCD3, previous value column. A change moves the old value into a previous_ column on the same row. Only one step of history is kept. Lesson 5.1
Type 6
Also called: SCD6, hybrid (1 + 2 + 3). Type 2 rows plus current_ columns overwritten on every version, so each row knows both what was true then and what is true now. Lesson 5.1

U

Unique test
Also called: unique (dbt), duplicate key check. A test that no key appears twice, such as each product_key once in dim_product. A duplicate doubles every fact that joins to it. Lesson 7.4
Unknown member
Also called: default member, -1 row, ghost record. A dimension row with key -1 (or 0) that facts point to when the real value is missing, so no fact key is NULL and inner joins drop nothing. Lesson 4.1
Upsert
Also called: INSERT ... ON CONFLICT, MERGE. Update the row if its key already exists, insert it if not, in one statement. The core of an incremental load. Lesson 7.2

V

valid_from, valid_to, is_current
Also called: effective dates, SCD columns, dbt_valid_from. The three columns of a type 2 row: when the version started, when it ended (9999-12-31 if still true) and a 1 or 0 flag for the current version. Lesson 5.1

See also the modelling cheat sheet, the SQL glossary and the SQL sandbox.