Physical design

Physical design controls target-specific DDL: concrete column types, partitioning, indexes, clustering, table engines, compression, and storage settings. It complements schema contracts: contracts are portable, physical design is sink-specific.

Configuration

sink:
  options:
    physical_design:
      enabled: true
      mode: auto
      apply: online
      apply_runtime: true
      reconciliation:
        mode: block
      columns:
        status:
          target_type:
            clickhouse: LowCardinality(String)
      partitioning:
        strategy: auto
        column: business_date
        granularity: month
      indexes:
        strategy: auto
        primary_key: [order_id]
      storage:
        mssql:
          compression: page
          clustered_columnstore: false
          filegroup: DATA
          textimage_filegroup: LOB
          index_fillfactor: 90
        postgres:
          fillfactor: 90
        clickhouse:
          engine: MergeTree
          partition_by: toYYYYMM(business_date)
          order_by: [business_date, order_id]
          low_cardinality:
            mode: auto
        bigquery:
          partition_by: business_date
          clustering: [customer_id, status]

Apply modes

Mode	Behavior
online	Apply only low-risk online-safe changes automatically.
safe_window	Allow planned blocking DDL inside a maintenance window.
plan_only	Render DDL and risks, do not apply.
manual_approval	Require approval artifact before execution.

New target tables can receive the full physical design. Existing targets only receive online-safe changes automatically. The execution decision is handled by Physical DDL apply runtime, keeping planning, approval, and connector-specific execution testable.

Existing-table drift reconciliation

physical_design.reconciliation controls what happens when an existing target table differs from the desired physical_design contract.

Mode	Existing-table behavior
block	Default. Detect drift, emit blockers/evidence, and stop before load.
auto_safe	Apply only target-certified online-safe physical changes, then continue if no blockers remain.
safe_window	Apply target-certified blocking changes (for example MSSQL compression REBUILD) when approval evidence is attached.
plan_only	Emit the reconciliation plan and block before load; never execute DDL.

ClickHouse v1 supports auto_safe only for table settings that can be changed with ALTER TABLE ... MODIFY SETTING: Table settings manipulations. Some valid CREATE TABLE ... SETTINGS values, including index_granularity, are create-time physical design choices and are not treated as existing-table auto-safe changes. Engine, partition, sorting key, primary key, and physical column-type drift are blockers because they require a shadow table migration or schema-evolution workflow. This keeps runtime loads from silently changing storage layout.

Use the read-only CLI before changing production routes:

dpone schema physical-diff \
  --manifest manifests/orders.yaml \
  --actual actual-clickhouse-physical.json \
  --format json

The actual JSON uses the same portable shape emitted by target introspection:

{
  "sink_type": "clickhouse",
  "table": "landing.orders",
  "engine": "MergeTree",
  "partition_by": "toYYYYMM(created_at)",
  "order_by": ["created_at", "order_id"],
  "columns": {
    "order_id": {"type": "Nullable(Int64)"}
  },
  "table_settings": {
    "min_rows_for_wide_part": 0
  }
}

Target examples

MSSQL

sink:
  options:
    physical_design:
      indexes:
        primary_key: [order_id]
      storage:
        mssql:
          compression: page
          clustered_columnstore: false

Planned SQL shape:

CREATE TABLE [landing].[orders] (...) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX [ix_landing_orders_order_id] ON [landing].[orders] ([order_id]);

For existing tables, compression changes still require a blocking ALTER TABLE ... REBUILD WITH (DATA_COMPRESSION = PAGE) and therefore reconciliation.mode: safe_window plus explicit approval evidence:

physical_design:
  apply_runtime: true
  apply: safe_window
  reconciliation:
    mode: safe_window
    approval:
      approved_by: dba-team
      approved_risks:
        - table_settings.compression
      table: DWH_VI.ch.marketing__wa_reg_auth_app
      expires_at: "2026-07-11T21:00:00Z"
  storage:
    mssql:
      compression: page

Postgres

sink:
  options:
    physical_design:
      indexes:
        primary_key: [order_id]
      storage:
        postgres:
          fillfactor: 90

Planned SQL uses CREATE INDEX CONCURRENTLY for existing-table index paths where possible.

ClickHouse

sink:
  options:
    physical_design:
      storage:
        clickhouse:
          engine: MergeTree
          partition_by: toYYYYMM(business_date)
          order_by: [business_date, order_id]
          low_cardinality:
            mode: auto
            max_distinct_values: 10000
            max_distinct_ratio: 0.05

ClickHouse ORDER BY and PRIMARY KEY columns should normally be not nullable. ClickHouse can allow Nullable(...) key expressions with the MergeTree allow_nullable_key table setting, but its own MergeTree documentation describes Nullable key expressions as possible but strongly discouraged and states that NULL values sort with NULLS_LAST semantics: MergeTree primary keys and indexes. ClickHouse documents allow_nullable_key as a MergeTree table setting that can be supplied in the storage-specific SETTINGS clause of CREATE TABLE: allow_nullable_key, CREATE TABLE clause order.

dpone best practice:

1. Prefer physical_design.storage.clickhouse.nullability.mode: non_nullable_by_default for inferred ClickHouse key columns.
2. Use allow_nullable_key only as an explicit compatibility escape hatch when preserving nullable key semantics is more important than ClickHouse's recommended physical design.
3. Keep table settings separate from insert settings: allow_nullable_key belongs to target table creation, not clickhouse_bulk.insert_settings.

Target table-settings syntax:

sink:
  options:
    physical_design:
      storage:
        clickhouse:
          order_by: [optional_code]
          table_settings:
            allow_nullable_key: 1

table_settings is the storage-specific target table contract. It affects CREATE TABLE ... SETTINGS ...; it does not affect data loading. Insert-time settings still belong to sink.options.clickhouse_bulk.insert_settings, and inferred type nullability still belongs to physical_design.storage.clickhouse.nullability.

dpone validates table settings before DDL execution:

• setting names must be safe SQL identifiers;
• values must be scalar strings, numbers, integers, or booleans;
• known wrong-context settings fail with an actionable alternative;
• unknown safe ClickHouse settings are allowed with a warning because ClickHouse table settings evolve between dpone releases.

Wrong-context example:

sink:
  options:
    physical_design:
      storage:
        clickhouse:
          table_settings:
            async_insert: 1

This fails because async_insert is an insert setting. Use:

sink:
  options:
    clickhouse_bulk:
      insert_settings:
        async_insert: 1

BigQuery

sink:
  options:
    physical_design:
      storage:
        bigquery:
          partition_by: business_date
          clustering: [customer_id, status]

BigQuery physical design is planned as table creation or controlled recreation. Unsafe changes to existing partitioning/clustering are not applied silently.

ClickHouse LowCardinality modes

Mode	Behavior
off	Never generate LowCardinality.
auto	Use profiler cardinality thresholds for string-like columns.
explicit	Apply only to configured columns.
force	Apply to configured columns even when profiler disagrees, with warning.
preserve	Keep existing/source LowCardinality, but do not generate new ones.

Auto mode requires a string-like column and a stable low-cardinality profile:

• distinct_count <= 10000
• distinct_ratio <= 0.05
• column name does not look like an identifier, UUID, email, URL, hash, or token

LowCardinality is certified only for string-like columns by default. Numeric, date, timestamp, binary and JSON columns are not automatically wrapped. If a future ClickHouse-specific contract needs non-string LowCardinality, add it as an explicit documented physical override and extend the certification suite first.

Decision categories in physical plans:

Example	Category
physical_design.columns.amount.target_type.clickhouse: String	explicit_physical_override
schema_contract.columns.amount.type: decimal	explicit_logical_contract
Source metadata numeric(18,4) -> Decimal(18,4)	auto_inferred
No metadata/sample confidence	quarantine_required / safe fallback

CLI

dpone schema physical-plan --manifest manifests/orders.batch.yaml --format md
dpone plan manifests/orders.batch.yaml --selector public.orders --format json

Runbook

Symptom	Action
Index/compression DDL is blocked	Switch to safe_window or create an approval artifact.
ClickHouse query is slow after load	Review ORDER BY and partition cardinality.
LowCardinality hurts performance	Set low_cardinality.mode: off or move the column out of columns.
BigQuery partition change is required	Use a shadow/recreate plan, then validate downstream permissions.
MSSQL table is write-heavy	Prefer row compression or no compression over page compression.

Related docs

• Type inference
• Schema contracts
• Physical DDL apply
• Online schema evolution
• Performance