Flow: ETL Ingestion and Warehouse Publication

This flow is mostly non-interactive. Customer data arrives through file upload, API push, API pull, or CDC, then converges into one platform-owned path that preserves the raw input, loads tenant-scoped Redshift objects, and publishes curated data for analytics.

Primary objectives:

• Accept multiple ingestion modes without creating separate downstream architectures.
• Preserve one raw copy of every payload for replay, debugging, and audit.
• Load tenant-specific Redshift staging, curated, and serving objects.
• Publish curated Parquet to S3 for the cross-tenant Athena query path.
• Keep tenant routing, checkpoints, retries, and batch state owned by the platform.

Sub-diagram

flowchart TD
  SRC["Customer sources<br/>File upload • API push • API pull • CDC"] --> API["Ingestion API / Adapter trigger"]
  SRC --> WORK

  API --> META["Operational metadata<br/>Batches • checkpoints • tenant mapping"]
  API --> RAW["S3 raw / failed"]

  META --> WORK["ETL Worker"]
  RAW --> WORK

  WORK --> LOAD["S3 load-ready files"]
  WORK --> RDS["Tenant Redshift DB<br/>staging -> curated -> serving"]
  WORK --> META

  LOAD --> RDS
  RDS --> CUR["S3 curated Parquet"]
  CUR --> ATH["Athena cross-tenant query path"]
  RDS --> SERV["Single-tenant serving views"]

  classDef store fill:#f6f8fa,stroke:#9aa4b2,color:#111;
  class META,RAW,LOAD,RDS,CUR,SERV,ATH store;

Outputs (what the rest of the platform relies on)

• Raw ingestion storage
• Original files, pushed payloads, API responses, and CDC batches under tenant-scoped S3 prefixes
• Failure artifacts that support replay and investigation without losing source input
• Load-ready artifacts
• Validated and normalized files that Redshift can load directly
• Tenant warehouse objects
• staging tables close to source shape
• curated tables with cleanup, typing, deduplication, and business mapping
• serving views as the stable single-store query surface
• Cross-tenant analytics data
• Curated Parquet exported from Redshift to S3 for Athena queries across authorized tenants
• Operational metadata
• Batch status, row counts, checkpoints, tenant-to-database routing, freshness, and error details

Notes / guardrails

• All ingestion modes must converge into the same downstream ETL path after intake.
• A batch is successful only after raw capture, load, transform, and curated export complete successfully.
• Checkpoints for API pull and CDC advance only after the batch succeeds end to end.
• Single-store reads use tenant-resolved Redshift serving views; multi-store reads use Athena over S3 curated/ data.
• The platform, not the agent, owns tenant routing, tenant scope, and data correctness.

Current implemented state

Brink POS is the first active source adapter using the ETL source adapter framework. The pipeline runs four implemented stages (extract_raw, normalize_load, load_to_staging, staging_to_curated) across five active Brink extract endpoints, populating seven curated tables in tenant Redshift databases.

See Brink POS First Integration for the full integration reference.