Tom's spreadsheet says 412 open sanitation requests. Your dashboard says 388. They're both reading the same city API — so why don't the numbers match? The answer lives in the most underrated skill in engineering: knowing what data actually looks like when it travels.

Thursday, 10:40 AM. Tom, the borough manager, walks into your weekly check-in holding a printed spreadsheet. "Your dashboard says 388 open sanitation requests," he says. "My export says 412." He has highlighted the difference in yellow. Maria, the operations director, looks at you: "Which one is right?"

Both of you pulled from the same NYC 311 API — Tom exported to Excel, you built a dashboard — and now there are two official numbers in a room full of people who need one.

Dev, the data engineer, isn't surprised. "The API's created_date looks unreliable," he mutters. "I wouldn't trust either number until we actually look at the records."

This week, we look at the records.

The client problem: two sources, two answers

This is a rite of passage in field engineering. A stakeholder exports data into a familiar tool, gets a different answer than your system, and suddenly your credibility is on trial — even though both numbers came from the same place.

Resist the urge to argue that your dashboard is correct. You don't know that yet. Somewhere between the city's API and Tom's spreadsheet, something changed — and finding it takes you through this post's three subjects: data formats, database shapes, and why queries get slow (slow dashboards are how stakeholders end up in Excel).

The investigation order: read a raw record field by field, like a contract. See how formats change data when it moves. Then model it in a database so there's exactly one definition of "open request."

Skill one: read a data shape from a sample

The 311 API returns JSON. Here is one synthetic record modeled on the NYC 311 API schema (not an observed record — the agency/complaint combination is illustrative):

{
  "unique_key": "60612345",
  "created_date": "2026-09-28T14:32:11.000",
  "closed_date": "2026-09-30T09:15:44.000",
  "agency": "DEP",
  "agency_name": "Department of Environmental Protection",
  "complaint_type": "Noise - Street/Sidewalk",
  "descriptor": "Loud Music/Party",
  "location_type": "Street/Sidewalk",
  "incident_zip": "11215",
  "incident_address": "5 AVENUE",
  "street_name": "5 AVENUE",
  "city": "BROOKLYN",
  "borough": "BROOKLYN",
  "latitude": "40.672098",
  "longitude": "-73.982212",
  "status": "Closed",
  "community_board": "6 Brooklyn",
  "council_district": "39"
}

No documentation you trust? Interrogate five or ten real records and ask four questions:

1. Which field is the identity? Here it's unique_key — the one value that identifies this request and nothing else. If two records share it, you have a duplicate. If it's missing, you can't reconcile anything downstream. Identity fields are the foundation of every "does your number match mine" conversation.

2. What type is each field, really? Notice latitude is a string, not a number — the API wraps coordinates in quotes. Your dashboard parses them into floats; Tom's Excel may treat them as text and sort "9.5" before "40.6". Type mismatches don't throw errors; they just produce different answers.

3. Which fields can be missing? closed_date is absent on open requests; incident_zip is sometimes empty. Code that assumes these fields exist will crash at 2 AM — or worse, silently drop records. Dev's skepticism belongs here too: he spotted records where closed_date comes before created_date. That's not paranoia; every quirk you don't model becomes a wrong number in a meeting.

4. What does each status value actually mean? The API returns "Open", "Closed", "Pending" — but "Pending" doesn't mean what a borough manager thinks it means. The moment you define these values for CityOps, you own the definition of Tom's number. Write it down.

The same data in three formats

The same record can travel as JSON, CSV, or YAML. They aren't interchangeable — each makes different tradeoffs:

FormatShapeBest forWatch out for
JSONNested objects, arrays, typed-ish valuesAPIs, config with structure, anything a program readsNo comments, no trailing commas; types are loose (everything could be a string)
CSVFlat rows and columnsSpreadsheets, bulk exports, TomUnquoted commas, encoding surprises, header drift
YAMLReadable nesting via indentationHuman-edited config filesIndentation errors break parsing; tabs are forbidden

JSON is what the API speaks. CSV is what Tom speaks. YAML is what your deployment config will speak later (Stage 5). Here's that same 311 record, flattened into the CSV Tom downloaded:

unique_key,created_date,closed_date,agency,complaint_type,descriptor,incident_zip,city,borough,status
60612345,2026-09-28T14:32:11.000,2026-09-30T09:15:44.000,DEP,Noise - Street/Sidewalk,Loud Music/Party,11215,BROOKLYN,BROOKLYN,Closed
60612346,2026-09-28T15:03:02.000,,DSNY,Street Condition,"Pothole, large",10001,MANHATTAN,MANHATTAN,Open

Notice what CSV already did to the data: the nesting is gone, latitude and longitude didn't survive the flat export, and "Pothole, large" needs quotes because it contains a comma. Every format conversion is a small translation — and every translation can introduce a small lie.

When a CSV breaks (and how Tom's export lied to him)

Tom's 412 vs your 388 came down to three classic CSV failures, stacked on top of each other:

Failure 1: commas in unquoted fields. Tom opened the CSV by double-clicking it, and Excel parsed it its own way. One descriptor — Loud party, building 4 — arrived without quotes around it, so the parser split it into two fields and shifted every column after it: the zip landed in the city column, "BROOKLYN" became the status, and the row vanished from the "Open" count. A handful of shifted rows, and the numbers drift.

Failure 2: header drift. Between this month's export and last month's, the city renamed a column and added a new one in the middle. Tom's formulas referenced column letters, not names — so "status" moved from column J to column K, and his filter silently read the wrong column.

Failure 3: encoding. A few records had accented street names in a legacy encoding. Excel guessed wrong, turned them into mojibake, and Tom deleted those rows during cleanup "because they looked corrupt." They were real requests that vanished from his count.

Reconcile it: the ledger

Three plausible causes — but plausible isn't reconciled. The rule: don't tell the customer what caused the difference until you can account for the difference record by record. Here's the ledger, built from the scenario's own causes (fictional numbers, illustrating the method):

Reconciliation stepOpen count
Tom's original spreadsheet412
Remove 16 rows counted as Open because the status filter read the wrong column after header drift396
Remove 12 rows where unquoted-comma shifts mislabeled closed requests as Open384
Restore 4 valid Open requests deleted as "corrupt" during cleanup388
Reconciled count388

Notice the direction: Tom's count was higher, so the ledger must explain inflation, not just loss. Two causes inflated his number (the shifted column, the shifted rows); one deflated it (the deleted rows). If your explanation only accounts for rows disappearing, it can't explain a count that went up.

And one more condition the ledger assumes: the same source cutoff, the same filters, the same status definition. Even correctly parsed datasets differ when captured at different times, queried with different borough or agency filters, or using different status rules. Before you reconcile records, reconcile the query: same cutoff timestamp, same filters, same definition of "open."

The difference between a CSV parsed properly and one split naively:

import csv

line = '60612346,2026-09-28T15:03:02.000,,DSNY,Street Condition,"Pothole, large",10001,MANHATTAN,MANHATTAN,Open'

# The naive way Tom's first attempt worked: split on commas
print(line.split(','))
# -> [..., 'Street Condition', '"Pothole', ' large"', ...]  -- 11 pieces, not 10

# The right way: let the csv module handle quoting
reader = csv.reader([line])
print(next(reader))
# -> [..., 'Street Condition', 'Pothole, large', ...]  -- 10 clean fields

The naive split turns one field into two and shifts everything after it. Ten columns become eleven, filters read the wrong data, and the count drifts. This is not a rare edge case — this is the usual result when someone hand-rolls a CSV parser.

Relational vs document: two ways to store the truth

Once you've read the data, you have to store it. The two shapes you'll meet everywhere:

Relational (SQL)Document (NoSQL)
ShapeTables with fixed columns; rows reference each other by keysSelf-contained documents (usually JSON); each record carries its own structure
StrengthOne definition of each fact; joins answer cross-cutting questions reliablyFlexible — a record can gain new fields without a migration
WeaknessSchema changes require migrations; you decide the shape up frontThe same field can mean different things in different documents; reconciliation is harder
When it winsCounting, reporting, reconciliation — "give me one true number"Rapidly changing shapes, nested data you rarely query across

For CityOps, relational wins. The project exists to produce numbers people can argue with and then agree on: open requests by agency, resolution times by borough. That is counting and joining. Relational tables are a natural fit for CityOps' reporting and referential-integrity requirements; document databases can also support reliable reporting, but would require equally explicit validation, update rules, and query definitions. Dev's unreliable created_date isn't fixed by a different database; it's fixed by validation rules and a quarantine table, which we'll build below.

Here's the minimal relational model for our 311 data. Two tables, each owning one kind of fact — the agency table holds known agency codes; requests reference those codes:

CREATE TABLE agencies (
  agency      TEXT PRIMARY KEY,
  agency_name TEXT
);

CREATE TABLE requests (
  unique_key     TEXT PRIMARY KEY,
  created_date   TEXT NOT NULL,
  closed_date    TEXT,
  agency         TEXT NOT NULL REFERENCES agencies(agency),
  complaint_type TEXT NOT NULL,
  descriptor     TEXT,
  incident_zip   TEXT,
  borough        TEXT,
  status         TEXT NOT NULL,
  latitude       REAL,
  longitude      REAL
);

Notice the decisions baked in: the primary key on unique_key means duplicates are rejected by the database itself — not by your memory of checking. The REFERENCES clause — a foreign key — means "DEP" is spelled one way, in one place. Schema design is where customer judgment becomes durable: every argument you settle in a meeting — what counts as "open," which agency codes exist — can live in the schema, enforced every time data arrives.

Indexes: the before-and-after story

Now the question that made your dashboard slow enough for Tom to export his own copy in the first place. The dashboard query is simple:

SELECT agency, COUNT(*) AS open_requests
FROM requests
WHERE status = 'Open'
GROUP BY agency
ORDER BY open_requests DESC;

With a few thousand rows, this is instant. At two million rows — the real scale of the 311 feed — it isn't. Without an index, the database answers by scanning every row: read row 1, check the status, read row 2, check the status, all the way down. This is a full table scan, and its cost grows with every record you add.

An index is a separate lookup structure the database maintains alongside the table — the index at the back of a textbook. Instead of reading every page, you look up the entry and jump straight there:

CREATE INDEX idx_requests_status_agency ON requests(status, agency);

SQLite shows you its plan directly:

-- Before the index:
EXPLAIN QUERY PLAN
SELECT agency, COUNT(*) FROM requests WHERE status = 'Open' GROUP BY agency;
-- SCAN requests          <-- reads every row

-- After the index:
EXPLAIN QUERY PLAN
SELECT agency, COUNT(*) FROM requests WHERE status = 'Open' GROUP BY agency;
-- SEARCH requests USING INDEX idx_requests_status_agency (status=?)   <-- jumps to the rows

SCAN versus SEARCH is the planner choosing a smarter path — but treat the demonstration honestly. The composite index (status, agency) matches this query's shape: it filters by status and groups by agency. A status-only index may help less, especially if most records share the same status — the planner weighs the data distribution, not just the index's existence. And a single timing on 5,000 rows proves little: SQLite may answer either version in milliseconds, and one measurement is mostly machine noise. Run EXPLAIN QUERY PLAN before and after, measure repeated executions on a sufficiently large, representative dataset, and only then claim a performance improvement.

A slow dashboard is still how you get a stakeholder who exports to Excel and stops trusting your numbers. Performance is a trust issue, not just a technical one — which is exactly why the measurement has to be honest.

Myth: "Indexes make everything faster, so index every column."
Every index costs you on every write — the database has to update the textbook index each time a page changes — and costs disk space. Index the columns you actually filter, join, and sort on (status, agency, created_date for us). The right number of indexes is "the queries you run," not "all of them."

Fix it together: the runnable example

One script, the whole lesson: fetch real 311 records from the live API, validate them, store them in SQLite, add the index, and inspect the query plan before and after. It needs nothing but Python 3 and the standard library. Read it as a guided preview of Stage 2 — the core Stage 1 outcome is recognizing data-format risks, understanding primary keys and indexes, and explaining how to investigate a count discrepancy. You don't need to master every line today.

import json
import sqlite3
import urllib.request
from datetime import datetime, timezone

# This is a sample ingestion, not a complete 311 dataset load.
# Later we'll add pagination, stable ordering, incremental updates,
# and source-watermark tracking.
API = "https://data.cityofnewyork.us/resource/erm2-nwe9.json?\$limit=5000"

REQUIRED = ["unique_key", "created_date", "agency", "complaint_type", "status"]
# Agreed statuses, updated after the first run quarantined "In Progress":
# the validator surfaced values the contract didn't anticipate, so the
# contract was updated. That's the validator doing its job.
VALID_STATUSES = {"Open", "Closed", "Pending",
                  "In Progress", "Assigned", "Unspecified"}

def parse_coordinate(value, field, problems):
    if value in (None, ""):
        return None
    try:
        number = float(value)
    except (TypeError, ValueError):
        problems.append(f"invalid {field}: {value!r}")
        return None
    lower, upper = (-90, 90) if field == "latitude" else (-180, 180)
    if not lower <= number <= upper:
        problems.append(f"{field} out of range: {number}")
        return None
    return number

def validate_record(rec):
    """Return (normalized, problems); empty problems means clean."""
    problems = []
    for field in REQUIRED:
        if not rec.get(field):
            problems.append(f"missing {field}")
    if rec.get("status") and rec["status"] not in VALID_STATUSES:
        problems.append(f"unknown status: {rec['status']}")
    parsed = {}
    for ts in ("created_date", "closed_date"):
        val = rec.get(ts)
        if val:
            try:
                parsed[ts] = datetime.fromisoformat(val)
            except ValueError:
                problems.append(f"bad timestamp in {ts}: {val}")
    if "created_date" in parsed and "closed_date" in parsed:
        if parsed["closed_date"] < parsed["created_date"]:
            problems.append("closed_date is earlier than created_date")
    lat = parse_coordinate(rec.get("latitude"), "latitude", problems)
    lon = parse_coordinate(rec.get("longitude"), "longitude", problems)
    if problems:
        return None, problems
    return {
        "unique_key": rec["unique_key"],
        "created_date": rec["created_date"],
        "closed_date": rec.get("closed_date"),
        "agency": rec["agency"],
        "complaint_type": rec["complaint_type"],
        "descriptor": rec.get("descriptor"),
        "incident_zip": rec.get("incident_zip"),
        "borough": rec.get("borough"),
        "status": rec["status"],
        "latitude": lat,
        "longitude": lon,
    }, []

# 1. Fetch from the live API (free, no key)
RUN_ID = datetime.now(timezone.utc).isoformat(timespec="seconds")
req = urllib.request.Request(API, headers={"User-Agent": "CityOps-ingest/0.1"})
with urllib.request.urlopen(req, timeout=30) as resp:
    raw_bytes = resp.read()
records = json.loads(raw_bytes.decode("utf-8"))
print(f"run {RUN_ID}: fetched {len(records)} records")

# Persist the raw response for reproducibility
with open("raw_requests.json", "w", encoding="utf-8") as f:
    f.write(raw_bytes.decode("utf-8"))

# 2. Validate; quarantine durably instead of dropping silently
good, quarantined = [], []
for rec in records:
    normalized, problems = validate_record(rec)
    if problems:
        quarantined.append((rec, problems))
    else:
        good.append(normalized)
print(f"clean: {len(good)}, quarantined: {len(quarantined)}")
with open("quarantine.jsonl", "w", encoding="utf-8") as f:
    for rec, problems in quarantined:
        f.write(json.dumps({"record": rec, "problems": problems}) + "\n")

# 3. Store in SQLite with the relational schema
db = sqlite3.connect("cityops.db")
db.execute("PRAGMA foreign_keys = ON")  # SQLite needs this per connection
db.execute("DROP TABLE IF EXISTS requests")
db.execute("DROP TABLE IF EXISTS agencies")
db.execute("""CREATE TABLE agencies (
  agency TEXT PRIMARY KEY,
  agency_name TEXT)""")
db.execute("""CREATE TABLE requests (
  unique_key TEXT PRIMARY KEY,
  created_date TEXT NOT NULL,
  closed_date TEXT,
  agency TEXT NOT NULL REFERENCES agencies(agency),
  complaint_type TEXT NOT NULL,
  descriptor TEXT,
  incident_zip TEXT,
  borough TEXT,
  status TEXT NOT NULL,
  latitude REAL,
  longitude REAL)""")
# A foreign key ensures the agency code refers to a valid parent row;
# it does not, by itself, prove the source agency code is semantically correct.
for rec in good:
    db.execute("INSERT OR IGNORE INTO agencies (agency) VALUES (?)",
               (rec["agency"],))
inserted = updated = 0
for rec in good:
    cur = db.execute(
        """INSERT INTO requests
           (unique_key, created_date, closed_date, agency, complaint_type,
            descriptor, incident_zip, borough, status, latitude, longitude)
           VALUES (?,?,?,?,?,?,?,?,?,?,?)
           ON CONFLICT(unique_key) DO UPDATE SET
             closed_date = excluded.closed_date,
             status = excluded.status,
             descriptor = excluded.descriptor""",
        (rec["unique_key"], rec["created_date"], rec["closed_date"],
         rec["agency"], rec["complaint_type"], rec["descriptor"],
         rec["incident_zip"], rec["borough"], rec["status"],
         rec["latitude"], rec["longitude"]))
    if cur.rowcount == 1:
        inserted += 1
    else:
        updated += 1
db.commit()

# One definition of "open", shared by the dashboard and every export
db.execute("DROP VIEW IF EXISTS open_requests")
db.execute("CREATE VIEW open_requests AS "
           "SELECT * FROM requests WHERE status = 'Open'")

QUERY = ("SELECT agency, COUNT(*) FROM open_requests "
         "GROUP BY agency ORDER BY 2 DESC")

# 4. Inspect the plan before and after the index
print("before:", db.execute("EXPLAIN QUERY PLAN " + QUERY).fetchall())
db.execute("CREATE INDEX idx_requests_status_agency "
           "ON requests(status, agency)")
print("after: ", db.execute("EXPLAIN QUERY PLAN " + QUERY).fetchall())
for agency, count in db.execute(QUERY).fetchall()[:5]:
    print(f"  {agency}: {count} open")
db.close()
print(f"inserted: {inserted}, updated: {updated}")

Five habits in this script, not just code. Quarantine durably: bad records land in quarantine.jsonl with their reasons attached — inspectable after the program exits. Tom's deleted "corrupt" rows are what happens when a pipeline drops data without a trace. Explicit upsert policy: ON CONFLICT ... DO UPDATE replays the same input to the same intended state — including status changes from Open to Closed — instead of silently ignoring records we've seen before. Idempotency means replaying the same input produces the same intended state, not ignoring every duplicate key. Count everything: fetched, clean, quarantined, inserted, updated — fetched must equal clean plus quarantined, or something escaped the accounting. The plan, not folklore: EXPLAIN QUERY PLAN before and after the index turns "indexes are faster" into evidence. Scale $limit up and watch the gap grow — on a representative dataset, with repeated measurements.

Break it on purpose

Two experiments, five minutes each:

Experiment 1: feed it the bad CSV. Take the sample CSV from earlier and add this row — a descriptor with a comma that lost its quotes:

60612347,2026-09-28T16:44:00.000,,DEP,Noise - Residential,Loud party, building 4,11215,BROOKLYN,BROOKLYN,Open

Parse it with csv.reader and count the fields: 11 instead of 10. The unquoted comma split the descriptor in two and shifted everything after it — the zip landed in the city column, and the row's status became "BROOKLYN". Now imagine that row flowing into a GROUP BY status and ask what Tom's spreadsheet did with it. A CSV has no schema enforcement — it carries corruption downstream, and the corruption looks like a slightly wrong number in a meeting.

Experiment 2: drop the index and grow the data. Change $limit=5000 to $limit=100000, comment out the CREATE INDEX line, and re-run. Compare the EXPLAIN QUERY PLAN output, restore the index, run again. At a hundred thousand rows the difference stops being academic — and the 311 feed holds millions. Two caveats: a single API request is not a reliable way to ingest a large dataset (production ingestion needs pagination, stable ordering, and snapshot consistency — Stage 2's territory), and one timing run is noise, so repeat the measurement. Now you understand, in your hands, why the dashboard got slow enough for Tom to reach for Excel.

Make it production-safe: validate on ingest

The script above already contains the production habit that matters most at this stage: validate every record at the boundary, before it touches your tables. This is the seed of a data contract — a written, enforced promise about what incoming data looks like (Stage 2 makes it a runnable artifact):

Reject the row, not the batch. One malformed record should never kill a 5,000-record pull. Quarantine it with its reason, keep the receipt, move on.

Check Dev's complaints explicitly. Dev said created_date looks unreliable — so the validator checks it directly: present, parseable, and not after closed_date. A colleague's skepticism is a requirements document in disguise. When the quarantine report shows exactly which records failed and why, Dev's distrust becomes the thing that made the numbers trustworthy.

Count everything, twice. Records fetched, records clean, records quarantined, rows inserted, rows updated. If fetched doesn't equal clean plus quarantined, something escaped your accounting. Reconciliation — every record accounted for — is the difference between a pipeline and a hope.

Explain it to Tom in plain language

You owe Tom an explanation, not a lecture — two paragraphs, no jargon:

"Tom, I found the gap between your 412 and our 388 — and I can show you every record in it. Three things happened to your spreadsheet on the way from the city's system: the city's export added a column since last month, which moved your status filter onto the wrong column and counted 16 non-open rows as open; a few complaint descriptions contained commas that shifted their rows sideways, mislabeling 12 closed requests as open; and 4 rows with unusual characters were deleted during cleanup even though they were real, open requests. 412 minus 16 minus 12 plus 4 is 388 — the ledger balances. None of this was your fault — the export format is fragile, and it breaks quietly."

"Here's what changes. CityOps will now validate every record the moment it arrives — checking that dates make sense and required fields are present — and set aside anything suspicious with a written reason instead of silently dropping it. We'll publish one definition of 'open request' that both the dashboard and any export use, so the numbers can't drift apart again. And I'll give you an export button in CityOps itself, built from the same validated data as the dashboard, so you never have to hand-fix a spreadsheet to trust a number."

Notice what that message does: it names the causes without blame, it describes a mechanism rather than a promise, and it ends with something Tom can use. That is customer communication for a data problem.

A trustworthy dashboard isn't one that shows a number. It's one that can explain where every record came from, which rules were applied, and why its number matches — or differs from — the customer's.

Add it to CityOps

This post isn't a detour — it's a permanent part of the project. Here's exactly what changes in the CityOps scaffold this week:

1. The database gets a schema, not just a file. The runnable script above becomes ingest.py in the repo: it creates cityops.db with the agencies and requests tables, the primary key on unique_key, the foreign key to agencies, the open_requests view, and the composite index on (status, agency). From now on, every part of CityOps — the dashboard, Tom's future export button, the Stage 2 pipeline — reads from these tables and this view. One definition of "open request," enforced by the schema, used by everyone.

2. The data contract v0 gets its first real entries. A data contract is a written promise about what incoming data looks like — and this week you can write the first version from evidence, not guesses:

## CityOps Data Contract v0 — 311 intake

### Definitions
- "Open request": a row in the `open_requests` view
  (`SELECT * FROM requests WHERE status = 'Open'`).
  The dashboard AND any export query this view — one definition, used by everyone.
  (Settled 2026-10-02 after the Tom/Maria meeting: 'Pending' does NOT
  count as open until the agency confirms the mapping.)
- Identity: `unique_key`. Duplicates are rejected by the primary key;
  replays update mutable fields per the upsert policy.
- Reporting rules: same source cutoff timestamp, same borough/agency
  filters, same status mapping, same data snapshot (run ID) — or the
  numbers are not comparable.

### Validation rules (enforced by ingest.py, quarantine on failure)
- unique_key, created_date, agency, complaint_type, status must be present.
- status must be one of the agreed values
  (Open, Closed, Pending, In Progress, Assigned, Unspecified —
  updated after the first run surfaced unanticipated values).
- created_date and closed_date must parse as ISO timestamps.
- closed_date must not be earlier than created_date
  (Dev's finding: the feed contains such rows; they are quarantined,
  not silently fixed.)
- Coordinates must be numeric and in range, or absent.

### Reconciliation
- Every run logs: fetched, clean, quarantined, inserted, updated.
- fetched must equal clean + quarantined. If not, the run is flagged.
- Quarantined records persist in quarantine.jsonl with reasons;
  the raw response persists per run for re-examination.

3. One decision, written down. FDE work runs on decisions with reasons attached. This week's, for the project log:

Decision: store validated rows in SQLite with a relational schema now; keep a copy of the raw JSON per run for audit. Rationale: the project's questions are counting questions, which relational handles well; the raw copies let Dev re-examine anything the validator quarantines. Revisit if daily volume outgrows a single file or concurrent writers appear.

4. Tom gets his export button — eventually. Not this week. The win that matters now: the next time Tom exports, the export will come from the same validated tables as the dashboard. The number can't drift, because there's only one place it comes from. The button itself is a Stage 3 milestone item; the reason it will be trustworthy is built this week.

Must know

  • Read a schema from a sample: identity field, real types (watch for numbers-as-strings), nullable fields, what each status value means
  • JSON vs CSV vs YAML: JSON for APIs, CSV for spreadsheets and bulk exports, YAML for human-edited config — and every conversion can change the data
  • CSV failure modes: unquoted commas, header drift, encoding guesses — don't hand-roll a parser, and don't trust column positions
  • Relational modeling: one table per kind of fact, primary keys reject duplicates, foreign keys keep spellings consistent
  • Indexes: what a full table scan is, what SEARCH ... USING INDEX means in EXPLAIN QUERY PLAN, and that indexes cost on writes
  • Validate on ingest: quarantine bad rows with reasons, reconcile every count, make re-runs idempotent

Useful later

  • Window functions and CTEs (common table expressions) for week-over-week trend queries (Stage 2 goes deep on SQL)
  • Composite vs covering indexes, and reading full EXPLAIN output on larger databases
  • Schema migration tools — for when the model needs to change without losing data
  • JSON Schema / Pydantic / Great Expectations as runnable data contracts (Stage 2's milestone)

Don't memorize this

  • SQL dialect trivia: which database says LIMIT vs TOP vs FETCH FIRST, backticks vs double quotes for identifiers, the exact list of SQLite type affinities — look it up when you need it; the concepts transfer
  • The full Socrata query language — $limit, $where, and $order cover most field work; the rest is documentation away
  • Every 311 field name — the feed has dozens; know the dozen you use and where the contract lists the rest
Discover→Scope→Build→Deploy→Adopt→Learn

This week lived in Discover and Scope: the schema, the validation rules, and the written definition of "open" are scoping decisions — they say what CityOps promises before a line of dashboard code depends on them.

Field check

  1. Next month Tom exports again and his number differs from the dashboard. List your debugging steps in order — what do you check first, and why?
  2. Dev proposes storing the raw JSON documents instead of relational tables: "the feed schema keeps changing, and rigid tables will just break." What is the real tradeoff here, and what compromise would you propose?
  3. You add an index and the dashboard query is still slow. What could be wrong, how would you detect it, and what would you tell Maria?
What a good answer looks like

Start with reconciliation, not blame: compare the counts at each stage — rows fetched from the API, rows passing validation, rows quarantined, rows in the table, rows the dashboard query returns. The stage where the numbers diverge is where the bug lives. Check the quarantine log first (it's the designed place for surprises), then whether the export used the same definition of "open" as the dashboard. If the numbers reconcile and still differ, the difference is in the definition or the format — like Tom's column shift — not the data. A good answer names the mechanism, shows the receipts, and never asks the stakeholder to trust a number it can't account for.