---
title: "ClickHouse Integration with Azure Data Factory Guide"
excerpt: "Practical guide to ClickHouse integration with Azure Data Factory. Optimize pipelines and achieve real-time analytics."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-01-31 00:00:00"
publishedOn: "2026-01-31 00:00:00"
updatedOn: "2026-01-31 00:00:00"
status: "published"
---

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](https://medium.com/@ogunodabas/downstream-upstream-system-c1dc6cf4b59e).**

### **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](https://www.ibm.com/think/topics/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](https://www.oracle.com/database/what-is-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](https://www.tinybird.co/blog/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](https://www.tinybird.co/blog/real-time-dashboards-are-they-worth-it) 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](https://www.tinybird.co/blog/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.