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.
No term matches. Try a shorter word.
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
- Link
- Also called: link table. A Data Vault table recording a relationship between hubs, such as which customer placed which order, one row per pair of keys. Lesson 6.2
- 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