Database Schema Design for Business Scenarios Questions

Design simple database schemas for realistic scenarios (e.g., e-commerce, social media, inventory management). Identify entities, attributes, and relationships. Create entity-relationship diagrams (ERDs). Apply normalization principles. Discuss primary keys, foreign keys, and indexes. Evaluate trade-offs between design options.

EasyTechnical

79 practiced

Explain First, Second, and Third Normal Forms (1NF, 2NF, 3NF). For each normal form provide a concrete example of a schema violation and how to correct it. Briefly describe Boyce–Codd Normal Form (BCNF) and give a scenario where BCNF matters.

HardTechnical

69 practiced

Multiple microservices exchange messages via Kafka using Avro schemas registered in a schema registry. Describe strategies for evolving schemas without breaking consumers: backward/forward/full compatibility rules, use of optional fields and defaults, consumer-driven contracts, and canary rollouts. Also explain how to handle schema evolution for events stored long-term and for relational tables that mirror service boundaries.

Sample Answer

Start with compatibility rules and what they mean for Avro+Schema Registry:- Backward compatible: New schema can read data produced with older schema (consumers compiled against new schema can read old messages). Safe when deploying consumers after producers change.- Forward compatible: Old consumers can read messages written with the new schema (new producers add fields that old consumers can ignore). Useful when you deploy producers first.- Full compatible: Both backward and forward — supports safe rolling both directions.

Practical schema-change patterns:- Add optional fields with a sensible default (Avro: add field with "default" and nullable type). This is the most common safe change — it's backward and forward in many cases depending on default.- Remove fields only if consumers don't require them; prefer deprecation first. Renaming requires aliasing in Avro or two-step add+remove via deprecation.- Change field type carefully: widening (int → long) can be safe; incompatible type changes require migration.

Consumer-driven contracts & testing:- Maintain a consumer contract suite: each consumer publishes expectations (fields used, required types). Run contract tests in CI against new producer schema (and vice versa). Use schema-registry compatibility checks plus automated consumer test runs that deserialize sample messages.

Deployment strategy:- Canary and phased rollouts: deploy producer (or consumer) to a small subset, monitor deserialization errors/metrics, then expand. Combine with feature flags that let new fields be toggled.- Two-version publishing: for large changes, run producers that publish both v1 and v2 topics or include a version field so consumers can handle both schemas while migrating.

Long-term event storage:- Store the schema id along with each event (schema-registry id or full schema) so you can deserialize old events later. Avoid reserializing into new schema unless you run a controlled backfill. For long-term retention, keep a mapping of schema evolution and migration scripts; maintain an offline ETL job to normalize historical events into current analytical model when needed.

Relational tables mirroring service boundaries:- For tables owned by the service, migrate using out-of-band migrations: add new columns with defaults and nullable, backfill asynchronously, then switch consumers to new columns before dropping old ones. Use change-data-capture (CDC) to propagate schema changes to downstream systems; coordinate schema change windows when multiple consumers depend on the same table.- When multiple teams read the table, treat it as a contract: prefer additive changes, advertise deprecations, and require consumer opt-in for breaking changes. Use views or materialized views to provide stable analytical schemas while services evolve.

Monitoring and governance:- Track schema compatibility violations, consumer deserialization errors, and metric thresholds. Enforce schema evolution policies in CI/pipeline (reject incompatible changes unless explicitly approved).- Document schema owners and migration playbooks; automate testing and canary rollouts.

Summary: prefer additive, defaulted, nullable changes; use consumer-driven contracts and CI to validate; roll out via canaries and versioning; store schema metadata with events; for relational mirrors use additive schema + CDC/backfill and strong coordination.

HardSystem Design

80 practiced

For a monitoring system ingesting high-volume metrics, design a schema and aggregation pipeline that stores raw metrics and materialized rollups (minute/hour/day aggregates). Explain how to implement materialized views or scheduled aggregation jobs, how to maintain correctness after late-arriving data, and how to partition and compress raw and aggregate data to optimize cost and query performance.

Sample Answer

Requirements & constraints:- Ingest high-volume time-series metrics (millions/sec), support raw queries and fast pre-aggregated minute/hour/day rollups, low-latency writes, bounded storage cost, correct aggregates despite late-arriving data, configurable retention.

High-level design:- Ingest -> Buffer/stream -> Raw storage (hot) + Real-time aggregator -> Materialized rollups (minute/hour/day) -> Long-term cold storage.

Schema- Raw metrics table (append-only): (tenant_id, metric_name, tags_hash, ts_ms, value, dimensions JSON) - Primary key: (tenant_id, metric_name, tags_hash, ts_ms)- Rollup tables: minute_rollup, hour_rollup, day_rollup with schema: (tenant_id, metric_name, tags_hash, bucket_start_ts, count, sum, min, max, sum_squares) - Use bucket_start_ts aligned to minute/hour/day.

Ingestion & Aggregation- Stream ingestion via Kafka / Kinesis. Use a stream processor (Flink/Spark Structured Streaming) to: - Persist raw events to cold object store (Parquet/ORC) or a TSDB (e.g., ClickHouse/Timescale/Influx) partitioned by day + tenant. - Maintain real-time in-memory incremental aggregates per bucket; flush to materialized tables at bucket end (or frequently).- Implement materialized views as idempotent upserts: - Use upsert semantics (e.g., INSERT ... ON CONFLICT UPDATE or Kafka compacted topic + consumer) to incrementally merge aggregate deltas: count +=, sum +=, min = min(...), max = max(...), sum_squares +=. - Two options: 1. Continuous materialized view: stream processor writes aggregates in near real-time. 2. Scheduled batch job: every minute/hour run a windowed aggregation job over raw data for that interval and upsert results—simpler and easier to validate.

Late-arriving data & correctness- Use watermarking in stream processing: accept lateness window (e.g., 2 hours). Within window, stream processor updates in-place aggregates.- For data beyond lateness window, run periodic backfill/reconciliation: - Maintain a compacted "delta" topic of raw writes; nightly or hourly run correction jobs that recompute aggregates for affected buckets by re-aggregating raw data for that bucket and compare vs stored aggregate; write correction as idempotent upsert.- Store versioning / last_updated_ts on rollup rows to detect stale aggregates and enable audit.

Partitioning, indexing & compression- Raw data storage: - Partition by (tenant_id hash mod N, date) in object store/warehouse (Parquet with snappy/zstd), sort within file by (metric_name, ts) to speed range scans. - Or use TSDB with time-partitioned shards (e.g., ClickHouse partition by toYYYYMM(ts), primary key (tenant, metric, ts))- Rollups: - Partition by (tenant_id hash mod M, bucket_date). Smaller partitions to keep rollup tables small and fast. - Use columnar format and compress heavily; rollups are small so zstd max gives good compression.- TTL / retention: - Raw: keep high-resolution raw (e.g., 1s) for short period (7–30 days), longer-term keep downsampled/raw-indexed for hot tenants only. - Rollups: retain minute for N days, hourly for months, daily for years.- Indexing: - Secondary index on (metric_name, tags_hash) for quick lookups; use inverted index if many tags.

Operational considerations- Idempotency: ensure writes to rollups are idempotent (use deterministic keys and atomic upserts). Use transactions or exactly-once stream processing if supported.- Backfills & schema changes: keep lineage and re-runable pipelines; use partitioned processing so re-processing limited scope.- Cost/Perf tradeoffs: store raw in cheap object store and keep rollups in a low-latency OLAP DB for queries. Cache most recent minute/hour aggregates in Redis for sub-second dashboards.- Monitoring & alerts: track lag, missing buckets, correction rates.

This design balances ingest scalability, query latency (materialized rollups), correctness (watermarks + reconciliation), and cost (cold raw in compressed columnar files, small rollup tables).

MediumSystem Design

72 practiced

Design a star schema for sales analytics with fact_sales and dimensions for customer, product, store, and date. The customer dimension must support Slowly Changing Dimensions (SCD Type 2) to capture historical changes. Provide table definitions (columns and keys), explain the use of surrogate keys, describe how to load SCD Type 2 records in ETL/ELT, and outline partitioning and indexing strategies for fast analytical queries at scale (hundreds of millions of rows).

Sample Answer

Requirements (clarify): fact table records each sale (measures: qty, price, discount, revenue); dimensions: customer (SCD Type 2), product, store, date. Support hundreds of millions of rows and fast analytic reads.

Table definitions (columns and keys):

customer_dim- customer_sk (INT/ BIGINT, surrogate PK)- customer_nk (VARCHAR) — natural/business key (customer_id)- first_name, last_name, email, segment, etc.- effective_from (DATE/TIMESTAMP)- effective_to (DATE/TIMESTAMP) — exclusive upper bound or NULL = current- is_current (BOOLEAN)- hash_or_checksum (VARCHAR) — optional for change detectionPrimary key: customer_skUnique index: (customer_nk, effective_from)

product_dim- product_sk (INT/BIGINT PK)- product_nk (product_id)- name, category, brand, price, attributes...Primary key: product_skUnique index: product_nk

store_dim- store_sk (INT/BIGINT PK)- store_nk (store_id)- name, region, city, store_type...Primary key: store_skUnique index: store_nk

date_dim- date_sk (INT PK, e.g., YYYYMMDD)- date, year, quarter, month, day_of_week, is_holiday...Primary key: date_sk

fact_sales- sale_sk (BIGINT PK, optional surrogate)- sale_id (business id)- sale_date_sk (FK -> date_dim.date_sk)- customer_sk (FK -> customer_dim.customer_sk) — points to the customer row valid at sale time- product_sk (FK -> product_dim.product_sk)- store_sk (FK -> store_dim.store_sk)- qty, unit_price, discount, total_amount, created_atPartition key: sale_date_sk or sale_date (see partitioning)Cluster key/sort: (date_sk, product_sk) for common queries

Surrogate keys: Use integer surrogate keys (auto-increment or sequence) in dims to decouple from changing natural keys and to efficiently join and store compact FKs. For customer SCD2, surrogate allows multiple versions per business customer_id.

ETL/ELT process for SCD Type 2 (customer):1. Detect changes: join incoming customer feed on customer_nk; compare business attributes via checksum/hash.2. For new customer_nk: INSERT new customer_dim row with effective_from = load_ts, effective_to = NULL, is_current = true, assign new customer_sk.3. For changed customer_nk where existing is_current = true: UPDATE existing row set effective_to = load_ts, is_current = false; INSERT new row with new surrogate customer_sk, effective_from = load_ts, effective_to = NULL, is_current = true.4. For unchanged: no op.5. Backfill/fixes: update effective_to ranges carefully; ensure idempotency using upsert keys.Implementation patterns: use CDC tools (Debezium), MERGE statements in ELT (Snowflake/BigQuery/Redshift/Spark SQL) or batch Spark jobs with transactional sinks.

Populating fact_sales:- Enrich incoming sales events with dimension surrogate keys: for customer use lookup on customer_nk to find the customer_dim row where effective_from <= sale_ts < effective_to (or is_current if sale_ts is current). In ELT prefer joining using range-aware window: in SQL MERGE join on customer_nk and sale_date between effective_from and coalesce(effective_to, '9999-12-31').- Write fact rows with those surrogate FKs.

Partitioning strategies:- Partition fact_sales by date (day/month) using sale_date_sk or sale_date to prune scans. For hundreds of millions of rows, monthly partitions are common; daily for higher volume.- Partition large dimensions by hash(product_sk) only if extremely large; usually dimensions fit in memory and aren't partitioned.- For cloud warehouses: use native partition/clustering (Snowflake micro-partitions + clustering keys on (customer_sk, product_sk)), BigQuery partitioned tables by date and clustered by customer_sk, product_sk.- Keep date_dim small and unpartitioned; customer_dim SCD2 can be clustered by customer_nk and effective_from.

Indexing and clustering:- On fact_sales: composite index or sort key on (sale_date_sk, customer_sk, product_sk) depending on engine. In Redshift, use sort keys; in Postgres/Greenplum create BRIN on date and B-tree on (customer_sk). In BigQuery/Snowflake use clustering keys to group by customer_sk/product_sk for efficient filters.- On customer_dim: index (customer_nk, effective_from, effective_to) to support point-in-time lookups; include is_current for current lookups.- On product_dim/store_dim: index their natural keys.

Query performance practices:- Use precomputed aggregates (daily rollups) for commonly used dashboards.- Use materialized views for top-N queries or heavy joins.- Avoid joining on natural keys at query time; always join via surrogate keys in fact.- For point-in-time historical joins (e.g., what was customer segment at purchase), ensure fact references the correct customer_sk version at load time. If backfilling, re-run enrichment for affected date ranges.

ETL operational considerations:- Use MERGE with dedupe and idempotent loads.- Maintain audit columns: load_ts, source_id, batch_id.- Run dimension SCD2 processing before fact enrichment or use a delayed enrichment step ensuring dimension state for the sale timestamp is available.- Monitor dimension growth and run compaction for small obsolete SCD rows if needed.

Trade-offs:- Storing customer_sk on fact fixes history at write time and simplifies queries, but requires correct enrichment; storing only customer_nk + sale_ts and joining at query time supports re-evaluating history but is costly.- Partition granularity balances pruning vs too many partitions.

This design supports SCD2 history, efficient joins via surrogate keys, partitioned facts for scale, and indexes/clustering that accelerate common analytics.

MediumSystem Design

71 practiced

Design schema tables to capture audit logs and data lineage for ETL jobs. Requirements: store job runs, input datasets (table, partition, timestamp), SQL/transformation version, output datasets, and optionally row-level provenance for debugging. Provide table structures, examples of queries to find the origin of a record, and storage/retention recommendations for lineage metadata.

Sample Answer

Clarify requirements: capture run-level metadata, input datasets (table, partition, timestamp), transformation version (SQL/git hash), output datasets, and optional row-level provenance. I'll propose normalized metadata tables plus an optional detailed provenance store.

Schema (relational/managed metastore):

sql

-- Job runs
CREATE TABLE job_run (
  run_id UUID PRIMARY KEY,
  job_name VARCHAR,
  start_ts TIMESTAMP,
  end_ts TIMESTAMP,
  status VARCHAR,         -- RUNNING, SUCCESS, FAILED
  executor VARCHAR,       -- cluster id / user
  transform_version VARCHAR, -- git SHA or DAG id
  comments TEXT
);

-- Datasets referenced by jobs
CREATE TABLE dataset (
  dataset_id UUID PRIMARY KEY,
  catalog VARCHAR,        -- e.g., hive, bigquery
  schema_name VARCHAR,
  table_name VARCHAR,
  logical_name VARCHAR,   -- e.g., sales.events
  created_ts TIMESTAMP
);

-- Partitions / input slices
CREATE TABLE dataset_slice (
  slice_id UUID PRIMARY KEY,
  dataset_id UUID REFERENCES dataset(dataset_id),
  partition_spec JSONB,   -- {"ds":"2025-01-01","hour":"12"} or NULL
  snapshot_ts TIMESTAMP   -- snapshot time or ingestion time
);

-- Many-to-many: which inputs consumed by a job_run
CREATE TABLE job_input (
  run_id UUID REFERENCES job_run(run_id),
  slice_id UUID REFERENCES dataset_slice(slice_id),
  read_bytes BIGINT,
  PRIMARY KEY (run_id, slice_id)
);

-- Outputs produced by a job_run
CREATE TABLE job_output (
  run_id UUID REFERENCES job_run(run_id),
  dataset_id UUID REFERENCES dataset(dataset_id),
  slice_id UUID REFERENCES dataset_slice(slice_id),
  write_rows BIGINT,
  write_bytes BIGINT,
  PRIMARY KEY (run_id, dataset_id, slice_id)
);

-- Optional row-level provenance (heavy; store sparingly)
CREATE TABLE row_provenance (
  prov_id UUID PRIMARY KEY,
  output_dataset_id UUID,
  output_pk JSONB,        -- primary key of output row
  source_refs JSONB,      -- list of {dataset_id, slice_id, row_identifier}
  run_id UUID REFERENCES job_run(run_id),
  created_ts TIMESTAMP
);

Example queries

1) Find job runs that produced a specific table partition:

sql

SELECT jr.* FROM job_run jr
JOIN job_output jo ON jr.run_id = jo.run_id
JOIN dataset_slice ds ON jo.slice_id = ds.slice_id
JOIN dataset d ON ds.dataset_id = d.dataset_id
WHERE d.logical_name='sales.events' AND ds.partition_spec->>'ds'='2025-01-01';

2) Trace origin of an output row (uses row_provenance):

sql

SELECT rp.source_refs
FROM row_provenance rp
WHERE rp.output_dataset_id = '<uuid>' AND rp.output_pk = '{"order_id":123}';

3) Upstream lineage graph (one hop):

sql

SELECT DISTINCT d.logical_name, ds.partition_spec
FROM job_input ji
JOIN dataset_slice ds ON ji.slice_id = ds.slice_id
JOIN dataset d ON ds.dataset_id = d.dataset_id
WHERE ji.run_id IN (
  SELECT run_id FROM job_output jo WHERE jo.dataset_id = '<output_dataset_id>'
);

Storage & retention recommendations- Keep run-level, dataset, and slice metadata indefinitely or at least 1–3 years (cheap, small).- Row-level provenance is high-volume: keep short-term (30–90 days) in a compressed, queryable store (Parquet on object storage, or specialized provenance DB). Persist sampled or on-demand full provenance; otherwise store lineage fingerprints (hashes) and the transform_version to reproduce provenance by re-running with debug flags.- Partition metadata (dataset_slice) should be partitioned by snapshot_ts for efficient pruning.- Archive old provenance to cold storage (S3 Glacier) and delete after policy period.- Use a metadata service (e.g., Airflow hooks, Data Catalog, openlineage) to emit events and enforce retention/ingest.

Best practices- Emit lineage at job completion with transform_version and SQL text; store SQL in a versioned code repo and reference commit SHA.- Enforce deterministic IDs for dataset slices so lineage joins are stable.- For debugging, support on-demand provenance capture (run job with provenance sampling).- Index common query columns (run_id, dataset_id, partition keys) and keep JSON fields small/normalized when used often.

Unlock Full Question Bank

Get access to hundreds of Database Schema Design for Business Scenarios interview questions and detailed answers.

Join thousands of developers preparing for their dream job.