Public procurement · Data engineering
How municipalities buy: a daily SECOP II observatory
A pipeline downloads the Colombian state's electronic contracts every day, models them with dbt and publishes indicators by municipality. This is the first run, and why coverage matters more than the average.
November 16, 2026 · dbt · DuckDB · 2 min
How does each municipality buy, and how does it compare with its peers? To answer that I built a pipeline that downloads SECOP II contracts daily, models them and publishes aggregate indicators. This article documents the first full run, on November 16, 2026.
The pipeline
Ingestion walks the datos.gov.co API by signing year, using keyset pagination instead of offsets. The first run downloaded 5,716,934 contracts, the same 5,716,934 the API reported at the start. Later runs fetch only updated contracts and merge them by identifier.
datos.gov.co → ingestion (Python) → Parquet → dbt on DuckDB → published indicators
The dbt models keep one signed contract per row and compute the same indicators at any grain with a single macro. Twenty-five tests run on every execution: unique keys, shares between 0 and 1, and totals that reconcile across tables. That leaves 5,650,452 signed contracts from 5,652 entities; 1,054,694 belong to mayors’ offices.
The contractor’s ID number is never stored. It is replaced by a pseudonym used only to count distinct suppliers.
What it shows
In 2025, the median mayor’s office signed 80% of its contracts as direct awards. The middle half of municipalities runs from 69% to 89%.
| Year | Municipalities | P25 | Median | P75 |
|---|---|---|---|---|
| 2019 | 24 | 84% | 96% | 100% |
| 2020 | 24 | 80% | 90% | 94% |
| 2021 | 129 | 73% | 82% | 90% |
| 2022 | 143 | 72% | 84% | 92% |
| 2023 | 197 | 69% | 82% | 92% |
| 2024 | 296 | 72% | 84% | 91% |
| 2025 | 394 | 69% | 80% | 89% |
| 2026* | 409 | 73% | 82% | 90% |
The number that changes the reading
The column that matters most is the second one. In 2019 only 24 municipalities had at least 30 mayor’s office contracts in SECOP II; in 2025 there were 394. Active local entities went from 500 to 3,041 over the same period.
Each year’s median is computed over a different group of municipalities. Comparing 2019 with 2025 mixes a change in how contracts are awarded with a change in who reports.
What these indicators do not say
They are statistical signals to look closer. They prove nothing about any entity or person.
- They count contracts, not pesos. A service contract weighs the same as a public works project.
- Direct awards are legal and expected in many cases, especially for service contracts, which are the majority.
- Mayors’ offices are identified by entity name, and municipalities by department and city as text, not by DIVIPOLA code.
- This page shows a snapshot. The pipeline runs every day; the figures here come from the run noted above.
Next step
With this clean table comes the second project: measuring whether procurement changes when the government changes hands.