Skip to content

ClickHouse -> MSSQL

This guide is a copy/paste-ready starting point for loading data from ClickHouse into MSSQL with dpone. It describes the production contract for analytical/event tables landing in SQL Server: certified type mapping, bounded source reads, MSSQL bcp staging, source/target acceptance, and route evidence before scheduling.

When to use this path

Use this path when ClickHouse is the system of record or ingestion boundary and MSSQL is the landing, warehouse, event-log, or downstream replication target.

For append-only or mostly append-only event tables, do not default to full_refresh. Production manifests should declare either:

  • incremental_append with source.options.incremental_column;
  • partition_replace / replace with a bounded date_column or predicate;
  • incremental_merge only when a stable unique_key exists.

Copy/paste manifest

# yaml-language-server: $schema=../../src/dpone/schema/etl-batch-manifest.schema.json
kind: dpone.batch.v1

defaults:
  name: clickhouse_to_mssql_example
  source:
    type: clickhouse
    connection_id: clickhouse_source
    options:
      batch_size: 50000
      export_format: csv
  sink:
    type: mssql
    connection_id: mssql_dwh
    table:
      schema: dbo
      name: orders
    strategy:
      mode: incremental_merge
      unique_key: order_id
      merge_policy: delete_insert
      duplicate_policy: fail

quality:
  gates:
    - id: source_target_rows
      type: row_count_reconciliation
      severity: error
      tolerance:
        mode: pct
        value: 0.1

schemas:
  analytics:
    tables:
      - orders

Run it locally:

dpone plan examples/source-sink/clickhouse-to-mssql.yaml --format md
dpone run examples/source-sink/clickhouse-to-mssql.yaml

The checked source file is examples/source-sink/clickhouse-to-mssql.yaml; CI compares its parsed YAML with this block.

If you change the strategy to full_refresh and empty output is invalid, row-count reconciliation is not enough: it can pass a 0 source / 0 target comparison. Add an explicit non-empty target gate:

quality:
  gates:
    - id: target_min_rows
      type: min_rows
      side: target
      threshold: 1
      severity: error

ClickHouse connection transport

dpone supports two ClickHouse client transports behind the same source.type: clickhouse manifest contract:

Transport Driver Typical ports When to use
Native TCP clickhouse-driver 9000, secure 9440 Self-hosted ClickHouse or clusters where native TCP is reachable.
HTTP/HTTPS clickhouse-connect 8123, secure 8443 ClickHouse Cloud, managed ClickHouse, or corporate networks where only HTTP(S) is exposed.

Airflow/Vault/env credentials can choose the transport through connection extras. For managed ClickHouse over HTTPS:

{
  "secure": true,
  "interface": "http",
  "port": 8443,
  "ca_cert": "/etc/ssl/certs/clickhouse-ca.pem"
}

Equivalent keys are accepted for the transport selector: interface, driver, protocol, transport, or client. Values http, https, clickhouse_connect, and connect select the HTTP adapter. Without an explicit selector dpone keeps the historical native TCP behavior.

Troubleshooting:

Symptom Meaning Action
SocketTimeoutError on 8123/9000 The pod cannot reach that endpoint/port. Check network policy and whether the server exposes HTTP or native TCP.
Unexpected packet ... expected Hello Native client was pointed at an HTTP/TLS endpoint, or TLS/native mode mismatch. Set interface: http with port: 8443, or use native secure 9440 only when native TCP is exposed.
TLS/certificate error HTTPS is reachable, but trust configuration is incomplete. Provide ca_cert or use a platform trust bundle approved by security.
MSSQL rows exist but all business columns are NULL, while __dpone__* lineage is populated Large ClickHouse extracts (>10k rows) streamed empty dicts when column names were taken from a zero-row header sample. Upgrade to a dpone build that resolves streaming headers via with_column_types / describe_result_columns. Validate business-column payload with connector-native evidence. The executable manifest gates are row_count_reconciliation, min_rows, and typed_hash_reconciliation; use the hash gate only when both probes emit hashes. Row counts alone are not payload-fidelity proof.

Supported load strategies

These rows describe public runtime contracts, not certification of this exact source, sink, transport, schema-evolution mode, and runtime combination.

Strategy Status Notes
full_refresh Supported Uses staging first, then applies the target-specific finalization plan.
incremental_append Supported Uses staging first, then applies the target-specific finalization plan.
incremental_merge Supported Default merge_policy: delete_insert; shadow_swap is available for DB targets.
replace Supported Uses staging first, then applies the target-specific finalization plan.
partition_replace Supported Replaces target partitions represented by staging partition.column; see Load strategies for native/fallback paths.
snapshot_diff Supported Requires a complete bounded snapshot and unique_key; applies the configured diff/delete policy.

See Load strategies for the detailed algorithm for each strategy.

Production gates

Treat this route as production-ready only when these gates are green:

Gate Evidence Why it matters
Source boundary source_boundary_profile Event tables must have a cursor, date window, partition column, or key.
Type profile type_matrix clickhouse_to_mssql_landing_v1 must classify every column as safe or contract-required.
Schema evolution route_schema_evolution New/widened columns are planned before source IO; incompatible changes fail or use __dpone__nc__*.
MSSQL bulk readiness bcp_bulk_readiness The runtime image has bcp, ODBC driver, staging permissions, and safe text codec settings.
Runtime evidence route_execution_ledger, run_artifact Load steps, row counts, errors and cleanup are durable.
Acceptance quality_reconciliation Counts, nulls, distincts, types and lineage columns are checked.
Performance benchmark_slo Throughput is measured by phase and meets the agreed route SLO.

Runtime algorithm

This sink does not currently implement StagedLoadPort, so this route records governance_finalization=legacy_post_finalize. Blocking gates run only after the sink has mutated or finalized the target. A failure prevents source-state advancement but cannot roll back that target mutation; inspect and repair or deduplicate the target before retrying. See Load governance.

flowchart TD
    A["Resolve manifest and registry entries"] --> B["Create ClickHouse source"]
    B --> C["Plan bounded extract"]
    C --> D["Read through bounded SELECT streaming"]
    D --> E["Emit ExtractResult with schema and artifact"]
    E --> F["Plan schema evolution"]
    F --> G["Create MSSQL staging or event batch"]
    G --> H["Load through BCP into staging followed by set-based MERGE/INSERT/REPLACE"]
    H --> I["Apply MSSQL finalization strategy"]
    I --> J["Run quality and reconciliation checks (legacy_post_finalize)"]
    J --> K["Advance state only after success"]

Strategy behavior

  • full_refresh: only for small or bounded reference tables. For large event tables it is a red flag unless the source predicate bounds the run.
  • incremental_append: extract only the cursor boundary and append rows through MSSQL bcp staging. Use incremental_state_timezone: passthrough when the target stores the same wall-clock values as the ClickHouse source.
  • incremental_merge: load into staging, validate duplicates, then use delete_insert by default; shadow_swap is available where table swaps are supported.
  • replace: reload a bounded predicate window through staging and then atomically replace the matching target slice.
  • snapshot_diff: compare a complete current source snapshot with the target by unique_key, then apply the configured insert, update, and delete policy.
  • partition_replace: extract a complete partition slice, load it into staging, and replace only partitions represented by partition.column.

Snapshot reconciliation is separate from the load strategy. Runtime planning reports that capability as reconciliation.mode=snapshot; in the official dpone.batch.v1 authoring schema, enable it with reconciliation: true.

Schema evolution and type mapping

Schema evolution is enabled by default and runs before the staging/final load path:

  1. Read source schema from ExtractResult.schema.
  2. Introspect the MSSQL target schema.
  3. Apply safe additions and widening operations.
  4. Fail breaking changes by default.
  5. If configured, route incompatible type changes to __dpone__nc__<column>.

The runtime and CLI share one profile: clickhouse_to_mssql_landing_v1. It unwraps Nullable(...) and LowCardinality(...), preserves ordinary numeric/date/time/string types, and requires explicit contracts for ambiguous or lossy families.

Use Schema evolution and Type mapping matrix when adding columns or changing source types.

Type matrix certification

Run the route type matrix before enabling a new ClickHouse table:

dpone schema type-matrix \
  --source clickhouse \
  --sink mssql \
  --source-type "event_at:DateTime64(3, 'Europe/Moscow')" \
  --source-type "user_id:UInt64" \
  --source-type "payload:Nullable(String)" \
  --format md

Production blockers:

Blocker Meaning Action
requires_explicit_contract=true The type is complex, lossy or vendor-specific. Add schema_contract, physical override, or quarantine/normalization.
DateTime64(p) with p > 7 SQL Server datetime2 cannot store precision above 7. Declare truncation to datetime2(7) or land as text.
Decimal(p,s) with p > 38 SQL Server decimal cannot represent the precision. Land as text or change source projection.
Array / Map / Tuple / Nested Shape is not first-class in SQL Server. Serialize JSON explicitly or normalize into child tables.

Performance profile

The certified baseline is clickhouse_streaming_to_mssql_bcp_staging:

ClickHouse SELECT stream
-> BulkTextCodec batch file
-> MSSQL bcp into staging
-> set-based append/merge/replace
-> acceptance and state commit

Default tuning:

  • batch_size: 100000 for ordinary event tables;
  • lower batch size for very wide rows or weak workers;
  • bounded date predicates for backfill;
  • lookback_days for late-arriving events;
  • route SLO evidence starts at 15k rows/sec for target_load_finalize, then should be calibrated by live benchmark.

Runbook

  1. Run dpone doctor --profile local and fix missing extras, bcp, ODBC driver, and ClickHouse client dependencies.
  2. Run dpone schema type-matrix --source clickhouse --sink mssql with the real source schema.
  3. Add schema_contract for every complex/lossy column before the first production run.
  4. Run dpone perf advise <manifest> and confirm event tables are not using blind full_refresh.
  5. Run dpone plan <manifest> --format md and review source boundary, staging path, schema evolution, state, and quality gates.
  6. Run a small bounded window first.
  7. Inspect .dpone/runs/clickhouse_to_mssql, route decision evidence, and MSSQL target row counts.
  8. Run acceptance: source/target count, NULL counts, selected distinct counts, type fidelity and lineage columns.
  9. For incremental jobs, verify state and timezone mode before enabling a schedule.
  10. Promote the manifest through GitOps only after route readiness evidence is green.

Industrial comparison

  • dlt-style load packages: dpone keeps the package idea but adds MSSQL bcp staging, type matrix certification and source/target acceptance.
  • Airbyte/Fivetran-style connector checks: dpone keeps connection validation and initial sync safety, but makes route decisions and blockers visible in OSS artifacts.
  • Informatica/SSIS/Pentaho-style staging: dpone uses staging-first finalization and step evidence, but avoids manual tuning by route advisor and typed runbooks.
  • MSSQL -> ClickHouse dpone route: this ClickHouse -> MSSQL path now shares the same contract language: route readiness, type profile, DQ, lineage, SLO and durable evidence.

Type contracts and physical design

This flow supports the shared dpone type-governance stack:

  • Type inference for source metadata, sampled profiling, confidence, and empty string vs NULL behavior.
  • Schema contracts for explicit logical column types, enforcement modes, and __dpone__nc__* variant columns.
  • Physical design for target-specific DDL such as concrete SQL types, indexes, partitioning, compression, ClickHouse LowCardinality, and BigQuery clustering.

Use dpone schema infer --manifest ... and dpone schema physical-plan --manifest ... before enabling new table DDL in production.