Real-time Dimension Modeling with Flink SQL: Problems, Challenges, and Solutions

Introduction: This article shares JD's real-time modeling case using Flink SQL, covering problem, difficulty, solution, and planning.

Problem : Two challenging scenarios are described: (1) real‑time multi‑stream full join, which may need to join one stream with full historical data; (2) real‑time stream full‑group aggregation such as row_number and min calculations. Simple SQL statements are shown:

select * from A full join B on A.name = B.name;

select id, name, val, row_number() over (partition by name order by val) as rn from A;

select name, min(val) from A group by k;

Directly using these SQLs faces issues: missing historical data at the start, large state storage in memory or RocksDB, and performance concerns.

Challenges : (1) Obtaining historical data requires external KV stores (e.g., HBase, Redis) and handling time‑range consistency; (2) Improving performance involves reducing I/O and increasing concurrency via async I/O, parallelism, and caching.

Solution : A component‑based design (RDM Building) is proposed, where users configure dimension‑modeling components that are parsed into Flink operators. Components such as HisRows handle fetching historical data, keyBy, window aggregation, and merging streams (A_all, B_all, C_all) for downstream Flink SQL computation.

Images illustrate the processing flow and component execution.

Planning : Future work includes a visual configuration UI, support for multiple streaming engines (Spark Streaming, etc.), and broader KV store integration beyond HBase and Redis.