Skip to main content

Apache Kafka

Apache Kafka is a distributed event streaming platform. Get topics into MotherDuck by materializing them as Iceberg tables, sinking them to object storage, or using a streaming ingestion partner.

MotherDuck doesn't consume from Kafka directly: there's no kafka extension, and a warehouse built for analytical scans is the wrong place to receive individual messages. Instead, something lands the topic in a format MotherDuck reads well, then MotherDuck queries it.

Three patterns cover almost every case. The difference between them is where the batching happens.

Materialize topics as Iceberg tables​

The cleanest option: let your Kafka platform write the topic to your object storage as an Iceberg table, then attach that catalog as a MotherDuck database. No pipeline code, and both Kafka consumers and MotherDuck see the same table.

  • Confluent Tableflow materializes Kafka topics as Iceberg or Delta Lake tables and exposes them through a built-in Iceberg REST catalog, or syncs the metadata to AWS Glue or another external catalog.
  • Redpanda Iceberg Topics writes topic data as Iceberg tables, using either an external REST catalog or a filesystem catalog in object storage.

Attach the catalog the same way as any other Iceberg REST catalog:

CREATE SECRET kafka_catalog_secret IN MOTHERDUCK (
    TYPE ICEBERG,
    TOKEN '<catalog_token>'
);

CREATE DATABASE topics (
    TYPE ICEBERG,
    "secret" kafka_catalog_secret,
    endpoint '<iceberg_rest_catalog_endpoint>',
    warehouse '<warehouse_identifier>',
    default_schema '<namespace>'
);

SELECT * FROM topics.<namespace>.orders_topic LIMIT 10;

The endpoint, warehouse identifier, and credential format come from your Kafka platform. Confluent publishes a DuckDB and Tableflow guide with the exact values. For the MotherDuck side, including authentication options and write limitations, see Apache Iceberg.

If your platform syncs metadata to AWS Glue instead of serving its own catalog, attach Glue. See AWS Glue in the Apache Iceberg page.

Sink to object storage and read the files​

If you already run Kafka Connect, an S3 sink connector writes topic data to a bucket as Parquet or JSON, and MotherDuck reads the prefix. This works with any Kafka distribution and needs no catalog.

CREATE SECRET my_s3_secret IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '<aws_access_key_id>',
    SECRET '<aws_secret_access_key>',
    REGION '<aws_region>'
);

CREATE TABLE events AS
SELECT * FROM read_parquet('s3://my-bucket/topics/events/**/*.parquet');

Use a wildcard over the whole prefix so new files are picked up as the sink writes them. Sinks usually partition by date or hour, so read those partitions as columns with hive_partitioning = true, and load incrementally rather than re-reading the full prefix on every run. See S3 import best practices and Data loading patterns.

Streamkap is a worked example of this pattern: it's Kafka-based, streams to S3 through the S3 sink connector, and MotherDuck reads the bucket.

Use a streaming ingestion partner​

Several partners consume Kafka (or act as the stream themselves) and write to MotherDuck for you:

  • Estuary materializes collections into MotherDuck tables, staging through object storage.
  • Streamkap handles change-data-capture sources and Kafka topics.
  • InfinYon and Bytewax are stream processors with MotherDuck sinks.

Writing your own consumer​

If you write the consumer yourself, batch aggressively. A per-message INSERT is the most expensive way to load data into an analytical engine: accumulate messages in memory or in a local DuckDB table and write batches of thousands to hundreds of thousands of rows, or write Parquet files and load those.

import duckdb
import pyarrow as pa

conn = duckdb.connect("md:my_db")

# `batch` is a list of decoded Kafka messages
batch_arrow = pa.Table.from_pylist(batch)
conn.register("batch_arrow", batch_arrow)
conn.sql("INSERT INTO events SELECT * FROM batch_arrow")

See Considerations for loading data for batch sizing and transaction guidance.

Things to know​

  • Latency is minutes, not milliseconds. Every pattern here batches: Iceberg materialization commits on an interval, sinks flush on a size or time trigger. If you need sub-second reads of the newest event, serve that from your streaming layer and use MotherDuck for the analytical view.
  • Schema changes need a plan. Avro and Protobuf schemas evolve, and each pattern handles that differently. Iceberg materialization applies schema evolution to the table; a file sink leaves you to handle it at read time with union_by_name = true.
  • Compaction matters. Frequent flushes produce many small files, which slows scans. Prefer Iceberg tables with maintenance enabled, or periodically rewrite the prefix into larger files.

Related content​