# 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](./TASK0.md) - Module 0 decision record - [TASK 3](./TASK3.md) - batch lifecycle, checkpoints, and failure packaging - [TASK 5](./TASK5.md) - load to Redshift staging ## Subtasks - [x] [TASK 6.1](./TASK6.1.md) - Implement staging-to-curated orchestration and upstream load-to-staging batch resolution - [x] [TASK 6.2](./TASK6.2.md) - Freeze curated target dependency matrix and v1 field behavior - [x] [TASK 6.3](./TASK6.3.md) - Implement curated SQL catalog, execution, and diagnostics - [x] [TASK 6.4](./TASK6.4.md) - 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`.