Keep the page before you parse it: an auditable TED data pipeline

Public procurement notices are public, but they are not automatically clean, complete or stable rows. A page can contain multilingual fields, several possible dates, missing identifiers and later corrections. If ingestion keeps only the columns a dashboard needs today, a parser decision can quietly erase the evidence needed to review it tomorrow.

ProcureScope is my Python and PostgreSQL portfolio prototype for a narrower question: how can an analytical table stay useful without pretending that every source record fits its schema? Its current query looks for TED notices with publication dates in a requested interval, IT-related CPV codes beginning with 72*, and place of performance in Poland. It is a monitoring and analysis project, not a complete view of procurement, legal advice or a system for submitting bids.

Treat pagination as part of completeness

The client uses TED Search API v3 in iteration mode. Each request carries the query, selected fields, a bounded page size and, after the first response, the next iteration token. The code clamps page size to 250, retries selected connection, rate-limit and server errors, and remembers tokens so a repeated token cannot create an endless loop.

The pipeline also pays attention to an incomplete search. When a response says it timed out, the page is yielded and handled, then the client raises an error instead of letting the run finish as successful. The run ledger records failed. That distinction matters: “some records arrived” is not the same as “the requested result set completed.”

Preserve the response before interpreting its records

For each page the client yields, the ingestion path writes the original response bytes to Bronze before normalizing individual notices. It stores a sidecar manifest with the query, date window, run ID, page number, fetch time, notice count, iteration state and SHA-256 checksum. Object paths include the fetch date, run ID and page, so a later ingestion does not overwrite an earlier page.

That ordering creates a recovery point. If a field mapping proves too narrow, the raw response still shows what TED returned. The current version does not yet include a command to reprocess Bronze without querying TED again, so the archive is preserved evidence rather than a turnkey replay system. A future replay command should record the parser version and the new output run, making the transformation reproducible without rewriting the source page.

Normalize a useful view and keep the source beside it

The Silver row selects fields useful for analysis: publication and notice identifiers, version, title, buyer, country, deadline, CPV codes, performance locations, procedure identifier and selected award fields. The normalizer handles several TED field shapes and prefers Polish, then English, for language variants. For a list of available deadline values, it selects the earliest parseable date for the summary column.

Those are intentional projections, not claims that the summary captures every legal or commercial detail. The full notice object remains in source_payload, and the original API page remains in Bronze. Award values and currencies are kept as returned fields; the project does not add together estimated and awarded values or combine currencies into a misleading total.

Records with a supported normalization error, such as one missing both a notice identifier and publication number, go to rejected_records with a reason and source object. Other records are upserted into source_notice_versions. A rejection is visible and reviewable; it is not silently dropped or made to look like a valid analytical row.

Make a repeat pull idempotent and a source change distinguishable

The technical key hashes a source identity and a version. It prefers TED’s notice identifier, falls back to the publication number, and uses the notice version or publication date as the version component. PostgreSQL inserts a new key or updates the existing row, so fetching the same key again does not add another row.

The timestamps answer different questions. first_seen_at stays at the first observation, last_seen_at advances whenever that key is fetched again, and source_changed_at advances only when the hash of the canonical source object changes. A routine refresh therefore does not look like a correction.

There is an important boundary: source_notice_versions is an upserted record per technical key, not a complete append-only history of every body ever seen for that key. The run-partitioned Bronze pages preserve that source-level history. The database row helps analytics; the raw archive helps audit what each run received.

Give each run an accounting check

ingestion_runs records the query window, status, pages and record counts. After pagination completes, the pipeline checks that received = loaded + rejected. It marks a run successful only after that reconciliation. If pagination, storage or database processing raises, the run is closed as failed with the counts and an error message.

The Airflow DAG runs a daily batch with a rolling correction window and starts dbt build only after ingestion succeeds. The dbt project defines analytical models for IT opportunities, buyer activity, upcoming deadlines, award-field coverage and data coverage. Metabase includes SQL questions for assembling reports, but the repository does not automatically provision a finished dashboard.

What the repository evidence supports

I reviewed ProcureScope at commit 615b575. Its GitHub Actions quality job passed Ruff and Python bytecode compilation. That job does not query TED or start the Docker Compose services, and the repository has no end-to-end test for the ingestion pipeline. The available CI result therefore verifies source quality, not that a notice can be fetched, archived, normalized and reconciled through PostgreSQL, MinIO, Airflow, dbt and Metabase in one run.

The implementation can be inspected in the pipeline, TED client, normalizer and database upsert. The repository is public, so you can also inspect the project README and run it locally with the documented Docker Compose setup.

The next useful proof

The next step is a reproducible integration test against a local TED-response fixture and disposable PostgreSQL. It should cover a repeated page token, a timed-out result, a record without an identity, two pulls of the same notice version, and a changed source hash. Then a replay command can prove that stored Bronze input produces the same normalized output for a pinned parser version.

That would test the important promise of this design: preserve the source, make every row outcome explainable, and keep a partial fetch from looking complete. For a related example of isolating invalid input, read how Aurora quarantines and revalidates synthetic telemetry.

← All notes