Building a Real-Time Data Warehouse: End-to-End Pipeline from Kafka to BI

1. Define What Deserves Real-Time Before Ingestion

The first mistake in real-time data warehouse projects is demanding real-time for all data. Different business domains have vastly different latency requirements: payment risk control may need seconds, inventory alerts within one minute, store dashboards every three minutes, while monthly profit and customer segmentation need no real-time computation at all.

Before building, assign a latency SLA to each data domain. For example:

Payment status: within 30 seconds

Inventory changes: within 1 minute

Order aggregation: within 3 minutes

Operational metrics: within 5 minutes

Financial settlement: T+1

These SLAs directly determine whether to use real-time streams, near-real-time micro-batches, or traditional batch processing. Mature platforms typically run all three layers in parallel.

2. Data Ingestion: Capture Changes, Not Snapshots

Sources include business databases (orders, inventory, contracts) via CDC, event-driven systems writing directly to Kafka, and IoT/log data via MQTT. The key principle: real-time ingestion should continuously capture "what just changed" rather than repeatedly querying "what exists now." For a 500-million-row order table, scanning by update timestamp every minute burdens the source database; CDC captures only the transition (e.g., order 10001 from unpaid to paid) with far lower system cost.

Enterprises often face heterogeneous sources (ERP, CRM, MES, Kafka, APIs, files). Writing separate collectors for each creates a maintenance nightmare. A unified data integration layer (e.g., FineDataLink 5.0) can connect diverse sources, standardize incremental capture, and provide centralized monitoring and schema evolution.

3. Kafka Is a Real-Time Data Bus, Not the Warehouse

Kafka sits at the center of many architectures, but it solves buffering and decoupling, not analytics. Producers write events; multiple consumers (risk, marketing, dashboards, warehouse, recommendation) read independently.

Critical design decisions:

Topic Design

Organize topics by business domain + event type (e.g., order_event, payment_event, refund_event, inventory_event) rather than dumping everything into a single giant topic.

Partition Key for Local Ordering

For order flows, partition by order_id so that payment and refund events for the same order stay in the same partition, preserving per-key order.

Store Business Events, Not Aggregated Results

Kafka should retain raw events like "order 10001 paid 299 yuan at 10:23" rather than pre-aggregated facts like "East China sales increased by 299." Raw events can be reused by risk, recommendation, marketing, and the warehouse; aggregated facts are bound to a single analytical view.

4. Stream Processing: The Hard Parts Are Time, State, and Consistency

Demo queries like SUM(order_amount) GROUP BY region hide production complexity. A single order may traverse create → pay → modify → refund → cancel refund. Naively summing each change double-counts.

Duplicate Handling (Idempotency)

At-least-once delivery means network glitches, restarts, or retries can reprocess messages. Every event must carry a business unique key; processing the same event twice must not alter the result.

Out-of-Order and Late Events

Event time (when the business event occurred) often differs from processing time (when the system sees it). A payment at 10:01 may arrive at 10:03, while a modification at 10:02 arrives first. Minute/hour-level metrics will diverge from offline results unless event-time windows and watermarks are correctly handled.

Dimension Joins with Slowly Changing Dimensions

Fact streams carry only keys (e.g., product_id). Analysis needs enriched attributes (product, brand, category, division, region). Dimensions themselves change: a product may move from category A to B in March. Should January orders show A or B? This requires explicit design of historical vs. current dimension snapshots.

Real-Time Aggregation

Operational dashboards need metrics like today's GMV, real-time order count, regional GMV, product sales, yield rate. Data flows through filter, transform, join, aggregate. Fixed, reusable logic (field mapping, conditional filtering, dimension lookup, group-by) can run in a lightweight integration layer (e.g., FineDataLink 5.0), while complex stateful windowing, out-of-order handling, and large-scale stream computing go to a dedicated engine like Flink. This splits the processing layer by complexity.

5. Layered Modeling: Don't Connect Kafka Directly to Reports

Skipping layers (Kafka → metric table → BI) works initially but causes duplication: sales dashboard computes GMV, operations dashboard recomputes it, finance recomputes again — three divergent definitions.

Adopt the classic ODS/DWD/DWS/ADS layering:

ODS: Preserve Raw Changes

Store every original event (create, update, pay, refund) intact. This "preserves the scene" for root-cause analysis: was the source wrong or the processing?

DWD: Standardize Business Facts

Unify primary keys, fields, codes, statuses, and business meanings across sources. Multiple order channels converge into single fact tables: order_fact, payment_fact, refund_fact. This layer establishes the single version of truth for downstream consumers.

DWS: Materialize Common Aggregates

Pre-compute reusable summaries: regional hourly sales, customer lifetime value, daily product sales, hourly equipment output. If multiple applications need them, compute once here to avoid repeated recomputation from DWD.

ADS: Serve Specific Business Scenarios

ADS powers executive cockpits, real-time large screens, risk alerts, and ad-hoc analysis. Principle: push common logic down, keep personalized logic up.

To avoid each downstream system re-pulling raw data, use a data distribution layer (e.g., FineDataLink 5.0) to fan out standardized DWD/DWS data to multiple targets. This ensures all consumers derive from the same curated dataset, so upstream schema or rule changes only need fixing in one place.

6. Metric Layer: Define Before BI

The final deliverable is not tables but metrics. A number like "Today's GMV: 12.8M" hides rules: which order statuses count? Are unpaid orders included? How to handle partial refunds? Are coupons part of GMV? Cross-day refunds? Test order exclusion?

Standardize definitions upstream: Net Payment = Valid Payments - Valid Refunds. First define "valid payment" and "valid refund" once, then derive metrics by date, region, channel, product. Organize metrics as atomic (payment amount), derived (East China today payment), and compound (AOV = payment amount / paying customers). Moving definition left prevents analysts from re-interpreting raw tables in every report.

Use the integration layer to pre-join orders, refunds, products, and compute common aggregates before BI consumes them. Then BI only handles filtering, drill-down, comparison, and visualization.

7. Production Monitoring: Five Must-Watch Signals

The danger is not failed jobs but silently wrong data. Monitor continuously:

End-to-end latency: Business event → BI visibility, not just Kafka lag. A 5-second consumer lag is meaningless if aggregation and BI refresh add 20 minutes.

Consumer lag: When production outpaces consumption, real-time data silently degrades to 10-minute-old data. Lag measures whether the pipeline keeps up.

Data volume reconciliation: Build a full chain: source changes → message count → consumed count → processed count → written count → anomaly count. If source emits 1M changes but only 980K land in the warehouse, the missing 20K must be explained.

Duplicates and anomalies: Track business key duplication rate, exception record count, retry counts. Job success ≠ data correctness.

Real-time vs. offline consistency: Compare real-time GMV (10M) with next-day offline recalculation (9.5M). Don't dismiss gaps as "expected real-time error." Diagnose: late events? duplicates? refund state changes? metric definition drift?

A mature real-time warehouse isn't error-free; it enables rapid isolation of the faulty layer, impact scope, and recovery point.

Conclusion

Strip away the technology labels (CDC, Kafka, Flink, warehouse, metrics, BI) and the core problem is simple: continuously transform business changes into trustworthy, unified, directly usable data. The complete pipeline: business changes → CDC/Kafka ingestion → clean, deduplicate, join, aggregate → ODS/DWD/DWS/ADS layering → unified metric computation → BI analysis → monitor latency, lag, reconciliation, anomalies.

When building, focus not on achieving 1-second latency but on: which data truly needs real-time? Can the entire chain be traced and recovered after failures? Will the same metric mean the same thing across every dashboard and system? Only when these three questions are answered do the components become a real-time data system that serves business decisions.