{ "policy": { "version": 5, "patterns": { "version": 1, "method": "CURATED_PRACTICE_TOPIC", "notice": "Pattern topics identify design choices to practice. Read the ticket's acceptance criteria and justify the simplest suitable approach. Tags are not capability or ownership evidence; an untagged ticket has no curated pattern topic assigned." }, "fieldMix": { "version": 1, "method": "CURATED_ESTIMATE", "notice": "Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence." }, "contentStatus": "PRACTICE_BRIEF", "assessmentStatus": "NOT_QUALIFIED", "evidenceUse": "NONE", "aiPolicy": "AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior.", "notice": "Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included.", "outcomeEvidence": "Tests, patches, and runbooks are requested deliverables. They become Outcome Evidence only through a qualified Mission and immutable Evidence IDs.", "ownershipEvidence": "Independent adaptation must be observed under a declared verification policy and cite immutable Evidence IDs. Completing a planning ticket establishes no Ownership Evidence." }, "patternTopics": [ { "id": "factory-method", "label": "Factory Method", "group": "Creational" }, { "id": "abstract-factory", "label": "Abstract Factory", "group": "Creational" }, { "id": "builder", "label": "Builder", "group": "Creational" }, { "id": "prototype", "label": "Prototype", "group": "Creational" }, { "id": "singleton", "label": "Singleton", "group": "Creational" }, { "id": "adapter", "label": "Adapter", "group": "Structural" }, { "id": "bridge", "label": "Bridge", "group": "Structural" }, { "id": "composite", "label": "Composite", "group": "Structural" }, { "id": "decorator", "label": "Decorator", "group": "Structural" }, { "id": "facade", "label": "Facade", "group": "Structural" }, { "id": "flyweight", "label": "Flyweight", "group": "Structural" }, { "id": "proxy", "label": "Proxy", "group": "Structural" }, { "id": "chain-of-responsibility", "label": "Chain of Responsibility", "group": "Behavioral" }, { "id": "command", "label": "Command", "group": "Behavioral" }, { "id": "interpreter", "label": "Interpreter", "group": "Behavioral" }, { "id": "iterator", "label": "Iterator", "group": "Behavioral" }, { "id": "mediator", "label": "Mediator", "group": "Behavioral" }, { "id": "memento", "label": "Memento", "group": "Behavioral" }, { "id": "observer", "label": "Observer", "group": "Behavioral" }, { "id": "state", "label": "State", "group": "Behavioral" }, { "id": "strategy", "label": "Strategy", "group": "Behavioral" }, { "id": "template-method", "label": "Template Method", "group": "Behavioral" }, { "id": "visitor", "label": "Visitor", "group": "Behavioral" }, { "id": "ports-and-adapters", "label": "Ports and Adapters", "group": "Architectural" }, { "id": "cqrs", "label": "CQRS", "group": "Architectural" }, { "id": "strangler-fig", "label": "Strangler Fig", "group": "Architectural" }, { "id": "saga", "label": "Saga", "group": "Distributed and reliability" }, { "id": "transactional-outbox", "label": "Transactional Outbox", "group": "Distributed and reliability" }, { "id": "circuit-breaker", "label": "Circuit Breaker", "group": "Distributed and reliability" }, { "id": "bulkhead", "label": "Bulkhead", "group": "Distributed and reliability" } ], "projects": [ { "id": "b164206a-8a79-461f-8c71-15e635e0b531", "key": "LAKE", "title": "Rebuild a trustworthy merchant analytics mart", "field": "Data engineering", "summary": "Replace a fragile daily sales query with versioned, reconcilable warehouse metrics.", "context": "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.", "stack": [ "SQL", "PostgreSQL", "TypeScript", "Object storage" ], "prerequisites": [ "SQL joins and aggregates", "Batch pipelines", "Metric definitions" ], "developerValue": "Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases.", "companyValue": "Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product.", "delivery": "Ten issues across definition, modeling, and release; deliver SQL, synthetic reconciliation fixtures, and an analyst handoff.", "phases": [ { "id": "define", "title": "Agree on the numbers", "goal": "Make metric grain and source assumptions explicit." }, { "id": "model", "title": "Build reliable models", "goal": "Handle refunds, dimensions, dates, and incremental updates." }, { "id": "release", "title": "Release the mart safely", "goal": "Reconcile, backfill, and govern a versioned dataset." } ], "tickets": [ { "id": "860da3db-7a5a-49f1-91ad-71abdb6f46c5", "key": "LAKE-101", "title": "Write the daily net-sales contract with worked rows", "type": "TASK", "priority": "HIGH", "difficulty": "FOUNDATIONAL", "estimateMinutes": 90, "phaseId": "define", "dependsOn": [], "scenario": "Finance subtracts posted refunds from captured charges; product subtracts pending refunds too. Both dashboards use Net sales and disagree for merchant M-17.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Review worked examples with the synthetic analyst role before downstream adoption.", "skills": [ "Metric definitions", "Data contracts", "Analytical reasoning" ], "fieldMix": [ { "field": "Data engineering", "percentage": 100 } ], "patterns": [] }, { "id": "8cc8501a-e1ed-438e-a4ac-b713d302c608", "key": "LAKE-102", "title": "Profile source keys before trusting the order feed", "type": "CHORE", "priority": "MEDIUM", "difficulty": "FOUNDATIONAL", "estimateMinutes": 90, "phaseId": "define", "dependsOn": [], "scenario": "The warehouse assumes order_id is globally unique, but two merchants both supplied order 7001. Daily deduplication keeps only one.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Profile before changing ingestion; retain only minimized aggregate reports.", "skills": [ "Data profiling", "Composite keys", "SQL" ], "fieldMix": [ { "field": "Data engineering", "percentage": 70 }, { "field": "Database engineering", "percentage": 30 } ], "patterns": [] }, { "id": "f9941471-d54b-43c5-b00b-e447d95c0ddc", "key": "LAKE-103", "title": "Remove the order-line and refund join fan-out", "type": "BUG", "priority": "HIGH", "difficulty": "INTERMEDIATE", "estimateMinutes": 150, "phaseId": "model", "dependsOn": [ "LAKE-101", "LAKE-102" ], "scenario": "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.", "acceptanceCriteria": [ "Aggregate sources at declared grains before joining.", "Count each capture and posted refund exactly once.", "Keep orders without refunds and flag orphan refunds." ], "implementationNotes": [ "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": "Compare a synthetic month and publish a new metric version with its correction delta.", "skills": [ "Join cardinality", "Aggregation", "Reconciliation" ], "fieldMix": [ { "field": "Data engineering", "percentage": 80 }, { "field": "Database engineering", "percentage": 20 } ], "patterns": [] }, { "id": "769f9fe4-d4f5-4db0-80ac-cb6991f18f4f", "key": "LAKE-104", "title": "Assign sales to the merchant's reporting day", "type": "STORY", "priority": "HIGH", "difficulty": "INTERMEDIATE", "estimateMinutes": 150, "phaseId": "model", "dependsOn": [ "LAKE-101" ], "scenario": "A 23:30 Los Angeles sale appears on tomorrow's report because the mart groups UTC dates. Merchants cannot reconcile their register totals.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Compare date-shifted totals before switching the synthetic merchant dashboard.", "skills": [ "Time-zone modeling", "Date dimensions", "Data quality" ], "fieldMix": [ { "field": "Data engineering", "percentage": 100 } ], "patterns": [] }, { "id": "c1ae672f-dad4-488c-acd0-9c3a9b6ddda7", "key": "LAKE-105", "title": "Preserve historical merchant plans in revenue breakdowns", "type": "TASK", "priority": "MEDIUM", "difficulty": "ADVANCED", "estimateMinutes": 210, "phaseId": "model", "dependsOn": [ "LAKE-102", "LAKE-104" ], "scenario": "M-17 upgrades from Starter to Growth in June. Joining today's merchant record moves all earlier revenue into Growth and rewrites quarterly comparisons.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Backfill synthetic history and retain the prior dimension version until totals reconcile.", "skills": [ "Slowly changing dimensions", "Temporal joins", "Integrity constraints" ], "fieldMix": [ { "field": "Data engineering", "percentage": 70 }, { "field": "Database engineering", "percentage": 30 } ], "patterns": [] }, { "id": "48420dcb-3f18-49bf-9d14-cda6e9dca4e7", "key": "LAKE-106", "title": "Pick up late refunds in an incremental load", "type": "BUG", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 210, "phaseId": "model", "dependsOn": [ "LAKE-103", "LAKE-104" ], "scenario": "The nightly job filters on order creation date. A refund posted today against a six-week-old order never reaches the mart.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Compare incremental and full builds on identical synthetic changes before switching schedules.", "skills": [ "Incremental processing", "Watermarks", "Idempotent loads" ], "fieldMix": [ { "field": "Data engineering", "percentage": 70 }, { "field": "Database engineering", "percentage": 30 } ], "patterns": [] }, { "id": "f1c26437-3860-4bd5-a65f-997651d8af2b", "key": "LAKE-107", "title": "Prevent a backfill from replacing a newer nightly partition", "type": "BUG", "priority": "HIGH", "difficulty": "EXPERT", "estimateMinutes": 300, "phaseId": "release", "dependsOn": [ "LAKE-105", "LAKE-106" ], "scenario": "A noon backfill finishes after the nightly job. Its older snapshot overwrites May's newer partition and removes refunds received at 18:00.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Publish shadow generations first; retain the previous complete manifest for rollback.", "skills": [ "Snapshot isolation", "Batch concurrency", "Atomic publication", "Recovery" ], "fieldMix": [ { "field": "Data engineering", "percentage": 60 }, { "field": "Distributed systems", "percentage": 40 } ], "patterns": [] }, { "id": "e50f9057-920e-4a92-808a-dc112da2cefd", "key": "LAKE-108", "title": "Block publication when revenue fails source reconciliation", "type": "TASK", "priority": "HIGH", "difficulty": "INTERMEDIATE", "estimateMinutes": 150, "phaseId": "release", "dependsOn": [ "LAKE-103", "LAKE-106" ], "scenario": "A transformation passes SQL syntax checks while dropping one currency partition. The dashboard refresh succeeds and hides the revenue gap.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Observe known fixtures first, then enforce the gate before manifest publication.", "skills": [ "Data quality gates", "Financial reconciliation", "Release controls" ], "fieldMix": [ { "field": "Data engineering", "percentage": 60 }, { "field": "Quality engineering", "percentage": 40 } ], "patterns": [] }, { "id": "2c5a8ca5-e926-47d7-b0b5-5f7b3650ac9f", "key": "LAKE-109", "title": "Separate merchant exports from the analyst-wide mart", "type": "TASK", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 180, "phaseId": "release", "dependsOn": [ "LAKE-108" ], "scenario": "Removing merchant_id from an export request returns every merchant's sales because the support route passes filters directly to an analyst-wide query.", "acceptanceCriteria": [ "Derive merchant scope from authorized context at the export boundary.", "Expose only approved aggregate columns.", "Reject unbounded date ranges and cross-merchant access." ], "implementationNotes": [ "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": "Route one synthetic merchant through reduced credentials first; revoke export permission on scope failures.", "skills": [ "Data access control", "Tenant boundaries", "Column minimization" ], "fieldMix": [ { "field": "Security", "percentage": 50 }, { "field": "Data engineering", "percentage": 30 }, { "field": "Privacy engineering", "percentage": 20 } ], "patterns": [] }, { "id": "8e1240e6-a04e-4a42-bdb4-9cb1dd597f0d", "key": "LAKE-110", "title": "Explain a metric release to the analyst on call", "type": "CHORE", "priority": "LOW", "difficulty": "FOUNDATIONAL", "estimateMinutes": 90, "phaseId": "release", "dependsOn": [ "LAKE-107", "LAKE-108" ], "scenario": "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.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Ship the note with the generation switch and record any rollback in the incident log.", "skills": [ "Technical communication", "Data explainability", "Operational handoff" ], "fieldMix": [ { "field": "Data engineering", "percentage": 80 }, { "field": "Site reliability", "percentage": 20 } ], "patterns": [] } ] } ] }