TASK 6: Staging To Curated

Summary

Implement the first business-facing staging_to_curated ETL stage for the narrowed Brink v1 slice: one tenant-day staging -> curated flow for curated.pos_orders and curated.pos_order_items only.

This task starts from the persisted load_to_staging replay boundary from TASK5.md, the stage lifecycle contract from TASK3.md, and the final curated target names frozen in TASK0.md, and turns them into a real staging_to_curated execution step that:

• resolves one successful upstream load_to_staging batch
• validates the required Redshift staging tables for the first order-centric curated slice
• executes business-facing SQL transforms in the tenant warehouse
• writes curated.pos_orders and curated.pos_order_items
• records curated-stage diagnostics, upstream lineage, and rerun-safe batch state

This task owns:

• staging_to_curated child job contract and orchestration
• upstream load-to-staging batch resolution for one tenant-day run
• the fixed v1 curated target catalog and dependency matrix for pos_orders and pos_order_items
• curated SQL template selection and execution through tenant warehouse access
• v1 field-behavior decisions where non-GetOrders enrichment remains deferred
• curated-stage diagnostics and stage-completion semantics
• unit tests and live E2E for the first staging -> curated boundary

This task does not implement raw extraction, normalization, Redshift COPY, or curated export to S3. Those belong to TASK2.md, TASK4.md, TASK5.md, and TASK7.md.

Dependencies

• TASK 0 - Module 0 decision record
• TASK 3 - batch lifecycle, checkpoints, and failure packaging
• TASK 5 - load to Redshift staging

Subtasks

• [x] TASK 6.1 - Implement staging-to-curated orchestration and upstream load-to-staging batch resolution
• [x] TASK 6.2 - Freeze curated target dependency matrix and v1 field behavior
• [x] TASK 6.3 - Implement curated SQL catalog, execution, and diagnostics
• [x] TASK 6.4 - Add unit tests and live E2E validation for staging_to_curated

Files to Create

1. Staging-to-curated orchestration contracts and services

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

Required logical responsibilities:

• child staging-to-curated job entry
• upstream load-to-staging batch resolver
• curated-transform orchestrator
• curated target catalog
• curated SQL template catalog
• curated SQL executor

Recommended logical services:

• BrinkPosStagingToCuratedJobs
• BrinkPosLoadToStagingBatchResolver
• BrinkPosCuratedTransformOrchestrator
• BrinkPosCuratedTargetCatalog
• BrinkPosCuratedSqlTemplateCatalog
• BrinkPosCuratedSqlExecutor

2. Curated-stage result and diagnostics models

Create or finalize the minimum models needed for one staging_to_curated execution and one full tenant-day curated batch.

Required minimum details:

• company_id
• tenant_id
• business_date
• pipeline_stage = staging_to_curated
• upstream load_to_staging batch reference
• trigger
• per-target dependency inventory
• SQL template identifier
• affected-row count or equivalent execution summary when available
• started_at
• completed_at
• target-level and batch-level status

3. V1 curated target catalog

Create or finalize the fixed v1 curated target inventory for this narrowed slice.

Required v1 target tables are:

• curated.pos_orders
• curated.pos_order_items

Source of truth for target names and minimum field surface:

• backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0612_create_curated_pos_orders.sql
• backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0611_create_curated_pos_order_items.sql

This task should treat those existing curated tables as the target contract and should not rename the v1 outputs back to older pos_transactions or pos_transaction_items terminology.

Files to Modify

4. Warehouse curated SQL execution seam

Modify or finalize the shared warehouse execution seam so staging_to_curated can execute target-specific SQL statements through tenant-resolved warehouse access.

Required responsibility split:

• shared ETL/Warehouse code owns upstream batch selection, target dependency validation, curated orchestration, SQL execution, and stage diagnostics
• Brink-specific code owns only the fixed curated target catalog, dependency matrix, and SQL-template identities for this slice

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

5. Curated target dependency consumption

Modify the load_to_staging output consumption path so staging_to_curated resolves required staging-table availability from persisted upstream batch details rather than from assumptions about which tables should exist.

6. Batch lifecycle touch points

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

Required Behavior

Scope rule

This task is strict v1 staging_to_curated only for:

• curated.pos_orders
• curated.pos_order_items

Kitchen targets, pos_transactions, pos_transaction_items, and every other curated contract are out of scope for this task even if older Brink groundwork documents describe them.

Upstream-batch rule

For one child staging_to_curated request:

1. if LoadToStagingBatchId is provided, resolve that successful load_to_staging batch
2. otherwise resolve the current successful load_to_staging batch for the same tenant and business date
3. use that upstream batch as the only staging source boundary for the full tenant-day curated run

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

Input contract rule

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

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

BusinessDate is required here because the effective date and loaded staging state were already resolved upstream.

Replay-boundary rule

• curated transforms start from Redshift staging tables only
• this stage must not read S3 raw/ or load/ directly
• this stage must not re-run normalization or Redshift COPY
• replay for staging_to_curated rebuilds curated outputs from persisted upstream staging state and metadata

For the first POC, the preferred replay path is a full tenant-day rebuild from persisted raw/ lineage through normalize_load and load_to_staging, followed by a fresh staging_to_curated run.

Target-contract rule

The target contracts for this task are the existing curated tables already present in warehouse bootstrap SQL.

This task must:

• freeze curated.pos_orders and curated.pos_order_items as the only v1 curated outputs
• define the minimum required staging dependencies for each target
• define the v1 field-behavior rules where source data exists only partially in the narrowed raw scope
• keep target naming aligned with TASK0.md

Dependency-matrix rule

• every curated target must declare its required staging tables explicitly
• dependency validation must happen before target SQL runs
• dependency rules must be based on the narrowed Sales2.GetOrders staging inventory from TASK4.md and TASK5.md
• the stage must not assume settings, tenders, or other deferred sources exist unless they were explicitly included in the narrowed slice

Deferred-enrichment rule

• fields that cannot be populated correctly from Sales2.GetOrders plus the current narrowed upstream staging set must remain nullable in v1
• this task must prefer NULL or explicitly documented fallback behavior over hidden scope expansion to new Brink endpoints
• payment-related fields such as payment_method must be handled with the narrowed available staging data only, or remain nullable when no safe canonical mapping exists
• temporary field behavior must be explicit in the curated target catalog or SQL template notes so later enrichment can replace it safely

SQL execution rule

• one curated target -> one SQL template family is the default v1 strategy
• each target SQL must read only the staging tables required by that curated contract
• business-facing derivations belong here, not in normalization
• the v1 POC curated replacement strategy is delete-and-rebuild by tenant_id + business_date
• before writing replacement rows for one target, the stage deletes existing curated rows for the same tenant-day scope using the warehouse-approved tenant discriminator and business_date
• after tenant-day deletion completes, the stage inserts the replacement result set for the current target
• zero-row successful rerun is valid and leaves the tenant-day scope empty after the delete-and-rebuild pass
• the tenant-day replacement strategy must be applied consistently across both curated targets
• target SQL must execute through tenant warehouse access rather than through ad-hoc direct connections

Success and failure rule

• the stage follows 100% success or nothing
• every required curated target for the selected upstream batch must succeed before the batch becomes successful and current
• if any required target fails, the full staging_to_curated batch fails
• partial curated writes may remain in Redshift for diagnostics during the failed attempt, but the batch is not current and the checkpoint does not advance
• upstream staging state remains intact for replay and diagnostics

Diagnostics rule

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

• upstream load-to-staging batch id
• per-target targetName
• per-target requiredStagingTables
• per-target sqlTemplateId
• per-target status and timing
• per-target affected-row count or equivalent execution summary when available

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

Lifecycle rule

• one child staging_to_curated job executes one tenant-day curated boundary
• the stage uses the batch, checkpoint, is_current, rerun, and upstream-lineage contract from TASK3.md
• downstream publish_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 load-to-staging batch resolution with explicit LoadToStagingBatchId
• upstream load-to-staging batch resolution through current successful batch lookup
• curated target catalog correctness for the narrowed v1 scope
• dependency validation for curated.pos_orders and curated.pos_order_items
• field-behavior rules for deferred enrichment, especially payment-related nullable behavior
• SQL template selection for each curated target
• curated delete-and-rebuild behavior for one tenant-day scope
• failure handling when one required curated target fails
• diagnostics recording and sanitization

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 load_to_staging batch can be used as the upstream source for staging_to_curated
• the stage reads Redshift staging tables and persisted upstream metadata rather than S3 artifacts directly
• curated.pos_orders and curated.pos_order_items are populated from real staging data
• batch metadata points to the upstream load-to-staging batch and the per-target SQL 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 load_to_staging batch
• start staging_to_curated from that upstream batch
• assert that curated.pos_orders and curated.pos_order_items contain rows or complete successfully for zero-row inputs
• assert that batch metadata records target names, dependencies, SQL template identifiers, and execution summaries
• assert that payment-related deferred fields follow the documented v1 nullable/fallback behavior
• assert that no secrets are written into stage diagnostics

Acceptance Criteria

• [x] Child staging-to-curated job entry exists for one tenant-day staging_to_curated stage
• [x] staging_to_curated resolves a successful upstream load_to_staging batch by explicit id or current-state lookup
• [x] staging_to_curated starts from Redshift staging only and does not read raw S3 artifacts directly
• [x] curated.pos_orders and curated.pos_order_items are frozen as the only curated targets in this task
• [x] The dependency matrix for both curated targets is explicit and validated before SQL execution
• [x] Deferred enrichment behavior is explicit and does not widen raw scope silently
• [x] Curated SQL executes for every required target in the selected upstream batch
• [x] The v1 POC curated replacement strategy is delete-and-rebuild by tenant_id + business_date
• [x] Target failure fails the full stage and does not advance checkpoint state
• [x] Successful rerun supersedes only the prior current successful staging_to_curated batch for the same tenant-day key
• [x] Curated-stage diagnostics capture upstream batch, target inventory, dependencies, SQL template identifiers, and execution summaries
• [x] Unit tests cover orchestration, dependency validation, field behavior, SQL selection, rerun semantics, and failure behavior
• [x] Live E2E verifies curated rows in Redshift from real staging data
• [x] Solution builds without errors

Notes

• Follow TASK0.md as the authoritative scope document when older Brink docs widen curated scope or rename the targets.
• Keep this task strict: staging -> curated only.
• Reuse existing warehouse curated SQL 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 export belongs to TASK7.md.