Prompt and scope
The table is written daily as Parquet, with many data pages inside each row group. Queries commonly filter by customer_id and a time range, matching under 1% of rows while scanning nearly the whole table. Explain how the optional Page Index can reduce irrelevant page reads while keeping old readers correct, controlling metadata cost, and proving that gains come from page pruning rather than cache or resource changes.
The capacity, selectivity, and scan ratio are interview assumptions, not universal benchmarks. This question fits data engineering, lakehouse engines, query optimization, and storage infrastructure roles. Its core skill is columnar file layout and predicate pushdown, so it belongs to data.
What interviewers assess
First, can you distinguish ColumnIndex from OffsetIndex? The former uses per-page boundary statistics to decide which pages may match; the latter maps matching row ranges to offsets in projected columns.
Second, can you explain ordered versus unordered columns? Ordered columns can use boundary binary search; unordered columns often require checking page bounds sequentially. A Page Index is not a general secondary index.
Third, can you protect correctness? Truncated min/max values may enlarge the candidate set but must not exclude a page that could match. Nulls, NaNs, column order, and column_orders follow the format definition.
Fourth, can you quantify trade-offs? Index metadata adds footer-area I/O and write work, while selective scans can reduce data-page I/O. Measure with the real workload instead of promising a fixed speedup.
Fifth, can you provide a fallback? An old reader may ignore the Page Index and still read correctly with ordinary row-group or page statistics. Enabling the index must not change result semantics.
Questions to clarify first
- Does the engine and reader implement ColumnIndex and OffsetIndex, and do they read them by default?
- Is
customer_idrange-clustered or sorted at write time, or is it unordered? - Are predicates equality, range, prefix, or complex expressions?
- Do existing files contain page-level statistics, and what are the encoding and page sizes?
- What is the minimum old-reader version and cross-language compatibility matrix?
- Are we optimizing point lookups, range scans, or full-table aggregates?
30-second answer framework
“I would first verify reader support and sample file footers for page counts, ColumnIndex size, ordering, and predicate selectivity. For sorted customer_id, I would use page min/max bounds to locate candidates; for other columns, I would test bounds and use OffsetIndex to map matching rows to projected columns. I would change ordering or page size only when a benchmark shows value, because Page Index is not a secondary index. Old readers must return the same result while ignoring it. Finally, under cold-cache and fixed resources, I would compare scanned bytes, pages read, planning time, end-to-end p95, and footer/index overhead against an index-disabled control.”
Step-by-step answer
Step 1: Verify format and reader support
Page Index is optional ColumnChunk metadata containing ColumnIndex and OffsetIndex. Inspect the file metadata for index location and length, column order, and column_orders; then enable explicit page-pruning metrics in the target engine. If a reader only writes the index but does not consume it, writing it will not reduce scans.
for each row_group:
read ColumnIndex for predicate columns
select pages whose min/max may match predicate
use OffsetIndex to map selected row ranges to projected columns
read only those page rangesStep 2: Separate ordered and unordered columns
Parquet documents that boundaries for ordered columns support binary search, while unordered columns generally require sequential min/max checks. Ordering is not a format-wide requirement. Record value-range overlap per row group instead of using only table cardinality.
Step 3: Interpret min/max conservatively
Writers may truncate long strings or use boundaries that cover the real value range. Such bounds may cause extra candidate pages but must not exclude a possible match. Interpret nulls, NaNs, and comparisons according to column_orders; when statistics are incomplete, read the page safely.
Step 4: Connect reads across columns
ColumnIndex identifies candidate pages only for predicate columns. Projection still needs other columns, so OffsetIndex maps matching row ranges to their page offsets. Page boundaries can differ across columns; never reuse a page number from one column for another. Without OffsetIndex, a reader may decode more columns sequentially.
Step 5: Measure write and metadata cost
More pages add page headers and index entries; larger pages reduce pruning granularity. Benchmark a matrix of query selectivity, row width, compression, and page size. Point lookups may justify more metadata, while broad scans and full aggregates may only pay extra footer I/O.
Step 6: Design compatibility and rollout
Before enabling writes, inventory all consumers. An old reader that ignores Page Index should use row-group statistics or normal page reads and return the same result. Roll out to new files and a fixed partition first, keeping an index-disabled control. Record result hashes, scanned bytes, and errors for supporting and non-supporting readers.
Step 7: Define repeatable acceptance
Run equivalent queries on the same snapshot with cold cache, fixed concurrency, and repeated trials. Record scanned bytes, pages read, skip ratio, footer/index bytes, decode CPU, end-to-end latency, and result validation. Keep a high-overlap unordered partition as a negative control; stop if the index only adds cost for low-selectivity workloads.
Model answer
“I would first verify that the reader consumes ColumnIndex and OffsetIndex, then sample footers for page count, index length, column_orders, and write ordering. Page Index is optional metadata, not a secondary index; it says which pages may match.
For sorted customer_id, I would binary-search page min/max bounds; for unordered columns, I would check bounds sequentially. I would use OffsetIndex to map matching predicate rows to projected columns, never reuse one column’s page number for another. Truncated statistics may widen candidates; missing statistics, nulls, or uncertain ordering require a safe read.
On writes, I would benchmark selectivity, page size, compression, and footer growth. During rollout I would retain an old-reader and index-disabled control, and require identical results. With cold cache and fixed resources, I would compare scanned bytes, skip ratio, index I/O, CPU, p95, and result hashes before expanding.”
Common mistakes
- Treating Page Index as a secondary index → unordered columns may still have many candidates → measure value-range overlap and selectivity.
- Writing only ColumnIndex → projected columns cannot jump by matching rows → validate OffsetIndex mapping.
- Treating truncated min/max as exact → real matches can be excluded → allow only conservative candidate expansion.
- Testing only warm cache → cache hides I/O → repeat cold-cache control runs.
- Reusing page numbers across columns → page boundaries differ → use row ranges and offsets.
- Ignoring old readers → rollout introduces compatibility regressions → maintain a reader matrix and fallback.
- Checking latency without results → pruning bugs can lose rows → compare hashes and business aggregates.
- Enabling everywhere by default → low-selectivity scans pay metadata cost → roll out per table or partition.
Follow-up questions
Follow-up 1: Why do ordered columns benefit more?
Ordering concentrates values in neighboring pages, so a range often maps to a contiguous page interval that supports binary search. Unordered values overlap more and produce more candidates. Page size and predicate selectivity still determine the result.
Follow-up 2: Can pages be skipped without OffsetIndex?
The predicate column can identify candidates, but projected columns cannot directly locate the same row ranges, so the reader may need more sequential reads. Validate the target reader rather than inferring support from file metadata alone.
Follow-up 3: Is truncated statistics safe?
A correct writer presents conservative bounds covering the real range. This can create false positives and extra reads, but not false negatives. If that guarantee is unavailable, fall back to normal reads.
Follow-up 4: How do you prove no rows are lost?
Run the same snapshot with Page Index enabled and disabled, compare complete results, counts, aggregates, and key samples, then test boundary values, nulls, duplicates, and long strings with synthetic data.
Follow-up 5: When is Page Index not worth writing?
Full scans, low-selectivity predicates, few pages, or footer-dominated latency may not benefit. Compare index bytes, write CPU, and maintenance cost, and keep a per-table or per-partition switch.
Follow-up 6: What about schema or ordering changes?
New files should be interpreted with their own schema and column_orders; the table cannot assume one global ordering. Mixed historical files need capability-aware handling and monitoring for missing indexes.