What is a factless fact table, and what kinds of questions does it answer?

## Definition A factless fact table is a fact table whose row carries only dimension foreign keys (plus perhaps a degenerate key such as a document number) and **no numeric measures**. It seems odd at first — a fact table with no facts — but the row's *existence* is the fact. The measurement is the count of rows meeting a set of dimensional constraints. ```sql CREATE TABLE fct_class_attendance ( date_key INTEGER NOT NULL, student_key INTEGER NOT NULL, course_key INTEGER NOT NULL, teacher_key INTEGER NOT NULL, room_key INTEGER NOT NULL ); ``` There is nothing to sum here, but there is plenty to count: attendances per course per term, distinct students per teacher, room utilisation by day of week. ## Flavour one: event tracking The first and more common flavour records that an event happened when the event has no natural quantity. Examples: - a student attended a class session - a customer viewed a product page or opened an email - an employee completed a mandatory training module - a patient was screened for a condition - an insurance policy was quoted (but not necessarily bound) All the analysis is counting: `COUNT(*)` for occurrences, `COUNT(DISTINCT student_key)` for reach, ratios of counts for participation rates. Watch that those distinct counts and ratios are themselves non-additive — you can sum attendance counts across days, but distinct-student counts have to be recomputed from the atomic rows for every grouping, because the same student appears on many days. A design temptation to resist is bolting a measure on just to feel comfortable: adding a fabricated `attendance_flag = 1` is harmless as a convenience column, but inventing `duration_minutes` when the source does not record it puts fiction in the mart. ## Flavour two: coverage or condition The second flavour records a *state of eligibility or applicability* over an interval, rather than an event: - which products were on promotion in which stores during which weeks - which customers were eligible for which offers - which salespeople covered which territories in which month - which parts are valid for which product models Nothing happened here; a condition simply held. These tables are usually dense — a promotion covering 800 products across 300 stores for a week generates 240,000 rows — and are loaded from the promotion, assignment or eligibility source rather than from a transaction stream. ## Why coverage tables exist: the negative question The reason a coverage table earns its storage is that a sales fact table can only tell you what *did* happen. "Which promoted products sold nothing last week?" cannot be answered by a fact table of sales, because the absent rows are precisely the answer. With a coverage table, it becomes a set difference: ```sql SELECT c.store_key, c.product_key FROM fct_promotion_coverage c WHERE c.week_key = 202612 AND NOT EXISTS ( SELECT 1 FROM fct_sales s WHERE s.week_key = c.week_key AND s.store_key = c.store_key AND s.product_key = c.product_key); ``` The same pattern answers "which eligible members never enrolled", "which assigned territories produced no calls", "which trained employees never used the system". Any time the business question contains the word *not*, look for a coverage table. ## Relationship to the other fact types Factless is orthogonal to the transaction/periodic/accumulating split rather than a fourth position on the same axis. An event-tracking factless table is a transaction fact table that happens to have no measures; a coverage table is closest to a periodic snapshot of a condition. Interviewers usually list all four together, so name it as a type but be ready to explain that its distinguishing feature is the absence of measures, not the timing of the rows. ## The optional counter column Many implementations add a column holding the constant 1 — `attendance_count`, `event_count` — so that reporting tools which insist on aggregating a measure have something to point at, and so SUM and COUNT give the same answer. It stores a byte per row of pure redundancy in exchange for tool convenience; either choice is defensible, but do not present the constant column as what makes it a fact table. ## Common mistakes The most common error is inventing a measure so the table "looks normal", which puts numbers in the warehouse that no source ever produced. The second is failing to spot that a question is a negative one and trying to answer it from the transaction table alone — you cannot count rows that were never written. The third is forgetting the density of coverage tables: every product-store-week combination that a promotion touches becomes a row, so a broad promotion can dwarf the sales fact table it supports.