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

The sharing session, presented by JD.com BI engineer Yang Shang'ang, introduces a practical case of using Flink SQL for real‑time dimension modeling, organized into problem description, difficulty analysis, solution design, and future planning.

Problem 1 : Real‑time multi‑stream full‑join where one stream must be joined with the full historical dataset. Example SQL: select * from A full join B on A.name = B.name;

Problem 2 : Real‑time stream full‑group aggregation, such as row_number and min calculations on continuously changing groups. Example SQL snippets: 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 name;

The challenges include acquiring historical data for streams, managing large state in memory or external KV stores (e.g., HBase, Redis), ensuring correct event ordering, and achieving acceptable performance by reducing I/O and increasing concurrency.

The proposed solution adopts a component‑based design called RDM Building, consisting of three layers: Components Config, RDDM Component Builder, and RDDM Component Parse. User‑defined configurations generate component objects that are translated into Flink SQL operators. The HisRows Component fetches historical data from KV storage, merges it with incoming streams (A, B, C), and produces enriched streams (A_all, B_all, C_all) for downstream SQL computation.

Key processing steps: keyBy on join keys, window aggregation to coalesce multiple binlog messages, conversion to unified tuple format (including insert and delete records), union of streams, flatMap to load historical data from cache or KV store, and final SQL execution on the enriched streams.

Future planning includes building a visual front‑end for component configuration, extending support to other real‑time engines such as Spark Streaming, and broadening KV storage options beyond HBase and Redis.