REST API -> MSSQL

This guide is a copy/paste-ready starting point for loading data from REST API into MSSQL with dpone.

When to use this path

Use this path when REST API is the system of record or ingestion boundary and MSSQL is the landing, warehouse, event-log, or downstream replication target.

Copy/paste manifest

name: api_to_mssql_example

source:
  type: api
  connection_id: example_api
  connection_type: vault
  options:
    api_type: rest
    method: GET
    url: https://api.example.com/orders
    records_path: data.items
    pagination:
      type: cursor
      cursor_path: data.next_cursor
      cursor_param: cursor
    incremental:
      cursor_field: updated_at
      start: "2026-01-01T00:00:00Z"

sink:
  type: mssql
  connection_id: mssql_dwh
  connection_type: vault
  table:
    schema: dbo
    name: orders
  strategy:
    mode: incremental_merge
    unique_key: order_id
    merge_policy: delete_insert
    duplicate_policy: fail
  options:
    bulk:
      mode: bcp
      bcp:
        batch_size: 100000
    field_terminator: "\t"
    row_terminator: "\n"

state:
  type: mssql
  connection_id: mssql_dwh
  table:
    schema: etl_state
    name: dpone_state

quality:
  mode: fail
  checks:
    - type: min_rows
      threshold: 1
    - type: source_target_count
      tolerance_pct: 0.1

observability:
  artifacts:
    enabled: true
    path: .dpone/runs/api_to_mssql

Run it locally:

dpone plan examples/api_to_mssql.yaml --format md
dpone batch run examples/api_to_mssql.yaml

Supported load strategies

Strategy	Status	Notes
full_refresh	Supported	Uses staging first, then applies the target-specific finalization plan.
incremental_append	Supported	Uses staging first, then applies the target-specific finalization plan.
incremental_merge	Supported	Default merge_policy: delete_insert; shadow_swap is available for DB targets.
replace	Supported	Uses staging first, then applies the target-specific finalization plan.
partition_replace	Supported	Replaces target partitions represented by staging partition.column; see Load strategies for native/fallback paths.
snapshot_reconciliation	Supported	Uses staging first, then applies the target-specific finalization plan.
cursor_incremental	REST API source only	Persists the response cursor or incremental field in state after sink success.

See Load strategies for the detailed algorithm for each strategy.

Runtime algorithm

flowchart TD
    A["Resolve manifest and registry entries"] --> B["Create REST API source"]
    B --> C["Plan bounded extract"]
    C --> D["Read through bounded paginated pull with streaming rows artifact"]
    D --> E["Emit ExtractResult with schema and artifact"]
    E --> F["Plan schema evolution"]
    F --> G["Create MSSQL staging or event batch"]
    G --> H["Load through BCP into staging followed by set-based MERGE/INSERT/REPLACE"]
    H --> I["Apply finalization strategy"]
    I --> J["Run quality and reconciliation checks"]
    J --> K["Advance state only after success"]

Strategy behavior

• full_refresh: extract the selected source boundary, load into staging, and replace the target according to the target's safe finalization path.
• incremental_append: extract only the incremental boundary and append rows through staging or event production.
• incremental_merge: load into staging, validate duplicates, then use delete_insert by default; shadow_swap is available where table swaps are supported.
• replace: reload a bounded predicate window through staging and then atomically replace the matching target slice.
• snapshot_reconciliation: compare the latest source snapshot with the target key set and apply configured physical-delete or soft-delete behavior through staging-first plans.
• partition_replace: extract a complete partition slice, load it into staging, and replace only partitions represented by partition.column.

Schema evolution and type mapping

Schema evolution is enabled by default and runs before the staging/final load path:

1. Read source schema from ExtractResult.schema.
2. Introspect the MSSQL target schema.
3. Apply safe additions and widening operations.
4. Fail breaking changes by default.
5. If configured, route incompatible type changes to __dpone__nc__<column>.

Use Schema evolution and Type mapping matrix when adding columns or changing source types.

Runbook

1. Start with dpone doctor --profile local and fix missing extras or native clients.
2. Run dpone plan <manifest> --format md and review source boundary, staging path, schema evolution, state, and quality checks.
3. Run a small bounded window first.
4. Inspect the run artifact under .dpone/runs/api_to_mssql.
5. For incremental jobs, verify state before enabling a schedule.
6. For delete-aware jobs, run reconciliation in report-only mode before enabling physical deletes.
7. Promote the manifest through GitOps after the plan and artifact are reviewed.

Cross-links

• Source -> sink matrix
• Load strategies
• Schema evolution
• Type mapping matrix
• Reconciliation and CDC
• Performance guide

Type contracts and physical design

This flow supports the shared dpone type-governance stack:

• Type inference for source metadata, sampled profiling, confidence, and empty string vs NULL behavior.
• Schema contracts for explicit logical column types, enforcement modes, and __dpone__nc__* variant columns.
• Physical design for target-specific DDL such as concrete SQL types, indexes, partitioning, compression, ClickHouse LowCardinality, and BigQuery clustering.

Use dpone schema infer --manifest ... and dpone schema physical-plan --manifest ... before enabling new table DDL in production.