Data engineering interview: How would you tune ORC Bloom-filter indexes?

Prompt and context

An ORC fact table is partitioned by date and each file contains many stripes. Users often filter by customerid and deviceid for equality, but write throughput is falling and some queries still scan too much data. Explain what ORC min/max statistics, row indexes, and Bloom filters can skip, how you would choose columns and a false-positive rate, and how you would benchmark the result.

What the interviewer is testing

• Understanding of ORC file, stripe, and row-index levels and the limits of predicate pushdown.
• Knowing that a Bloom filter can produce false positives but must not reject a value that is present.
• Connecting column cardinality, equality selectivity, write CPU, metadata size, and query savings.
• Proving value with skipped stripes, bytes read, filter hit rates, and end-to-end latency rather than a configuration diff.

Questions to clarify first

1. Are queries mostly highly selective equality predicates, or ranges, ordering, and prefixes?
2. What are the per-stripe cardinality, duplication, and distribution of customerid and deviceid?
3. Do the reader and writer support ORC Bloom-filter indexes and the target version's properties?
4. What are the budgets for write latency, file size, and object-store requests?
5. Are there salting, hashing, or privacy requirements that prohibit raw values in indexes?

A 30-second answer framework

I would separate the predicates first: min/max fits ordered ranges, row indexes narrow a match to a smaller row group, and Bloom filters help with selective equality checks. I would enable the filter first for a measurable column such as customer_id, then compare the default false-positive rate with a lower one on the target reader while measuring write amplification. The benchmark would record skipped stripes, bytes read, CPU, file size, and p95 latency, and would use both random absent values and present values to verify that no rows are lost.

Step-by-step deep answer

Step 1: Assign responsibilities to the three index types

ORC stores lightweight indexes at file, stripe, and row-index levels. Min/max records a column range and can reject a stripe that cannot intersect a range predicate; row indexes narrow the search to a fixed row group. A Bloom filter says a value may be in that index range, so it can reject a value known to be absent for an equality predicate, but it may retain an actually absent range.

Step 2: Choose columns from predicates and distribution

Prefer columns that receive frequent equality filters, have many distinct values per stripe, and can remove stripes from real queries. A low-cardinality column, or one present in nearly every stripe, adds write and metadata cost with little pruning. Ranges, ordering, and aggregations need partitions, sorting, min/max statistics, or a specialized index rather than a Bloom filter alone.

Step 3: Set a false-positive budget

A lower false-positive rate usually requires more bits and hash work, increasing file size and writer CPU; a higher rate retains more stripes and reduces read savings. Establish a baseline with the default, then test a small range of values using real stripe cardinality and query selectivity. Put write throughput, file size, and bytes read in one cost table instead of optimizing only for the lowest rate.

Step 4: Verify the write and read path

Confirm that the writer creates Bloom-filter indexes for the target columns and that the reader consumes them during predicate pushdown. If a property change affects only new files, separate old and new file coverage. The query plan or engine metrics should expose index reads, skipped stripes, and final rows scanned; without those signals, do not claim the filter is active.

Step 5: Build a controlled benchmark

Prepare four workloads: present values, random absent values, low-selectivity values, and range predicates. Fix partitions, file sizes, cache state, and concurrency. Compare filters disabled, the default false-positive rate, and candidate rates while recording stripes scanned, bytes read, decompressed bytes, CPU, p50/p95 latency, write time, and file size. Repeat each workload and report both cold-cache and warm-cache results.

Step 6: Handle evolution and operations

Re-evaluate per-stripe cardinality and selectivity after adding columns or changing sort order. Compaction, merging, and rewrites change index quality, so record index properties in table metadata and the release manifest. Monitor metadata share, write failures, scan amplification, and reader-version differences. If a reader does not support Bloom filters, a safe fallback is to scan, not to discard data.

Step 7: Verify correctness and privacy boundaries

Use values known to exist to check that reads are not lost, and many absent values to measure pruning. If an index is missing or intentionally corrupted, the reader should fall back to a data scan and alert. For sensitive columns, check that the index format, logs, and caches do not expose raw values; if needed, hash or restrict indexed columns and have security review the collision and false-positive risks.

High-quality sample answer

I would first separate min/max, row indexes, and Bloom filters, then select customer_id because real equality queries show high per-stripe selectivity; I would not enable filters blindly for low-cardinality columns. I would benchmark the default rate and progressively lower rates while measuring writer CPU, file size, skipped stripes, bytes read, and p95 latency. The benchmark would include present, absent, low-selectivity, and range queries and verify from the reader plan that the filter is consumed. During a mixed old/new rollout I would segment metrics by file version and fall back to scanning when an index is missing or unsupported. Finally, I would prove no false negatives with correctness samples and check that sensitive values are not exposed through indexes, logs, or caches.

Common mistakes

• Treating a Bloom filter as an exact index that returns every matching row.
• Enabling it on every low-cardinality or ubiquitous column without measuring write amplification.
• Using a range query as proof of Bloom-filter value and confusing it with min/max statistics.
• Looking only at total latency without skipped-stripe and bytes-read metrics, leaving cache effects unexplained.
• Having an unsupported reader discard data instead of scanning it, causing false negatives.

Follow-up questions and responses

Follow-up 1: Why do false positives not cause missing rows?

The filter rejects only a range proven not to contain the value. A false positive keeps an absent range, which the ORC scan then checks. It changes performance, not the correct result.

Follow-up 2: How do you decide whether a column deserves a filter?

Measure per-stripe value coverage and query selectivity, observe how many stripes absent values eliminate, and compare saved read cost with writer CPU, metadata space, and file-lifecycle cost. A column without measured savings should not be enabled by default.

Follow-up 3: How do filters work with partitions and sorting?

Partitions reduce the file set first; sorting and min/max reduce stripes; Bloom filters add an equality check for less ordered data. Disable each layer in the same benchmark to show that the gain comes from the intended layer rather than a partition change.

Follow-up 4: What if old and new ORC files use different parameters?

Segment metrics by writer version and let readers use whatever index exists; scan files with no filter. Normalize gradually through rewrites or merges, without assuming every file has the same false-positive rate during migration.

Follow-up 5: How do you release an index-parameter change?

Record table properties, writer version, target columns, and false-positive rate. Canary representative partitions, compare write and query metrics, then expand. Rollback means stopping new writes with the setting; existing files remain readable with their own indexes.