# TASK 5: Load To Redshift Staging

## Summary

Implement the first warehouse-facing `load_to_staging` ETL stage for the narrowed Brink v1 slice: one tenant-day `load -> Redshift staging` flow for `Sales2.GetOrders` contracts only.

This task starts from the persisted `normalize_load` replay boundary from `TASK4.md` and the stage lifecycle contract from `TASK3.md`, and turns them into a real `load_to_staging` execution step that:

- resolves one successful upstream `normalize_load` batch
- reads the produced Parquet artifact inventory from S3 `load/`
- maps each emitted contract to one Redshift `staging` table deterministically
- executes Redshift `COPY` for every required table
- records staging-load diagnostics, upstream lineage, and rerun-safe batch state

This task owns:

- `load_to_staging` child job contract and orchestration
- upstream normalize-load batch resolution for one tenant-day run
- the fixed v1 contract-to-table catalog for the narrowed `GetOrders` slice
- Redshift `COPY` command construction and execution through tenant warehouse access
- staging-load diagnostics and stage-completion semantics
- unit tests and live E2E for the first `load -> Redshift staging` boundary

This task does not implement `raw -> load` normalization, `staging -> curated` SQL, or curated export. Those belong to `TASK4.md`, `TASK6.md`, and `TASK7.md`.

## Dependencies

- [TASK 0](./TASK0.md) - Module 0 decision record
- [TASK 3](./TASK3.md) - batch lifecycle, checkpoints, and failure packaging
- [TASK 4](./TASK4.md) - raw to load normalization

## Subtasks

- [x] [TASK 5.1](./TASK5.1.md) - Implement load-to-staging orchestration and upstream normalize-load batch resolution
- [x] [TASK 5.2](./TASK5.2.md) - Implement staging table catalog and Redshift `COPY` command construction
- [x] [TASK 5.3](./TASK5.3.md) - Implement Redshift staging load execution, diagnostics, and rerun semantics
- [x] [TASK 5.4](./TASK5.4.md) - Add unit tests and live E2E validation for `load_to_staging`

## Files to Create

### 1. Load-to-staging orchestration contracts and services

Create or finalize the ETL/Warehouse-facing services that execute one tenant-day `load_to_staging` run.

Required logical responsibilities:

- child load-to-staging job entry
- upstream normalize-load batch resolver
- staging-load orchestrator
- deterministic contract-to-table catalog
- Redshift `COPY` command factory
- staging-load executor

Recommended logical services:

- `BrinkPosLoadToStagingJobs`
- `BrinkPosNormalizeLoadBatchResolver`
- `BrinkPosStagingLoadOrchestrator`
- `BrinkPosStagingTableCatalog`
- `RedshiftCopyCommandFactory`
- `RedshiftStagingLoadExecutor`

### 2. Staging-load result and diagnostics models

Create or finalize the minimum models needed for one `load_to_staging` execution and one full tenant-day staging batch.

Required minimum details:

- `company_id`
- `tenant_id`
- `business_date`
- `pipeline_stage = load_to_staging`
- upstream `normalize_load` batch reference
- `trigger`
- per-table contract and target table inventory
- input `load/` URIs or prefixes
- schema version
- `copySqlTemplateId` or equivalent diagnostic identifier
- `started_at`
- `completed_at`
- table-level and batch-level status

### 3. V1 staging target catalog

Create or finalize the fixed v1 contract-to-table inventory for `Sales2.GetOrders` based on the emitted load contracts from `TASK4.md`.

Required v1 target tables are:

- `staging.brink_sales2_orders`
- `staging.brink_sales2_order_entries`
- `staging.brink_sales2_order_entry_voids`
- `staging.brink_sales2_order_entry_taxes`
- `staging.brink_sales2_order_entry_promotions`
- `staging.brink_sales2_order_entry_discounts`
- `staging.brink_sales2_order_entry_modifiers`
- `staging.brink_sales2_order_entry_modifier_taxes`
- `staging.brink_sales2_order_entry_modifier_promotions`
- `staging.brink_sales2_order_payments`
- `staging.brink_sales2_order_payment_details`
- `staging.brink_sales2_order_payment_additional_data`
- `staging.brink_sales2_order_taxes`
- `staging.brink_sales2_order_discounts`
- `staging.brink_sales2_order_promotions`
- `staging.brink_sales2_order_surcharges`
- `staging.brink_sales2_order_surcharge_taxes`
- `staging.brink_sales2_order_deposits`

This list is narrower than older Brink groundwork docs and should stay aligned with the actual `GetOrders` load-contract inventory from `TASK4.md`.

## Files to Modify

### 4. Warehouse SQL execution seam

Modify or finalize the shared warehouse execution seam so `load_to_staging` can execute Redshift `COPY` statements through tenant-resolved warehouse access.

Required responsibility split:

- shared ETL/Warehouse code owns upstream batch selection, staging orchestration, `COPY` execution, and stage diagnostics
- Brink-specific code owns only the fixed catalog that maps Brink-emitted contract names to staging target tables

This task may reuse or extend existing Redshift connection and SQL-execution services, but it should not invent a parallel warehouse-access path just for Brink.

### 5. Load-artifact inventory consumption

Modify the `normalize_load` output consumption path so `load_to_staging` resolves the upstream produced artifact inventory by contract/table from persisted batch details rather than from in-memory normalization state.

### 6. Batch lifecycle touch points

Use the lifecycle contract from `TASK3.md` so `load_to_staging` batches store current-state, checkpoint, rerun lineage, and upstream `normalize_load` batch references consistently.

## Required Behavior

### Scope rule

This task is strict v1 `load_to_staging` only for the `Sales2.GetOrders` contract set produced by `TASK4.md`.

No curated SQL or non-`GetOrders` Brink endpoint belongs to this task, even if older Brink groundwork documents describe a broader staging-load catalog.

### Upstream-batch rule

For one child `load_to_staging` request:

1. if `NormalizeLoadBatchId` is provided, resolve that successful `normalize_load` batch
2. otherwise resolve the current successful `normalize_load` batch for the same tenant and business date
3. use that upstream batch as the only input inventory for the full tenant-day staging-load run

Failed or non-current upstream normalize-load batches must not be used unless a later replay policy expands explicitly.

### Input contract rule

The child load-to-staging contract should follow this logical shape:

- `CompanyId`
- `TenantId`
- required `BusinessDate`
- optional `NormalizeLoadBatchId`
- `Trigger`
- `ForceReset`
- optional `RequestedBy`

`BusinessDate` is required here because the effective date and produced `load/` artifacts were already resolved upstream.

### Replay-boundary rule

- staging load starts from persisted `load/` artifacts only
- this stage must not re-run normalization or re-call Brink
- this stage must resolve all table inputs from the persisted upstream batch diagnostics and artifact inventory
- replay for `load_to_staging` rebuilds warehouse staging state from persisted `load/` artifacts

For the first POC, the preferred replay entry point for rerun is the successful tenant-day raw lineage that reproduces a fresh `normalize_load` batch and then rebuilds `load_to_staging` from that selected upstream artifact chain.

### Contract-to-table rule

- one emitted load contract must map to exactly one Redshift staging table
- the mapping must be explicit and deterministic
- the job should not rely on ad-hoc string mutation to infer table names if a formal catalog can be provided
- the contract inventory used here must match the narrowed `TASK4.md` `GetOrders` contract set exactly

### `COPY` execution rule

- one `COPY` execution per required staging table per tenant-day batch is the default v1 strategy
- each `COPY` must target the S3 artifacts produced for that contract/table
- `COPY` must load Parquet input through the tenant warehouse connection
- the target table must already exist before execution begins
- the exact auth model for Redshift `COPY` from S3 must be consumed from centralized warehouse/platform configuration rather than hard-coded per Brink table

### Staging replacement rule

- the v1 POC replacement strategy for the staging boundary is delete-and-rebuild by `tenant_id + business_date`
- before loading the new batch into each required staging table, the stage deletes existing rows for the same tenant-day scope using the warehouse-approved tenant discriminator and `business_date`
- after tenant-day deletion completes, the stage loads the replacement rows from the selected upstream `load/` artifacts
- the strategy must be applied consistently for every required staging table in the batch
- the strategy must remain stage-local and must not mutate curated tables
- rerun must create a new `load_to_staging` batch; it must not overwrite prior batch metadata
- zero-row successful rerun is valid and leaves the tenant-day scope empty after the delete-and-rebuild pass

### Success and failure rule

- the stage follows `100% success or nothing`
- every required staging table for the selected upstream batch must load successfully before the batch becomes successful and current
- if any required table load fails, the full `load_to_staging` batch fails
- partial table completion may remain in Redshift for diagnostics during the failed attempt, but the batch is not current and the checkpoint does not advance
- upstream `load/` artifacts remain intact for replay and diagnostics

### Diagnostics rule

`load_to_staging` batch details must preserve enough detail for later `staging_to_curated` work, including at least:

- upstream normalize-load batch id
- per-table `contractName`
- per-table `tableName`
- per-table `schemaVersion`
- input artifact URIs or prefixes
- per-table status and timing
- `copySqlTemplateId` or equivalent execution identifier

Diagnostics must not store secrets, tokens, or raw Redshift auth material.

### Lifecycle rule

- one child `load_to_staging` job executes one tenant-day warehouse load boundary
- the stage uses the batch, checkpoint, `is_current`, rerun, and upstream-lineage contract from `TASK3.md`
- downstream `staging_to_curated` work must resolve current-state from this stage explicitly rather than from newest-row inference

## Testing

### Unit tests

Unit tests are mandatory for this task.

Add unit tests for:

- upstream normalize-load batch resolution with explicit `NormalizeLoadBatchId`
- upstream normalize-load batch resolution through current successful batch lookup
- deterministic contract-to-table catalog correctness for the narrowed v1 scope
- `COPY` command construction for Parquet input and target table selection
- staging delete-and-rebuild behavior for one tenant-day scope
- failure handling when one required table load fails
- diagnostics recording and sanitization
- downstream-readable batch details for `staging_to_curated`

### Live E2E tests

This task ends at a real warehouse boundary, so live E2E is required whenever Aspire-managed secrets and integrations are available.

Add live E2E coverage that verifies at least:

- one successful tenant-day `normalize_load` batch can be used as the upstream source for `load_to_staging`
- the stage reads the persisted `load/` artifact inventory instead of re-running normalization
- Redshift `staging` tables are populated from real Parquet `load/` artifacts
- batch metadata points to the upstream normalize-load batch and the per-table load results correctly
- rerun semantics produce a new successful current batch without deleting batch history

Recommended live verification shape:

- run or reuse one real tenant-day `normalize_load` batch
- start `load_to_staging` from that upstream batch
- assert that required `staging.brink_sales2_*` tables received rows or completed successfully for zero-row inputs
- assert that batch metadata records table names, contract names, schema versions, and input URIs
- assert that no secrets are written into stage diagnostics

## Acceptance Criteria

- [x] Child load-to-staging job entry exists for one tenant-day `load_to_staging` stage
- [x] `load_to_staging` resolves a successful upstream `normalize_load` batch by explicit id or current-state lookup
- [x] `load_to_staging` starts from persisted `load/` S3 artifacts only
- [x] The narrowed `GetOrders` contract inventory maps deterministically to the required Redshift staging tables
- [x] Redshift `COPY` executes for every required staging table in the selected upstream batch
- [x] The v1 POC staging replacement strategy is delete-and-rebuild by `tenant_id + business_date`
- [x] Table-load failure fails the full stage and does not advance checkpoint state
- [x] Successful rerun supersedes only the prior current successful `load_to_staging` batch for the same tenant-day key
- [x] Staging-load diagnostics capture upstream batch, per-table contract inventory, schema versions, statuses, and input URIs
- [x] Unit tests cover orchestration, mapping, `COPY` construction, rerun semantics, and failure behavior
- [x] Live E2E verifies Redshift staging load from real produced Parquet artifacts
- [x] Solution builds without errors

## Notes

- Follow `TASK0.md` as the authoritative scope document when older Brink docs widen staging-load scope.
- Keep this task strict: `load -> Redshift staging` only.
- Reuse existing warehouse Redshift connection/execution seams where possible instead of building Brink-specific infrastructure.
- For the first POC, prefer full tenant-day rebuild semantics over partial merge or row-patch logic.
- Curated SQL and business-facing validation belong to `TASK6.md`.