Build a Real-Time E-Commerce Pipeline

Stream PostgreSQL changes through Kafka into ClickHouse with live dashboards.

Introduction

30 Second Summary

Every time you place an online order, that transaction needs to travel from the store's database to dashboards where analysts track revenue and trends in real time. This journey from "order placed" to "chart updated" is powered by a data pipeline.

In this project, you will build a complete real-time analytics pipeline that captures e-commerce orders from a database, streams them through a message broker, and displays live charts on a dashboard. You'll use Docker to run the entire stack locally on your machine.

What You'll Build

A Change Data Capture (CDC) pipeline that streams PostgreSQL database changes through Apache Kafka into ClickHouse for analytics, visualized on a live Grafana dashboard.

By the end of this project, you'll have:

  • A Docker Compose stack running 8 containers including PostgreSQL, Kafka, Debezium, ClickHouse, and Grafana.
  • Hands-on experience with Kafka CLI tools, a Python producer, and consumer group inspection.
  • A live Grafana dashboard displaying real-time order volume and revenue charts powered by ClickHouse.
  • Secret Mission: Build pre-aggregated analytics with SummingMergeTree for lightning-fast dashboard queries.

Are there any prerequisites?

You'll need Docker Desktop installed on your machine, along with basic familiarity with SQL and the command line. This stack uses around 3-5 GB of RAM, so a system with at least 8 GB is recommended.

Set Up the Project and Launch the Stack

In the previous step, you installed Python and the Kafka client library. Now it's time to define the infrastructure that powers your pipeline.

Your analytics pipeline needs eight services running together: a transactional database, a message broker, a change-capture engine, an analytical database, a dashboard, and supporting services. You'll define all of them in a single Docker Compose file and bring them up with one command.

In this step, get ready to:
  • Create the docker-compose.yml and init.sql files that define the entire stack.
  • Launch all 8 containers with Docker Compose.
  • Verify that all services are healthy and accessible.
Create the Docker Compose and database seed files

The docker-compose.yml file tells Docker which containers to run and how they connect to each other. You'll define eight services across three logical groups: core infrastructure, data pipeline services, and monitoring tools.

  • Open the ecommerce-pipeline folder in your text editor.
  • Create a new file called docker-compose.yml inside the ecommerce-pipeline folder.
  • Paste the following into docker-compose.yml to define the PostgreSQL database, Zookeeper coordinator, and Kafka broker:
services:
  postgres:
    image: postgres:15
    container_name: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: ecommerce
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
      - "-c"
      - "max_wal_senders=4"
      - "-c"
      - "max_replication_slots=4"
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

  zookeeper:
    image: confluentinc/cp-zookeeper:7.7.8
    container_name: zookeeper
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000
    ports:
      - "2181:2181"

  kafka:
    image: confluentinc/cp-kafka:7.7.8
    container_name: kafka
    depends_on:
      - zookeeper
    ports:
      - "9092:9092"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_LISTENERS: INSIDE://0.0.0.0:29092,OUTSIDE://0.0.0.0:9092
      KAFKA_ADVERTISED_LISTENERS: INSIDE://kafka:29092,OUTSIDE://localhost:9092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: INSIDE:PLAINTEXT,OUTSIDE:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: INSIDE
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1

What does this configuration do?

This first section defines the three foundational services your pipeline needs.

  • postgres runs your e-commerce database. The wal_level=logical setting enables the write-ahead log mode that Debezium needs to capture row-level changes.
  • zookeeper is a coordination service that Kafka depends on for managing broker metadata.
  • kafka has two listeners: INSIDE (port 29092) for container-to-container traffic and OUTSIDE (port 9092) for your Python scripts running on the host machine.
  • Below the kafka service, paste these remaining services and the volumes section:
  kafka-connect:
    image: debezium/connect:3.0
    container_name: kafka-connect
    depends_on:
      - kafka
      - postgres
    ports:
      - "8083:8083"
    environment:
      BOOTSTRAP_SERVERS: kafka:29092
      GROUP_ID: connect-cluster
      CONFIG_STORAGE_TOPIC: connect-configs
      OFFSET_STORAGE_TOPIC: connect-offsets
      STATUS_STORAGE_TOPIC: connect-status
      CONFIG_STORAGE_REPLICATION_FACTOR: 1
      OFFSET_STORAGE_REPLICATION_FACTOR: 1
      STATUS_STORAGE_REPLICATION_FACTOR: 1
      KEY_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      VALUE_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      KEY_CONVERTER_SCHEMAS_ENABLE: "false"
      VALUE_CONVERTER_SCHEMAS_ENABLE: "false"

  clickhouse:
    image: clickhouse/clickhouse-server:24.8
    container_name: clickhouse
    ports:
      - "8123:8123"
      - "9000:9000"
    environment:
      CLICKHOUSE_USER: default
      CLICKHOUSE_PASSWORD: ""
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
    volumes:
      - clickhouse-data:/var/lib/clickhouse
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

  grafana:
    image: grafana/grafana:latest
    container_name: grafana
    ports:
      - "3000:3000"
    environment:
      GF_SECURITY_ADMIN_USER: admin
      GF_SECURITY_ADMIN_PASSWORD: admin
      GF_INSTALL_PLUGINS: grafana-clickhouse-datasource
    volumes:
      - grafana-data:/var/lib/grafana
    depends_on:
      - clickhouse

  kafka-ui:
    image: provectuslabs/kafka-ui:latest
    container_name: kafka-ui
    ports:
      - "8080:8080"
    environment:
      KAFKA_CLUSTERS_0_NAME: local
      KAFKA_CLUSTERS_0_BOOTSTRAPSERVERS: kafka:29092
      KAFKA_CLUSTERS_0_ZOOKEEPER: zookeeper:2181
    depends_on:
      - kafka

volumes:
  postgres-data:
  clickhouse-data:
  grafana-data:

What do these services do?

  • kafka-connect runs Debezium, the CDC engine that reads the PostgreSQL write-ahead log and streams changes to Kafka. Schema wrapping is disabled so the JSON stays simple for ClickHouse to parse.
  • clickhouse is a columnar analytical database (ClickHouse) that can ingest directly from Kafka topics for lightning-fast queries.
  • grafana provides the real-time dashboard. The GF_INSTALL_PLUGINS variable tells Grafana to auto-install the ClickHouse data source plugin on startup.
  • kafka-ui gives you a visual dashboard for inspecting Kafka topics, messages, and consumer groups.
  • Save docker-compose.yml.

Seeing YAML syntax errors later?

YAML is very sensitive to indentation. Every nested level must use exactly 2 spaces. Tabs will cause parse errors.

Double-check that each service name (like postgres:) is indented 2 spaces under services:. Help me fix my docker-compose.yml indentation

✔️ Awesome, I've got everything!

Great. Double check you've saved docker-compose.yml before moving on.

ⓧ I'd like to double check the full code

services:
  postgres:
    image: postgres:15
    container_name: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: ecommerce
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
      - "-c"
      - "max_wal_senders=4"
      - "-c"
      - "max_replication_slots=4"
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

  zookeeper:
    image: confluentinc/cp-zookeeper:7.7.8
    container_name: zookeeper
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000
    ports:
      - "2181:2181"

  kafka:
    image: confluentinc/cp-kafka:7.7.8
    container_name: kafka
    depends_on:
      - zookeeper
    ports:
      - "9092:9092"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_LISTENERS: INSIDE://0.0.0.0:29092,OUTSIDE://0.0.0.0:9092
      KAFKA_ADVERTISED_LISTENERS: INSIDE://kafka:29092,OUTSIDE://localhost:9092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: INSIDE:PLAINTEXT,OUTSIDE:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: INSIDE
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1

  kafka-connect:
    image: debezium/connect:3.0
    container_name: kafka-connect
    depends_on:
      - kafka
      - postgres
    ports:
      - "8083:8083"
    environment:
      BOOTSTRAP_SERVERS: kafka:29092
      GROUP_ID: connect-cluster
      CONFIG_STORAGE_TOPIC: connect-configs
      OFFSET_STORAGE_TOPIC: connect-offsets
      STATUS_STORAGE_TOPIC: connect-status
      CONFIG_STORAGE_REPLICATION_FACTOR: 1
      OFFSET_STORAGE_REPLICATION_FACTOR: 1
      STATUS_STORAGE_REPLICATION_FACTOR: 1
      KEY_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      VALUE_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      KEY_CONVERTER_SCHEMAS_ENABLE: "false"
      VALUE_CONVERTER_SCHEMAS_ENABLE: "false"

  clickhouse:
    image: clickhouse/clickhouse-server:24.8
    container_name: clickhouse
    ports:
      - "8123:8123"
      - "9000:9000"
    environment:
      CLICKHOUSE_USER: default
      CLICKHOUSE_PASSWORD: ""
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
    volumes:
      - clickhouse-data:/var/lib/clickhouse
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

  grafana:
    image: grafana/grafana:latest
    container_name: grafana
    ports:
      - "3000:3000"
    environment:
      GF_SECURITY_ADMIN_USER: admin
      GF_SECURITY_ADMIN_PASSWORD: admin
      GF_INSTALL_PLUGINS: grafana-clickhouse-datasource
    volumes:
      - grafana-data:/var/lib/grafana
    depends_on:
      - clickhouse

  kafka-ui:
    image: provectuslabs/kafka-ui:latest
    container_name: kafka-ui
    ports:
      - "8080:8080"
    environment:
      KAFKA_CLUSTERS_0_NAME: local
      KAFKA_CLUSTERS_0_BOOTSTRAPSERVERS: kafka:29092
      KAFKA_CLUSTERS_0_ZOOKEEPER: zookeeper:2181
    depends_on:
      - kafka

volumes:
  postgres-data:
  clickhouse-data:
  grafana-data:

Now you need a SQL seed file that creates the orders table and populates it with sample data when PostgreSQL starts for the first time.

  • Create a new file called init.sql in the same ecommerce-pipeline folder.
  • Paste the following SQL into init.sql:
CREATE TABLE IF NOT EXISTS orders (
    order_id SERIAL PRIMARY KEY,
    product VARCHAR(100) NOT NULL,
    amount INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO orders (product, amount, status) VALUES
    ('laptop', 1299, 'completed'),
    ('keyboard', 79, 'completed'),
    ('mouse', 49, 'pending'),
    ('monitor', 349, 'shipped'),
    ('headphones', 199, 'pending');

What does this SQL do?

  • The CREATE TABLE statement defines an orders table with columns for product name, price amount, order status, and timestamps.
  • The INSERT statement seeds 5 sample orders so you have data to work with from the start.
  • The docker-compose.yml mounts this file into PostgreSQL's docker-entrypoint-initdb.d directory, which automatically runs it when the container starts for the first time.
  • Save init.sql.
  • Confirm both files exist by running this in your terminal from the ecommerce-pipeline folder:
ls

You should see docker-compose.yml and init.sql in the output.

Don't see both files?

Make sure your terminal is inside the ecommerce-pipeline folder. Run pwd to check your current directory. If you're not in the right folder, navigate there first.

Help me find my project files

✔️ Awesome, I've got everything!

Great. Double check you've saved init.sql before moving on.

ⓧ I'd like to double check the full code

CREATE TABLE IF NOT EXISTS orders (
    order_id SERIAL PRIMARY KEY,
    product VARCHAR(100) NOT NULL,
    amount INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO orders (product, amount, status) VALUES
    ('laptop', 1299, 'completed'),
    ('keyboard', 79, 'completed'),
    ('mouse', 49, 'pending'),
    ('monitor', 349, 'shipped'),
    ('headphones', 199, 'pending');
Launch the stack and verify all services

With both files in place, you can spin up all 8 containers at once. This stack uses roughly 3-5 GB of RAM, so make sure Docker Desktop has enough memory allocated.

Check your Docker Desktop memory

Open Docker Desktop, go to Settings, then Resources. Make sure at least 6 GB of memory is allocated. If your system has 8 GB of total RAM, 6 GB for Docker should work. Close any unnecessary applications to free up memory.

  • Make sure Docker Desktop is running.
  • Launch all containers by running this command from the ecommerce-pipeline folder:
docker compose up -d

Docker pulls the images on the first run, which may take a few minutes depending on your internet speed. The -d flag runs everything in the background so you keep your terminal free.

  • Wait about 30-60 seconds for all services to start, then check that every container is running:
docker compose ps

You should see all 8 containers listed with a status of Up or running: postgres, zookeeper, kafka, kafka-connect, clickhouse, grafana, and kafka-ui.

Containers not all running?

  • If a container shows Exited or Restarting, check its logs with docker compose logs <service-name> (for example, docker compose logs kafka-connect).
  • If you see a port conflict error (like port is already allocated), another application is using that port. Stop the conflicting application or change the port mapping in docker-compose.yml.
  • The kafka-connect container can take up to 60 seconds to fully start. If it shows Restarting, wait a minute and run docker compose ps again.

Help me debug my Docker containers

Now verify that the web interfaces are accessible.

  • Open your browser and go to http://localhost:8080 to access Kafka UI. You should see a dashboard showing your local Kafka cluster.
  • Go to http://localhost:3000 to access Grafana. Log in with username admin and password admin. Grafana may prompt you to change the password. You can click Skip for now.
  • Go to http://localhost:8123 to verify the ClickHouse HTTP API. You should see the text Ok. in your browser.

Why check all three UIs?

Each UI confirms a different layer of your stack is healthy. Kafka UI proves the message broker is running. Grafana proves the dashboard layer is ready. The ClickHouse Ok. response proves the analytical database is accepting connections.

Your entire stack is up and running. Eight containers are working together, ready to stream data from a transactional database all the way to an analytical dashboard. Next up, you'll get hands-on with Kafka by creating topics and producing messages using CLI tools.

Explore Kafka with CLI Tools

Your entire stack is up and running with all 8 containers healthy. Before wiring these services together, let's get comfortable with the messaging system sitting at the center of your pipeline.

Kafka is a distributed event streaming platform. Every piece of data in your pipeline will flow through it. In this step, you'll use Kafka's built-in CLI tools to create topics, send messages, and inspect how consumers track their progress.

In this step, get ready to:
  • Create a Kafka topic with multiple partitions.
  • Produce and consume messages in real-time using the CLI.
  • Inspect consumer groups and explore the Kafka UI.
Create a topic and inspect it

Kafka organizes messages into topics. Each topic can be split into partitions, which allow multiple consumers to read from the same topic in parallel.

You'll run Kafka's CLI tools inside the kafka container using docker exec. This lets you execute commands directly inside a running container from your host terminal.

  • Create a test topic called test-orders with 3 partitions by running this command:
docker exec kafka kafka-topics --create --topic test-orders --bootstrap-server kafka:9092 --partitions 3 --replication-factor 1

What does this command do?

docker exec kafka runs the command inside your running kafka container.

kafka-topics --create is a built-in Kafka CLI tool that creates a new topic. --partitions 3 splits the topic into 3 partitions for parallel processing. --replication-factor 1 means each partition has one copy (fine for a local single-broker setup).

  • Confirm the topic was created by listing all Kafka topics:
docker exec kafka kafka-topics --list --bootstrap-server kafka:9092

You should see test-orders in the output along with some internal Kafka topics (like __consumer_offsets).

  • Inspect the partition layout of your new topic by running:
docker exec kafka kafka-topics --describe --topic test-orders --bootstrap-server kafka:9092

You should see 3 partitions (Partition 0, 1, and 2) listed with their leader and replica assignments.

Topic creation failed?

  • Make sure the kafka container is running. Check with docker compose ps and look for the kafka service showing a healthy state.
  • If you see Error while executing topic creation, the Kafka broker may still be starting up. Wait 10-15 seconds and try again.
  • Help me troubleshoot my Kafka topic creation error.
Produce and consume messages manually

Kafka's CLI includes a console producer and consumer for quick testing. The producer sends messages to a topic. The consumer reads them back.

You'll need two terminal windows open at the same time for this exercise. One will be sending messages, and the other will be receiving them.

  • In your first terminal window, start a console producer by running:
docker exec -it kafka kafka-console-producer --topic test-orders --bootstrap-server kafka:9092

What should I see?

This starts an interactive session. Your terminal prompt changes to > and waits for you to type messages. Each line you type and press Enter becomes a message sent to the test-orders topic.

  • Type each of these JSON messages one at a time, pressing Enter after each:
{"order_id": 1, "product": "laptop", "amount": 999}
{"order_id": 2, "product": "keyboard", "amount": 79}
{"order_id": 3, "product": "mouse", "amount": 49}

Leave this producer terminal running. Now open a second terminal window to start consuming messages.

  • In your second terminal, start a console consumer by running:
docker exec -it kafka kafka-console-consumer --topic test-orders --bootstrap-server kafka:9092 --from-beginning

You should see all three JSON messages appear in the consumer terminal. They may not be in the same order you typed them, because Kafka distributes messages across partitions.

Why --from-beginning?

The --from-beginning flag tells the consumer to read all messages from the start of the topic. Without it, the consumer only shows messages that arrive after it starts.

  • Try typing a new message in your producer terminal, like {"order_id": 4, "product": "monitor", "amount": 349}.

The message appears in your consumer terminal almost instantly. This is real-time event streaming in action.

  • Press Ctrl+C in both the producer and consumer terminals to exit.

Not seeing any messages in the consumer?

  • Make sure you used --from-beginning in the consumer command. Without it, the consumer only shows new messages that arrive after it starts.
  • Confirm the topic name matches exactly. Both commands should target test-orders.
  • Help me figure out why my Kafka console consumer isn't showing messages.
Explore consumer groups and offsets

Every time a consumer reads from a topic, Kafka tracks its position using an offset. An offset is a number that marks which message the consumer has read up to.

Consumers are organized into consumer groups. The console consumer you just ran was automatically assigned to a group by Kafka. Let's inspect it.

  • List all consumer groups by running:
docker exec kafka kafka-consumer-groups --list --bootstrap-server kafka:9092

You should see at least one group name that starts with console-consumer- followed by a random number. Copy this group name for the next command.

  • Describe the consumer group to see its offset details by running:
docker exec kafka kafka-consumer-groups --describe --group [[CONSUMER_GROUP="your consumer group name from the list output"]] --bootstrap-server kafka:9092

What do these columns mean?

  • TOPIC and PARTITION show which topic partition this row describes.
  • CURRENT-OFFSET is how far the consumer has read in that partition.
  • LOG-END-OFFSET is the total number of messages in the partition.
  • LAG is the difference between the two. A lag of 0 means the consumer has read everything. Any positive number means messages are waiting to be consumed.

Now let's see all of this visually in the Kafka UI.

  • Open your browser and navigate to http://localhost:8080.
  • Click Topics in the left sidebar.
  • Click on test-orders to see its partition count, message count, and individual messages.
  • Click the Messages tab to browse the actual JSON messages you produced.
  • Click Consumers in the left sidebar to see the consumer group you inspected from the CLI.

Why use Kafka UI?

The CLI tools are powerful but text-heavy. Kafka UI gives you a visual dashboard to browse topics, read message payloads, check partition distribution, and monitor consumer group lag at a glance. In later steps, you'll use it to inspect CDC events flowing from PostgreSQL.

Kafka UI not loading?

  • Make sure the kafka-ui container is running. Check with docker compose ps.
  • If another application is already using port 8080, the container may have failed to start. Check for port conflicts with docker compose logs kafka-ui.
  • Help me troubleshoot my Kafka UI not loading at localhost:8080.

You've now explored the core building blocks of Kafka: topics, partitions, producers, consumers, and consumer groups. Next up, you'll write a Python script that simulates real e-commerce orders flowing into Kafka.

Write a Python Kafka Producer

In Step 2, you explored Kafka's CLI tools by creating topics, producing messages by hand, and consuming them in real-time. Now it's time to graduate from manual typing to programmatic producing.

In a real e-commerce system, orders arrive automatically from an application. In this step, you'll write a Python script that simulates that behavior by sending randomized order events to Kafka every second.

In this step, get ready to:
  • Create a producer.py script that sends random order events to Kafka.
  • Run the producer and verify messages arrive via CLI and Kafka UI.
  • Inspect consumer group lag to understand how Kafka tracks consumption progress.
Create the producer script

Your producer script needs to connect to Kafka, define a catalog of products, and continuously send randomized order events. You'll build it in two chunks: the setup, then the main event loop.

  • Create a new file called producer.py inside your ecommerce-pipeline folder using your text editor.
  • Add the imports, Kafka connection, and product data by copying this code into producer.py:
import json
import time
import random
from kafka import KafkaProducer

producer = KafkaProducer(
    bootstrap_servers=["localhost:9092"],
    value_serializer=lambda v: json.dumps(v).encode("utf-8"),
)

products = [
    {"name": "laptop", "price": 1299},
    {"name": "keyboard", "price": 79},
    {"name": "mouse", "price": 49},
    {"name": "monitor", "price": 349},
    {"name": "headphones", "price": 199},
    {"name": "webcam", "price": 89},
    {"name": "usb-hub", "price": 39},
    {"name": "mousepad", "price": 19},
]

statuses = ["pending", "completed", "shipped", "cancelled"]

print("Starting order producer. Press Ctrl+C to stop.")
order_id = 100

What does this code do?

  • json, time, and random are standard Python libraries for encoding data, generating timestamps, and picking random values.
  • from kafka import KafkaProducer pulls in the producer class from the kafka-python library you installed in Step 1.
  • bootstrap_servers points to localhost:9092, which is Kafka's OUTSIDE listener that routes traffic from your host machine into the container.
  • value_serializer converts Python dictionaries into JSON-encoded bytes before sending them to Kafka.
  • products is a catalog the script randomly picks from when generating each order.
  • order_id starts at 100 so you can easily distinguish producer-generated orders from the ones you typed manually in Step 2.

You won't see any output yet. The next chunk adds the event loop that actually sends messages.

  • Add the main event loop at the bottom of producer.py, directly below the order_id = 100 line:
try:
    while True:
        product = random.choice(products)
        order = {
            "order_id": order_id,
            "product": product["name"],
            "amount": product["price"],
            "status": random.choice(statuses),
            "timestamp": time.strftime("%Y-%m-%d %H:%M:%S"),
        }
        future = producer.send("test-orders", value=order)
        result = future.get(timeout=10)
        print(
            f"Sent order {order_id}: {product['name']} "
            f"-> partition {result.partition}, offset {result.offset}"
        )
        order_id += 1
        time.sleep(1)
except KeyboardInterrupt:
    print("\nStopping producer.")
finally:
    producer.flush()
    producer.close()

What does this code do?

  • The while True loop sends one order per second until you press Ctrl+C.
  • random.choice() picks a random product and status for each order, simulating realistic e-commerce traffic.
  • producer.send() pushes the order to the test-orders topic. Kafka decides which partition to place it in.
  • future.get(timeout=10) waits for Kafka to acknowledge the message. The result includes the partition and offset where the message landed.
  • The finally block calls flush() and close() to ensure no messages are lost when the script exits.
  • Save producer.py.

Seeing a syntax error when you save?

  • Make sure the try: block starts at column 0 (no indentation). The while True: inside it should be indented one level.
  • Check that except KeyboardInterrupt: and finally: are aligned with try: at column 0.

Help me fix a syntax error in my producer.py script.

✔️ Awesome, I've got everything!

Double check you've saved producer.py. You'll run it in the next substep.

ⓧ I'd like to double check the full code

import json
import time
import random
from kafka import KafkaProducer

producer = KafkaProducer(
    bootstrap_servers=["localhost:9092"],
    value_serializer=lambda v: json.dumps(v).encode("utf-8"),
)

products = [
    {"name": "laptop", "price": 1299},
    {"name": "keyboard", "price": 79},
    {"name": "mouse", "price": 49},
    {"name": "monitor", "price": 349},
    {"name": "headphones", "price": 199},
    {"name": "webcam", "price": 89},
    {"name": "usb-hub", "price": 39},
    {"name": "mousepad", "price": 19},
]

statuses = ["pending", "completed", "shipped", "cancelled"]

print("Starting order producer. Press Ctrl+C to stop.")
order_id = 100

try:
    while True:
        product = random.choice(products)
        order = {
            "order_id": order_id,
            "product": product["name"],
            "amount": product["price"],
            "status": random.choice(statuses),
            "timestamp": time.strftime("%Y-%m-%d %H:%M:%S"),
        }
        future = producer.send("test-orders", value=order)
        result = future.get(timeout=10)
        print(
            f"Sent order {order_id}: {product['name']} "
            f"-> partition {result.partition}, offset {result.offset}"
        )
        order_id += 1
        time.sleep(1)
except KeyboardInterrupt:
    print("\nStopping producer.")
finally:
    producer.flush()
    producer.close()
Run the producer and watch messages arrive

The script is ready. Time to fire it up and see orders flowing into Kafka in real-time.

  • Make sure your terminal is in the ecommerce-pipeline folder (the one containing producer.py).
  • Start the producer by running this command:
python producer.py

You should see output like Sent order 100: laptop -> partition 2, offset 15 printing every second.

Seeing a connection error or ModuleNotFoundError?

  • If you see ModuleNotFoundError: No module named 'kafka', the kafka-python library is not installed. Run pip install kafka-python==2.3.1 and try again.
  • If you see NoBrokersAvailable, your Kafka container may not be running. Run docker compose ps and check that the kafka container shows as healthy.

Help me fix my Kafka producer connection error.

  • Leave the producer running.
  • Open a second terminal window.
  • Start a console consumer to watch the messages arrive by running this command:
docker exec -it kafka kafka-console-consumer --topic test-orders --bootstrap-server kafka:9092 --from-beginning

You should see JSON order messages appearing in the consumer terminal as the producer sends them. Each message includes an order_id, product, amount, status, and timestamp.

  • Open your browser and go to http://localhost:8080 to open Kafka UI.
  • Click Topics in the left sidebar.
  • Click test-orders to see the topic details.
  • Click the Messages tab to browse individual messages. You can see the JSON payload, partition number, and offset for each message.
  • Go back to the terminal running the producer and press Ctrl+C to stop it.
  • Go to the terminal running the consumer and press Ctrl+C to stop it too.
Inspect consumer group lag

Consumer group lag tells you how far behind a consumer is from the latest message in a topic. This is one of the most important monitoring metrics in any Kafka deployment.

  • Start a console consumer with a named group called order-watchers by running this command:
docker exec -it kafka kafka-console-consumer --topic test-orders --bootstrap-server kafka:9092 --group order-watchers --from-beginning

What is the --group flag?

The --group order-watchers flag assigns this consumer to a named consumer group. Kafka tracks the group's progress (called offsets) so it knows exactly which messages have been read.

  • Let the consumer run for a few seconds. You should see it reading through the existing messages.
  • Press Ctrl+C to stop the consumer.

The consumer is now stopped, but Kafka remembers where it left off. If new messages arrive, the consumer falls behind.

  • Open a second terminal and run the producer again to add more messages by running this command:
python producer.py
  • Let it send about 10 messages (roughly 10 seconds).
  • Press Ctrl+C to stop the producer.
  • Check the consumer group lag by running this command:
docker exec kafka kafka-consumer-groups --describe --group order-watchers --bootstrap-server kafka:9092

You should see a table with columns including CURRENT-OFFSET, LOG-END-OFFSET, and LAG for each partition.

What do these columns mean?

  • CURRENT-OFFSET is the last message the order-watchers group successfully read.
  • LOG-END-OFFSET is the most recent message available in that partition.
  • LAG is the difference between the two. It tells you how many unread messages the consumer has fallen behind. In production, high lag means your consumer cannot keep up with incoming data.

Seeing all zeros in the LAG column?

This means the consumer caught up before you stopped it. Stop the consumer first, then run the producer to add new messages. The lag only appears when messages arrive after the consumer stops.

Help me understand why my consumer group lag is zero.

Your Python producer is now streaming orders into Kafka like a real application would. Next, you'll set up Debezium to capture actual database changes from PostgreSQL and push them into Kafka automatically.

Set Up CDC with Debezium

In the last step, you wrote a Python producer that simulates e-commerce orders and explored consumer groups and lag. That showed you how Kafka tracks message consumption across consumers.

Now it is time to connect your real database to Kafka. Instead of manually producing fake messages, you will use Debezium to automatically capture every change made to the PostgreSQL orders table and stream it to Kafka in real-time. This pattern is called Change Data Capture (CDC).

In this step, get ready to:
  • Register a Debezium connector that watches the PostgreSQL orders table.
  • Trigger INSERT and UPDATE events in PostgreSQL and observe them flowing into Kafka.
  • Consume CDC events with the Kafka CLI and inspect the Debezium message envelope.
Register the Debezium PostgreSQL connector

Debezium reads the PostgreSQL WAL (write-ahead log) directly. Every INSERT, UPDATE, and DELETE on the orders table gets captured automatically and published to a Kafka topic.

Why Debezium instead of a custom producer?

Your Python producer from the last step required custom code to send messages. Debezium needs zero application changes. It taps directly into PostgreSQL's WAL to capture every row-level change.

This means it catches changes made by any client. Direct SQL queries, admin tools, other services. Everything gets captured.

Debezium runs as a connector inside Kafka Connect, which is the kafka-connect container in your Docker stack. You register a connector by sending a JSON configuration to the Kafka Connect REST API.

  • Register the Debezium PostgreSQL connector by running this command:

macOS

curl -X POST http://localhost:8083/connectors \
  -H "Content-Type: application/json" \
  -d '{
    "name": "orders-connector",
    "config": {
      "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
      "database.hostname": "postgres",
      "database.port": "5432",
      "database.user": "postgres",
      "database.password": "postgres",
      "database.dbname": "ecommerce",
      "topic.prefix": "ecommerce",
      "table.include.list": "public.orders",
      "plugin.name": "pgoutput"
    }
  }'

Windows

$body = @"
{
  "name": "orders-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres",
    "database.port": "5432",
    "database.user": "postgres",
    "database.password": "postgres",
    "database.dbname": "ecommerce",
    "topic.prefix": "ecommerce",
    "table.include.list": "public.orders",
    "plugin.name": "pgoutput"
  }
}
"@

Invoke-RestMethod -Method Post -Uri "http://localhost:8083/connectors" -ContentType "application/json" -Body $body

What does this configuration do?

  • connector.class tells Kafka Connect to use the Debezium PostgreSQL connector.
  • database.hostname points to postgres, which is the container name from your Docker Compose file.
  • topic.prefix sets the prefix for auto-created Kafka topics. Debezium names them as {prefix}.{schema}.{table}, so your CDC topic will be ecommerce.public.orders.
  • plugin.name uses pgoutput, which is PostgreSQL's built-in logical decoding plugin.
  • Confirm the connector is running by checking its status:

macOS

curl http://localhost:8083/connectors/orders-connector/status

Windows

curl.exe http://localhost:8083/connectors/orders-connector/status

You should see a JSON response with "state": "RUNNING" for both the connector and its task. This means Debezium is actively watching your orders table.

Connector not showing as RUNNING?

  • If you see a Connection refused error, the kafka-connect container may still be starting up. Wait 30 seconds and try again.
  • If the connector state is FAILED, double check that the PostgreSQL container is healthy by running docker compose ps.
  • If you get a 409 Conflict error, the connector already exists. That means your earlier registration succeeded. Run the status check command to confirm it is running.

Why won't my Debezium connector start? I registered it with curl but the status isn't RUNNING.

Trigger CDC events and observe them in Kafka

Your Debezium connector is now watching the orders table. Any INSERT, UPDATE, or DELETE you make in PostgreSQL gets captured and sent to the ecommerce.public.orders Kafka topic automatically.

  • Insert a new order into PostgreSQL by running:
docker exec -it postgres psql -U postgres -d ecommerce -c "INSERT INTO orders (product, amount, status) VALUES ('monitor', 349, 'pending');"
  • Update an existing order by running:
docker exec -it postgres psql -U postgres -d ecommerce -c "UPDATE orders SET status = 'shipped' WHERE product = 'monitor';"

What just happened behind the scenes?

PostgreSQL recorded both changes in its write-ahead log. Debezium detected those WAL entries and converted them into Kafka messages on the ecommerce.public.orders topic. No code changes needed.

  • Open your browser and go to http://localhost:8080 to open the Kafka UI.
  • Click Topics in the left sidebar.
  • Find and click the ecommerce.public.orders topic.
  • Click the Messages tab to view the CDC events.

Don't see the ecommerce.public.orders topic?

  • The topic is only created after the connector starts successfully. Go back and run the status check command to confirm the connector state is RUNNING.
  • Try refreshing the Kafka UI page. New topics may take a few seconds to appear.

Why can't I see the CDC topic in Kafka UI?

You should see messages containing a JSON envelope. Each message has before, after, and op fields.

What are the before, after, and op fields?

Every Debezium message wraps the change in an envelope with three key fields:

  • before contains the row's previous values. For INSERTs, this is null because the row did not exist yet. For UPDATEs with the default replica identity, it contains only the primary key column (order_id).
  • after contains the row's values after the change. For DELETEs, this is null because the row no longer exists.
  • op indicates the operation type: r for snapshot read, c for create (INSERT), u for update, and d for delete.
Consume CDC events with the Kafka CLI

The Kafka UI gives you a visual way to browse CDC events. Now you will use the Kafka CLI consumer to read the same messages directly from the terminal.

  • Start a console consumer on the CDC topic by running:
docker exec -it kafka kafka-console-consumer --topic ecommerce.public.orders --bootstrap-server kafka:9092 --from-beginning

What should I see?

You will see raw JSON messages scroll through your terminal. Each message is a Debezium CDC event with the full envelope structure.

  • Messages with "op": "r" are snapshot reads. These are the existing rows that were already in the table when the connector first started.
  • Messages with "op": "c" are the INSERTs you just ran. The before field is null and after contains the new row.
  • Messages with "op": "u" are the UPDATEs. The before field contains only the primary key (order_id) and after shows the full updated row.
  • Press Ctrl+C to stop the consumer when you are done inspecting the messages.

Not seeing any messages?

  • Make sure you included --from-beginning in the consumer command. Without it, the consumer only shows new messages that arrive after it starts.
  • Check that you are reading from topic ecommerce.public.orders (the CDC topic), not test-orders (the manual topic from earlier steps).

Why am I not seeing CDC messages in my Kafka console consumer?

Your CDC pipeline is live. Every change in PostgreSQL now flows automatically into Kafka. Next up, you will connect Kafka to ClickHouse so these events become queryable in a fast analytical database.

Ingest Kafka Data into ClickHouse

In the previous step, you configured Debezium to capture row-level changes from PostgreSQL and stream them into Kafka. Your CDC topic ecommerce.public.orders is now filling up with insert and update events.

Now you need to get that streaming data into ClickHouse for fast analytical queries. ClickHouse has a native Kafka Engine that subscribes directly to Kafka topics. You will use a three-table pattern to automatically ingest CDC events into a query-ready table.

In this step, get ready to:
  • Create a ClickHouse target table with the ReplacingMergeTree engine.
  • Build a Kafka Engine table and Materialized View to auto-ingest CDC events.
  • Verify the full pipeline by inserting orders in PostgreSQL and querying them in ClickHouse.
Create the ClickHouse target table

The three-table pattern in ClickHouse works like this: a Kafka Engine table reads raw messages, a Materialized View transforms each message, and a MergeTree target table stores the final data. You will build all three, starting with the target table.

  • Open the ClickHouse client by running this command:
docker exec -it clickhouse clickhouse-client

You should see a prompt that starts with :) indicating you are inside the ClickHouse interactive shell.

Connection refused or container not found?

Make sure the ClickHouse container is running. Run docker compose ps and check that the clickhouse container shows a healthy status. If it restarted, run docker compose up -d from your ecommerce-pipeline folder to bring it back.

Help me troubleshoot the ClickHouse container not starting in my Docker Compose stack.

  • Create the orders target table by running this SQL statement:
-- Target table for deduplicated order data
CREATE TABLE orders (
    order_id UInt32,
    product String,
    amount UInt32,
    status String,
    created_at DateTime,
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY order_id;

What does this SQL do?

This creates a table with six columns matching the fields Debezium captures from PostgreSQL.

The ReplacingMergeTree(updated_at) engine is the key detail. Debezium sends a new message for every INSERT and UPDATE. Without deduplication, you would end up with multiple rows for the same order. ReplacingMergeTree keeps only the latest version of each row based on the updated_at column, collapsing old versions during background merges.

You should see Ok. printed in the ClickHouse client after running the statement.

Seeing a syntax error?

Make sure you copied the entire statement including the semicolon at the end. ClickHouse requires a trailing ; to execute a statement. If you see a multi-line continuation prompt (:-]), type ; and press Enter to finish.

Help me fix a syntax error when creating a ClickHouse table with ReplacingMergeTree.

Create the Kafka Engine table and Materialized View

With the target table ready, you need two more pieces. A Kafka Engine table subscribes to the CDC topic and reads raw Debezium messages. A Materialized View transforms each message and writes the extracted fields into your orders table automatically.

  • Create the Kafka Engine table by running this SQL statement in the same ClickHouse client session:
-- Kafka Engine table that subscribes to the CDC topic
CREATE TABLE orders_kafka (
    before String,
    after String,
    op String
) ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka:29092',
    kafka_topic_list = 'ecommerce.public.orders',
    kafka_group_name = 'clickhouse-consumer',
    kafka_format = 'JSONEachRow';

What does this table do?

This table does not store data permanently. It acts as a Kafka consumer that reads messages from the ecommerce.public.orders topic. Each Debezium message has three top-level fields: before (the old row), after (the new row), and op (the operation type). These map to the three String columns.

The broker address uses kafka:29092 because ClickHouse is inside the Docker network. This is the INSIDE listener you configured in docker-compose.yml for container-to-container communication.

You should see Ok. in the ClickHouse client.

Seeing a Kafka connection error?

The table creation itself should succeed even if the Kafka broker is temporarily unreachable. If you see an error about the broker, confirm that the kafka container is running with docker compose ps. Also double-check that you typed kafka:29092 (port 29092, not 9092).

Help me troubleshoot a Kafka connection error in ClickHouse when creating a Kafka Engine table.

  • Create the Materialized View that transforms Debezium messages and inserts them into the target table by running this SQL:
-- Materialized View: extract fields from Debezium's "after" image
CREATE MATERIALIZED VIEW orders_mv TO orders AS
SELECT
    JSONExtractUInt(after, 'order_id') AS order_id,
    JSONExtractString(after, 'product') AS product,
    JSONExtractUInt(after, 'amount') AS amount,
    JSONExtractString(after, 'status') AS status,
    toDateTime(JSONExtract(after, 'created_at', 'Int64') / 1000000) AS created_at,
    toDateTime(JSONExtract(after, 'updated_at', 'Int64') / 1000000) AS updated_at
FROM orders_kafka
WHERE after != '';

What does this Materialized View do?

Every time a message arrives in orders_kafka, this view fires automatically. It uses JSONExtract functions to pull individual fields out of the after JSON string (the new row image from Debezium).

Debezium's JSON converter represents PostgreSQL timestamps as microseconds since epoch. Dividing by 1000000 converts them to seconds, which toDateTime() expects.

The WHERE after != '' clause filters out DELETE events where the after field is null (since there is no new row to store for a delete).

You should see Ok. in the ClickHouse client. The Materialized View starts consuming from Kafka immediately upon creation.

Why the Kafka Engine instead of a Kafka Connect sink?

ClickHouse offers its own native Kafka Engine, which is a built-in table engine that subscribes directly to Kafka topics. The alternative would be a separate Kafka Connect ClickHouse Sink connector, which adds another moving part to the stack.

The Kafka Engine approach teaches you more about ClickHouse internals and how Materialized Views work as stream processors. The three-table pattern (Kafka Engine, Materialized View, MergeTree target) is the recommended approach in the ClickHouse documentation.

View created but no data appearing later?

Verify that your Debezium connector is still running. Open a new terminal and run:

curl http://localhost:8083/connectors/orders-connector/status

The status should show RUNNING for both the connector and its task. If it shows FAILED, restart it by deleting and re-creating the connector from Step 4.

Help me troubleshoot why data is not flowing from Kafka into my ClickHouse orders table.

Verify the data pipeline

Your three-table pipeline is wired up. Time to prove it works end-to-end by querying the data that has already flowed through and then inserting fresh orders.

  • Query the orders table to see any data that has already arrived from previous CDC events by running this in the ClickHouse client:
-- Query with FINAL to get deduplicated latest rows
SELECT * FROM orders FINAL ORDER BY order_id;

What does FINAL do?

ReplacingMergeTree deduplicates rows during background merges, which happen asynchronously. Adding FINAL to a query forces deduplication at read time, so you always see the latest version of each row. Without it, you might see duplicate rows for the same order_id if a merge has not run yet.

You may see rows from the CDC events generated in the previous step, or the table may be empty if those messages were consumed before the Materialized View existed. Either is fine.

  • Open a new terminal window (keep the ClickHouse client open).
  • Insert a few new orders into PostgreSQL by running these commands:
docker exec -it postgres psql -U postgres -d ecommerce -c "INSERT INTO orders (product, amount, status) VALUES ('webcam', 89, 'pending');"
docker exec -it postgres psql -U postgres -d ecommerce -c "INSERT INTO orders (product, amount, status) VALUES ('usb-hub', 39, 'completed');"
docker exec -it postgres psql -U postgres -d ecommerce -c "INSERT INTO orders (product, amount, status) VALUES ('mousepad', 19, 'shipped');"

What happens when you insert?

Each INSERT triggers the full pipeline: PostgreSQL writes to its WAL, Debezium reads the WAL change, publishes a CDC event to the ecommerce.public.orders Kafka topic, ClickHouse's Kafka Engine consumes the message, and the Materialized View extracts the fields into the orders target table. This all happens within seconds.

  • Switch back to your ClickHouse client terminal.
  • Query the orders table again to see the new data by running:
-- Check that new orders arrived from PostgreSQL
SELECT * FROM orders FINAL ORDER BY order_id;

You should see rows for webcam, usb-hub, and mousepad in the results. The data flowed from PostgreSQL through Debezium, Kafka, and into ClickHouse automatically.

Not seeing the new rows?

Wait 5-10 seconds and run the query again. There is a small delay as the message passes through Kafka and gets consumed by ClickHouse.

If rows still do not appear, check Kafka UI at http://localhost:8080. Navigate to the ecommerce.public.orders topic and confirm new messages were produced. If messages are there but ClickHouse is empty, the Materialized View may have an issue. Drop all three objects (DROP VIEW orders_mv, DROP TABLE orders_kafka, DROP TABLE orders) and re-create them from the beginning of this step.

Help me debug why new PostgreSQL inserts are not showing up in my ClickHouse orders table.

Your CDC pipeline is now fully wired. Data flows from PostgreSQL through Kafka into ClickHouse without any manual intervention. Next up, you will build a Grafana dashboard to visualize this streaming data in real time.

Build a Grafana Dashboard

Your data pipeline is humming along. Orders flow from PostgreSQL through Debezium and Kafka into ClickHouse automatically.

Now it is time to make that data visible. Grafana connects directly to ClickHouse and lets you build live dashboards that update in real time as new data arrives.

In this step, get ready to:
  • Connect Grafana to ClickHouse as a data source.
  • Build two dashboard panels for order analytics.
  • Test the full pipeline end-to-end from PostgreSQL to Grafana.
Configure the ClickHouse data source

Grafana needs a data source connection before it can query ClickHouse. The ClickHouse plugin was automatically installed when the Grafana container started, thanks to the GF_INSTALL_PLUGINS environment variable in your docker-compose.yml.

  • Open your browser and go to http://localhost:3000.
  • Log in with username admin and password admin.
  • Grafana may prompt you to change the password. Click Skip for now.

You should see the Grafana home screen with a welcome message.

  • Click Connections in the left-side menu.
  • Click Add new connection.
  • Type ClickHouse in the search bar.
  • Select ClickHouse from the results.
  • Click Add new data source in the top right.

The data source configuration page opens. Fill in the connection details:

  • In the Server field, enter clickhouse.
  • Set Protocol to Native (this should already be the default).
  • Confirm Port is set to 9000.
  • Leave Secure connection toggled off.
  • Enter default in the Username field.
  • Leave the Password field empty.
  • Scroll down and set the Default database to default.

Why use the container name as the server?

Grafana runs inside Docker alongside ClickHouse. Container-to-container communication uses the Docker service name (clickhouse) instead of localhost. This is the same networking pattern the other containers use.

  • Click Save & test at the bottom of the page.

You should see a green banner confirming the connection is working.

Connection failed?

Make sure the Server field says clickhouse (the Docker container name), not localhost.

Verify the ClickHouse container is running by checking docker compose ps in your terminal.

Why can't Grafana connect to my ClickHouse data source?

Create dashboard panels

With the data source connected, you can now build a dashboard to visualize your e-commerce orders. You will create two panels: one showing order volume over time and another showing revenue broken down by order status.

  • Click Dashboards in the left-side menu.
  • Click New and select New Dashboard.
  • On the empty dashboard, click + Add visualization.
  • Select your ClickHouse data source from the list.

The panel editor opens. This is where you write SQL queries and choose how to display the results.

  • In the query editor at the bottom, switch to SQL editor mode (instead of Query builder).
  • Set the Query type dropdown to Time series.
  • Clear any existing query and paste the following SQL:
SELECT
    $__timeInterval(created_at) as time,
    count() as order_count
FROM orders FINAL
WHERE $__timeFilter(created_at)
GROUP BY time
ORDER BY time

What does this query do?

`$__timeInterval(created_at)` is a Grafana macro that buckets timestamps into intervals based on the dashboard's time range. If you are viewing the last hour, it might group by 1-minute intervals.

`$__timeFilter(created_at)` filters rows to only include those within the dashboard's selected time range. `FINAL` ensures ClickHouse deduplicates rows from the ReplacingMergeTree engine so each order appears only once.

  • Click Run Query (or press Cmd+Enter on macOS, Ctrl+Enter on Windows) to execute the query.
  • In the visualization list on the right side, select Time series if it is not already selected.
  • Under Panel options on the right sidebar, set the Title to Order Volume Over Time.

Adjust the time range if the chart is empty

If you do not see data points, change the dashboard time range using the time picker in the top right corner of Grafana. Try setting it to Last 24 hours or Last 7 days to cover when your orders were inserted.

  • Click Apply in the top right to save this panel and return to the dashboard.

You should see a time series chart on your dashboard. Now add the second panel.

  • Click Add in the dashboard header and select Visualization.
  • Select your ClickHouse data source.
  • Switch to SQL editor mode.
  • Set the Query type to Table.
  • Paste the following SQL query:
SELECT
    status,
    sum(amount) as total_revenue
FROM orders FINAL
GROUP BY status
ORDER BY total_revenue DESC

What does this query do?

This groups all orders by their status (pending, completed, shipped, cancelled) and sums the amount for each group. The result shows total revenue per status category.

  • Click Run Query to execute.
  • In the visualization list on the right, select Bar chart.
  • Under Panel options, set the Title to Revenue by Status.
  • Click Apply to save this panel.

Your dashboard now has two panels showing order volume and revenue.

  • Click the Save dashboard icon (floppy disk) in the top right.
  • Enter E-Commerce Analytics as the dashboard title and click Save.

Bar chart showing empty or no data?

Make sure the Query type is set to Table (not Time series) for the revenue panel. Bar charts need a string column and a numeric column.

Check that your ClickHouse orders table has data by running SELECT count() FROM orders FINAL in the ClickHouse client.

Why is my Grafana bar chart panel showing no data from ClickHouse?

Test the live pipeline end-to-end

The real magic is seeing data flow through all five systems in real time. You will insert new orders into PostgreSQL and watch them appear on your Grafana dashboard within seconds.

  • Insert several new orders into PostgreSQL by running this command:
docker exec -it postgres psql -U postgres -d ecommerce -c "
INSERT INTO orders (product, amount, status) VALUES
    ('webcam', 89, 'pending'),
    ('usb-hub', 39, 'completed'),
    ('laptop', 1299, 'shipped'),
    ('mousepad', 19, 'pending'),
    ('keyboard', 79, 'cancelled');
"

What happens when you insert these rows?

PostgreSQL writes the changes to its WAL (write-ahead log). Debezium reads the WAL and publishes CDC events to the ecommerce.public.orders Kafka topic.

ClickHouse's Kafka Engine table consumes those events. The materialized view extracts the data and inserts it into the orders target table. Grafana queries ClickHouse and displays the results.

  • Switch back to your Grafana dashboard in the browser.
  • Click the refresh icon in the top right corner of the dashboard (or press Cmd+Shift+R on macOS, Ctrl+Shift+R on Windows).

Both panels should update within seconds to reflect the new orders. The time series chart shows a bump in order volume, and the bar chart shows updated revenue totals.

Enable auto-refresh for a live feel

Click the refresh interval dropdown next to the time picker (it shows a circular arrow icon). Set it to 5s or 10s. Now the dashboard refreshes automatically so you can watch data arrive in near real-time.

  • Try inserting a few more orders or running your producer.py script from Step 3 to generate a burst of data. Watch the time series chart update with each refresh cycle.

Dashboard not updating with new orders?

Check that the Debezium connector is still running by visiting http://localhost:8083/connectors/orders-connector/status in your browser. The state should show RUNNING.

Verify data is reaching ClickHouse by running SELECT count() FROM orders FINAL in the ClickHouse client. If the count does not increase after inserting into PostgreSQL, the issue is between Debezium and ClickHouse.

Why are new PostgreSQL orders not showing up in my Grafana dashboard?

Your real-time analytics pipeline is complete. Data flows from PostgreSQL through Debezium, Kafka, and ClickHouse all the way to a live Grafana dashboard. Next up, a Secret Mission to push your ClickHouse skills further with pre-aggregated analytics.

Secret mission

Build Pre-Aggregated Analytics with SummingMergeTree

Your Grafana dashboard works great. But every query hits the raw orders table and forces deduplication with FINAL. Can you build a pre-aggregated layer in ClickHouse that makes your dashboard queries lightning-fast?

Before you go

Clean Up Your Resources

This project runs entirely on local Docker containers, so there are no ongoing cloud costs. However, the containers do consume system RAM and CPU while running. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • 8 Docker containers: postgres, zookeeper, kafka, kafka-connect, clickhouse, grafana, kafka-ui.
  • 3 Docker volumes: postgres-data, clickhouse-data, grafana-data.
  • Project files: docker-compose.yml, init.sql, producer.py.
  • Python library: kafka-python.
  • Debezium connector registered via the Kafka Connect REST API.
  • ClickHouse tables, Kafka Engine table, and materialized views.
  • Grafana dashboard and ClickHouse data source.

Keep everything running

No action needed. Choose this if you are still actively experimenting with the pipeline or plan to continue right away.

  • Your 8 containers will continue running and consuming approximately 3-5 GB of RAM.
  • All data in PostgreSQL, ClickHouse, and Grafana persists in Docker volumes.
  • The CDC pipeline stays active. Any new rows inserted into PostgreSQL will automatically flow through to ClickHouse.

Pause - I'll come back to this later

Shut down the running containers to free up memory, but keep all your data and configuration intact.

  • Open your terminal and navigate to the ecommerce-pipeline folder.
  • Stop all containers by running:
docker compose stop
  • This stops the containers without removing them or their volumes. Your PostgreSQL data, ClickHouse tables, and Grafana dashboards are all preserved.
  • When you are ready to resume, run this from the same folder:
docker compose start
  • All containers will restart with your existing data and configuration.

Delete - I don't want to use this again

Remove all containers, volumes, and project files to start fresh.

  • Open your terminal and navigate to the ecommerce-pipeline folder.
  • Remove all containers and their associated volumes by running:
docker compose down -v
  • The -v flag removes the Docker volumes (postgres-data, clickhouse-data, grafana-data), deleting all stored data.
  • Optionally, uninstall the Python Kafka library by running:
pip uninstall kafka-python
  • Optionally, delete the project folder entirely by running:
cd ~/Desktop
rm -rf ecommerce-pipeline
  • After these steps, everything from this project will be removed from your machine.

Mission accomplished

That's a Wrap!

You just built a production-grade real-time analytics pipeline from scratch. Data now flows automatically from a transactional database, through an event stream, into a columnar analytics engine, and onto a live dashboard.

You've learned how to:

  • Built a complete CDC pipeline streaming row-level changes from PostgreSQL through Debezium and Kafka into ClickHouse, with a live Grafana dashboard displaying real-time e-commerce analytics.
  • Mastered Kafka fundamentals hands-on: created topics, produced and consumed messages via the CLI, wrote a Python producer, inspected consumer groups and lag, and explored the Kafka UI.
  • Used ClickHouse's native Kafka Engine and Materialized Views to automatically ingest streaming data into analytical tables without writing any consumer code.
  • Secret Mission: Created pre-aggregated analytics with SummingMergeTree for lightning-fast dashboard queries.

Ready to quiz yourself?