Where pipelines can write

A Load step delivers its rows to a target. This is the half that makes a pipeline a data-movement tool rather than an ingestion funnel: the same extract-and-transform can land in the platform’s warehouse, in your own database, or as files in your own bucket.

Three target kinds, on tabs in the editor:

Kind	Writes to	Modes
Dataset	a managed dataset in the tenant’s ClickHouse	Replace · Append · Merge
Database	your own database or warehouse	Replace · Append · Merge
Files	your own object store	Replace · Append

Every target names its destination by connection id. Inline secrets are refused by the schema, not merely discouraged — a password can’t ride along in a DAG.

Dataset

The managed dataset is the default: it’s what dashboards, models and the SQL editor read.

{ "datasetName": "orders_daily", "mode": "merge", "mergeKeys": ["order_id"] }

• Replace (full refresh) — the dataset becomes exactly this run’s rows.
• Append — rows are added.
• Merge (upsert by key) — rows replace existing rows sharing the same merge keys (1–16 columns). See Merge semantics below.

Database

Write back into your own database — the destination connection’s kind picks the write path, and you don’t configure it:

Family	Engines	How it writes
Postgres wire	Postgres, CockroachDB, TimescaleDB, YugabyteDB, Neon, Supabase, AlloyDB, Azure/RDS/Cloud SQL Postgres, Materialize…	COPY FROM STDIN
MySQL wire	MySQL, MariaDB, TiDB, SingleStore, PlanetScale, Azure/RDS/Cloud SQL MySQL…	batched INSERTs
TDS	SQL Server, Azure SQL, Azure Synapse, Fabric Warehouse	batched INSERTs
Cloud warehouses	Snowflake, BigQuery, Redshift	native bulk COPY from a staging bucket

{
  "target": {
    "kind": "database",
    "sourceId": "8f14e45f-…",
    "table": "analytics.orders",
    "mode": "merge",
    "mergeKeys": ["order_id"],
    "createTable": true
  }
}

table is schema.table or a bare table (the connection’s default schema). createTable defaults to true — the table is created from the incoming schema when it doesn’t exist.

Replace never drops your table

replace is a truncate + insert inside one transaction — so your DDL, indexes, constraints and grants survive. It is never a drop-and-recreate, and readers never see a half-written table.

Bulk staging for warehouses

A cloud warehouse loaded with row INSERTs takes minutes where a COPY takes seconds. So the warehouse reads the Parquet itself, in parallel, out of a staging bucket you own:

• Snowflake and BigQuery require staging. Without it the step is refused and the error names the fix — rather than quietly row-streaming for an hour.
• Redshift falls back to row INSERTs, loudly, when staging isn’t set.

The staging bucket is not a preference — a warehouse can only unload into its own cloud’s storage:

Warehouse	Staging bucket
Snowflake	S3
Redshift	S3
BigQuery	GCS

A mismatch (BigQuery to S3) is refused with the reason.

Set bulkStaging on the destination connection’s config:

{
  "bulkStaging": {
    "kind": "s3",
    "bucket": "my-staging-bucket",
    "region": "eu-west-1",
    "prefix": "datasquares/",
    "accessKeyId": "AKIA…",
    "secretAccessKey": "…"
  }
}

For BigQuery, { "kind": "gcs", "bucket": "my-staging-bucket" }.

Staged files land under a per-run prefix and are swept afterwards. A bulkStaging block that is present but malformed fails loudly — a silent fallback to row-streaming would mask a config typo as a performance regression.

Two honest caveats

• There’s no connection-form field for bulkStaging yet — it rides the connection’s config JSON.
• The Snowflake / BigQuery / Redshift COPY paths are statement-pinned, not yet live-verified against real accounts.

Files

Write Parquet, CSV or JSON into S3, Azure Blob, ADLS Gen2 or GCS. Format conversion is free — Parquet is already the canonical wire form — and a transform can sit in between with no cluster.

Target connections can be created in the connection wizard under Data Sources or via POST /api/sources.

{
  "target": {
    "kind": "file",
    "sourceId": "8f14e45f-…",
    "path": "exports/orders/",
    "format": "parquet",
    "compression": "snappy",
    "partitionBy": ["order_date"],
    "mode": "replace"
  }
}

Field	Notes
Path	ending in / writes a directory (one object per part); anything else writes a single object
Format	parquet, csv or json
Compression	none, gzip, zstd, or snappy (Parquet only)
Partition by	1–4 columns → a hive col=value/ folder tree. Directory paths only
Mode	replace or append
Header	CSV/JSON only — Parquet carries its own schema

Hive partitioning turns partitionBy: ["order_date"] into exports/orders/order_date=2026-07-14/…parquet — the layout Athena, BigQuery external tables, Spark and DuckDB all prune on.

Replace is surgical

replace clears only this step’s own output objects under the path. It is never a blind prefix purge — a sibling pipeline writing elsewhere under the same prefix is untouched.

API / webhook

Deliver the step’s rows to any HTTP endpoint as batched JSON POSTs — the machinery reverse-ETL rides on. The endpoint lives in a webhook connection (URL, optional HMAC secret, optional static headers — all encrypted).

{
  "target": {
    "kind": "api",
    "sourceId": "8f14e45f-…",
    "batchSize": 500,
    "requestsPerSecond": 5,
    "retries": 3
  }
}

Each request body is a JSON array of row objects. The headers carry:

Header	Meaning
X-DataSquares-Signature	sha256=<hex> — HMAC-SHA256 over timestamp.deliveryId.payload with the connection’s secret
X-DataSquares-Timestamp	signed, so staleness checks can’t be tampered with
X-DataSquares-Delivery-Id	stable across retries — dedupe key for the receiver
X-DataSquares-Batch	0-based batch index

It is the same signature scheme as alert webhooks, so a receiver verifies every DataSquares delivery one way:

const signed = `${timestamp}.${deliveryId}.${rawBody}`;
const expect = "sha256=" + hmacSha256Hex(secret, signed);

Failure policy, per batch:

• 429 / 503 — waits for Retry-After (heartbeat-sliced, capped) without spending a retry.
• 5xx / network — exponential backoff, up to retries attempts.
• Any other 4xx — dead-letters immediately; retrying a 400 wastes the receiver’s calls.

A batch that can’t deliver lands on the step’s deadletter port as a lake part — the original rows plus _ds_error and _ds_batch. The part is written even when empty, so a port consumer never special-cases a clean day. Connect the port to a load to inspect the failures, or to another API sink to replay them — the sink strips its own _ds_ annotations on the way out, so a replayed delivery is byte-clean. Nothing is ever silently dropped.

Rate-limit manners

requestsPerSecond paces the client side; Retry-After is honored regardless. The delivery id staying stable across retries means a receiver that dedupes by it is safe even when a success response is lost in transit.

Merge semantics

Merge is a real upsert: rows replace existing rows sharing the same merge keys, instead of appending duplicates or dropping the table. It’s what turns an incremental extract or a CDC stream from insert-only into genuine change application.

In the managed dataset it’s a ClickHouse ReplacingMergeTree(_ds_version) ordered by your merge keys.

A differently-keyed table is refused, never dropped

If a target table already exists but is keyed or versioned differently, the load is refused with the reason. Your data is never silently discarded to make the new shape fit.

CDC deletes

Rows arriving from a CDC stream carry three system columns:

Column	Meaning
_ds_op	the change: insert, update, delete
_ds_deleted	whether this row is a deletion
_ds_version	event time (microsecond tie-break) — newest version wins

The database sink applies deletes: a deleted row upstream becomes a deleted row in your table, not a stale survivor. Pair a CDC extract with a Merge load on the primary key and your destination tracks the source.

Fan-out: one step, up to 8 destinations

One load step can deliver the same rows to up to 8 targets. The rows are extracted and transformed once and written N times, in order — so a failure names exactly which destinations already have the data.

{
  "targets": [
    { "kind": "dataset", "datasetName": "orders", "mode": "replace" },
    {
      "kind": "database",
      "sourceId": "8f14e45f-…",
      "table": "analytics.orders",
      "mode": "replace"
    },
    {
      "kind": "file",
      "sourceId": "3c6e0b8a-…",
      "path": "exports/orders/",
      "format": "parquet",
      "mode": "replace"
    }
  ]
}

API only

Fan-out is DAG-JSON / API only — there’s no UI for it yet; the editor writes a single target. A load step’s config takes exactly one of datasetName, target, or targets.

Limits worth knowing

• Fan-out has no UI — API or DAG JSON only.
• File destinations write Parquet, CSV or JSON. Spreadsheet formats and SFTP delivery aren’t part of the editor’s destination tabs today.
• The warehouse COPY paths are statement-pinned, not live-verified against real Snowflake / BigQuery / Redshift accounts yet.
• bulkStaging has no connection-form field — it rides the connection config JSON.
• Decimals land as double — values past 15 significant digits are not exact.

Related

• Building a pipeline — the load step in the editor.
• Change data capture — where _ds_deleted comes from.
• Connector catalog — the connections a target can name.
• Pipelines API