Skip to content
ML Academy

Week 2 · Saturday — build and investigate

Forecasting studio 2: reconcile the modelling table and draw the evaluation boundary

Project thread forecasting · linked lessons day 6, day 7, day 8, day 9, day 10

Milestone W2-a. Three pieces of work:

  • a corrected, reconciled SQL query that produces one row per departure (target bookings_final plus the D-14 snapshot and the schedule and calendar features allowed by the contract);
  • a reusable set of data-quality checks with saved outputs;
  • a provisional evaluation boundary that protects the final test period until Week 6.
  • 90 minsession length
  • 3timed blocks
  • 5tasks
  • —status

Session plan90 min

  1. 15′Recap and plan
  2. 60′Build - reconcile, check, bound
  3. 15′Error analysis and record

Before you start

Prepared before the session (under 3 minutes):

  • The sample database at python/artifacts/airline_sample.sqlite. Rebuild it if missing with cd python && uv run python -m capstone.airline_sim; it is the same seed-42 version as in Week 1.
  • forecasting-project/ with the dataset contract v0 and brief v1.
  • Lesson 7's join-grain example open for reference.

Nothing is pre-written for you. The queries, checks and boundary note are learner-authored.

Blocks3

  1. Block 1: Recap and plan

    15 min

    Re-read the dataset contract and brief v1 (5 min). Then answer, from memory, the question you carried in decision-log.md. Write down how today's work will test it.

    Write today's plan in three lines:

    • the columns that go into the modelling table, each with its contract verdict;
    • the checks you will automate;
    • the dates you propose for the evaluation boundary.

    Any Week 1 carry-forward minimum is finished here first.

  2. Block 2: Build - reconcile, check, bound

    60 min
    1. Minutes 0-10 - reproduce the fan-out (task reproduce-fanout). See the wrong number before writing the right query.
    2. Minutes 10-30 - the departure-level modelling table (task model-table), reconciled against flight_day.
    3. Minutes 30-50 - automated quality checks (task quality-checks). Make them a reusable script. Sunday runs the same script on a new feed.
    4. Minutes 50-60 - provisional evaluation boundary (task evaluation-boundary). Draw it before any model is fitted (Day 9).

  3. Block 3: Error analysis and record

    15 min

    Do task check-review. For each check, write the real-world defect it would catch, and what that defect would do to the D-14 feature or the target if nothing caught it.

    Then record in decision-log.md:

    • the boundary decision;
    • the reconciliation result;
    • which check you trust least;
    • the next unresolved question.

Tasks5

Sign in to save your work.

  1. Task 1

    code

    Restrict to departures in 2023. Join flight_day to booking_curve on (date, flight_no) with no snapshot filter. For each month, report the row count and SUM(bookings_final) from the joined result, and from flight_day alone. Save the query as sql/w02_fanout.sql.

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

  2. Task 2

    code

    Write sql/w02_model_d14.sql. It returns one row per departure, using only columns your contract allows at the D-14 cutoff plus the target:

    • keys: date, flight_no, route, market;
    • calendar: weekday, month from calendar, holiday;
    • capacity;
    • bk_d14, the cumulative_bookings at days_to_departure = 14;
    • optionally bk_d30, the same at 30 days, for Week 5;
    • the target bookings_final.

    Reconcile it with three checks:

    1. The row count equals flight_day.
    2. SUM(bookings_final) by month equals flight_day.
    3. bk_d14 is never NULL.

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

  3. Task 3

    code

    Implement at least six checks as one script, sql/w02_checks.sql or src/checks.py. Each check returns the number of violating rows. Cover:

    • uniqueness of (date, flight_no) and of (date, flight_no, days_to_departure);
    • every departure has exactly one D-14 snapshot;
    • 13 snapshots per departure;
    • no NULL keys or snapshot values;
    • value ranges (no negative bookings);
    • cumulative_bookings never decreases as days_to_departure falls;
    • bk_d14 <= bookings_final;
    • flight number and route format (upper case, no whitespace).

    Save the output as a table: check | violating rows | threshold | action if violated.

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

  4. Task 4

    writing

    Record a provisional evaluation boundary in decision-log.md. The default is below; change it only with a written reason.

    • Protected final test: departures 2024-07-01 to 2024-12-31. Do not inspect, plot or summarize them until Week 6.
    • Development: departures up to 2024-06-30.
    • Validation window inside development: departures 2024-01-01 to 2024-06-30.
    • Training: departures ending 2023-12-17, so there is a 14-day gap equal to the horizon.

    Explain why the gap is needed. Note where the documented 2024-03-01 competitor-capacity step on IST-ADB, IST-AYT and IST-FRA falls. Add a code guard, for example an assertion that any development data frame has a maximum date of 2024-06-30 or earlier. Record row counts per partition.

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

  5. Task 5

    review

    For each check, write one line: the real-world defect it would catch (for example a re-delivered snapshot, a renamed flight number or a missing feed day), and its effect on bk_d14 or the target if undetected. Name one defect that none of your checks would catch.

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

Rubric4 criteria

CriterionMeetsExceeds
ReconciliationThe modelling table has exactly one row per departure. Row counts and monthly target sums match `flight_day`, and the fan-out is reproduced and explained.Also reconciles the D-0 snapshot with `bookings_final`. Turns the reconciliations into reusable assertions rather than one-off queries.
Quality checksAt least six checks run, return violating-row counts, and have thresholds and actions.The checks are parameterized by table name and dates, so they run unchanged on a new feed. They include a point-in-time check that needs a load timestamp, or the learner explains why it cannot be written yet.
Evaluation boundaryThe protected test, the validation window and the 14-day gap are recorded with a reason and a code guard, before any model is fitted.Explains which questions the validation window cannot answer (summer season, post-break stability) and how Week 5 backtesting will address them.
Point-in-time disciplineOnly contract-approved columns are used. The snapshot filter is in the join.Documents any "unclear" column left out and the evidence that would let it in.

Required output

Saved in forecasting-project/:

  • sql/w02_fanout.sql;
  • sql/w02_model_d14.sql, with the reconciliation output ("expected vs got");
  • the quality-check script and its saved output table;
  • the evaluation-boundary note, with partition counts and the guard;
  • a decision-log entry naming the next unresolved question.

Covers the Week 2 Saturday part of Appendix C: a reconciled join or aggregation, data-quality checks and a provisional evaluation boundary.

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