Skip to content
ML Academy

Week 2 · Bonus — optional extra studio

Bonus studio 3: data-quality audit and one-page EDA memo on the messy flight table

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

Optional bonus. It adds no milestone and does not change core or studio completion. The rule catalogue can seed the Week 2 quality checks of the forecasting project; run it on the feed you will actually model, not on this table.

  • 90 minsession length
  • 5timed blocks
  • 5tasks
  • —status

Session plan90 min

  1. 15′Declare the rules before looking
  2. 30′Run the audit on the raw table and on a control
  3. 25′Size the damage on three decision metrics
  4. 15′Write the one-page memo
  5. 5′Calibrate against the control

Before you start

Prepare before the session (about 2 minutes).

  • The sample database python/artifacts/airline_sample.sqlite (seed 42, synthetic; rebuild with cd python && uv run python -m capstone.airline_sim if missing).
  • Python (uv run --project python python) or the sqlite3 shell. Everything can be done in SQL.
  • Open nothing from Day 6 except the schema. Today the aim is a systematic audit and a memo, not rediscovering the Day 6 story.

Tables:

TableWhat it is
flight_day_rawThe messy 2022-2023 copy of the flight-by-date table, as it would arrive from the source system
flight_dayThe same flights before the mess was added. Real pipelines rarely have this; here it is the control: last month's accepted snapshot, or an upstream source
booking_curveBooking snapshots by flight, departure date and days out (13 snapshots from 90 to 0)
calendarOne row per date

Scope: departures 2023-01-01 to 2023-12-31. The airline schedule is 19 flight numbers operating daily. All data are synthetic.

Blocks5

  1. Block 1: Declare the rules before looking

    15 min

    Do task declare-rules in 15 minutes. Do not run any query on flight_day_raw in this block. You may read the schema only.

  2. Block 2: Run the audit on the raw table and on a control

    30 min

    Do task run-audit.

    1. Minutes 0-5 - set up one SQL file with a common table expression for the 2023 rows of each table and one query per rule that returns (rule, table_name, violations).
    2. Minutes 5-25 - implement and run your rules on flight_day_raw and on the control flight_day. Eight rules are enough; more than twelve will not fit.
    3. Minutes 25-30 - the tolerance question: pick one rule that fires on the control and decide what is wrong, the rule or the data.

  3. Block 3: Size the damage on three decision metrics

    25 min

    Do task size-effects.

    1. Minutes 0-5 - choose the repair for each rule that fired (documented choice, no silent imputation).
    2. Minutes 5-20 - compute the three metrics as-is, after each single repair, and after all repairs, against the control.
    3. Minutes 20-25 - rank the issues by effect on the decision.

  4. Block 4: Write the one-page memo

    15 min

    Do task eda-memo. Write the memo to the person who must decide whether the 2023 raw table can feed route-level planning. The chart or table is part of the 15 minutes.

  5. Block 5: Calibrate against the control

    5 min

    Do task calibration-review.

Tasks5

Sign in to save your work.

  1. Task 1

    writing

    Write a rule catalogue in notes/audit-rules.md before any query touches flight_day_raw.

    For each rule give: an id, a one-line statement of what must be true, the class (key, coverage, domain, cross-field, cross-table, missingness), the severity (blocks the decision, backlog, cosmetic), and a prediction of how many rows will violate it in the raw table and in the control.

    Write at least ten rules. Cover at least four classes. Each rule must be checkable with the columns and tables in the setup. State the tolerance for any rule that compares two computed numbers.

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

  2. Task 2

    code

    Implement your rules for 2023 and run them on flight_day_raw and on flight_day. Save the SQL as sql/bonus03_audit.sql and the output as a single table with one row per rule and two violation counts (raw and control).

    Then take the first rule that fires on the control and decide whether the rule or the data is wrong. If the rule is wrong, give it a justified tolerance and re-run it on both tables.

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

  3. Task 3

    analysis

    A planner wants to use the 2023 raw table for route-level planning. Measure how much each issue changes three decision metrics. For each metric compute the value as-is, after each single repair you chose, and after all repairs, and compare to the control (flight_day) value.

    Metrics:

    1. Network passengers in 2023 (SUM(passengers)).
    2. IST-TZX passengers in March 2023, both the total and the value per flight-day present.
    3. Network revenue in 2023 (SUM(revenue)), and for the intl-long market alone.

    Repairs you may use: one row per key (say which row, when revenue conflicts), capping or setting aside rows that break integrity, treating NULL revenue as missing rather than zero. Do not impute fares. State what you did with the 57 missing flight-days.

    Finish with a ranked table: issue, rows affected, effect on each metric, and whether it blocks the decision.

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

  4. Task 4

    writing

    Write a one-page memo (at most 350 words) for the planner, plus one chart or one table. It must contain:

    • a verdict on whether the 2023 raw table can feed route-level planning as it stands;
    • the single issue that changes the decision, with its size on the decision metric;
    • a ranked backlog of the rest, each with an owner type and a proposed rule;
    • one interpretation limit of your own numbers;
    • the next two actions and what each would change.

    The title of the chart or table must state the finding.

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

  5. Task 5

    review

    Compare your declare-rules predictions with the audit results in at most 150 words:

    • How many predictions of the control were wrong, and which rules?
    • Which rule you wrote turned out to be miscalibrated, and what evidence told you?
    • Which class of rule found the issue that mattered most?
    • What is the first rule you would add next time?

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

Rubric4 criteria

CriterionMeetsExceeds
Rule catalogueAt least ten checkable rules across at least four classes, with severity and a stated tolerance where two computed numbers are compared, written before running them.Predictions are explicit and calibrated, and each rule says which decision it protects.
Audit execution and calibrationRules run on both tables with counts in a single output, and a rule that fires on the control is diagnosed as a rule problem or a data problem with evidence.Every claim is scoped to the rows checked, and the learner states which rules passed here but would fail on another period.
Effect on decisionsThree decision metrics are compared with the control, with single-repair and all-repair values, and issues are ranked by effect, not by row count.The learner shows an error that cancels at one level and not at another, and states the limit of a per-unit repair.
Memo qualityThe memo gives a verdict, names the one blocking issue with its size, ranks the rest and states one real limit.The memo's chart or table title states the finding and the next actions say what each would change.

Required output

Saved in forecasting-project/:

  • notes/audit-rules.md (dated before the audit);
  • sql/bonus03_audit.sql and its output table (raw against control);
  • the effect-sizing table for the three metrics;
  • the one-page memo with its chart or table;
  • the calibration note.

The studio is optional and changes no completion count.

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