Indexing the Data Lake for Online Point Queries A Deep Dive

The Problem: Data Lakes Are Not Built for Point Queries

Data lakes are great for batch analytics, but when you need to fetch a single user's record—say, for an AI agent answering "what was I listening to last summer?"—traditional query engines like Trino or BigQuery add seconds of overhead. Even with cloud storage now offering single-digit millisecond latency, the bottleneck is the query planning and scheduling, not the storage itself.

Spotify faces this challenge at exabyte scale. While Bigtable handles petabyte-scale online workloads, the bulk of data resides in GCS. The solution isn't to copy everything into a KV store—that would be prohibitively expensive. Instead, they built an external index that makes existing Parquet files randomly accessible.

The Core Idea: Replace Scans with Lookups

The fundamental problem with Parquet is the chain of dependent reads: fetch footer, parse row group metadata, scan key column, locate pages. Each step requires a round-trip to storage. RAP eliminates this chain by using a precomputed index that maps each key directly to file and row numbers.

# Pseudo-code for RAP-based point query
index = load_external_index("user_id")
file_loc, row_num = index.lookup(user_id)
# Now issue a single ranged read to fetch exactly the needed bytes
page_data = read_range(file_loc, offset=row_num.page_offset, length=row_num.page_size)
result = decode_page(page_data)

The index is a multimap: each key can point to multiple entries across files. Index size is roughly 1% of source data (terabytes of index for petabytes of data), which is manageable and distributes well via hash bucketing.

Optimizations That Matter

Once you have an external index, you can optimize the Parquet files for point queries without breaking compatibility for batch analytics. The key optimizations fall into three categories:

1. Concentrating Key Data

• Sort by key: Ensures rows for the same key are contiguous, minimizing pages to read.
• Co-grouping: Use ARRAY_AGG to store all values for a key in one row—no sort needed.
• Coarser partitioning: Weekly partitions instead of daily reduce the number of files a key spans.

2. Reducing Bytes Read

• One page per key: Flush pages at key boundaries, making each page exactly one key's data.
• ZSTD frame resets: Keep conventional page sizes but compress each key as a separate frame, allowing direct addressing.
• Storage alignment: Pad with ZSTD skippable frames to align reads to block boundaries.

3. Reducing Read Operations

• Blobs/Variants: Store point-query fields as a single JSON or Variant column—one read per file.
• Interleaving columns: Physically place columns for each key together, enabling a single contiguous read.
• Covering indexes: Hoist small values directly into the index, eliminating storage reads entirely.

Trade-offs and Limitations

These optimizations aren't free. One-page-per-key can bloat the PageIndex. ZSTD frame resets force PLAIN encoding, which may reduce compression. Interleaving columns hurts single-column scans by adding dead space. Covering indexes increase index size. Teams must carefully choose which optimizations apply based on their access patterns.

Next Steps

RAP is a promising pattern, but it requires significant engineering effort to implement. For teams considering this, start by analyzing your point-query patterns and identifying which optimizations bring the most value. Also explore complementary techniques like secondary indexes for multi-dimensional lookups.

For more context on modern data engineering trends, check out this Python 3.15 feature preview or learn about zigzag CSS grid layouts.

Deep Dive: The External Index

The external index is the heart of RAP. It's built by reading footers and page locations, scanning key columns, and writing out a mapping. Building it is a batch process that runs on new data as it arrives.

# Example: Building an index fragment for a new Parquet file
from rap import IndexBuilder

builder = IndexBuilder(key_column="user_id")
# Read footer and page locations
metadata = read_parquet_footer(file_path)
# Scan key column to find row groups
row_groups = scan_key_column(metadata, key_column="user_id")
# Write index entries
for row_group in row_groups:
    builder.add_entry(key=row_group.key, file=file_path, rows=row_group.rows)
builder.write_fragment("index/user_id/2026-07-01.rap")

The index is append-only, with fragments per pipeline run. This design avoids contention and allows incremental updates.

Practical Considerations and Pitfalls

Index size and cost: Indexing petabytes produces terabytes of index. While this is cheaper than duplicating data into a KV store, it's not free. Consider partitioning strategies to keep index size manageable.

Columnar vs. row-oriented trade-offs: Some optimizations (like interleaving) make the file less efficient for traditional columnar scans. If your data is heavily used for both analytics and point queries, you may need to balance between the two.

Tooling maturity: This is a custom solution from Spotify. Open-source alternatives may not offer the same level of optimization. Expect to invest in building your own tooling.

Next learning path: To go deeper, study Parquet internals, ZSTD compression framing, and index data structures. Also look into how similar problems are solved in other systems like Apache Druid or ClickHouse.

Conclusion

RAP shows that data lakes can serve interactive workloads without a separate serving layer. By collapsing dependent reads into a single lookup, it unlocks low-latency access to exabytes of data. The key takeaway: start with an external index, then optimize your file layout incrementally.

If you're building AI agents that need to retrieve user context, this is a pattern worth studying. For more on related topics, see the Python 3.15 preview or the zigzag CSS grid technique.