Designing a Star Schema for Web Analytics Data

Why schema design still matters in a columnar world

Columnar engines are fast enough that people skip modelling entirely and dump everything into one wide table. That works until you need to answer a question the table cannot express — "what was this page's title at the time it was viewed?" — or until every query re-derives the same joins.

A star schema gives you a shared vocabulary: facts are things that happened, dimensions are the things they happened to.

Start with the grain

The grain is the single most important decision: what does one row in the fact table represent? Get it wrong and every metric downstream is subtly incorrect.

For web analytics there are usually three fact tables at three grains:

• fact_pageview — one row per pageview
• fact_session — one row per session (derived)
• fact_event — one row per custom event (click, signup, purchase)

Resist the urge to fold sessions into pageviews. "Bounce rate" is a session-grain metric; computing it from a pageview table means a count(distinct) gymnastics routine in every query.

The dimensions

CREATE TABLE dim_page (
  page_key    BIGINT PRIMARY KEY,
  site_id     INTEGER NOT NULL,
  path        VARCHAR NOT NULL,
  title       VARCHAR,
  section     VARCHAR,
  valid_from  TIMESTAMP NOT NULL,
  valid_to    TIMESTAMP,
  is_current  BOOLEAN NOT NULL
);

CREATE TABLE dim_device (
  device_key  BIGINT PRIMARY KEY,
  browser     VARCHAR,
  browser_ver VARCHAR,
  os          VARCHAR,
  device_type VARCHAR,   -- desktop | mobile | tablet
  is_bot      BOOLEAN
);

CREATE TABLE dim_geo (
  geo_key     BIGINT PRIMARY KEY,
  country     VARCHAR,
  region      VARCHAR,
  timezone    VARCHAR
);

CREATE TABLE dim_source (
  source_key   BIGINT PRIMARY KEY,
  referrer_host VARCHAR,
  channel       VARCHAR,  -- direct | organic | social | referral | paid
  utm_source    VARCHAR,
  utm_medium    VARCHAR,
  utm_campaign  VARCHAR
);

And the fact:

CREATE TABLE fact_pageview (
  pageview_id   BIGINT PRIMARY KEY,
  date_key      INTEGER NOT NULL,     -- 20260706
  timestamp     TIMESTAMP NOT NULL,
  site_id       INTEGER NOT NULL,
  visitor_key   BIGINT NOT NULL,
  session_id    BIGINT NOT NULL,
  page_key      BIGINT NOT NULL,
  device_key    BIGINT NOT NULL,
  geo_key       BIGINT NOT NULL,
  source_key    BIGINT NOT NULL,
  duration_ms   INTEGER,              -- measures
  scroll_pct    SMALLINT
);

Facts hold keys and measures. Nothing else. If you find yourself adding a descriptive string to a fact table, it belongs in a dimension. Naming the events that feed these tables is its own discipline — see event schema design.

Why a date dimension earns its keep

CREATE TABLE dim_date (
  date_key    INTEGER PRIMARY KEY,   -- 20260706
  date        DATE,
  day_of_week SMALLINT,
  week_of_year SMALLINT,
  month       SMALLINT,
  quarter     SMALLINT,
  year        SMALLINT,
  is_weekend  BOOLEAN,
  is_holiday  BOOLEAN
);

It looks redundant next to a timestamp column, but it lets you answer "weekday vs weekend conversion" or "fiscal quarter" with a join instead of a pile of date arithmetic — and it makes the fiscal calendar a data problem rather than a code problem.

Slowly changing dimensions

When a page's title changes, do you want old pageviews to show the old title or the new one?

• Type 1 — overwrite. Simple, loses history. Fine for correcting a typo in a browser name.
• Type 2 — new row with valid_from / valid_to and a new surrogate key. History preserved. This is why dim_page above has validity columns.

Type 2 is why facts reference a surrogate page_key rather than the path string: the key pins the row to the version of the page that existed at that moment.

Where to denormalize for DuckDB

Textbook star schemas assume join cost matters. On a columnar engine, small dimension joins are cheap — but not free, and they add query complexity.

Practical compromise: keep the star as the model of record, and materialize a wide view for the dashboard.

CREATE OR REPLACE VIEW v_pageview_wide AS
SELECT f.timestamp, f.site_id, f.session_id, f.visitor_key,
       p.path, p.title, d.browser, d.device_type,
       g.country, s.channel, s.utm_source,
       f.duration_ms, f.scroll_pct
FROM fact_pageview f
JOIN dim_page   p USING (page_key)
JOIN dim_device d USING (device_key)
JOIN dim_geo    g USING (geo_key)
JOIN dim_source s USING (source_key);

Dashboard queries hit the view. Because DuckDB pushes projections down, selecting three columns from the view does not materialize all twenty.

Deriving the session fact

Sessions are computed, not tracked. A standard 30-minute inactivity rule:

CREATE OR REPLACE TABLE fact_session AS
WITH marked AS (
  SELECT visitor_key, timestamp, page_key, source_key,
         CASE WHEN timestamp - lag(timestamp) OVER w > INTERVAL 30 MINUTE
                OR lag(timestamp) OVER w IS NULL
              THEN 1 ELSE 0 END AS is_new
  FROM fact_pageview
  WINDOW w AS (PARTITION BY visitor_key ORDER BY timestamp)
),
grouped AS (
  SELECT *, sum(is_new) OVER (PARTITION BY visitor_key ORDER BY timestamp) AS sess_num
  FROM marked
)
SELECT visitor_key, sess_num,
       min(timestamp) AS started_at,
       max(timestamp) AS ended_at,
       count(*)       AS pageviews,
       count(*) = 1   AS is_bounce
FROM grouped
GROUP BY visitor_key, sess_num;

Now bounce rate is avg(is_bounce) — one column, no subquery. The window-function guide covers this sessionization pattern in depth.

Frequently asked questions

Does schema design still matter with a columnar engine?

Yes, though less for storage than for clarity. Columnar compression forgives some denormalization, but grain confusion — mixing pageviews and sessions in one table — produces wrong numbers regardless of engine.

What grain should the pageview fact table use?

One row per pageview. Choose the finest grain you will need to answer questions at, then derive sessions and daily rollups from it. Aggregating early is easy to do and impossible to undo.

Why keep a separate date dimension?

So that fiscal periods, holidays, and week definitions live in one place instead of being recomputed in every query. It also makes calendar joins cheap and keeps date logic consistent across reports.

Should I denormalize dimensions into the fact table?

For low-cardinality attributes read on nearly every query — device type, country, browser — yes. Columnar compression makes repeated values nearly free, and skipping the join is a real saving. Keep high-cardinality or frequently-changing attributes in dimensions.

How do I handle a dimension value that changes over time?

Decide whether history matters. If you need to know what was true at event time, use a type-2 dimension with validity ranges; if you only care about the current value, overwrite it. Web analytics needs type 2 less often than classic BI does.

Rules that keep the model honest

1. One grain per fact table, declared in a comment at the top of the DDL.

2. Facts contain keys and numbers only.

3. Every dimension gets a surrogate key, never a natural key like a URL string.

4. Add an "unknown" row (key -1) to every dimension so facts never carry NULL keys.

5. Additive measures where possible. Ratios like bounce rate are computed at query time from additive parts, never stored pre-divided — averaging averages is how dashboards start lying.