Skip to content

First local pipeline

Use this page when you want a realistic local database pipeline without external vendor credentials. The default integration stack runs PostgreSQL, MSSQL, ClickHouse, Kafka, Schema Registry, and MinIO through Docker Compose.

Start local services

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

Default local endpoints:

Service Endpoint
PostgreSQL 127.0.0.1:55432
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_USER=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_USER=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:

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
  table:
    schema: etl_state
    name: dpone_state

Plan and run

dpone doctor --profile local
dpone plan manifests/local_postgres_to_mssql.yaml
dpone run manifests/local_postgres_to_mssql.yaml
dpone run-report --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