Data Formats and Databases in One Sitting

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.

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 realistic record, close to what the live endpoint serves:

{
  "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:

Format	Shape	Best for	Watch out for
JSON	Nested objects, arrays, typed-ish values	APIs, config with structure, anything a program reads	No comments, no trailing commas; types are loose (everything could be a string)
CSV	Flat rows and columns	Spreadsheets, bulk exports, Tom	Unquoted commas, encoding surprises, header drift
YAML	Readable nesting via indentation	Human-edited config files	Indentation 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.

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)
Shape	Tables with fixed columns; rows reference each other by keys	Self-contained documents (usually JSON); each record carries its own structure
Strength	One definition of each fact; joins answer cross-cutting questions reliably	Flexible — a record can gain new fields without a migration
Weakness	Schema changes require migrations; you decide the shape up front	The same field can mean different things in different documents; reconciliation is harder
When it wins	Counting, 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 — exactly what relational databases are for. 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. Three tables, each owning one kind of fact:

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

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 ON requests(status);
CREATE INDEX idx_requests_agency_status ON requests(agency, status);

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 (status=?)   <-- jumps to the rows

SCAN versus SEARCH — the difference between a dashboard that loads in a blink and one that takes forty seconds. A forty-second dashboard is how you get a stakeholder who exports to Excel and stops trusting your numbers. Performance is a trust issue, not just a technical one.

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 time the query before and after. It needs nothing but Python 3 and the standard library.

import json
import sqlite3
import time
import urllib.request
from datetime import datetime

API = "https://data.cityofnewyork.us/resource/erm2-nwe9.json?$limit=5000"

REQUIRED = ["unique_key", "created_date", "agency", "complaint_type", "status"]

def validate_record(rec):
    """Return a list of problems; empty means the record is clean."""
    problems = []
    for field in REQUIRED:
        if not rec.get(field):
            problems.append(f"missing {field}")
    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")
    return problems

# 1. Fetch from the live API (free, no key)
with urllib.request.urlopen(API, timeout=30) as resp:
    records = json.loads(resp.read().decode("utf-8"))
print(f"fetched {len(records)} records")

# 2. Validate; quarantine the bad ones instead of dropping them silently
good, quarantined = [], []
for rec in records:
    problems = validate_record(rec)
    (good if not problems else quarantined).append((rec, problems))
print(f"clean: {len(good)}, quarantined: {len(quarantined)}")
for rec, problems in quarantined[:5]:
    print("  QUARANTINE", rec.get("unique_key"), problems)

# 3. Store in SQLite with the relational schema
db = sqlite3.connect("cityops.db")
db.execute("DROP TABLE IF EXISTS requests")
db.execute("""CREATE TABLE requests (
  unique_key TEXT PRIMARY KEY,
  created_date TEXT NOT NULL,
  closed_date TEXT,
  agency TEXT NOT NULL,
  complaint_type TEXT NOT NULL,
  descriptor TEXT,
  incident_zip TEXT,
  borough TEXT,
  status TEXT NOT NULL,
  latitude REAL,
  longitude REAL)""")
for rec, _ in good:
    db.execute(
        "INSERT OR IGNORE INTO requests VALUES (?,?,?,?,?,?,?,?,?,?,?)",
        (rec.get("unique_key"), rec.get("created_date"), rec.get("closed_date"),
         rec.get("agency"), rec.get("complaint_type"), rec.get("descriptor"),
         rec.get("incident_zip"), rec.get("borough"), rec.get("status"),
         float(rec["latitude"]) if rec.get("latitude") else None,
         float(rec["longitude"]) if rec.get("longitude") else None))
db.commit()

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

# 4. Time the query WITHOUT the index
t0 = time.perf_counter()
db.execute(QUERY).fetchall()
no_index = time.perf_counter() - t0
print(f"without index: {no_index:.3f}s")

# 5. Add the index, time it again
db.execute("CREATE INDEX idx_requests_status ON requests(status)")
t0 = time.perf_counter()
rows = db.execute(QUERY).fetchall()
with_index = time.perf_counter() - t0
print(f"with index:    {with_index:.3f}s")
for agency, count in rows[:5]:
    print(f"  {agency}: {count} open")
db.close()

Three habits in this script, not just code. Quarantine, don't silently drop: bad records keep their reasons attached, printed for inspection — Tom's deleted "corrupt" rows are what happens when a pipeline drops data without a trace. INSERT OR IGNORE makes re-runs idempotent: fetch twice, count once. The before/after timing turns "indexes are faster" from folklore into a number you measured. Scale $limit up and watch the gap grow.

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. Time the query, restore the index, run again. At a hundred thousand rows the difference stops being academic — and the 311 feed holds millions. 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. 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. Three things happened to your spreadsheet on the way from the city's system: a few complaint descriptions contained commas that shifted their rows sideways, so some closed requests got counted as open; the city's export added a column since last month, which moved your status filter onto the wrong column; and a handful of rows with unusual characters were deleted during cleanup even though they were real requests. 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.

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 requests table, the primary key on unique_key, and the index on status. From now on, every part of CityOps — the dashboard, Tom's future export button, the Stage 2 pipeline — reads from these tables. 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 `requests` where status = 'Open'.
  (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.

### Validation rules (enforced by ingest.py, quarantine on failure)
- unique_key, created_date, agency, complaint_type, status must be present.
- 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.)

### Reconciliation
- Every run logs: fetched, clean, quarantined, inserted.
- fetched must equal clean + quarantined. If not, the run is flagged.

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.

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.