Data Modeling and Schema Design Questions

Focuses on designing efficient, maintainable data schemas for transactional and analytical systems. Candidates should demonstrate understanding of normalization principles and normal forms, when and why to denormalize for performance, and schema design patterns for different use cases. Expect dimensional modeling topics including fact and dimension tables, star and snowflake schemas, grain definition, slowly changing dimensions, and strategies for handling historical data. The topic also includes trade offs between online transaction processing and online analytical processing designs, query performance considerations, indexing and partitioning strategies, and the ability to evaluate and improve existing schemas to meet business requirements and scale.

MediumTechnical

36 practiced

When storing analytical data as Parquet files in a data lake, how do you choose a partitioning scheme (columns to partition by), directory layout, and target file sizes to balance query performance and cost (e.g., in Presto/Spark)? Discuss small-file problems, partition explosion, and the role of bucketing/clustering.

Sample Answer

Situation: Choosing how to store analytics data in Parquet for Presto/Spark requires trading read latency, compute cost, and maintainability. Below is a practical approach a data scientist can use.

Recommendation summary:- Choose partition columns by query predicates and cardinality: - Prefer low-to-moderate cardinality, highly-filtered columns (date, country, environment). Avoid high-cardinality user_id or transaction_id as top-level partitions. - Use date (year/month/day) for time-series where queries commonly filter by time.- Directory layout: - Hive-style keys: /table/partition_col=val/... for automatic predicate pushdown and tooling compatibility. - Keep partition depth reasonable (2–3 levels). E.g., /date=YYYY-MM/day=MM or /date=YYYY-MM-DD/country=US only if queries often filter both.- Target file sizes: - Aim for 128–512 MB compressed Parquet files for analytic reads; smaller files increase overhead, larger files may reduce parallelism. - In Spark, write with repartition/coalesce to control number of output files; in Presto, ensure splitability.- Small-file problem & compaction: - Small files (< 64 MB) cause many tasks and high metadata overhead; schedule periodic compaction (batch jobs) to merge small files. Use tools: Spark jobs, Delta/ICEBERG/Hudi compaction features, or cloud optimizers.- Partition explosion: - Avoid creating partitions for high-cardinality values or very fine time granularity if most values are empty. Use bucketing/clustering or store high-cardinality columns inside data rather than as partitions. - If many partitions are sparse, consider partition pruning tests and use partition-time retention policies.- Bucketing / clustering: - Use bucketing (or Hive/ICEBERG partitions + buckets) on join keys or high-cardinality grouping columns to improve join performance and reduce shuffle (e.g., bucket by user_id with N buckets matching executors). - Clustering/Z-ordering (in formats that support it) improves range/point lookups by co-locating similar values—useful for multi-column filtering.- Practical checks & metrics: - Monitor query scan bytes, number of files opened, and task startup time. If many small files are read per query, add compaction. - Test common queries: measure latency and cost (engine compute time) after each scheme change.- Trade-offs: - More partitions reduce scanned data but increase metadata and management cost. - Bucketing/clustering reduces shuffle but increases complexity at write time.

Example: For ad-events table: partition by date=YYYY-MM-DD (daily), bucket by user_id into 64 buckets, target ~256 MB files per partition. Run nightly compaction to merge small files and rebuild buckets if necessary.

This approach balances predicate pushdown, parallelism, and manageable metadata while keeping costs and query latency low.

EasyTechnical

38 practiced

Design a product dimension table that must support slowly changing attributes (price and category) and efficient joins to sales facts. Sketch column names and types, indicate keys, and briefly describe how you'd implement SCD behavior in the schema.

HardSystem Design

34 practiced

Architect a data warehouse that ingests 10 TB/day with near-real-time analytics (ingestion latency < 1 minute for recent records). Describe schema patterns (star/snowflake/hybrid), storage choices (cloud columnar engines), partitioning/clustering strategies, CDC ingestion path, incremental materialized views, and cost/performance trade-offs. Explain how the design supports both ad-hoc analyst queries and dashboard SLAs.

Sample Answer

Requirements & constraints:- Ingest 10 TB/day (~116 MB/s sustained) with <1 min ingestion latency for recent records.- Support ad-hoc analytics (complex joins, ML feature engineering) and low-latency dashboards (P95 query < 2s).- Cost-conscious on storage / compute.

High-level architecture:- Streaming layer: Kafka / Kinesis for durable ingest + partitioning per source.- CDC: Debezium connectors produce event streams (insert/update/delete) into Kafka topics.- Stream processing: Flink/Spark Structured Streaming for lightweight enrichment, validation, deterministic upserts, and write-ahead to OLAP stores.- Storage/OLAP: Cloud columnar engine such as Snowflake / BigQuery / ClickHouse / Redshift Serverless as primary analytic store; object store (S3) for raw/parquet lake.- Metadata/Orchestration: Airflow, DBT for DAGs and schema evolution.

Schema patterns:- Hybrid star schema: fact tables (event-level) in wide, partitionable columnar layout; dimension tables normalized to 2NF where appropriate (hybrid snowflake for slowly changing dims).- Facts store event streams with surrogate keys; use conformed dimensions for reuse across facts.

Partitioning & clustering:- Time-range partitioning by ingestion_event_time (hour/day) to support fast recent scans and retention.- Sub-partition or clustering on high-cardinality business keys (customer_id, region) for hotspot pruning and efficient single-customer queries.- Use micro-partitions (BigQuery/Snowflake) or sort keys + distribution keys (Redshift/ClickHouse) tuned by query patterns.

CDC ingestion path:- Debezium → Kafka topics (per table) → Stream processor: - Canonicalize schema, apply business keys, dedupe. - Produce upserts into OLAP via batch micro-batches (every 30s) using table-level MERGE or native upsert APIs. - Also write compacted Parquet snapshots to S3 for time-travel and ML feature backfills.

Incremental materialized views & serving:- Maintain recent-window MV (last 1–7 days) in OLAP using incremental refresh (continuous aggregation in streaming engine or DB-native materialized views refreshed every 30s–1m).- Precompute slow aggregations (daily/hourly rollups) and ML feature store views; store hot slices in a low-latency cache (Redis or ClickHouse readonly replicas) for dashboards.

Cost / performance trade-offs:- Frequent micro-batch/upsert increases compute cost; mitigate by batching writes, using columnar bulk loads, and tiered storage (hot recent partitions on compute-backed tables, colder on S3).- Choosing Snowflake/BigQuery reduces ops but costs per query/storage; ClickHouse/ClickHouse Cloud gives lower latency but requires more engineering.- Retention policy: keep raw Parquet indefinitely (cheap), keep hot partitions for 30–90 days in the analytic engine.

Supporting ad-hoc analysts vs dashboards:- Ad-hoc: Provide read-only clones, sample/export raw Parquet, and a sandbox cluster sized for exploration. Use conformed dims and clear data catalog to speed joins and feature engineering.- Dashboards: Point to aggregated materialized views and cached hot partitions; enforce query SLAs via resource isolation (warehouses/slots), result caching, and query-level timeouts.

Operational concerns:- Monitor ingestion lag, Kafka consumer lag, and MV refresh times. Automated schema evolution with DBT models and contract tests. Backfills via snapshot Parquet → bulk load.This design balances sub-minute freshness for dashboards with scalable, cost-aware storage and strong support for analyst-driven exploration and ML workloads.

MediumTechnical

56 practiced

Given the following Postgres table DDL:

CREATE TABLE transactions (
  transaction_id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  amount NUMERIC(12,2),
  occurred_at TIMESTAMP WITHOUT TIME ZONE,
  metadata JSONB
);

Propose a partitioning and indexing strategy for monthly partitioned data and frequent queries by user_id and date range. Show sample SQL to create a range-partitioned parent and a child partition plus the recommended indexes.

Sample Answer

Approach: use RANGE partitioning on occurred_at by month so queries with date ranges scan only relevant partitions. Create per-partition B-tree composite indexes on (user_id, occurred_at) to accelerate WHERE user_id = X AND occurred_at BETWEEN a AND b. For very large partitions add a BRIN on occurred_at to speed range scans and reduce index size. Keep primary key (transaction_id) defined on parent — Postgres will create it per partition.

SQL (parent + one monthly child + indexes):

CREATE TABLE transactions (
  transaction_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  amount NUMERIC(12,2),
  occurred_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  metadata JSONB,
  PRIMARY KEY (transaction_id, occurred_at)  -- include occurred_at to avoid PK collisions across partitions
) PARTITION BY RANGE (occurred_at);

-- Example: partition for March 2025
CREATE TABLE transactions_2025_03 PARTITION OF transactions
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Indexes on the child partition
CREATE INDEX ON transactions_2025_03 (user_id, occurred_at);
CREATE INDEX ON transactions_2025_03 (occurred_at) USING BRIN;

Notes and recommendations:- Create partitions monthly via a scheduled job (e.g., script/cron) ahead of time.- Composite btree (user_id, occurred_at) supports both user+range and user-only lookups; if you query by occurred_at across users often, keep separate index on occurred_at (BRIN or btree depending on volume).- BRIN is very small and effective for append-only time-series data.- Consider partition pruning by ensuring queries filter occurred_at; avoid functions on occurred_at that prevent pruning.- For global uniqueness of transaction_id consider using a globally unique sequence (UUID/bigserial) or create a unique index across partitions via triggers if required.

MediumTechnical

29 practiced

Create a migration plan (high level) to convert a busy orders table from storing full addresses inline to referencing an addresses table (normalization), ensuring no downtime and minimal risk. Include steps for data migration, application compatibility, and rollback.

Unlock Full Question Bank

Get access to hundreds of Data Modeling and Schema Design interview questions and detailed answers.

Join thousands of developers preparing for their dream job.