Skip to content

First local pipeline

Use this page when you want a realistic local database pipeline without external vendor credentials. The tutorial uses PostgreSQL and MSSQL; the same integration stack also provides MySQL, ClickHouse, Kafka, Schema Registry, and MinIO for the next routes.

Start local services

docker compose -f docker/docker-compose.integration.yml up -d --build postgres mysql mssql clickhouse kafka schema-registry minio
docker compose -f docker/docker-compose.integration.yml ps

The test-only S3 server is built from a pinned upstream source revision on the first run, so allow extra time for the initial image build. Later runs reuse the local image unless its build inputs change.

Default local endpoints:

Service Endpoint
PostgreSQL 127.0.0.1:55432
MySQL 127.0.0.1:53306
MSSQL 127.0.0.1:51433
ClickHouse native 127.0.0.1:59000
ClickHouse HTTP 127.0.0.1:58123
Kafka 127.0.0.1:59092
Schema Registry http://127.0.0.1:58081
MinIO 127.0.0.1:59090

Bootstrap test data

Create a PostgreSQL table:

PGPASSWORD=dpone psql \
  -h 127.0.0.1 \
  -p 55432 \
  -U dpone \
  -d dpone_it \
  -c "CREATE SCHEMA IF NOT EXISTS demo;"

PGPASSWORD=dpone psql \
  -h 127.0.0.1 \
  -p 55432 \
  -U dpone \
  -d dpone_it \
  -c "DROP TABLE IF EXISTS demo.orders;
      CREATE TABLE demo.orders (
        order_id bigint PRIMARY KEY,
        customer_id bigint,
        amount numeric(18, 2),
        status text,
        updated_at timestamptz
      );
      INSERT INTO demo.orders
      SELECT i, 1000 + i, i * 1.25, CASE WHEN i % 2 = 0 THEN 'paid' ELSE 'new' END, now()
      FROM generate_series(1, 1000) AS i;"

Create the target database in MSSQL if needed:

sqlcmd -S 127.0.0.1,51433 -U sa -P 'Dp0ne.Strong.Pw.2026!' -C \
  -Q "IF DB_ID('dpone_it') IS NULL CREATE DATABASE [dpone_it];"

Configure local credentials

export DPONE_CONN_POSTGRES_DEMO_HOST=127.0.0.1
export DPONE_CONN_POSTGRES_DEMO_PORT=55432
export DPONE_CONN_POSTGRES_DEMO_DATABASE=dpone_it
export DPONE_CONN_POSTGRES_DEMO_USERNAME=dpone
export DPONE_CONN_POSTGRES_DEMO_PASSWORD=dpone

export DPONE_CONN_MSSQL_DEMO_HOST=127.0.0.1
export DPONE_CONN_MSSQL_DEMO_PORT=51433
export DPONE_CONN_MSSQL_DEMO_DATABASE=dpone_it
export DPONE_CONN_MSSQL_DEMO_USERNAME=sa
export DPONE_CONN_MSSQL_DEMO_PASSWORD='Dp0ne.Strong.Pw.2026!'
export DPONE_CONN_MSSQL_DEMO_TRUST_SERVER_CERTIFICATE=yes

Create a manifest

Create manifests/local_postgres_to_mssql.yaml:

name: local_postgres_to_mssql

source:
  type: postgres
  connection_id: postgres_demo
  connection_type: env
  table:
    schema: demo
    name: orders
  options:
    incremental_column: updated_at

sink:
  type: mssql
  connection_id: mssql_demo
  connection_type: env
  table:
    schema: landing
    name: orders
  strategy:
    mode: incremental_merge
    unique_key: order_id
  options:
    bulk:
      mode: bcp
    schema_evolution:
      enabled: true

state:
  type: mssql
  connection_id: mssql_demo
  connection_type: env
  table:
    schema: etl_state
    name: dpone_state

runtime:
  compatibility:
    legacy_runtime_connections: explicit_only

This local tutorial explicitly enables the temporary compatibility bridge for direct connection_id credentials. Use logical connection_ref bindings for production deployments.

Plan and run

dpone doctor --profile local
dpone plan manifests/local_postgres_to_mssql.yaml --format json
dpone run manifests/local_postgres_to_mssql.yaml --format json

The run JSON is the authoritative result of this execution. The separate dpone run-report command creates a manual/synthetic artifact and does not support --latest.

Validate the result

sqlcmd -S 127.0.0.1,51433 -U sa -P 'Dp0ne.Strong.Pw.2026!' -C -d dpone_it \
  -Q "SELECT COUNT(*) AS rows_loaded FROM [landing].[orders];"

Next steps