Analytical Data Systems and Warehousing Questions

Architectures and operational patterns for analytical workloads and reporting. Coverage includes data warehouses, data marts, column oriented analytic storage, data lake and lakehouse architectures, extract transform load and extract load transform pipelines, batch and streaming ingestion, schema on read versus schema on write, materialized views and aggregation strategies, columnar compression and storage formats, partitioning and clustering tuned for analytic queries, cost versus performance trade offs for managed cloud services, and integration with business intelligence and reporting tools. Candidates should be able to distinguish online analytical processing from online transaction processing and choose appropriate architectures and tools for large scale analytics, including managed offerings and cost optimization strategies.

HardTechnical

17 practiced

Design an exactly-once streaming ingestion pipeline ingesting events from Kafka, processing them with Flink or Spark Structured Streaming, and writing to Delta Lake. Explain how to handle duplicates, out-of-order events, transactional writes, checkpoints, and failure recovery to guarantee correctness.

Sample Answer

Requirements (clarify): ingest from Kafka, process event-time semantics, deduplicate and order as needed, guarantee exactly-once writes to Delta Lake under crashes, re-delivery, or restarts. Latency target and allowed lateness determine watermarking choices.

High-level architecture:Kafka → Stream Processor (Flink or Spark Structured Streaming) → Delta Lake (via transactional sink) Use the stream engine’s checkpointing + a two-phase commit or idempotent/transactional Delta writer so Kafka offsets are committed only after a successful persistent commit.

Key components & how they guarantee correctness

1) Source: Kafka- Use Kafka consumer with enable.auto.commit=false.- Let the stream engine manage offsets via checkpointing; offsets should be included in the sink transaction (see below).

2) Event-time processing, ordering, and out-of-order events- Assign event-time timestamps on ingestion.- Use watermarks with an allowed lateness based on SLA.- For ordering within keys, use keyed state and event-time buffers. For example, buffer and sort events per key until watermark passes; optionally use small deterministic sort windows.- For aggregations/windowing use event-time windows with watermark to emit final results and late-event handling policy.

3) Deduplication- Use a unique event id (producer-assigned). Maintain per-key dedup state (hash or bloom filter) with TTL longer than maximum allowed lateness + processing skew.- In Flink: use keyed state (ValueState or State TTL) to track seen event IDs; purge after TTL.- In Spark Structured Streaming: use dropDuplicates on the event id with watermark on event-time or implement idempotent merges in foreachBatch.

4) Exactly-once transactional writes to Delta Lake- Delta Lake provides atomic commits via its transaction log. Ensure writer is transactional: - Flink: implement a TwoPhaseCommitSinkFunction that stages files/partitions, writes data, then on checkpoint complete performs atomic commit to Delta's transaction log. Also record Kafka offsets in the same prepared transaction. On failure, abort prepared transaction; on recovery, resume. - Spark Structured Streaming: use the official Delta Lake sink (writeStream.format("delta") with checkpointLocation). Spark’s micro-batch model + Delta ensures atomic, idempotent commits per batch. For custom logic, use foreachBatch and perform MERGE/UPSERT keyed by event id within a transaction; record batch id and latest Kafka offsets in a control table/txn metadata so you only commit offsets after success.- Crucial: tie Kafka offset commit to the same logical commit—i.e., only mark offsets as consumed after the Delta transaction commits. In Flink this is part of 2PC; in Spark when using built-in Delta sink, Spark’s checkpointing ensures deterministic batches and prevents double-apply.

5) Checkpoints & failure recovery- Enable frequent durable checkpoints (Flink) or reliable checkpointing and write-ahead logs (Spark). Configure retention and external durable storage (S3/ADLS/GCS).- On restart: restore operator state (dedup state, window state) from checkpoint; for in-flight 2PC transactions, if prepared but not committed, the coordinator must abort or recover based on transaction metadata.- For Spark: the combination of checkpoint + Delta’s atomic commits ensures reprocessing of any micro-batch is safe because writes are idempotent/transactional. Use a deterministic query plan.

6) Edge cases & operational considerations- Late events beyond watermark: route to a side table or reprocess via backfill job.- State size: use TTL and external backing (RocksDB) for Flink large state.- Schema evolution: use Delta’s schema evolution options and validation.- Monitoring: alert on long-running checkpoints, frequent retries, and high uniqueness set growth.- Testing: fault-injection tests (kill job during commit), replay tests, and chaos testing.

Example patterns- Flink: keyed by event key → dedupe using keyed ValueState with TTL → use TwoPhaseCommitSinkFunction writing parquet files and committing transaction to Delta with offsets in metadata.- Spark: structured streaming micro-batches → foreachBatch(batchDF, batchId) { merge into delta using event_id as key; record batchId → offsets in _delta_transactions or control table } with checkpointLocation so retries are idempotent.

Summary: exactly-once requires (1) deterministic processing with event-time and deduplication, (2) transactional/atomic commits to Delta, and (3) tying Kafka offsets to those commits via checkpointed 2PC (Flink) or idempotent micro-batch commits (Spark + Delta). Proper watermarking, state TTLs, durable checkpoint storage, and operational tests complete the correctness guarantees.

EasyTechnical

16 practiced

Explain the differences between OLAP and OLTP systems. For each dimension—data models, query patterns, latency requirements, consistency, storage format, and scale—describe typical characteristics. Given a startup processing 1k TPS of orders and needing daily and ad-hoc analytics, recommend whether to use a combined system or two separate systems and justify your choice.

Sample Answer

Brief summary: OLTP (Online Transaction Processing) is optimized for transactional workloads (orders, payments). OLAP (Online Analytical Processing) is optimized for reporting and analytics. They have different design trade-offs and should usually be separate for reliability and performance.

Differences by dimension:- Data models: OLTP — normalized row-based schemas (3NF) to avoid anomalies; OLAP — denormalized/star/snowflake schemas or wide fact tables for fast aggregation.- Query patterns: OLTP — many short point reads/writes (INSERT/UPDATE/DELETE); OLAP — long, complex scans, GROUP BY, joins, window functions.- Latency requirements: OLTP — sub-10–100 ms transactional latency; OLAP — minutes to hours acceptable for batch, seconds for dashboards if near-real-time.- Consistency: OLTP — strong ACID guarantees required; OLAP — eventual consistency is acceptable; data accuracy important but can be delayed.- Storage format: OLTP — row-oriented storage, indexes, B-trees; OLAP — columnar storage, compression, columnar files (Parquet/ORC), vectorized execution.- Scale: OLTP — scale for TPS and low-latency OLTP (vertical or sharded); OLAP — scale for data volume (TBs-PBs) and concurrent complex queries (MPP, distributed query engines).

Recommendation for startup (1k TPS orders, daily + ad-hoc analytics):Use two systems: a dedicated OLTP database for order processing and a separate OLAP/data warehouse for analytics. Justification:- Separation isolates transactional latency and ACID needs from heavy analytical queries that would otherwise slow writes.- Simpler scaling: scale OLTP for TPS and OLAP for storage and analytical concurrency independently.- Operational safety: backups, retention, and schema evolution differ.Implementation pattern: keep OLTP (e.g., PostgreSQL or managed RDS) and stream changes via CDC (Debezium/Kafka) or batch ETL to a DW (Snowflake, BigQuery, Redshift, ClickHouse) with daily loads and an optional near-real-time materialized layer for dashboards. For very small teams, consider a managed HTAP or single system temporarily, but plan to separate as traffic/analytic needs grow.

HardTechnical

17 practiced

Design storage and query patterns to support high-cardinality dynamic cohort analysis for marketing (e.g., filter by dozens of attributes to define cohorts at query time). Discuss trade-offs between pre-computation (materialized cohort tables), analytic indexes, and runtime filtering for performance, cost, and freshness.

Sample Answer

Requirements & constraints (clarify up front):- Ad-hoc cohorts: arbitrary combinations of dozens of high-cardinality attributes (user_id, geo, device, purchase amount buckets, custom flags).- Scale: 100M+ users, tens of attributes, SLAs: interactive (sub-second to few seconds) for analysts.- Non-functional: cost sensitivity, freshness (near-real-time to hourly), maintainability.

High-level options and trade-offs1) Pre-computation (materialized cohort tables / materialized views)- What: Precompute and store common cohort combinations or per-attribute bitmaps / inverted lists; optionally maintain rollups.- Pros: Very fast queries for precomputed combos, predictable cost per query.- Cons: Combinatorial explosion if many combinations => storage blowup and heavy maintenance; freshness lag if refresh is periodic; expensive if many variants.- Best when: A small set of known high-usage cohort definitions or expensive aggregations.

2) Analytic indexes (bitmap indexes, inverted indexes, columnar DB indexes)- What: Keep per-attribute indexes (Roaring bitmaps, posting lists) or use systems with fast vectorized filters (ClickHouse, Druid, Pinot, Snowflake with search optimization service).- Pros: Efficient intersection of many filters (bitmap AND/OR), compact for sparse attributes, supports ad-hoc combos with good perf; can be updated incrementally.- Cons: Index maintenance cost on writes/streaming; some systems require memory/compute to serve interactive queries; complexity in pipeline.- Best when: Many high-cardinality attributes needed ad-hoc; need balance of freshness and interactivity.

3) Runtime filtering (scan/stream processing)- What: Evaluate filters at query time by scanning columnar store (Parquet on S3 + Presto/Trino/BigQuery).- Pros: Lowest storage precomputation cost; flexible and fresh (real-time if streaming ingestion).- Cons: Potentially high query latency and cost for large scans; unpredictable performance for complex filters.- Best when: Lower scale, infrequent queries, or when using massive distributed engines tuned with predicate pushdown and pruning.

Recommended hybrid architecture (practical for Data Engineer)- Base storage: Columnar, partitioned data lake (Parquet/ORC) + metadata (Hive/Glue) for durability and low-cost bulk analytics.- Fast path: Maintain per-attribute Roaring bitmap indexes or inverted indexes in an indexing engine (Druid/ClickHouse/Pinot) for attributes used in interactive segmentation. Keep these indexes incremental via streaming (Kafka → stream processors → indexer).- Precompute hot cohorts: Materialize top N (by usage) cohort aggregates or bitmaps as cached tables or materialized views updated frequently (near-real-time or hourly).- Query planner / API: On query arrival: - If cohort matches precomputed cache → return immediately. - Else build filter by intersecting attribute bitmaps via index engine (fast) → produce user list or aggregate. - Fallback to lake scan only for extremely rare filters or full ad-hoc deep analysis.- Freshness: Use streaming updates to indexer for sub-minute freshness where needed; schedule incremental re-indexing/compaction to keep bitmap efficiency.

Engineering considerations & optimizations- Choose bitmap representation (Roaring) to compress and speed intersections for both dense and sparse attributes.- Cardinality management: bucketize continuous attributes (amounts, recency) to reduce unique keys while preserving utility.- Cost control: tier data—hot indexes & materialized cohorts in expensive fast storage; cold detailed traits in low-cost lake.- Monitoring: track query patterns to decide which cohorts/attributes to index or precompute.- Access patterns: return counts first (fast), then lazy fetch member IDs for export to downstream systems.

Complexity & trade-offs summary- Precompute: fastest per query, high storage/maintenance, lower freshness.- Indexes: best balance—interactive ad-hoc queries, moderate storage and maintenance, good freshness.- Runtime scans: cheapest storage but slow/unpredictable for high-cardinality, expensive at scale.

Example technologies- Fast analytics/indexing: ClickHouse, Druid, Pinot, ClickHouse + materialized views.- Storage: S3 + Parquet + Glue / BigQuery / Snowflake (with search optimization).- Streaming: Kafka + Spark/Flink/Beam to update indexes/materialized cohorts.

This hybrid approach gives interactive ad-hoc cohortting with controlled cost and near-real-time freshness while avoiding combinatorial precomputation.

MediumTechnical

34 practiced

SQL/data modeling: Implement Slowly Changing Dimension Type 2 (SCD2) logic in SQL for a customer dimension. Given staging_customers(id, email, name, updated_at) and dim_customers(surrogate_id, natural_id, email, name, valid_from, valid_to, is_current). Provide an upsert strategy (in SQL or pseudocode) to apply changes incrementally.

Sample Answer

Approach: detect rows where natural key (id) is new, changed (some attribute differs), or unchanged. For changed rows, expire existing current record (set valid_to, is_current=false) and insert a new version with updated attributes and valid_from = staging.updated_at. Do this in a single transaction using MERGE or separate steps if MERGE unavailable.

sql

-- Using MERGE-style pseudocode (works on SQL Server / Oracle; adapt for Postgres)
BEGIN TRANSACTION;

-- 1) Expire current dim rows when attributes changed
MERGE INTO dim_customers d
USING (
  SELECT s.id, s.email, s.name, s.updated_at
  FROM staging_customers s
) s
ON (d.natural_id = s.id AND d.is_current = TRUE)
WHEN MATCHED AND (d.email <> s.email OR d.name <> s.name) THEN
  UPDATE SET
    d.valid_to = s.updated_at, 
    d.is_current = FALSE;

-- 2) Insert new versions for changed or new rows
INSERT INTO dim_customers (surrogate_id, natural_id, email, name, valid_from, valid_to, is_current)
SELECT NEXTVAL('dim_customers_seq') AS surrogate_id, s.id, s.email, s.name, s.updated_at, NULL, TRUE
FROM staging_customers s
LEFT JOIN dim_customers d ON d.natural_id = s.id AND d.is_current = TRUE
WHERE d.natural_id IS NULL                      -- new natural key
   OR (d.email <> s.email OR d.name <> s.name)  -- changed attributes

COMMIT;

Key points:- Use transaction to avoid race conditions.- Use updated_at as valid_from; set valid_to when expiring.- Use is_current boolean for fast current-state queries and valid_from/valid_to for history.- Ensure idempotency: run incremental batches with deduplication (e.g., use latest staging row per id).Edge cases:- Concurrent runs: use locks or CDC watermark.- Late-arriving/ backdated updated_at: compare timestamps and handle overlaps.Alternatives:- Use CDC stream (+ Kafka) to generate events and apply same logic in streaming processors (Spark, Flink).- SCD Type 1 for non-historized attributes.

EasyTechnical

24 practiced

Design a simple star schema for an e-commerce analytics workload to support sales reporting and user-behavior analytics. Include at least: one fact table for orders or events, and three dimension tables. Provide sample columns and briefly justify choice of surrogate keys versus natural keys.

Unlock Full Question Bank

Get access to hundreds of Analytical Data Systems and Warehousing interview questions and detailed answers.

Join thousands of developers preparing for their dream job.