How to Create a Real-Time Dashboard from Kafka Events

Every team running Kafka eventually wants the same thing: a dashboard that shows what's happening right now. Orders per minute. Error rates. Revenue accumulating in real time. The events are already flowing through your topics — you just need to get them onto a screen.

The problem is that dashboards speak SQL, and Kafka speaks event streams. You can't point a chart widget at a Kafka topic. You need something in between that turns a firehose of JSON events into queryable rows.

The architecture

Here's the pattern that actually works in production:

Kafka topic --> Consumer --> Sink table (Postgres) --> SQL query --> Dashboard widget

Your Kafka consumer reads events from a topic, transforms them if needed, and writes rows into a Postgres table. Your dashboard runs SQL queries against that table on a schedule. The sink table is the bridge between streaming and batch — it absorbs writes at event speed and serves reads at query speed.

This isn't a novel architecture. It's what Confluent calls the "Kafka to RDBMS" pattern, what most teams arrive at after trying (and abandoning) direct Kafka-to-dashboard connectors. The sink table is boring. It's also the part that never breaks at 3 AM.

The sink table pattern

Your sink table mirrors the shape of your Kafka events, plus metadata columns for operational sanity:

CREATE TABLE order_events (
  id            BIGSERIAL PRIMARY KEY,
  event_id      UUID NOT NULL UNIQUE,
  event_type    TEXT NOT NULL,
  order_id      TEXT NOT NULL,
  customer_id   TEXT,
  amount_cents  INTEGER,
  currency      TEXT DEFAULT 'USD',
  status        TEXT,
  kafka_topic   TEXT,
  kafka_offset  BIGINT,
  event_ts      TIMESTAMPTZ NOT NULL,
  ingested_at   TIMESTAMPTZ DEFAULT now()
);
 
CREATE INDEX idx_order_events_ts ON order_events (event_ts);
CREATE INDEX idx_order_events_type ON order_events (event_type);

Two columns matter more than they look. event_id with a unique constraint gives you idempotent writes — your consumer can crash and replay without duplicating rows. ingested_at vs event_ts tells you the lag between when something happened and when your dashboard knows about it.

Don't over-normalize this table. The temptation is to build a star schema with dimension tables for customers, products, and currencies. Resist it. Your dashboard queries need to be fast and self-contained. Denormalize into the sink table and let the analytics be simple.

Setting up a Kafka trigger in Fastero

Fastero's triggers can fire on Kafka topic events directly. You configure a trigger that listens to a topic, and when events arrive, it can execute a SQL query, run a workflow, or push data into a connected database.

The setup is: connect your Kafka cluster (bootstrap servers + auth), pick the topic, define the trigger action. For the sink table pattern, the trigger action inserts each event into your Postgres sink table. You don't write a consumer — the trigger IS the consumer.

This matters because consumer management is the part of Kafka that eats your weekends. Consumer group rebalancing, offset commits, deserialization errors, backpressure handling — all of that becomes Fastero's problem. Your job is to define the sink table schema and write the dashboard queries.

Aggregation queries: counts, sums, and rates

Once events land in the sink table, you're back in familiar SQL territory. Here are the queries that power most real-time ops dashboards.

Revenue counter (last 24 hours):

SELECT
  SUM(amount_cents) / 100.0 AS revenue_24h,
  COUNT(*) AS order_count,
  ROUND(AVG(amount_cents) / 100.0, 2) AS avg_order_value
FROM order_events
WHERE event_type = 'order.completed'
  AND event_ts > now() - INTERVAL '24 hours'

This becomes a KPI widget on your dashboard — three numbers, refreshed every minute. Wire it up in Fastero by running the query in the SQL editor, clicking "Add to Dashboard," and picking the number widget type.

Orders per minute (last hour), using tumbling windows:

SELECT
  DATE_TRUNC('minute', event_ts) AS minute,
  COUNT(*) AS orders,
  SUM(amount_cents) / 100.0 AS revenue
FROM order_events
WHERE event_type = 'order.completed'
  AND event_ts > now() - INTERVAL '1 hour'
GROUP BY 1
ORDER BY 1

DATE_TRUNC is the poor man's tumbling window, and it works great. Each row represents one minute of activity. Gaps show up as missing rows — which is itself useful information. If you see a 3-minute gap in orders at 2 PM on a Tuesday, something broke.

For hourly windows, swap 'minute' for 'hour' and extend the lookback:

SELECT
  DATE_TRUNC('hour', event_ts) AS hour,
  COUNT(*) AS orders,
  SUM(amount_cents) / 100.0 AS revenue,
  COUNT(DISTINCT customer_id) AS unique_customers,
  ROUND(AVG(amount_cents) / 100.0, 2) AS avg_order_value
FROM order_events
WHERE event_type = 'order.completed'
  AND event_ts > now() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1

This is a line chart. Five series if you want them all, or split it into two widgets — one for volume (orders, unique customers) and one for monetary metrics (revenue, AOV). Mixing scales on a single chart makes everything look flat.

Consumer lag: knowing when you're behind

The gap between event_ts and ingested_at is your consumer lag, measured in your own data. Forget the Kafka consumer group lag metric for a moment — that tells you about offsets. This tells you about time, which is what your dashboard users care about.

SELECT
  DATE_TRUNC('minute', ingested_at) AS minute,
  ROUND(AVG(EXTRACT(EPOCH FROM (ingested_at - event_ts))), 1) AS avg_lag_seconds,
  MAX(EXTRACT(EPOCH FROM (ingested_at - event_ts))) AS max_lag_seconds,
  COUNT(*) AS events_ingested
FROM order_events
WHERE ingested_at > now() - INTERVAL '1 hour'
GROUP BY 1
ORDER BY 1

Put this on its own dashboard widget. When avg_lag_seconds creeps above 30, your "real-time" dashboard is lying — it's showing data that's half a minute old. When it hits 300, you have a consumer that's fallen behind and needs attention.

You can also set a trigger-based alert on this query: notify Slack when average lag exceeds 60 seconds. Now your real-time dashboard monitors itself.

An honest take on "real-time"

Here's the opinion I'll defend: true real-time dashboards are overrated for most businesses. A 5-minute refresh cycle catches 95% of what matters. Daily revenue? You don't need it updating every second. Weekly signups? Hourly is fine.

The exceptions are ops dashboards. Error rates, transaction volume, payment failure spikes, API latency — these need sub-minute freshness because the response time to an incident is measured in minutes, not hours. If your checkout flow breaks at 11:03 AM, finding out at 11:08 is fine. Finding out at noon is expensive.

So before you build the full Kafka-to-sink-table pipeline, ask: would a scheduled SQL dashboard with 5-minute refresh do the job? If yes, skip the Kafka consumer entirely and query your production database (or a read replica) directly. Save the event-stream architecture for the cases where latency actually costs you money.

When you do need it, though, the sink table pattern gives you the best of both worlds: streaming writes from Kafka, familiar SQL reads for your dashboards. No specialized streaming SQL engine, no ksqlDB cluster to manage, no Flink jobs to debug. Just Postgres doing what Postgres does.

Try Fastero free — connect Kafka and Postgres, set up triggers that turn event streams into queryable tables, and build live dashboards from SQL. No credit card required.