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 - Module 0 decision record
• TASK 3 - batch lifecycle, checkpoints, and failure packaging
• TASK 4 - raw to load normalization

Subtasks

• [x] TASK 5.1 - Implement load-to-staging orchestration and upstream normalize-load batch resolution
• [x] TASK 5.2 - Implement staging table catalog and Redshift COPY command construction
• [x] TASK 5.3 - Implement Redshift staging load execution, diagnostics, and rerun semantics
• [x] TASK 5.4 - 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.