Advanced13 min read

Dimensional Modeling Interview Questions

Grain, facts, dimensions, and slowly changing dimensions — the modeling questions that decide whether you are hired as a senior engineer, with worked telecom examples.

Modeling questions are the best signal an interviewer has for seniority, because there is no syntax to hide behind. You either think in grain, keys and history, or you do not.

Always start with the grain

Tip — The four-step design method (Kimball)

1) Pick the business process. 2) Declare the grain — 'one row = one ___'. 3) Identify the dimensions. 4) Identify the facts (the numbers). Say these four steps out loud and you have already answered half the question.

A star schema for network sessions. One fact table at the centre, flat dimensions around it.

Facts and their types

Fact type	One row =	Example
Transaction	One discrete event	One network session, one dropped call
Periodic snapshot	One entity per fixed period	Daily KPI per cell site
Accumulating snapshot	One process instance, updated over time	A provisioning order: requested → activated → completed
Factless	An event with no measure	A subscriber was eligible for a promotion on a date

Also know additivity, which interviewers probe with 'can you SUM this?': fully additive measures (data volume) sum across every dimension; semi-additive measures (account balance, active subscribers) sum across some dimensions but not time; non-additive measures (ratios like drop rate) cannot be summed at all — you must recompute them from their numerator and denominator.

Watch out — The ratio trap

Never store only drop_rate in a fact table. Store dropped_calls and call_attempts, and compute the ratio at query time. Averaging an average across sites gives a wrong number — a favourite interview gotcha.

Dimensions and slowly changing dimensions

SCD Type 2: a change creates a new row with its own surrogate key and validity window, so history stays correct.

Type	Behaviour	Choose when
Type 0	Never changes	Immutable attributes like original signup date
Type 1	Overwrite in place, history lost	Corrections and typos; history is not meaningful
Type 2	New row + validity dates + is_current flag	History matters — the default for real business attributes
Type 3	Extra column holding the previous value	You need exactly one prior value, e.g. 'previous plan'
Type 6	Hybrid of 1 + 2 + 3	You need both 'as-was' and 'as-is' views of the same attribute

-- SCD Type 2 upsert with MERGE in BigQuery
merge core.dim_subscriber t
using stage.subscriber_changes s
  on t.subscriber_id = s.subscriber_id
 and t.is_current = true

-- 1. close the old row when a tracked attribute changed
when matched
     and t.tariff_plan != s.tariff_plan then update set
  t.valid_to    = s.change_date,
  t.is_current  = false

-- 2. brand new subscriber
when not matched then insert
  (surrogate_key, subscriber_id, tariff_plan, valid_from, valid_to, is_current)
values
  (generate_uuid(), s.subscriber_id, s.tariff_plan, s.change_date, '9999-12-31', true);

-- a second INSERT pass adds the new current row for changed subscribers

Surrogate keys

A surrogate key is a warehouse-generated key for a dimension row, independent of the source system's natural key
Type 2 dimensions require one — the same subscriber_id has several rows, so the natural key is no longer unique
It insulates you from source changes (system merges, key reuse, format changes)
In BigQuery, prefer a deterministic hash (farm_fingerprint of business key + valid_from) over a random UUID, so rebuilds are reproducible and idempotent

Practice questions

QDesign a data model for network quality analytics. Walk me through your thinking.show

Use the four-step method out loud, and state the grain before anything else.

Process — network sessions and quality measurements per subscriber, per site
Grain — 'one row per network session' for the detail fact; plus a daily periodic snapshot per site for reporting speed
Dimensions — dim_date, dim_subscriber (Type 2 for tariff/segment), dim_cell_site (Type 2 for location/config), dim_device, dim_network_type
Facts — session duration, bytes up/down, dropped flag, latency; store counts and denominators, never pre-divided ratios
Physical — partition the fact by event_date, cluster by cell_site_id; conformed dimensions shared across all network domains so metrics are comparable
Why the snapshot too — billions of session rows are too slow for dashboards; a daily aggregate keeps Tableau fast while detail stays available for drill-down

Likely follow-up: How do you keep the daily snapshot consistent with the detail fact?

QWhat is a conformed dimension and why does it matter?show

A conformed dimension is one shared, identically defined dimension used across multiple fact tables and business domains.

It lets you compare and combine metrics across domains — the same dim_cell_site joins to sessions, outages and capacity facts
Without it, every team builds its own site dimension and no two reports agree
This is exactly the value of a central core layer with reusable data products: one definition, many consumers

QWhen would you deliberately NOT use a star schema?show

Star schemas are for analytical serving. Other shapes fit other jobs.

OLTP systems — stay normalised (3NF) for write integrity
Raw/landing layer — keep source structure as-is so you can always replay
One Big Table — for a single well-known access pattern, a flat wide table in a columnar store can beat a star (no joins at all)
Data Vault — when auditability and many changing sources matter more than query simplicity; often loaded into star schemas downstream

QHow do you handle a many-to-many relationship in dimensional modeling?show

Use a bridge table, and be explicit about the double-counting risk it introduces.

Example: one network incident affects many cell sites — a bridge_incident_site table holds the pairs
Add a weighting factor if you need allocated, non-double-counted totals
Always state the risk: naively summing across a bridge inflates results, so the semantic layer must handle it

← PreviousGCP Data Services: Choosing the Right Tool Next →dbt and the Transformation Layer