Skip to main content

Warehouse Syncs API

Scheduled imports from your data warehouse - BigQuery, Snowflake or Redshift - into the same write paths a CSV import uses.

All endpoints on this page are relative to the base URL: https://api-eu1.joryio.com/api - see API Overview.

Overview​

A warehouse sync reads a table or view you prepare and writes each page of rows into Joryio as it arrives. Nothing accumulates in memory, so a sync of ten million rows costs the same memory as one of ten thousand.

Two things a sync is not:

  • It is not free SQL. The query is generated on every run so it can carry the incremental predicate, so you give us a table or view name and a watermark column rather than a SELECT.
  • It is not a separate ingestion path. Rows land through exactly the same code a file import uses, including the consent transition rules - so an unsubscribe in your warehouse is honoured, and a re-subscribe is not, unless you deliberately say otherwise.

Authentication​

Authorization: Bearer YOUR_API_KEY

Reading a sync needs integrations:read; creating, updating, running or deleting one needs integrations:write. Turning on overrideConsent additionally needs compliance:write.

The sync object​

FieldTypeNotes
integrationIduuidThe stored warehouse connection to read through
connectorIdstringbigquery, snowflake or redshift
syncTypestringusers, events or deletions - decides which write path the rows take
sourceTablestringtable, schema.table or db.schema.table. One of this or sourceQuery
sourceQuerystring?A saved read-only SELECT, used instead of a table
watermarkColumnstring?The column that says a row changed. Omit for a full refresh every run
keyColumnstring?A unique, orderable tiebreaker. Recommended - see below
watermarktimestamp?How far the sync has read. Managed by us; clear it with Rewind
columnMappingobjectWarehouse column to target field path
schedulestringmanual, every_5_min, every_15_min, hourly, every_6_hours, daily, weekly, monthly
cadenceobjectRead-only. configuredMs, effectiveMs, throttled - see below
enabledbooleanOff means the scheduler skips it, keeping the schedule for later
overrideConsentbooleanTreat the warehouse as authoritative for consent. Requires compliance:write
lastRunStatsobject?Rows read, created, updated, skipped, failed, duration, and a bounded error sample

Column mapping​

Map each warehouse column to a target field path:

TargetMeaning
email, phone, externalIdIdentity. At least one of email or externalId is required per row
attributes.<key>A profile attribute. The key may not contain a dot
subscriptions.<channel>Channel-global consent - email, sms, whatsapp, push, viber
subscriptions.list.<listId>.<channel>Consent in ONE subscription group
userId, eventName, timestamp, properties.<key>For syncType: events

Deletions​

A deletions sync reads a table of people to erase. It does not delete anything itself - it creates erasure requests on the same compliance path the DSR API uses, so an erasure walks profiles, per-list subscriptions, events, wallet passes and stored files, honours your anonymize-instead-of-delete setting, and leaves the audit trail a regulator would be shown.

Two consequences worth knowing:

  • A row naming someone who was never imported, or who has already been erased, is skipped, not failed. Deletion lists routinely contain both.
  • Someone already being erased is not queued again. The row stays in your table until you remove it, so without that guard every run would queue another erasure for the same person.

Consent moves through a sync under the same rules as a file import:

  • An unsubscribe is always honoured, with no permission and no flag. Syncing opt-outs out of your CRM needs nothing switched on.
  • A sync will not re-subscribe someone who opted out, and will not downgrade a confirmed double opt-in, unless overrideConsent is set.

overrideConsent means more on a sync than on a file. A file is applied once; a sync re-applies its source every cycle, so an override left on reverses every unsubscribe that arrives between runs, for as long as the sync exists.

Endpoints​

List syncs​

GET /warehouse-syncs

Get a sync​

GET /warehouse-syncs/:id

Returns the sync including lastRunStats and the current watermark.

Preview source rows​

POST /warehouse-syncs/preview
{ "integrationId": "0f8e...", "sourceTable": "analytics.public.contacts", "limit": 20 }

A bounded read of the source as it stands - no watermark, no incremental predicate - so you can build a column mapping against the real column names rather than remembered ones. Send exactly one of sourceTable or sourceQuery. Runs inline and is hard-capped at 100 rows.

Writing SQL instead of naming a table​

sourceQuery takes a saved read-only SELECT for sources a view cannot shape. It is checked by the same read-only guard when you save it, not at 3am when it first runs.

The incremental predicate is applied by wrapping your query:

SELECT * FROM (<your query>) AS jry_src WHERE updated_at > :watermark ORDER BY updated_at ASC

So your query must output the watermark column, and the key column if you set one. You do not alias them yourself - name the column in watermarkColumn and make sure your SELECT returns it. If it does not, saving the sync fails with a message listing the columns your source actually returns, rather than the sync failing on its first run at 3am. That contract is why a table or view is the default and this is the escape hatch. One statement only - a second would run outside the subselect.

List runs​

GET /warehouse-syncs/:id/runs?limit=50

Recent runs, newest first. Each carries its counters, the watermark it started from and ended at, a bounded sample of row-level errors, and a run-level error if the run itself failed.

The sync object tells you whether a sync is working now. This tells you when it started failing, which is the question you actually have when the numbers look wrong - a sync that has quietly skipped 3% of its rows for a month looks identical, on the sync object, to one that skipped 3% this morning.

The 50 most recent runs per sync are kept. The error sample is bounded because a broken column mapping fails every row, and a million identical messages help nobody: the counters are the truth about scale, the sample is the truth about cause.

Create a sync​

POST /warehouse-syncs
{
  "integrationId": "0f8e...",
  "connectorId": "snowflake",
  "name": "Contacts sync",
  "syncType": "users",
  "sourceTable": "analytics.public.contacts",
  "watermarkColumn": "updated_at",
  "schedule": "hourly",
  "columnMapping": {
    "EMAIL": "email",
    "CUSTOMER_ID": "externalId",
    "PLAN": "attributes.plan",
    "EMAIL_CONSENT": "subscriptions.email"
  }
}

sourceTable and watermarkColumn must be plain identifiers. Anything else - quotes, spaces, semicolons, comment markers - is rejected with a 400, because those names are interpolated into generated SQL.

Update a sync​

PATCH /warehouse-syncs/:id

Create and update accept only the fields you can set: name, integrationId, syncType, sourceTable, sourceQuery, watermarkColumn, keyColumn, columnMapping, schedule, enabled, overrideConsent. Any other field - id, organizationId, workspaceId, status, watermark or the run counters - is rejected with a 400; those belong to Joryio. integrationId must be a warehouse connection in the same workspace as the sync, otherwise the request returns 404. connectorId is optional and is taken from that connection - if you send it, it must match.

Changing schedule or enabled recomputes when the sync next comes due.

Run now​

POST /warehouse-syncs/:id/run

Returns { "success": true, "queued": true }, or queued: false when the sync is already queued or running. A sync that overlaps itself re-reads the same rows and doubles the warehouse bill for no new data.

Rewind​

POST /warehouse-syncs/:id/rewind

Clears the watermark so the next run re-reads the table from the beginning. This is safe - every write is an idempotent upsert keyed on identity - and it is the fix when a column mapping was wrong: correct the mapping, rewind, run.

Delete​

DELETE /warehouse-syncs/:id

Deletes the configuration. Data already imported is untouched.

Effective cadence​

A run that takes longer than its interval is refused, not queued - a sync never overlaps itself. So a 5-minute schedule on a sync that takes 7 minutes really runs every 7 minutes, and cadence.throttled is how you find that out instead of wondering.

every_5_min is the practical floor for a polled read, and the reason is cost rather than capability: Snowflake bills per second of warehouse uptime with a 60-second minimum on every resume, so a sync this frequent never lets the warehouse sleep. BigQuery bills bytes scanned, and the incremental predicate only avoids a full scan when the table is partitioned or clustered on the watermark column.

Monthly​

monthly keeps the day of month the sync started on. A sync anchored on the 31st runs on 28 February (29 in a leap year) and returns to 31 March - it does not migrate to the 28th and stay there. All schedule arithmetic is UTC, because a sync carries no timezone and local-time arithmetic would silently mean whichever zone the scheduler happens to run in.

How incremental reads work​

With a watermarkColumn set, each run reads rows at or after the stored watermark, ordered by that column. The watermark advances only after a page has been durably written, so an interrupted run re-reads its tail rather than skipping it.

With a keyColumn, the boundary problem disappears rather than being managed. The cursor becomes (watermarkColumn, keyColumn) and each run reads strictly after the last row it read:

WHERE updated_at > :ts OR (updated_at = :ts AND id > :lastId)
ORDER BY updated_at, id

Rows sharing a timestamp are read exactly once - neither lost nor re-read - and no extra count query is needed. Pick something monotonic: an auto-increment id or a ULID. A content hash is stable but randomly ordered, so a row inserted later at the same instant would sort before the cursor and never be read at all.

Without a keyColumn we fall back to a timestamp-only cursor, and the boundary is handled in one of two ways:

  • Fingerprints (default, up to 5,000 boundary rows). We remember which rows sat on the boundary instant and skip exactly those next run. The boundary is re-read each time so they can be recognised, and nothing is missed.
  • Row counts (past 5,000). We remember how many rows sat there and re-read only when that number grows. Cheaper, because the boundary is not re-read, and it has one hole: a row deleted and a row inserted at the same instant leaves the count unchanged, so the new row is never read.

A keyColumn avoids both trades - the boundary rows are excluded in the WHERE clause, so they are neither re-read nor missed. It is the recommended setting for exactly this reason. An exclusive boundary silently loses rows that share the watermark's exact timestamp but had not been read when the run ended. An inclusive one re-reads a few rows instead, which costs nothing: every write is an idempotent upsert, so a row that arrives twice is stored once.