Data Warehouse and Dimensional Modeling Questions

Design and model scalable analytical data systems using dimensional modeling principles and data warehouse architecture patterns. Core concepts include fact and dimension tables, defining and enforcing grain, surrogate keys, degenerate and role playing dimensions, conformed dimensions, and handling slowly changing dimensions including Type One, Type Two, and Type Three. Understand schema choices and trade offs such as star schema versus snowflake schema, normalization versus denormalization, and fact table types including transactional, periodic snapshot, and accumulating snapshot. Apply design decisions to meet query patterns and performance goals by considering partitioning, indexing, compression, columnar storage, and aggregation strategies. Be able to design schemas for different business domains, reason about data integration and consistency, and optimize for common analytical workloads and reporting requirements.

MediumTechnical

100 practiced

Explain the trade-offs between normalization and denormalization in dimension tables. Provide three scenarios where denormalization improves analytic performance and two scenarios where normalization is preferable for maintainability or consistency.

Sample Answer

Normalization vs denormalization in dimension tables is a trade-off between storage/consistency and query performance/complexity.

Trade-offs (brief):- Normalization: reduces duplication, enforces referential integrity, simplifies updates (single source of truth). But joins across many small lookup tables increase query latency for wide analytic queries.- Denormalization: embeds related attributes into one wide dimension row (fewer joins), improving read/query performance and simplifying SQL for analysts. Cost: larger storage, higher ETL complexity, increased risk of inconsistent copies and harder updates.

Three scenarios where denormalization improves analytic performance:1. High-concurrency BI dashboards: dashboards join fact table to dimensions for many metrics; denormalized wide dimension removes multiple small joins and reduces query latency for BI tools (e.g., Looker/Power BI).2. Ad-hoc interactive exploration: analysts run many different filters/aggregations; having flattened attributes (category, subcategory, vendor_name) in one table avoids repeated joins and speeds iteration.3. Columnar storage + OLAP queries: when using Redshift/Snowflake/BigQuery, denormalized dimensions allow pruning and vectorized scans that reduce I/O and improve performance for large scans.

Two scenarios where normalization is preferable:1. Frequently changing master data: attributes that update independently (e.g., product hierarchy, vendor contact) should be normalized to avoid costly, error-prone updates across many denormalized rows and to maintain consistency.2. Strict data governance / storage cost concerns: when storage or lineage matters (many downstream systems rely on canonical records), normalized structures simplify ETL, reduce duplicated metadata, and make auditing and referential integrity easier.

Practical guidance: prefer denormalization for read-heavy, low-update analytic workloads and fast analyst experience; prefer normalization when updates, governance, or storage/consistency are primary concerns. Consider hybrid approaches: materialized views, slowly changing dimensions for stability, or periodic denormalized refreshes to balance both needs.

MediumTechnical

76 practiced

Provide an SQL query (choose dialect) that calculates a 7-day rolling active user count from a transactional events fact with schema: events(event_id, user_id, event_ts). The query should be efficient for a warehouse that supports window functions and partitioning by date.

Sample Answer

Approach: first deduplicate events to one row per user per day (daily active), pre-aggregate by event_date (so the query can leverage a date-partitioned table), then compute a 7-day rolling sum over those daily counts using a window function. This is efficient for warehouses that support date partitioning and window frames (BigQuery / PostgreSQL).

sql

-- BigQuery Standard SQL
WITH daily_active AS (
  -- dedupe to one event per user per day, use partition filter for performance
  SELECT
    DATE(event_ts) AS event_date,
    user_id
  FROM `project.dataset.events`
  WHERE event_ts >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)  -- limit scan
  GROUP BY DATE(event_ts), user_id
),
daily_counts AS (
  SELECT
    event_date,
    COUNT(*) AS dau  -- count of distinct users per day (after dedupe)
  FROM daily_active
  GROUP BY event_date
),
calendar AS (
  -- ensure contiguous dates (fills missing days with 0)
  SELECT day AS event_date
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      (SELECT MIN(event_date) FROM daily_counts),
      (SELECT MAX(event_date) FROM daily_counts)
    )
  ) AS day
)
SELECT
  c.event_date,
  COALESCE(d.dau, 0) AS dau,
  SUM(COALESCE(d.dau, 0)) OVER (
    ORDER BY c.event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_active_users
FROM calendar c
LEFT JOIN daily_counts d
  USING (event_date)
ORDER BY c.event_date;

Key points:- Deduplicate events by (user_id, DATE(event_ts)) to compute DAU accurately.- Pre-aggregation reduces data scanned and enables partition pruning if events table is partitioned by DATE(event_ts).- Using a calendar ensures missing days show 0 and the rolling window is contiguous.- Window frame "ROWS BETWEEN 6 PRECEDING" operates on ordered rows (dates) after filling gaps.

Performance & trade-offs:- Scans limited by WHERE and partitioning. For very large windows or full-history distinct counts, consider precomputed daily aggregates or approximate distinct (HLL) for lower memory.- Time complexity dominated by scan and group-by; using partitions/clustering and incremental ETL (materialized daily_counts) improves performance. Edge cases: timezone normalization for event_ts, late-arriving events (handle via backfill or windowed ETL).

EasyTechnical

86 practiced

Describe what a degenerate dimension is and provide a concrete example from an orders system. Explain when keeping an attribute in the fact table as a degenerate dimension is preferable to creating a separate dimension table.

MediumTechnical

80 practiced

Design a conformed date dimension that supports fiscal calendars with variable month boundaries, week-based reporting, and holiday flags. Describe the schema fields you would include, how you would generate the table, and how queries would join to it to enable flexible time-based reporting.

Sample Answer

Requirements (clarify): table must be conformed (single canonical date dim), support fiscal calendars with variable month boundaries (e.g., 4-4-5 or custom month start offsets), week-based reporting (iso & custom week starts), and holiday flags (region-specific).

Schema (recommended fields):- date_key (INT, YYYYMMDD) — PK- date (DATE)- year, month, day, day_of_week (int)- iso_year, iso_week, iso_week_start_date, iso_week_end_date- fiscal_year, fiscal_period, fiscal_month_start_date, fiscal_month_end_date, fiscal_quarter, fiscal_year_week- month_name, quarter_name- week_of_month, week_of_quarter- is_weekend (bool)- holiday_name (STRING, nullable)- is_holiday (bool)- holiday_region (STRING) — for multi-region flags- business_day (bool) — computed with holiday + weekend- attributes JSON or array for custom calendars (e.g., period_type: "4-4-5", offset=)- created_at, updated_at

Generation approach:- Implement as an ELT job (Airflow) that runs once to build full range and incremental for new dates. Use Spark or SQL: 1. Generate calendar sequence for desired range. 2. Compute standard fields using date functions. 3. Compute ISO weeks via library or algorithms. 4. Apply fiscal rules: either parameterize rules (fiscal year start date, month boundary rules or 4-4-5 templates). For 4-4-5, algorithmically assign period by accumulating weeks per period; for custom month boundaries use a table of fiscal_month_starts and join by date >= start AND < next_start. 5. Enrich with holiday table(s) (region, date, name) via left join; set is_holiday, holiday_name, business_day. 6. Store partitioned by year (and/or fiscal_year) in a conformed schema (Parquet/Delta/Lakehouse + metadata).

Sample Spark pseudocode:

python

# spark dataframe `dates` with date column
dates = spark.range(start_ts, end_ts).selectExpr("date_add('1970-01-01', id) as date")
# compute fields, join fiscal_starts and holidays, compute period assignment

Joining patterns in queries:- Date-level granularity: JOIN dim_date d ON fact.event_date = d.date- For fiscal-month reporting: GROUP BY d.fiscal_year, d.fiscal_period- For week reporting: GROUP BY d.iso_year, d.iso_week or d.fiscal_year_week- For holiday-aware metrics: WHERE d.business_day = true OR SUM(CASE WHEN d.is_holiday THEN 1 ELSE 0 END)

Examples:- Monthly revenue by fiscal period: SELECT d.fiscal_year, d.fiscal_period, SUM(f.amount) FROM fact f JOIN dim_date d ON f.date = d.date GROUP BY d.fiscal_year, d.fiscal_period;- Weekly active users (custom week start Monday): SELECT d.iso_year, d.iso_week, COUNT(DISTINCT user_id) FROM fact f JOIN dim_date d ON f.date = d.date GROUP BY d.iso_year, d.iso_week;

Trade-offs & notes:- Keep fiscal rules parameterized; store rule metadata so historical reports remain reproducible.- Precompute heavy fields (fiscal_month boundaries, week ranges) to speed queries.- For multi-region holidays, either keep holiday columns per region or a separate dim_holiday + join.- Partition by date year/fiscal_year for performance; create indexes/materialized aggregates for common rollups.

MediumTechnical

71 practiced

The analytics team runs complex ad-hoc queries joining many dimension tables with the fact. Explain how you would use materialized views, aggregate tables, or OLAP cubes to accelerate common queries. Include eviction/refresh strategies and cost considerations.

Sample Answer

Start by classifying query patterns: identify high-frequency filters, group-bys, time windows, and cardinality of dimensions. Then pick the right acceleration technique.

Materialized views / aggregate tables- Use when queries repeatedly aggregate the fact by a predictable set of dimensions (e.g., daily impressions by campaign, country). Create pre-aggregated tables or DB-native materialized views that store SUM/COUNT and join keys.- Example:

sql

CREATE MATERIALIZED VIEW mv_ads_daily
AS SELECT date, campaign_id, country, SUM(impressions) AS impressions, SUM(clicks) AS clicks
FROM fact_ads GROUP BY date, campaign_id, country;

- Refresh: incremental/append-only refresh using streaming or CDC for low-latency (micro-batches), full refresh nightly for wide schemas. Eviction: drop partitions older than retention window (e.g., keep 2 years).- Cost: storage + compute on refresh. Benefit: huge query-time reduction (orders of magnitude) for aggregated queries.

OLAP cubes / columnar analytic stores- Use for ad-hoc slicing across many dimensions and high cardinality (Druid, ClickHouse, BigQuery BI Engine, Cube.js + OLAP store). They pre-index dimensions, build bitmaps, and support rollups.- Refresh: ingest streaming events or batch rollups. Eviction: segment compaction and TTL for older granular segments.- Cost: higher ingestion/cluster cost but excellent concurrency and sub-second queries.

Trade-offs & strategy- Hybrid: keep raw fact for detailed drill-down; serve common dashboards from materialized/aggregate tables or OLAP rollups.- Use partitioning and clustering keys aligned to query predicates to minimize scan costs.- Autoscaling refresh jobs: schedule incremental updates more frequently for recent partitions, full recompute less often.- Measure: track query hit rate on cached aggregates vs. raw; estimate cost-per-query saved vs. refresh cost to decide which aggregates to maintain.

Security/consistency- Choose atomic refresh or swap tables to avoid partial reads; expose MV metadata so analysts know freshness.

This approach balances latency, cost, and flexibility: pre-aggregate where patterns are stable, use OLAP for flexible slicing, and keep raw facts for ad-hoc deep dives.

Unlock Full Question Bank

Get access to hundreds of Data Warehouse and Dimensional Modeling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.