TASK 4: Raw To Load Normalization

Summary

Implement the first normalize_load ETL stage for the narrowed Brink v1 slice: one tenant-day raw -> load flow for Sales2.GetOrders only.

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

• resolves one successful upstream extract_raw batch
• reads persisted raw XML from S3 raw/
• normalizes GetOrders XML into the existing GetOrders staging/load contracts
• writes Parquet artifacts to S3 load/
• records load-stage diagnostics, contract inventory, and upstream lineage

This task owns:

• normalize_load child job contract and orchestration
• upstream raw-batch resolution for one tenant-day run
• GetOrders normalization catalog and context construction
• relational decomposition of the GetOrders payload into load contracts
• load artifact persistence and diagnostics
• unit tests and live E2E for the first raw -> load boundary

This task does not implement Redshift COPY, curated SQL, or curated export. Those belong to later tasks.

Dependencies

• TASK 0 - Module 0 decision record
• TASK 2 - extract raw XML
• TASK 3 - batch lifecycle, checkpoints, and failure packaging

Subtasks

• [x] TASK 4.1 - Implement normalize-load orchestration and upstream raw-batch resolution
• [x] TASK 4.2 - Implement GetOrders relational decomposition and normalization catalog
• [x] TASK 4.3 - Implement load artifact persistence and normalize-load diagnostics
• [x] TASK 4.4 - Add unit tests and live E2E validation for normalize_load

Files to Create

1. Normalize-load orchestration contracts and services

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

Required logical responsibilities:

• child normalize-load job entry
• upstream raw-batch resolver
• normalize-load orchestrator
• normalization catalog for the narrowed v1 scope
• normalization context factory
• load artifact writer

Recommended logical services:

• BrinkPosNormalizeLoadJobs
• BrinkPosRawBatchResolver
• BrinkPosNormalizeLoadOrchestrator
• BrinkPosNormalizationCatalog
• BrinkPosNormalizationContextFactory
• BrinkPosLoadArtifactWriter

2. Load-stage result and diagnostics models

Create or finalize the minimum models needed for one normalize_load endpoint execution and one full tenant-day load batch.

Required minimum details:

• company_id
• tenant_id
• business_date
• pipeline_stage = normalize_load
• upstream extract_raw batch reference
• stream_name
• operation_name
• target_contract_group
• emitted contracts with schema versions, row counts, and load/ URIs
• started_at
• completed_at
• endpoint and batch status

3. Normalization contract inventory

Create or finalize the fixed v1 operation-to-contract inventory for Sales2.GetOrders based on the existing staging model under:

• backend/integrations/Kds.Ai.Integrations.BrinkPos/Models/Staging/Sales2/GetOrders

This task should treat that existing staging model as the target contract and verify its completeness against:

• Brink portal semantics
• temp/GetOrders_WithKnownBusinessDate_CallsRealBrinkEndpoint_2024-10-06.xml

Files to Modify

4. Shared normalization seam

Modify or finalize the shared normalization seam so the ETL job starts from persisted raw S3 objects and ETL context, not from in-memory DTO state from extraction.

Required responsibility split:

• shared ETL/Warehouse code owns upstream raw selection, context construction, stage lifecycle, and load/ artifact persistence
• Kds.Ai.Integrations.BrinkPos owns GetOrders parsing, source-shaped mapping, and operation-specific normalization profiles

5. Brink GetOrders normalization profiles and mappers

Modify the Brink normalization path so Sales2.GetOrders emits the full existing staging-model contract set needed by this slice.

This task should not widen v1 scope to any non-GetOrders endpoint.

6. S3 load storage integration

Modify the shared ETL storage path so load artifacts are written to the standard S3 load/ layout from docs/ETL.md, and final URIs are recorded in normalize_load batch diagnostics.

7. Batch lifecycle touch points

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

Required Behavior

Scope rule

This task is strict v1 normalize_load only for:

• Sales2.GetOrders

No kitchen or settings endpoint belongs to this task, even if older Brink groundwork documents describe a broader normalization catalog.

Upstream-batch rule

For one child normalize-load request:

1. if ExtractBatchId is provided, resolve that successful extract_raw batch
2. otherwise resolve the current successful extract_raw batch for the same tenant and business date
3. use that upstream batch as the only normalization source for the full tenant-day load run

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

Input contract rule

The child normalize-load contract should follow this logical shape:

• CompanyId
• TenantId
• required BusinessDate
• optional ExtractBatchId
• Trigger
• ForceReset
• optional RequestedBy

BusinessDate is required here because the effective date was already resolved by extract_raw.

Replay-boundary rule

• normalization starts from persisted raw/ artifacts only
• this stage must not call Brink again
• this stage must not depend on typed DTOs carried across from extract_raw
• replay for normalize_load rebuilds load/ artifacts from persisted upstream raw artifacts

Target-contract rule

The target contract for this task is the existing GetOrders staging model already present in code.

Required emitted contracts for v1 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 derived from the current code-backed staging model and should be treated as the source of truth for this slice.

Relational decomposition rule

• Sales2.GetOrders must not be normalized as one flat header contract only
• nested collections must be decomposed into separate emitted contracts where the staging model already represents them relationally
• child contracts must preserve deterministic parent-child join keys and row ordinals where needed
• this stage remains source-shaped and technical; do not add curated logic or cross-endpoint enrichment here

Normalization boundary rule

• shared ETL/Warehouse code owns stage orchestration and load artifact persistence
• Brink normalization code owns XML parsing, payload traversal, and source-shaped mapping
• the job-level contract depends on OperationName, TargetContractGroup, ParserVersion, and ContractVersion
• the job-level contract must not depend on concrete mapper class names

Metadata envelope rule

Every emitted contract must include the ETL metadata envelope needed by the staging plan, including at least:

• _etl_batch_id
• _etl_source_type
• _etl_operation_name
• _etl_contract_name
• _etl_schema_version
• _etl_ingested_at
• _etl_raw_s3_path
• _etl_row_ordinal

Load artifact rule

• generated load artifacts must be written to S3 load/
• final load/ URIs, schema versions, row counts, and contract names must be recorded in batch details
• if any required contract write fails, the endpoint fails and the full normalize_load batch fails
• already written load/ artifacts may remain for diagnostics and replay

Zero-row rule

• zero rows is not a failure by itself
• successful normalization may emit zero rows for one or more contracts
• the stage is still successful if parsing, mapping, and required persistence complete successfully

Lifecycle rule

• one child normalize_load job executes one tenant-day stage boundary
• the stage uses the batch, checkpoint, is_current, rerun, and upstream-lineage contract from TASK3.md
• fail-fast behavior is acceptable because v1 contains one required upstream operation only

Testing

Unit tests

Unit tests are mandatory for this task.

Add unit tests for:

• upstream raw-batch resolution with explicit ExtractBatchId
• upstream raw-batch resolution through current successful extract_raw batch lookup
• normalization catalog correctness for the narrowed v1 scope
• NormalizationContext construction from persisted raw artifact metadata
• Sales2.GetOrders relational decomposition into the full emitted contract set
• ETL metadata envelope population on emitted rows
• deterministic parent-child key and ordinal behavior
• zero-row successful normalization behavior
• load artifact persistence and diagnostics recording
• failure handling when one required emitted contract write fails

Live E2E tests

This task ends at a real ETL 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 extract_raw batch can be used as the upstream source for normalize_load
• the stage reads real persisted raw XML rather than re-calling Brink
• Sales2.GetOrders produces Parquet artifacts in S3 load/
• emitted artifacts match the expected contract inventory for the selected real raw sample
• batch metadata points to the produced load/ artifacts and upstream raw batch correctly

Recommended live verification shape:

• run one real tenant-day extract_raw batch
• start normalize_load from that raw batch
• assert that expected Parquet artifacts exist in load/
• assert that metadata records contract names, row counts, schema versions, and URIs
• assert that no secrets are written into load diagnostics or object metadata

Acceptance Criteria

• [x] Child normalize-load job entry exists for one tenant-day normalize_load stage
• [x] normalize_load resolves a successful upstream extract_raw batch by explicit id or current-state lookup
• [x] normalize_load starts from persisted raw S3 artifacts only
• [x] Sales2.GetOrders is the only normalization source in this task
• [x] The existing GetOrders staging model is reused as the target load contract
• [x] GetOrders normalization emits the full required relational contract set for the existing staging model
• [x] Every emitted contract includes the ETL metadata envelope and deterministic lineage fields
• [x] Parquet load artifacts are written to S3 load/
• [x] Normalize-load diagnostics capture upstream batch, contract inventory, row counts, schema versions, and load URIs
• [x] Zero-row successful normalization is handled correctly
• [x] Unit tests cover orchestration, decomposition, metadata, and failure behavior
• [x] Live E2E verifies Parquet output from real persisted raw artifacts
• [x] Solution builds without errors

Notes

• Follow TASK0.md as the authoritative scope document when older Brink docs widen normalization scope.
• Keep this task strict: raw -> load only.
• Reuse the existing code-backed staging model for GetOrders; verify completeness rather than redesigning the stage shape.
• Keep normalization source-shaped; curated semantics belong to later tasks.