Database Selection and Trade Offs Questions

How to evaluate and choose data storage systems and architectures based on workload characteristics and business constraints. Coverage includes differences between relational and nonrelational families such as document stores, key value stores, wide column stores, graph databases, time series databases, and search engines; mapping query patterns and latency requirements to storage options; trade offs between strong consistency and eventual consistency and their impact on availability and complexity; partition key design, replication strategies, and high availability considerations; operational concerns including backups, monitoring, vendor and cost trade offs, migration or hybrid strategies, and when to adopt polyglot persistence. Senior level discussion includes selecting specific managed services and reasoning about expected load patterns, failure modes, and operational burden.

MediumSystem Design

43 practiced

Design a storage solution for metrics/time-series: system must handle 100 million unique series, an ingest rate of 500k writes/sec, queries for arbitrary time ranges and rollups, retention of 1 year at full resolution and 7 years of aggregated resolution. Recommend database family and explain compression, downsampling, partitioning/shard placement, and lifecycle.

Sample Answer

Requirements (clarified):- 100M unique series, 500k writes/sec ingest- Query arbitrary time ranges + rollups- 1 year full-resolution retention, 7 years aggregated- Analytical access for models and ad-hoc queries

Recommendation — database family:- Use a columnar/time-series optimized DB with good compression and OLAP capability: e.g., ClickHouse or TimescaleDB (Postgres + hypertables) for analytics; pair with a high-ingest TSDB (Cortex/Thanos or InfluxDB Enterprise) if purely metrics API required. I recommend ClickHouse for heavy analytical queries and long-term aggregation because of columnar compression and fast group-by/rollup performance.

Ingest architecture:- Ingest tier (stateless collectors) buffer writes to a write-ahead queue (Kafka) to absorb spikes.- Consumers batch and write in micro-batches (e.g., 1s windows) to ClickHouse using MergeTree/ReplicatedMergeTree or to TimescaleDB via COPY/partitioned writes.

Partitioning & shard placement:- Primary partition by time (daily/monthly depending on query patterns) + secondary partition / sharding by series_id hash to distribute hot series evenly.- Use replication across availability zones for durability. Assign shard count so writes/sec per shard <= 5-10k to allow headroom (at 500k/s with 100 shards -> 5k/s).

Compression:- Use columnar compression (LZ4/ZSTD) for numeric metrics — ClickHouse compresses dense numeric columns well.- Store tags/labels as dictionary-encoded low-cardinality columns (or a separate label table with integer ids) to reduce storage and speed joins.

Downsampling & lifecycle:- Keep full-resolution raw data for 1 year (high-frequency, e.g., 1s/10s depending on source).- Create automated rollup pipeline that computes aggregated series (min/max/avg/count, percentile sketches) at multiple resolutions (e.g., 1m, 1h, 1d) using streaming jobs (Kafka Streams/Flink) or periodic batch jobs.- Retention policy: raw table TTL = 1 year (ClickHouse TTL or Timescale retention policy); aggregated tiers stored with longer TTLs: 1m -> 1y, 1h -> 7y. Use materialized views to maintain rollups in near real-time.

Querying & rollups:- Query planner directs full-res queries to raw table when within 1 year; for older ranges or when coarse resolution requested, query rollup tables and stitch results.- Support adaptive resolution: for long-range queries, automatically pick coarser rollup to reduce scan and latency.

Operational considerations:- Monitoring of ingest lag, per-shard load, and compaction/merge times.- Backups of metadata and incremental snapshots; use cold object store (S3) for very long-term compressed aggregates.- Schema: keep metric value, timestamp, series_id (int), tags refs, and precomputed aggregates where possible.

Trade-offs:- ClickHouse: excellent compression and analytics, more effort for high-cardinality tag handling and real-time single-point writes. TimescaleDB: simpler relational model and SQL, but may need more hardware for equivalent compression/throughput.- Using Kafka + batching trades a little latency for durability and throughput.

This design balances ingestion scale, query flexibility for data scientists, storage efficiency via compression and downsampling, and lifecycle automation for retention.

EasyTechnical

43 practiced

You're choosing between a columnar data warehouse (Redshift, BigQuery, ClickHouse) and a row-oriented OLTP database for analyzing event logs and building ML features. Describe the factors you would use (query shape, latency, ingestion characteristics, storage cost, concurrency) and give a recommendation for nightly model training versus low-latency feature lookups.

MediumSystem Design

33 practiced

Design an online feature store to support model serving: 10M distinct feature keys, 1M QPS reads with <10ms read latency, nightly batch feature computation, and 7-day online retention. Recommend storage technologies (Redis vs DynamoDB vs Cassandra vs Bigtable), replication strategy, and operational considerations (capacity planning, TTLs, failover).

Sample Answer

Requirements recap:- 10M distinct feature keys, 1M QPS reads, <10 ms read latency, nightly batch compute, 7‑day retention.

Recommended high-level design:- Two-tier online store: durable wide‑column DB as source-of-truth + an in-memory cache layer for very low latency/high QPS. - Durable store: DynamoDB or Bigtable (managed, single-digit-ms P95 reads, automatic scaling). Pick DynamoDB if you want serverless ops and predictable single-digit ms latency; pick Bigtable if you already run GCP and need heavy scan throughput. - Cache layer: Redis Cluster (sharded) as an LRU/TTL cache or read-through cache to absorb most of the 1M QPS and keep tail latency <10 ms.

Why this combo:- Running 1M QPS directly from DynamoDB/Bigtable is possible but expensive and increases egress/latency variability. Redis provides predictable sub-ms reads for cache hits; durable store guarantees persistence and bulk-load from nightly jobs.- Cassandra self‑managed adds operational burden; if you have mature on‑premops, it’s viable, but managed options reduce ops for a DS team.

Data flow:- Nightly batch computes features (Spark/Beam) -> write Parquet to HDFS/GCS/S3 and bulk-load to durable store with TTL/expiry metadata.- Bulk-load process writes both durable store and warms Redis (for hottest keys) via a bulk preload or on‑demand warming.- Serving path: model requests hit a request router -> check Redis (read-through) -> if miss, read durable store -> write-back to Redis.

Replication & failover:- Multi-AZ and multi-region replication for durable store (DynamoDB Global Tables or Bigtable cross‑region replication) for DR.- Redis: use clustered shards and replicas per shard (primary + 1-2 replicas). Use Redis Sentinel/managed Redis (ElastiCache/MemoryStore) for automated failover.- Failover behavior: on cache fail or mass eviction, fall back to durable store; implement circuit-breaker and rate-limiter to protect durable store during cache storms; serve best-effort stale features if durable store is unreachable.

TTL / retention:- Set TTL = 7 days at write-time in durable store (native TTL in DynamoDB/Bigtable) and Redis keys with matching TTLs. Nightly batch can overwrite TTLs as needed.- Use tombstones or soft-deletes if you need audit/history beyond 7 days in archival storage.

Capacity planning & ops:- Estimate record size (avg bytes per key). Example: 1 KB/key -> 10M keys = ~10 GB; with metadata and indices, budget 2–3x headroom. For 1M QPS, ensure Redis cluster can handle throughput: if each Redis node can do ~50k ops/sec, need ~20 shards with replicas (so ~40-60 nodes). For DynamoDB, provision or use autoscaling with sufficient RCUs/TCUs — plan for peak QPS and burst capacity.- Load testing: simulate production QPS and cache-miss storms; measure P50/P95/P99 latencies.- Monitoring & alerts: cache hit ratio, read latency, error rate, CPU/memory, eviction rate, throughput to durable store, TTL expirations.- Security and consistency: use IAM, encryption at rest/in transit; decide eventual vs strong consistency (reads from replicas may be eventual; prefer strongly consistent reads for critical features when needed, at cost of latency).

Operational mitigations:- Hot-key handling: partition hot keys across shards; use local caches or replicated hot-key store; pre-warm Redis with hottest N keys.- Cost/perf tradeoffs: increase cache capacity to reduce durable read cost; use adaptive TTLs to keep only useful keys in Redis.- Backups and replay: retain nightly Parquet and change logs to rebuild store; test rebuild time and have playbooks for partial restores.

Summary recommendation:- Durable store: DynamoDB (or Bigtable if GCP). Cache: Redis Cluster (managed). Multi-AZ/global replication, TTLs set at write-time, bulk nightly loads + cache warming, capacity planning based on per-key size and per-node throughput, and robust failover with circuit breakers and fallback to stale features.

EasyTechnical

45 practiced

Define polyglot persistence and provide three distinct practical examples where different stores are combined in a single product (for example, product catalog: relational for transactions, document DB for denormalized views, search engine for discovery). For each example, explain the data flows and synchronization constraints.

Sample Answer

Polyglot persistence is the practice of using multiple types of data stores within a single system, choosing each store for the workload it suits best (transactions, full‑text search, time series, graph queries, etc.). It improves performance, developer productivity, and scalability by matching data models to access patterns.

Example 1 — E‑commerce product catalog- Stores: Relational DB (orders, inventory), Document DB (product pages, denormalized SKU views), Search engine (Elasticsearch for discovery).- Data flow: Product/price master updated in RDBMS → change capture (CDC) publishes events → transformer writes denormalized product documents to MongoDB and indexes to Elasticsearch.- Sync constraints: Strong consistency needed for inventory/checkout (RDBMS source of truth). Eventually consistent product views/search are acceptable but must reflect price/inventory within seconds for UX.

Example 2 — User activity / personalization- Stores: Event stream (Kafka), Time-series or columnar store (Clickstream analytics), Key-value store (Redis) for real-time user profile, Graph DB for social relationships.- Data flow: Frontend emits events to Kafka → stream processors update real‑time profile in Redis and append aggregated metrics to analytics store; enrichers update graph when relationships change.- Sync constraints: Real-time recommendations require low-latency updates in Redis (near‑real time), analytics can be eventually consistent (minutes), graph updates must preserve transactional integrity for relationship operations.

Example 3 — Fraud detection system- Stores: Relational (customer accounts), Document store (raw transaction payloads), Feature store (vectorized features, e.g., Feast on S3/DB), OLAP warehouse for model training.- Data flow: Transactions persisted to RDBMS and raw JSON to document store → feature pipeline computes features and saves to feature store and warehouse → real‑time scoring service reads features (low latency) and writes alerts back to RDBMS.- Sync constraints: Features for online scoring must be consistent and low-latency; training data can tolerate eventual consistency; auditability requires immutability of raw transaction store.

As a data scientist, design pipelines that respect each store’s consistency and latency guarantees: use CDC/streams for reliable propagation, idempotent consumers, schema/versioning for evolving denormalized views, and monitoring to detect sync lag.

HardTechnical

43 practiced

Describe storage and architectural considerations required to implement GDPR's 'right to be forgotten' across systems: relational databases, NoSQL stores, search indexes, analytics data lake, and backups. Provide an implementation approach (tombstones, lineage, selective anonymization) and a testing/verification strategy.

Sample Answer

Clarify requirements & scope: identify what “personal data” is for your models, legal retention windows, whether erasure must be absolute or pseudonymization/anonymization is acceptable, and which identifiers link datasets (IDs, email, device IDs).

Architecture & storage considerations:- Relational DBs: support cascading deletes, soft-delete flag + tombstone metadata (who/when/reason). Keep audit-log separate and immutable. Use FK constraints cautiously to avoid orphaned PII.- NoSQL stores: similar pattern—store a deletion-state field and versioned records. For append-only stores (Cassandra), use tombstones but ensure compaction windows meet SLA for irreversibility.- Search indexes (Elasticsearch): maintain a deletion queue to remove or update documents; keep index-to-source mapping to find and purge hits.- Analytics data lake: avoid storing raw PII in bulk tables. Use tokenization/pseudonymization at ingestion. Keep lineage metadata to map tokens back to identities only in secure vaults; for erasure, delete tokens and rebuild derived datasets if necessary.- Backups & snapshots: maintain policy for retention and encryption. Implement targeted backup reprocessing—track which backup contains the subject and either purge (if supported) or mark backups for destruction per retention schedule.

Implementation approaches:- Tombstones + eventual purge: mark and propagate delete events through CDC (Kafka) to all sinks; consumers react to tombstone to remove/anonymize.- Selective anonymization: where full deletion breaks analytics or models, replace PII with irreversible hashes/synthetic values and record change in lineage.- Lineage & provenance: maintain centralized metadata catalog mapping source identifiers, datasets, models, transformation steps—used to find and remediate all derived artifacts.

Operational model:- Central Delete Service (API + message bus) that accepts legal requests, authenticates, and emits idempotent delete events with scope. Consumers implement handlers: hard-delete, sanitize, or flag.- Monitoring & access controls: restrict who can reverse pseudonymization; log every action.

Testing & verification strategy:- Unit tests for consumer handlers; integration tests that simulate delete events flowing through CDC into DBs, indexes, data lake, and model feature stores.- End-to-end test harness: populate synthetic subject data with unique markers, run deletion, then assert absence in: - Primary DB rows - NoSQL documents - Search index queries - Downstream analytics tables and feature stores - Model artifacts (check feature vectors rebuilt) - Backups (if immediate removal required, verify scheduled purge or flagged retention)- Periodic audits: automated scans using the lineage catalog and pattern-matching to detect residual PII. Maintain cryptographic proofs (hash commitments) to verify deletion without exposing original data.- SLA & reporting: generate compliance reports per request showing timestamps and locations cleaned; retain immutable audit of the request processing.

Trade-offs:- Immediate hard deletes are costly and may break reproducibility; pseudonymization preserves analytics but needs strict key management.- Complete purging from backups may be infeasible instantly; define legal-acceptable retention and destruction procedures.

As a data scientist, ensure pipelines are designed to allow reproducible model training without raw PII (use tokens/synthetic), so deletion requests minimally disrupt analytics while honoring GDPR.

Unlock Full Question Bank

Get access to hundreds of Database Selection and Trade Offs interview questions and detailed answers.

Join thousands of developers preparing for their dream job.