Skip to content
E-Commerce Order Flow Explains Database, Warehouse, Lake, Platform & Lakehouse Differences
Big Data 51 min read

E-Commerce Order Flow Explains Database, Warehouse, Lake, Platform & Lakehouse Differences

This article uses an e-commerce order scenario to distinguish databases, data warehouses, data lakes, data platforms, and lakehouse architectures, detailing their roles, storage, schemas, and query patterns, then dives into Iceberg, Hudi, Paimon, and Delta Lake selection, CDC pipelines, schema evolution, small-file compaction, and real-time/offline consistency engineering practices.

Rain's Path to Java Mastery
Rain's Path to Java Mastery
Rain's Path to Java Mastery

1. An E-Commerce Scenario Links Five Concepts

Assume a user places an order on an e-commerce app:

Database : Order written to MySQL, inventory deducted — no overselling, no duplicate charges. Handles online transactions.

Data Lake : MySQL binlog via Flink CDC into Kafka or real-time compute; order logs, user events, image metadata saved to object storage. Low-cost raw data retention.

Data Warehouse : Spark/Flink clean and model raw data into order details, user summaries, KPIs for BI. Unified historical analysis.

Lakehouse : Same order data written once into Iceberg, Hudi, or Paimon tables supporting upsert, snapshots, and stream/batch reads. One copy, both real-time and offline usable.

Data Platform (中台) : Metrics like GMV, active users, risk customers become unified indicators, tags, and APIs consumed by marketing, recommendation, risk control. Asset reuse and governance.

Key boundary: Lakehouse solves "store cheap & manage well"; Data Platform solves "duplicate build & metric conflicts". They complement but cannot replace each other.

2. Five-Way Comparison

Dimensions: core positioning, data shape, typical storage, schema approach, write pattern, query pattern, latency, cost, typical tech, biggest risk.

Database (OLTP) : Row store, high-perf disk, schema-on-write, high-frequency small transactions, index point queries, ms latency, higher cost, MySQL/Oracle/PostgreSQL/OceanBase, risk: analytical queries drag down OLTP.

Data Warehouse (OLAP) : Column store, MPP/distributed storage, schema-on-write, batch ETL, large scans/aggregations, sec to T+1, medium-high cost, Hive/ClickHouse/Doris/StarRocks/MaxCompute, risk: inflexibility, long pipelines.

Data Lake : HDFS/S3/OSS, Parquet/ORC, schema-on-read, append-only, exploratory reads, latency depends on pipeline, low cost, risk: becomes data swamp.

Data Platform : Metrics, tags, APIs, asset catalog, service-oriented output, depends on underlying SLA, org/build cost high, OneData/OneID/OneService/Dataphin, risk: built but unused.

Lakehouse : Object storage/HDFS + open table formats (Iceberg/Hudi/Paimon/Delta Lake), write-time constraints with flexible evolution, stream/batch writes, upsert, ACID, interactive analysis/stream/batch/incremental reads, sec to hour latency, low storage + elastic compute, risk: small files, metadata bloat, concurrent conflicts.

Technical summary: Database guarantees transaction correctness; warehouse guarantees analysis grain; lake guarantees raw completeness; platform guarantees asset reuse; lakehouse gives one dataset both low-cost storage and warehouse management capabilities.

3. Why These Layers Emerged

Architecture evolves due to real pain, not for show. Early Lambda architecture maintained separate batch (Hive/Spark T+1) and streaming (Kafka/Flink minute-level) paths: same GMV coded twice, two storages, double backfill, reconciliation nightmares. Public DWS layer hard to reuse, duplicate compute, post-backfill inconsistency.

Lakehouse aims for: (1) one dataset, no lake↔warehouse shuffling; (2) native open table formats; (3) streaming write, batch compute, interactive query share data; (4) unified metadata, permissions, lineage, lifecycle.

Lambda vs Lakehouse architecture
Lambda vs Lakehouse architecture
Lakehouse unified storage and compute
Lakehouse unified storage and compute

Note: Lakehouse unifies storage and table format foundation, not that all queries must use one engine. ClickHouse/Doris can still serve as high-SLA report acceleration layers.

4. Database vs Warehouse: One Runs Transactions, One Runs Analysis

4.1 Why Analytical Queries Must Not Hit the Business Database

Databases use row store, B+ tree, MVCC, locks for high-concurrency small transactions. They care "is this order written correctly", not "three-year category profit margin". A monthly report SQL scanning full table on the primary DB will likely slow online business via full scans, long transactions, IO contention.

-- Anti-pattern: large analytical query on primary DB
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS order_cnt
FROM orders
WHERE created_at > '2023-01-01'
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

Transactional queries should use proper composite indexes:

ALTER TABLE orders ADD INDEX idx_uid_status_ctime (user_id, status, created_at);
EXPLAIN SELECT id, order_no, amount FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY created_at DESC LIMIT 20;
Index design highlight: user_id , status are equality conditions placed first; created_at for ordering placed last. This narrows scan range and may leverage index ordering to avoid filesort. Covering index depends on actual SELECT fields and EXPLAIN .

Analytical traffic should go to read replicas, CDC into warehouse or lake. Don't bind boss's dashboard and user payment requests on the same primary.

MVCC interview follow-up:

-- Snapshot read: consistent view via MVCC
SELECT * FROM `order` WHERE id = 1;
-- Current read: lock latest data
SELECT * FROM `order` WHERE id = 1 FOR UPDATE;

Under MySQL InnoDB REPEATABLE READ, ordinary snapshot read and locking current read differ. Preventing phantom reads in current reads involves Next-Key Lock, not simply "RR never has phantoms".

4.2 Warehouse Layering Isn't About Creating More Tables

Warehouse core: turn multi-system data into one set of agreed metrics. Common layers:

ODS : Retain source system raw structure for traceability

DWD : Clean, deduplicate, standardize, keep finest business facts

DWS : Light aggregation by subject and common grain

ADS : Serve reports, risk, marketing specific scenarios

Modeling: Inmon top-down enterprise normalization, Kimball dimensional/star schema. Chinese internet projects commonly use Kimball + layered modeling .

Example DWD order detail table (Doris syntax):

CREATE TABLE dwd_order_detail (
  order_id BIGINT COMMENT '订单ID',
  user_id BIGINT COMMENT '用户ID',
  sku_id BIGINT COMMENT '商品SKU ID',
  order_amount DECIMAL(16,2) COMMENT '订单实付金额',
  create_time TIMESTAMP COMMENT '下单时间',
  dt DATE COMMENT '分区日期'
) ENGINE=OLAP
DUPLICATE KEY(order_id)
COMMENT '电商订单明细事实表'
PARTITION BY RANGE(dt) ()
DISTRIBUTED BY HASH(order_id) BUCKETS 10
PROPERTIES (
  "replication_num" = "3",
  "compression" = "ZSTD"
);

ODS→ADS processing key: put deduplication and metric definitions at the right layer:

-- DWD: keep latest update_time per order
INSERT OVERWRITE TABLE dwd_order_di PARTITION (dt = '${bizdate}')
SELECT order_id, user_id, sku_id, CAST(amount AS DECIMAL(18,2)) AS amount, pay_time, update_time
FROM (
  SELECT order_id, user_id, sku_id, amount, pay_time, update_time,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn
  FROM ods_order_inc WHERE dt = '${bizdate}'
) t WHERE rn = 1;

-- DWS: common grain summary for multi-scenario reuse
INSERT OVERWRITE TABLE dws_trade_user_1d PARTITION (dt = '${bizdate}')
SELECT user_id, COUNT(DISTINCT order_id) AS order_cnt, SUM(amount) AS gmv, MAX(pay_time) AS last_pay_time
FROM dwd_order_di WHERE dt = '${bizdate}' AND pay_time IS NOT NULL GROUP BY user_id;

-- ADS: application-facing, no redefinition of GMV
SELECT user_id, order_cnt, gmv FROM dws_trade_user_1d WHERE dt = '${bizdate}' AND gmv > 0;

Hive/ORC summary table can also be date-partitioned:

CREATE TABLE dws_trade_user_1d (
  user_id BIGINT COMMENT '用户ID',
  order_cnt BIGINT COMMENT '当日订单数',
  pay_amt DECIMAL(18,2) COMMENT '当日支付金额'
) PARTITIONED BY (dt STRING) STORED AS ORC;
Warehouse's most valuable sentence: Metric definitions should settle in DWS and metric layer, not scattered across twenty report SQLs. Layering decouples ingestion, common processing, and scenario application so the same public logic runs only once.

Historical dimensions need SCD (slowly changing dimension) designs like zipper tables; otherwise today's user level overwrites yesterday's, causing "time travel" in historical order analysis.

5. Data Lake & Data Platform: One Isn't a Dump, One Isn't a Super Warehouse

5.1 Data Lake Lowers Storage Barrier, Not Governance Barrier

Data lake = HDFS/S3/OSS + Parquet/ORC/JSON, suitable for logs, IoT, audio/video, model samples — "collected now, unsure how to use later". But without metadata catalog, permissions, quality, lineage, lifecycle, it quickly becomes a data swamp: PBs stored, no one knows tables; found table, fields meaningless; want to delete, fear model dependency; upstream error, can't trace impacted reports.

So onboarding should simultaneously register in Catalog, assign Owner, quality rules, lineage collection. Apache Atlas, DataHub can handle parts, but tools don't replace governance responsibility.

5.2 Data Platform Core: OneData, OneID, OneService

Data platform isn't a database, nor "build another super warehouse". It's an assetization and reuse mechanism:

OneData : unified model, metrics, statistical definitions.

OneID : connect user, customer, merchant entity identifiers.

OneService : unified data delivery via APIs, metric services, tag services.

Supporting capabilities: MDM, metadata catalog, lineage, quality, permissions, audit, data asset lifecycle.

Many platform projects fail not due to weak tech, but no one pays for reuse: business complains slow onboarding, platform team distant from business, ends up as centralized data-fetching department.

Safer path: first find a metric/tag genuinely reused by multiple business lines as pilot, give asset Owner, SLA, reuse rate metrics, then gradually platformize. Don't build "company-level big platform" then hunt for needs.

6. Lakehouse Core: Not Object Storage, But Table Formats

Parquet files on object storage don't auto-gain transactional capabilities. What gives "lake" the "warehouse" abilities are table formats like Iceberg, Hudi, Paimon, Delta Lake. They provide via metadata, transaction logs, snapshots, manifest files, or LSM structures:

ACID or table-level transaction commits

Row-level update, delete, upsert

Schema evolution

Snapshot isolation & time travel

Incremental reads

Multi-engine sharing

File statistics & data skipping

6.1 How to Choose Among Four Table Formats

Comparison dimensions:

Design Gene : Iceberg (open table format, multi-engine consistent access), Hudi (incremental processing & upsert), Paimon (Flink-native real-time lakehouse), Delta Lake (Spark/Databricks ecosystem)

Metadata/Structure : Iceberg ( metadata.json → manifest list → manifest → data file), Hudi ( .hoodie/ Timeline), Paimon (LSM-Tree + Snapshot), Delta Lake ( _delta_log/)

Write Characteristics : Iceberg (snapshot commit, optimistic concurrency), Hudi (CoW/MoR, strong row-level update), Paimon (LSM append + background compaction), Delta Lake (smooth writes in Spark ecosystem)

Schema Evolution : All support; Iceberg strong, field-ID based

Incremental Read : Iceberg (snapshot diff), Hudi (native Incremental Query), Paimon (changelog incremental consumption), Delta Lake (Change Data Feed etc.)

Standout Capability : Iceberg (hidden partitioning, partition evolution, multi-engine), Hudi (CoW/MoR, indexing, CDC), Paimon (primary key tables, Partial Update, unified stream/batch), Delta Lake (Deletion Vectors, Liquid Clustering)

Better For : Iceberg (Spark/Flink/Trino/StarRocks coexistence, large table analytics), Hudi (high-frequency CDC, upsert, Hive migration), Paimon (Flink real-time pipelines, second-level freshness), Delta Lake (Databricks/Spark unified stack)

Some project benchmarks: Paimon ~1-3s end-to-end latency at 10k TPS; Iceberg high-frequency streaming writes typically seconds to tens of seconds; Hudi MoR writes faster but read has log merge cost. These numbers vary greatly with version, checkpoint, file size, storage media, query mode — not universal baselines.

Real selection boils down to three questions: write-heavy or read-heavy? How high real-time requirement? Main compute stack Flink or Spark?

Multi-engine sharing, PB analytics, strong schema evolution → Iceberg

Spark-centric, high-frequency upsert/CDC → Hudi

Flink-centric, real-time primary key tables & changelog → Paimon

Databricks/Spark integrated stack → Delta Lake

6.2 Iceberg: Snapshots, Hidden Partitioning, Time Travel

An Iceberg Append commit isn't just "write another Parquet file"; it atomically switches metadata to generate a new snapshot:

Table table = catalog.loadTable(TableIdentifier.of("db", "orders"));
AppendFiles append = table.newAppend();
append.appendFile(
  DataFiles.builder(table.spec())
    .withPath("/warehouse/orders/data/00000-0-abc.parquet")
    .withFileSizeInBytes(1024 * 1024)
    .build()
);
// Atomic commit: readers see either old or new snapshot
append.commit();
Snapshot current = table.currentSnapshot();
long snapshotId = current.snapshotId();

Old snapshots retained temporarily, enabling historical reads by snapshot or time, and rollback of bad batches.

Spark integration and CDC Merge:

SparkSession spark = SparkSession.builder()
  .appName("LakehouseDemo")
  .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions")
  .config("spark.sql.catalog.my_catalog", "org.apache.iceberg.spark.SparkCatalog")
  .config("spark.sql.catalog.my_catalog.type", "hive")
  .getOrCreate();

spark.sql("CREATE TABLE IF NOT EXISTS my_catalog.db.order_dwd (" +
  "order_id STRING, user_id STRING, amount DECIMAL(10,2), ts TIMESTAMP" +
  ") USING iceberg PARTITIONED BY (days(ts))");

spark.sql("MERGE INTO my_catalog.db.order_dwd t " +
  "USING cdc_source_view s " +
  "ON t.order_id = s.order_id " +
  "WHEN MATCHED THEN UPDATE SET * " +
  "WHEN NOT MATCHED THEN INSERT *");

Dataset<Row> oldData = spark.read()
  .format("iceberg")
  .option("snapshot-id", 3821550127949089987L)
  .load("my_catalog.db.order_dwd");

User behavior table with hidden partitioning, target file size, merge-on-read:

CREATE TABLE lake.db.user_behavior (
  user_id BIGINT,
  behavior STRING COMMENT 'click/view/buy',
  ts TIMESTAMP,
  product_id BIGINT
) USING iceberg
PARTITIONED BY (days(ts), bucket(16, user_id))
TBLPROPERTIES (
  'format-version' = '3',
  'write.target-file-size-bytes' = '268435456',
  'write.merge.mode' = 'merge-on-read'
);

-- Read historical version by timestamp or snapshot
SELECT * FROM lake.db.user_behavior TIMESTAMP AS OF '2026-10-01 00:00:00';
SELECT * FROM lake.db.user_behavior VERSION AS OF 1234567890;

-- CDC row-level update & delete
MERGE INTO lake.db.user_behavior AS t
USING ods.user_behavior_cdc AS s
ON t.user_id = s.user_id AND t.ts = s.ts
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;
Why hidden partitioning is fast: days(ts) partition info written into metadata. Query filtering on ts lets engine prune manifest list and manifest first, then use column stats min/max to skip files. Many data files never opened.

Time travel for both query and mistake recovery:

CALL system.rollback_to_snapshot('ods_order', 123456789);

Spark DataFrame write and snapshot rollback can combine, but rollback should be triggered by exception handling or ops process, not unconditionally after every normal write.

public void appendOrders(SparkSession spark, Dataset<Row> orderDF) {
  orderDF.writeTo("iceberg_ods.dwd_order_iceberg")
    .option("write-format", "parquet")
    .option("merge-schema", "true")
    .append();
}

public void rollback(SparkSession spark, long snapshotId) {
  spark.sql("CALL iceberg_ods.system.rollback_to_snapshot(" +
    "'iceberg_ods.dwd_order_iceberg', " + snapshotId + ")");
}

Iceberg schema evolution uses field IDs, not column positions. Adding/renaming columns can modify metadata only, no historical Parquet rewrite.

table.updateSchema()
  .addColumn("channel", Types.StringType.get(), "流量渠道")
  .commit();

However, compatible vs breaking changes must be handled separately . Adding nullable column is safe; int → string or changing business meaning should use new column, dual-write canary, and necessary backfill — don't expect table format to solve semantic issues.

6.3 Flink CDC + Iceberg: Real-Time Ingestion Main Pipeline

Flink SQL from MySQL binlog to Iceberg:

CREATE TABLE user_source (
  database_name STRING METADATA FROM 'source.database' VIRTUAL,
  table_name STRING METADATA FROM 'source.table' VIRTUAL,
  id DECIMAL(20,0) NOT NULL,
  name STRING,
  phone_number STRING,
  PRIMARY KEY (id) NOT ENFORCED
) WITH (
  'connector' = 'mysql-cdc',
  'hostname' = 'mysql-host',
  'database-name' = 'inventory',
  'table-name' = 'users',
  'username' = 'flinkuser',
  'password' = '***',
  'scan.incremental.snapshot.enabled' = 'true'
);

CREATE TABLE all_users_sink (
  database_name STRING,
  table_name STRING,
  id DECIMAL(20,0) NOT NULL,
  name STRING,
  PRIMARY KEY (database_name, table_name, id) NOT ENFORCED
) WITH (
  'connector' = 'iceberg',
  'catalog-name' = 'iceberg_catalog',
  'catalog-type' = 'hive',
  'warehouse' = 's3://lakehouse/warehouse',
  'format-version' = '2'
);

INSERT INTO all_users_sink SELECT * FROM user_source;

Two practical details: scan.incremental.snapshot.enabled=true for incremental snapshot reads, reducing full-phase lock pressure.

Composite PK (database_name, table_name, id) avoids PK conflicts after sharding.

Java DataStream similar:

StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
env.enableCheckpointing(60000);

MySqlSource<String> source = MySqlSource.<String>builder()
  .hostname("mysql-host").port(3306)
  .databaseList("order_db").tableList("order_db.t_order")
  .username("flinkuser").password("***")
  .deserializer(new JsonDebeziumDeserializationSchema())
  .build();

DataStream<String> stream = env.fromSource(source, WatermarkStrategy.noWatermarks(), "MySQL CDC");
stream.sinkTo(IcebergSink.forRowData().tableLoader(tableLoader).build());
End-to-end Exactly-Once isn't just enabling one checkpoint. It requires CDC Source, Flink Checkpoint, Sink two-phase commit or idempotent writes working together. Failure recovery, commit timeouts, external system semantics all need joint verification.

Incremental consumption can use snapshot ID as boundary; dimension joins must prevent "old order joining new dimension":

SET snapshot_id = (
  SELECT snapshot_id FROM iceberg_catalog.ods.order_ods.snapshots
  ORDER BY committed_at DESC LIMIT 1
);

INSERT INTO dwd.order_detail_wide
SELECT o.order_id, u.user_name, p.product_name, o.amount, o.event_time
FROM (
  SELECT * FROM iceberg_catalog.ods.order_ods
  /*+ OPTIONS('snapshot-id' = ${snapshot_id}) */
) o
LEFT JOIN iceberg_catalog.dim.user_dim
FOR SYSTEM_TIME AS OF o.event_time ON o.user_id = u.user_id
LEFT JOIN iceberg_catalog.dim.product_dim p ON o.product_id = p.product_id
WHERE o.event_time > CURRENT_TIMESTAMP - INTERVAL '10' MINUTE;

Here FOR SYSTEM_TIME AS OF embodies business time semantics : order happened yesterday, should join yesterday's valid user dimension, not today's updated profile.

6.4 Paimon: Real-Time Primary Key Tables & Partial Update

Paimon excels in Flink real-time pipelines. Primary key tables based on LSM support upsert, and can produce changelog for downstream streaming consumption:

CREATE CATALOG paimon_catalog WITH (
  'type' = 'paimon',
  'warehouse' = 'hdfs:///paimon/warehouse'
);

CREATE TABLE paimon_catalog.dwd.order_detail (
  order_id BIGINT,
  user_id BIGINT,
  amount DECIMAL(18,2),
  update_time TIMESTAMP(3),
  PRIMARY KEY (order_id) NOT ENFORCED
) WITH (
  'bucket' = '4',
  'changelog-producer' = 'input'
);

INSERT INTO paimon_catalog.dwd.order_detail
SELECT order_id, user_id, amount, update_time FROM ods_order_cdc;

Deduplication merge strategy to keep latest per PK:

CREATE TABLE paimon_order (
  order_id BIGINT,
  user_id BIGINT,
  pay_amt DECIMAL(18,2),
  update_time TIMESTAMP(3),
  PRIMARY KEY (order_id) NOT ENFORCED
) WITH (
  'connector' = 'paimon',
  'merge-engine' = 'deduplicate',
  'changelog-producer' = 'input'
);
changelog-producer

common configs: input, lookup — different modes trade off overhead vs changelog completeness; can't just copy params without checking downstream semantics.

Paimon's practical Partial Update : order stream and risk stream update different fields of same user wide table.

CREATE TABLE paimon.dw.dwd_order (
  order_id BIGINT,
  user_id BIGINT,
  amount DECIMAL(18,2),
  status STRING,
  update_time TIMESTAMP(3),
  dt STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
) PARTITIONED BY (dt)
WITH (
  'bucket' = '4',
  'changelog-producer' = 'lookup',
  'snapshot.time-retained' = '7 d'
);

CREATE TABLE paimon.dw.dws_user_wide (
  user_id BIGINT,
  order_cnt BIGINT,
  last_pay_ts TIMESTAMP(3),
  risk_score INT,
  PRIMARY KEY (user_id) NOT ENFORCED
) WITH (
  'merge-engine' = 'partial-update',
  'fields.order_cnt.aggregate-function' = 'sum'
);

-- Order stream updates only order fields
INSERT INTO paimon.dw.dws_user_wide
SELECT user_id, order_cnt, last_pay_ts, CAST(NULL AS INT) FROM order_stream;

-- Risk stream updates only risk fields
INSERT INTO paimon.dw.dws_user_wide
SELECT user_id, CAST(NULL AS BIGINT), CAST(NULL AS TIMESTAMP(3)), risk_score FROM risk_stream;
Design highlight: Traditional Flink Join for wide tables requires large state. Partial Update pushes "merge fields by PK" down to storage layer, significantly reducing Join state pressure. However, how aggregation fields merge, whether nulls overwrite, how out-of-order data handled — still need business-defined validation.

Paimon also supports time-travel scans and tag management:

SELECT * FROM paimon_order /*+ OPTIONS('scan.timestamp-millis'='1730000000000') */;
CALL sys.create_tag('paimon_order', 'before_big_promotion');

6.5 Hudi: CoW/MoR & Native Incremental Query

Hudi's two table types often cause confusion:

CoW (Copy on Write) : Rewrite data files on update, higher write amplification; direct read; suitable for read-heavy, write-light, prioritize read perf.

MoR (Merge on Read) : Changes written to log first, background merge; read merges Base + Log; suitable for write-heavy, low-latency CDC.

Upsert write config example:

HoodieWriteConfig cfg = HoodieWriteConfig.newBuilder()
  .withPath("hdfs:///hudi/dwd_order")
  .forTable("dwd_order")
  .withWriteOperationType(WriteOperationType.UPSERT)
  .withIndexConfig(
    HoodieIndexConfig.newBuilder()
      .withIndexType(HoodieIndex.IndexType.BUCKET)
      .withBucketIndexEngineType(BucketIndexEngineType.SIMPLE)
      .withBucketNum(8)
      .build()
  )
  .withStorageConfig(
    HoodieStorageConfig.newBuilder()
      .parquetMaxFileSize(256L * 1024 * 1024)
      .build()
  )
  .withCompactionConfig(
    HoodieCompactionConfig.newBuilder()
      .withInlineCompaction(true)
      .withMaxNumDeltaCommitsBeforeCompaction(10)
      .build()
  )
  .build();

Three engineering levers:

BUCKET index reduces index probe cost under high-concurrency upsert. parquetMaxFileSize controls file size at source.

Inline Compaction auto-merges after Delta Commit threshold.

Hudi incremental query reads changes after a commit:

Dataset<Row> incremental = spark.read()
  .format("hudi")
  .option("hoodie.datasource.query.type", "incremental")
  .option("hoodie.datasource.read.begin.instanttime", "20260101000000")
  .load("/warehouse/hudi/orders");
incremental.show();
_hoodie_commit_time

etc. serve as incremental consumption basis, avoiding downstream full rescans. CDC often configures precombine field to ensure out-of-order messages retain correct version by business time.

7. One Dataset Serving BI, Real-Time Compute, and AI

Unified table format lets compute engines choose per scenario, not copy data per engine.

7.1 Federated Query: Lake Logs Join Warehouse Metrics

SELECT l.user_id, o.total_order_cnt, l.page_view_cnt
FROM iceberg.ods.user_behavior_log l
LEFT JOIN clickhouse.dws.user_order_1d o ON l.user_id = o.user_id
WHERE l.dt = '2026-10-28' AND o.dt = '2026-10-28' LIMIT 1000;

Presto/Trino federated query suits cross-source analysis, but cross-engine joins aren't inherently cheap. Large table joins still need broadcast, skew handling, network, predicate pushdown.

7.2 External Catalog: StarRocks/Trino Direct Query Iceberg

CREATE EXTERNAL CATALOG iceberg_lake
PROPERTIES (
  'type' = 'iceberg',
  'iceberg.catalog.type' = 'hive',
  'hive.metastore.uris' = 'thrift://hms:9083'
);

SELECT dt, COUNT(*) AS cnt FROM iceberg_lake.db.user_behavior
WHERE dt >= '2026-10-01' GROUP BY dt ORDER BY dt;
Zero-ETL value isn't "ETL disappears", but avoiding another data copy just for query. Cleaning, modeling, quality checks still needed; data just stops meaningless shuffling.

For high-concurrency sub-second fixed reports, add ClickHouse/Doris/StarRocks, materialized views, or caching on top of lakehouse. Unified base and high-perf acceleration layer don't conflict.

8. Java Side: Shielding Multi-Engine Differences

If business code littered with "this query MySQL, next Hive, then Iceberg", it becomes an if-else mess. Better: strategy routing encapsulation.

public interface DataQueryService { QueryResult query(QueryRequest req); }

@Service("mysql")
class MySqlQueryService implements DataQueryService {
  @Override public QueryResult query(QueryRequest req) { return jdbcTemplate.queryForObject(req.getSql(), rowMapper); }
}

@Service("hive")
class HiveQueryService implements DataQueryService {
  @Override public QueryResult query(QueryRequest req) { return sparkSql(req.getSql()); }
}

@Service("lakehouse")
class LakehouseQueryService implements DataQueryService {
  @Override public QueryResult query(QueryRequest req) { return icebergSql(req.getSql(), req.getSnapshotId()); }
}

@Component @RequiredArgsConstructor
class DataQueryRouter {
  private final Map<String, DataQueryService> serviceMap;
  public QueryResult route(QueryRequest req) {
    DataQueryService service = serviceMap.get(req.getScene());
    if (service == null) throw new IllegalArgumentException("Unsupported query scene");
    return service.query(req);
  }
}

Add new engine: new implementation + routing config, no caller changes.

In platform scenario, prepend metric metadata, permissions, masking, cache, capacity protection before routing:

@Service
public class OneDataServiceGateway {
  private final Map<QueryScene, EngineExecutor> router = new EnumMap<>(QueryScene.class);
  private final Cache<String, QueryResult> hotCache = Caffeine.newBuilder()
    .maximumSize(10_000).expireAfterWrite(Duration.ofSeconds(30)).recordStats().build();

  @PostConstruct public void init() {
    router.put(QueryScene.REALTIME_DASHBOARD, dorisExecutor);
    router.put(QueryScene.ADHOC_ANALYSIS, clickHouseExecutor);
    router.put(QueryScene.DETAIL_SCAN, paimonExecutor);
  }

  public QueryResult query(QueryReq req) {
    String cacheKey = req.getMetricCode() + ":" + req.getDimHash();
    return hotCache.get(cacheKey, key -> {
      // Metric must come from registry, no caller-defined grain
      MetricDef def = metricRegistry.get(req.getMetricCode());
      accessControl.checkAndMask(def, req);
      EngineExecutor executor = router.get(def.getScene());
      if (executor == null) throw new IllegalStateException("No engine configured");
      // Query plan includes partition pruning, predicate pushdown, etc.
      return executor.execute(def, req);
    });
  }
}

Expose unified metric API:

@RestController @RequestMapping("/api/metric")
public class MetricServiceController {
  private final MetricMetadataService metadataService;
  private final AccessControl accessControl;
  private final QueryPlanBuilder planBuilder;
  private final QueryExecutor executor;

  @GetMapping("/{metricCode}")
  public MetricResult query(@PathVariable String metricCode, @RequestParam Map<String, Object> dims) {
    MetricMeta meta = metadataService.getByCode(metricCode);
    accessControl.checkAndMask(meta, dims);
    QueryPlan plan = planBuilder.build(meta, dims);
    return executor.execute(plan);
  }
}

Production needs param whitelist, timeout, rate limit, circuit breaker, audit, cache consistency. Especially never concatenate caller SQL directly, or unified gateway becomes unified "injection entry".

Platform code's real value isn't Spring annotations, but "metric grain defined once, permissions & masking upfront, multi-engine transparent to business".

9. Engineering Pits That Really Separate Teams

Concepts are entry-level; post-launch pain comes from these details.

Small file avalanche : High-frequency streaming writes, too-short checkpoints, too many partitions → Write-side micro-batching; target file 256-512MB; Iceberg rewrite_data_files; Hudi Clustering/Compaction; Paimon background compaction.

Metadata bloat : Snapshots & manifests accumulate long-term → expire_snapshots, rewrite_manifests, remove_orphan_files; configure TTL & retention count.

Concurrent write conflicts : Multiple jobs modifying same table/partition → Optimistic concurrency + retry; partition/bucket isolation; serialize write entry if needed.

CDC loss/duplication/out-of-order : Full/incremental switch, task recovery, duplicate messages → Flink Checkpoint + two-phase commit; PK upsert idempotency; precombine /event-time version control.

Stream/batch grain inconsistency : Two codebases, different time semantics & dedup keys → DWS unified definition; unify PK, event time, watermark; T+1 auto-reconciliation.

Schema changes break downstream : Upstream adds/changes columns or business meaning → Field ID evolution; compatible changes canary; breaking changes new column, dual-write, backfill.

Slow lake queries : Full scans, missing stats & clustering → Partition pruning, predicate pushdown, Z-Order, min/max, Bloom Filter, materialized views, caching.

Data skew : Big-promo hot users, streamers, merchants → Salting, two-phase aggregation, Flink local-global aggregation.

Big-promo write backpressure : Traffic spike, checkpoint blocking, partition write concentration → write.distribution-mode=hash; proper scaling; unaligned checkpoint if needed; monitor backpressure & commit latency.

Data swamp : No catalog, owner, lineage, quality, lifecycle → Register on ingestion; Atlas/DataHub; permissions, quality, TTL built together.

Permissions & compliance : Multi-engine permission fragmentation, PII exposed → Ranger/Sentry, Catalog unified auth, column-level masking, dynamic masking, audit.

Cost runaway : Cold data on high-perf media, unlimited snapshot retention → Hot/cold tiering, object storage IA/Archive, TTL, elastic compute.

Platform high concurrency : Big-promo hot metrics hammering OLAP repeatedly → Caffeine/Redis cache, Sentinel rate limit, read-write split, circuit breaker, capacity isolation.

Platform built for building's sake : Assets unused, business onboarding slow → Domain-driven build, strong-demand pilot first; measure by reuse rate, onboarding time, SLA.

9.1 Small File Governance: "Source Control + Background Merge"

Iceberg common ops trio:

CALL lake.system.rewrite_data_files(
  table => 'db.user_behavior',
  options => map('target-file-size-bytes', '268435456', 'min-input-files', '5')
);

CALL lake.system.expire_snapshots(
  table => 'db.user_behavior',
  older_than => TIMESTAMP '2026-10-20 00:00:00',
  retain_last => 10
);

CALL lake.system.rewrite_manifests('db.user_behavior');

Write side can also set: 'write.target-file-size-bytes' = '268435456' Flink Checkpoint not recommended to blindly shrink to seconds for low latency. High-frequency commits generate many files/snapshots; vivo practice even suggests ≥5 min checkpoint intervals for some scenarios. But not universal: real-time business must balance SLA, recovery time, failure semantics — don't drag end-to-end latency just for pretty files.

Compaction typical trigger: every 10-30 min, every N delta commits, or file count threshold. It reads old files, writes new, commits new snapshot; old snapshots readable until commit; real risk is compaction contending CPU/IO with online reads/writes.

9.2 Why Real-Time & Offline Always Differ by That 2%?

Common gaps hide in three places:

Meaning differs : real-time calculates order amount, offline conveniently uses payment amount.

Time differs : one uses event time, other processing time.

Dedup differs : real-time relies on state dedup, offline uses ROW_NUMBER(), PK and out-of-order strategy inconsistent.

Fix in three layers:

Grain layer: DWS or metric center defines once.

Tech layer: unify PK, idempotent upsert, event time, watermark.

Monitor layer: T+1 auto-reconcile, alert & assign owner when diff exceeds threshold.

Lakehouse reduces duplicate storage & pipelines, but won't auto-unify business grain. If real-time & offline SQL write to same table but definitions still differ, numbers still won't match.

10. Completing Answers in Interviews or Tech Reviews

If only 30 seconds:

Database targets OLTP, ensures high-concurrency transaction consistency; warehouse targets OLAP, unifies historical analysis grain via layering & dimensional modeling; data lake uses low-cost storage for structured/semi/unstructured raw data; data platform isn't storage but OneData/OneID/OneService for asset reuse; lakehouse leverages Iceberg/Hudi/Paimon open table formats to bring ACID, upsert, schema evolution, time travel on lake storage, reducing lake↔warehouse copies. Five layers collaborate, not simply replace.

If follow-ups, use cheat sheet:

Will data lake replace warehouse? No. Lake excels full-volume flexible storage; warehouse excels modeling, grain, high-perf analysis; lakehouse fuses them.

Will lakehouse replace ClickHouse/Doris? Not necessarily. High-SLA sub-second core reports may still need dedicated OLAP acceleration.

Is data platform a buzzword? Unified metric, ID, service are real needs; easy to break is org shape detached from business, over-centralized.

Iceberg vs Paimon? Multi-engine & large analytics → Iceberg; Flink real-time PK tables & changelog → Paimon.

Why not Lambda anymore? Two codebases, grains, storage too costly; lakehouse narrows diff to compute layer.

How time travel works? Immutable data files + metadata snapshots; querying old version = reading file set pointed by old snapshot.

Small file merge frequency? Trigger by file count, commit count, latency, resource watermark — no universal fixed cycle.

Platform vs warehouse relation? Warehouse leans storage & modeling; platform leans asset, service, governance; platform can sit on warehouse or lakehouse.

Finally, don't just drop tech terms — add your project constraints for credibility: daily data volume, table count, query P99, end-to-end freshness, CDC peak, compaction cycle, reconciliation diff rate, and why this selection.

Closing

One sentence wraps it:

Database runs transactions, warehouse runs grain, lake runs raw, platform runs reuse, lakehouse lets low-cost data be transactionally & standardly managed.

Landing bottom-up: first get MySQL + CDC + table format base pipeline running, then build ODS/DWD/DWS layers & quality system, finally sink truly reused metrics/tags into platform services. Don't chase "full suite" upfront, or you'll pay for a more complex reporting platform.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

data warehousePaimondata lakeIcebergdata architecturelakehouseCDCHudi
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.