Reproducible extraction

What you will be able to do

Turn your queries into an extraction that anybody can repeat. A Python script runs saved queries with parameters, writes CSV files, and records exactly what it selected from which snapshot.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions. You write the two query files and the three versions of scripts/extract.py in notebook cells, run the script, and read its manifest. Its first cell downloads the practice data and builds the database, so it works without the local project from Module 1.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• Half-open date ranges, from Time and ranking: Half-open ranges.
• One latest row per ticket, from Time and ranking: the latest outcome of each ticket.
• Read a text file with pathlib, from Reading and writing files: Paths with pathlib in Python for Practical AI Engineering.
• Write a CSV file with the csv module, from Reading and writing files: CSV and JSON in the Python course.
• Command-line options with argparse, from Command-line interface: argparse in the Python course.

What you need on your computer (only for the local route; the notebook and the boxes on this page need none of it)

• The folder ~/projects/ticket-data with larkfield.db and q.py. You made it in Set up a practice database: Build the database.
• If the folder or the database is missing or changed, follow that section again, or reset the database with python3 get_data.py --reset. The extraction only reads the database, as q.py does.
• The folder queries/, as in Run a saved query. This lesson adds extract_tickets.sql, extract_outcomes.sql, 35- and 36-.
• A folder scripts/ for extract.py. If it is not there yet, make it in the project folder with mkdir scripts. If your script breaks, copy the finished one from the course’s C04/pipeline folder.
• A terminal open in that folder. The script uses the Python standard library only: no virtual environment yet. This lesson writes python3, as on macOS and Linux. On Windows, type python instead, as explained in Which Python command to type.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4). The Windows commands are not tested. The notebook was tested in Jupyter.

A file that nobody can explain

Priya receives a CSV file of tickets from a colleague. It has 1,416 rows. She has questions before she trains anything:

• Which export is it from? The help desk exports again every month.
• Which tickets were selected? Why 1,416 and not 1,417?
• Which query made it? Has somebody changed the query since?
• When was it made?

The file cannot answer them. So Mei never sends a data file alone. She sends it with a manifest: a small JSON file that records how the data was made. Her manifest records five facts:

Fact	Example	Why
Extraction time	2026-10-07T03:08:46+00:00	When the script ran, in UTC.
Source version	The snapshot table: export of 2026-07-01 06:00:00, version 1.0	Which data went in. A snapshot is a fixed copy of the data at one time; it does not change after the export.
Selection criteria	created_from = 2026-01-01, created_before = 2026-07-01	Which rows were chosen, and the rule.
Query fingerprint	A SHA-256 of each query file	Which exact query ran.
Row counts	1,416 ticket rows; 1 row not selected	What came out, and what was left out.

A SHA-256 is a 64-character fingerprint of a text or a file. The same text always gives the same fingerprint. A change of one character, even a space, gives a completely different one. So two manifests with the same query SHA-256 used the same query.

Parameters: values that are not part of the query

The selection dates change from one extraction to the next. The query should not. So Mei writes the query with named parameters: :created_from and :created_before are placeholders, and Python gives their values separately.

sql = "SELECT COUNT(*) FROM tickets WHERE team = :team"
con.execute(sql, {"team": "returns"})

sqlite3 sends the query text and the values to SQLite separately. SQLite reads the text as SQL and the values as values only. A value can never become part of the SQL. This also keeps the query text the same for every selection, so its SHA-256 stays the same.

The other way is to build the SQL text with an f-string: f"... WHERE team = '{team}'". It works for returns. The failure case below shows what happens with other values. Never put a value into SQL text with an f-string.

In PostgreSQL

With the psycopg library, named parameters are written %(team)s instead of :team, and positional ones %s instead of ?. The rule is the same: pass the values separately. Not run in this course.

Preview the selection in SQL

Before Mei writes the script, she checks what the selection will contain. She puts the two dates in place of the parameters, only for this test:

Run SQL in your browser

Try it: preview the selection

A comparison is 1 when true and 0 when false, so SUM counts the true rows. You should see 1,416 selected, 1 not selected, and 1,417 in all. Add a query below it, SELECT ticket_id, created_at FROM tickets WHERE created_at >= '2026-07-01';, and run again: the row not selected is T-10147, the ticket from 2036.

SQL query (you can change it)SELECT SUM(created_at >= '2026-01-01' AND created_at < '2026-07-01') AS selected, SUM(NOT (created_at >= '2026-01-01' AND created_at < '2026-07-01')) AS not_selected, COUNT(*) AS all_rows FROM tickets;

The extraction keeps the 14 repeated ticket rows. Removing them is a cleaning decision, and Module 4 makes it and counts it. An extraction only selects: it does not change values.

The two query files

Mei saves each query in its own file, so that the script can compute its SHA-256 and so that a reviewer can read it.

queries/extract_tickets.sql

-- One row per ticket row in the export, with the customer's attributes.
-- LEFT JOIN keeps tickets with no customer (guests) and tickets whose
-- customer is no longer in the customer table. Duplicates are kept here:
-- the cleaning step removes them and counts them.
SELECT
    t.ticket_id,
    t.customer_id,
    t.created_at,
    t.channel,
    t.team,
    t.priority,
    t.order_value,
    t.word_count,
    t.first_reply_minutes,
    t.priority_now,
    t.closed_at,
    c.segment,
    c.region,
    c.joined_on
FROM tickets AS t
LEFT JOIN customers AS c
    ON c.customer_id = t.customer_id
WHERE t.created_at >= :created_from
  AND t.created_at <  :created_before
ORDER BY t.ticket_id;

queries/extract_outcomes.sql

-- Every outcome event of the tickets in the selection. Events for tickets
-- that are not in the ticket table are left out by the join.
SELECT
    o.outcome_id,
    o.ticket_id,
    o.recorded_at,
    o.status,
    o.resolution_code,
    o.csat
FROM outcomes AS o
WHERE o.ticket_id IN (
    SELECT t.ticket_id
    FROM tickets AS t
    WHERE t.created_at >= :created_from
      AND t.created_at <  :created_before
)
ORDER BY o.outcome_id;

Three choices in these files matter:

• The same rule in both files. An outcome event is selected when its ticket is selected. So the two files always describe the same tickets.
• ORDER BY. Without it, the database can return rows in any order, and the CSV file can differ between two runs with the same data.
• The extraction keeps columns such as priority_now. They are leakage, information from after the ticket’s creation. The extraction is a faithful copy; Module 5 decides which columns a model may use.

The outcomes query uses IN (subquery), not a join. IN only tests if the ticket is in the list, so a ticket that is in tickets twice cannot double its events.

Run SQL in your browser

Your turn: how many events will the extraction keep?

The query counts all 1,874 events. Add the selection rule of extract_outcomes.sql, with the two dates in place of the parameters. Predict first: 4 events belong to tickets that are not in tickets, and the 2036 ticket has 1 event.

SQL query (you can change it)-- Count the outcome events whose ticket is in the selection -- (created from 2026-01-01, before 2026-07-01). SELECT COUNT(*) AS events FROM outcomes;

Show the example answer

SELECT COUNT(*) AS events
FROM outcomes
WHERE ticket_id IN (
    SELECT ticket_id
    FROM tickets
    WHERE created_at >= '2026-01-01'
      AND created_at <  '2026-07-01'
);

The result is 1,869: 1,874 − 4 events of the missing tickets T-9990 to T-9993 − 1 event of T-10147.

Guided practice: build extract.py

Build the script in three versions. Run each version before you add more.

1. Make the two files queries/extract_tickets.sql and queries/extract_outcomes.sql. Copy the text above into them.

2. Make the file scripts/extract.py with this first version. It runs one query with parameters and prints the row count.

   scripts/extract.py (version 1)

   """Extract tickets and outcomes from larkfield.db into CSV files, with a manifest."""
   
   import sqlite3
   from pathlib import Path
   
   
   def main():
       params = {"created_from": "2026-01-01", "created_before": "2026-07-01"}
       sql = Path("queries/extract_tickets.sql").read_text(encoding="utf-8")
   
       con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)
       rows = con.execute(sql, params).fetchall()
       con.close()
   
       print(f"{len(rows)} ticket rows")
       print(rows[0])
   
   
   if __name__ == "__main__":
       main()

   The address file:larkfield.db?mode=ro with uri=True opens the database read-only, as q.py does. The keys of params are the parameter names without the colon.

3. Run it from the project folder. The paths in the script start from the folder where you run it.

   python3 scripts/extract.py

   Output

   1416 ticket rows
   ('T-10001', 'C-0228', '2026-01-05 07:30:24', 'email', 'returns', 2, 47.25, 134, 9, 2, '2026-01-06 17:58:45', 'home', 'north', '2025-10-11')

   If you see sqlite3.ProgrammingError: You did not supply a value for binding parameter :created_before., a key in params is missing or has a different spelling.

4. Replace the whole file with version 2. The function run runs one query file and writes a CSV file. It returns the facts for the manifest: the query file, its SHA-256, the output file and the row count.

   scripts/extract.py (version 2)

   """Extract tickets and outcomes from larkfield.db into CSV files, with a manifest."""
   
   import csv
   import hashlib
   import sqlite3
   from pathlib import Path
   
   OUT = Path("data/extract")
   
   
   def run(con, query_file, params, out_file):
       sql = Path(query_file).read_text(encoding="utf-8")
       cursor = con.execute(sql, params)
       header = [d[0] for d in cursor.description]
       rows = cursor.fetchall()
       with open(out_file, "w", newline="", encoding="utf-8") as f:
           writer = csv.writer(f, lineterminator="\n")
           writer.writerow(header)
           writer.writerows(rows)
       return {"query": query_file, "query_sha256": hashlib.sha256(sql.encode()).hexdigest(),
               "file": str(out_file), "rows": len(rows)}
   
   
   def main():
       params = {"created_from": "2026-01-01", "created_before": "2026-07-01"}
       con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)
       OUT.mkdir(parents=True, exist_ok=True)
       files = [
           run(con, "queries/extract_tickets.sql", params, OUT / "tickets.csv"),
           run(con, "queries/extract_outcomes.sql", params, OUT / "outcomes.csv"),
       ]
       con.close()
       for f in files:
           print(f)
   
   
   if __name__ == "__main__":
       main()

   Line	Why
   cursor.description	The column names of the result. They become the CSV header.
   lineterminator="\n"	The same line ends on every computer, so the file is byte for byte the same on macOS and Windows.
   hashlib.sha256(sql.encode()).hexdigest()	The SHA-256 of the query text, as 64 hexadecimal characters.
   OUT.mkdir(parents=True, exist_ok=True)	Makes data/extract/ if it does not exist yet.

5. Run it again:

   Output

   {'query': 'queries/extract_tickets.sql', 'query_sha256': '28a0267e280062b0e02f9727cb5a95d2c8b60309e5fc992a578e44ba61b54338', 'file': 'data/extract/tickets.csv', 'rows': 1416}
   {'query': 'queries/extract_outcomes.sql', 'query_sha256': '533e9573c06975a463369aea503c4218c806640b99067d240607c1cea6fdc864', 'file': 'data/extract/outcomes.csv', 'rows': 1869}

   Your SHA-256 values are the same as these only if your query files are exact copies, including the comments and the last line break. If one differs, compare the files character by character. The row counts must be the same.

6. Replace the whole file with the final version. It adds three things: the dates as command-line options, the snapshot facts from the database, and the manifest.

   scripts/extract.py

   """Extract tickets and outcomes from larkfield.db into CSV files, with a manifest."""
   
   import argparse
   import csv
   import hashlib
   import json
   import sqlite3
   from datetime import datetime, timezone
   from pathlib import Path
   
   OUT = Path("data/extract")
   
   
   def run(con, query_file, params, out_file):
       sql = Path(query_file).read_text(encoding="utf-8")
       cursor = con.execute(sql, params)
       header = [d[0] for d in cursor.description]
       rows = cursor.fetchall()
       with open(out_file, "w", newline="", encoding="utf-8") as f:
           writer = csv.writer(f, lineterminator="\n")
           writer.writerow(header)
           writer.writerows(rows)
       return {"query": query_file, "query_sha256": hashlib.sha256(sql.encode()).hexdigest(),
               "file": str(out_file), "rows": len(rows)}
   
   
   def main():
       parser = argparse.ArgumentParser()
       parser.add_argument("--created-from", default="2026-01-01")
       parser.add_argument("--created-before", default="2026-07-01")
       args = parser.parse_args()
       params = {"created_from": args.created_from, "created_before": args.created_before}
   
       con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)
       snapshot = dict(con.execute("SELECT name, value FROM snapshot").fetchall())
       total = con.execute("SELECT COUNT(*) FROM tickets").fetchone()[0]
       OUT.mkdir(parents=True, exist_ok=True)
       files = [
           run(con, "queries/extract_tickets.sql", params, OUT / "tickets.csv"),
           run(con, "queries/extract_outcomes.sql", params, OUT / "outcomes.csv"),
       ]
       con.close()
   
       manifest = {
           "extracted_at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
           "source": snapshot,
           "selection": {**params, "rule": "created_from <= created_at < created_before"},
           "ticket_rows_in_source": total,
           "ticket_rows_not_selected": total - files[0]["rows"],
           "files": files,
       }
       (OUT / "manifest.json").write_text(json.dumps(manifest, indent=2) + "\n", encoding="utf-8")
       for f in files:
           print(f"{f['file']}: {f['rows']} rows")
       print(f"Not selected: {manifest['ticket_rows_not_selected']} ticket row(s)")
   
   
   if __name__ == "__main__":
       main()

   Part	Why
   argparse	--created-from and --created-before change the selection without a change to the code. The defaults are the full first half of 2026.
   SELECT name, value FROM snapshot	The database records its own source and export time. dict(...) turns the three rows into a dictionary.
   total - files[0]["rows"]	The ticket rows that the selection left out. A number that is not zero must have a reason.
   datetime.now(timezone.utc)	The extraction time in UTC, so it means the same in every country.

7. Run the final version:

   python3 scripts/extract.py

   Output

   data/extract/tickets.csv: 1416 rows
   data/extract/outcomes.csv: 1869 rows
   Not selected: 1 ticket row(s)

8. Open data/extract/manifest.json. Your extracted_at is your own time. Everything else is the same as here, except the two query_sha256 values if your query files differ.

   data/extract/manifest.json

   {
     "extracted_at": "2026-10-07T03:08:46+00:00",
     "source": {
       "source": "Larkfield help-desk export (synthetic, Nextia Learning C04)",
       "exported_at": "2026-07-01 06:00:00",
       "data_version": "1.0"
     },
     "selection": {
       "created_from": "2026-01-01",
       "created_before": "2026-07-01",
       "rule": "created_from <= created_at < created_before"
     },
     "ticket_rows_in_source": 1417,
     "ticket_rows_not_selected": 1,
     "files": [
       {
         "query": "queries/extract_tickets.sql",
         "query_sha256": "28a0267e280062b0e02f9727cb5a95d2c8b60309e5fc992a578e44ba61b54338",
         "file": "data/extract/tickets.csv",
         "rows": 1416
       },
       {
         "query": "queries/extract_outcomes.sql",
         "query_sha256": "533e9573c06975a463369aea503c4218c806640b99067d240607c1cea6fdc864",
         "file": "data/extract/outcomes.csv",
         "rows": 1869
       }
     ]
   }

The manifest answers Priya’s four questions. The source is the export of 2026-07-01, version 1.0. The selection has a rule and two dates. The query fingerprints and the extraction time are recorded. The 1 row not selected is the 2036 ticket. Empty cells in the CSV files are NULL values in the database. Module 4 loads them with care.

Your turn: extract March only

Change one condition: extract the tickets of March 2026 only. Predict the number of ticket rows first. You counted March in the last lesson.

1. Run the script with two options:

   python3 scripts/extract.py --created-from 2026-03-01 --created-before 2026-04-01

2. Read the output and the selection and ticket_rows_not_selected values in the manifest.

3. Run python3 scripts/extract.py again with no options, to restore the full extraction for the next module.

Show the expected result

Output

data/extract/tickets.csv: 252 rows
data/extract/outcomes.csv: 330 rows
Not selected: 1165 ticket row(s)

252 ticket rows, the same as the half-open March count in the last lesson. The manifest records "created_from": "2026-03-01" and "created_before": "2026-04-01", and 1,165 rows not selected. The two query_sha256 values do not change, because the dates are parameters and not part of the query text. The March files replace the earlier files in data/extract/. Without the manifest, nobody could tell which selection the folder holds now.

Common mistake

An f-string query breaks on a quote, or returns every row

Symptom: Tomás builds a query for one team with an f-string. It works for weeks. Then it stops with an error for one value, and for another value it returns all 1,417 rows with no error.

To see it, make a scratch file scripts/try_quote.py:

scripts/try_quote.py

import sqlite3

con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)

for team in ["returns", "customer's account", "returns' OR '1'='1"]:
    print("value:", team)
    safe = con.execute("SELECT COUNT(*) FROM tickets WHERE team = :team", {"team": team}).fetchone()[0]
    print("  parameter:", safe)
    try:
        unsafe = con.execute(f"SELECT COUNT(*) FROM tickets WHERE team = '{team}'").fetchone()[0]
        print("  f-string: ", unsafe)
    except sqlite3.Error as error:
        print("  f-string:  error:", type(error).__name__, error)

con.close()

Output of python3 scripts/try_quote.py

value: returns
  parameter: 327
  f-string:  327
value: customer's account
  parameter: 0
  f-string:  error: OperationalError near "s": syntax error
value: returns' OR '1'='1
  parameter: 0
  f-string:  1417

Cause: the f-string puts the value inside the SQL text. The quote in customer's ends the SQL string early, so SQLite reads s account' as SQL, and fails. The third value is worse: its quotes turn it into SQL code, team = 'returns' OR '1'='1', which is true for every row. Attackers use this method, called SQL injection, to read or change data that they must not reach.

Fix: use a parameter for every value: WHERE team = :team, and pass {"team": team}. With a parameter, both strange values are only text: no team has that name, so the count is 0. Delete scripts/try_quote.py when you are done.

In AI work: values often come from outside. Examples are a date from a scheduler, a customer name, a ticket text, or the output of a language model. Any of them can contain a quote. A parameter keeps each one a value.

Check your understanding

You run extract.py for January and then for June. Do the query_sha256 values in the two manifests differ? Why?

Show the answer

No. The script computes the SHA-256 from the query file’s text, and the dates are not in the text. They are parameters, passed separately. The selection part of the manifest shows the different dates. Suppose an f-string put the dates into the query text. Then every selection would have a different fingerprint, and you could not see if the query logic changed.

Check your understanding

The manifest says ticket_rows_not_selected: 1. Why is this number in the manifest, and what should you do with it?

Show the answer

It makes the rows that the selection left out visible. An extraction that silently drops rows looks the same as one that drops none. Each row left out needs a reason: here, T-10147 has a created_at in 2036, outside the range. If the number changes in a later extraction with the same dates, the source data changed.

Module practice

The outline’s task for this module: extract one latest outcome per ticket, without accidental duplication.

Worked example: where duplicates come from

Mei starts small, with three tickets from the last lesson. The window step numbers each ticket’s events, and the filter rn = 1 keeps one row each:

ticket_id	events	latest event (rn = 1)	status
T-10120	2	O-00246	resolved
T-10135	1	O-00182	resolved
T-10136	3 (two at the same second)	O-00229	resolved

Three tickets, three rows. On the real data, there are two ways to get more rows than tickets, and both are quiet:

1. Ties. An ORDER BY without outcome_id gives 1,432 rows with RANK(), for 1,399 tickets.

2. A join to the repeated ticket rows. Mei joins the latest events (1,399 rows) to tickets to keep only the tickets of the export:

   WITH ranked AS (
       SELECT ticket_id, outcome_id, recorded_at, status,
              ROW_NUMBER() OVER (
                  PARTITION BY ticket_id
                  ORDER BY recorded_at DESC, outcome_id DESC
              ) AS rn
       FROM outcomes
   ),
   latest AS (
       SELECT * FROM ranked WHERE rn = 1
   )
   SELECT COUNT(*) AS result_rows, COUNT(DISTINCT t.ticket_id) AS tickets
   FROM tickets AS t
   JOIN latest AS l
       ON l.ticket_id = t.ticket_id;

   result_rows	tickets
   1409	1395

   The 14 tickets that appear twice in tickets each match their latest event twice. The join also removed the 4 events of tickets that are not in tickets (1,399 → 1,395 tickets).

The fix is to join to a list that has one row per ticket: SELECT DISTINCT ticket_id FROM tickets. Then the check is: result rows = distinct tickets = tickets that have events. On this snapshot, that number is 1,395: 1,403 distinct tickets, minus the 8 tickets with no event.

Your task

Write a query that returns one row per ticket in the export that has at least one outcome event. Return four columns: ticket_id, outcome_id, recorded_at and status of the latest event. Break ties with outcome_id.

Run SQL in your browser

Module practice: one latest outcome per ticket

The box shows the first 50 rows and the total count. The total must be 1,395. The check compares every row with ours, so a wrong tie choice (for example, reopened for T-10136) fails even when the count is right.

SQL query (you can change it)-- One latest outcome event per ticket in the export. -- Columns: ticket_id, outcome_id, recorded_at, status. -- Write your query here. SELECT ticket_id, outcome_id, recorded_at, status FROM outcomes LIMIT 5;

Then do the same on your computer, and save the checks with it:

1. Save your query as queries/35-latest-outcome.sql, and run python3 q.py queries/35-latest-outcome.sql. The last line must be 1395 rows (showing 20; add --all to see every row).
2. Make queries/36-latest-outcome-check.sql, which prints three numbers: the result rows, the distinct tickets in the result, and the tickets in the export that have events.
3. Run it. All three numbers must be equal.

Show the model answer

queries/35-latest-outcome.sql

-- One latest outcome event per ticket in the export.
WITH unique_tickets AS (
    SELECT DISTINCT ticket_id
    FROM tickets
),
ranked AS (
    SELECT
        o.ticket_id,
        o.outcome_id,
        o.recorded_at,
        o.status,
        ROW_NUMBER() OVER (
            PARTITION BY o.ticket_id
            ORDER BY o.recorded_at DESC, o.outcome_id DESC
        ) AS rn
    FROM outcomes AS o
    JOIN unique_tickets AS u
        ON u.ticket_id = o.ticket_id
)
SELECT ticket_id, outcome_id, recorded_at, status
FROM ranked
WHERE rn = 1
ORDER BY ticket_id;

Output (first and last lines)

ticket_id  outcome_id  recorded_at          status
---------  ----------  -------------------  --------
T-10001    O-00005     2026-01-06 17:58:45  resolved
T-10002    O-00001     2026-01-05 15:29:21  resolved
T-10003    O-00075     2026-01-13 18:56:26  resolved
...

1395 rows (showing 20; add --all to see every row)

queries/36-latest-outcome-check.sql

-- Checks for 35-latest-outcome.sql: all three numbers must be equal.
WITH unique_tickets AS (
    SELECT DISTINCT ticket_id
    FROM tickets
),
ranked AS (
    SELECT
        o.ticket_id,
        o.outcome_id,
        o.recorded_at,
        o.status,
        ROW_NUMBER() OVER (
            PARTITION BY o.ticket_id
            ORDER BY o.recorded_at DESC, o.outcome_id DESC
        ) AS rn
    FROM outcomes AS o
    JOIN unique_tickets AS u
        ON u.ticket_id = o.ticket_id
),
latest AS (
    SELECT ticket_id, outcome_id, recorded_at, status
    FROM ranked
    WHERE rn = 1
)
SELECT
    (SELECT COUNT(*) FROM latest) AS latest_rows,
    (SELECT COUNT(DISTINCT ticket_id) FROM latest) AS distinct_tickets,
    (SELECT COUNT(*) FROM unique_tickets
     WHERE ticket_id IN (SELECT ticket_id FROM outcomes)) AS tickets_with_events;

Output

latest_rows  distinct_tickets  tickets_with_events
-----------  ----------------  -------------------
1395         1395              1395

1 row

Of the 1,395 latest events, 1,391 are resolved, 3 are escalated and 1 is reopened. The join to unique_tickets leaves out the 4 events of missing tickets. DISTINCT stops the 14 repeated ticket rows from doubling events. outcome_id DESC decides the 33 ties: the event logged last wins.

Module check

Check your understanding

Retrieval: what is the difference between a CTE and a window function?

Show the answer

A CTE (WITH name AS (...)) gives a name to a middle result, so a query can be written and checked as steps. A window function (... OVER (PARTITION BY ... ORDER BY ...)) adds a value to each row from a group of related rows, and keeps every row. They work together: a window function computes rn in one CTE, and the next step filters rn = 1.

Check your understanding

Predict: in your latest-outcome query, you change ROW_NUMBER() to RANK() and remove outcome_id DESC. How many rows does the query return, and which check catches it?

Show the answer

More than 1,395 rows. 33 tickets have more than one event at their latest time, so RANK() gives two rows with rank 1 for each of them. Among the tickets in the export, that is 1,428 rows. The check “result rows = distinct tickets” catches it: 1,428 rows, but 1,395 distinct tickets.

Check your understanding

Completion check: explain how your query handles ties. Then show that your extraction gives the same selection on the fixed snapshot.

Show the answer

Ties. When two events of a ticket have the same recorded_at, the query orders them by outcome_id DESC. The help desk gives outcome IDs in logging order, so the event logged last wins. For T-10136, resolved (O-00229) wins over reopened (O-00228), both at 2026-01-28 19:46:52. Without this rule, RANK() keeps both rows and ROW_NUMBER() keeps one that the database chooses.

Reproduce. Run python3 scripts/extract.py twice. Compare the two manifests: extracted_at changes, and everything else is the same. Then compare the CSV files’ SHA-256 with ours:

macOS

shasum -a 256 data/extract/tickets.csv data/extract/outcomes.csv

Linux

sha256sum data/extract/tickets.csv data/extract/outcomes.csv

Windows

Get-FileHash data\extract\tickets.csv, data\extract\outcomes.csv

Expected (macOS)

96786af9e2ebb2492219867ac302f57fabdefb4c53fb60a1071d9412e70e6f39  data/extract/tickets.csv
60c828f37db48a01ac6f003f5879a2c3ed5254f2c0e12816f814ed661c4067b4  data/extract/outcomes.csv

PowerShell shows the same values in capital letters. The counts must be 1,416 ticket rows and 1,869 outcome events, from the snapshot exported at 2026-07-01 06:00:00, version 1.0. The file fingerprints depend only on the snapshot, the selection and the query results, so they match ours even if your query comments differ.

Recap

In this module, you made your queries readable, correct over time, and repeatable:

• CTEs split a query into named steps. Count the rows of each step, and know which count must not change.
• Times in SQLite are text. Check MIN and MAX first, and filter with half-open ranges.
• Window functions number, rank and compare rows inside partitions. For “latest per ticket”, break ties with outcome_id, and check that rows = distinct tickets.
• Named parameters keep values out of the SQL text: no broken quotes, no injection, and the same query fingerprint for every selection.
• scripts/extract.py writes the extraction and a manifest: the snapshot, the selection, the query SHA-256 values and the row counts. Run it twice, and the files are the same.

Next, Module 4 profiles the extracted data before it changes anything. It looks at missing values, repeated rows, misspelled categories and values in the wrong unit.

Next: Profile before changing