# 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

```mermaid
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](../features/ETL/brink-pos-first-integration.md) for the full integration reference.