# 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. ## ADBT — Make a subscription analytics mart reproducible A fictional SaaS analyst reports expansion revenue differently from finance because snapshots, refunds and contract changes are joined at incompatible grains. **Field:** Data engineering. **Suggested stack:** SQL, PostgreSQL, dbt. **Engineer value:** Practice analytical modeling and explainable transformation contracts. **Company value:** Review trustworthy reporting definitions and the cost of correcting historical reports. **Delivery agreement:** Ten scoped tickets across three phases. Build a synthetic local service or select a ticket after recreating its prerequisites; estimates exclude setup. ### Setup prerequisites - Create a synthetic source schema and local transformation project. - Use integer minor units and distinct reporting currencies. ### Define reporting grain Specify source and metric contracts. #### ADBT-101 — Declare the subscription-movement grain before joining invoice lines **Task · Medium priority · Foundational** noCV practice brief v5 · ADBT-101 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Define reporting grain. 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. A report doubles subscription movement when a contract has two invoice lines in the same month. Acceptance criteria - Document one row per subscription, period and currency. - Identify source keys and permitted multiplicity. - Reject duplicate grain keys in a contract check. Implementation constraints - Create a small synthetic multi-line invoice example. Verification - Reconcile a two-line invoice to one movement row. - Insert duplicate source identity and fail the grain check. Deliverables - Grain specification and executable uniqueness check Rollout and recovery: Review the grain contract before enabling downstream joins. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. #### ADBT-102 — Separate recurring contract value from collected cash **Story · Medium priority · Intermediate** noCV practice brief v5 · ADBT-102 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Define reporting grain. Depends on: ADBT-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 late payment makes the recurring-revenue chart dip even though no subscription changed. Acceptance criteria - Define contract value and cash collection as separate measures. - Document refund and unpaid-invoice treatment. - Keep currency amounts separate without implicit conversion. Implementation constraints - Use named business definitions in model metadata. Verification - Delay a payment and keep contracted recurring value unchanged. - Mix currencies and reject an unsupported combined total. Deliverables - Metric definitions and contrasting examples Rollout and recovery: Publish separate metric names; retire ambiguous aliases after consumer review. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. #### ADBT-103 — Reject source schema drift before materializing the mart **Chore · High priority · Foundational** noCV practice brief v5 · ADBT-103 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Define reporting grain. Depends on: ADBT-101. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 70% · Quality 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. A partner renamed subscription_id and the daily transformation published an empty table successfully. Acceptance criteria - Validate required columns and accepted types. - Fail before replacing the active reporting relation. - Report the missing contract field and source version. Implementation constraints - Avoid relying on a row-count check alone. Verification - Transform a valid empty source under its declared contract. - Rename a required column and retain the previous active mart. Deliverables - Source contract gate Rollout and recovery: Run the contract gate before scheduled builds; keep prior output on failure. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. ### Transform consistently Handle temporal joins and incremental updates. #### ADBT-104 — Join customer segments as of the revenue event date **Bug · Medium priority · Advanced** noCV practice brief v5 · ADBT-104 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Transform consistently. Depends on: ADBT-101, ADBT-102. 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. Historical enterprise revenue changes whenever sales updates a customer's current segment. Acceptance criteria - Use valid-time segment intervals for the event date. - Detect overlapping or missing segment intervals explicitly. - Preserve the event's original reporting currency. Implementation constraints - Do not fill missing historical segments with the current value. Verification - Change today's segment and keep prior periods unchanged. - Create overlapping segment periods and fail the temporal join check. Deliverables - As-of join model and interval assertions Rollout and recovery: Build a comparison table; switch readers after historical differences are reviewed. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. #### ADBT-105 — Make incremental movement loads include revised source rows **Story · Medium priority · Advanced** noCV practice brief v5 · ADBT-105 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Transform consistently. Depends on: ADBT-103, ADBT-104. Difficulty: Advanced. Estimated focused work: 240 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 incremental model filters only creation time, so a corrected subscription end date never reaches the mart. Acceptance criteria - Track source revision or updated watermark with tie breaking. - Recompute affected grain keys deterministically. - Commit the output and checkpoint consistently. Implementation constraints - Document how deletions and corrections identify affected periods. Verification - Correct a prior end date and update the affected month. - Retry after an interrupted run and avoid duplicate movement rows. Deliverables - Revision-aware incremental model Rollout and recovery: Shadow full and incremental builds on a synthetic corpus; fall back to full rebuild if they diverge. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. #### ADBT-106 — Represent cancelled subscriptions as movements instead of deleting them **Task · Medium priority · Intermediate** noCV practice brief v5 · ADBT-106 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Transform consistently. Depends on: ADBT-102, ADBT-105. 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. Deleting a cancelled subscription from the source erases the churn event from the report. Acceptance criteria - Retain cancellation effective date and source tombstone lineage. - Emit one cancellation movement per effective revision. - Expose unavailable source history as incomplete reporting. Implementation constraints - Use synthetic deletion events rather than recovering private backups. Verification - Apply a cancellation tombstone and retain its churn movement. - Replay the tombstone and verify no double cancellation. Deliverables - Tombstone handling and movement fixtures Rollout and recovery: Enable tombstone capture before source deletion; halt destructive cleanup if lineage is missing. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. #### ADBT-107 — Detect fan-out before publishing account-level revenue totals **Bug · High priority · Expert** noCV practice brief v5 · ADBT-107 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Transform consistently. Depends on: ADBT-104, ADBT-105, ADBT-106. Difficulty: Expert. Estimated focused work: 300 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 70% · Quality 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. A new feature joins account tags to movement rows and inflates revenue for accounts with multiple tags. Acceptance criteria - Assert row and amount conservation across the join. - Define tag allocation or a nonduplicating existence filter. - Fail publication when conservation fails. Implementation constraints - Keep tag-level attribution distinct from account-level totals. Verification - Add two tags to an account and preserve its revenue total. - Use the naive many-to-many join in a regression and detect inflation. Deliverables - Fan-out guard and corrected account model Rollout and recovery: Canary the model in a separate schema; restore prior view selection on failed conservation. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. ### Publish reliable outputs Validate releases and retain reproducible reports. #### ADBT-108 — Generate a report manifest with exact model and source revisions **Task · Medium priority · Foundational** noCV practice brief v5 · ADBT-108 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Publish reliable outputs. Depends on: ADBT-105, ADBT-107. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 80% · Storage systems 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 analyst sends a CSV to finance and later cannot identify which transformation commit or source cutoff produced it. Acceptance criteria - Include model commit, source checkpoints and generated UTC time. - Hash exported bytes and preserve a report identity. - Exclude credentials and raw customer fields from the manifest. Implementation constraints - Keep the manifest next to synthetic exports. Verification - Regenerate the same named inputs and compare content hashes. - Change a source revision and receive a distinct manifest. Deliverables - Report manifest and reproducibility command Rollout and recovery: Attach manifests to new reports; label earlier exports as lacking lineage. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. #### ADBT-109 — Prove an incremental mart matches a full refresh after corrections **Task · Medium priority · Expert** noCV practice brief v5 · ADBT-109 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Publish reliable outputs. Depends on: ADBT-106, ADBT-107, ADBT-108. Difficulty: Expert. Estimated focused work: 360 minutes; setup and prerequisite tickets are additional. Estimated field mix: Quality engineering 50% · Data engineering 50%. 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 team wants to reduce daily build cost but needs confidence that incremental state does not drift after late corrections. Acceptance criteria - Author a sequence of inserts, revisions and tombstones. - Compare every grain key and measure to a full refresh. - Report extra, missing and changed rows separately. Implementation constraints - Run both paths against the same immutable synthetic inputs. Verification - Replay the complete change sequence with equal final outputs. - Skip one revision intentionally and verify a precise discrepancy report. Deliverables - Differential transformation harness Rollout and recovery: Require a clean differential run before changing schedule; revert to full refresh on mismatch. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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. #### ADBT-110 — Write the analytics incident handoff for an unexplained revenue shift **Chore · Medium priority · Intermediate** noCV practice brief v5 · ADBT-110 · Make a subscription analytics mart reproducible Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Publish reliable outputs. Depends on: ADBT-108, ADBT-109. Difficulty: Intermediate. Estimated focused work: 120 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 60% · Site reliability 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 report moves after a release and on-call staff cannot tell a genuine source correction from a transformation regression. Acceptance criteria - Start investigation from report manifests and affected grain keys. - Separate source, definition and implementation changes. - Include restore-view and corrected-export procedures. Implementation constraints - Use a fabricated incident with no real financial assertions. Verification - Trace one legitimate correction through source lineage. - Trace a fan-out regression and restore the prior reporting view. Deliverables - Analytics incident runbook Rollout and recovery: Validate the runbook with local reports; version it with model publication. Project prerequisites: Create a synthetic source schema and local transformation project. Use integer minor units and distinct reporting currencies. Engineer value: Practice analytical modeling and explainable transformation contracts. Company value: Review trustworthy reporting definitions and the cost of correcting historical reports. 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.