Your data pipeline looked solid on paper. AWS Glue processes data from S3, transforms it with Spark, and loads results into your analytics database. Clean ETL architecture. The data engineering team approved the design.

Then you moved to production analytics serving.

Dashboard queries that should return in milliseconds take 3-5 seconds. Real-time metrics lag by 15 minutes because of batch processing windows. Users complain about stale data during business hours. Your Glue jobs run constantly, burning through DPUs, yet analytics performance remains mediocre.

Sound familiar?

This is the Glue integration trap. What works brilliantly for batch ETL and data lake management breaks when you need real-time analytics serving with sub-second query performance.

The problem isn't that AWS Glue is poorly designed. Most teams misunderstand what Glue actually solves. Glue excels at orchestrating Spark transformations and managing data lake metadata. It wasn't built to serve dashboards, power customer-facing analytics, or deliver sub-100ms API responses at high concurrency.

ClickHouse® integration with Amazon Glue typically follows three patterns: (1) Glue as ETL loading ClickHouse for fast queries, (2) Glue Data Catalog as lakehouse metadata with ClickHouse querying Iceberg tables directly, and (3) hybrid architectures combining both. The right pattern depends on whether you need batch transformation infrastructure, unified metadata governance, or both.

Let's explore what actually works.

Pattern 1: Glue Spark ETL Loading ClickHouse for Fast Analytics

The most common pattern: AWS Glue runs Spark jobs transforming data and loading results into ClickHouse tables for low-latency serving.

Why this pattern exists

Glue excels at orchestrating complex transformations—joining datasets from multiple sources, enriching with external data, applying business logic, running ML models. ClickHouse excels at serving analytical queries with sub-second latency on billions of rows.

Glue provides transformation infrastructure. ClickHouse provides query performance.

Separate the layers: heavy batch processing in Spark, fast serving in ClickHouse.

How to connect Spark to ClickHouse

Two main approaches exist:

Spark native connector (recommended):

• DataSource V2 API integration with better parallelism
• Pushdown optimization for filters and aggregations
• Natural integration with Spark SQL and DataFrame APIs
• Optimized batch writes to ClickHouse

JDBC connector (simpler, less efficient):

• Standard Spark JDBC interface
• Works without additional dependencies
• Lower performance for large-scale reads/writes
• Easier for quick prototypes or small data volumes

The parallelization challenge

Default JDBC reads execute on single Spark task—entire table scanned serially. For large ClickHouse tables, this kills performance.

Force parallel reads with partitioning:

df = (
spark.read.format("jdbc")
.option("url", "jdbc:clickhouse://HOST:8123/DB")
.option("dbtable", "events")
.option("user", USER)
.option("password", PASS)
.option("partitionColumn", "event_date")
.option("lowerBound", "2025-01-01")
.option("upperBound", "2025-12-31")
.option("numPartitions", "64")
.load()
)

This creates 64 parallel tasks reading different date ranges simultaneously. Read time: 10 minutes → 30 seconds for billion-row table.

For writes, batch size matters enormously:

df.write \
.format("jdbc") \
.option("url", "jdbc:clickhouse://HOST:8123/DB") \
.option("dbtable", "target_table") \
.option("batchsize", "100000") \
.mode("append") \
.save()

ClickHouse performance depends heavily on insert batch size. Many small inserts (1000 rows each) create excessive parts requiring background merges. Larger batches (50,000-100,000 rows) reduce merge overhead and improve query performance.

In many deployments, ClickHouse effectively acts as the downstream system for analytics serving, absorbing curated outputs from Spark while isolating query workloads from upstream batch complexity. This separation clarifies ownership and SLAs between transformation and serving layers, a pattern discussed extensively in the context of a downstream system.

The staging table pattern for idempotency

Glue jobs sometimes rerun—failures, backfills, late-arriving data. Direct loading creates duplicate data or inconsistent state.

Production pattern:

1. Glue writes to staging table (append-only with batch metadata)
2. ClickHouse promotion query deduplicates and moves to final table
3. Staging table TTL removes old batches automatically

-- Staging table with batch tracking
CREATE TABLE events_staging (
event_id String,
user_id UInt64,
event_time DateTime,
batch_id String,
ingestion_time DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (batch_id, event_time);

-- Promotion to final table (deduplicated)
INSERT INTO events_final
SELECT event_id,
user_id,
event_time
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY event_id ORDER BY ingestion_time DESC
) as rn
FROM events_staging
WHERE batch_id = 'batch_2025_01_28'
)
WHERE rn = 1;

Rerun the same Glue job safely. Staging absorbs duplicates; promotion ensures idempotency.

When Glue ETL to ClickHouse makes sense

Choose this pattern when:

• Complex transformations require Spark (large joins, ML, heavy aggregations)
• Multiple source systems need unification before analytics serving
• Batch processing acceptable (hourly, daily loads) for your freshness requirements
• ClickHouse serving critical for dashboard and API performance
• Separation of concerns—transformation infrastructure (Glue) versus query serving (ClickHouse)

Don't choose this when:

• Real-time streaming required (Kafka → ClickHouse more direct)
• Simple transformations don't justify Spark overhead
• Continuous updates needed (Glue batch windows add staleness)

One team shared: "We process clickstream data in Glue—sessionization, attribution modeling, fraud detection. Transformed results load to ClickHouse hourly. Dashboards query ClickHouse in 50ms. Separating heavy batch work from serving solved our performance problem."

Pattern 2: Glue Data Catalog as Lakehouse Metadata, ClickHouse Querying Directly

The alternative pattern: Data lives in S3 as Iceberg tables cataloged in Glue Data Catalog. ClickHouse queries lake tables directly without duplicating data.

Why this pattern exists

In modern cloud computing, data lakes centralize storage with governance and compliance requirements. Multiple systems need access—Athena for ad-hoc queries, EMR for batch processing, ClickHouse for low-latency serving.

Duplicating data into ClickHouse wastes storage and creates synchronization problems. Query the lake directly when ClickHouse needs lake access.

How ClickHouse connects to Glue catalogs

ClickHouse provides DataLakeCatalog database engine discovering tables from external catalogs:

CREATE DATABASE glue_lake
ENGINE = DataLakeCatalog
SETTINGS catalog_type = 'glue',
region = 'us-east-1';

-- Discover tables
SHOW TABLES FROM glue_lake.analytics_db;

-- Query Iceberg table through catalog
SELECT event_date,
COUNT(*) as events
FROM glue_lake.analytics_db.user_events
WHERE event_date >= today() - 7
GROUP BY event_date;

ClickHouse reads Iceberg metadata (manifests, snapshots) from Glue catalog, then reads Parquet files directly from S3. No data duplication—single source of truth in S3.

The Iceberg format requirement

Critical limitation: Glue catalog integration in ClickHouse primarily supports Iceberg format. Don't assume Delta Lake, Hudi, or arbitrary Parquet work without validation.

Iceberg provides:

• ACID transactions on object storage
• Time travel through snapshot isolation
• Schema evolution without rewriting data
• Partition evolution changing partitioning scheme over time
• Metadata optimization for efficient query planning

If your lake uses other formats, either migrate to Iceberg or use Pattern 1 (ETL to ClickHouse tables).

Performance depends on lake health

ClickHouse query performance on Iceberg depends entirely on lakehouse maintenance:

Small files problem:

• Thousands of tiny Parquet files slow queries dramatically
• S3 list operations become bottleneck
• ClickHouse reads more files than necessary

Solution: Regular compaction merging small files into larger ones (target: 128MB-512MB per file).

Snapshot accumulation:

• Iceberg creates new snapshot per write operation
• Manifests accumulate over time
• Query planning reads excessive metadata

Solution: Expire old snapshots regularly—retain only needed for time travel and compliance.

Partition strategy:

• Poor partitioning forces full table scans
• ClickHouse can't prune effectively

Solution: Partition by query filter columns (date ranges, geographic regions, tenant IDs).

AWS Glue Data Catalog now supports storage optimization for Iceberg tables including automatic compaction and snapshot expiration policies. Configure these from the start.

Authentication and permissions complexity

Querying Iceberg through Glue requires proper AWS credentials:

ClickHouse needs:

• IAM role or access keys with S3 read permissions
• Glue Data Catalog permissions for metadata access
• Network connectivity to S3 and Glue API endpoints

If using Lake Formation:

• Row-level and column-level security policies
• Additional permissions management
• Integration with Lake Formation grants

Configuration example:

CREATE DATABASE glue_lake
ENGINE = DataLakeCatalog
SETTINGS catalog_type = 'glue',
region = 'us-east-1',
aws_access_key_id = '...',
aws_secret_access_key = '...',
-- Or use IAM role if ClickHouse runs on EC2
use_instance_profile = 1;

Common failure: Works in Athena but fails in ClickHouse due to missing IAM permissions or network access. Test connectivity thoroughly.

When querying Iceberg through Glue makes sense

Choose this pattern when:

• Data already lives in S3 as Iceberg with governance requirements
• Multiple consumers need same data (Athena, Spark, ClickHouse)
• Avoiding duplication matters for storage costs or compliance
• Exploratory queries on large infrequently-accessed datasets
• Lake Formation governance required across organization

Don't choose this when:

• Sub-100ms queries required (lakehouse adds latency versus native ClickHouse tables)
• High concurrency dashboard serving (S3 requests accumulate costs)
• Frequent access to same data (materialization in ClickHouse more efficient)

One team explained: "We keep raw events in S3 Iceberg for compliance—7 years retention required. ClickHouse queries recent months directly through Glue catalog. Older data accessed occasionally via Athena. Single source of truth without duplicating petabytes."

Pattern 3: Hybrid Architecture Combining Both Approaches

Most production systems combine patterns: lakehouse governance with selective materialization in ClickHouse.

The hybrid workflow

1. Raw data lands in S3 as Iceberg cataloged in Glue Data Catalog
2. Glue Spark jobs transform and curate datasets
3. Hot data materializes in ClickHouse for serving dashboards and APIs
4. Cold data remains in lake accessed occasionally through catalog
5. Glue provides unified metadata for both lake and ClickHouse tables

Incremental loading with Glue Job Bookmarks

Glue provides job bookmarks tracking processed data across runs—essential for incremental loads:

from awsglue.context import GlueContext
from awsglue.job import Job

glueContext = GlueContext(SparkContext.getOrCreate())
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# Read with bookmark support
datasource = glueContext.create_dynamic_frame.from_catalog(
database = "analytics",
table_name = "events",
transformation_ctx = "datasource" # Stable context for bookmarking
)

# Transform and write to ClickHouse
# Only new partitions processed on subsequent runs

Transformation context must remain stable across job executions. Changing it resets bookmark state, potentially reprocessing all data.

For S3 sources, bookmarks track new partitions (partition-based incremental). For JDBC sources, define bookmark key columns filtering new rows.

When hybrid architecture makes sense

Choose hybrid when:

• Compliance requires lake retention but serving requires speed
• Multiple analytical workloads with different latency requirements
• Governance and lineage managed centrally in Glue catalog
• Cost optimization—expensive ClickHouse storage for hot data only
• Flexibility—materialize datasets proving valuable for performance

Most enterprise deployments eventually arrive here. Start with one pattern, evolve to hybrid as requirements clarify.

How Tinybird Simplifies ClickHouse Integration

Everything discussed—Spark connectors, staging tables, incremental bookmarks, catalog integration, idempotency patterns—requires significant engineering to implement and maintain, a recurring challenge across real-time data platforms.

Tinybird provides managed ClickHouse with built-in data pipeline capabilities eliminating most Glue integration complexity.

Streaming ingestion without Glue jobs

While Glue excels at batch Spark transformations, most analytics workloads need continuous data ingestion, not hourly/daily batch windows.

Tinybird's streaming architecture:

• Kafka, webhooks, HTTP events flow continuously into ClickHouse
• Sub-second queryability from ingestion to query results
• No batch delays or Glue job scheduling
• Incremental materialized views maintain aggregations automatically

No Glue jobs for simple data movement. Stream directly to Tinybird, transform with SQL.

SQL transformations instead of Spark jobs

For many transformations, Spark is overkill. Joins, aggregations, filtering, enrichment execute efficiently in SQL. These datasets can further benefit from projections to precompute or reorganize data for faster query serving.

Tinybird workflow:

1. Ingest raw events continuously
2. SQL transformations define materialized views
3. Instant APIs serve results with sub-100ms latency

No Glue job development, testing, or deployment. Write SQL, get production data pipelines.

When Tinybird makes sense versus Glue integration

Choose Tinybird when:

• Real-time analytics required (seconds, not hours)
• Operational simplicity preferred over infrastructure control
• SQL transformations sufficient (90% of cases)
• Streaming ingestion from Kafka, webhooks, or events
• Sub-100ms serving for dashboards and APIs critical

A common outcome of sub-100ms APIs is customer-facing use cases such as recommendations, dynamic pricing, and real-time personalization, where fresh features and context must be computed and served continuously without batch windows.

Keep Glue when:

• Complex Spark transformations genuinely required (ML pipelines, heavy joins across disparate sources)
• Existing Glue investment and expertise justify continuation
• AWS ecosystem integration with EMR, Athena, SageMaker essential
• Lake governance through Glue catalog mandated organizationally

Many teams use both: Glue for complex batch transformations landing in S3 lake, Tinybird streaming subsets from Kafka for real-time analytics serving. Separate batch infrastructure from serving platforms.

The Path Forward: Architecture Matching Requirements

ClickHouse integration with Amazon Glue solves specific problems:

Glue Spark ETL → ClickHouse separates heavy batch transformations from fast query serving
Glue Data Catalog with Iceberg provides unified lakehouse metadata ClickHouse queries directly
Hybrid approaches combine governance, retention, and selective materialization

But these patterns require engineering investment:

• Connector configuration and parallelization tuning
• Staging tables and idempotency logic
• Job bookmark management for incrementals
• Lake maintenance (compaction, snapshot expiration)
• Authentication and permission coordination
• Monitoring and cost optimization

Tinybird provides purpose-built alternative for teams where:

• Real-time matters more than batch windows
• SQL transformations sufficient versus Spark complexity
• Operational simplicity preferred over infrastructure control
• Analytics serving (APIs, dashboards) is primary requirement

The choice is yours: build integration infrastructure connecting Glue and ClickHouse, or adopt platforms designed for real-time analytics serving from the start.

For analytics that stay fast as data scales—choose architectures built for the job.