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.

Five 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
Sheets	a tab of a Google Sheets spreadsheet	Replace · Append
API	any HTTP endpoint, as batched JSON POSTs	—

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	batched INSERTs
Cloud warehouses	Snowflake, BigQuery, Redshift	native bulk COPY from a staging bucket

A Fabric Warehouse connection is not a destination yet: it signs in only with Microsoft Entra ID, and the load is refused, saying why, before it reads a row.

{
  "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.

What each mode does in your database

The modes mean the same on every database family:

• Append adds the rows all or nothing. Postgres-wire engines take them in one COPY; MySQL and SQL Server take every INSERT inside one transaction. A load that fails part-way writes nothing, so its retry never duplicates rows.
• Replace swaps the rows inside one transaction (below).
• Merge stages the batch, then deletes the rows of your table that share a merge key with it and inserts the batch, both in one transaction. Your table needs no unique index. See Merge semantics.

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.

A MySQL table without transactions

A MySQL table on an engine without transactions (MyISAM) cannot roll back: an append that fails part-way keeps the rows it already wrote. On a MySQL-wire service that caps one transaction (PlanetScale’s transaction timeout, TiDB’s txn-total-size-limit, Group Replication’s transaction size limit), one load’s rows must fit in one transaction, as a replace or merge already must; split a larger load into several steps.

Dates, times and large rows (MySQL and SQL Server)

• Dates, times and timestamps arrive exact. A time lands as a time of day, timestamps keep their microseconds, and a timestamp with a time zone lands as its UTC time. A time column the load creates on MySQL is TIME(6), so it keeps its microseconds too.
• Refused by name, never altered: an infinite date or timestamp (infinity, -infinity) and a date or timestamp outside the years 1–9999 (a BC date, a five-digit year; a timestamp with a time zone is judged by its UTC time), which neither database can store, and a nanosecond timestamp with digits below the microsecond. The load stops before it writes anything and names the column. For a year outside 1–9999 it also names the value and, on a merge, the row by its key. Replace or cast the value in a transform step.
• Large rows on MySQL go out in as many statements as the server’s max_allowed_packet allows. A single row bigger than that is refused, naming both sizes: raise max_allowed_packet on the server, or split the large values first.

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.

On a Snowflake connection, fill in Bulk staging (S3) in the connection form (bucket, optional prefix and region, access key ID and secret). On Redshift and BigQuery there is no form field yet: set bulkStaging on the 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

• Only Snowflake has a connection-form field for bulk staging; on Redshift and BigQuery bulkStaging 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, JSON, an Excel workbook, or a Delta Lake or Apache Iceberg table into S3, an S3-compatible store (MinIO, Ceph, R2, B2, Wasabi, Spaces, Scaleway, OVHcloud, IBM COS — in Preview), Azure Blob, ADLS Gen2, GCS or SFTP. 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. A Delta or Iceberg table is a folder (end it with /); an Excel workbook is one file
Format	parquet, csv, json, xlsx, delta (Preview) or iceberg (Preview)
Compression	none, gzip, zstd, or snappy (Parquet, Delta and Iceberg only); none for xlsx, which is already a compressed archive
Partition by	1–4 columns → a hive col=value/ folder tree. Directory paths only; not for xlsx
Mode	replace or append
Header	CSV, JSON and Excel only — Parquet, Delta and Iceberg carry their own schema
xlsx	Excel only: sheetName (at most 31 characters, none of : \ / ? * [ ]), freezeHeader and autoFilter (both on unless set to false)
catalog	Iceberg only: publish through the connection’s Iceberg REST catalog — see Apache Iceberg tables

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. A Files extract with Folder names are columns on reads the tree back with order_date as a column again — as text; type it with hiveTypes or a cast step.

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.

Excel workbooks

format: "xlsx" writes one workbook at the exact path, replaced on every run, with typed cells (numbers stay numbers, dates stay dates), the header row frozen and filter dropdowns on it unless you turn them off. A step with more rows than Excel’s own limit of 1,048,576 is refused, naming the number — write Parquet or CSV for data that size. SFTP is a fine home for a workbook a partner collects.

Delta Lake tables (Preview)

format: "delta" writes a Delta table: Parquet data files plus a _delta_log/ commit that Spark, Databricks and DuckDB (delta_scan) read as a table.

• append adds a commit with the new files; replace commits a new version that takes the old files out of the table. The old files stay in the store, so time travel works until someone runs VACUUM — the destination never deletes data.
• A retried step never commits twice.
• An append must match the table’s schema and partitioning; a replace may change them.
• Refused, never guessed at: column types Delta cannot hold (lists, structs, maps, time, interval — the column is named), and a table Spark has checkpointed.
• One pipeline per table: the stores have no put-if-absent, so two writers to the same table are not supported.
• S3, Azure and GCS only — the table records absolute file addresses.

Apache Iceberg tables (Preview)

format: "iceberg" writes an Apache Iceberg v2 table: Parquet data files with Iceberg field ids, plus Avro manifests. There are two ways to publish it.

Catalog-less (the default). The table is its folder: metadata/vN.metadata.json and metadata/version-hint.text beside the manifests. No catalog service or extra credentials are involved, and engines read it by path — DuckDB iceberg_scan, Spark’s Hadoop catalog, Trino’s file metastore, Athena and Snowflake external tables. As with Delta, one pipeline per table.

Through an Iceberg REST catalog (Preview). If your lakehouse runs a REST catalog (Apache Polaris / Snowflake Open Catalog, Lakekeeper, Databricks Unity’s Iceberg endpoint, Gravitino, Nessie …), add it to the store connection — its URI, an optional warehouse, and a sign-in (OAuth2 client credentials with an optional token URI and scope, a bearer token, or none) — then name the table in the step:

{
  "target": {
    "kind": "file",
    "sourceId": "8f14e45f-…",
    "path": "lake/orders/",
    "format": "iceberg",
    "mode": "append",
    "catalog": { "namespace": ["analytics"], "table": "orders", "createTable": true }
  }
}

In the editor, pick an Iceberg destination on a store that has a catalog and turn on Publish to the connection’s Iceberg REST catalog; a namespace with several levels is typed with dots (sales.emea). Then:

• The namespace must already exist — it is never created. A missing table is created unless createTable is false.
• The catalog owns the table. path is only the location asked for when the table is created; an existing table is written where the catalog keeps it, which must be inside the connected store — otherwise the step is refused, naming both locations.
• The commit is atomic in the catalog. If another writer commits first, an append is replayed on top of its snapshot; a retried step never commits twice; nothing is deleted.
• A table another engine owns (one this destination did not create) is written with its own schema — the same column names and types, in any order — and its own partitioning. Its schema, partitioning and properties are never changed, and a required column must arrive without nulls.
• Credentials stay on the connection, encrypted; the step only names the table. The catalog URI and token URI must be public https:// addresses.

Rules for both ways:

• An append must match the table’s schema and partitioning; a replace commits a new snapshot (earlier snapshots stay readable) and, on a table this destination created, may change the schema or partitioning.
• Partitions are identity partitions on string, integer, long, date or boolean columns. Tables partitioned any other way (bucket, day …), format versions other than 2, and tables whose default file format is not Parquet are refused by name.
• Column types Iceberg cannot hold here (lists, structs, maps, time, interval) are refused, naming the column; nanosecond timestamps are written at microseconds.
• Column min/max statistics are not written, so readers scan rather than prune on them.
• S3, an S3-compatible store, Azure or GCS — not SFTP; the table records absolute file addresses.

Google Sheets

The Sheets tab writes the step’s rows into one tab of a spreadsheet, through a Google Sheets connection. Share the spreadsheet with that connection’s service account as an Editor.

• Spreadsheet — optional; defaults to the connection’s own.
• Tab — the tab’s name (default Sheet1, up to 31 characters, none of : \ / ? * [ ]). A missing tab is created; a spreadsheet never is, so a mistyped ID fails instead of leaving a stray document.
• Mode — Replace the tab’s rows or Append below. Other tabs are left alone.
• Header — on by default; when appending, it is written only to an empty tab.

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, retried up to retries times after the first attempt (0–8, default 3).
• 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. The dead-letter part is kept with the run for 30 days by default; connect the port to a load to keep failures longer.

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.

In your own database it is a delete and an insert in one transaction, the same on Postgres, MySQL and SQL Server: the rows of your table sharing a merge key with the batch are deleted, then the batch is inserted. Rows sharing a key inside one batch collapse first: when the batch carries _ds_version, the newest wins, so the result never depends on file order; without it, one of the duplicates is kept, so dedupe them upstream.

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, on Postgres, MySQL and SQL Server alike: in a Merge, a row flagged _ds_deleted removes its key from your table and is never inserted, so 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.

The _ds_deleted column lands in your table too, so a table you create yourself needs it; a Merge never writes it true.

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.
• Delta Lake and Apache Iceberg tables are Preview: proven by writing and independently reading them back (DuckDB), not yet against Spark, Trino or Databricks reads, nor against vendor catalogs.
• Iceberg REST catalogs sign in with OAuth2 client credentials or a bearer token. AWS Glue’s and S3 Tables’ SigV4-signed endpoints, vended credentials and remote signing are not supported yet.
• The warehouse COPY paths are statement-pinned, not live-verified against real Snowflake / BigQuery / Redshift accounts yet.
• bulkStaging has no connection-form field on Redshift and BigQuery — 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