Lakehouse Architecture at Bilibili: Query Acceleration and Index Enhancement Practices

The presentation, hosted by DataFunTalk and featuring Bilibili technical expert Li Chengxiang, introduces the concept of lake‑warehouse integration, which combines the flexibility of a data lake with the performance of a data warehouse.

What is a Data Lake? A data lake provides a unified storage system for all data types—structured, semi‑structured, and unstructured—without data silos, supports multiple compute engines (e.g., Spark, Flink, Presto), and offers flexible APIs for SQL, Dataset, RDD, and machine‑learning workloads, though data quality can be lower.

What is a Data Warehouse? A data warehouse enforces a strong schema, stores data in a closed format, offers high query efficiency through tight compute‑storage integration, rich indexing, and pre‑computation, and is suited for known‑data analytics and decision‑making.

Bilibili originally built its big‑data platform on the Hadoop ecosystem (HDFS, Hive, Spark, Presto) and later introduced ClickHouse for interactive analytics, which created challenges such as data duplication, longer ingestion pipelines, and data inconsistency.

To bridge the gap between lake and warehouse, Bilibili evaluated Iceberg, Hudi, and Delta Lake, ultimately selecting Iceberg for its ability to address these issues.

Lake‑Warehouse Architecture – Real‑time data flows through Kafka and is ingested by Flink into Iceberg tables on HDFS; batch data is written by Spark. A custom service called Magnus continuously optimizes the organization of Iceberg tables using Spark jobs. Queries are served by Trino (PrestoSQL) with Alluxio caching metadata and index data.

Data Sorting Organization – For star‑schema benchmark queries, Bilibili uses Z‑Order sorting to maintain multi‑dimensional clustering, improving data skipping. Traditional MinMax file‑level indexes are insufficient when multiple filter columns are involved, so Z‑Order interleaves bits of selected columns to preserve clustering across all of them.

Implementation details include mapping various data types to positive integers, handling boundary‑based interleave indexing, and using Spark RangePartitioner to generate Z‑Values that start from zero.

Experimental results show that Z‑Order combined with MinMax indexing reduces file reads and query latency dramatically on the Star Schema Benchmark (SSB), though effectiveness diminishes with too many sort columns.

Index Enhancement – Bilibili adds BloomFilter indexes at the file level for equality filters, and BitMap indexes for both equality and range filters. To mitigate high cardinality and range‑filter costs, they introduce Range‑Encoded BitMap and Bit‑Slice Encoded BitMap, drastically reducing the number of stored BitMaps while preserving filtering power.

These indexing and sorting strategies are exposed via Iceberg‑Spark extensions, allowing users to specify distribution and ordering methods (hash, range, Z‑Order, Hilbert curve) and to invoke Magnus‑driven optimization tasks.

Overall, the combined sorting and indexing techniques enable multi‑field, multi‑type filtering with minimal file access, significantly accelerating analytical queries in Bilibili's lake‑warehouse environment.

For more details, see the accompanying diagrams and screenshots embedded in the original article.