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 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:

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 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) 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 — 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 products is.