Storage Formats and Partitioning for Analytics Questions

Addresses choices and trade offs for storage formats and physical partitioning in analytical and large scale storage systems. Topics include columnar file formats such as parquet and orc and table formats such as delta lake; compression and encoding techniques; partitioning schemes that enable data pruning and efficient predicate push down; file sizing and layout for scan performance; cost and performance trade offs in cloud object storage; and coordination between logical partitioning and physical storage format to optimize query throughput and storage cost.

MediumTechnical

36 practiced

Provide a safe rollout strategy for changing the default compression codec from snappy to zstd in a large Parquet data lake. Include steps for backward compatibility, consumer support, validation tests, staged rollout, and rollback plan.

Sample Answer

Summary: Roll the default Parquet compression from snappy → zstd incrementally to preserve read compatibility, give consumers time to update, validate performance/bit-for-bit correctness, and provide an immediate rollback path.

1) Prep & compatibility- Ensure readers (Spark, Presto, Hive, custom readers) support zstd (versions, jars). Document minimal client versions.- Keep writer configurable: default=snappy, opt-in zstd via flag/env var.- Update schema/metadata conventions: add writer-version and compression codec to file metadata.

2) Consumer support & communication- Announce migration timeline, breaking changes, and upgrade matrix to teams and downstream consumers.- Provide test artifacts: sample zstd Parquet files, code snippets to read them in Spark/Python/Presto.- Run a “canary consumer” program and invite teams to validate.

3) Validation tests- Functional: read/write parity (round-trip), schema preservation, row counts.- Compatibility: read zstd files with all supported reader versions.- Performance: measure CPU, IO, serialization time, file sizes for representative datasets.- Data integrity: checksums (MD5/parquet-level), spot-check queries, and end-to-end pipeline runs.

4) Staged rollout- Stage 0: Opt-in zstd for small non-critical pipelines; collect metrics and consumer feedback.- Stage 1: Enable zstd for internal batch pipelines with limited datasets; monitor errors, latency, and downstream correctness for 2 weeks.- Stage 2: Enable zstd for a percentage of newly created partitions (e.g., 10% → 50% → 100%) using feature flag and rollout window.- Stage 3: Once stable, flip default to zstd; keep opt-out flag for 30–90 days.

5) Monitoring & validation during rollout- Track read errors, failing jobs, consumer-side exceptions, compression ratios, CPU usage, and query latencies.- Create alerts for increased read errors or degraded performance thresholds.

6) Backward compatibility & migration- Readers must support both codecs; maintain ability to read snappy-written files indefinitely.- Provide a migration job to rewrite old snappy partitions to zstd if desired; prioritize hot tables.

7) Rollback plan- If critical failures occur, revert default flag to snappy immediately.- For already-written zstd files: keep them readable by ensuring reader compatibility; if readers don’t support zstd, run bulk re-encoding jobs back to snappy from backups/replicated data.- Communicate rollback actions and timelines to consumers.

8) Post-rollout- Formalize support policy, update docs, deprecate snappy default only after consumer upgrades confirmed, and archive metrics and lessons learned.

This minimizes blast radius, preserves read compatibility, and gives teams time to adapt while measuring actual benefits.

MediumTechnical

71 practiced

Analysts perform heavy ad-hoc queries that read large partitions but select only a few columns. What storage format and physical layout choices (rowgroup sizing, compression, column order, partition depth) would you recommend to reduce read amplification and cost? Explain why each recommendation helps for ad-hoc workloads.

Sample Answer

Recommendations (summary): use a columnar format (Parquet or ORC), large balanced rowgroups (tuned to ~64–512MB), modern compression (ZSTD or Snappy with ZSTD for long‑term cold), sort/cluster data and order columns sensibly, shallow partitioning by low‑to‑medium cardinality keys, and enable data skipping structures (min/max stats, bloom filters where supported). Each choice reduces read amplification and cost as explained below.

Details and rationale:- Format: Parquet or ORC — both store column chunks and support column pruning, predicate pushdown, column encodings and stats. Columnar I/O means scanning only the few columns analysts need instead of full rows.- Rowgroup/Row‑group size: target rowgroup sizes so each column chunk is reasonably large (typical 64–256MB; up to 512MB for cloud object stores). Too small → many small IOs and metadata overhead; too large → higher memory during reads. Proper sizing optimizes sequential reads and lowers per‑file overhead billed by cloud providers.- Compression & encoding: use ZSTD (better ratio) or Snappy (lower CPU) — both work well with columnar encodings. Enable dictionary/RLE for low‑cardinality columns to shrink disk read. Smaller compressed pages = fewer bytes to fetch and decrypt.- Column order/layout: place frequently queried columns and wide columns earlier in the file so readers can finish sooner and avoid fetching cold column chunks in some engines; ensure page sizes are tuned so column pages permit early stop. Also enable page/column statistics (min/max) so engines can skip rowgroups.- Partition depth and file size: partition by low‑to‑medium cardinality, avoid deep/high-cardinality partitioning which creates many small files. Prefer fewer partitions with larger files (100MB–1GB) to reduce per‑file read cost and metadata operations. For ad‑hoc that read large partitions, shallow partitioning keeps scans efficient.- Sorting / clustering within files: sort or cluster by commonly filtered columns (date, user_id) to increase data locality so min/max stats and zone maps skip large ranges—this drastically reduces bytes read for selective predicates.- Extra: enable bloom filters (ORC/Parquet with extension) for high‑cardinality filter columns, and collect/maintain accurate statistics for the query engine to do effective pruning.

Tradeoffs: ZSTD reduces IO cost but increases CPU; larger rowgroups reduce metadata overhead but increase memory needs for readers. Tune based on workload, cluster memory and cloud egress pricing.

MediumTechnical

54 practiced

Explain how Parquet and ORC collect and store column statistics such as min, max, null count, and distinct-count approximations. How do query engines use those statistics for pruning rowgroups or stripes, and under what circumstances might statistics be missing or misleading?

Sample Answer

Parquet and ORC collect per-column statistics at write time and store them in file/stripe/row-group metadata so engines can skip large chunks without scanning rows.

How they collect & store:- Parquet: Writers record column-level min, max, and null_count for each column chunk (row group) and optionally page-level indexes and Bloom filters (newer formats/engines). Distinct-count is not stored natively; some systems add approximate NDV summaries externally (HyperLogLog) or use Bloom filters for membership tests.- ORC: Stores rich column statistics per stripe and file footer: number of values, nulls, min/max, sum/count for numeric types, true/false counts for booleans, and optional Bloom filters and statistics for string lengths. ORC can also include column-level bitsets and some writers emit approximate distinct information.

How query engines use them:- Predicate evaluation against min/max/null_count: if predicate (e.g., col > 100) is incompatible with a stripe’s min/max, the engine prunes that stripe/row-group entirely.- Null-count helps skip groups for IS NOT NULL/IS NULL predicates.- Bloom filters and page indexes help avoid reading pages or columns when membership tests fail.- Approximate NDV can be used for planning (join strategies, cardinality estimates) and may influence execution plans.

When stats are missing or misleading:- Writers may disable statistics (for performance/privacy) or not support them for complex/nested types — then pruning can’t occur.- Encodings/serialization quirks: byte-wise min/max for strings or dictionaries can produce wrong ordering (collation/UTF-8 issues), and signed/unsigned misinterpretation can break numeric bounds.- Overflows/truncation: writers may cap stats or drop NDV; floating-point NaNs and Na/inf behave oddly.- Sampling/approximation: Bloom filters yield false positives; HLL/estimates have error bounds — can’t safely prove absence.- Predicate complexity: functions, UDFs, non-deterministic expressions, time-zone-normalized timestamps, or predicates on derived columns prevent reliable pruning.- Post-write mutations: if data is mutated outside the writer (rare), stats become stale.

Practical notes for data engineers:- Ensure writers enable and correctly configure statistics and Bloom filters for your query engine (e.g., Spark’s parquet/ORC writer settings).- Be cautious relying on NDV approximations for strict correctness; use them for planning only.- Test pruning behavior with representative data (especially strings, timestamps) because encoding and collation can make stats misleading.

MediumTechnical

47 practiced

Explain how format-level features such as Parquet dictionary encoding and ORC stripe indexes interact with sort order and file layout to enable faster scans. Describe how sorting data before write can improve both compression and predicate pruning and what ingestion throughput trade-offs that causes.

MediumTechnical

37 practiced

A Spark job scanning a Parquet table is slow but the same cluster reads other tables quickly. List practical performance checks and optimizations you would perform: consider column projection, predicate pushdown, rowgroup settings, compression codec, dictionary encoding settings, file sizes, and executor memory. Describe how each change impacts scan performance.

Sample Answer

Start by verifying workload vs table differences (schema, file layout, stats) and then run targeted checks/changes. For each item below I describe the check, actionable optimizations, and why it affects scan performance.

- Column projection (column pruning) - Check: Are queries selecting * or unnecessary columns? - Optimize: Select only needed columns; ensure Spark pushdown of projection is enabled. - Impact: Reduces I/O and CPU spent deserializing columns; for Parquet this can drop read bytes substantially when wide tables exist.

- Predicate pushdown - Check: Confirm predicates appear in the physical plan (explain() shows ParquetFilters) and spark.sql.parquet.filterPushdown=true. - Optimize: Push filters to storage by rewriting predicates to supported forms; create partition columns for high-selectivity predicates. - Impact: Narrows data read at scan time (fewer row groups/files), saving I/O and CPU.

- Row-group / row-group size - Check: Inspect Parquet metadata (parquet-tools or metadata) for row-group counts and sizes. - Optimize: Recreate files with row-group sizes tuned (e.g., 512MB file / ~128MB row-group) using writer options. - Impact: Too small row-groups => higher metadata overhead; too large => less effective predicate/column pruning and memory spikes. Proper sizing balances seeks and pruning.

- Compression codec - Check: See codec in metadata (SNAPPY, GZIP, ZSTD, NONE). - Optimize: Use a fast codec with good ratio (Snappy or Zstd w/ low CPU) for read-heavy; use Gzip/Zstd stronger compression for storage if CPU acceptable. - Impact: Affects bytes on disk and CPU decompression cost; choose codec that minimizes end-to-end read latency for your CPU/IO profile.

- Dictionary encoding / encoding settings - Check: Look at column encodings in metadata; high-cardinality columns may not benefit. - Optimize: For low-cardinality strings, ensure dictionary encoding and encoding thresholds are enabled at write time. - Impact: Improves compression and reduces disk I/O for repetitive values; avoids wasted space for high-cardinality columns.

- File sizes and number of files - Check: Count files and their sizes in the table directory. - Optimize: Compact many small files into larger ones (coalesce/repartition when writing) targeting HDFS/S3 optimal sizes (e.g., 128–512MB). - Impact: Reduces per-file open/read overhead and metadata operations; fewer tasks and less scheduler overhead in Spark.

- Executor memory and off-heap settings - Check: Monitor GC, spilling, shuffle read/write metrics, and executor logs. - Optimize: Increase executor memory or tune spark.memory.fraction and shuffle/spill settings; enable vectorized Parquet reader (spark.sql.parquet.enableVectorizedReader=true). - Impact: Prevents excessive spilling and GC pauses during decompression/deserialization; vectorized reads reduce CPU and improve throughput.

Additional practical steps:- Enable Spark UI and use query metrics (Input Metrics, Shuffle, Task durations) to locate bottleneck.- Refresh table statistics (ANALYZE TABLE) so the planner can make better decisions.- For cloud object stores, consider IO patterns (list/GET costs) and use manifest or Hive partitioning to avoid excessive metadata ops.

Prioritize: fix column projection/predicate pushdown and compact tiny files first (largest wins), then tune codec/row-groups and executor memory based on observed CPU vs I/O bottleneck.

Unlock Full Question Bank

Get access to hundreds of Storage Formats and Partitioning for Analytics interview questions and detailed answers.

Join thousands of developers preparing for their dream job.