Data Modeling for Query Performance Questions

Focuses on schema and data modeling choices that enable efficient querying at scale. Topics include normalization and denormalization trade offs, analytical schemas such as star schema and snowflake schema, the roles of fact tables and dimension tables, modeling for common query patterns and aggregations, and how model choices impact indexing, join costs, and storage. Candidates should be able to justify schema decisions based on query workload, discuss partitioning and sharding implications for model design, and propose modeling adjustments that improve query latency and maintainability.

HardTechnical

29 practiced

You receive order events as nested JSON payloads that include an items array (with item_id, price, promotion) and customer attributes. Design a Spark-based model (table schemas and example DataFrame transformation steps) to store this data to support fast analytical queries: total revenue per item, promotion effectiveness, and customer lifetime metrics. Explain your choice regarding flattening nested arrays versus keeping nested columnar structures, partitioning, and file format.

Sample Answer

Start by defining a canonical schema and then produce two read/write/transform pipelines: one that normalizes (flatten) items into a fact table for analytics, and a compact nested store for full event rehydration.

Canonical incoming JSON schema (logical):- order_id, order_ts, customer: {customer_id, signup_ts, cohort}, items: [{item_id, qty, price, promotion:{promo_id, type, discount_pct}}], order_total, channel

Recommended table schemas:

1) orders_fact (one row per order)- order_id STRING (PK)- order_ts TIMESTAMP- customer_id STRING- order_total DECIMAL- channel STRING- item_count INT

2) order_items_fact (denormalized: one row per item in order) — main analytics table- order_id STRING- order_ts TIMESTAMP- customer_id STRING- item_id STRING- qty INT- price DECIMAL- line_total DECIMAL (qty * price * (1 - discount))- promo_id STRING- promo_type STRING- discount_pct DOUBLE

3) customers_dim- customer_id STRING (PK)- signup_ts TIMESTAMP- cohort STRING- last_order_ts TIMESTAMP- lifetime_value DECIMAL

Why flatten items: queries like total revenue per item and promotion effectiveness require efficient filtering/aggregation by item_id and promo_id. A flattened parquet table with one row per item avoids expensive explode+aggregation at query time and leverages column pruning.

Example Spark transformation (PySpark):

python

from pyspark.sql import functions as F
from pyspark.sql.types import *

raw = spark.read.json("/raw/orders/*.json")

orders = raw.select(
  F.col("order_id"),
  F.to_timestamp("order_ts").alias("order_ts"),
  F.col("customer.customer_id").alias("customer_id"),
  F.col("order_total"),
  F.size("items").alias("item_count"),
  F.col("channel")
)

items = raw.select(
  F.col("order_id"),
  F.to_timestamp("order_ts").alias("order_ts"),
  F.col("customer.customer_id").alias("customer_id"),
  F.explode("items").alias("item")
).select(
  "order_id","order_ts","customer_id",
  F.col("item.item_id").alias("item_id"),
  F.coalesce(F.col("item.qty"), F.lit(1)).alias("qty"),
  F.col("item.price").cast("decimal(10,2)").alias("price"),
  F.col("item.promotion.promo_id").alias("promo_id"),
  F.col("item.promotion.type").alias("promo_type"),
  F.col("item.promotion.discount_pct").cast("double").alias("discount_pct")
).withColumn("line_total", F.expr("qty * price * (1 - coalesce(discount_pct,0))"))

Partitioning & File format:- Store as Parquet (columnar, predicate pushdown, efficient compression).- Partition orders and items by event date (order_date = date(order_ts)) and optionally by env (region/channel) if queries often filter by it.- Use bucketing on item_id (or Z-Ordering in Databricks) for heavy item-level joins/aggregations to reduce shuffle.- Compact small files with periodic compaction jobs (Delta Lake or Hudi recommended for upserts/time travel).

Indexes & optimizations:- Use an MV (materialized view) or pre-aggregated table for daily item revenue: item_revenue_by_day(item_id, order_date, revenue, units, promo_id).- Maintain customers_dim by incremental jobs: update last_order_ts and lifetime_value (sum of line_total) using aggregations on order_items_fact.

Trade-offs:- Flattening increases storage slightly but dramatically speeds analytics and simplifies joins/filters.- Keeping nested JSON in a raw zone is useful for auditing; keep both raw (as JSON/Parquet nested) and curated flattened tables.- Use CDC/upserts (Delta/Hudi/Iceberg) if you need corrections.

This design supports fast revenue-per-item, promo effectiveness (group by promo_id, compare lift), and customer LTV (aggregate line_total by customer), while preserving raw nested events for reprocessing.

EasyTechnical

28 practiced

Given a single large table events(event_id, user_id, event_time, event_type, payload) that is frequently queried by date ranges and event_type, recommend an indexing and physical layout strategy to improve query performance. Consider both row-oriented databases and columnar warehouses: composite/covering indexes, clustering/sort keys, partitioning, and bitmap/applicable indexes. Explain trade-offs for write throughput and storage.

Sample Answer

Goal: speed up queries filtered by date ranges and event_type on events(event_id, user_id, event_time, event_type, payload). Provide recommendations for both row-oriented DBs and columnar warehouses, with trade-offs.

Row-oriented DB (Postgres / MySQL / cloud OLTP):- Partitioning: Range-partition by event_time (daily/weekly/monthly depending on volume) so date-range scans prune partitions.- Composite/covering index: Create a partition-local composite index on (event_type, event_time) or (event_time, event_type) depending on common predicate. If queries always include event_type + time range, index (event_type, event_time) is ideal. Include event_id/user_id as INCLUDE columns to make it covering and avoid lookups.- Clustering: Use CLUSTER / physically reorder table by (event_type, event_time) for cold read-heavy workloads.- Bitmap indexes: Not standard in most row-stores; in Oracle/SQL Server consider bitmap index on low-cardinality event_type (but avoid for high-concurrency OLTP because of locking).

Trade-offs (row-store):- Writes: Partitioning + indexes increase write cost (index maintenance, partition routing). Frequent small partitions increase overhead.- Storage: Covering indexes add storage but reduce I/O for reads.- Concurrency: Bitmap indexes harm concurrent writes; avoid in high-write systems.

Columnar Warehouse (Redshift / Snowflake / BigQuery):- Partitioning / clustering: Use date partitioning on event_time (date partition in BigQuery/Snowflake) so pruning cuts scanned data. Add sort/cluster key on (event_type, event_time) for Redshift or clustering on (event_type, event_time) in Snowflake to co-locate relevant rows.- Columnar benefits: payload stored compressed and scanned only if selected; filters on event_type and date highly selective so scanning cost is low.- Materialized views / pre-aggregated tables: If common rollups exist, keep MV by (event_type, date).- Bitmap indexes: Not typically used; clustering and statistics drive pruning.

Trade-offs (columnar):- Writes: Bulk/batch loads are efficient; many small inserts (streaming) are expensive and may require micro-batches or INSERT staging tables.- Storage: Columnar compression reduces storage for payloads; clustering metadata adds minimal overhead.

Guidelines:- Choose partition granularity by retention and query patterns (daily for high volume, monthly for low).- If writes are heavy and low-latency, minimize secondary indexes and favor partitioning + analytics engine.- Monitor query plans and add covering indexes or clustering iteratively; measure write latency and storage growth.

EasyTechnical

28 practiced

Explain database normalization (up to 3NF) and the main trade-offs when denormalizing for analytical query performance. Provide concrete examples where denormalization reduces expensive joins and improves latency, and where denormalization increases storage and update complexity. When would you prefer normalization in an analytical pipeline?

Sample Answer

Normalization (up to 3NF)- 1NF: atomic values, no repeating groups.- 2NF: 1NF + every non-key attribute fully depends on the whole primary key (remove partial dependencies).- 3NF: 2NF + no transitive dependencies (non-key attributes depend only on the key).

Why normalize: reduces redundancy, enforces data integrity, makes updates/constraints simpler.

Denormalization trade-offs for analytical performance- Benefit: fewer joins → lower query latency. Example: a flattened fact table that stores customer_name and product_category alongside foreign keys avoids joining customer and product dimension tables for common reports. In a high-concurrency dashboard, this can cut query time from seconds to sub-second by eliminating costly distributed joins.- Cost: higher storage and update complexity. Example: if customer address is denormalized into many event rows, an address change requires updating many rows (or waiting for ETL to overwrite), increasing write cost and risk of inconsistent historical snapshots. Wide tables also increase I/O and storage bills.

Concrete patterns- Good denormalization fit: read-heavy OLAP/BI queries where dimensions change rarely — implement star schema, precomputed aggregated tables, or materialized views.- Poor fit: pipelines with frequent upstream updates, strict consistency or audit needs — normalization (or slowly changing dimensions with versioning) keeps a single source of truth.

When to prefer normalization in an analytical pipeline- When data integrity, auditability, and easier incremental updates matter (e.g., regulatory reporting).- When upstream systems update frequently and you need to avoid expensive multi-row updates.- During raw ingestion and canonical staging: keep normalized/raw schema, then denormalize in downstream curated/ marts where performance demands it.

Practical approach: keep a normalized canonical store (for correctness), and selectively denormalize (materialized views, aggregated tables, or pre-joined marts) for performance-sensitive analytical workloads, with clear refresh/CDC strategies and monitoring for consistency.

EasyTechnical

27 practiced

Explain common Slowly Changing Dimension (SCD) types (Type 0, 1, 2, 3) and describe the performance and storage implications of implementing SCD Type 2 for a customer dimension used in analytics. How does SCD Type 2 affect joins from facts to dimensions and what indexes or schemas help efficient historical joins?

Sample Answer

Type 0, 1, 2, 3 — short definitions- Type 0: No changes allowed; original values never updated (static reference data).- Type 1: Overwrite the old value with the new one (no history kept). Simple, small storage, but you lose history.- Type 2: Keep full history by creating a new row for each change. Rows have a surrogate key, business key, and validity columns (valid_from / valid_to or start_date / end_date) and often a current_flag.- Type 3: Keep limited history by adding new columns for previous values (e.g., previous_address). Only a fixed number of historical states preserved.

Performance and storage implications of SCD Type 2- Storage: dimension grows with every change — for high-change attributes or high-cardinality customers this can be significant.- ETL: more complex (detect changes, insert new rows, set end-dates) and higher write volume.- Query performance: point-in-time analytics require joins by date ranges or by version; more rows to scan increases I/O and CPU.

How SCD Type 2 affects joins from facts to dimensions- Two common patterns: 1. Fact stores the surrogate_dimension_key at event time (best): joins are simple equality joins (fact.dim_sk = dim.dim_sk) and very efficient. 2. Fact stores only business_key and event_date: you must perform a temporal join: fact.event_date BETWEEN dim.valid_from AND dim.valid_to (or <= valid_to). These are range joins and can be slower, especially without proper indexing.

Indexes and schemas that help efficient historical joins- Prefer storing the dimension surrogate key in the fact during ingestion (enrich in ETL). This yields fast equality joins and small fact tables (no range filters).- If temporal joins are necessary: - Use composite indexes: (business_key, valid_from) or (business_key, valid_from, valid_to). Many DBs can use these for range scans. - Use partitioning by date on the dimension (valid_from) and/or on facts by event_date to prune partitions. - Use clustered index on surrogate key and/or on (business_key, valid_from) for OLTP/warehouse DBs. - In columnar analytic stores, keep dimensions narrow and use bloom filters or zone maps (e.g., Redshift, Snowflake, BigQuery) to speed range lookups.- Alternatives: maintain an active view/table (current snapshot) alongside the SCD2 history for queries that only need current state; materialized views for common time-window joins.

Recommendation- For analytics, best practice is enrich facts at write-time with the correct dim_surrogate_key (point-in-time), partition facts by event_date, and index the fact.dim_sk for fast star-schema joins. Use SCD2 for attributes where historical correctness matters; otherwise use Type 1 to reduce storage and complexity.

MediumTechnical

37 practiced

Write a performant ANSI SQL query to compute per-user 7-day rolling revenue using window functions on a large events table. Explain how you would partition and order data physically (e.g., partition by user_id, cluster by event_date) so the query parallelizes well, and discuss distribution keys or sharding considerations if the table is distributed across nodes.

Sample Answer

Approach: compute a 7-day rolling sum per user using a window function ordered by event_date. Prefer RANGE with an interval on DATE if supported; otherwise use date-anchored joins or pre-aggregated daily buckets. Physically partitioning by event_date (date grain) and clustering/sorting by user_id then event_date gives good parallelism for time-window queries while keeping user rows contiguous for window computation.

SQL (ANSI-style; if your engine supports RANGE ... INTERVAL):

sql

SELECT
  user_id,
  event_date,
  SUM(revenue) OVER (
    PARTITION BY user_id
    ORDER BY event_date
    RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
  ) AS revenue_7d
FROM events
WHERE event_date BETWEEN :start_date AND :end_date; -- push predicate to prune partitions

If RANGE with INTERVAL is not supported and you have at most one row per (user_id, event_date), you can pre-aggregate daily then use ROWS:

sql

WITH daily AS (
  SELECT user_id, CAST(event_date AS DATE) AS event_date, SUM(revenue) AS rev_day
  FROM events
  GROUP BY user_id, CAST(event_date AS DATE)
)
SELECT
  user_id,
  event_date,
  SUM(rev_day) OVER (
    PARTITION BY user_id
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d
FROM daily;

Key points / reasoning:- Pre-aggregate by date reduces row count for window ops and avoids duplicates.- Push date predicates to prune partitions and reduce I/O.- RANGE with INTERVAL is semantically correct for date windows; ROWS requires dense sequence (one row/day) so pre-aggregation ensures correctness.

Physical layout & parallelism:- Partition by event_date (daily/monthly) to enable pruning for time ranges and parallel scans across partitions.- Cluster (sort) each partition by user_id, event_date so window frames for a user are mostly local and sequential reads are efficient.- On distributed systems, set distribution key / hash on user_id to colocate all events for a user on the same node—this minimizes cross-node shuffles for PARTITION BY user_id windows.- If user_id skew exists (super-users), mitigate by salting user_id (user_id_mod = hash(user_id) % N) or hybrid partitioning: primary partition by event_date, secondary hash (& bucket) by user_id to balance I/O while keeping some locality.- For massively parallel engines (Spark, Redshift, BigQuery): prefer pre-aggregation + partition prune + sort within partition; consider using distribution/sort keys (Redshift) or clustered tables (BigQuery).

Edge cases & operational notes:- Handle late-arriving events with incremental recompute or watermarking.- Watch for timezone/date truncation — normalize event timestamps to a consistent date.- Monitor and rebalance partitions if skew appears; collect statistics for optimizer.- Test with representative data to tune partition size (too small -> metadata overhead; too large -> slow scans).

Complexity: Window computation is linear in number of rows after pre-aggregation; network/shuffle cost depends on distribution strategy (avoid shuffles by co-locating by user_id).

Unlock Full Question Bank

Get access to hundreds of Data Modeling for Query Performance interview questions and detailed answers.

Join thousands of developers preparing for their dream job.