Database and Data Platform Selection Questions

Evaluation and selection of database and data platform technologies to meet analytical and operational needs. Covers assessment of relational, non relational, columnar, and specialized systems such as time series and search engines; data warehouse platforms and cloud analytics platforms; query patterns and workload characteristics; consistency and transactional guarantees; partitioning and clustering strategies; storage formats and compression; performance and scalability trade offs; operational complexity and administration overhead; data ingestion and incremental loading patterns; pricing and cloud platform considerations; and how to choose the right solution based on data volume, concurrency, latency requirements, and total cost of ownership.

MediumTechnical

49 practiced

Design a pattern to implement cross-service consistency for an order-processing workflow that writes to an OLTP orders DB and also writes events to an analytics warehouse. Compare implementing distributed transactions (2PC) vs eventual consistency via SAGA or outbox + CDC. Consider failure modes, latency, and developer experience.

Sample Answer

Requirements & constraints:- Strong consistency between OLTP orders DB and analytics warehouse is desirable but not strictly required in real time.- Scale: high write rate, multiple services, heterogeneous storage (RDBMS + data warehouse).- Non-functional: availability, low operational complexity, recoverability, and auditability.

Option A — Distributed transactions (2PC):- Approach: Use XA/2PC coordinator to atomically commit order write and event write across systems.- Failure modes: coordinator single point of failure, locking in resource managers causing long-held DB locks, complex recovery when participants crash mid-commit. If the analytics system doesn’t support XA (common for warehouses), impossible.- Latency: higher synchronous latencies because commit requires coordinator round-trips to all participants.- Developer experience/ops: high cognitive and operational burden; brittle across heterogeneous systems; hard to scale across geo/managed services.

Option B — SAGA (orchestrated/choreographed):- Approach: Break the workflow into compensatable steps (create order → publish event → update downstream). Orchestrator coordinates steps and compensations on failure.- Failure modes: need reliable compensating actions; compensation complexity for side-effects (e.g., payments). Risk of temporary inconsistencies until saga completes.- Latency: lower per-step latency; end-to-end eventual consistency; depends on orchestration speed.- Developer experience: requires modeling compensations and idempotent operations; moderate complexity but doable for business flows.

Option C — Outbox + CDC (recommended for this context):- Approach: Write order row and an outbox event record in the same OLTP transaction. Use CDC (Debezium/Kafka Connect) or a background poller to stream outbox rows to Kafka and into the analytics warehouse (via streaming pipelines).- Failure modes: simpler recovery model — if delivery fails, pipeline retries; need to ensure at-least-once delivery and idempotent consumers to avoid duplicates; careful schema evolution and retention.- Latency: near-real-time (depends on CDC throughput and batch loads) with much lower OLTP latency than 2PC.- Developer experience/ops: good balance — transactional atomicity on the OLTP side with standard streaming tooling; easier to monitor, replay, and audit; fewer cross-system locks; works well with warehouses (CDC → Kafka → transform → warehouse).

Comparison summary:- Consistency: 2PC gives strong cross-system atomicity (if supported). Outbox+CDC and SAGA give eventual consistency but are observable and recoverable.- Reliability & failure handling: Outbox+CDC is most operationally robust for heterogeneous stacks; SAGA works where compensations are straightforward. 2PC is fragile at scale.- Latency: 2PC highest, SAGA/outbox lower; outbox+CDC offers best throughput with acceptable latency.- Developer experience: Outbox+CDC lowest friction (transactional semantics remain local); SAGA requires business modeling; 2PC increases complexity and often impractical.

Recommendation:For a data engineering context (OLTP orders DB + analytics warehouse), implement outbox + CDC streaming into your warehouse. Benefits: atomic writes on OLTP, scalable, observable pipelines, replayability for backfills, and easy integration with Kafka/Spark. Complement with idempotent consumers, monitoring for lag/errors, schema evolution policies, and retention/garbage-collection for outbox records. Use SAGA only for complex multi-service business transactions needing compensations; avoid 2PC except in tightly controlled homogeneous environments that fully support XA.

MediumTechnical

36 practiced

You must design a hot/cold storage tiering strategy for log data: hot tier for last 30 days with fast query response, cold tier for 2 years of archived logs with infrequent access. Explain data placement, file formats, compaction, lifecycle policies, and how to implement transparent query routing between tiers.

Sample Answer

Requirements & constraints- Hot: last 30 days, low-latency ad-hoc queries (analytics, dashboards)- Cold: up to 2 years, infrequent access, cost-optimized- Transparent to analysts, consistent schema, eventual compaction/archival

High-level placement- Object store (S3) bucket with two prefixes: - s3://my-bucket/logs/hot/YYYY/MM/DD/... (frequent small writes, short retention) - s3://my-bucket/logs/cold/YYYY/MM/... (larger, compacted files, long-term)- Maintain a single data catalog (Glue/Hive metastore) that knows partitions in both prefixes.

File formats & partitioning- Use columnar Parquet for both tiers for compression and predicate pushdown.- Hot writes: small micro-batches write Parquet with partitioning by date (YYYY/MM/DD) and additional high-selectivity columns (service, region).- Cold storage: larger Parquet files (256MB–1GB) partitioned by month (YYYY/MM) to reduce small-file overhead.

Compaction & ingestion pipeline- Ingest streaming or batch writes to hot prefix (e.g., Kafka → Spark Structured Streaming / Flink → write Parquet).- Run periodic compaction job (Spark) that: - Reads daily hot partitions older than configurable threshold (e.g., 7 days) - Deduplicates and repartitions by month - Writes optimized Parquet to cold prefix with proper statistics and Bloom filters (if supported) - Optionally convert to ORC or Parquet with ZSTD/snappy compression for better cold-read performance- Maintain transactional consistency via atomic moves (write to temp prefix then S3 rename) or use Delta Lake / Iceberg / Hudi for ACID and time-travel.

Lifecycle policies- S3 Lifecycle rules: - Hot prefix: transition objects to STANDARD_IA after 30 days or delete after 31 days - Cold prefix: transition to Glacier Flexible Retrieval after 180 days (if needed), expire after 2 years- If using table formats (Iceberg/Hudi), run metadata compaction and remove orphan files per retention policy before lifecycle transitions.- Enable object lock/versioning if retention regulation requires.

Transparent query routingOption A — Single unified table with partition-aware location mapping:- Create external table per tier in catalog (logs_hot, logs_cold) and a union view logs_all that queries hot partitions first then cold.- Use partition projection / partition pruning so queries touching recent dates hit hot data only.- Example SQL view: CREATE VIEW logs AS SELECT * FROM logs_hot UNION ALL SELECT * FROM logs_cold WHERE date < current_date - interval '30' day;- Query engines (Presto/Athena/Spark) will prune partitions and read only relevant S3 prefixes.

Option B — Table format with automatic data placement (recommended):- Use Iceberg/Delta Lake/Iceberg with partition evolution: maintain a single table pointing to both storage locations via table property locations or write-compaction that moves data between tiers while keeping single table metadata.- Query engine reads metadata and only touches files relevant to the predicate; compaction jobs update manifests so cold files are excluded for hot queries.

Operational considerations- Catalog refresh: ensure Glue/Metastore updated after compaction/moves (automate via pipeline).- Monitoring: track small-file counts, query latency per partition, S3 access patterns.- Security & compliance: encryption, IAM, and lifecycle audit logs.- Cost vs latency trade-offs: Glacier introduces retrieval latency — avoid for last 2 years unless acceptable.

Why this works- Parquet + partitioning + compaction minimizes I/O and query latency for hot tier and reduces storage cost in cold tier.- Using a unified catalog or table format makes routing transparent to users and enables safe background migration between tiers.

EasyTechnical

49 practiced

Given table events(event_id bigint PK, user_id bigint, event_type varchar, event_ts timestamp, properties jsonb), write a SQL query (PostgreSQL) that computes for every day in the past 30 days the number of active users (distinct user_id) and a 7-day rolling average of daily active users. Include sample input rows and expected output format in your answer.

Sample Answer

Approach: generate the past 30 calendar days, left-join aggregated daily distinct users from events, then compute a 7-day rolling average using a window function.

sql

WITH days AS (
  SELECT generate_series(current_date - interval '29 days', current_date, interval '1 day')::date AS day
),
daily_active AS (
  SELECT
    event_ts::date AS day,
    COUNT(DISTINCT user_id) AS daily_active
  FROM events
  WHERE event_ts >= current_date - interval '29 days'::interval
    AND event_ts < current_date + interval '1 day'
  GROUP BY event_ts::date
)
SELECT
  d.day,
  COALESCE(da.daily_active, 0) AS daily_active,
  ROUND(
    AVG(COALESCE(da.daily_active, 0)) OVER (
      ORDER BY d.day
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )::numeric,
    2
  ) AS rolling_7d_avg
FROM days d
LEFT JOIN daily_active da USING (day)
ORDER BY d.day;

Sample input (events):- (1, 10, 'click', '2025-11-28 10:00', '{}')- (2, 11, 'view', '2025-11-28 11:00', '{}')- (3, 10, 'view', '2025-11-29 09:00', '{}')- (4, 12, 'click', '2025-11-30 12:00', '{}')- (5, 13, 'view', '2025-12-01 08:00', '{}')

Expected output format (one row per day for past 30 days):day | daily_active | rolling_7d_avg2025-11-28 | 2 | 0.292025-11-29 | 1 | 0.432025-11-30 | 1 | 0.572025-12-01 | 1 | 0.71... (continues up to current_date)

Notes:- Uses COUNT(DISTINCT user_id) for DAU.- Rolling window uses previous 6 days + current day (7-day).- COALESCE fills missing days with 0.- Adjust rounding or window frame (e.g., RANGE BETWEEN) if you prefer calendar-anchored windows.

MediumTechnical

36 practiced

Explain strategies to handle heavy cardinality joins in a data warehouse: consider pre-aggregation, sharded/bucketed joins, bloom-filter pushdown, and approximate algorithms. Provide guidance on when to use each and how these approaches affect accuracy and query latency.

Sample Answer

Start by clarifying the problem: heavy-cardinality joins (very large tables, many distinct keys) can blow up shuffle, memory use, and latency. Here are practical strategies, when to use them, and trade-offs.

1) Pre-aggregation (materialized aggregates / denormalization)- What: compute and store rolled-up facts (e.g., daily user counts per key) so queries hit smaller tables.- When: common analytical patterns with predictable group-by dimensions, slow-changing lookups.- Effect: drastically reduces join size and latency; accuracy is exact for aggregated metrics. Cost: storage, ETL complexity, staleness if not updated frequently.

2) Sharded / bucketed joins (co-partitioning)- What: physically partition both tables by the join key (hash partitioning/bucketing) so matching keys land on same worker.- When: large ongoing joins in Spark/Presto where repeated joins occur; pipelines you can control.- Effect: reduces shuffle and memory pressure, improves latency deterministically. Accuracy unaffected. Cost: needs coordinated partitioning, can suffer skew—combine with skew handling (salting).

3) Bloom-filter pushdown (filter before shuffle)- What: build a compact bloom filter from the smaller side and distribute it to prune rows from the larger side before joining.- When: asymmetric joins where one side is significantly smaller and you can tolerate false positives.- Effect: reduces data scanned and shuffled, improving latency. Introduces false positives (no false negatives) so results remain complete but may include extra rows—accuracy of downstream aggregates can be slightly inflated unless corrected. Choose filter size to trade memory vs false-positive rate.

4) Approximate algorithms (HyperLogLog, sampling, sketches)- What: use probabilistic structures (HLL for distinct counts, Count-Min Sketch for frequencies, sample-based joins) instead of full joins.- When: dashboarding, exploratory analytics, SLAs require low-latency and approximate answers are acceptable.- Effect: massive latency and resource savings; controlled error bounds (e.g., ±% for HLL). Accuracy is approximate—document error bounds and use exact computation for billing/critical reports.

Guidance summary:- If exactness is required and patterns are stable: pre-aggregate or denormalize.- If you can control storage/layout and want deterministic speedups: shard/bucket and co-partition.- If one side is small: use bloom filters to prune before shuffle (tune false-positive rate).- If latency trumps exactness: use sketches/approximate joins with clear error budgets.

Combine methods: e.g., pre-aggregate then bucket, or bloom-filter plus sampling. Monitor skew, validate accuracy with periodic full joins, and expose error margins to consumers.

EasyTechnical

46 practiced

Explain the CAP theorem in distributed databases in the context of an online banking ledger versus an analytics warehouse. For each system, state which guarantees (consistency, availability, partition tolerance) are most important and why.

Unlock Full Question Bank

Get access to hundreds of Database and Data Platform Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.