---
title: "ClickHouse Integration with Amazon Kinesis: Patterns, Reality"
excerpt: "ClickHouse integration with Amazon Kinesis: key architecture patterns, delivery guarantees, and real-world production trade-offs."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-01-20 00:00:00"
publishedOn: "2026-01-20 00:00:00"
updatedOn: "2026-01-20 00:00:00"
status: "published"
---

Your Kinesis-to-ClickHouse® pipeline looked clean in the architecture review. Real-time events flowing from producers. Fast analytics. Sub-second dashboards. The proof-of-concept worked perfectly with test traffic.

Then you hit production load.

Now some shards throttle while others sit idle. Consumer lag grows unpredictably during resharding. Firehose introduces mysterious 5-minute delays. ClickHouse query performance degrades as tiny inserts create thousands of parts. Your pipeline oscillates between "working fine" and "completely broken" with no obvious pattern.

The problem isn't that Kinesis and ClickHouse are incompatible. **Most teams fundamentally misunderstand that this integration has no single "right answer"—only trade-offs between latency, replay capability, operational complexity, and delivery guarantees.**

ClickHouse integration with Amazon Kinesis follows three viable architectural paths: **managed ingestion via ClickPipes**, **Kinesis → Firehose → S3 → ClickHouse for replay-friendly pipelines**, and **Kinesis → Kafka → ClickHouse when your ecosystem already runs Kafka**. Each approach radically shifts where operational burden lives, what latency you can achieve, and how you handle failures and reprocessing.

The choice isn't about features or performance in isolation—it's about **which failure modes you're equipped to debug and which guarantees you actually need**.

## **The Three Real Integration Paths (And Why Two Are Better)**

Before choosing connectors or tuning parameters, understand the fundamental architectural split and what each path optimizes for.

### **ClickPipes: Managed ingestion from Kinesis Data Streams**

If you're running ClickHouse Cloud, ClickPipes provides managed ingestion directly from Kinesis Data Streams. You configure AWS credentials, select the stream, choose starting offset and format, and the service handles consumption, transformation, and writing to ClickHouse tables. This approach ensures continuous ingestion and efficient handling of [streaming data](https://www.ibm.com/think/topics/streaming-data) for large-scale analytics workloads.

**What ClickPipes handles automatically:**

* Continuous consumption with offset tracking and checkpointing  
* Checkpoint persistence using KeeperMap (ClickHouse's distributed KV store)  
* Enhanced Fan-Out configuration for higher throughput and stability  
* Auto-scaling of consumer replicas within ClickHouse Cloud  
* IAM role-based authentication (no long-lived access keys)

**This is the path with the lowest operational overhead.** You don't maintain consumers, manage autoscaling, handle AWS SDK versions, or debug checkpoint corruption.

**When this works:**

* You want fast time-to-value without building consumer infrastructure  
* Your transformation logic is straightforward (filtering, type conversion, column mapping)  
* Your bottleneck is getting data into ClickHouse with quality, not building complex streaming logic

**When this doesn't work:**

* You need complex enrichment (joins with dimension tables, external API calls)  
* You require sophisticated routing by event type across multiple destinations  
* You need full control over batching, retry logic, and error handling  
* You're running self-managed ClickHouse (ClickPipes is ClickHouse Cloud only)

### **Kinesis → Firehose → S3 → ClickHouse: The replay-friendly pattern**

This is the **favorite pattern when you prioritize replay capability and operational simplicity** over absolute minimal latency.

**Architecture flow:**

1. Kinesis Data Streams receives events from producers  
2. Kinesis Data Firehose buffers and delivers to S3  
3. ClickHouse ingests from S3 via ClickPipes or batch loads

**Why this pattern dominates production pipelines:**

**S3 becomes your durable, replayable log.** If ClickHouse ingestion fails, you re-ingest from S3. If you change schema, you re-ingest from S3. If you discover a parsing bug that corrupted data for three days, you re-ingest those three days from S3.

**Firehose handles buffering and delivery.** You don't manage consumer fleets, checkpoint tables, or shard rebalancing. Firehose absorbs backpressure and retries delivery failures.

**Storage cost is low.** S3 is dramatically cheaper than running compute for always-on consumers. Compressed Parquet files in S3 cost pennies per TB-month.

**Trade-off: Latency from buffering.** Firehose buffers based on size (1-128 MiB) and time (60-900 seconds). **Whichever threshold hits first triggers delivery.** Default buffering (5 MiB, 300 seconds) introduces minutes of delay—acceptable for analytics dashboards, unacceptable for alerting or real-time personalization.

### **Kinesis → Kafka → ClickHouse: When your ecosystem is Kafka**

If your organization already operates Kafka or Confluent, **normalizing Kinesis to Kafka topics and using Kafka-ClickHouse integration** can make sense.

**Bridge options:**

* Confluent's Kinesis Source Connector reads from Kinesis and writes to Kafka topics  
* AWS Labs' Kinesis-Kafka Connector (works bidirectionally)  
* Custom consumers that publish to Kafka

**Once in Kafka:**

* Use ClickHouse Kafka table engine (native integration)  
* Use Kafka Connect Sink to ClickHouse (external connector)  
* Leverage Confluent Cloud managed connector

**Why this adds complexity:**

* Another infrastructure layer (Kafka cluster or Confluent Cloud)  
* Additional points for duplication or reordering  
* More operational burden (Kafka management, connector monitoring)

**Why teams do it anyway:**

* Unified ecosystem: CDC from databases, application events, logs all flow through Kafka  
* Mature tooling: Schema Registry, stream processing with Flink/ksqlDB, extensive connector ecosystem  
* Kafka's replay and retention model already understood by team

**Reality check:** One team told us: "We added Kafka between Kinesis and ClickHouse because 'we're a Kafka shop.' Six months later we realized we were operating two streaming systems for one pipeline. Migrated to ClickPipes and saved 20 hours/week."

Unless you have strong reasons (existing Kafka infrastructure, need Kafka-specific tooling, stream processing requirements), **directly integrating Kinesis and ClickHouse is simpler**.

## **Kinesis Fundamentals That Break Pipelines**

Most Kinesis-ClickHouse pipeline failures trace to misunderstanding Kinesis's resource model and limits.

### **Shard limits: 1 MB/s and 1,000 records/s per shard**

In provisioned mode (not on-demand), **each Kinesis shard supports:**

* **Write:** 1 MB/s OR 1,000 records/s (whichever you hit first)  
* **Read:** 2 MB/s OR 2,000 GetRecords API calls per second

**The brutal reality: small events hit the 1,000 records/s limit long before the 1 MB/s throughput limit.**

**Example breakdown:**

Your events average 500 bytes each:

* **Throughput perspective:** 1 MB/s \= 2,000 events/s per shard (plenty of headroom)  
* **Record count perspective:** 1,000 records/s per shard (hard limit)

**You're bottlenecked at 1,000 events/s per shard, not 2,000.**

**Shard calculation formula:**

required\_shards \= max(  
  throughput\_MB\_per\_sec / 1,  
  records\_per\_sec / 1000  
) \* safety\_margin

**For 50,000 events/s at 500 bytes each:**

* Throughput calculation: 25 MB/s → 25 shards  
* Record count calculation: 50,000 / 1,000 → **50 shards**  
* **You need 50 shards** (with 20% margin: 60 shards)

**Missing this calculation causes mysterious throttling** even when you're nowhere near throughput limits.

### **Hot shards: Why partition key choice is architecture**

Kinesis routes records to shards based on **partition key hash**. Poor partition key choice creates hot shards—one shard saturated while others idle.

**Common mistakes:**

**Low cardinality keys:**

\# BAD: Only 5 possible partition keys  
partition\_key \= event\_type  \# "click", "view", "purchase", "signup", "logout"

**Result:** All events of the same type go to the same shard. If 80% of events are "view", one shard handles 80% of traffic.

**Skewed distribution:**

\# BAD: One customer generates 70% of events  
partition\_key \= customer\_id

**Result:** That customer's events saturate one shard. Adding more shards doesn't help—that customer still maps to one shard via consistent hashing.

**Partition key best practices:**

**For order-sensitive events (per-entity ordering required):**

partition\_key \= str(user\_id)  \# or device\_id, session\_id

**Ensure entity distribution is reasonably uniform.** If you have 1,000 users and one user generates 60% of traffic, this doesn't work.

**For order-insensitive events (analytics, metrics):**

import random  
partition\_key \= f"{entity\_id}:{random.randint(0, 99)}"

**Spreads single entity across multiple shards.** You lose strict ordering per entity but distribute load evenly.

**For high-cardinality uniform distribution:**

import hashlib  
partition\_key \= hashlib.md5(f"{event\_id}{timestamp}".encode()).hexdigest()

**Ensures even distribution** across all shards regardless of event characteristics.

### **Record aggregation with KPL (and the deaggregation trap)**

If you're hitting 1,000 records/s per shard with small events, **Kinesis Producer Library (KPL) aggregation** is the solution.

**How KPL aggregation works:**

* Packs multiple logical records into a single physical Kinesis record  
* Uses Protocol Buffers format for efficient packing  
* **Effectively bypasses the 1,000 records/s limit** by treating aggregated records as single records

**Example:**

* 50 logical events of 200 bytes each  
* Aggregated into 1 Kinesis record of \~10 KB  
* **Write limit now 1,000 aggregated records/s \= 50,000 logical events/s per shard**

**The trap: Consumer must deaggregate.**

If your consumer doesn't deaggregate KPL records, **it sees binary Protocol Buffer blobs that look like corrupted data**. This breaks JSON parsers, CSV parsers, and any format-specific processing.

**Who handles deaggregation automatically:**

* Kinesis Client Library (KCL) in Java, Python, Node.js  
* AWS Lambda with Kinesis trigger (built-in deaggregation)

**Who doesn't:**

* Custom consumers using raw AWS SDK GetRecords  
* Third-party connectors that don't explicitly support KPL format  
* ClickHouse Kafka table engine if you bridge Kinesis → Kafka without deaggregation step

**If you use KPL aggregation, verify your entire pipeline supports it.** One team spent two days debugging "corrupted Kinesis data" before realizing their custom consumer wasn't deaggregating.

## **ClickPipes for Kinesis: The Managed Path**

For teams on ClickHouse Cloud, ClickPipes eliminates most operational complexity.

### **Automatic offset management and checkpointing via KeeperMap**

**ClickPipes handles Kinesis shard consumption automatically:**

* Tracks sequence numbers per shard  
* Stores checkpoints in KeeperMap (ClickHouse's distributed key-value store backed by ClickHouse Keeper)  
* Recovers from failures by resuming from last checkpoint  
* Handles shard splits and merges during resharding

**You don't manage:**

* DynamoDB tables for lease tracking  
* Consumer rebalancing logic  
* Checkpoint corruption or recovery  
* Shard assignment across consumer instances

**One customer:** "We deleted 800 lines of KCL consumer code and a DynamoDB table. ClickPipes handled it all. Our checkpoint-related on-call incidents went to zero."

### **Enhanced Fan-Out for high-throughput stability**

Kinesis Enhanced Fan-Out provides **dedicated 2 MB/s read throughput per consumer** instead of shared 2 MB/s per shard.

**Why this matters at scale:**

**Without Enhanced Fan-Out (shared throughput):**

* 10 consumers reading from 1 shard share 2 MB/s  
* Each consumer gets \~200 KB/s  
* GetRecords polling introduces latency and contention

**With Enhanced Fan-Out:**

* Each consumer gets dedicated 2 MB/s per shard  
* Push-based delivery (SubscribeToShard) instead of polling  
* Lower latency and more predictable performance

**ClickPipes supports Enhanced Fan-Out configuration.** For high-throughput pipelines (hundreds of MB/s ingestion rate), this prevents read-side bottlenecks.

### **When managed ingestion beats custom consumers**

**ClickPipes wins when:**

* Your team has limited AWS streaming expertise  
* You want to ship analytics features, not operate consumers  
* Your transformations are straightforward (field mapping, filtering, type conversion)  
* You value managed operations over absolute control

**Custom consumers win when:**

* You need complex enrichment (external API calls, joins with dimension tables)  
* You have existing consumer infrastructure (Lambda, ECS, Kubernetes)  
* You require custom batching or retry logic  
* You're on self-managed ClickHouse (ClickPipes is Cloud-only)

**The hidden cost of custom consumers:** One SaaS company calculated they spent 12 engineering hours per week managing their custom Kinesis consumer (debugging lease issues, tuning batch sizes, handling resharding failures). ClickPipes recovered those 12 hours for product work.

## **The Firehose → S3 → ClickHouse Pattern**

For teams that prioritize replay capability and simple operations over minimal latency, **this pattern is remarkably robust**.

### **Buffering mechanics: Size and interval control latency**

Firehose buffers records before delivering to S3. **Delivery happens when size OR interval threshold is reached, whichever comes first.**

**Configurable parameters:**

* **Buffer size:** 1-128 MiB (default often 5 MiB)  
* **Buffer interval:** 60-900 seconds (default often 300 seconds)

**Latency calculation for near-real-time analytics:**

**Scenario 1: High-volume stream**

* Events: 10 MB/s  
* Buffer size: 5 MiB  
* **Delivery every \~500ms** (size threshold reached quickly)  
* Total latency: \< 1 second to S3

**Scenario 2: Low-volume stream**

* Events: 100 KB/s  
* Buffer size: 5 MiB  
* Buffer interval: 300 seconds  
* **Delivery every 300 seconds** (interval threshold reached)  
* Total latency: 5+ minutes to S3

**For sub-minute freshness and maintaining [low latency](https://www.cisco.com/site/us/en/learn/topics/cloud-networking/what-is-low-latency.html):**

buffer\_size \= 1-8 MiB  
buffer\_interval \= 60-120 seconds

**Trade-off:** Smaller buffers and shorter intervals create more S3 objects. More objects mean:

* Higher S3 PUT request costs  
* More overhead in ClickHouse ingestion (more small files to process)  
* Less efficient compression (smaller Parquet files compress worse)

**Optimal balance for most use cases:**

* Buffer size: 3-5 MiB  
* Buffer interval: 90-120 seconds  
* Format: Parquet with Snappy compression

### **Why S3 becomes your durable log**

**The replay advantage is massive:**

**Schema evolution:** You discover a new field buried in JSON payloads that should have been extracted as a column. With S3 as source, you re-ingest historical data with the new schema. **Without S3, that data is lost forever.**

**Bug fixes:** A parsing error corrupted three days of data. Re-ingest those three days from S3 with the fixed parser. **Without S3, the corrupted data is permanent.**

**Backfills:** You add a new analytics table or materialized view. Re-ingest 30 days of history from S3 to populate it. **Without S3, you only have data going forward.**

**ClickHouse cluster migrations:** Moving to new cluster architecture? Re-ingest from S3. **Without S3, you're stuck with live cutover risk.**

**S3 storage cost: $0.023 per GB-month** (Standard tier). For 1 TB of compressed events: **$23/month**. That's negligible compared to the engineering time saved on data recovery and backfills.

### **Parquet formatting and partition strategy**

**Format choice matters for cost and performance:**

**JSON (uncompressed or gzip):**

* Simple to produce and debug  
* 5-10x larger than Parquet  
* Slower ClickHouse parsing (CPU-intensive JSON parsing)

**Parquet with Snappy compression:**

* 80-90% smaller than JSON  
* Columnar format aligns with ClickHouse internals  
* Faster ingestion (less CPU for parsing, less network bandwidth)

**S3 partition strategy:**

s3://bucket/events/  
  year=2025/  
    month=01/  
      day=20/  
        hour=14/  
          part-00001.parquet  
          part-00002.parquet

**Benefits:**

* Selective replay (re-ingest just Jan 15th without scanning entire bucket)  
* Lifecycle policies (delete data older than 90 days automatically)  
* Parallel ingestion (ClickHouse can read multiple partitions concurrently)

**Firehose supports dynamic partitioning** using record fields to organize S3 output paths automatically.

## **Building Custom Consumers That Don't Break**

For self-managed ClickHouse or requirements beyond what ClickPipes provides, **you're building a custom consumer**. Here's what actually matters.

### **KCL, leases, and DynamoDB checkpointing**

**Kinesis Client Library (KCL) solves four hard problems:**

1. **Shard distribution:** How multiple consumer instances divide work across shards  
2. **Lease management:** Which instance owns which shard (stored in DynamoDB table)  
3. **Checkpointing:** Where in each shard the consumer has processed up to  
4. **Resharding adaptation:** Detecting shard splits/merges and reassigning work

**Without KCL, you're reimplementing all of this.** Most teams underestimate the complexity.

**KCL lease table in DynamoDB:**

* One record per shard  
* Tracks owner (which consumer instance), checkpoint (sequence number), and lease expiration  
* KCL workers heartbeat leases and steal expired leases for rebalancing

**Checkpoint timing is critical:**

**Checkpoint too early (before processing):**

* Failure after checkpoint but before insert → **data loss**

**Checkpoint too late (after successful processing but before commit):**

* Failure before checkpoint → **duplicates on retry**

**Correct pattern:**

\# Pseudocode  
for batch in kinesis\_stream:  
    insert\_into\_clickhouse(batch)  \# with deduplication token  
    checkpoint\_sequence(batch.last\_sequence\_number)

**ClickHouse insert must be idempotent** (more on this below).

### **Resharding survival: Why pipelines fail during shard splits**

Kinesis allows changing shard count (split shards to scale up, merge shards to scale down). **KCL is designed to handle this, but edge cases break pipelines.**

**Reported failure mode:**

During resharding, **KCL creates new leases for child shards with initial position set to TRIM\_HORIZON** (oldest available data), even though the parent shard was already processed up to LATEST.

**Result:** Consumers suddenly reprocess hours or days of old data, creating massive duplicates.

**Mitigation:**

* Test resharding in staging with realistic data volumes  
* Monitor checkpoint positions during and after resharding  
* Use `AFTER_SEQUENCE_NUMBER` positioning when creating child shard leases

**One team:** "We resharded from 20 to 40 shards during a load test. Suddenly saw 8 hours of duplicate events. Spent two days debugging lease creation logic before finding the TRIM\_HORIZON issue."

### **Batching strategy: 10k-100k rows per insert**

ClickHouse performs best with **large batch inserts** (10,000 to 100,000 rows), not thousands of tiny inserts.

**Why tiny inserts kill ClickHouse:**

* Each insert creates at least one part on disk  
* Too many parts trigger constant background merges  
* Merges consume I/O and CPU, degrading query performance  
* Query latency increases as queries scan thousands of small parts

**Consumer batching logic:**

BATCH\_SIZE \= 50000  
BATCH\_TIMEOUT \= 5  \# seconds

batch \= \[\]  
last\_flush \= time.time()

for record in consume\_kinesis():  
    batch.append(record)  

    if len(batch) \>= BATCH\_SIZE or (time.time() \- last\_flush) \> BATCH\_TIMEOUT:  
        insert\_to\_clickhouse(batch)  
        checkpoint\_kinesis(record.sequence\_number)  
        batch \= \[\]  
        last\_flush \= time.time()

**Balance:**

* Large batches \= fewer inserts, better ClickHouse performance  
* Small batches \= lower latency, higher ingestion overhead

**For most streaming analytics: 10k-50k rows per batch with 5-10 second timeout.**

## **Delivery Guarantees: At-Least-Once Reality**

Despite what some documentation implies, **Kinesis-to-ClickHouse pipelines are fundamentally at-least-once systems**.

### **Why exactly-once is a myth in Kinesis pipelines**

**Failure modes that cause duplicates:**

**Producer retries:**

* Producer sends record  
* Kinesis receives and stores it  
* Network failure before acknowledgment reaches producer  
* Producer retries → **duplicate record in Kinesis**

**Consumer checkpointing:**

* Consumer processes batch and inserts to ClickHouse successfully  
* Consumer crashes before checkpointing  
* New consumer instance starts from last checkpoint → **reprocesses and re-inserts batch**

**Kinesis shard iterator expiration:**

* Consumer stops polling for \> 5 minutes  
* Shard iterator expires  
* Consumer reconnects and gets new iterator from last checkpoint → **potential duplicates**

**You cannot eliminate these failure modes without distributed transaction coordination** (which Kinesis doesn't provide).

### **Idempotent inserts with deduplication tokens**

**ClickHouse provides insert deduplication** for MergeTree tables to handle retry scenarios.

**How it works:**

* Each insert is assigned a deduplication token (hash of block content or explicit token)  
* ClickHouse stores recent tokens in a deduplication window  
* If the same token appears within the window, insert is skipped

**Enable deduplication for non-replicated MergeTree:**

SET insert\_deduplicate \= 1;

**For replicated tables, deduplication is enabled by default.**

**Using explicit deduplication tokens:**

import hashlib

def insert\_batch(batch, shard\_id, sequence\_range):  
    \# Create deterministic token from batch identity  
    token \= hashlib.sha256(  
        f"{shard\_id}:{sequence\_range}:v1".encode()  
    ).hexdigest()  

    clickhouse\_client.execute(  
        f"INSERT INTO events SETTINGS insert\_deduplication\_token='{token}'",  
        batch  
    )

**Critical limitation: Deduplication window is finite** (default 100 blocks or configurable). If many inserts occur between original and retry, the token may fall out of the window → **duplicate inserts succeed**.

**For high-concurrency pipelines, increase window:**

SET max\_insert\_delayed\_streams\_for\_parallel\_write \= 1000;

### **ReplacingMergeTree for eventual deduplication**

If insert-level deduplication isn't sufficient, **use ReplacingMergeTree to deduplicate at the data model level**.

CREATE TABLE events  
(  
    event\_id String,  
    event\_time DateTime64(3),  
    user\_id String,  
    event\_type LowCardinality(String),  
    properties String,  
    version DateTime64(3)  \-- For tie-breaking  
)  
ENGINE \= ReplacingMergeTree(version)  
ORDER BY (event\_type, event\_id, event\_time);

**How it works:**

* Multiple rows with same ORDER BY key (event\_type, event\_id, event\_time)  
* During background merges, ClickHouse keeps only the row with highest `version` value  
* Deduplication is **eventually consistent** (happens during merges, not immediately)

**Query implications:**

**Without FINAL modifier:**

SELECT count() FROM events WHERE event\_type \= 'purchase';  
\-- May include duplicates if merge hasn't completed

**With FINAL modifier:**

SELECT count() FROM events FINAL WHERE event\_type \= 'purchase';  
\-- Forces deduplication at query time (expensive for large scans)

**Practical pattern:** Accept eventual deduplication for most queries. Use FINAL for critical metrics or build deduplicated materialized views.

## **Asynchronous Inserts: The Critical ClickHouse Setting**

**The number one mistake in Kinesis-to-ClickHouse pipelines: bombing ClickHouse with thousands of tiny inserts.**

### **async\_insert prevents merge explosions**

Asynchronous inserts move batching to the server side:

* Client sends insert → server responds immediately after buffering  
* Server accumulates inserts in memory  
* Flushes to disk based on thresholds (size, time, number of inserts)

**Enable asynchronous inserts:**

SET async\_insert \= 1;  
SET wait\_for\_async\_insert \= 0;  \-- Don't wait for persistence

**Flush thresholds:**

SET async\_insert\_max\_data\_size \= 10000000;  \-- 10 MB  
SET async\_insert\_busy\_timeout\_ms \= 1000;     \-- 1 second

**With async inserts enabled:**

* 1,000 tiny inserts from consumers → batched into \~10 large inserts by server  
* Fewer parts created on disk  
* Less merge pressure  
* Better query performance

### **wait\_for\_async\_insert=0 and acknowledgment semantics**

**`wait_for_async_insert = 0`:** Client receives acknowledgment when data is **buffered in memory**, not when persisted to disk.

**Trade-off:**

* **Lower latency:** Insert returns in milliseconds instead of waiting for disk write  
* **Acknowledgment doesn't guarantee durability:** Server crash before flush → data loss

**For Kinesis consumers with checkpointing:**

**Aggressive acknowledgment (wait=0):**

insert\_to\_clickhouse(batch, wait\_for\_async\_insert=0)  
checkpoint\_kinesis()  \# Checkpoint immediately

**Risk:** Server crash between acknowledgment and flush → data loss (mitigated by Kinesis replay from checkpoint)

**Conservative acknowledgment (wait=1):**

insert\_to\_clickhouse(batch, wait\_for\_async\_insert=1)  
checkpoint\_kinesis()  \# Only checkpoint after persistence

**Benefit:** No data loss on server crash  
 **Cost:** Higher latency (wait for disk write before checkpointing)

**Most production pipelines use `wait=0` and rely on Kinesis replay for crash recovery.**

### **Flush thresholds that balance latency and parts**

**Tuning async insert flush behavior:**

\-- Flush after accumulating 100 MB (large batches)  
SET async\_insert\_max\_data\_size \= 100000000;

\-- Or flush after 5 seconds (low latency)  
SET async\_insert\_busy\_timeout\_ms \= 5000;

\-- Or flush after 1000 queries accumulated  
SET async\_insert\_max\_query\_number \= 1000;

**High-throughput pipeline (optimize for parts):**

* `max_data_size = 100 MB`  
* `busy_timeout = 10 seconds`  
* Accept 10-second flush latency to create larger parts

**Low-latency pipeline (optimize for freshness):**

* `max_data_size = 10 MB`  
* `busy_timeout = 1 second`  
* Create more parts but get sub-second visibility

**The right balance depends on your query patterns and ingestion rate.**

## **Table Design for Streaming Events**

Schema and storage engine choice dramatically impact both ingestion performance and query latency.

### **Partitioning by date for maintenance and drops**

**Partition by date for time-series event data:**

CREATE TABLE events  
(  
    event\_time DateTime64(3),  
    ingest\_time DateTime64(3),  
    user\_id String,  
    session\_id String,  
    event\_type LowCardinality(String),  
    properties String  
)  
ENGINE \= MergeTree  
PARTITION BY toYYYYMMDD(event\_time)  
ORDER BY (event\_type, event\_time, user\_id);

**Why partition by date:**

**Fast historical data drops:**

ALTER TABLE events DROP PARTITION '20250101';  
\-- Deletes entire partition instantly (metadata operation)

**Better than:**

DELETE FROM events WHERE event\_time \< '2025-01-01';  
\-- Triggers mutations, rewrites parts, slow and expensive

**Partition-level operations:**

* Backup specific date ranges  
* Move old partitions to cheaper storage  
* Reprocess individual days without affecting others

**Partition granularity:**

* Daily: 365 partitions per year (good for most use cases)  
* Hourly: 8,760 partitions per year (for very high volume)  
* Monthly: 12 partitions per year (for lower volume or long retention)

### **ORDER BY aligned with query patterns**

**The ORDER BY key determines:**

* Data locality on disk (related rows stored together)  
* Primary index structure  
* Query filtering performance

**Design ORDER BY for your most common query patterns:**

**Product analytics example:**

\-- Common queries filter by event\_type then time range  
ORDER BY (event\_type, toDate(event\_time), user\_id)

**Observability/logs example:**

\-- Common queries filter by service and severity  
ORDER BY (service, severity, event\_time)

**User behavior analysis example:**

\-- Common queries aggregate per user over time  
ORDER BY (user\_id, event\_time, event\_type)

**Anti-pattern: Too many columns in ORDER BY**

\-- BAD: 6 columns in ORDER BY  
ORDER BY (event\_type, user\_id, session\_id, device\_id, country, event\_time)  
\-- Creates high cardinality, poor compression, slower inserts

**Rule of thumb: 2-4 columns, highest cardinality last.**

### **Materialized views for pre-aggregation**

**Don't make dashboards compute aggregations on billions of raw events every query.**

**Pattern: Raw ingestion table \+ aggregated serving tables**

\-- Raw events (append-only, minimal ORDER BY)  
CREATE TABLE events\_raw  
(  
    event\_time DateTime64(3),  
    user\_id String,  
    event\_type LowCardinality(String),  
    revenue Decimal(10,2)  
)  
ENGINE \= MergeTree  
ORDER BY (event\_time);

\-- Pre-aggregated metrics per minute  
CREATE TABLE events\_by\_minute  
(  
    minute DateTime,  
    event\_type LowCardinality(String),  
    event\_count SimpleAggregateFunction(sum, UInt64),  
    revenue\_sum SimpleAggregateFunction(sum, Decimal(10,2))  
)  
ENGINE \= AggregatingMergeTree  
ORDER BY (event\_type, minute);

\-- Materialized view populates aggregates automatically  
CREATE MATERIALIZED VIEW mv\_events\_by\_minute  
TO events\_by\_minute  
AS SELECT  
    toStartOfMinute(event\_time) AS minute,  
    event\_type,  
    sumSimpleState(1) AS event\_count,  
    sumSimpleState(revenue) AS revenue\_sum  
FROM events\_raw  
GROUP BY minute, event\_type;

**Benefits:**

* Dashboard queries hit small aggregated table (millions of rows) instead of raw table (billions of rows)  
* Sub-second query latency regardless of data volume  
* Aggregates update in real-time as data arrives

**One customer:** "Our dashboard went from 8-second queries on raw events to 40ms queries on materialized aggregates. Same metrics, 200x faster."

While ClickHouse excels at fast ingestion and querying, traditional [data warehouses](https://www.tinybird.co/blog/why-data-warehouses) often struggle to achieve similar performance for real-time workloads. Understanding these differences helps teams design more efficient analytics architectures.

## **How Tinybird Eliminates Kinesis Integration Complexity**

Everything discussed—consumer management, checkpoint tables, batching logic, async insert tuning, merge management, deduplication strategies—requires deep infrastructure expertise and ongoing operational overhead.

**Tinybird eliminates this complexity entirely for Kinesis-to-ClickHouse pipelines.**

### **Native Kinesis connectors with automatic management**

**No custom consumers. No KCL. No lease tables. No checkpoint logic.**

Tinybird provides native Kinesis Data Streams connectors that handle:

* Automatic shard consumption and load balancing  
* Checkpoint persistence without DynamoDB tables  
* Resharding detection and adaptation  
* Enhanced Fan-Out configuration for high-throughput streams  
* Exactly-once ingestion semantics built-in

**You point Tinybird at your Kinesis stream and define the schema. Everything else is automatic.**

One customer: "We were spending 15 hours/week managing KCL consumers, debugging checkpoint issues, and tuning batch sizes. With Tinybird, that operational burden disappeared. Zero Kinesis-related incidents in six months."

### **No merge explosions, no async insert tuning**

**The silent killer of Kinesis-ClickHouse pipelines: tiny inserts creating thousands of parts.**

Traditional approaches require:

* Careful batching logic in consumers (10k-100k rows per insert)  
* Async insert configuration (`wait_for_async_insert`, flush thresholds)  
* Monitoring part count and merge queue depth  
* Manual intervention when merges fall behind

**Tinybird handles batching and merge management automatically:**

* Intelligent batching based on traffic patterns (no manual tuning)  
* Automatic merge optimization without configuration  
* Zero manual intervention for part count management  
* Consistent sub-second ingestion regardless of event rate

### **Sub-second ingestion without operational burden**

**Traditional Kinesis-ClickHouse architecture:**

* KCL consumer fleet (ECS, Kubernetes, or Lambda)  
* Lease management in DynamoDB  
* Custom batching and retry logic  
* ClickHouse async insert configuration  
* Monitoring for lag, checkpoints, merges  
* On-call rotation for consumer failures

**Tinybird architecture:**

* Define Kinesis Data Source in UI  
* Configure schema and authentication  
* Data flows automatically with sub-second latency  
* Built-in monitoring and alerting  
* Zero infrastructure to operate

**Latency comparison:**

**Self-managed pipeline:**

* Kinesis → Custom consumer → Batching → ClickHouse  
* Median: 3-8 seconds (depends on batching configuration)  
* P99: 15-30 seconds (batch timeout edge cases)

**Tinybird:**

* Kinesis → Tinybird Data Source → Automatic ingestion  
* Median: 500-800ms  
* P99: 1-2 seconds

### **SQL to API instantly**

The entire point of Kinesis-to-ClickHouse integration is **making streaming data queryable for applications and dashboards**.

With traditional ClickHouse, after solving ingestion, you still need:

* API layer to expose queries  
* Authentication and authorization  
* Query parameter validation  
* Response caching strategies  
* Multi-tenant data isolation  
* API monitoring and rate limiting

**Tinybird transforms SQL queries into production APIs with zero additional code:**

\-- Write SQL query with parameters  
SELECT  
    toStartOfMinute(event\_time) AS minute,  
    event\_type,  
    count() AS event\_count  
FROM events  
WHERE event\_time \>= now() \- INTERVAL {{Int32(hours, 24)}} HOUR  
  AND user\_id \= {{String(user\_id, required=True)}}  
GROUP BY minute, event\_type  
ORDER BY minute DESC

**Becomes:**

GET /api/v0/pipes/user\_events.json?hours=24\&user\_id=abc123

**Built-in features:**

* Type-safe query parameters with validation  
* Authentication via API tokens  
* Automatic caching at multiple layers  
* Sub-100ms query latency for pre-aggregated data  
* Per-tenant data isolation  
* Real-time monitoring and query profiling

**No API service to build, deploy, or maintain.**

### **Real production example: IoT analytics at scale**

One [Internet of Things (IoT)](https://www.ibm.com/think/topics/internet-of-things) company processing 100M Kinesis events per day:

**Before Tinybird (self-managed ClickHouse):**

* 4 engineers managing Kinesis consumers and ClickHouse  
* KCL consumer fleet on ECS with autoscaling  
* DynamoDB lease table \+ monitoring  
* Custom batching logic (2,000 lines of Python)  
* Async insert tuning and merge monitoring  
* Custom API layer (Express.js, 3,000+ lines)  
* Median query latency: 1.2s (P99: 6s)

**After Tinybird:**

* Zero Kinesis consumer infrastructure  
* Zero DynamoDB tables for leases  
* Zero custom batching code  
* APIs auto-generated from SQL  
* Median query latency: 65ms (P99: 180ms)

**Engineering time recovered: 20+ hours/week** from Kinesis operations to product development.

**Infrastructure cost reduction: 40%** (eliminated ECS cluster, DynamoDB tables, API service).

The fundamental insight: **Most teams don't want to operate Kinesis-ClickHouse integration—they want real-time analytics from streaming data.** Tinybird provides the latter without requiring you to become an expert in KCL, checkpoint management, or merge tuning.

Ultimately, the strength of Tinybird lies in its seamless handling of [real-time data processing](https://www.tinybird.co/blog/real-time-data-processing) — bridging the gap between ingestion, transformation, and analytics with minimal operational overhead.

## **Choose Based on Replay Requirements and Latency Budget**

ClickHouse integration with Amazon Kinesis works. Hundreds of companies process billions of events daily through these pipelines.

But **"works" and "operationally sustainable" are different things.**

**Managed ingestion complexity (ClickPipes):**

* Requires ClickHouse Cloud (not available for self-managed)  
* Limited to transformation capabilities built into ClickPipes  
* Less control over batching and retry logic  
* **Benefit:** Zero consumer infrastructure or checkpoint management

**Firehose → S3 → ClickHouse complexity:**

* Buffering introduces latency (minutes, not seconds)  
* Multiple moving parts (Firehose, S3, ingestion)  
* **Benefit:** Replay capability and simple operations

**Custom consumer complexity:**

* KCL lease management and checkpoint tables  
* Resharding survival requires careful testing  
* Batching and async insert tuning  
* Merge explosion prevention  
* **Benefit:** Maximum control and customization

**Traditional path:** Hire engineers with deep Kinesis and ClickHouse expertise. Build consumer infrastructure. Accept operational burden as the cost of real-time analytics.

**Tinybird path:** Native Kinesis connectors with automatic management. Zero consumer infrastructure. Sub-second ingestion without tuning. SQL queries become APIs instantly.

For teams building Kinesis-to-ClickHouse pipelines: **choose based on your operational capacity and what you want engineers focused on**. Debugging KCL lease issues and tuning merge settings? Or building analytics features customers care about?

The choice is yours. For most teams, spending weeks mastering resharding edge cases isn't the goal—shipping [real-time analytics](https://www.tinybird.co/blog/real-time-analytics-a-definitive-guide) products is.