Route live wide-type and full-strategy certification¶

This is the mandatory bar for claiming Batch ETL supported (or stronger) on any source→sink route.

Required evidence¶

Wide typed fixture covering the pair type profile’s round-tripable types (not a 3-column smoke table).
Every load strategy declared supported for that sink, each with semantic assertions (not only COUNT(*)).
Contract-required / unsupported types remain hermetic fail-closed tests and are listed as N/A for live with a reason.
SKIP/UNVERIFIED when environment or billing is missing — never report as PASS.

mysql→BigQuery reference implementation¶

Asset	Path
Feature design	`docs/feature-design-route-live-wide-certification-v1.md`
Shared MySQL gates	`tests/integration/mysql/mysql_live_support.py`
Wide fixture	`tests/integration/mysql/mysql_wide_type_fixtures.py`
Strategy configs	`tests/integration/mysql/mysql_bigquery_strategy_configs.py`
Assertions	`tests/integration/mysql/mysql_bigquery_assertions.py`
Live suite	`tests/integration/mysql/test_mysql_to_bigquery_vendor_live_integration.py`

Run:

docker compose -f docker/docker-compose.integration.yml up -d mysql
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
export BIGQUERY_DWH_PROJECT_ID=<project>
export BIGQUERY_DWH_SERVICE_ACCOUNT_KEY_FILE=/absolute/path/sa.json
uv run pytest tests/integration/mysql/test_mysql_to_bigquery_vendor_live_integration.py -q

postgres→BigQuery reference implementation¶

Asset	Path
Feature design	`docs/feature-design-postgres-route-live-wide-certification-v1.md`
Shared Postgres gates	`tests/integration/postgres/postgres_live_support.py`
Wide fixture	`tests/integration/postgres/postgres_bigquery_wide_fixtures.py`
Strategy configs	`tests/integration/postgres/postgres_bigquery_strategy_configs.py`
Assertions	`tests/integration/postgres/postgres_bigquery_assertions.py`
Live suite	`tests/integration/postgres/test_postgres_to_bigquery_vendor_live_integration.py`
Type profile	`postgres_to_bigquery_analytics_v1`

Run:

docker compose -f docker/docker-compose.integration.yml up -d postgres
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
export BIGQUERY_DWH_PROJECT_ID=<project>
export BIGQUERY_DWH_SERVICE_ACCOUNT_KEY_FILE=/absolute/path/sa.json
export DPONE_IT_BQ_DATASET=dpone_it_postgres
uv run pytest tests/integration/postgres/test_postgres_to_bigquery_vendor_live_integration.py -q

postgres→Postgres reference implementation¶

Asset	Path
Feature design	`docs/feature-design-postgres-route-live-wide-certification-v1.md`
Shared Postgres gates	`tests/integration/postgres/postgres_live_support.py`
Wide fixture	`tests/integration/postgres/postgres_postgres_wide_fixtures.py`
Strategy configs	`tests/integration/postgres/postgres_postgres_strategy_configs.py`
Assertions	`tests/integration/postgres/postgres_postgres_assertions.py`
Live suite	`tests/integration/postgres/test_postgres_to_postgres_vendor_live_integration.py`
Type profile	`postgres_to_postgres_identity_v1`

Run:

docker compose -f docker/docker-compose.integration.yml up -d postgres
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv run pytest tests/integration/postgres/test_postgres_to_postgres_vendor_live_integration.py -q

postgres→MSSQL reference implementation¶

Asset	Path
Feature design	`docs/feature-design-postgres-route-live-wide-certification-v1.md`
Shared Postgres gates	`tests/integration/postgres/postgres_live_support.py`
Wide fixture	`tests/integration/postgres/postgres_mssql_wide_fixtures.py`
Strategy configs	`tests/integration/postgres/postgres_mssql_strategy_configs.py`
Assertions	`tests/integration/postgres/postgres_mssql_assertions.py`
Live suite	`tests/integration/postgres/test_postgres_to_mssql_vendor_live_integration.py`
Type profile	`postgres_to_mssql_native_v2`

Run:

docker compose -f docker/docker-compose.integration.yml up -d postgres mssql
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv run pytest tests/integration/postgres/test_postgres_to_mssql_vendor_live_integration.py -q

bytea/varbinary uses hex character BCP + CONVERT(varbinary, …, 2) (see docs/feature-design-mssql-hex-binary-character-bcp-v1.md).

postgres→ClickHouse reference implementation¶

Asset	Path
Feature design	`docs/feature-design-postgres-route-live-wide-certification-v1.md`
Shared Postgres gates	`tests/integration/postgres/postgres_live_support.py`
Wide fixture	`tests/integration/postgres/postgres_clickhouse_wide_fixtures.py`
Strategy configs	`tests/integration/postgres/postgres_clickhouse_strategy_configs.py`
Assertions	`tests/integration/postgres/postgres_clickhouse_assertions.py`
Live suite	`tests/integration/postgres/test_postgres_to_clickhouse_vendor_live_integration.py`
Type profile	`postgres_to_clickhouse_analytics_v1`

Run:

docker compose -f docker/docker-compose.integration.yml up -d postgres clickhouse
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv run pytest tests/integration/postgres/test_postgres_to_clickhouse_vendor_live_integration.py -q

snapshot_diff / scd2 use staging-first ClickHouse finalizers (docs/feature-design-clickhouse-snapshot-diff-scd2-v1.md).

mssql→ClickHouse reference implementation¶

Asset	Path
Feature design	`docs/feature-design-mssql-route-live-wide-certification-v1.md`
Shared MSSQL gates	`tests/integration/mssql/mssql_live_support.py`
Wide fixture	`tests/integration/mssql/mssql_clickhouse_wide_fixtures.py`
Strategy configs	`tests/integration/mssql/mssql_clickhouse_strategy_configs.py`
Assertions	`tests/integration/mssql/mssql_clickhouse_assertions.py`
Live suite	`tests/integration/mssql/test_mssql_to_clickhouse_vendor_live_integration.py`
Type profile	`mssql_to_clickhouse_lossless_v2`

Run:

docker compose -f docker/docker-compose.integration.yml up -d mssql clickhouse
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv run pytest tests/integration/mssql/test_mssql_to_clickhouse_vendor_live_integration.py -q

snapshot_diff / scd2 use staging-first ClickHouse finalizers (docs/feature-design-clickhouse-snapshot-diff-scd2-v1.md). Phase A1 of docs/feature-design-mssql-route-live-wide-certification-v1.md.

mssql→Postgres reference implementation¶

Asset	Path
Feature design	`docs/feature-design-mssql-route-live-wide-certification-v1.md`
Shared MSSQL gates	`tests/integration/mssql/mssql_live_support.py`
Wide fixture	`tests/integration/mssql/mssql_postgres_wide_fixtures.py`
Strategy configs	`tests/integration/mssql/mssql_postgres_strategy_configs.py`
Assertions	`tests/integration/mssql/mssql_postgres_assertions.py`
Live suite	`tests/integration/mssql/test_mssql_to_postgres_vendor_live_integration.py`
Type profile	`mssql_to_postgres_native_v1`

Run:

docker compose -f docker/docker-compose.integration.yml up -d mssql postgres
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv run pytest tests/integration/mssql/test_mssql_to_postgres_vendor_live_integration.py -q

Postgres-safe CSV extract (not mssql-delimited); varbinary → bytea via \x hex CSV. Phase A3 of docs/feature-design-mssql-route-live-wide-certification-v1.md.

mssql→Kafka reference implementation¶

Asset	Path
Feature design	`docs/feature-design-mssql-route-live-wide-certification-v1.md`
Shared MSSQL gates	`tests/integration/mssql/mssql_live_support.py`
Wide fixture	`tests/integration/mssql/mssql_kafka_wide_fixtures.py`
Strategy configs	`tests/integration/mssql/mssql_kafka_strategy_configs.py`
Assertions	`tests/integration/mssql/mssql_kafka_assertions.py`
Live suite	`tests/integration/mssql/test_mssql_to_kafka_vendor_live_integration.py`

Run:

docker compose -f docker/docker-compose.integration.yml up -d mssql kafka
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv sync --extra kafka --extra mssql
uv run pytest tests/integration/mssql/test_mssql_to_kafka_vendor_live_integration.py -q

partition_replace / scd2 and backfill inner modes other than keyed upsert replay are N/A for KafkaSink. Phase A4 of docs/feature-design-mssql-route-live-wide-certification-v1.md.

mssql→BigQuery reference implementation¶

Asset	Path
Feature design	`docs/feature-design-mssql-route-live-wide-certification-v1.md`
Shared MSSQL gates	`tests/integration/mssql/mssql_live_support.py`
Wide fixture	`tests/integration/mssql/mssql_bigquery_wide_fixtures.py`
Strategy configs	`tests/integration/mssql/mssql_bigquery_strategy_configs.py`
Assertions	`tests/integration/mssql/mssql_bigquery_assertions.py`
Live suite	`tests/integration/mssql/test_mssql_to_bigquery_vendor_live_integration.py`
Type profile	`mssql_to_bigquery_analytics_v1`

Run:

docker compose -f docker/docker-compose.integration.yml up -d mssql
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
export BIGQUERY_DWH_PROJECT_ID=<project>
export BIGQUERY_DWH_SERVICE_ACCOUNT_KEY_FILE=/absolute/path/sa.json
export DPONE_IT_BQ_DATASET=dpone_it_mssql
uv sync --extra mssql --extra gcp
uv run pytest tests/integration/mssql/test_mssql_to_bigquery_vendor_live_integration.py -q

CSV extract with base64 varbinary→BYTES; all eight BigQuery strategies. Phase A5 of docs/feature-design-mssql-route-live-wide-certification-v1.md.

mysql→Kafka reference implementation¶

Asset	Path
Shared MySQL + Kafka gates	`tests/integration/mysql/mysql_live_support.py`
Wide fixture	`tests/integration/mysql/mysql_kafka_wide_fixtures.py`
Strategy configs	`tests/integration/mysql/mysql_kafka_strategy_configs.py`
Assertions	`tests/integration/mysql/mysql_kafka_assertions.py`
Live suite	`tests/integration/mysql/test_mysql_to_kafka_vendor_live_integration.py`

Run:

docker compose -f docker/docker-compose.integration.yml up -d mysql kafka
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv run pytest tests/integration/mysql/test_mysql_to_kafka_vendor_live_integration.py -q

postgres→Kafka reference implementation¶

Asset	Path
Feature design	`docs/feature-design-postgres-route-live-wide-certification-v1.md`
Shared Postgres gates	`tests/integration/postgres/postgres_live_support.py`
Wide fixture	`tests/integration/postgres/postgres_kafka_wide_fixtures.py`
Strategy configs	`tests/integration/postgres/postgres_kafka_strategy_configs.py`
Assertions	`tests/integration/postgres/postgres_kafka_assertions.py`
Live suite	`tests/integration/postgres/test_postgres_to_kafka_vendor_live_integration.py`

Run:

docker compose -f docker/docker-compose.integration.yml up -d postgres kafka
export DPONE_RUN_INTEGRATION=1 DPONE_RUN_INTEGRATION_LIVE=1
uv run pytest tests/integration/postgres/test_postgres_to_kafka_vendor_live_integration.py -q

partition_replace / scd2 and backfill inner modes other than keyed upsert replay are N/A for KafkaSink.

Applying to other integrations¶

When adding or claiming a route:

Copy the helper split pattern (*_live_support, *_wide_type_fixtures, *_strategy_configs, *_assertions, thin test_*).
Map the pair type profile into a wide DDL + seed.
Cover every strategy in the sink factory (or document N/A).
Update matrix/guide only after live PASS evidence for that breadth.
Keep credentials out of git, logs, and artifacts.

Peer guides may already say Batch ETL from earlier narrow smoke evidence; migrate those live suites to this wide/strategy bar before the next certification claim bump. mysql→{bigquery,postgres,mssql,clickhouse,kafka}, postgres→{bigquery,postgres,mssql,clickhouse,kafka}, and mssql→{clickhouse,mssql,postgres,kafka,bigquery} meet the wide bar (mssql→CH/MSSQL binary columns use hex character BCP; mssql→Postgres/Kafka/BQ use CSV; mssql→Postgres varbinary → bytea via \x hex; mssql→BigQuery varbinary → BYTES via base64 CSV; ClickHouse includes snapshot_diff/scd2; Kafka excludes partition_replace/scd2 and backfill inner modes other than keyed upsert replay as documented N/A). Phase A of docs/feature-design-postgres-route-live-wide-certification-v1.md is complete; Phase B1 closes the mssql binary character-BCP N/A; Phase B2 closes ClickHouse snapshot_diff/scd2. Phase A of docs/feature-design-mssql-route-live-wide-certification-v1.md is complete (A1–A5: mssql→clickhouse, mssql→mssql, mssql→postgres, mssql→kafka, mssql→bigquery). Phase C adds the self-service golden path and landing example parity for mssql→sink guides (docs/feature-design-certified-route-recipe-golden-path-v1.md; postgres/mysql baseline: docs/feature-design-certified-route-self-service-ux-v1.md). Phase D adds gated live CI (docs/feature-design-route-live-wide-ci-v1.md).

CI (Phase D)¶

Workflow: .github/workflows/route-live-wide-certification.yml

Trigger	Legs
`schedule` (nightly `15 3 * * *`)	Docker only: `{postgres,mysql,mssql}` × `{postgres,mssql,clickhouse,kafka}`
`workflow_dispatch`	Same Docker matrix (filterable via `source` / `sink`); optional BigQuery when `include_bigquery=true`

Policy (SKIP ≠ PASS):

Preflight fail-closed (compose health + DPONE_RUN_INTEGRATION=1; BQ secrets required when BigQuery legs are requested).
After pytest, tools/ci/assert_junit_executed.py fails the job when junit is missing, empty, all-skipped, or passed < 1.
Default PR ci.yml still excludes integration_live — this workflow is opt-in / scheduled only.

Manual dispatch examples:

# One Docker leg
gh workflow run route-live-wide-certification.yml \
  -f source=postgres -f sink=mssql -f include_bigquery=false

# BigQuery legs (requires repo secrets BIGQUERY_DWH_PROJECT_ID +
# BIGQUERY_DWH_SERVICE_ACCOUNT_JSON)
gh workflow run route-live-wide-certification.yml \
  -f source=all -f sink=bigquery -f include_bigquery=true

Artifacts upload as route-live-wide-{source}-{sink} (junit under test_artifacts/route_live_wide/). Keep credentials out of git, logs, and artifacts.

Strategy metadata on file exports¶

snapshot_diff and scd2 require __dpone__row_hash (and SCD2 validity columns) in staging. StrategyMetadataEnricher applies this for:

InMemoryRowsArtifact / StreamingRowsArtifact
delimited FileExportArtifact families (csv, mssql-delimited, clickhouse-tsv), including partitioned, batched, and physically chunked exports

Unsupported artifact/wire types fail closed — they must never silently skip enrichment. Live IT should call the enricher (or ETLProcessor) before sink.load, matching production.