Skip to content
Embabel Worlds

5.8 tabular — a published file as a joinable source

A large part of the open-data estate publishes no API. It publishes a file: a CSV, a tab-delimited export named .csv, an XLSX with a provenance banner above the real header. A tabular producer makes that file a first-class join target — the realm declares WHERE the table is and WHICH column joins, and nothing else.

producers:
  - name: paymentTimesByAbn
    kind: tabular
    url: "https://example.test/register/{today}-report.xlsx"
    format: auto              # csv | tsv | xlsx | xml | auto (default: sniff extension, then bytes)
    sheet: "Standard report"  # xlsx only; omit for the first sheet
    headerRow: auto           # or a 1-based row number
    keyColumn: "ABN"
    keyMatch: digits          # exact (default) | ci | digits
    keyAs: abn                # the join's recordKeyField
    rowIdAs: recordKey        # OPTIONAL synthesized per-row id — see below
    userAgent: browser        # some publishers 403 a bare client
    fileCacheSeconds: 21600    # TABULAR ONLY — see the note below
    maxRows: 200000
    maxRowsPerKey: 500
    project:
      businessName: "Business Name"
      paidWithinTerms: "% paid within terms"

Guarantees

  • Nothing is downloaded until a query traverses the edge. Declaring a 200MB register costs nothing; a query that never reaches it never pays for it.

  • One copy per deployment, not per world or per key. The file is cached by resolved URL and revalidated conditionally, so an unchanged register is not re-transferred. A batch of anchor keys reads one file.

  • fileCacheSeconds is THIS producer’s, and an unknown field voids the whole file. It caches a downloaded document, so it exists only on tabular. A remote producer caches with cache: { kind: ttl, seconds: … } alone. Putting it on a remote producer is not ignored, and the cost is not confined to that one field: the file fails to parse, so every producer declared in it ceases to exist — not some of them, the whole file. It is reported as a problem on the OWNING REALM, naming the file and the parse error, and logged as a warning, because a file of producers vanishing is not a detail to leave sitting in a list somebody has to go and read.

    It used to be silent, and that is worth knowing if you are reading older material: the realm said problems: 0 while every producer in the failed file did not exist, and the only symptom was Unknown producer '<name>' in plan when a query finally needed one — surviving a refresh and a restart, because nothing was stale, the file had simply never parsed. Check each field against the producer’s own kind.

  • A banner above the header does not become the schema. With headerRow: auto (the default) the header is detected by column shape, so the “Generated on …” lines these exports carry are skipped rather than parsed as column names.

  • The same rows always yield the same records. Detection, matching and projection are deterministic; no model is involved anywhere in this producer.

  • keyMatch: digits reconciles the four ways an identifier is published — spaced, unspaced, as an integer, and as a float (12345678901.0). It is opt-in: exact never reconciles silently, so a realm chooses when identifier forms may be treated as equal.

  • A capped read says so. Hitting maxRows is reported as a truncation warning — the rows returned are a prefix of the file, never presented as all of it.

  • A publisher outage does not become a factual claim. If the download fails and a cached copy exists, the cached copy is served and the staleness reported. If none exists, the hop returns no rows with a diagnostic — never a silent empty that reads as “no such record”.

  • A keyColumn that is not a column of the file is reported, listing the columns that were found. Publishers rename columns; a realm that goes stale must fail visibly rather than return zero rows forever.

rowIdAs — for registers whose rows carry no identifier. A materialized record MUST carry the target type’s identity property; one that doesn’t is DROPPED, and the fetch REPORTS how many it dropped and which property they lacked. It did not always: a register with no usable id produced nothing at all while every fetch log said it matched, which reads exactly like an empty source.

Two of the three cases need no declaration at all. A type that declares NO identity property — a per-(petition, constituency) signature row, a per-(place, month) count, anything whose uniqueness is the combination rather than a field — is given a deterministic id minted from the record’s own values: identical rows still MERGE, different rows never collide, and re-fetching is idempotent. A type that DECLARES an identity and receives a record without it is a data error, and that record is dropped and counted.

rowIdAs is for the third case: a type that MUST declare an identity because queries key on it, fed by a register that has no per-row id to give (the NDIS compliance CSV’s Provider Number is blank on 98% of rows). rowIdAs: recordKey stamps a deterministic <normalized key>:<ordinal> id, and the type declares that property as its identity. It is honest about what it is: a ROW POSITION within one file version, not a source identity — stable enough for transient virtual rows, and not a key to store or compare across file versions.

Date tokens carry a FORMAT. Report URLs use the publisher’s own date format, not ISO: {today-90d|date:dd-MMM-yyyy} renders 03-May-2026. Without the format suffix a token is ISO ({today}, {today-7d}). A |-separated composite anchor key can safely fill multiple report parameters: {key1|date:dd-MMM-yyyy} and {key2|date:dd-MMM-yyyy} render the two ISO key parts in the publisher’s date format. Numbered parts are URL-encoded; malformed dates fail loudly.

Costs and limits

  • maxRows bounds the parse; maxRowsPerKey bounds how much one popular key may contribute. Both are honest caps, both are reported.

  • url accepts {today} / {today-Nd} for date-stamped filenames, and {key} or numbered composite parts {key1}..{key9} — any key token makes the producer one download per anchor, appropriate only for a genuine per-entity or per-window export.

  • Every value is a string unless the target type declares the column otherwise. A leading-zero identifier survives; arithmetic over an undeclared column is the query’s job.

  • An empty cell is absent, not an empty string — as LOAD CSV reads it. count(o.qty) counts the rows that state a quantity, and o.qty IS NULL finds the ones that do not.

  • A column the target type declares numeric is read as the cell means it. An exponent is part of the number (1.2E3 is 1200), a typographic minus (−5, U+2212) is a minus, and a cell that is ONLY a parenthesised amount is negative ((2.50) is -2.5; Box (15) is still 15). A cell stating no number (n/a) is left as written.

  • A cell holding several values is one string until the realm says otherwise. Published tables often store a one-to-many relation in one delimited cell — a person’s children as gershom_1302,eliezer_1114. A producer’s compute: turns it into a list, one Cypher expression over the record r:

    compute:
      childIds: "[c IN split(r.children, ',') WHERE trim(c) <> '' | trim(c)]"

    A join keyed on childIds then looks up each element (§5.1, a list-valued keyField), at every hop of a variable-length walk (§5.12). Keyed on the raw cell, it looks up the whole string as one key and finds nobody — with no error, because a key that matches no row is an ordinary answer.

  • Omitting keyColumn returns the whole table for every key. Legitimate for a small catalogue, wrong for anything large.

XML registers — format: xml + recordElement. Some registers publish structured XML rather than a table (the ABN Bulk Extract). Declaring format: xml requires recordElement, the element that delimits ONE record; the declaration is rejected at load without it. Columns are then slash-joined paths relative to that element, with @attr for attributes:

  - name: abrByAbn
    kind: tabular
    format: xml
    recordElement: ABR
    url: "https://example.test/public_split_1_10.zip"
    urls:
      - "https://example.test/public_split_11_20.zip"
    keyColumn: "ABN"
    keyMatch: digits
    project:
      abnStatus: "ABN@status"                                    # attribute of a child element
      legalName: "MainEntity/NonIndividualName/NonIndividualNameText"
      otherNames: "OtherEntity/NonIndividualName/NonIndividualNameText"
      lastUpdated: "@recordLastUpdatedDate"                      # attribute of the record element itself
  • A path that repeats within one record (trading names) yields ALL its values joined with | — no occurrence is silently dropped.
  • A path absent from a record is absent from that record, never blank-filled.
  • An xml file that is a zip container is read entry by entry (.xml members, in name order) as one logical file. The parse streams throughout: file size is bounded by maxRows, never by memory.
  • keyColumn is a path like any other; keyMatch applies unchanged.

urls — one register split across several files. A register published as multiple files (the ABN Bulk Extract is two ~500MB zips) is declared as ONE producer: url plus additional urls. Every anchor key is matched against every file and the results concatenated — so a key found only in the second file can never read as absent. Each file is fetched and cached independently, deployment-wide, and any URL may carry the usual tokens. Two producers over the halves would force every consumer to remember the union; forgetting it would silently halve the register, which is why the split lives in the producer, not in queries.

When NOT to use it. A file that changes on a feed cadence and is small and static enough to enumerate is still not reference data — but a source with a real API is better served by remote, which can push predicates down to the server instead of filtering a downloaded file.