Skip to content
MSO CloudDocumentation

Ingest pipeline

On this page

In short#

Data travels from a vendor API, sheet tab or file into Bronze, is promoted to typed Silver facts and rolled up into Gold; a re-run lands on the same numbers. Failures are classified as permanent, transient or parked.

Rows move bronze to silver to gold; failures and estimates leave the main lineSourcesAPI, upload, sheet tabBronzeraw rowsSilvertyped factsGoldrollupsDashboardsand assistantpromoterspermanent failureDead-letter tablefailed rowsthird-party dataEstimate storethird-party estimateslabelled, never a KPI

Four mechanisms make re-runs converge: content-hash deduplication in Bronze, natural-key upserts in Silver, watermarks that advance only on success, and a transactional outbox between web app and worker (System architecture).

Bronze: what lands first#

Bronze keeps the source bytes, so a replay re-derives everything downstream without calling a vendor. It is append-only: a correction is a later row, not an edit.

Table Holds Deduplication
raw_events Orders, creators, inventory: exact payload sha256 of the key-sorted payload in a unique index; conflicts ignored
import_staging_rows Every other stream: ad spend, payouts, leads, datasets Dedupe key, plus template and version; queued, promoted, rejected or superseded

Silver: typed facts#

Canonical marketplace streams upsert from the sync processor; every other stream goes through a named promoter. Idempotency is structural:

  1. Duplicates inside one batch collapse last-wins before the write, because Postgres refuses to update one conflict key twice in a statement.
  2. A chunked upsert applies ON CONFLICT DO UPDATE on a declared natural key, for example organization, platform, shop and order id. Nullable key columns use NULLS NOT DISTINCT; otherwise a null-keyed row duplicates on every sync.

Marketplace facts update on conflict, so a re-pull of a closed day corrects a late-settling number.

Gold: what dashboards read#

The agg_* rollups are the read surface: organization, SKU and creator by day, shopper by month, campaign, content-intelligence and dataset-period rollups, refreshed nightly and on demand. The brand is in every rollup's unique key; without it a row becomes the sum of all brands, shown under every brand.

Watermarks and re-pulls#

Stream (default window)-30 d-21 d-14 d-7 d-3 dtodayGA4 sessions; TikTok Shop video3 dSearch Console5 dGoogle Ads, TikTok Ads, Shopee Ads7 dMarketplace orders (nightly backfill, 02:00)7 dMeta Ads; TikTok Shop LIVE14 dLinkedIn Page; Brevo events30 da day restated here: inside the 14 d and 30 d windows, a backfill for the restRun typeIncrementalStarts: cursor, or high-water markwith modified-sinceWrites: cursor and high-water markBackfillStarts: a bounded windowWrites: high-water mark only;a historical cursor would corrupt itReplayStarts: Bronze rows already storedWrites: SilverCalls no vendor
Vendors restate closed days, so each stream re-pulls a trailing window on every run. A day restated outside the window reaches Silver only through a backfill.

Each connector stream keeps one watermark row: cursor, high-water mark, last success and backfill times, running row count. Order streams start five minutes before the cursor so a boundary order is never missed; the shoppable-video stream finalizes closed days once a day at 01:00 Vietnam time; a rolling-window third-party pull prunes its stale predecessors after each write.

The durable handoff#

Outbox dispatch: the job is published once, under a stable idWeb appPostgresDispatcherQueueWorker1business and outbox rows, one commit2claim with short lease3revalidate Zod schema4publish, stable job id5mark published6deliver the job once

The web app never publishes to the queue directly, so the state change and the intent to do work commit or roll back together. The dispatcher claims committed rows with FOR UPDATE SKIP LOCKED and publishes outside any transaction; a stable job id makes a crash between publish and mark replay-safe, and the claim predicate reclaims an expired lease.

Platform events use a separate outbox: platform_outbox rows are written with the change, and a worker sweep every minute claims and delivers them (Platform API and events).

Failure classification#

Failure Class What happens
Unprocessable payload, authentication error, decrypt failure, template error, shop-to-brand mismatch, constraint violation Permanent One dead-letter row; retries stop
Vendor rate limit Parked Connector cools down, health shows degraded; no alert
Anything else Transient Retried with backoff

A dead-letter row carries the payload, error code, message and attempt count; repeats per connector, job type and error code bump the count, and the next successful sync resolves that connector's open rows.

Uploads and mapping templates#

  • Canonical streams (orders, creators, inventory, creator costs, targets, products) parse straight into Bronze.
  • Every other stream uses a mapping template, data not code: source kind, target stream and one transform per column from a closed set. Its dedupe key must cover the stream's natural key, and an active template is immutable: an edit writes version N+1 and archives N (Import mapping template).
  • XLSX exports often prepend a pivot sheet, so the decoder picks the largest sheet. Uploads are capped at 8 MiB.
  • Google Sheets is the same contract over another transport, one template per tab; ranges are read concurrently under a hard cap, then merged in configured order.

Brand binding and shop ownership#

Every ingest write resolves its brand through one function that never defaults silently: a single-brand organization pins automatically; a missing, disagreeing or non-viewable brand is refused. A shop maps to exactly one brand: an unregistered shop is claimed by its first writer, and another brand's shop raises a permanent mismatch.

Queues and schedules#

Ingest work is spread over named queues: shared sync, per-marketplace, marketing sync, datasets, webhooks and manual upload. Schedulers are declared in code and upserted by name at boot; an undeclared one is removed, and every pattern is pinned to Vietnam time.

Queues Retries
Sync and manual upload Five, exponential backoff from 30 seconds
Shorter operational queues Two or three

Where it stops#

Third-party estimates bypass Bronze, Silver and Gold and are never reconciled. Uploads above the cap are refused, and a new corpus shape may need operator help. A restatement outside a stream's trailing window is picked up only by a backfill. Connector readiness: Project status.

Decisions#

Decision Why Enforced by
Durable async work is an outbox row in the business row's transaction; the worker publishes it Direct publish either announces a rolled-back row or loses work when the process dies Audit refuses direct publish and after-commit callbacks; lease and job id make replay safe
Permanent failures dead-letter once; transient ones back off; rate limits park the connector Blanket retries bury one bad credential; blanket dead-letters turn blips into manual work One classifier in connector core; dedupe key; refused dead-letter writes metered at zero
Content-hash Bronze, natural-key Silver with NULLS NOT DISTINCT, watermarks on success Vendors restate days and jobs crash; a non-idempotent pipeline inflates every retry Nullable-upsert audit, save-time dedupe check, CI promoters over adversarial fixtures