Cloud Data Warehouse Design and Optimization Questions

Covers design and optimization of analytical systems and data warehouses on cloud platforms. Topics include schema design patterns for analytics such as star schema and snowflake schema, purposeful denormalization for query performance, column oriented storage characteristics, distribution and sort key selection, partitioning and clustering strategies, incremental loading patterns, handling slowly changing dimensions, time series data modeling, cost and performance trade offs in cloud managed warehouses, and platform specific features that affect query performance and storage layout. Candidates should be able to discuss end to end design considerations for large scale analytic workloads and trade offs between latency, cost, and maintainability.

HardSystem Design

68 practiced

Design a strategy to implement SCD handling for a high-cardinality dimension with millions of customer records and frequent updates. Show how to minimize storage, avoid expensive full table scans, support point-in-time joins to facts, and discuss indexing or partitioning approaches to make queries efficient.

Sample Answer

Requirements & constraints:- Millions of customers, frequent updates, must support point-in-time joins from fact tables, minimize storage and avoid full-table scans, low-latency lookups for current row.

High-level approach:- Use an append-only change-log stored in a table format that supports time-travel and record-level upserts (Delta Lake / Apache Hudi / Iceberg). Keep two views: 1) current_dimension (materialized) for point lookups 2) history_dimension (append-only immutable changes with effective_from, effective_to, is_current)

Ingestion & CDC:- Ingest updates via CDC (Kafka + Debezium or cloud CDC). Apply incremental upserts using micro-batches: - Write raw events to a staging delta - Use targeted merge-on-key (customer_id) to update current_dimension and append a history row only when values change.

Surrogate keys & PIT joins:- Use a surrogate_key generated as (auto-increment or monotonic id) per new version.- For point-in-time joins, facts include event_time; join facts -> history_dimension on customer_id and effective_from <= event_time < effective_to. Alternatively use time-travel: join fact to history table "AS OF" event_time when engine supports it.

Storage optimization:- Store history as columnar Parquet/ORC with compression; deduplicate by only appending when values change.- Keep only N recent versions per customer in hot store; cold versions can go to cheaper long-term storage.- Use delta compaction to reduce small files.

Avoiding full-table scans / indexing:- Partition by hash(customer_id) or by range on customer_id shard to ensure merges and lookups target limited partitions.- Cluster/ sort within partitions by customer_id, effective_from (Z-order / sort) to narrow file reads.- Maintain a small “current index” table keyed by customer_id mapping to (partition, file, offset or surrogate_key) for sub-second current lookups—kept updated with upserts.- Use bloom filters on files for customer_id to skip files quickly; rely on min/max statistics.

Query patterns & performance:- Point lookups: query current_dimension by customer_id using index → single partition/file read.- Point-in-time joins: join facts to history_dimension using partition pruning (hash/customer_id) + effective_from range → scans limited to small set of files; engines with predicate pushdown and bloom filters avoid full scans.- Batch rebuilds: if schema changes, backfill by processing only deltas.

Trade-offs:- Merge-on-write gives fast reads for current state but more CPU on writes; merge-on-read (append-only + periodic compaction) reduces write cost but increases read latency for point-in-time queries.- Maintaining a current index increases storage but prevents full scans.

Monitoring & maintenance:- Track version counts per customer; schedule compaction, vacuuming, and cold-move jobs.- Monitor long-tail hot keys and rebalance partitions if skewed.

This design balances storage efficiency, fast point lookups, and scalable point-in-time joins by combining CDC, targeted merges, partitioning/clustering, and file-level pruning/indexing.

HardTechnical

58 practiced

Implement an incremental merge pattern using Spark Structured Streaming and Delta Lake (pseudocode acceptable). Given schema: events(id STRING, user_id STRING, event_time TIMESTAMP, value DOUBLE). Describe how you deduplicate, handle late-arriving events, and maintain exactly-once semantics for upserts into a Delta table.

Sample Answer

Approach: use Structured Streaming with event-time watermarking + per-batch deduplication and perform upserts via Delta Lake MERGE inside foreachBatch. Watermark bounds late data; MERGE in a single transaction ensures atomic upserts -> exactly-once when combined with Spark checkpointing.

Pseudocode (PySpark-style):

python

from pyspark.sql.functions import expr, row_number
from pyspark.sql.window import Window

events = spark.readStream.format("kafka")...  # or other source
# parsed to schema: id, user_id, event_time, value

# apply watermark on event_time to bound state for dedup
watermarked = events.withWatermark("event_time", "10 minutes")

def upsert_batch(batch_df, batch_id):
    # keep the latest event per id within the batch + late window
    w = Window.partitionBy("id").orderBy(expr("event_time desc"), expr("value desc"))
    deduped = (batch_df
               .withColumn("rn", row_number().over(w))
               .filter("rn = 1")
               .drop("rn"))
    # MERGE into Delta table on id, update if incoming event_time >= existing.event_time
    delta_table = "delta.`/path/to/table`"
    (spark.table(delta_table)  # used only for clarity; MERGE uses SQL below
     )
    deduped.createOrReplaceTempView("updates")
    spark.sql(f"""
      MERGE INTO delta.`/path/to/table` AS target
      USING updates AS src
      ON target.id = src.id
      WHEN MATCHED AND src.event_time >= target.event_time
        THEN UPDATE SET *
      WHEN NOT MATCHED
        THEN INSERT *
    """)

# write stream with foreachBatch; checkpoint for exactly-once
query = (watermarked.writeStream
         .foreachBatch(upsert_batch)
         .option("checkpointLocation", "/checkpoints/upsert")
         .start())

Key points / reasoning:- Watermark ("10 minutes") bounds state and drops events later than allowed lateness; adjust per SLA. Late events beyond watermark can be routed to a separate "late" table if needed.- Per-batch row_number dedup ensures within-batch duplicates resolved to latest by event_time.- MERGE with conditional update (src.event_time >= target.event_time) avoids overwriting newer records with older arrivals.- Exactly-once: Spark streaming + Delta transactions + checkpointing ensure idempotent, atomic upserts. foreachBatch executes deterministically per batch id; if retried, MERGE is safe.- Considerations: tune watermark, batch size, partitioning of Delta table (by date), optimize/compaction, handle concurrent writers, and retention/VACUUM settings.

EasyTechnical

67 practiced

Describe the primary differences between OLTP and OLAP systems. In the context of a cloud data warehouse, explain why design choices such as indexing, normalization, and transaction optimization differ from those in online transactional databases.

Sample Answer

OLTP vs OLAP — high-level differences:- Purpose: OLTP systems support day-to-day transactional workloads (insert/update/delete, many small concurrent transactions). OLAP systems support analytical workloads (complex, read-heavy queries across large data volumes).- Data shape and size: OLTP has many narrow, highly normalized tables with small rows. OLAP stores wide, denormalized facts and dimensions, often orders of magnitude larger.- Query patterns: OLTP: short, indexed lookups and point updates. OLAP: long-running aggregations, joins, scans, and time-series analyses.- Consistency/latency: OLTP prioritizes strong transactional consistency and low latency. OLAP trades immediate consistency for query throughput and columnar read efficiency.

Why cloud data warehouse design choices differ:- Indexing: Traditional B-tree indexes help OLTP point lookups. Cloud warehouses (columnar storage, MPP) often rely on columnar compression, zone maps/metadata, and sorted/clustered columns rather than many secondary indexes — indexes add storage/maintenance overhead and don’t help wide analytical scans.- Normalization: OLTP uses normalization to avoid update anomalies and minimize transaction cost. OLAP favors denormalization (star/snowflake schemas, flattened tables) to reduce expensive joins during aggregations and to improve scan locality and compression.- Transaction optimization: OLTP needs ACID, row-level locking, and fast commit paths. Cloud warehouses optimize for bulk loads and snapshot isolation (MVCC), favoring batch ingestion and eventual visibility for performance and concurrency. They minimize per-row transactional overhead and rely on append-only/immutable storage and efficient compaction.

Practical implications for a data engineer:- Design ETL to produce denormalized, query-friendly fact tables; batch/stream loads tuned to warehouse ingestion patterns.- Use clustering/partitioning and predicate pushdown to improve scan efficiency instead of many indexes.- Accept different SLAs: OLAP prioritizes throughput and predictable analytic latency; OLTP prioritizes sub-ms transaction latency and strict consistency.

HardTechnical

57 practiced

Explain how compression, zone maps, and micro-partitions work in cloud warehouses like Snowflake or columnar engines, and how they influence predicate pushdown and I/O pruning. Provide examples of how good clustering or sorting can reduce IO by orders of magnitude.

Sample Answer

Compression, zone maps, and micro-partitions are complementary techniques columnar cloud warehouses (Snowflake, Redshift Spectrum, BigQuery, etc.) use to minimize IO and CPU.

How they work- Columnar compression: Columns store homogeneous data so encoders (RLE, dictionary, delta, bitpack) achieve high compression and allow reading only the needed column bytes. Compression also preserves patterns that accelerate CPU decompression and vectorized processing.- Micro-partitions: Data is stored in small immutable files/segments (e.g., Snowflake ~16–256MB logical micro-partitions). Each micro-partition contains columnar data plus metadata.- Zone maps / min-max statistics: For every micro-partition and column, warehouses store statistics (min, max, null count, distinct count, sketches). These act as lightweight indexes.

Predicate pushdown and I/O pruning- Predicate pushdown: Query engine rewrites filters so they execute as early as possible (on storage or during scan). Combined with zone maps, the engine checks a micro-partition’s stats: if predicate cannot be satisfied (e.g., WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31' but partition max < '2024-01-01'), the micro-partition is pruned without reading its bytes.- Because stats live in metadata, pruning costs tiny CPU/memory and saves expensive network/disk I/O and decompression.

Example: clustering/sorting reduces IO- Unsorted table: 1 TB of events uniformly distributed by date in 1000 micro-partitions → a date-range query covering 1% of dates would still touch ~10 micro-partitions by chance, but often touches hundreds because rows for those dates are scattered.- Well-clustered/sorted by event_date: rows for the same date are colocated; the same 1% date-range will only require scanning ~10 micro-partitions out of 1000 (1% IO). If each micro-partition is 128 MB, unsorted may require reading 800 MB–1 TB; sorted requires ~1.28 GB — orders of magnitude less.- Real-world: Sorting or manual clustering can reduce scanned bytes from hundreds of GB to a few GB; many customers report 10x–1000x reduction depending on cardinality and data skew.

Practical tips- Choose clustering keys with low write churn and high selectivity (dates, customer_id with hotspot awareness).- Use compaction/maintenance to avoid small micro-partitions or highly mixed partitions.- Monitor scanned bytes and query profiles; use table clustering/automatic clustering where available.- Combine compression, zone maps, and predicate pushdown: compressed column reads + pruning yield best IO reduction.

Trade-offs- Aggressive clustering increases maintenance cost (re-clustering, compaction) and may affect ingest latency.- Statistics are approximate; extremely selective predicates may require additional indexes or materialized views.

In short: compression reduces bytes, micro-partitions + zone maps let engines skip entire files cheaply via predicate pushdown, and good clustering/sorting aligns data distribution with query patterns to turn IO-heavy scans into tiny targeted reads — often cutting I/O by orders of magnitude.

HardTechnical

58 practiced

A long-running query in Redshift shows high IO and CPU time. Describe a step-by-step diagnostic approach using Redshift system tables (STL_QUERY, SVL_QUERY_REPORT, STL_SCAN, SVV_DISKUSAGE, SVV_TABLE_INFO). What metrics would you look at, and what concrete fixes might you propose based on common root causes?

Sample Answer

Start by clarifying scope (which query, timeframe, and affected user/queue). Then run a targeted diagnostic loop: identify the query, inspect plan-level IO/CPU, find hot tables/columns, and check disk/ table stats.

1) Find the query and summary metrics- Query STL_QUERY for runtime, start/end, userid, and aborted flag.- Use SVL_QUERY_REPORT to get per-step CPU/ms and rowcounts.Example:

sql

-- find recent long-running queries
select userid, query, starttime, endtime, elapsed/1000000.0 as seconds
from STL_QUERY
where endtime >= sysdate - 1
and elapsed > 600000000  -- >10 minutes
order by elapsed desc limit 10;

2) Inspect per-step CPU/IO and bottleneck steps- From SVL_QUERY_REPORT check steps with high "cpu_time" and "disk" columns, and "rows" vs "rows_out".- Look for steps with high "peak_memory_mb" or repeated spills to disk.

3) Identify scanned blocks and table hotspots- STL_SCAN shows tbl, per-segment blocks scanned and rows read. High blocks_read relative to table size indicates full scans.

sql

select s.tbl, t."table", sum(s.blocks) as blocks_scanned, max(s.rows) rows_read
from stl_scan s
join svv_table_info t on s.tbl = t.table_id
where s.query = 12345
group by s.tbl,t."table"
order by blocks_scanned desc;

4) Check table statistics and disk usage- SVV_TABLE_INFO shows size, unsorted and stats_off (% stale stats).- SVV_DISKUSAGE shows skew across slices and nodes (hot nodes).

sql

select * from svv_table_info where table = 'schema.table';
select * from svv_diskusage where userid = <user> order by pct_capacity_used desc limit 20;

Key metrics to evaluate- elapsed time, cpu_time, read_io (disk bytes), blocks_scanned, rows_in/rows_out, peak_memory_mb, stats_off, skew (pct_used per slice), unsorted ratio, table size, data distribution key cardinality.

Common root causes and fixes- Full table scans on large tables: add selective predicates, push filters early, create ZORDER/COMPOUND sort keys or interleave sort keys, and add appropriate distribution key to avoid reshuffle. Consider materialized views or pre-aggregations.- Stale or missing statistics: RUN ANALYZE on affected tables to refresh planner stats.- Skewed data/distribution: If one slice reads far more blocks, change distkey or use even distribution (diststyle key vs all vs even) or redistribute data; consider AUTO distribution for appropriate workloads.- Disk spills / insufficient memory: increase WLM query slot memory, move to a higher WLM queue with more memory, or rewrite query to reduce memory (break into temp tables, use CTAS, or sort/aggregate smaller partitions).- Large intermediate shuffles / network IO: Reorder joins to drive from smallest table, use distribution keys to collocate joins, use broadcast (DISTSTYLE ALL) for small dimension tables.- Wide or unsorted tables causing many blocks scanned: VACUUM to resort and reclaim deleted rows, and resize sort keys to align with common filter columns.- Hot nodes / disk capacity issues: check SVV_DISKUSAGE, re-balance by resizing cluster (resize or add nodes) or unload/load to redistribute.

Follow-up actions- Re-run the query with EXPLAIN (or SVL_QUERY_REPORT) after changes to confirm reduced blocks_scanned, cpu_time, and no disk spill.- Implement monitoring of stats_off, disk usage, longest-running queries in daily alerts and automate ANALYZE/VACUUM for high-change tables.

This process isolates whether the issue is planner (stats/keys), execution (memory/IO/spill), or infrastructure (skew/disk). For each diagnosed cause implement targeted remediation and validate with before/after metrics (elapsed, cpu_time, blocks_scanned, peak_memory).

Unlock Full Question Bank

Get access to hundreds of Cloud Data Warehouse Design and Optimization interview questions and detailed answers.

Join thousands of developers preparing for their dream job.