DWH Interview Question 22 - What is a factless fact table?

Data Warehouse Interview Q&A · Question 22

In this post we are going to cover the factless fact table, a fact table with no measures at all, and why it is still one of the most useful tables in a data warehouse.

A factless fact table holds only dimension keys: a date, a customer, a product, a student, a class. There is no amount and no quantity. The row itself is the fact. It says "this event happened" or "this condition existed on this day".

There are two kinds. Event tables record things that happened, like page views or class attendance. Coverage tables record what was possible, like which products were on promotion. We will build one of each for TB Shop and use the coverage table to answer a question the sales fact cannot: what did not happen?

Question 22 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. Let's create some sample data
3. Step 1: Count the events
4. Step 2: What was promoted but did NOT sell?
5. Why not just add a column to the sales fact?
6. Follow-up questions interviewers ask
7. The full script

The interview answer

Say this first

A factless fact table has only dimension keys and no numeric measures. It records that an event happened (a customer viewed a product, a student attended a class) or that a condition existed (which products were on promotion). It is analyzed with COUNT, and coverage tables reveal what did NOT happen.

Let's create some sample data

We need three tables: fact_page_views for product page views, fact_promo_coverage for the products on promotion, and a normal fact_sales table to compare against.

Sample data — run this first

DROP TABLE IF EXISTS fact_page_views;
DROP TABLE IF EXISTS fact_promo_coverage;
DROP TABLE IF EXISTS fact_sales;

-- EVENT factless fact: a customer viewed a product page (no measures at all)
CREATE TABLE fact_page_views (
  view_date DATE,
  customer  VARCHAR(50),
  product   VARCHAR(50)
);
INSERT INTO fact_page_views VALUES
  ('2026-01-10', 'Sara Khan', 'Desk Lamp'),
  ('2026-01-10', 'Omar Ali',  'Desk Lamp'),
  ('2026-01-10', 'Omar Ali',  'Wireless Mouse'),
  ('2026-01-10', 'Lina Park', 'Laptop Stand'),
  ('2026-01-11', 'Sara Khan', 'Desk Lamp'),
  ('2026-01-11', 'Mei Chen',  'Laptop Stand');

-- COVERAGE factless fact: which products were on promotion, on which day
CREATE TABLE fact_promo_coverage (
  promo_date DATE,
  product    VARCHAR(50),
  promotion  VARCHAR(50)
);
INSERT INTO fact_promo_coverage VALUES
  ('2026-01-10', 'Laptop Stand',   'New Year Sale'),
  ('2026-01-10', 'Wireless Mouse', 'New Year Sale'),
  ('2026-01-10', 'Desk Lamp',      'New Year Sale');

-- a normal transaction fact for comparison
CREATE TABLE fact_sales (sale_date DATE, product VARCHAR(50), amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES
  ('2026-01-10', 'Laptop Stand',   35.00),
  ('2026-01-10', 'Wireless Mouse', 25.00);

Let's look at the page views.

Query

SELECT * FROM fact_page_views ORDER BY 1;

Result6 rows · 3 columns

view_date	customer	product
2026-01-10	Sara Khan	Desk Lamp
2026-01-10	Omar Ali	Desk Lamp
2026-01-10	Omar Ali	Wireless Mouse
2026-01-10	Lina Park	Laptop Stand
2026-01-11	Sara Khan	Desk Lamp
2026-01-11	Mei Chen	Laptop Stand

Six rows, each with only a date, a customer and a product. There is nothing to add up. Each row just says that this customer looked at this product on this day. Other classic examples are student attendance, employees completing a training and patients visiting a clinic.

Query

SELECT * FROM fact_promo_coverage ORDER BY 1;

Result3 rows · 3 columns

promo_date	product	promotion
2026-01-10	Laptop Stand	New Year Sale
2026-01-10	Wireless Mouse	New Year Sale
2026-01-10	Desk Lamp	New Year Sale

On January 10, TB Shop ran a New Year Sale on three products: Laptop Stand, Wireless Mouse and Desk Lamp. The coverage table records exactly that. Nothing was measured, it simply describes what was possible.

Query

SELECT * FROM fact_sales ORDER BY 1;

Result2 rows · 3 columns

sale_date	product	amount
2026-01-10	Laptop Stand	35.00
2026-01-10	Wireless Mouse	25.00

And the sales fact for January 10 has only two rows, Laptop Stand for 35.00 and Wireless Mouse for 25.00.

Step 1: Count the events

So what is the measure in a factless fact table? It is the count of rows. Let's ask how many views each product got.

Query

SELECT product, COUNT(*) AS page_views
FROM fact_page_views
GROUP BY product
ORDER BY page_views DESC;

Result3 rows · 2 columns

product	page_views
Desk Lamp	3
Laptop Stand	2
Wireless Mouse	1

Desk Lamp has 3 views, Laptop Stand 2 and Wireless Mouse 1. There was nothing to SUM, but COUNT(*) answers "how often did it happen?" just as well.

Step 2: What was promoted but did NOT sell?

Now the question that makes coverage tables worth having: which products were on promotion but did not sell at all?

The sales fact cannot answer it on its own. It only has rows for things that sold, so an unsold product simply is not there. The coverage table knows everything that could have sold. Let's start from coverage, LEFT JOIN to sales on product and date, and keep only the rows with no match.

Query

SELECT c.product, c.promotion
FROM fact_promo_coverage c
LEFT JOIN fact_sales s
  ON s.product = c.product
 AND s.sale_date = c.promo_date
WHERE s.product IS NULL;

Result1 row · 2 columns

product	promotion
Desk Lamp	New Year Sale

The answer is Desk Lamp on the New Year Sale. It was on promotion and it got the most page views, but it did not sell. Maybe the price was wrong or it was out of stock. Either way, this is exactly what the business wants to know, and it is impossible to see from the sales fact alone.

Why not just add a column to the sales fact?

A common follow-up is: why not put a promotion column on the sales fact instead of building a separate table?

• The sales fact only has rows when something sold. A promotion on a product that never sold leaves no trace there.
• Some events have nothing to measure. Page views and attendance still need dates, customers and products to be analyzed.
• Same dimensions as every other fact. A factless fact table gives these events a proper home, joined to the same conformed dimensions as the rest of the warehouse.

Tip

Some teams add a constant column like event_count = 1 so BI tools can sum it. It is still a factless fact table in spirit.

Follow-up questions interviewers ask

What is the measure in a factless fact table?

COUNT of rows (some add a constant event_count = 1 column).

Give two kinds.

Event tracking (attendance, page views) and coverage (promotions, store assortment).

How do you find events that did not happen?

Coverage LEFT JOIN activity WHERE activity key IS NULL, or EXCEPT.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #22 - What is a factless fact table?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q22;  SET search_path TO dwh_q22;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_page_views;
DROP TABLE IF EXISTS fact_promo_coverage;
DROP TABLE IF EXISTS fact_sales;

-- EVENT factless fact: a customer viewed a product page (no measures at all)
CREATE TABLE fact_page_views (
  view_date DATE,
  customer  VARCHAR(50),
  product   VARCHAR(50)
);
INSERT INTO fact_page_views VALUES
  ('2026-01-10', 'Sara Khan', 'Desk Lamp'),
  ('2026-01-10', 'Omar Ali',  'Desk Lamp'),
  ('2026-01-10', 'Omar Ali',  'Wireless Mouse'),
  ('2026-01-10', 'Lina Park', 'Laptop Stand'),
  ('2026-01-11', 'Sara Khan', 'Desk Lamp'),
  ('2026-01-11', 'Mei Chen',  'Laptop Stand');

-- COVERAGE factless fact: which products were on promotion, on which day
CREATE TABLE fact_promo_coverage (
  promo_date DATE,
  product    VARCHAR(50),
  promotion  VARCHAR(50)
);
INSERT INTO fact_promo_coverage VALUES
  ('2026-01-10', 'Laptop Stand',   'New Year Sale'),
  ('2026-01-10', 'Wireless Mouse', 'New Year Sale'),
  ('2026-01-10', 'Desk Lamp',      'New Year Sale');

-- a normal transaction fact for comparison
CREATE TABLE fact_sales (sale_date DATE, product VARCHAR(50), amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES
  ('2026-01-10', 'Laptop Stand',   35.00),
  ('2026-01-10', 'Wireless Mouse', 25.00);


-- Step 1: Event factless fact: the measure is COUNT
SELECT product, COUNT(*) AS page_views
FROM fact_page_views
GROUP BY product
ORDER BY page_views DESC;

-- Step 2: Coverage + sales: what was promoted but did NOT sell?
SELECT c.product, c.promotion
FROM fact_promo_coverage c
LEFT JOIN fact_sales s
  ON s.product = c.product
 AND s.sale_date = c.promo_date
WHERE s.product IS NULL;

Key points

• Only dimension keys, no numeric measures
• Event tables: the row itself is the fact; measure with COUNT
• Coverage tables: what was possible (promotions, assortment)
• Coverage LEFT JOIN activity finds what did NOT happen
• Same conformed dimensions as every other fact

Next in this series

Question 23 — Aggregate tables What are aggregate tables, and when should you build them?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.