Factless Fact Tables: Modeling Events, Coverage & Eligibility Without Measures

factless fact table is the one design pattern that breaks the rule every beginner learns first — that a fact table is where the numbers live. A factless fact table has no numbers. It is a fact table made almost entirely of foreign keys pointing at dimensions, with not a single amount, quantity, or price column to sum. And yet it is one of the most useful shapes in a star schema, because the presence of the row is itself the fact. A row exists exactly when a student attended a class, when a product was on promotion, when a user viewed a page — and the metric you want is simply how many such rows there are, or, more subtly, which combinations have no row at all.

That second question is the one factless fact tables answer better than any other structure: what did not happen. A normal sales fact only records the sales that occurred, so it can never tell you which promoted products failed to sell — those rows were never written. This guide walks through the four ideas an interviewer will actually probe about factless fact tables — why a measureless fact table is legitimate, the event-tracking family you count with COUNT(*), the coverage / eligibility family that records the universe of what could occur, and the anti-join technique that turns the two into an answer for absence — and pairs each with a Solution-Tail interview answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the factless fact practice library →, rehearse the coverage-and-absence patterns on the coverage fact practice set →, and lock in your grain decisions on the fact grain practice set →.

---

On this page

• Why a fact table can have no measures at all
• Event-tracking factless facts — the row is the event
• Coverage & eligibility factless facts — what could happen
• Answering "what did NOT happen" with anti-joins
• Grain, keys & design pitfalls for factless facts
• Cheat sheet — factless fact recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why a fact table can have no measures at all

A factless fact table is a fact table whose only measure is its own existence — that one idea explains every use of the pattern

The one-sentence invariant: a factless fact table records that an event or a condition occurred by writing a row of foreign keys, and the measure you want is derived from the rows themselves rather than stored in a column. Everything about the pattern follows from that. There is no amount, no quantity, no price — there is a set of dimension keys, maybe a date key, maybe a degenerate id, and the fact is that this particular combination of keys came together at all.

Why "factless" is not a contradiction.

• The row is the fact. In a sales fact you store amount; the row plus the number is the fact. In a factless fact the row alone is the fact — one row means "this happened" or "this was true," and the numeric answer is COUNT(*) over those rows.
• The measure is implicit, not absent. "Factless" is a slightly misleading name: there is a fact — an occurrence or a condition — it simply is not pre-aggregated into a stored number. The measure lives in the cardinality of the table.
• It still obeys star-schema rules. A factless fact table sits at the centre of a star, joins to conformed dimensions on surrogate keys, and has a clearly declared grain. It is a fact table in every structural sense except the measure column.

The two families you must be able to name.

• Event tracking. One row every time something happens: a student attends a class, a user views a page, a visitor logs in, a patient is admitted. You answer questions by counting rows and counting distinct dimension members.
• Coverage / condition (eligibility). One row for every combination that is eligible or in effect, whether or not anything happened: which products are on promotion this week, which students are enrolled in which course, which sales reps cover which territory. This records the universe of the possible.
• The two are complementary. Event tables tell you what occurred; coverage tables tell you what could have occurred. You need both to ask "what was possible but never happened."

What interviewers listen for.

• Do you say "the existence of the row is the measure" in the first sentence? — senior signal.
• Can you name both families — event tracking and coverage — unprompted? — required framing.
• Do you reach for a coverage table specifically to answer "what did not happen", and explain why the event fact alone cannot? — the whole point.
• Do you resist the temptation to add a dummy count = 1 column, and explain that COUNT(*) already gives it? — a common anti-pattern they probe.

Worked example — the same event, with and without a measure

Detailed explanation. The quickest way to feel why a factless design is legitimate is to put a conventional sales fact next to an attendance fact. The sales fact has a measure to sum; the attendance fact has nothing to sum, yet both answer real business questions. The attendance fact answers "how many attendances" with COUNT(*) — the same answer a stored attendances = 1 column would give, minus the redundant column.

Question. Show a normal sales fact row and a factless attendance fact row side by side, and state how you get a number out of each.

Input.

fact table	keys	measure column
fct_sales	product_key, date_key, store_key	amount = 42.50
fct_attendance	student_key, class_key, date_key	(none)

Code.

-- measured fact: sum the stored measure
SELECT date_key, SUM(amount) AS revenue
FROM   fct_sales
GROUP  BY date_key;

-- factless fact: the metric is the row count itself
SELECT class_key, COUNT(*) AS attendances
FROM   fct_attendance
GROUP  BY class_key;

Step-by-step explanation. fct_sales carries amount, so the metric is SUM(amount) — the number was written at load time. fct_attendance carries no numeric column, so there is nothing to SUM; instead each row is one attendance, and COUNT(*) per class_key returns how many students attended. Both queries are ordinary star-schema aggregations — the only difference is that the factless query counts rows where the measured query sums a column. Adding an attendances INTEGER DEFAULT 1 column to the factless table would let you write SUM(attendances), but it would always equal COUNT(*), so it stores nothing new.

Output.

query	grouped by	result
SUM(amount) on fct_sales	date_key	revenue per day
COUNT(*) on fct_attendance	class_key	attendances per class

Rule of thumb. If the business question is "how many times did X happen," you do not need a measure column — you need a row per occurrence and COUNT(*). Reach for a measure only when there is a genuine number to add up.

---

2. Event-tracking factless facts — the row is the event

An event factless fact stores one row per occurrence, joins only to dimensions, and is queried with COUNT(*) and COUNT(DISTINCT)

The event-tracking family is the one most people meet first. The grain is "one row each time the event happens," the columns are the foreign keys that describe who / what / when, and the answer to almost every question is a count. Attendance is the textbook example: a row is written the moment a student attends a class on a date, and the table never stores a quantity because the quantity is always one.

Declaring the grain.

• Grain is a single sentence. "One row per student per class per day attended." Write that sentence before you write the DDL; every column exists to serve it, and no column may be finer or coarser than it.
• Keys describe the event. student_key, class_key, date_key — each a surrogate key into a conformed dimension. Together they answer who attended what and when.
• No numeric measure. There is deliberately nothing to sum. The event's "value" is that it happened.

The metrics you actually compute.

• COUNT(*) — how many events. Attendances per class, page views per article, logins per day. This is the additive workhorse.
• COUNT(DISTINCT key) — how many distinct members. Distinct students who attended (headcount, not visit-count), unique visitors, distinct products viewed. This is non-additive across some dimensions, which matters when you roll up.
• Ratios across dimensions. Attendance rate = distinct attendees ÷ enrolled; both numerator and denominator often come from factless tables (the second from a coverage table, section 3).

Degenerate dimensions on event facts.

• The event id has no dimension. A session_id, page_view_id, or admission_no that identifies the specific event but has no attributes of its own is stored in the fact table as a degenerate dimension — a key with no dimension to join to.
• It enables COUNT(DISTINCT event_id). When a single logical event can emit multiple rows, the degenerate id lets you count events, not rows.
• It is not a measure. A degenerate dimension is still a key; it never gets summed.

Worked example — a student-attendance factless fact

Detailed explanation. The canonical event factless fact is class attendance. Each time a student shows up to a class on a given day, one row is written with three foreign keys and nothing else. The table can grow to billions of rows and still stores not one number, because every question about it is a count.

Question. Model an attendance factless fact and count (a) total attendances per class and (b) distinct students who attended each class.

Input.

student_key	class_key	date_key
1	100	20260901
2	100	20260901
1	100	20260902
3	101	20260901

Code.

CREATE TABLE fct_attendance (
    student_key BIGINT NOT NULL REFERENCES dim_student(student_key),
    class_key   BIGINT NOT NULL REFERENCES dim_class(class_key),
    date_key    INT    NOT NULL REFERENCES dim_date(date_key)
    -- no measure column: the row is the attendance
);

SELECT class_key,
       COUNT(*)                     AS attendances,
       COUNT(DISTINCT student_key)  AS distinct_students
FROM   fct_attendance
GROUP  BY class_key;

Step-by-step explanation. The table holds only foreign keys, so a row is the atomic statement "this student attended this class on this date." For class_key = 100 there are three rows (student 1 twice on different days, student 2 once), so COUNT(*) = 3 attendances. But only two distinct students attended, so COUNT(DISTINCT student_key) = 2. The two numbers answer different questions — visit-count vs headcount — and both fall out of the same keys-only table.

Output.

class_key	attendances	distinct_students
100	3	2
101	1	1

Rule of thumb. For an event fact, keep COUNT(*) (how many times) and COUNT(DISTINCT member) (how many members) clearly separated in your head — they diverge exactly when a member can generate more than one event.

SQL interview question on counting events in a factless fact

Question. You have the fct_attendance table above and a dim_date with a week column. An interviewer asks for weekly attendances and weekly distinct attendees per class, sorted by class and week. Write the query and explain why the two counts can differ within a week.

Solution Using COUNT(*) versus COUNT(DISTINCT) over a joined date dimension

Code.

SELECT d.week,
       f.class_key,
       COUNT(*)                       AS attendances,
       COUNT(DISTINCT f.student_key)  AS distinct_attendees
FROM   fct_attendance f
JOIN   dim_date d ON d.date_key = f.date_key
GROUP  BY d.week, f.class_key
ORDER  BY f.class_key, d.week;

Step-by-step trace.

student_key	class_key	date_key	week
1	100	20260901	2026-W36
2	100	20260901	2026-W36
1	100	20260902	2026-W36
3	101	20260901	2026-W36

1. The join to dim_date attaches a week label to each attendance row without changing the grain — it is a lookup, one date key maps to one week.
2. GROUP BY week, class_key buckets the rows; class 100 in week W36 has three rows.
3. COUNT(*) returns 3 attendances for that bucket — student 1 attended on two days, counted twice, which is correct for "visits."
4. COUNT(DISTINCT student_key) returns 2 — student 1's two visits collapse to one person, which is correct for "unique attendees."

Output:

week	class_key	attendances	distinct_attendees
2026-W36	100	3	2
2026-W36	101	1	1

Why this works — concept by concept:

• Row-as-event grain — because one row means one attendance, COUNT(*) is a fully additive measure you can roll up across any dimension (day → week → term) without a stored column.
• COUNT(DISTINCT) non-additivity — distinct attendees cannot be summed across weeks (the same student in two weeks would be double-counted), so it must be recomputed at each grain, not aggregated from a lower level.
• Dimension join preserves grain — joining dim_date to label the week is a many-to-one lookup, so it neither adds nor drops attendance rows; the counts stay correct.
• Two questions, one table — visit-count and headcount are different business metrics that both come from the same factless table, which is exactly why the pattern is so economical.
• Cost — COUNT(*) is O(rows) on a scan or index; COUNT(DISTINCT) is O(rows) plus a hash/sort on the distinct key, measurably heavier at scale.

SQL
Topic — factless-fact
Factless fact event-counting problems

Practice →

Modeling Topic — event-log Event-log and occurrence-tracking problems

Practice →

---

3. Coverage & eligibility factless facts — what could happen

A coverage factless fact enumerates the eligible combinations — the universe of the possible — so you can measure the gap between could and did

The second family is the one interviewers use to separate people who memorised "factless = attendance" from people who actually understand the pattern. A coverage (or condition, or eligibility) factless fact does not record events. It records which combinations are in effect: which products are on promotion in which store this week, which students are enrolled in which course this term, which insurance plan covers which procedure. Nothing has to happen for a coverage row to exist — the row asserts a condition, not an occurrence.

Why coverage tables exist at all.

• Event facts only record what happened. A sales fact has rows only for products that sold. It is structurally incapable of telling you which promoted products sold nothing — those rows were never written.
• Coverage records the denominator. To ask "what fraction of promoted products sold," you need the full set of promoted products (the denominator) somewhere. That set is the coverage table.
• The classic case is promotion coverage. For every store / product / promotion / day that a promotion is in effect, write one coverage row — regardless of whether that product sold. Now the promoted-but-unsold products are answerable.

The grain of a coverage table.

• One row per eligible combination per time bucket. "One row per product per store per promotion per day the promotion runs." The combination, not an event, defines the row.
• It can be large. Coverage explodes combinatorially — every eligible product in every covered store for every active day — so it is often kept at a coarser grain (per week, per promotion period) or as date-range rows with valid_from / valid_to.
• Snapshot vs range. You can materialise coverage as a daily snapshot (one row per active day, easy to join to a date-grain event fact) or as effective-dated ranges (compact, but you join on BETWEEN). Snapshots trade storage for simpler joins.

What coverage tables model in the wild.

• Eligibility. Which customers are eligible for an offer, which employees are eligible for a benefit — the set you will later compare against who redeemed or enrolled.
• Assignment. Which sales rep covers which account, which nurse is rostered to which ward — a many-to-many relationship captured as a factless fact, which doubles as a bridge.
• Applicability. Which tax rule applies to which product, which SLA applies to which ticket class — conditions in force that downstream facts are checked against.

Worked example — a promotion-coverage factless fact

Detailed explanation. The archetypal coverage table is promotion coverage. A retailer runs a promotion; some products are included, in some stores, for some dates. The coverage table gets one row for every product-store-date the promotion is in effect — even a product that never rings up a single sale. That is the whole point: coverage captures the products that could have sold under the promotion.

Question. Model a promotion-coverage factless fact and count how many distinct products were on promotion in each store, independent of whether they sold.

Input.

product_key	store_key	promo_key	date_key
500	1	9	20260901
501	1	9	20260901
502	1	9	20260901
500	2	9	20260901

Code.

CREATE TABLE fct_promo_coverage (
    product_key BIGINT NOT NULL REFERENCES dim_product(product_key),
    store_key   BIGINT NOT NULL REFERENCES dim_store(store_key),
    promo_key   BIGINT NOT NULL REFERENCES dim_promotion(promo_key),
    date_key    INT    NOT NULL REFERENCES dim_date(date_key)
    -- factless: presence means "this product was on promotion here today"
);

SELECT store_key,
       COUNT(DISTINCT product_key) AS products_on_promo
FROM   fct_promo_coverage
GROUP  BY store_key;

Step-by-step explanation. Each row asserts that a product was on promotion in a store on a date — a condition, not a sale. Store 1 has three distinct promoted products (500, 501, 502); store 2 has one (500). COUNT(DISTINCT product_key) per store answers "how wide was the promotion here," a number the sales fact could never give you because the sales fact only knows about products that actually sold.

Output.

store_key	products_on_promo
1	3
2	1

Rule of thumb. If a question contains the word "eligible," "on offer," "covered," "assigned," or "enrolled" — anything about what is in effect rather than what occurred — you are looking at a coverage factless fact, not an event one.

SQL interview question on modeling eligibility as coverage

Question. A university needs to answer "how many students are enrolled in each course this term" and, later, "which enrolled students never attended." An interviewer asks how you model enrollment and why it must be a separate table from attendance. Give the coverage table and the enrollment count query.

Solution Using an enrollment coverage fact separate from the attendance event fact

Code.

CREATE TABLE fct_enrollment_coverage (
    student_key BIGINT NOT NULL REFERENCES dim_student(student_key),
    course_key  BIGINT NOT NULL REFERENCES dim_course(course_key),
    term_key    INT    NOT NULL REFERENCES dim_term(term_key)
    -- one row per student enrolled in a course for a term (a condition)
);

SELECT course_key,
       COUNT(DISTINCT student_key) AS enrolled_students
FROM   fct_enrollment_coverage
GROUP  BY course_key;

Step-by-step trace.

student_key	course_key	term_key	meaning
1	100	202603	enrolled (may or may not attend)
2	100	202603	enrolled
3	100	202603	enrolled
3	101	202603	enrolled

1. Enrollment is a condition — the student is registered for the course this term whether or not they ever walk in — so it is coverage, not an event.
2. Attendance is a separate event fact (fct_attendance) written only when a student shows up, so it cannot represent enrolled-but-absent students.
3. COUNT(DISTINCT student_key) per course over the coverage table gives the enrolled headcount — course 100 has three enrolled students.
4. Keeping enrollment and attendance in two tables is what later makes "enrolled but never attended" answerable: it is coverage minus events.

Output:

course_key	enrolled_students
100	3
101	1

Why this works — concept by concept:

• Coverage grain — one row per enrolled student-course-term captures the eligible universe; the row asserts a standing condition, so it exists independently of any attendance event.
• Denominator capture — enrollment is the denominator for attendance-rate and no-show metrics; without a coverage table there is no place that stores the students who could have attended.
• Separation of concerns — modeling enrollment and attendance as distinct factless facts keeps each at its own honest grain and prevents you from faking absence with outer joins on a single table.
• Reusable universe — the same coverage table feeds many questions (enrolled counts, no-show lists, capacity utilisation), which is why it earns its own place in the schema.
• Cost — the coverage table is O(eligible combinations), which is bounded and usually far smaller than the event stream, so it is cheap to store and index.

Modeling
Topic — coverage-fact
Coverage and eligibility modeling problems

Practice →

SQL Topic — factless-fact Factless fact table design problems

Practice →

---

4. Answering "what did NOT happen" with anti-joins

Coverage LEFT JOIN events, keep the NULLs — the anti-join is how a factless design turns absence into a queryable result

Here is the payoff that makes the whole pattern worth learning. Business people constantly ask negative questions: which promoted products sold nothing, which enrolled students never attended, which eligible customers never redeemed. An event fact alone cannot answer any of them, because absence leaves no row. The technique is always the same shape: take the coverage table (what was possible), left-join it to the event table (what happened), and keep only the rows where the event side is NULL — those are the non-events.

The anti-join, three equivalent ways.

• LEFT JOIN ... WHERE right.key IS NULL. Join coverage to events on the shared keys; unmatched coverage rows get NULLs on the event side; filter to IS NULL. The most portable and most readable form.
• NOT EXISTS (correlated subquery). For each coverage row, check that no matching event row exists. Often the planner's favourite — it can short-circuit on the first match and handles NULLs cleanly.
• EXCEPT / MINUS. Set-difference the coverage key-set minus the event key-set. Elegant when the two sides share exactly the same key columns and you only want the keys.

The correctness traps.

• NOT IN with NULLs is a landmine. WHERE key NOT IN (SELECT key FROM events) returns no rows at all if the subquery yields a single NULL, because NOT IN is three-valued. Prefer NOT EXISTS or LEFT JOIN ... IS NULL, which are NULL-safe.
• Join on the complete grain. The anti-join must match on every key that defines "the same thing." Match promoted products to sales on product_key AND store_key AND date_key; drop one and you either miss non-events or invent false ones.
• Align the time grain. If coverage is per-day and events are per-transaction, aggregate or key both to the same date grain before the anti-join, or a same-day sale in a different hour can be mismatched.

Why you need both tables.

• Coverage = the universe. Without a coverage fact there is no set of "everything that could have happened" to subtract from.
• Events = what occurred. Without the event fact there is nothing to subtract.
• Absence = coverage − events. The non-event is precisely the coverage rows with no matching event; that is a set difference you cannot compute from either table alone.

Worked example — promoted products that never sold

Detailed explanation. The definitive "what did not happen" query pairs promotion coverage with a sales event fact. Coverage lists every product on promotion in a store on a day; sales lists every product that actually rang up. Anti-joining coverage to sales on the full key surfaces exactly the promoted products that sold nothing — the rows a plain sales report can never show.

Question. Given fct_promo_coverage (product/store/date on promotion) and fct_sales (product/store/date that sold), list the promoted products that had zero sales.

Input.

coverage (product, store, date)	sales (product, store, date)
500, 1, 0901	500, 1, 0901
501, 1, 0901	(no row)
502, 1, 0901	502, 1, 0901
500, 2, 0901	(no row)

Code.

SELECT c.product_key, c.store_key, c.date_key
FROM   fct_promo_coverage c
LEFT   JOIN fct_sales s
       ON  s.product_key = c.product_key
       AND s.store_key   = c.store_key
       AND s.date_key    = c.date_key
WHERE  s.product_key IS NULL;   -- promoted but never sold

Step-by-step explanation. The LEFT JOIN keeps every coverage row and attaches matching sales rows where they exist. Product 500 in store 1 sold, so it matches and is discarded by the IS NULL filter; product 502 in store 1 likewise matches and drops. Product 501 in store 1 and product 500 in store 2 have no sale on that date, so their sales columns are NULL, and the WHERE s.product_key IS NULL filter keeps exactly those two — the promoted-but-unsold products.

Output.

product_key	store_key	date_key
501	1	20260901
500	2	20260901

Rule of thumb. "What did not happen" is always coverage LEFT JOIN events ... WHERE events.key IS NULL. If you find yourself trying to answer it from the event table alone, you are missing a coverage fact.

SQL interview question on absence and the NOT IN pitfall

Question. A colleague wrote WHERE student_key NOT IN (SELECT student_key FROM fct_attendance) to find enrolled students who never attended, and it returns zero rows even though many students were absent. Explain the bug and give a correct, NULL-safe query for "enrolled but never attended," per course.

Solution Using NOT EXISTS instead of NOT IN

Code.

SELECT e.course_key, e.student_key
FROM   fct_enrollment_coverage e
WHERE  NOT EXISTS (
           SELECT 1
           FROM   fct_attendance a
           WHERE  a.student_key = e.student_key
             AND  a.class_key   = e.course_key   -- align to the same grain
       );

Step-by-step trace.

enrolled (student, course)	attended?	NOT EXISTS result
1, 100	yes	excluded
2, 100	no	kept (never attended)
3, 100	no	kept
3, 101	yes	excluded

1. The bug: fct_attendance.student_key contains a NULL (or the subquery can), so NOT IN evaluates to UNKNOWN for every row and the whole result collapses to zero — three-valued logic, not a data problem.
2. NOT EXISTS asks a yes/no question per enrolled row — "is there any attendance for this student in this course?" — and NULLs inside the subquery simply fail to match, so they do not poison the result.
3. The correlation matches on both keys (student_key and course/class), so it checks attendance for the right course, not attendance anywhere.
4. Students 2 and 3-in-course-100 have no matching attendance row, so NOT EXISTS is true and they are correctly returned as never-attended.

Output:

course_key	student_key
100	2
100	3

Why this works — concept by concept:

• Anti-join semantics — "never attended" is a set difference (enrolled minus attended); NOT EXISTS expresses that difference directly and returns the coverage rows with no event match.
• NULL-safety — NOT EXISTS uses two-valued existence checks, so a NULL in the attendance keys cannot wipe the result the way NOT IN's three-valued logic does.
• Full-grain correlation — matching on student and course guarantees you are testing attendance for the same course the student is enrolled in, so you neither miss absentees nor flag the wrong ones.
• Coverage as the driving table — the query iterates the enrollment universe and probes events, which is the only order that can surface absence; driving from events could never see a student who generated no row.
• Cost — NOT EXISTS is typically an anti-semi-join, O(coverage rows) probing an index on the event keys; far cheaper and safer than materialising a NOT IN list.

SQL
Topic — factless-fact
Anti-join and "what did not happen" problems

Practice →

Modeling Topic — coverage-fact Coverage-vs-event absence problems

Practice →

---

5. Grain, keys & design pitfalls for factless facts

Declare the grain, count the rows, and never invent a dummy measure — the discipline that keeps a keys-only table honest

A factless fact table is simple to draw and easy to get subtly wrong. Because it has no measure to anchor it, the grain is the only thing keeping the table meaningful, and a sloppy join can silently multiply your "counts." This section is the checklist an interviewer expects you to run through when they say "design me a factless fact."

Grain first, always.

• One sentence, no exceptions. State the grain — "one row per student per class per day attended" — before any DDL. Every foreign key is there to satisfy that sentence; if a column does not help identify one row, it does not belong.
• Mixed grains corrupt counts. If some rows are per-day and others per-week in the same table, COUNT(*) means nothing. Keep one grain per table.
• Choose event or condition, not both. A single factless table is either event-tracking or coverage. Cramming both into one table (with a type flag) destroys the clean COUNT(*) and forces filters everywhere.

Counting safely.

• COUNT(*) is additive; COUNT(DISTINCT) is not. You can sum COUNT(*) up a hierarchy; you cannot sum distinct counts across buckets. Recompute distincts at each grain.
• Beware join fan-out. Joining a factless fact to a dimension that is not many-to-one (e.g. a bridge, or a mis-keyed dimension with duplicates) multiplies rows and inflates COUNT(*). Verify every fact-to-dimension join is many-to-one, or count a degenerate id with COUNT(DISTINCT) to stay safe.
• Degenerate ids protect event counts. When one logical event can produce several rows, store the event id in the fact and COUNT(DISTINCT event_id) so a downstream join cannot inflate the metric.

Anti-patterns to name and reject.

• The constant 1 measure. Adding attendances INTEGER DEFAULT 1 so you can "SUM a fact" is redundant — COUNT(*) already returns it. It wastes space and invites bugs when someone loads a 2.
• Storing derived attributes on the fact. Do not denormalise dimension attributes (student name, class title) into the factless fact; keep it keys-only and join to conformed dimensions.
• Using a coverage table as an event table (or vice versa). Counting coverage rows as if they were events overstates activity; both must exist and be used for what they model.

Worked example — how a fan-out join inflates a factless count

Detailed explanation. The most common factless-fact bug is a count that comes back too high because a join multiplied the rows. Here an attendance fact is joined to a class-instructor bridge where a class has two co-instructors; the join doubles every attendance row, and a naive COUNT(*) reports twice the real attendance.

Question. An attendance fact is joined to bridge_class_instructor (a class can have several instructors) to attribute attendances to instructors. Show how COUNT(*) inflates and how to count correctly.

Input.

attendance (student, class)	class → instructors
1, 100	100 → {A, B}
2, 100	100 → {A, B}

Code.

-- WRONG: the bridge fans out each attendance into one row per instructor
SELECT COUNT(*) AS attendances          -- returns 4, not 2
FROM   fct_attendance a
JOIN   bridge_class_instructor b ON b.class_key = a.class_key;

-- RIGHT: count distinct attendance events, immune to the fan-out
SELECT COUNT(DISTINCT a.attendance_id) AS attendances   -- returns 2
FROM   fct_attendance a
JOIN   bridge_class_instructor b ON b.class_key = a.class_key;

Step-by-step explanation. The bridge maps class 100 to two instructors, so the join turns each of the two attendance rows into two rows (one per instructor) — four rows total. Plain COUNT(*) now reports 4 attendances, which is wrong: only two students attended. Counting DISTINCT attendance_id — a degenerate dimension carried on the fact — collapses the fan-out back to the two real events, giving the correct 2 regardless of how many instructors the bridge attaches.

Output.

query	result	correct?
COUNT(*) after bridge join	4	no (inflated)
COUNT(DISTINCT attendance_id)	2	yes

Rule of thumb. Any join to a non-many-to-one table can inflate COUNT(*); carry a degenerate event id on the factless fact and count it distinctly whenever a bridge or multi-valued dimension is in the join path.

SQL interview question on grain and additivity

Question. You are asked to report "distinct products on promotion per store per week" from fct_promo_coverage (grain: product/store/promo/day), and a teammate proposes summing a daily products_on_promo count up to the week. Explain why that is wrong and write the correct weekly query.

Solution Using COUNT(DISTINCT) at the target grain instead of summing lower-grain counts

Code.

SELECT d.week,
       c.store_key,
       COUNT(DISTINCT c.product_key) AS products_on_promo
FROM   fct_promo_coverage c
JOIN   dim_date d ON d.date_key = c.date_key
GROUP  BY d.week, c.store_key;

Step-by-step trace.

product_key	store_key	date_key	week
500	1	20260901	2026-W36
500	1	20260902	2026-W36
501	1	20260902	2026-W36

1. The teammate's plan computes a daily distinct-product count (day 0901 = 1, day 0902 = 2) and sums them to 3 for the week — but product 500 was on promotion on both days and gets counted twice.
2. Distinct counts are non-additive: you cannot sum a distinct measure from a lower grain to a higher one without double-counting members that span buckets.
3. The correct query recomputes COUNT(DISTINCT product_key) directly at the week grain, so product 500 (present on two days) is counted once.
4. The result is 2 distinct products on promotion in store 1 for the week, not the inflated 3.

Output:

week	store_key	products_on_promo
2026-W36	1	2

Why this works — concept by concept:

• Grain discipline — recomputing the distinct at the reporting grain respects that the coverage grain is per-day; you never sum across the boundary that would double-count a member.
• Non-additive measures — COUNT(DISTINCT) cannot be rolled up like SUM, so the only correct way to get a weekly distinct is to aggregate the base rows to the week directly.
• Dimension-driven rollup — joining dim_date to attach the week lets one query serve any time grain by changing the GROUP BY, without pre-aggregated intermediate tables that would bake in the additivity bug.
• Keys-only integrity — because the coverage fact stores only keys, there is no stored count to be tempted into summing; the metric is always derived at query time at the right grain.
• Cost — a single COUNT(DISTINCT) over the base rows is O(rows) with a hash aggregate, and it avoids the two-pass "aggregate then re-aggregate" that produces the wrong answer anyway.

Grain
Topic — fact-grain
Fact-table grain and additivity problems

Practice →

SQL Topic — factless-fact Factless fact modeling and pitfall problems

Practice →

---

Cheat sheet — factless fact recipes

Event factless fact (keys only, count the rows).

CREATE TABLE fct_attendance (
    student_key BIGINT NOT NULL,
    class_key   BIGINT NOT NULL,
    date_key    INT    NOT NULL
);
SELECT class_key, COUNT(*) AS attendances
FROM   fct_attendance GROUP BY class_key;

Coverage / eligibility factless fact (the universe of the possible).

CREATE TABLE fct_promo_coverage (
    product_key BIGINT NOT NULL,
    store_key   BIGINT NOT NULL,
    promo_key   BIGINT NOT NULL,
    date_key    INT    NOT NULL
);

"What did NOT happen" — LEFT JOIN … IS NULL.

SELECT c.product_key, c.store_key, c.date_key
FROM   fct_promo_coverage c
LEFT   JOIN fct_sales s
       ON s.product_key = c.product_key
      AND s.store_key   = c.store_key
      AND s.date_key    = c.date_key
WHERE  s.product_key IS NULL;

Absence — the NULL-safe NOT EXISTS form.

SELECT e.course_key, e.student_key
FROM   fct_enrollment_coverage e
WHERE  NOT EXISTS (
    SELECT 1 FROM fct_attendance a
    WHERE a.student_key = e.student_key
      AND a.class_key   = e.course_key
);

Attendance rate = distinct attendees ÷ enrolled (event ÷ coverage).

SELECT en.course_key,
       COUNT(DISTINCT at.student_key)::numeric
         / COUNT(DISTINCT en.student_key) AS attendance_rate
FROM   fct_enrollment_coverage en
LEFT   JOIN fct_attendance at
       ON at.student_key = en.student_key
      AND at.class_key   = en.course_key
GROUP  BY en.course_key;

Coverage vs event — which family to build.

Question shape	Family	Metric
"How many times did X happen?"	event tracking	COUNT(*)
"How many distinct members did X?"	event tracking	COUNT(DISTINCT member)
"What was eligible / on offer / assigned?"	coverage	COUNT(DISTINCT combo)
"What was possible but never happened?"	coverage anti-join event	LEFT JOIN … IS NULL

---

Frequently asked questions

What is a factless fact table?

A factless fact table is a fact table that contains only foreign keys to dimensions (plus optionally a date key and a degenerate id) and no numeric measure column. The presence of a row is the fact: it records that an event occurred or that a condition was in effect. You derive metrics by counting rows with COUNT(*) and COUNT(DISTINCT ...) rather than summing a stored measure, so "factless" means "no stored measure," not "no meaning."

What are the two types of factless fact tables?

There are two families. Event tracking stores one row every time something happens — a student attends a class, a user views a page, a visitor logs in — and you answer questions by counting those rows. Coverage (or condition / eligibility) stores one row for every combination that is in effect — a product on promotion, a student enrolled in a course, a rep assigned to a territory — regardless of whether anything happened. Event tables tell you what occurred; coverage tables tell you what could have occurred.

How do you get a measure from a factless fact table?

You count. COUNT(*) over the rows gives the number of events (attendances, views, logins), and COUNT(DISTINCT some_key) gives the number of distinct members (unique attendees, unique visitors). Because the grain is "one row per occurrence," the row count is the measure — adding a constant 1 column so you can SUM it is redundant, since it always equals COUNT(*).

How do you answer "what did not happen" in a star schema?

You need two tables: a coverage factless fact for what was possible and an event fact for what occurred. Then you anti-join them — coverage LEFT JOIN events ON <full key> WHERE events.key IS NULL, or the NULL-safe NOT EXISTS form. The coverage rows with no matching event are exactly the things that did not happen: promoted products that sold nothing, enrolled students who never attended. An event fact alone can never answer this because absence leaves no row.

What is a coverage fact table?

A coverage fact table is a factless fact that enumerates the universe of eligible or in-effect combinations — every product on promotion in every store on every active day, for example — whether or not an event occurred against them. It captures the denominator you need for rates (attendance rate, redemption rate) and the reference set you anti-join against to find non-events. It typically lives at a per-day snapshot grain or as effective-dated valid_from / valid_to ranges.

Can a factless fact table have a degenerate dimension?

Yes, and it often should. A degenerate dimension is a key that identifies the specific event — an attendance_id, session_id, or admission_no — but has no attributes of its own, so it is stored in the fact table with no dimension to join to. It lets you COUNT(DISTINCT event_id) to get an accurate event count even when a join (to a bridge or multi-valued dimension) fans a single event out into several rows. It is still a key, never a measure.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every factless-fact idea above, from the event-counting attendance table to the coverage universe and the anti-join for absence, maps to a hands-on practice room where you build the model and the query against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you find the products that were on promotion but never sold?" holds up under a senior interviewer's depth probes.

Practice factless-fact problems now →
Coverage-fact drills →