Raw Data, Staging, and Loading

There is a moment, somewhere between a spreadsheet cell and a database column, when the original is either preserved or lost forever. Everything else in this system can be redone. That moment cannot.

Section 1Where the data comes from

Before any of this is a database, it is people reading things.

For the Taiwan Politics database, the principal sources are Central Election Commission bulletins — official publications listing candidates, districts, parties, and results, election by election, going back decades. A research assistant works through them, often from scanned PDFs, sometimes from bulk downloads, and records what they find. Supplementary material comes from legislative gazettes, party publications, biographical reference works, and occasionally Wikipedia where it is used as a lead rather than as an authority.

Three characteristics of this input shape everything downstream.

It arrives in bulk, long after the fact. Nobody is entering a candidate into a form on the day they register. A researcher is reconstructing the 1998 municipal elections in 2024, which means every value is a transcription of a transcription, and the person who could clarify an ambiguity is generally unavailable.

Quality varies by source and by era. Recent CEC data is structured and machine-readable. Older bulletins are scanned images of typeset tables. A single load may span both.

The researcher knows things the spreadsheet cannot hold. An RA who spent an afternoon with a 1998 bulletin knows that one district's candidate list was printed twice with a correction, that two entries share a name, that one birth date was handwritten in the margin. A spreadsheet with four columns cannot carry any of that — which is one reason the audit layer exists downstream, and one reason a free-text notes column is worth more than it looks.

Section 2The staging contract

Between collection and loading sits staging: getting the researcher's working files into a shape the loader can consume.

The contract is deliberately minimal, and one word in it does the work:

Staged types must be loadable, not correct.

The difference decides whether this step helps or harms.

Staging may therefore do: renaming columns to match the target table; splitting one file into several when it holds more than one entity; removing blank rows and stray header repeats; ensuring a file has a consistent column set. It may not do: parsing ambiguous values, inferring missing ones, dropping rows that look wrong, or standardising a vocabulary.

The test is whether the operation is reversible from the staged file alone. Renaming a column is. Converting an era year is not.

Section 3One file, one table

The Taiwan staging directory holds one Excel file per canonical table, named for its target.

admin_district_parent_future.xlsx    family_records.xlsx
admin_district_transition_future.xlsx legislative_leader_positions.xlsx
admin_districts.xlsx                  legislative_leaders.xlsx
admin_districts_future.xlsx           legislative_session_positions.xlsx
candidates.xlsx                       legislative_sessions.xlsx
elected_positions.xlsx                officials.xlsx
election_districts.xlsx               online_profiles.xlsx
elections.xlsx                        parties.xlsx
...                                   ...

The mapping from file to table is explicit rather than inferred. This looks like unnecessary typing and is worth the keystrokes: an explicit dictionary makes a typo a loud failure instead of a silently skipped table.

DATA_DIR = DATA_IMPORTS_DIR / "twpolitics"
SCHEMA_NAME = "twpolitics"

FILES_TO_TABLES = {
    "officials.xlsx":            "officials",
    "parties.xlsx":              "parties",
    "elected_positions.xlsx":    "elected_positions",
    "admin_districts.xlsx":      "admin_districts",
    "elections.xlsx":            "elections",
    "election_districts.xlsx":   "election_districts",
    "legislative_sessions.xlsx": "legislative_sessions",
    "races.xlsx":                "races",
    "family_records.xlsx":       "family_records",
    "candidates.xlsx":           "candidates",
    "party_leaders.xlsx":        "party_leaders",
    "legislative_leaders.xlsx":  "legislative_leaders",
    # ... 22 entries in total
}

Load order matters. Referenced tables go first — parties, districts, positions, elections — then the tables that reference them. Officials before candidacies, because a candidacy points at an official.

Section 4What loading does

Loading is a narrow operation with one job: get every staged row into its canonical table, and record where it came from, in a single transaction.

It does exactly three things.

All three in one transaction. A canonical row without its raw capture is an orphan claim; a raw capture without its canonical row is data that never arrived. Either one alone is a bug, so neither is committed without the other.

What loading conspicuously does not do:

• It does not guess. 民國45年 goes into the database as 民國45年.
• It does not evaluate quality. A row with three empty columns loads exactly like a complete one. Whether that matters is a rule's job, and rules may not even exist yet.
• It does not create audit decisions. A row that happens to have a birth year has not thereby been approved. The architecture contract is explicit: do not create a synthetic violation, resolved violation, or de facto approval for a subject that satisfies a rule.
• It does not silently drop rows. See §7.

Section 5Immutable raw capture

This is the part of the chapter to remember.

When 林文德's row moves from officials.xlsx into twpolitics.officials, the canonical row holds what the schema can express. The immutable raw capture holds what the spreadsheet actually said — the complete original row, as JSON, with enough positional information to find it again in the source file.

{
  "source_file": "officials.xlsx",
  "sheet": "officials",
  "row_number": 3022,
  "raw": {
    "official_id": "3021",
    "official_name": "林文德",
    "official_birthyear": "民國45年",
    "official_sex": "M",
    "notes": "birthyear handwritten in margin of 1998 bulletin"
  }
}

Look at the notes value. The canonical officials table has no column for it. Under a load that maps only the columns the schema knows about, that observation — made by a researcher holding the actual bulletin — evaporates.

With raw capture, it survives. Two years later, when someone is adjudicating whether 林文德's birth year is reliable, it is still there and still says where the number came from.

Why not just keep the spreadsheets?

A fair question, since the files already exist. Three reasons the capture has to be in the database.

Linkage. A file in a folder cannot tell you which of its 50,000 rows produced canonical row 3021. The capture can, because the canonical row points at it.

Durability. Files get reorganised, renamed, re-exported, and edited in place. A row in a database with an integrity constraint does not.

Queryability. “Show me every official whose raw birth year contains 民國” is a query against the capture. Against a folder of spreadsheets it is an afternoon of scripting.

Section 6Verifying a load

A load that reports success has not necessarily worked. Four checks, every time.

The second one catches a failure mode worth naming, because it is common and silent: character-encoding damage. Chinese names read from a workbook with the wrong encoding do not error. They load as mojibake, and if nobody hashes the values, the first person to notice is a researcher six months later wondering why a name looks wrong.

The fourth check does double duty. Load-order mistakes show up as dangling references — but the numbers also become the baseline for the audit layer. “2,954 family records have no linked official” is a load-verification result on day one and a rule's population on day ninety.

Section 7Procedural failure

Some rows cannot be loaded. Not because they are low quality — Chapter 1 established that low quality is admissible — but because they cannot be represented: a duplicate primary key, a value that will not fit its column, a required identifier that is blank.

These are procedural failures, and the rule is:

A row that cannot be admitted is a reported failure, not a row that quietly vanishes.

Report it with its file, row number, and the exact reason. Then work out which of two situations you are in:

• The staged file is wrong. A researcher duplicated a row while editing. Fix the file and reload.
• The schema is wrong. The data contains something legitimate that the schema cannot express — two candidacies that the schema assumed could not coexist, a name longer than the column allows. Then the schema changes, via the migration classes from Chapter 2.

The second case is more common than it feels, and it is the most valuable thing a load teaches you. A procedural failure is frequently the data telling you that a modelling assumption was wrong. Suppressing it — with a try/except, a filtered row, a truncated value — discards that lesson and produces a database that silently disagrees with its sources.

Section 8A cautionary tale

The Taiwan staging directory contains twelve files this chapter has not mentioned: officials_q.xlsx, candidates_q.xlsx, party_leaders_q.xlsx, and nine more. The _q stands for quarantine.

They are the residue of an earlier architecture — the validate-then-admit design from Chapter 1. Rows that failed the admission bar were written to a parallel set of tables, identical in shape, to be dealt with later.

Here is what “later” produced:

Forty percent. For South Korea, the quarantine is not an exception queue; it is a substantial fraction of the dataset, sitting in twelve tables with no primary keys and no foreign keys — because the whole point of quarantine was that these rows could not satisfy such constraints.

No primary keys means no stable way to reference a quarantined row. No foreign keys means no way to relate it to anything. So the rows could not be worked on incrementally, which meant they were not worked on at all, which meant the queue only ever grew.

Taiwan's quarantine has since been resolved: all 5,473 rows were traced to matching lossless source records and matching canonical destinations, and 6,134 destination rows were created, including 661 provisional officials for people observed in a role but not yet matched to an existing record. The _q tables are now retirement candidates, awaiting a checksummed export before deletion. South Korea's has not been resolved, and doing so is a significant part of extending this system to that database.

The data is now in the database, verbatim, with its provenance recorded. In the next chapter we make that provenance precise: what a source actually is, what a locator is, and why evidence that a value came from somewhere must never be confused with evidence that a researcher approved it.