# noCV engineering task library Content version 5 Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Tests, patches, and runbooks are requested deliverables. They become Outcome Evidence only through a qualified Mission and immutable Evidence IDs. Independent adaptation must be observed under a declared verification policy and cite immutable Evidence IDs. Completing a planning ticket establishes no Ownership Evidence. ## LAKE — Rebuild a trustworthy merchant analytics mart A fictional marketplace reports daily sales to merchants. Refund joins inflate revenue, merchants close books in different time zones, and backfills compete with nightly loads. All source orders and merchants are synthetic. **Field:** Data engineering. **Suggested stack:** SQL, PostgreSQL, TypeScript, Object storage. **Engineer value:** Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. **Company value:** Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. **Delivery agreement:** Ten issues across definition, modeling, and release; deliver SQL, synthetic reconciliation fixtures, and an analyst handoff. ### Setup prerequisites - SQL joins and aggregates - Batch pipelines - Metric definitions ### Agree on the numbers Make metric grain and source assumptions explicit. #### LAKE-101 — Write the daily net-sales contract with worked rows **Task · High priority · Foundational** noCV practice brief v5 · LAKE-101 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Agree on the numbers. Depends on: No preceding ticket. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 100%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. Finance subtracts posted refunds from captured charges; product subtracts pending refunds too. Both dashboards use Net sales and disagree for merchant M-17. Acceptance criteria - Define grain, inclusion states, currencies, and reporting date. - Provide six worked rows covering captures, refunds, pending entries, and voids. - Explicitly exclude tax and shipping from this exercise's net-sales metric. Implementation constraints - Do not resolve ambiguity by silently adopting whichever query exists. Verification - Recalculate worked totals independently from the contract. - Demonstrate why pending refunds and voids do not change posted totals. Deliverables - Versioned metric contract with executable example assertions Rollout and recovery: Review worked examples with the synthetic analyst role before downstream adoption. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### LAKE-102 — Profile source keys before trusting the order feed **Chore · Medium priority · Foundational** noCV practice brief v5 · LAKE-102 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Agree on the numbers. Depends on: No preceding ticket. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 70% · Database engineering 30%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The warehouse assumes order_id is globally unique, but two merchants both supplied order 7001. Daily deduplication keeps only one. Acceptance criteria - Report duplicates at global and merchant-scoped order grain. - Count missing merchant IDs, order IDs, currencies, and update timestamps. - Produce bounded sample references without customer details. Implementation constraints - Profiling reports anomalies and never deletes source rows. Verification - Detect intentionally duplicated merchant/order pairs. - Keep same-ID orders from different merchants distinct and report missing keys. Deliverables - Read-only profiling SQL and annotated output Rollout and recovery: Profile before changing ingestion; retain only minimized aggregate reports. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. ### Build reliable models Handle refunds, dimensions, dates, and incremental updates. #### LAKE-103 — Remove the order-line and refund join fan-out **Bug · High priority · Intermediate** noCV practice brief v5 · LAKE-103 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Build reliable models. Depends on: LAKE-101, LAKE-102. Difficulty: Intermediate. Estimated focused work: 150 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 80% · Database engineering 20%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. An order with three lines and two refunds becomes six joined rows. The daily mart counts each captured amount several times although the ledger is correct. Acceptance criteria - Aggregate sources at declared grains before joining. - Count each capture and posted refund exactly once. - Keep orders without refunds and flag orphan refunds. Implementation constraints - DISTINCT on monetary values is not a fix; equal legitimate amounts remain distinct. Verification - Reconcile a three-line, two-refund order to its source total. - Include equal-value captures and an orphan refund to expose accidental deduplication. Deliverables - Corrected SQL and join-fan-out fixture Rollout and recovery: Compare a synthetic month and publish a new metric version with its correction delta. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### LAKE-104 — Assign sales to the merchant's reporting day **Story · High priority · Intermediate** noCV practice brief v5 · LAKE-104 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Build reliable models. Depends on: LAKE-101. Difficulty: Intermediate. Estimated focused work: 150 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 100%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. A 23:30 Los Angeles sale appears on tomorrow's report because the mart groups UTC dates. Merchants cannot reconcile their register totals. Acceptance criteria - Derive reporting dates from the merchant's configured time zone. - Retain the original UTC instant. - Handle 23-hour and 25-hour days without lost or duplicate source rows. Implementation constraints - An unknown time zone blocks the affected partition instead of defaulting silently. Verification - Assign sales on both sides of local midnight correctly. - Exercise clock-change boundaries and an invalid time zone. Deliverables - Reporting-date model and boundary fixtures Rollout and recovery: Compare date-shifted totals before switching the synthetic merchant dashboard. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### LAKE-105 — Preserve historical merchant plans in revenue breakdowns **Task · Medium priority · Advanced** noCV practice brief v5 · LAKE-105 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Build reliable models. Depends on: LAKE-102, LAKE-104. Difficulty: Advanced. Estimated focused work: 210 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 70% · Database engineering 30%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. M-17 upgrades from Starter to Growth in June. Joining today's merchant record moves all earlier revenue into Growth and rewrites quarterly comparisons. Acceptance criteria - Join sales to the plan effective at the sale instant. - Reject overlapping effective intervals per merchant. - Represent missing history explicitly instead of using today's plan. Implementation constraints - Use inclusive-start, exclusive-end intervals and document their boundary. Verification - Assign sales before and at the upgrade instant correctly. - Detect interval overlaps and gaps without double-counting. Deliverables - Historical dimension model and interval checks Rollout and recovery: Backfill synthetic history and retain the prior dimension version until totals reconcile. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### LAKE-106 — Pick up late refunds in an incremental load **Bug · High priority · Advanced** noCV practice brief v5 · LAKE-106 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Build reliable models. Depends on: LAKE-103, LAKE-104. Difficulty: Advanced. Estimated focused work: 210 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 70% · Database engineering 30%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The nightly job filters on order creation date. A refund posted today against a six-week-old order never reaches the mart. Acceptance criteria - Select changed keys using source change positions or update times. - Recompute affected reporting partitions beyond today's orders. - Advance checkpoints only after selected changes commit. Implementation constraints - Document watermark precision and ties; overlapping reads must be idempotent. Verification - Apply a late refund and reconcile its affected date. - Fail mid-load and replay equal-timestamp updates without loss or duplication. Deliverables - Incremental model and checkpoint recovery tests Rollout and recovery: Compare incremental and full builds on identical synthetic changes before switching schedules. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. ### Release the mart safely Reconcile, backfill, and govern a versioned dataset. #### LAKE-107 — Prevent a backfill from replacing a newer nightly partition **Bug · High priority · Expert** noCV practice brief v5 · LAKE-107 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Release the mart safely. Depends on: LAKE-105, LAKE-106. Difficulty: Expert. Estimated focused work: 300 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 60% · Distributed systems 40%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. A noon backfill finishes after the nightly job. Its older snapshot overwrites May's newer partition and removes refunds received at 18:00. Acceptance criteria - Associate each generation with a source snapshot boundary. - Reject cutover when another generation superseded the intended partition revision. - Resume backfills without publishing incomplete partition sets. Implementation constraints - Document locking or compare-and-swap policy; finish time is not freshness. Verification - Overlap backfill and nightly load; prove the fresher state wins. - Interrupt before publication and verify readers see a complete prior generation. Deliverables - Partition publication protocol and overlap reproduction Rollout and recovery: Publish shadow generations first; retain the previous complete manifest for rollback. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### LAKE-108 — Block publication when revenue fails source reconciliation **Task · High priority · Intermediate** noCV practice brief v5 · LAKE-108 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Release the mart safely. Depends on: LAKE-103, LAKE-106. Difficulty: Intermediate. Estimated focused work: 150 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 60% · Quality engineering 40%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. A transformation passes SQL syntax checks while dropping one currency partition. The dashboard refresh succeeds and hides the revenue gap. Acceptance criteria - Compare counts and exact minor-unit totals by merchant, date, and currency. - Block unexplained differences while preserving the previous generation. - Report bounded discrepancy references and transformation version. Implementation constraints - Percentage tolerance cannot excuse exact integer ledger mismatches. Verification - Publish a fully reconciled fixture. - Drop one EUR partition and assert publication blocks with an actionable report. Deliverables - Reconciliation gate and discrepancy report Rollout and recovery: Observe known fixtures first, then enforce the gate before manifest publication. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### LAKE-109 — Separate merchant exports from the analyst-wide mart **Task · High priority · Advanced** noCV practice brief v5 · LAKE-109 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Release the mart safely. Depends on: LAKE-108. Difficulty: Advanced. Estimated focused work: 180 minutes; setup and prerequisite tickets are additional. Estimated field mix: Security 50% · Data engineering 30% · Privacy engineering 20%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. Removing merchant_id from an export request returns every merchant's sales because the support route passes filters directly to an analyst-wide query. Acceptance criteria - Derive merchant scope from authorized context at the export boundary. - Expose only approved aggregate columns. - Reject unbounded date ranges and cross-merchant access. Implementation constraints - An analyst credential cannot substitute for merchant authorization. Verification - Export permitted dates for one merchant with correct totals. - Omit or replace merchant scope and request raw customer columns; assert denial. Deliverables - Scoped export boundary and authorization regression cases Rollout and recovery: Route one synthetic merchant through reduced credentials first; revoke export permission on scope failures. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### LAKE-110 — Explain a metric release to the analyst on call **Chore · Low priority · Foundational** noCV practice brief v5 · LAKE-110 · Rebuild a trustworthy merchant analytics mart Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Release the mart safely. Depends on: LAKE-107, LAKE-108. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 80% · Site reliability 20%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. After a mart correction, May net sales changes and analysts need to know which rows moved. Build a synthetic before/after comparison and quantify the delta instead of assuming a target percentage. Acceptance criteria - Document metric versions, source cutoff, and affected partitions. - Break the delta into fan-out, late-refund, and reporting-day effects. - Provide reproducible comparison queries and previous-manifest restoration steps. Implementation constraints - Label measured deltas as results from the synthetic fixture, not expected company impact. Verification - Reproduce the note's totals from the synthetic fixture created for this project. - Exercise rollback and confirm the prior version is visible. Deliverables - Analyst release note and verified handoff checklist Rollout and recovery: Ship the note with the generation switch and record any rollback in the incident log. Project prerequisites: SQL joins and aggregates Batch pipelines Metric definitions Engineer value: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases. Company value: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence.