ClickHouse Integration with Azure Synapse guide

Your data platform looked solid on paper. Azure Synapse orchestrates data transformations with Spark pools, manages data lake storage in ADLS Gen2, and provides SQL analytics capabilities. Clean lakehouse architecture with unified workspace. The data engineering team approved the design.

Then you deployed to production analytics serving.

Queries that should return in milliseconds take 5-8 seconds. Dashboard users complain about stale data because Synapse Spark jobs run hourly. Your Synapse SQL pools struggle with high concurrency workloads. Costs climb as you scale dedicated pools trying to achieve acceptable query performance.

Sound familiar?

This is the Synapse integration trap. What works brilliantly for batch data warehouse orchestration breaks when you need real-time analytics serving with sub-second query performance at scale.

The problem isn't that Azure Synapse is poorly designed. Most teams misunderstand what Synapse actually solves. Synapse excels at orchestrating complex ETL workflows with Spark, managing data lake governance, and providing SQL analytics over warehouse data. It wasn't built to serve customer-facing dashboards, power real-time APIs, or deliver sub-100ms responses at high concurrency—that's ClickHouse®'s job.

Critical reality: Synapse integrates with ClickHouse through multiple patterns, not a single native connector. Integration requires choosing between Spark connectors for ETL, data lake staging for governance, or HTTP interfaces for direct loading—each with distinct trade-offs.

ClickHouse integration with Azure Synapse typically follows three patterns: (1) Synapse Spark transforming data and loading ClickHouse via native connector, (2) ADLS Gen2 as staging layer with ClickHouse reading via table functions, and (3) Synapse Pipelines pushing data to ClickHouse HTTP interface. The right pattern depends on transformation complexity, network topology, governance requirements, and operational maturity.

Let's explore what actually works in production.

Pattern 1: Synapse Spark with ClickHouse Native Connector

The most integrated pattern for complex transformations: Synapse Spark pools execute ETL jobs and load results into ClickHouse using native Spark connector.

Why this pattern exists

Synapse Spark excels at complex transformations—joining datasets across sources, applying business logic, running ML models, aggregating at scale. ClickHouse excels at serving analytical queries with low latency (often sub-second).

Synapse provides transformation infrastructure. ClickHouse provides query serving performance.

Separate heavy batch processing (Spark) from fast query serving (ClickHouse).

How to configure Spark connector in Synapse

Step 1: Add connector dependencies to Spark pool

Synapse allows library management at pool and workspace level. Add ClickHouse Spark connector JARs:

• ClickHouse Spark connector runtime
• ClickHouse JDBC driver (all-in-one JAR)

Configure via Synapse workspace packages or pool-level library management:

{
"name": "clickhouse-spark-runtime",
"coordinates": "com.clickhouse.spark:clickhouse-spark-runtime-3.3_2.12:0.7.3"
}

Step 2: Register ClickHouse as Spark catalog

Configure catalog connection in notebook session:

%%configure -f
{
"conf": {
"spark.sql.catalog.clickhouse": "com.clickhouse.spark.ClickHouseCatalog",
"spark.sql.catalog.clickhouse.host": "clickhouse.example.com",
"spark.sql.catalog.clickhouse.protocol": "https",
"spark.sql.catalog.clickhouse.http_port": "8443",
"spark.sql.catalog.clickhouse.user": "default",
"spark.sql.catalog.clickhouse.password": "{{ secrets.clickhouse_password }}"
}
}

Never hardcode credentials. Use Azure Key Vault integration with Synapse for password management.

Step 3: Read and write DataFrames

Read from ClickHouse:

df = spark.read \
.format("clickhouse") \
.option("host", "clickhouse.example.com") \
.option("protocol", "https") \
.option("http_port", "8443") \
.option("user", "default") \
.option("password", password) \
.option("database", "analytics") \
.option("table", "events") \
.load()

Write to ClickHouse:

transformed_df.write \
.format("clickhouse") \
.option("host", "clickhouse.example.com") \
.option("protocol", "https") \
.option("http_port", "8443") \
.option("user", "default") \
.option("password", password) \
.option("database", "analytics") \
.option("table", "events_aggregated") \
.option("batchSize", "200000") \
.option("compression", "lz4") \
.mode("append") \
.save()

Performance tuning for Spark connector

Critical settings affecting throughput:

batchSize: Rows per write batch

• Default: Often too small (10,000-50,000)
• Recommended: 100,000-500,000 rows
• Impact: Larger batches reduce ClickHouse insert overhead and parts created

DataFrame partitioning before write:

# Repartition before writing to control parallelism
df_repartitioned = transformed_df.repartition(8)

df_repartitioned.write \
.format("clickhouse") \
.option("batchSize", "200000") \
.mode("append") \
.save()

Number of partitions = concurrent connections to ClickHouse. Too many small partitions create connection overhead; too few underutilize parallelism.

Compression codec selection:

• LZ4: Best balance for throughput (fast compression, decent ratio)
• ZSTD: Better compression ratio, slower (use when network bandwidth limited)
• None: Only when ClickHouse and Spark in same region with unlimited bandwidth

Schema design in ClickHouse for Spark writes

Don't just dump Spark output to arbitrary ClickHouse tables. Design tables for query patterns:

CREATE TABLE events_aggregated (
event_date Date,
event_hour UInt8,
user_id String,
event_count UInt64,
revenue Decimal(18,2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_hour, user_id);

Partition by date for query pruning and TTL management.

ORDER BY matching filter columns in queries—sparse primary index efficiency depends on this.

Avoid high-cardinality ORDER BY (random UUIDs, detailed timestamps with milliseconds) unless specifically required.

Type mapping considerations

Spark types → ClickHouse types require careful mapping:

Timestamps:

• Spark: TimestampType (microsecond precision)
• ClickHouse: DateTime (second) or DateTime64(3) (millisecond)
• Specify precision explicitly to avoid truncation surprises

Decimals:

• Spark: DecimalType(precision, scale)
• ClickHouse: Decimal(precision, scale) with P ≤ 76
• Validate precision limits before migration

Nullability:

• Spark: Columns nullable by default
• ClickHouse: Requires explicit Nullable(Type)
• Schema drift occurs when Spark nullable columns map to non-nullable ClickHouse columns

Test with representative sample data before full historical loads.

When Spark connector makes sense

Choose Synapse Spark connector when:

• Complex transformations require Spark capabilities (large joins, ML, aggregations)
• Multiple source integration needed (ADLS, Azure SQL, Cosmos DB, external APIs)
• Team expertise in Spark strong
• Batch processing acceptable (hourly, daily loads) for freshness requirements
• Programmatic control over partitioning and batching needed

Don't choose this when:

• Simple data movement without transformation (use Pattern 2 or 3)
• Real-time streaming required (Kafka → ClickHouse more direct)
• Network restrictions prevent Spark reaching ClickHouse (see private networking section)

One team shared: "We aggregate 500GB daily user events in Synapse Spark—sessionization, attribution modeling, fraud scoring. Transformed results load to ClickHouse hourly via connector. Dashboards query ClickHouse in 50ms. Separating heavy batch work from serving solved our performance problem."

Pattern 2: ADLS Gen2 as Staging Layer with ClickHouse Reading

The most robust pattern for governance and decoupling: Synapse writes analytical data to ADLS Gen2 in Parquet format, ClickHouse reads directly from storage using table functions.

Why this pattern exists

ADLS Gen2 provides centralized data lake with governance—access controls, lineage tracking, immutable storage for compliance. Multiple systems need access—Synapse for exploration, ClickHouse for serving, other tools for reporting.

Duplicating data into ClickHouse wastes storage and creates synchronization problems. Use data lake as single source of truth; ClickHouse reads when needed.

How Synapse writes to ADLS

Spark DataFrame to Parquet:

# Write partitioned Parquet to ADLS
transformed_df.write \
.format("parquet") \
.partitionBy("event_date") \
.mode("append") \
.option("compression", "snappy") \
.save("abfss://curated@mystorageaccount.dfs.core.windows.net/events/")

Partition structure:

/curated/events/
event_date=2026-01-28/
part-00001-snappy.parquet (256MB)
part-00002-snappy.parquet (256MB)
event_date=2026-01-29/
...

Parquet benefits:

• Columnar format optimized for analytical queries
• Schema embedded in file metadata
• Compression (Snappy, ZSTD) reduces storage costs
• Predicate pushdown enables reading only required columns

How ClickHouse reads from ADLS

Use azureBlobStorage table function for ADLS Gen2:

INSERT INTO analytics.events
SELECT event_time,
user_id,
event_type,
properties
FROM azureBlobStorage(
'https://mystorageaccount.blob.core.windows.net/curated/events/event_date=2026-01-28/*.parquet',
'mystorageaccount',
'STORAGE_ACCOUNT_KEY',
'Parquet'
)
WHERE event_time >= today();

For distributed processing across ClickHouse cluster:

INSERT INTO analytics.events
SELECT *
FROM azureBlobStorageCluster(
'default', -- cluster name
'https://mystorageaccount.blob.core.windows.net/curated/events/event_date=2026-01-28/*.parquet',
'mystorageaccount',
'STORAGE_ACCOUNT_KEY',
'Parquet'
);

Parallelizes reads across cluster nodes for faster ingestion of large datasets.

Authentication options for ADLS access

Storage Account Key (simplest):

FROM azureBlobStorage(
'https://account.dfs.core.windows.net/container/*.parquet',
'account_name',
'account_key',
'Parquet'
)

SAS Token (time-limited, more secure):

• Generate SAS with read permissions
• Include token in connection string
• Configure expiration and rotation

Managed Identity (most secure, Azure resources only):

• ClickHouse on Azure VM/AKS with managed identity
• Assign Storage Blob Data Reader role
• No secrets in configuration or code

Use Managed Identity when possible to eliminate credential rotation and reduce attack surface.

File organization for performance

Small files problem:

• Thousands of tiny files (< 10MB) kill performance
• Excessive Azure Storage API calls
• ClickHouse reads sequentially through many chunks

Optimal file sizing:

• Target: 128MB-512MB per Parquet file
• Configure Spark write with .option("maxRecordsPerFile", 2000000)
• Balance file count versus individual file size

Partition strategy:

/events/
event_date=2026-01-28/
event_hour=00/
part-00001.parquet (256MB)
part-00002.parquet (256MB)
event_hour=01/
...

ClickHouse reads only required partitions when filtering: WHERE event_date = '2026-01-28' AND event_hour = 10.

Synapse Serverless SQL over same data

Advantage of ADLS staging: Synapse Serverless SQL queries same Parquet files for exploration and validation:

SELECT event_date,
COUNT(*) as event_count,
SUM(revenue) as total_revenue
FROM OPENROWSET(
BULK 'https://mystorageaccount.dfs.core.windows.net/curated/events/event_date=2026-01-28/*.parquet',
FORMAT='PARQUET'
) AS events
GROUP BY event_date;

Use cases:

• Data validation before loading to ClickHouse
• Ad-hoc exploration by data analysts
• Joining with other datasets in ADLS
• Testing transformations before productionizing

Performance note: Serverless SQL optimized for exploration, not high-concurrency serving. Production dashboards should query ClickHouse.

When ADLS staging makes sense

Choose ADLS staging when:

• Governance requirements mandate centralized data lake
• Multiple consumers need same data (Synapse, ClickHouse, reporting tools)
• Immutable audit trail required for compliance
• Decoupling transformation pipeline from serving infrastructure
• Large batch volumes (hundreds of GB) where staging cost justified

Don't choose this when:

• Real-time streaming required (adds unnecessary latency)
• Small datasets (< 1GB) where staging overhead not justified
• Tight latency budgets (staging adds pipeline stage)

One team explained: "We process daily clickstream—2TB raw events. Synapse Spark transforms to Parquet in ADLS (curated zone). ClickHouse loads aggregated views for dashboards. Serverless SQL for analyst exploration. Single source of truth with multiple consumption patterns."

Pattern 3: Synapse Pipelines Pushing to ClickHouse HTTP Interface

Alternative pattern for direct data movement: Synapse Pipelines copy data from sources and push to ClickHouse HTTP interface using Web Activity or REST connector.

Why this pattern exists

Not all data flows through Spark or ADLS. Some sources (Azure SQL, Cosmos DB, APIs) produce data Synapse needs to load directly without heavy transformation.

Pipelines provide orchestration. ClickHouse HTTP provides standard REST endpoint.

Direct data flow: Source → Pipeline transformation → ClickHouse. No intermediate storage.

How to configure HTTP ingestion

Step 1: Create Linked Service for ClickHouse

Configure REST linked service with parameterized query:

{
"name": "ClickHouseHTTP",
"type": "RestService",
"typeProperties": {
"url": "https://clickhouse.example.com:8443/?query=@{encodeUriComponent(linkedService().pQuery)}",
"authenticationType": "Basic",
"userName": "default",
"password": {
"type": "AzureKeyVaultSecret",
"store": {"referenceName": "AzureKeyVault"},
"secretName": "clickhouse-password"
}
},
"parameters": {
"pQuery": {"type": "String"}
}
}

Critical details:

URL encoding required: encodeUriComponent for SQL in query parameter.

HTTPS mandatory for production (port 8443 ClickHouse Cloud, 8123/443 self-managed).

Credentials in Key Vault, never hardcoded.

Step 2: Define Web Activity for INSERT

Execute INSERT with JSONEachRow format:

{
"name": "InsertToClickHouse",
"type": "WebActivity",
"typeProperties": {
"url": "https://clickhouse.example.com:8443/",
"method": "POST",
"headers": {
"Content-Type": "application/x-ndjson"
},
"body": "@activity('CopyData').output",
"authentication": {
"type": "Basic",
"username": "default",
"password": {
"type": "AzureKeyVaultSecret",
"secretName": "clickhouse-password"
}
}
},
"linkedServiceName": {
"referenceName": "ClickHouseHTTP",
"parameters": {
"pQuery": "INSERT INTO events FORMAT JSONEachRow"
}
}
}

Step 3: Configure data format settings

ClickHouse insert with tolerant parsing:

INSERT INTO events
SETTINGS
date_time_input_format='best_effort',
input_format_skip_unknown_fields=1,
input_format_allow_errors_num=100
FORMAT JSONEachRow

Settings explained:

date_time_input_format='best_effort': Parse various datetime formats automatically.

input_format_skip_unknown_fields=1: Ignore JSON fields not in table schema—critical for schema evolution.

input_format_allow_errors_num=100: Allow up to 100 parse errors before failing—balance reliability versus data quality.

Known limitations with Synapse Pipelines

HTTP compression doesn't work correctly:

When Copy Activity enables compression to ClickHouse HTTP endpoint, it sends 0-byte payload. Known issue—disable compression:

{
"sink": {
"type": "RestSink",
"httpCompressionType": "none" // Must be disabled
}
}

Compression must happen at data level (Parquet, gzip files in ADLS) not HTTP transport level from Pipelines.

4MB response limit on Web Activity:

Web Activity response body limited to 4MB. If reading large query results from ClickHouse, pipeline fails. Use Copy Activity for data movement, Web Activity only for control operations.

When Pipelines HTTP push makes sense

Choose Synapse Pipelines HTTP when:

• Simple data movement without complex transformations
• Orchestration-first workflows (conditional logic, error handling, dependencies)
• Multiple pipeline sources (Azure SQL, Cosmos, REST APIs)
• Moderate data volumes (thousands to millions of rows per run)

Don't choose this when:

• Large batch loads (multi-GB)—Spark or ADLS staging more efficient
• Complex transformations requiring Spark capabilities
• Compression required for payload size (known Synapse limitation)

Private Network Connectivity: The Complexity Layer

Most production deployments run ClickHouse in private networks. Synapse running in Azure's multi-tenant environment can't reach private endpoints by default.

Synapse Managed Virtual Network

When enabled, Synapse Managed VNet restricts outbound traffic to approved destinations only via Managed Private Endpoints.

Impact on ClickHouse integration:

• Spark pools in Managed VNet cannot reach public ClickHouse endpoints
• Pipelines cannot access private ClickHouse without Self-Hosted Integration Runtime
• Requires either Managed Private Endpoint setup or alternative architecture

Managed Private Endpoints

Create managed private endpoints from Synapse to ClickHouse:

1. ClickHouse supports Azure Private Link
2. Create Private Endpoint in Synapse workspace
3. Approve connection from ClickHouse side
4. Spark and Pipelines reach ClickHouse over Microsoft backbone

No public internet exposure. Traffic stays within Azure network.

Self-Hosted Integration Runtime alternative

When Managed Private Endpoints not feasible:

Deploy Self-Hosted IR:

• Install on VM in VNet with network access to ClickHouse
• Synapse Pipelines route traffic through Self-Hosted IR
• IR acts as bridge between Synapse and private ClickHouse

Use cases:

• ClickHouse on-premises or external cloud
• Network policies prohibit Managed Private Endpoints
• Need custom network routing or proxies

When private networking matters

Choose private networking when:

• Security policies prohibit public database endpoints
• Compliance requirements mandate private connectivity
• Data sensitivity requires network isolation

Trade-off: Operational complexity (Private Endpoints, IR management) versus security requirements.

How Tinybird Simplifies Azure Synapse Integration

Everything discussed—Spark connector configuration, ADLS staging orchestration, Pipeline HTTP setup, private network coordination, schema mapping, incremental loading—requires significant engineering to implement and maintain.

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

Streaming ingestion without Spark jobs

While Synapse Spark excels at complex batch transformations, most analytics workloads need continuous data ingestion—often called streaming data—not hourly job runs.

Tinybird's approach:

• Kafka, webhooks, HTTP events, and real-time change data capture stream continuously
• Sub-second queryability from ingestion to results
• No Spark jobs for simple data movement
• Incremental materialized views maintain aggregations automatically

This pattern is particularly relevant for device telemetry and Internet of Things (IoT) pipelines, where events arrive continuously and freshness directly impacts product and operational outcomes.

No hourly batch windows. Stream data, query immediately.

SQL transformations instead of Spark complexity

Synapse Spark powerful but complex—cluster management, library dependencies, job scheduling, performance tuning. For most transformations, SQL simpler and more maintainable.

Tinybird workflow:

1. Ingest raw data from sources continuously
2. SQL pipes define transformations and aggregations
3. Instant APIs serve results with sub-100ms latency for user-facing analytics

Teams building product analytics can even prototype a lightweight Google Analytics alternative to capture events and ship real-time dashboards in minutes without maintaining Spark clusters.

When Tinybird makes sense versus Synapse integration

Choose Tinybird when:

• Real-time analytics required (seconds to minutes, not hours)
• Operational simplicity preferred over infrastructure control
• SQL transformations sufficient (vast majority of cases)
• Sub-100ms serving for dashboards and APIs critical
• Managed platform acceptable for analytics infrastructure

Keep Synapse integration patterns when:

• Complex Spark transformations genuinely required (heavy ML, complex joins across disparate sources)
• Existing Synapse investment and expertise justify continuation
• Unified lakehouse governance through Synapse workspace essential
• Batch processing genuinely appropriate (regulatory reporting, daily aggregations)

Many teams use both: Synapse for complex batch transformations landing in ADLS, Tinybird streaming operational data for real-time serving. Separate batch infrastructure from serving platforms.

The Path Forward: Architecture Matching Requirements

ClickHouse integration with Azure Synapse solves specific problems:

Spark connector enables complex ETL with programmatic control
ADLS staging provides governance and multi-consumer access
Pipelines HTTP delivers orchestrated direct loading

But these patterns require engineering investment:

• Connector configuration and dependency management
• ADLS file organization and partition strategy
• Private network setup (Managed VNet, Private Endpoints, Self-Hosted IR)
• Schema mapping and type compatibility
• Incremental loading and deduplication logic
• Performance tuning (batch sizes, parallelism, compression)

Tinybird provides purpose-built alternative for teams where:

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

The choice is yours: build integration infrastructure connecting Synapse 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.