Building Real-Time Dashboards from Kafka Data with SQL

Introduction

You have millions of events flowing through Kafka every minute: orders, clicks, sensor readings, log entries. Somewhere downstream, a product manager refreshes a dashboard and sees data from 15 minutes ago. That gap between event time and insight time is where businesses lose money, miss outages, and frustrate users.

The traditional approach to dashboards involves batch ETL pipelines that extract data from Kafka, load it into a data warehouse, and then connect a BI tool. This works for historical analysis, but it falls short when you need to react to what is happening right now. Fraud detection, inventory monitoring, live sales tracking - these use cases demand dashboards that update in seconds, not minutes or hours.

This guide walks you through building real-time dashboards from Kafka data using RisingWave, a streaming database that lets you write standard SQL to process Kafka streams continuously. You will connect Kafka as a source, build incrementally maintained materialized views for your dashboard metrics, and wire everything to Grafana for visualization. No Java code, no complex DAGs, just SQL.

Architecture: Kafka to Dashboard in Three Layers

Before writing any SQL, it helps to understand the data flow. The architecture has three layers, each with a clear responsibility.

graph LR
    A[Kafka Topics] -->|Stream ingestion| B[RisingWave]
    B -->|Materialized Views| C[Grafana Dashboards]
    style A fill:#231F20,stroke:#231F20,color:#fff
    style B fill:#1A6AFF,stroke:#1A6AFF,color:#fff
    style C fill:#F46800,stroke:#F46800,color:#fff

Layer 1: Kafka (event source)

Kafka holds your raw event streams. Producers write JSON, Avro, or Protobuf messages to topics. In this tutorial, we use an orders topic containing e-commerce order events with fields like order_id, product_name, quantity, unit_price, and region.

Layer 2: RisingWave (stream processing)

RisingWave connects to Kafka as a source and processes events using SQL. A materialized view is a precomputed query result that RisingWave maintains incrementally - whenever a new Kafka message arrives, only the affected rows in the materialized view update. This is fundamentally different from batch recomputation. You get fresh results without re-scanning the entire dataset.

Layer 3: Grafana (visualization)

Grafana connects to RisingWave through the PostgreSQL protocol (RisingWave is PostgreSQL-compatible). Each Grafana panel queries a materialized view, and because those views are already precomputed, queries return in milliseconds regardless of the underlying data volume.

This architecture eliminates the batch ETL layer entirely. Data flows from Kafka through RisingWave to Grafana with sub-second latency.

Step 1: Ingest Kafka Events into RisingWave

The first step is telling RisingWave where to find your Kafka data. You do this with a CREATE SOURCE or CREATE TABLE statement.

CREATE SOURCE vs CREATE TABLE

RisingWave offers two ways to ingest from Kafka:

For dashboards, CREATE SOURCE is usually sufficient since your materialized views handle all aggregation. However, if you need to run ad-hoc queries against raw events, use CREATE TABLE.

Connecting to Kafka

Here is how to create a source that reads from a Kafka topic containing JSON-encoded order events:

CREATE SOURCE orders_source (
    order_id VARCHAR,
    customer_id VARCHAR,
    product_name VARCHAR,
    quantity INT,
    unit_price NUMERIC,
    region VARCHAR,
    order_status VARCHAR,
    order_timestamp TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'orders',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

A few things to note:

• connector = 'kafka' tells RisingWave to use the Kafka connector.
• topic = 'orders' specifies which Kafka topic to consume.
• properties.bootstrap.server points to your Kafka broker(s). For multi-broker clusters, separate addresses with commas.
• scan.startup.mode = 'earliest' tells RisingWave to read from the beginning of the topic. Use 'latest' if you only want new messages.
• FORMAT PLAIN ENCODE JSON declares that messages are plain (not upsert or CDC) and encoded as JSON.

RisingWave also supports Avro, Protobuf, and CSV encodings. For Avro with Schema Registry, you would add schema.registry parameters. Check the Kafka source documentation for the full set of configuration options.

Extracting Kafka Metadata

Sometimes your dashboard needs Kafka-level metadata such as the partition, offset, or message timestamp. RisingWave lets you include these as columns:

CREATE SOURCE orders_source (
    order_id VARCHAR,
    customer_id VARCHAR,
    product_name VARCHAR,
    quantity INT,
    unit_price NUMERIC,
    region VARCHAR,
    order_status VARCHAR,
    order_timestamp TIMESTAMPTZ
) INCLUDE partition AS kafka_partition
  INCLUDE offset AS kafka_offset
  INCLUDE timestamp AS kafka_timestamp
WITH (
    connector = 'kafka',
    topic = 'orders',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

The INCLUDE clauses add kafka_partition, kafka_offset, and kafka_timestamp as queryable columns - useful for monitoring consumer lag or debugging data delivery issues.

Step 2: Build Materialized Views for Dashboard Metrics

With Kafka events flowing into RisingWave, you can define materialized views that continuously compute the metrics your dashboard needs. Each materialized view is a standing SQL query that RisingWave keeps up to date as new events arrive.

Revenue per Minute (Time-Windowed Aggregation)

For a time-series chart showing revenue trends, use RisingWave's TUMBLE window function to bucket events into fixed time intervals:

CREATE MATERIALIZED VIEW mv_revenue_per_minute AS
SELECT
    window_start,
    window_end,
    COUNT(*) AS order_count,
    SUM(quantity * unit_price) AS total_revenue
FROM TUMBLE(orders, order_timestamp, INTERVAL '1 MINUTE')
GROUP BY window_start, window_end;

This groups orders into one-minute windows and computes the count and total revenue for each window. Querying this view returns results instantly:

       window_start        |        window_end         | order_count | total_revenue
---------------------------+---------------------------+-------------+--------------
 2026-04-01 10:01:00+00:00 | 2026-04-01 10:02:00+00:00 |           2 |        209.97
 2026-04-01 10:02:00+00:00 | 2026-04-01 10:03:00+00:00 |           3 |        409.95
 2026-04-01 10:03:00+00:00 | 2026-04-01 10:04:00+00:00 |           2 |        129.97
 2026-04-01 10:04:00+00:00 | 2026-04-01 10:05:00+00:00 |           2 |        339.97
 2026-04-01 10:05:00+00:00 | 2026-04-01 10:06:00+00:00 |           1 |         79.98

The TUMBLE function is one of several time window functions RisingWave supports. For overlapping windows (e.g., a 5-minute window that slides every 1 minute), use HOP instead.

Top Products by Revenue

For a leaderboard panel showing which products generate the most revenue:

CREATE MATERIALIZED VIEW mv_top_products AS
SELECT
    product_name,
    SUM(quantity) AS total_units_sold,
    SUM(quantity * unit_price) AS total_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY product_name;

Query result:

    product_name     | total_units_sold | total_revenue | order_count
---------------------+------------------+---------------+------------
 Wireless Headphones |                6 |        479.94 |           3
 Mechanical Keyboard |                3 |        389.97 |           2
 USB-C Hub           |                3 |        149.97 |           2
 Monitor Stand       |                3 |        119.97 |           2
 Laptop Sleeve       |                1 |         29.99 |           1

Order Status Breakdown

For a pie chart or gauge showing the distribution of order statuses:

CREATE MATERIALIZED VIEW mv_order_status_summary AS
SELECT
    order_status,
    COUNT(*) AS order_count,
    SUM(quantity * unit_price) AS total_value
FROM orders
GROUP BY order_status;

Query result:

 order_status | order_count | total_value
--------------+-------------+------------
 completed    |           7 |      739.88
 pending      |           2 |      389.97
 cancelled    |           1 |       39.99

Revenue by Region

For a geographic breakdown or bar chart of regional performance:

CREATE MATERIALIZED VIEW mv_orders_per_region AS
SELECT
    region,
    COUNT(*) AS order_count,
    SUM(quantity * unit_price) AS total_revenue,
    AVG(quantity * unit_price) AS avg_order_value
FROM orders
GROUP BY region;

Query result:

  region  | order_count | total_revenue | avg_order_value
----------+-------------+---------------+----------------
 us-east  |           4 |        509.92 |          127.48
 ap-south |           2 |        339.97 |         169.985
 eu-west  |           2 |        229.97 |         114.985
 us-west  |           2 |         89.98 |           44.99

All four materialized views are maintained incrementally. When a new order arrives in Kafka, RisingWave updates only the affected rows in each view. There is no periodic refresh or scheduled recomputation.

Step 3: Connect Grafana to RisingWave

With your materialized views in place, the final step is connecting Grafana to visualize them.

Add RisingWave as a Data Source

RisingWave speaks the PostgreSQL wire protocol, so Grafana treats it as a PostgreSQL database.

1. In Grafana, go to Connections > Data sources > Add data source.
2. Select PostgreSQL.
3. Enter the connection details:

4. Click Save & test to confirm the connection.

For production deployments, create a dedicated read-only user instead of using root:

CREATE USER grafana_reader WITH PASSWORD 'a_strong_password';
GRANT SELECT ON mv_revenue_per_minute TO grafana_reader;
GRANT SELECT ON mv_top_products TO grafana_reader;
GRANT SELECT ON mv_order_status_summary TO grafana_reader;
GRANT SELECT ON mv_orders_per_region TO grafana_reader;

Build Dashboard Panels

Each panel in your Grafana dashboard maps to a SQL query against a materialized view.

Time-series panel (revenue per minute):

SELECT
    window_start AS time,
    total_revenue,
    order_count
FROM mv_revenue_per_minute
ORDER BY window_start;

Set the visualization to Time series and map time as the time column.

Bar chart panel (top products):

SELECT
    product_name,
    total_revenue
FROM mv_top_products
ORDER BY total_revenue DESC
LIMIT 10;

Pie chart panel (order status):

SELECT
    order_status,
    order_count
FROM mv_order_status_summary;

Stat panel (total revenue):

SELECT SUM(total_revenue) AS revenue
FROM mv_revenue_per_minute;

Set Refresh Intervals

Grafana supports auto-refresh intervals. Because your materialized views are always current, set the dashboard refresh to 5 seconds or 10 seconds for a near-real-time experience. Unlike batch systems where more frequent refreshes waste compute, querying RisingWave materialized views is cheap since results are precomputed.

Why This Approach Beats Traditional Alternatives

Building dashboards this way with RisingWave provides several advantages over alternative architectures.

Compared to Batch ETL + Data Warehouse

Traditional pipelines using tools like Airflow + dbt + BigQuery or Snowflake introduce minutes to hours of latency. RisingWave eliminates the ETL scheduler entirely. Data is available in your materialized views within seconds of arriving in Kafka.

Compared to ksqlDB

ksqlDB processes Kafka streams with SQL, but it stores results back in Kafka topics, not in a queryable database. You still need another component to serve dashboards. RisingWave stores materialized view results in its own storage layer and serves queries directly over the PostgreSQL protocol.

Compared to Flink + Database

Apache Flink is a powerful stream processor, but building dashboards requires deploying Flink, writing transformations (SQL or Java), and sinking results to an external database like PostgreSQL. RisingWave collapses this into a single system: it is both the stream processor and the serving database.

What Are Real-Time Dashboards with Kafka and SQL?

Real-time dashboards with Kafka and SQL are monitoring interfaces that visualize continuously updating metrics derived from Apache Kafka event streams using SQL queries. Instead of running batch jobs on a schedule, a streaming SQL engine like RisingWave processes each Kafka message as it arrives and updates precomputed results called materialized views. Dashboard tools such as Grafana query these views to display metrics that reflect the current state of your data within seconds of the events occurring.

How Does RisingWave Process Kafka Data Differently from Batch Systems?

RisingWave processes Kafka data incrementally rather than in scheduled batches. When a new message arrives on a Kafka topic, RisingWave evaluates only the change and updates the affected rows in its materialized views. This incremental processing model means a single new event does not trigger a full recomputation of the entire dataset. The result is consistent sub-second freshness for dashboard queries, regardless of whether the underlying Kafka topic contains thousands or billions of messages. You can learn more about this approach in the RisingWave architecture documentation.

Can I Use RisingWave with an Existing Kafka Cluster?

Yes. RisingWave connects to any standard Apache Kafka cluster as well as Kafka-compatible systems like Redpanda, Amazon MSK, Confluent Cloud, and WarpStream. You do not need to modify your existing Kafka setup. RisingWave acts as a Kafka consumer, reading from the topics you specify. It supports JSON, Avro (with Schema Registry), Protobuf, and CSV encodings. For secured clusters, RisingWave supports SSL/TLS, SASL/PLAIN, and SASL/SCRAM authentication. See the full list of supported Kafka configurations in the documentation.

When Should I Use CREATE SOURCE vs CREATE TABLE for Kafka Ingestion?

Use CREATE SOURCE when you only need Kafka data inside materialized views and do not need to query raw events directly. The source acts as a streaming input without storing the raw data in RisingWave, saving storage. Use CREATE TABLE with a Kafka connector when you need to run ad-hoc queries on raw events, require primary key-based deduplication (upsert semantics), or want to join streaming data with other tables. For most dashboard use cases, CREATE SOURCE is the right choice because the materialized views already contain the aggregated results you need.

Conclusion

Building real-time dashboards from Kafka data does not require complex infrastructure or specialized programming languages. With RisingWave, the entire pipeline from Kafka ingestion to dashboard-ready metrics is expressed in standard SQL.

Here is what we covered:

• Kafka source setup: Use CREATE SOURCE or CREATE TABLE to connect RisingWave to your Kafka topics with a single SQL statement.
• Materialized views: Define standing queries that RisingWave maintains incrementally - revenue per minute, top products, order status breakdowns, and regional metrics.
• Grafana integration: Connect Grafana to RisingWave through the PostgreSQL protocol and build panels that query precomputed materialized views for millisecond response times.
• Architecture simplicity: Replace multi-component batch ETL pipelines with a single streaming database that handles both processing and serving.

The key insight is that materialized views shift computation from query time to ingestion time. Your dashboard queries become simple SELECTs against precomputed results, making them fast regardless of data volume.

Ready to build your own real-time dashboards? Try RisingWave Cloud free - no credit card required. Sign up here.

Join our Slack community to ask questions and connect with other stream processing developers.