Lakehouse Integration: Query Pushdown from Hengshi BI to Iceberg, Delta Lake, and Paimon

Article body

Full article

9 min read

Introduction

Enterprise data architecture has moved from data warehouses to data lakes and then to the lakehouse.

Data warehouses perform well for structured analytics, but scaling and storage can be expensive. Data lakes store raw data at a lower cost, but teams must add transaction guarantees and query optimization. A lakehouse brings transactions, metadata, and table management to object storage so analytics systems can query data in place.

Iceberg, Delta Lake, and Apache Paimon are common lakehouse table formats. Hengshi BI reads these formats through a unified connection layer and sends filters, pruning, and aggregation to the storage or compute engine whenever possible. This article compares the three formats and explains the query-pushdown and caching design.

1. How the Lakehouse Changes BI

1.1 Traditional BI and the Data Warehouse

A traditional pipeline cleans business data through ETL, loads it into a warehouse, and lets BI query the warehouse.

Analysis starts only after data movement. The pipeline introduces synchronization delay, duplicate storage, and modeling constraints. Business users can query only the data that the team has loaded and modeled in advance.

1.2 BI Queries Lakehouse Tables Directly

In a lakehouse architecture, applications write data to Iceberg, Delta, or Paimon tables, and BI queries those tables.

Less movement: Data becomes queryable after it enters a lakehouse table, shortening the analytics pipeline.
Full detail: Analysts can use aggregates and drill into raw records.
Lower storage cost: Object storage separates compute from storage and can retain large historical datasets.

The BI query engine must understand table metadata, statistics, and partition layouts to avoid scanning every source file.

2. Technical Differences Between the Table Formats

2.1 Apache Iceberg

Iceberg emphasizes an open specification and engine independence. It provides:

Hidden partitioning: Users filter on business columns, while Iceberg maps predicates to physical partitions.
Snapshot isolation: Each write creates a snapshot, allowing readers and writers to work at the same time.
Schema evolution: Compatible field changes preserve access to historical data.
Column statistics: Files record minimum, maximum, and null counts for file-level pruning.

A BI engine can read Iceberg manifests and exclude files that cannot match a query predicate.

2.2 Delta Lake

Delta Lake integrates with the Spark ecosystem and uses the Delta Log for ACID transactions and version management.

ACID transactions: The transaction log maintains a consistent table state.
Time Travel: Queries can read a specified historical version.
Z-Order clustering: Data is organized by several columns to reduce scans for filtered queries.
OPTIMIZE: Small files are compacted to improve read efficiency.

A BI connector must parse the Delta Log and create its query snapshot against one table version.

2.3 Apache Paimon

Paimon targets unified streaming and batch workloads, including continuous writes from Flink and batch analytics.

Streaming and batch access: One table supports streaming writes and batch reads.
LSM structure: Leveled compaction supports sustained write throughput.
Changelog: The table records changes for CDC workloads.
Primary-key tables: Upserts retain the latest state for each business key.

The BI engine must understand Paimon snapshots and changelog semantics to avoid counting old and updated records together.

3. Hengshi BI’s Lakehouse Connection Architecture

3.1 Unified Connection Layer

Hengshi BI provides a corresponding access path for each format in its data-source layer.

Iceberg connector

Discovers tables through Hive Metastore, AWS Glue, or a filesystem catalog
Reads manifests for statistics and file lists
Converts BI queries into Iceberg scan plans

Delta connector

Reads the Delta Log to select a query version
Parses transaction entries to find the active files
Provides a Time Travel entry point for historical analysis

Paimon connector

Accesses Paimon tables written by Flink or Spark
Reads snapshots and changelogs to obtain the current data state
Retrieves newly written data for incremental analysis

3.2 Query Pushdown

Query pushdown executes filtering and aggregation closer to the data, reducing the volume transferred to and processed by BI nodes.

Partition pruning

The connector reads table metadata and identifies partition fields. If a query filters on a partition column such as a date, the scan covers only matching partitions.

File-level pruning

The connector reads minimum, maximum, and null-count statistics. If a file’s range cannot satisfy the predicate, the query plan skips that file.

Column pruning

Lakehouse tables often contain dozens or hundreds of columns. The query plan reads only the columns required by the analysis, using columnar formats such as Parquet or ORC to reduce I/O.

Aggregation pushdown

The query engine can push grouped aggregates into the storage compute layer and reuse materialized views, aggregate statistics, or available indexes. This reduces the number of detailed records returned to BI.

3.3 Layered Caching

The first scan of a lakehouse table may have high latency because it must inspect many files. Hengshi BI can use three cache layers:

Metadata cache: Catalog entries, manifests, and column statistics
Query-result cache: Results for identical predicates
Pre-aggregation cache: Precomputed summaries for frequent analysis patterns

The platform invalidates related entries when it detects a new snapshot or commit, preventing stale results.

4. Integration Patterns

4.1 Iceberg with a StarRocks Acceleration Layer

A financial organization can use Flink to write data into Iceberg. Hengshi BI queries Iceberg for detailed exploration and synchronizes data required by frequent analyses into StarRocks.

Query routing sends detailed exploration to Iceberg and fixed dashboards to StarRocks, balancing full-history storage cost with interactive performance.

4.2 Direct Delta Lake Analysis

Teams using Delta Lake can write data with Spark and run OPTIMIZE and Z-Order. Hengshi BI reads Delta tables and uses their file organization and statistics to reduce scan volume. Time Travel selects a specific snapshot for version comparisons.

4.3 Real-Time Analytics with Paimon

A retailer can capture operational database changes with Flink CDC and write them into a Paimon primary-key table. Hengshi BI queries the latest snapshot to provide low-latency data for revenue and conversion metrics.

5. Engineering Challenges

5.1 Small Files

Continuous streaming writes can create many small files, with each file adding metadata and scheduling overhead. Teams can address the problem at three points:

Adjust checkpoint and file-rolling policies on the write side.
Run Iceberg rewrite_data_files or Paimon compaction on the storage side.
Cache metadata in BI to reduce repeated file listing.

5.2 Metadata Hotspots

High query concurrency can overload a catalog such as Hive Metastore. Connectors can cache schemas and partition lists on BI nodes and rate-limit catalog requests. Larger deployments can adopt a catalog service with greater throughput.

5.3 Consistency

Queries must bind to a defined snapshot while a table receives writes or runs OPTIMIZE. The connection layer records a snapshot version at query start and completes the read against that version. Monitoring can also check the latest snapshot time and detect stalled write jobs.

5.4 Permissions

Lakehouse tables often use systems such as Ranger for access control. A BI platform can map user permissions to lakehouse policies or connect through a service account and inject user-level filters into each query. Every access should enter an audit log.

6. Conclusion

The lakehouse lets BI query detailed data in object storage and reduces movement and duplicate storage. Useful query performance and consistency depend on connectors that understand snapshot, statistics, and partition semantics.

Hengshi BI connects to Iceberg, Delta Lake, and Paimon through a unified layer. Partition pruning, file-level statistics, column pruning, aggregation pushdown, and layered caching control scan volume. Organizations can query the lakehouse directly or add an acceleration layer such as StarRocks for selected workloads.