Public Procurement Data Platform

The Procurement Service received contract data as a large spreadsheet extract that was explored by hand. I designed and built, alone, the chain that turns this extract into data the directorate can trust: a Python ETL organised in bronze, silver and gold layers, a 35-rule quality catalogue with quarantine, a SQL Server star schema, a Power BI semantic model with row-level traceability, and DDI metadata published to an access-controlled catalogue.

The distinctive part is verifiability. Each execution ends with an integrity certificate: a Merkle tree over every row hash, recomputed from the source, the cleansed data and the warehouse. Any figure on the dashboard can be traced to its execution, file and spreadsheet line, and a third party can check it without access to the workstation.

The Financial Control Directorate checks public spending commitments, including procurement contracts. The Procurement Service needed a reliable view of contract volumes, award procedures, execution and data quality, aligned with the Public Procurement Code (ordinance no. 2019-679).

I do not operate the source information system: I work from the contracts extract the Service requests. The tool was built at the request of the head of the Procurement Service and is also the subject of my degree thesis.

• Manual, non-reproducible analysis of a spreadsheet of several thousand rows.
• Duplicates, inconsistent amounts and free-text fields, with no record of what had been corrected.
• No way to prove that a dashboard figure matched the source, line by line.
• Named individuals in the data, so any publication had to separate public, restricted and internal content.

• Reproducible: the same extract always gives the same warehouse, and a run is refused when a control fails.
• Traceable to the source line, from any visual of the report.
• Documented to an international standard (DDI) without hand-typed statistics.
• Confidential by construction: no personal name in any public output.
• Constraints: a single workstation, SQL Server in evaluation edition, Power BI without Premium, no data leaving the administration.

One source: the contracts and agreements extract, received as Excel workbooks. The pipeline enforces a 24-column input contract, reads every workbook of the raw folder on each run, ignores temporary files, and archives an exact copy of each source under a name that carries its SHA-256 hash. Sources are never moved or modified.

• Strict Kimball star schema: slicers and axes on dimensions only; the fact table holds keys, amounts, flags and contract attributes.
• Ten one-way relationships from facts to dimensions, no bidirectional filter.
• Budget-section codes mapped to unique acronyms by a reference table owned by the ETL and enforced with a UNIQUE constraint, replacing a keyword-guessing DAX column that gave the same acronym to 17 of 39 sections.
• Fiscal-year dimension used as the time table; automatic date tables disabled.
• For the analytical extensions, an audit dimension per model run, so every result carries its execution, certificate and software versions.

• Bronze: raw workbooks, read in a deterministic order, plus the hashed archive.
• Silver: normalised labels and amounts, durations parsed with the original kept, a row hash computed for every line, strict duplicates sent to quarantine.
• Gold: tables recreated and loaded in SQL Server, an execution log row per run, a consolidated CSV and Parquet export, and a quality report listing every flagged line.
• Final step: the integrity certificate, written to a registry and to a warehouse table read by the report.
• Every change to the Power BI project is applied by an idempotent Python script, run first on a copy, with a verifier that checks every visual field and filter against the model.

• A versioned catalogue of 35 rules, each with an identifier cited in the report next to the finding it produced.
• Six anomaly controls surfaced to users (amount and form anomalies), plus rules mapped to articles of the Procurement Code, such as amendments above 30 % of the contract amount.
• Explicit comparability: a year-on-year change is shown only if the previous year is exactly N-1, each year has at least 30 records, and the smaller volume is at least 25 % of the larger. Otherwise the report says 'no reliable N-1'.
• Execution rate shown with its base: below 10 % of the committed amount it is greyed, labelled indicative and excluded from rankings and alerts.
• Merkle certificate (RFC 6962): leaves are the row hashes, the root is recomputed from source, silver and gold; altering one franc in a copy is detected and located to its spreadsheet line.
• Business feedback loop: a stratified sample of flagged rows reviewed by the Service (confirmed, justified, to review), with per-rule precision and Wilson intervals.

• Named fields (agents, financial controllers) documented but never published: they appear only in restricted files and in a restricted-distribution annex.
• Four access profiles on the catalogue: visitor, standard account, authorised account and administrator, with downloads logged.
• Catalogue storage moved outside the web root, administrative API restricted to administrators, and a default role misconfiguration of the catalogue software found by testing and fixed.
• Secrets read from environment variables, never written in notebooks; all computation stays on the administration's workstation.

• Lineage across layers: the source, silver and quality-report line numbers follow different rules; they were derived from the deterministic read order and checked on sample rows before being shown.
• Warehouse identifiers are reassigned on each reload, so traceability relies on the row hash, which is stable as long as the line is unchanged.
• The metadata editor's own export silently drops variable statistics; the publication source was fixed to the notebook output and checked against the published record field by field.
• Power BI animations and labels behave differently on screen and in PDF export; alerts were designed to remain meaningful as static prints.

• A pipeline run in production on the workstation, refused automatically when a control fails.
• A multi-page Power BI report with printable A4 pages, a drill-through traceability sheet and a restricted annex.
• A DDI 2.5 record of 65 variables published to the internal catalogue, with an integrity certificate attached as a document.
• Six governance documents (privacy policy, terms of use, security policy, user guides), drafted for the directorate's validation.

• Alert thresholds and section acronyms are awaiting formal validation by the directorate.
• The extract lacks the contract awardee, its taxpayer number and stage dates: supplier concentration, splitting and delays cannot be analysed reliably yet.
• History before the current year is thin, so most year-on-year comparisons are deliberately suppressed.
• Single-workstation deployment with SQL Server in evaluation edition: a supported edition and a shared environment are prerequisites for wider use.

• Extend the input contract with awardee, taxpayer number and stage dates, then add concentration and delay indicators.
• Move to a supported SQL Server edition with scheduled runs and a shared report workspace.
• Promote validated forensic checks (procedure above its ceiling, presumed splitting) into the rule catalogue.
• Feed reviewed decisions back into the risk score, then evaluate a supervised model.