Skip to content
ML Academy

Week 1 · Saturday — build and investigate

Forecasting studio 1: dataset contract and problem brief

Project thread forecasting · linked lessons day 1, day 2, day 3, day 4, day 5

Milestone W1-a of the Weeks 1-6 forecasting thread (point-in-time-safe forecasting of airline demand from booking snapshots): a dataset contract v0 and a problem brief v0 for forecasting bookings_final per departure at the D-14 booking snapshot, so that the decision to close or keep the lowest fare class (L) can be taken 14 days before departure.

  • 90 minsession length
  • 3timed blocks
  • 4tasks
  • —status

Session plan90 min

  1. 15′Recap and plan
  2. 60′Build - grain profile, dataset contract, problem brief
  3. 15′Error analysis and record

Before you start

Prepared before the session (about 5 minutes; if you do it in-session it comes out of the recap block):

  1. Build or verify the synthetic sample database from the repository root: cd python && uv run python -m capstone.airline_sim. It is deterministic (seed 42), takes a few seconds and writes python/artifacts/airline_sample.sqlite. All data are synthetic; carrier ZX and its flights are fictional, and airport codes are only labels.

  2. Create one project folder that you will use unchanged for Weeks 1-6, for example forecasting-project/ at the repository root, with notes/, sql/, src/, outputs/ and an empty decision-log.md. One folder, one dataset version (the seed-42 database), one notebook or script set - no parallel projects.

  3. Have lesson 2 (the framing card) open. Tables you will use:

    TableGrain (to be proven in task 1)Coverage
    flight_dayflight number x departure date2022-01-01 to 2024-12-31
    booking_curveflight number x departure date x snapshot (days_to_departure 90 ... 0)same departures
    calendardatesame period
    flight_day_rawmessy copy of flight_day for 2022-2023 (used on Day 6; not needed today)2022-2023

Run SQL with sqlite3 python/artifacts/airline_sample.sqlite (or Python's sqlite3 via uv run --project python python).

Blocks3

  1. Block 1: Recap and plan

    15 min

    Closed book for the first 5 minutes: write the six decision-first questions from Day 1 and the six framing-card steps from Day 2 (decision, grain, target, cutoff and horizon, availability test, baseline + metric unit + action cost). Spend 5 minutes checking them against lessons 1 and 2 and marking what you missed.

    Use the last 5 minutes to plan: name the three columns you expect to be hardest to classify as "available at the D-14 cutoff" and say why. If you are doing the setup yourself now, it comes out of this block (2-5 minutes).

  2. Block 2: Build - grain profile, dataset contract, problem brief

    60 min

    Work in this order and keep to the timebox; a complete rough version beats a polished half.

    1. Minutes 0-15 - grain profile (task profile-grain). Prove what one row means in flight_day and booking_curve with queries, not by reading column names.
    2. Minutes 15-40 - dataset contract v0 (task dataset-contract). One row per column with an availability verdict at the D-14 cutoff and a reason a reviewer could check.
    3. Minutes 40-60 - problem brief v0 (task problem-brief). One page, decision first.

    Everything you write here is your own work; the database and folder are the only scaffolding.

  3. Block 3: Error analysis and record

    15 min

    Try to break your own contract (task open-questions). Pick one column you marked "available" and write the exact query that would produce its value for departure ZX104 on 2023-06-15 using only data that existed at that departure's D-14 cutoff (2023-06-01). If you cannot write it, downgrade the verdict to "unclear".

    Then record in decision-log.md: date, what you built, the most consequential open question, and what you will check first next week. Two sentences of session review: what was harder than expected, and why.

Tasks4

Sign in to save your work.

  1. Task 1

    code

    Write and run SQL that proves the grain of flight_day and booking_curve. Save it as sql/w01_profile.sql and paste the outputs into notes/profile-w01.md.

    1. Compare COUNT(*) with the number of distinct (date, flight_no) pairs in flight_day.
    2. In booking_curve, count snapshot rows per departure and list the distinct days_to_departure values.
    3. Report the date range, the routes, the markets and the number of flight numbers per route.
    4. Reconcile the two tables: for how many departures does cumulative_bookings at days_to_departure = 0 differ from bookings_final?

    The expected evidence for this task is shown after you save your own output.

  2. Task 2

    writing

    Write notes/dataset-contract-v0.md.

    Table level: grain, primary key, date coverage, refresh assumption (how and when new departures and snapshots arrive), a "synthetic data" statement, and an owner for each table. Owners are simulated roles, such as "RM systems team (simulated)".

    Column level: one row for every column of flight_day and booking_curve, plus any calendar column you might use. Columns: name | meaning | unit/type | when the value comes into existence | available at the D-14 cutoff? (yes / no / unclear) | reason | quality rule (allowed range, nulls, uniqueness).

    The expected evidence for this task is shown after you save your own output.

  3. Task 3

    writing

    Write notes/problem-brief-v0.md (at most 300 words). Cover these points in order:

    1. The decision, its owner and its frequency: at D-14, close or keep fare class L for each departure.
    2. Is ML necessary? Compare a rule, an analysis and a model (Day 1).
    3. Grain, and the target. Say why the target is not passengers or revenue.
    4. Cutoff, horizon, observation window and label window.
    5. Baseline intent. Define a pickup forecast precisely enough to code next week, using only departures that had already flown at the cutoff.
    6. The metric unit: seats of absolute error per departure.
    7. One action cost, labelled as a hypothetical assumption.
    8. One paragraph on what this forecast can and cannot answer. Keep prediction, inference, causal effect and decision apart.

    The expected evidence for this task is shown after you save your own output.

  4. Task 4

    review

    In the error-analysis block, list every "unclear" verdict and every assumption the brief depends on. For each one, write how you would check it and what you would change if the check failed. Rank the list by consequence for the forecast. Carry the top item into decision-log.md as next week's first question.

    The expected evidence for this task is shown after you save your own output.

Rubric4 criteria

CriterionMeetsExceeds
Grain evidenceThe grain of both tables is stated and backed by row-count queries. The D-0 reconciliation was run and reported.Also explains, from the counts, how an unfiltered join of the two tables would multiply rows and silently use post-cutoff snapshots, and proposes the uniqueness check that would catch it.
Availability reasoningEvery column has a verdict and a checkable reason. Outcome columns and post-cutoff snapshots are excluded.Separates "exists in the table" from "existed with this value at the cutoff" for the borderline columns the table does not settle. Gives a concrete verification for each, and explains what a backfilled or overwritten snapshot would do to the verdicts.
Decision-first briefDecision, target, cutoff, horizon, baseline, metric unit and one labelled hypothetical cost are all present and consistent with each other.Argues explicitly whether a model is needed at all compared with a rule, and states what evidence would change the plan. Separates the forecasting question from causal what-if questions.
Honest recordingNotes the synthetic data, labels assumptions as assumptions, and claims no results that were not computed.The decision-log entry is specific enough that someone else could continue the work from it.

Required output

Saved in forecasting-project/:

  • sql/w01_profile.sql and notes/profile-w01.md, the grain evidence with outputs;
  • notes/dataset-contract-v0.md;
  • notes/problem-brief-v0.md;
  • a dated decision-log.md entry with the next unresolved question.

Session review: two sentences on what was harder than expected. This is the Saturday "increment + next question" required by Appendix C (dataset contract and problem brief).

Studios are tracked separately from the 60 core lessons; completing one never completes a lesson.