Bounded native ClickHouse to MSSQL transport¶

This opt-in route loads one ClickHouse query through bounded native files, imports independent staging tables with BCP or Microsoft.Data.SqlClient, and publishes once through the existing MSSQL transaction finalizer. The standard dpone run composition is available for inert quality policy; deployments with authored quality gates must inject their quality executor. It supports full_refresh and an explicit UTC partition_replace window. Existing character-spool routes retain their defaults. Retained evidence for dpone 0.80.0 at commit 6ae541d38ac223327d7edb23510859df91173bda establishes scoped local Docker correctness and controlled recovery: six non-binary profiles, 64 rows per fidelity cell, full refresh and explicit UTC partition replacement, with encoding/import policies 2/1 and 1/2. Production workload performance, independent source DDL and target-writer governance, and hard-failure recovery remain UNVERIFIED. These results do not certify a new deployment.

Choose the verification backend¶

Verification	Import	Effective path	Identity and journal	Compatibility
omitted	BCP	BCP plus Python business-row readback	v1	Existing behavior is unchanged
`python_readback`	BCP	BCP plus Python business-row readback	v1	Explicit form of existing behavior
`target_local`	BCP	Supervised BCP plus aggregate-only SQL Server verification	v2	Opt-in; no fallback to v1 inside the invocation
`target_local`	`mssql_sqlclient`	`SqlBulkCopy` plus session-bound aggregate verification	v2	Optional Linux x64 companion; no fallback to BCP

The optimized selector belongs under native_transfer.execution:

native_transfer:
  execution:
    verification_backend: target_local
    import_backend: mssql_sqlclient
    layout_version: 2

Start with examples/native/clickhouse-to-mssql-target-local.yaml and inspect the resolved contract without opening connections:

uv run dpone plan examples/native/clickhouse-to-mssql-target-local.yaml --format json

The mssql_native plan reports verification_backend=target_local, verification_identity_version=2, the selected writer capability and layout. Its required dependencies include stable_target_custody, supervised_bcp_writer, and target_local_digest. Direct dpone run hydrates the declared connections, uses the configured durable runtime storage roots, and composes the native state machine. A deployment-owned runtime remains required when authored quality policies are present.

flowchart LR
    CH[Bounded ClickHouse query] --> FILES[Sealed native files]
    FILES --> SELECT{Explicit writer}
    SELECT -->|default| BCP[Supervised BCP]
    SELECT -->|mssql_sqlclient| TDS[SqlBulkCopy companion]
    TDS --> LOCK[Session grant lock]
    BCP --> BARRIER[Held exact-stage barrier]
    LOCK --> BARRIER
    BARRIER --> RAW[Aggregate raw digest]
    RAW --> PREP[Prepared aggregate verification]
    PREP --> PUB[Atomic publication]
    BCP -->|ambiguous outcome| UNKNOWN[UNKNOWN]
    TDS -->|lost acknowledgement| UNKNOWN
    UNKNOWN --> CUSTODY[Retained target custody]

Prepare and configure¶

The platform owner supplies dedicated connections, durable fenced storage, source DDL exclusion, target writer exclusion and the transaction-state catalog. The standard composition supplies transaction admission, source/schema binding, target-local custody, operational evidence and stateless checkpoint ordering. Set runtime.storage.work_dir and checkpoint_dir to storage that survives the worker process and is available to the recovery operator. A local ephemeral path cannot provide restart recovery.

The YAML example is for plan inspection; execution requires the platform capabilities described below. Use the native example and inspect it with:

dpone plan examples/native/clickhouse-to-mssql-native.yaml --format md

Supply an explicit execution interval.interval_end in UTC when constructing the load configuration. The example replaces the complete preceding day, including an empty source interval. Rows outside [start, end) and NULL window values remain unchanged. The interval never comes from values found in staging. Full refresh omits strategy.window and replaces the complete target under its existing catalog-preservation policy. Do not combine a native window with legacy partition options.

By default, only one local plain MergeTree table in an Atomic database is admitted. The mandatory source schema_guard_factory must exclude ALTER, RENAME, EXCHANGE, DROP/recreate and relevant access-policy changes from metadata admission until source cleanup. It does not need to freeze ordinary DML. A no-op context manager is suitable only for synthetic tests. The source records the nonzero table UUID and one query ID. A query ID is not a reusable snapshot token.

Exact raw rows from ReplacingMergeTree¶

The opt-in source selector preserves the multiset returned by one ordinary non-FINAL query. Use the raw snapshot example:

source:
  options:
    native_transfer:
      source_snapshot:
        mode: exact_raw_rows
        replica_scope: single_server
      execution:
        verification_backend: target_local

single_server requires ReplacingMergeTree; connected_replica requires ReplicatedReplacingMergeTree and a directly connected, identified replica. Both require an Atomic database. Shared engines, views, Distributed tables, custom SQL and ambiguous endpoints fail admission. BCP remains the default; the existing SqlClient selector and resource limits are independent. Set execution.verification_backend: target_local for raw mode: its durable v2 identity is required for source-free recovery. Raw mode with Python readback fails before I/O; legacy manifests retain their defaults.

Raw mode also inherits the target-local layout matrix. For example, a ReplacingMergeTree is_deleted UInt8 column normally maps to tinyint, which this verifier does not admit. Preserve its values through an explicit lossless bigint target mapping and provision the matching target:

sink:
  options:
    physical_design:
      columns:
        is_deleted:
          target_type:
            mssql: bigint

The schema planner validates that mapping; it does not bypass target admission. Other unsupported wire types require their own supported mapping or a separately qualified verifier extension. Raw mode cannot fall back to Python readback.

Use a direct native TLS connection with certificate and hostname verification; set the connection's ca_cert when the server uses a private CA. The raw session has one host and rejects automatic reconnect or replica rotation. Its catalog permission must expose relation, column, policy, settings, mutation and part metadata. Missing permissions or unsupported metadata fail before success.

The physical inventory is bounded to 8,192 active parts. A bounded window must use toYYYYMM, toYYYYMMDD or toDate on its UTC time column as the partition expression; unsupported partition expressions fail admission. Patch partitions are included conservatively. Timezone-less timestamp window columns require proven UTC server and session calendars; explicitly UTC timestamp columns are independent of those defaults. Inherited limit, offset and extremes are neutralized and sorting overflow throws. Unsupported additional table/result filters are rejected, never cleared; normal row policies remain enforced. Use the stable error codes and remediation in raw snapshot troubleshooting.

Raw rows are the query-visible versions that still exist when the SELECT acquires its snapshot. Background merges may already have removed older versions. dpone preserves identical duplicates and treats version and deletion marker columns as ordinary values. It does not run FINAL, deduplicate, or reconstruct insertion history. Normal access policies, lightweight-delete masks and supported patch parts still apply.

Start with dpone plan examples/native/clickhouse-replacing-to-mssql-native.yaml --format json. Planning reports declared semantics and pending live admission; it performs no credential lookup or network access. Supply the normal explicit UTC execution interval when running the example. Runtime checks the exact source relation, schema, TLS authority, settings, policies and replica before the data query. Pipeline check --connections is configuration-only and is not a source snapshot certificate.

At EOF, completion_metadata.source_snapshot_v1 binds the bounded source profile and EOF descriptors to the existing verified stage receipts. Hidden part provenance is removed before target encoding. Physical part checksums describe storage; the typed multiset digest verifies delivered values. endpoint_authority_agreement means the observed endpoints agree; it does not prove the absence of an intermediate change or equality with another replica.

Before EOF, settle the existing writer and target custody before starting a new invocation. Unknown custody remains held. After verified EOF, recovery validates the selector, source marker, descriptor hashes, schema, window and row count before opening the target; it never reconnects to ClickHouse. Use the existing native recovery commands below. Removing the selector affects future runs only and does not authorize cleanup of an uncertain publication.

Certification of this new source mode is UNVERIFIED until its own exact-build synthetic route, TLS, replica and recovery matrix is retained. Existing plain MergeTree or writer-only receipts do not certify raw snapshot support. See the approved source contract for the required cells and limitations.

The finite source types include scalar integers through UInt64, float, Decimal through precision 38, String, UUID, Date/Date32 and UTC DateTime/DateTime64 through six fractional digits, with nullable variants. Temporal values travel as integer microseconds to avoid driver floating-point conversion. Unsupported engines, views, computed columns, custom SQL, deduplication, CDC and uncomposed hooks fail admission. All ordinary table columns are extracted; authored column selection is rejected. NaN, Infinity, numeric overflow and fractional truncation are rejected. Text decoding is strict. The native encoder can preserve arbitrary bytes for an already admitted binary wire contract, but the canonical ClickHouse planner maps String to Unicode text. There is currently no authored binary-semantic mapping from ClickHouse String to MSSQL varbinary; a physical type override is rejected as a cross-family conversion. Binary encoder tests therefore do not certify that end-to-end source route. Its live binary profile remains UNVERIFIED. VARCHAR/CHAR are currently rejected by the importer, including when a collation is configured.

Target-local P1 layout matrix¶

verification_backend: target_local deliberately admits a narrower layout than the general native BCP transport. The generated TargetLocalLayoutMatrixV1 currently permits at most 100 business columns and only these SQL Server wire types; each may be nullable:

SQL Server wire type	ClickHouse source family	Target-local P1
`bigint`	signed integer mapped to `Int64`	admitted
`float(53)`	`Float64`	admitted
`nvarchar(max)`	ClickHouse `String` with Unicode text semantics	admitted
`datetime2(6)`	UTC `DateTime64(6)`	admitted

Decimal, smaller integer widths, UUID, Date/Date32, binary, varchar/char, and other otherwise valid native BCP layouts remain on python_readback until their target-local SQL digest framing has differential live proof. Readiness rejects an unsupported target-local layout before source extraction or writer launch. The content-addressed matrix is produced by TARGET_LOCAL_LAYOUT_MATRIX_V1; its exact-commit certification artifact is layout-matrix-v1.json. The same synthetic receipt pack records only allowlisted runtime/service versions and content-addressed image identities in environment-v1.json; it contains no host, port, login, database, schema, table, or path.

Prepared verification additionally admits only the framework-owned suffix types varchar(26), varchar(32), varchar(64), nvarchar(max), int, and datetime2(7). Application columns cannot use that suffix allowance.

Bounded delivery reuses frame sizes, projects canonical metadata in the prepared INSERT and computes business/full digests in one iterator. All four raw checks, the independent prepared prepublication check and the finalizer target-clock UPDATE remain. Follow delivery acceleration for structural evidence, optional observations and measurement instructions. These changes establish no measured acceleration and do not enable native partition SWITCH. Existing callers need no manifest or recovery migration.

Compose the runtime¶

Build the v2 identity with the framework-owned P1 factory. The plan already binds the target, source query, window, schema, and wire contract; the factory binds the reviewed BCP proof capabilities and timeout policy. Pass the same identity to NativeRuntimeBindings and compose_native_stage_context:

from dpone.contracts.mssql_native_verification_identity import (
    build_bcp_target_local_verification_identity,
)
from dpone.runtime.mssql_native_runtime import NativeRuntimeBindings
from dpone.runtime.sinks.mssql_native_composition import compose_native_stage_context

verification_identity = build_bcp_target_local_verification_identity(
    plan,
    timeout_seconds=3600,
)

context = compose_native_stage_context(
    # existing required capabilities omitted here
    verification_identity=verification_identity,
    target_local_timeout_seconds=3600,
)
bindings = NativeRuntimeBindings(
    service=service,
    stage_context=context,
    admission=transaction_admission,
    verification_identity=verification_identity,
)

Do not construct the identity hashes manually. A changed timeout or frozen plan produces a different invocation key and cannot resume unfinished work.

NativeMssqlRuntime accepts these required application capabilities:

Capability	Responsibility
`store`, `target_id`	Durable `WindowStore`, stable physical target identity and fenced CAS
`bindings(config, owner, lease, cancelled)`	Target-only restoration of one invocation and `NativeRuntimeBindings`
`source(config, binding)`	Owned context manager producing the existing `LoadPayload`; no source access during recovery
`preflight(config)`	Local and target capacity, permissions, required service admission
`quality(config, handle, lease)`	Configured quality gates against prepared staging before publication
`evidence(config, result, context, lease)`	Durable idempotent evidence bound to the exact commit receipt
`advance_state(config, result, lease)`	Fenced idempotent checkpoint CAS after evidence persistence
`custody_factory(store, target_id)`	Optional override for stable target custody; the framework default preserves old constructor calls and blocks v1 while v2 custody is held
`v2_journal_admission(journal, identity)`	Optional stricter deployment admission; the framework default requires the journal's immutable identity to equal the composed v2 identity

The binding contains a service, NativeStageContext and fresh MssqlTransactionAdmission. Set context.cancelled to the supplied event. Restore stable invocation, query, schema, wire and interval identities before opening the source. The context journal factory creates dpone.adapters.mssql_native_chunks_journal.NativeChunkJournal against the same store and lease. Do not create a new generation merely because a response was lost.

Construct MSSQLSink with native_staged_load_service_factory=lambda sink: MssqlNativeStagedLoadService(sink, MssqlNativeStagePreparer(sink, context_factory)). This constructor injection avoids mutating an already constructed sink. Use compose_native_stage_context from dpone.runtime.sinks.mssql_native_composition to assemble the independent importers, writer scopes, journal, verification, cleanup and capacity checks. Pass the existing BcpOptions class from dpone.runtime.connectors.mssql_bulk as bcp_options_factory at the application composition root. Its target session must use the configured staging/target database; differing staging and target databases are not admitted by this helper. The context owns:

BoundedNativeChunks with a context-manager importer factory. Every importer owns and closes a separate target connector, native wire columns, MssqlNativeEncoder.encode_row, and store.assert_lease.
native_stage_writer_scope on that same connector, keyed by the stable attempt identity. Old writers must settle before retry or removal. The preparation scope covers verification through commit and owns an independent same-database session created by target_connector.open_session(). The context releases its lock and closes that session on every exit. Reusing the target connector or opening a different database fails before preparation begins.
Real receipt verification and cleanup callbacks using importer inspect and settle; cleanup checks the table's persisted owner binding.
Capacity checks before extraction, preparation and publication. Use require_native_target_capacity to require catalog, volume and log-space observations; denied or missing observations are admission failures.
The existing MSSQL schema preplanner, transaction admission and extraction lifecycle. The completed payload persists schema, physical source UUID, lifecycle, mutation plan and operation identity for source-free recovery.

Pass the resulting runtime through DefaultProcessRunner(native_runtime_factory=lambda process_config: runtime). The factory must select the runtime for the actual route. The executable composition-order tests demonstrate callback ordering and recovery, and the real-finalizer tests exercise the concrete preparation scope with commit, lost acknowledgement and unknown outcomes using synthetic SQL fixtures. When COMMIT acknowledgement is lost, the finalizer can close its own connection and confirm the exact receipt while the preparation lock remains held. A confirmed commit continues to evidence and checkpoint completion; an unknown outcome retains its existing recovery path. These fixtures are not deployment authorities.

Resource limits and observations¶

native_transfer.execution.native_chunks requires max_total_encoded_bytes and stage_allocated_bytes_stop_threshold. Defaults are 65,536 rows, 16 MiB per chunk, 1 MiB per business row, two pending chunks and 1,024 staging tables. Parallelism defaults to 1 and is 1–64. Optional encoding_parallelism and import_parallelism under native_chunks independently fall back to execution.chunking.parallelism. Authored overrides must be strict integers 1–64; explicit YAML null is invalid. Python keyword-only overrides default to None, retaining all eight historical positional fields. Follow the concurrency how-to and its new example for resolved planner output and upgrade guidance. The pending bound is independent of the cumulative encoded-byte limit. One prepared table also counts toward the staging-table limit. Rows are limited to 1–1,000,000; chunk bytes to 1–1 GiB; row bytes must not exceed chunk bytes; pending chunks and parallelism are 1–64. Required cumulative limits and staging table counts are positive integers. Booleans and unknown limit keys are rejected.

The shared retained-work capacity is C = max(E, I) + max_pending, where E/I are the effective encoding/import limits. Import concurrency bounds file import/verify tasks, not all target connections. The local payload bound is (C + 1) * max_bytes, plus format/receipt overhead. The executor checks observed filesystem capacity with a 1 GiB reserve before worker startup. Driver allocation and process RSS are separate observations; encoded-byte limits do not guarantee physical RSS caps. Application preflight also checks capacity before extraction. SQL allocation counts reserved pages of all dpone_native_ staging tables in the configured database, including other invocations. SQL allocation and log headroom checks are observations, not exclusive reservations. Already active imports can overshoot an observed SQL stop threshold; exceeding it prevents successful stage completion/publication.

The journal stores per-phase worker observations, encoded bytes, rows, immutable file hashes and typed multiset digests. Effective parallelism uses observed worker active time and wall time. A digest is probabilistic evidence; small correctness certification cases must also compare exact typed multisets. Do not report the configured worker count as measured overlap.

Diagnose and recover¶

Every target-local invocation seals schema and transaction authority before ClickHouse business-row I/O. Read-only inspection needs only the durable SQLite journal. Live recovery actions require the original manifest selector and explicit confirmation; they reconnect only to MSSQL and never reopen the source.

Follow the MSSQL native recovery runbook for the complete inspect, inspect-all, reconcile, resume, and retire commands, state-to-action matrix, outputs, exit codes, safe retries, and escalation.

UNKNOWN retains custody across lease expiry. Held v2 custody blocks both v2 and rollback v1 invocations. Publication outcome is reconciled from the exact transaction receipt before any replay, cleanup, or custody release. Layout-v2 recovery also binds its persisted hash and mutation-watermark identity, as decided in ADR 0077.

For a first deployment, run admitted synthetic NULL, duplicate, Unicode, Decimal, empty-window and outside-window invariance cases in an explicitly approved disposable environment. Record authored binary source mapping as unsupported; encoder-only binary fixtures do not enable the canonical route. Test interrupted imports and lost commit acknowledgements. Record exact source commit, dependency/server versions and observed resources. Follow the approved design and route certification standard before promoting a specific environment. Existing manifests need no migration.