# TASK 5.4: Add Unit Tests And Live E2E Validation For Load-To-Staging

## Summary

Add the verification layer for the first `load -> Redshift staging` ETL boundary. This subtask closes `TASK 5` by proving both locally and against real integrations that `load_to_staging` works for Brink `Sales2.GetOrders` contracts.

## Dependencies

- [TASK 5.1](./TASK5.1.md) - load-to-staging orchestration and upstream normalize-load batch resolution
- [TASK 5.2](./TASK5.2.md) - staging table catalog and Redshift `COPY` command construction
- [TASK 5.3](./TASK5.3.md) - Redshift staging load execution, diagnostics, and rerun semantics

## Files to Create

### 1. Load-to-staging verification suites

Create or finalize the test suites needed for:

- unit coverage of orchestration and mapping rules
- unit coverage of SQL construction, staging execution, and diagnostics
- live E2E coverage of real `normalize_load -> load_to_staging` execution

## Required Behavior

- unit tests must cover the final `load_to_staging` flow assembled from `TASK 5.1`-`TASK 5.3`
- live E2E must verify the real integration path whenever Aspire-managed secrets are available
- the live test should prove that a real persisted `load/` artifact set can be loaded into Redshift `staging` and recorded in batch metadata

## Testing

### Unit tests

Ensure the final unit suite covers:

- upstream normalize-load batch selection
- staging table catalog and `COPY` SQL construction
- staging replacement strategy and rerun semantics
- Redshift execution diagnostics and failure behavior

### Live E2E

Add a live E2E test that verifies at least:

- one real `normalize_load` batch can serve as the upstream source
- `load_to_staging` reads the persisted `load/` artifact inventory instead of re-running normalization
- expected Redshift `staging.brink_sales2_*` tables are loaded
- batch metadata points to the upstream normalize-load batch and per-table staging results

## Acceptance Criteria

- [x] Unit-test coverage exists for the assembled `load_to_staging` flow
- [x] Live E2E exists for real Parquet-to-Redshift staging load
- [x] Live E2E verifies table-load results and metadata linkage
- [x] `TASK 5` is verifiable as a completed ETL stage

## Notes

- This subtask is mandatory because `load_to_staging` is a finished ETL boundary.
- Keep SQL-construction verification separate from actual warehouse execution verification.