ClickHouse Integration with Azure Data Factory Guide

Your data pipeline looked solid on paper. Azure Data Factory orchestrates data movement from Azure SQL, Cosmos DB, and Blob Storage into your analytics database. Clean ETL architecture with visual workflows and managed connectors. The data engineering team approved the design.

Then you deployed to production analytics serving.

Queries that should return in milliseconds take 3-5 seconds. Dashboard users complain about stale data because ADF runs hourly batches. Your pipelines fail intermittently with HTTP timeouts and authentication errors. Data Factory costs climb as you increase DIUs chasing acceptable performance.

Sound familiar?

This is the Data Factory integration trap. What works brilliantly for batch ETL orchestration breaks when you need real-time analytics serving with sub-second query performance.

The problem isn't that Azure Data Factory is poorly designed. Most teams misunderstand what Data Factory actually solves. ADF excels at orchestrating complex data workflows across Azure services with visual pipelines and managed connectors. It wasn't built to serve dashboards, power customer-facing analytics, or deliver sub-100ms API responses—that's ClickHouse®'s job.

Critical reality: Azure Data Factory has no native ClickHouse connector. Integration requires combining generic HTTP/REST activities, staging through Blob Storage, or orchestrating compute (Functions, Databricks) that loads ClickHouse directly.

ClickHouse integration with Azure Data Factory typically follows three patterns: (1) staging data in Azure Blob with ClickHouse pulling via table functions, (2) ADF pushing data to ClickHouse HTTP interface, and (3) ADF orchestrating compute that loads ClickHouse with client libraries. The right pattern depends on data volume, network topology, and operational complexity tolerance.

Let's explore what actually works in production.

Pattern 1: Staging in Azure Blob with ClickHouse Pulling Data

The most robust pattern for large-scale batch: ADF copies data to Azure Blob Storage in analytical format (Parquet/CSV), then ClickHouse reads directly from Blob using table functions.

Why this pattern exists

ADF excels at copying data between Azure services with managed connectors. Blob Storage provides cheap, scalable staging area. ClickHouse reads analytical formats (Parquet, CSV) efficiently from object storage.

Separate movement from transformation: ADF orchestrates data movement to Blob, ClickHouse handles analytical ingestion and query serving.

How the pattern works

Step 1: ADF copies source data to Blob

Use Copy Activity with managed connectors:

{
"name": "CopyToBlob",
"type": "Copy",
"inputs": [{"referenceName": "AzureSQLTable"}],
"outputs": [{"referenceName": "BlobParquet"}],
"typeProperties": {
"source": {"type": "AzureSqlSource"},
"sink": {
"type": "ParquetSink",
"formatSettings": {
"type": "ParquetWriteSettings",
"compressionCodec": "snappy"
}
}
}
}

Parquet recommended for analytical workloads—columnar format, efficient compression, schema embedded.

Partition by date for incremental processing:

• Blob path: events/dt=2026-01-28/part-00001.parquet
• ClickHouse reads only needed partitions
• Easy cleanup of old data

Step 2: ClickHouse ingests from Blob

Use azureBlobStorage table function reading Parquet:

INSERT INTO events_raw
SELECT *
FROM azureBlobStorage(
'https://mystorageaccount.blob.core.windows.net/data/events/*.parquet',
'STORAGE_ACCOUNT_KEY',
'Parquet'
)
WHERE toDate(event_time) = today();

Batch ingestion benefits:

• Large file reads (hundreds of MB to GB) more efficient than many small HTTP requests
• Parquet columnar format optimized for analytical queries
• Network costs lower (single region transfers)
• Retry logic simpler (rerun ClickHouse query versus replay ADF pipeline)

Authentication options for Azure Blob

ClickHouse supports three authentication methods:

Storage Account Key (simplest):

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

SAS Token (time-limited):

FROM azureBlobStorage(
'https://account.blob.core.windows.net/container/*.parquet?sas_token',
'Parquet'
)

Managed Identity (most secure, Azure resources only):

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

Managed Identity eliminates secret rotation and reduces security attack surface. Use when ClickHouse deployment supports it.

File organization and performance

Small files problem kills performance:

• Thousands of tiny files (< 10MB each) create excessive S3/Blob API calls
• List operations become bottleneck
• ClickHouse reads sequentially through many small chunks

Optimal file sizing:

• Target: 128MB-512MB per file
• Configure ADF Copy Activity compression and file size thresholds
• Partition by date/hour balancing granularity versus file count

Partitioning strategy:

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

ClickHouse reads only required partitions when filtering by date/hour.

When Blob staging makes sense

Choose Azure Blob staging when:

• Large batch volumes (multi-GB per pipeline run)
• Cost optimization critical (Blob storage cheaper than compute for staging)
• Network allows ClickHouse accessing Blob Storage (public or Private Link)
• Analytical formats appropriate (Parquet, CSV, TSV)
• Batch processing acceptable (hourly, daily loads)

Don't choose this when:

• Real-time streaming required (minutes, not hours)
• Network isolation prevents ClickHouse reaching Blob
• Complex transformations needed during ingestion (use Pattern 3)

One team shared: "We move 500GB daily from Azure SQL to ClickHouse. ADF copies to Parquet in Blob (30 minutes), ClickHouse reads and loads (20 minutes). Total pipeline 50 minutes. Previous approach pushing via HTTP took 4+ hours and frequently failed."

Pattern 2: ADF Pushing Data to ClickHouse HTTP Interface

Alternative pattern for smaller volumes or when Blob staging impractical: ADF sends data directly to ClickHouse HTTP interface using REST connector and Web Activity.

Why this pattern exists

Not all data lives in Blob. Some sources (Cosmos DB, external APIs, Azure Functions output) produce data ADF needs to load directly. HTTP interface provides standard REST endpoint accepting data.

Direct data flow: Source → ADF transformation → ClickHouse HTTP endpoint. No intermediate storage required.

How to configure HTTP ingestion

Step 1: Create REST Linked Service

ClickHouse HTTP endpoint as base URL with parameterized query:

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

Critical details:

encodeUriComponent required for URL encoding query parameter with SQL statement.

Basic authentication standard for ClickHouse HTTP. Store passwords in Azure Key Vault, never hardcode.

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

Step 2: Define INSERT query with format

ClickHouse expects SQL query specifying format:

INSERT INTO events_raw
SETTINGS
date_time_input_format='best_effort',
input_format_skip_unknown_fields=1
FORMAT JSONEachRow

Settings explained:

date_time_input_format='best_effort': Parse various datetime formats automatically (ISO 8601, Unix timestamps, human-readable).

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

FORMAT JSONEachRow: One JSON object per line (NDJSON). Most compatible format for ADF→ClickHouse.

Important ADF expression caveat: Single quotes in SQL must be doubled (''best_effort'') within ADF expressions to escape properly.

Step 3: Configure Copy Activity with REST sink

{
"name": "CopyToClickHouse",
"type": "Copy",
"typeProperties": {
"source": {"type": "CosmosDbSqlApiSource"},
"sink": {
"type": "RestSink",
"httpRequestTimeout": "00:05:00",
"requestInterval": 10,
"requestMethod": "POST",
"writeBatchSize": 10000,
"httpCompressionType": "none"
}
},
"linkedServiceName": {
"referenceName": "ClickHouseREST",
"parameters": {
"pQuery": "INSERT INTO events_raw SETTINGS date_time_input_format=''best_effort'' FORMAT JSONEachRow"
}
}
}

Performance tuning:

writeBatchSize: Rows per HTTP request. Larger batches (10,000-100,000) reduce overhead and ClickHouse insert parts.

requestInterval: Milliseconds between batches. Increase if ClickHouse throttles.

httpCompressionType: "none": Critical—compression doesn't work correctly with ADF Copy Activity to ClickHouse HTTP. Known issue sends 0-byte payloads when enabled. Leave disabled.

Handling schema drift and malformed data

Production data contains surprises—new fields, missing values, wrong types, encoding issues.

Tolerant ingestion settings:

INSERT INTO events_raw
SETTINGS
input_format_skip_unknown_fields=1, -- Ignore extra fields
input_format_allow_errors_num=100, -- Allow up to 100 parse errors
input_format_allow_errors_ratio=0.01, -- Or 1% error ratio
date_time_input_format='best_effort' -- Flexible date parsing
FORMAT JSONEachRow

Error handling strategy:

Parse errors skip problematic rows up to threshold. ClickHouse logs errors for investigation. Balance between pipeline reliability (don't fail on few bad rows) and data quality (alert when error threshold exceeded).

For nested JSON objects:

SETTINGS
input_format_json_read_objects_as_strings=1, -- Store nested objects as JSON strings
input_format_import_nested_json=1 -- Or parse into Nested columns

Store complex JSON as String column, parse later in materialized views. More flexible than rigid schema enforcement.

Performance optimization for HTTP ingestion

Parallel parsing when CPU-bound on ClickHouse side:

SETTINGS input_format_parallel_parsing=1

Supported for JSONEachRow, CSV, TSV. Parses input using multiple threads before inserting.

Async inserts for high-throughput bursts:

SETTINGS
async_insert=1,
wait_for_async_insert=1 -- Wait for server persist (default)

async_insert=1 buffers inserts and flushes in background, improving throughput at cost of latency.

wait_for_async_insert=0 returns immediately after buffering (fire-and-forget). Fastest but weakest durability guarantee—use only when acceptable to verify success asynchronously.

When HTTP push makes sense

Choose HTTP push when:

• Moderate data volumes (tens of thousands to millions of rows per batch)
• Direct integration needed without Blob staging
• Sources don't naturally output to Blob (APIs, Cosmos DB, Functions)
• Simpler architecture preferred (fewer components)

Don't choose this when:

• Very large batches (multi-GB) better suited to Blob staging
• Network restrictions prevent HTTP access to ClickHouse
• Compression required for payload size (known ADF limitation)

One team explained: "We ingest user events from Cosmos DB change feed every 15 minutes. ADF reads changes and pushes via HTTP to ClickHouse. 50,000 events per run completes in 2 minutes. Simple, reliable, no staging overhead."

Pattern 3: ADF Orchestrating Compute Loading ClickHouse

Most flexible pattern for complex scenarios: ADF orchestrates Azure Functions, Databricks, or Container Instances that load ClickHouse using client libraries with custom logic, and propagate results to the downstream system.

Why this pattern exists

Sometimes Copy Activity limitations or HTTP interface constraints prevent optimal loading:

• Complex transformations requiring stateful processing
• Upsert patterns needing staging table + merge logic
• Advanced batching with retries, deduplication, validation
• Performance requirements exceeding generic connectors

ADF provides orchestration. Custom compute provides loading logic.

Architecture flow

1. ADF triggers Azure Function or Databricks job
2. Compute reads source data (Blob, SQL, APIs)
3. Custom logic applies transformations, batching, deduplication
4. ClickHouse client (HTTP or JDBC) executes optimized inserts
5. Result returned to ADF for pipeline monitoring

Example: Azure Function loading ClickHouse

Function triggered by ADF:

import clickhouse_connect
from azure.storage.blob import BlobServiceClient

def main(req):
# Read from Blob
blob_client = BlobServiceClient(account_url, credential)
data = blob_client.download_blob("events.parquet").readall()

# Connect to ClickHouse
client = clickhouse_connect.get_client(
host='clickhouse.example.com',
port=8443,
username='default',
password=os.environ['CLICKHOUSE_PASSWORD'],
secure=True
)

# Custom transformation and batching logic
batch = transform_data(data)

# Insert with optimal settings
client.insert(
'events_raw',
batch,
settings={
'async_insert': 1,
'date_time_input_format': 'best_effort'
}
)

return {"status": "success", "rows": len(batch)}

ADF Web Activity invokes Function:

{
"name": "LoadClickHouseFunction",
"type": "WebActivity",
"typeProperties": {
"url": "https://myfunctionapp.azurewebsites.net/api/load_clickhouse",
"method": "POST",
"body": {
"blob_path": "@pipeline().parameters.blobPath",
"target_date": "@pipeline().parameters.targetDate"
},
"authentication": {
"type": "MSI",
"resource": "https://myfunctionapp.azurewebsites.net"
}
}
}

Benefits of compute orchestration pattern

Full control over insert logic, batching, error handling, retries.

Client library features beyond HTTP interface—connection pooling, prepared statements, binary protocols.

Complex transformations applied in familiar programming languages (Python, Java, Scala).

Idempotent upserts through staging tables and merge logic.

Custom monitoring emitting detailed metrics and logs.

When compute orchestration makes sense

Choose orchestrated compute when:

• Complex logic required beyond Copy Activity capabilities
• Performance optimization needs custom batching and parallelization
• Upsert patterns with staging and merge workflows
• Team expertise in Functions/Databricks stronger than ADF configuration
• Flexibility valued over managed connectors

Trade-off: More operational complexity (deploying Functions, managing code) versus ADF-native simplicity.

One team shared: "ADF Copy Activity couldn't handle our deduplication requirements. We wrote Azure Function with staging table logic—inserts to staging, promotes to final table after deduplication. ADF orchestrates hourly runs. Perfect control, zero duplicates."

Private Network Connectivity Challenges

Most production ClickHouse deployments run in private networks—VNets, Private Link, firewalls—typical of modern cloud computing environments. ADF running in Azure's multi-tenant environment can't reach private endpoints by default.

Integration Runtime options

Azure Integration Runtime (default):

• Multi-tenant Azure service
• Cannot reach VNet-private resources
• Suitable only for public endpoints

Self-Hosted Integration Runtime:

• Agent running on your VM/AKS in VNet
• Full network access to private resources
• Copy Activity and Web Activity use this runtime
• Requires VM/cluster management

Managed VNet Integration Runtime:

• Azure-managed network isolation
• Managed Private Endpoints to Azure services
• Simplifies private connectivity without self-hosted IR
• Available in ADF Managed VNet feature

ClickHouse Cloud Azure Private Link

ClickHouse Cloud supports Azure Private Link for VNet-private connectivity:

1. Enable Private Link on ClickHouse Cloud service
2. Create Private Endpoint in your VNet
3. Configure DNS for private resolution
4. ADF Self-Hosted IR or Managed VNet IR reaches ClickHouse privately

No public internet exposure. Traffic stays within Azure backbone.

When private connectivity isn't possible

If private networking too complex or unavailable, fallback patterns:

Proxy Function in VNet:

• Azure Function with VNet integration
• Function reaches private ClickHouse
• ADF calls Function via public HTTPS
• Function acts as proxy to ClickHouse

Temporary firewall rules:

• Allow ADF Azure IR public IPs (not recommended—large IP ranges)
• Enable only during pipeline runs (automation required)
• Security risk versus operational simplicity trade-off

Most teams eventually adopt Self-Hosted IR or Managed VNet IR for production workloads. Initial public endpoint acceptable for development.

Incremental Loading and Deduplication Patterns

Batch ETL requires incremental loading—process only new/changed data. ADF provides watermark patterns. ClickHouse requires deduplication strategies.

ADF watermark tracking

Store last processed value (timestamp, ID) in Azure SQL or Table Storage:

{
"name": "IncrementalCopy",
"type": "Copy",
"typeProperties": {
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": "SELECT * FROM events WHERE updated_at > '@{activity('GetWatermark').output.firstRow.watermark}'"
}
}
}

After successful load, update watermark to latest processed value.

ClickHouse deduplication strategies

ClickHouse doesn't support UPDATE in traditional sense. Design for append-only with eventual deduplication.

Strategy 1: ReplacingMergeTree

CREATE TABLE events_deduplicated (
event_id String,
event_time DateTime,
user_id String,
data String,
version DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toDate(event_time)
ORDER BY (event_id, event_time);

Background merges deduplicate keeping highest version per event_id. Query with FINAL for deduplicated view (slower) or accept transient duplicates.

Strategy 2: Staging table with explicit merge

-- Staging table (append-only from ADF)
CREATE TABLE events_staging (
event_id String,
event_time DateTime,
data String,
batch_id String,
load_time DateTime DEFAULT now()
)
ENGINE = MergeTree()
ORDER BY (batch_id, event_id);

-- Final table
CREATE TABLE events_final (
event_id String,
event_time DateTime,
data String
)
ENGINE = MergeTree()
ORDER BY (event_time, event_id);

-- Scheduled deduplication query
INSERT INTO events_final
SELECT event_id, event_time, data
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY load_time DESC) as rn
FROM events_staging
WHERE toDate(load_time) = today()
)
WHERE rn = 1;

Control over deduplication timing and logic. Staging absorbs duplicates from retries; scheduled promotion ensures clean final table.

Strategy 3: Idempotent aggregations

For analytics dashboards, duplicates often acceptable if aggregations handle them:

SELECT
event_date,
uniqExact(event_id) as unique_events, -- Exact count handles duplicates
countIf(event_type = 'purchase') as purchases
FROM events_raw
GROUP BY event_date;

Simpler than explicit deduplication when use case permits.

Understanding the underlying database fundamentals helps teams choose sensible ingestion and deduplication strategies in ClickHouse. Append-only designs, columnar storage, and merge semantics align well with batch and incremental loads while keeping query performance predictable as data grows.

How Tinybird Simplifies Azure Data Factory Integration

Everything discussed—Blob staging orchestration, HTTP interface configuration, async insert tuning, network connectivity setup, incremental watermark management, deduplication patterns—requires significant engineering to implement and maintain.

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

Streaming ingestion without orchestration overhead

While ADF excels at batch ETL orchestration, most analytics workloads need continuous data ingestion, not hourly pipeline runs. For teams that require real-time data ingestion, the difference between minutes and seconds directly impacts user experience and operational visibility.

Tinybird's approach:

• Kafka, webhooks, HTTP events stream continuously into Tinybird
• Sub-second queryability from ingestion to query results
• No ADF pipelines for simple data movement
• Incremental materialized views maintain aggregations automatically

No hourly batch windows. Stream data, query immediately.

SQL transformations instead of complex pipelines

ADF pipelines become complex fast—Copy Activities, Stored Procedures, Functions, conditional logic, error handling. For most transformations, SQL is 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

No visual pipeline design. Write SQL, get production data workflows.

When Tinybird makes sense versus ADF 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 real-time dashboards and APIs critical
• Managed platform acceptable for analytics infrastructure

Keep ADF integration patterns when:

• Complex Azure ecosystem orchestration required (Functions, Logic Apps, Databricks, ML)
• Existing ADF investment and expertise justify continuation
• Compliance requirements mandate specific Azure services
• Batch processing genuinely appropriate for use case (daily aggregations, regulatory reporting)

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

The Path Forward: Architecture Matching Requirements

ClickHouse integration with Azure Data Factory solves specific problems:

Blob staging provides robust batch loading for large volumes
HTTP push enables direct loading for moderate datasets
Compute orchestration delivers custom logic for complex scenarios

But these patterns require engineering investment:

• REST connector configuration and query parameterization
• Async insert tuning and error handling
• Private network setup (Self-Hosted IR, Managed VNet, Private Link)
• Incremental loading with watermark tracking
• Deduplication patterns (ReplacingMergeTree, staging tables)
• Monitoring and pipeline troubleshooting

Tinybird provides purpose-built alternative for teams where:

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

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

If sub-second queries and scale are non-negotiable, evaluate the best database for real-time analytics for your workload before committing to orchestration patterns that may constrain serving performance.

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