How to Build a Real‑Time Data Warehouse with Flink SQL: Architecture, Implementation, and Governance

Document Overview

The first half of this tutorial was published previously; the second half starts from Chapter 5, focusing on the core of real‑time data warehouse construction.

5.1 Early Real‑Time Computing

Early implementations processed each data source separately with Flink, leading to duplicated cleaning, filtering, and enrichment steps and causing code coupling, resource waste, and lack of monitoring.

5.2 Real‑Time Warehouse Architecture

The architecture mirrors the offline warehouse with layers: data source, detailed layer, aggregation layer, and optional application layer. Real‑time layers are similar to offline ones but contain fewer layers and store data in Kafka, HBase, MySQL, or other KV stores instead of Hive tables.

5.3 Lambda and Kappa Architectures

Lambda adds a batch path to the real‑time pipeline, resulting in duplicated logic and resources. Kappa removes the batch path, simplifying deployment but limiting use cases because the same table must be stored in both streaming and batch stores.

5.4 Stream‑Batch Integration

Combines Flink SQL streaming with Iceberg tables for ACID guarantees, reducing latency and supporting both batch scans and real‑time updates.

6. Building a Real‑Time Warehouse with Flink SQL (0→1)

A demo uses an e‑commerce order model. The pipeline includes:

Canal to capture MySQL binlog and write to Kafka.

Flink SQL to clean and join data, writing detailed wide tables back to Kafka.

Dimension tables stored in MySQL (or HBase in production).

Aggregated tables (ADS) created by joining detailed streams with dimensions.

Key DDL statements create source tables (ODS), dimension tables, detailed tables (DWD), and ADS tables. Sample SQL inserts populate ads_province_index and ads_sku_index using Flink’s temporal table joins.

7. Data Governance

Effective governance requires a systematic framework covering asset classification, architecture, metadata, security, and lifecycle management. Data assets are graded (L1‑L4, Lx) based on business impact, and governance processes include:

Asset‑level classification and propagation through upstream/downstream dependencies.

Online system change notification via release platforms or direct communication for high‑grade assets.

Offline code review rules (naming, null handling, performance checks) and task monitoring.

8. Data Quality Construction

Quality is measured across six dimensions: completeness, conformity, consistency, accuracy, uniqueness, and timeliness. Monitoring rules (strong vs. weak) are applied based on asset grade, using tools such as DataWorks DQC to enforce thresholds on row counts, null ratios, and value distributions.

9. Naming Conventions

Tables follow a structured pattern:

{layer}_{department}_{business_domain}_{subject}_{description}_{frequency}

. Prefixes indicate layer (ods, dwd, dws, ads, dim). Suffixes denote data frequency (d‑daily, i‑incremental, f‑full, w‑weekly, l‑link). Intermediate tables use mid_ prefixes, temporary tables use tmp_, and dimension tables use dim_. Metric names use lowercase words separated by underscores, avoid SQL keywords, and include suffixes like _cnt for counts or _price for monetary values.

10. Visualization

Throughout the article, architecture diagrams and example screenshots illustrate the layered design, data flow, and monitoring dashboards.

Conclusion

By following the layered architecture, Flink SQL implementation, and rigorous governance and quality practices, teams can build scalable, low‑latency real‑time data warehouses that support OLAP analytics, dashboards, and alerting while maintaining data integrity and operational efficiency.