Nicolás Forero

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%.

0%25%50%75%100%2019: p10 66%, p25 84%, median 96%, p75 100%, p90 100% · 24 municipalities201924 municipalities2020: p10 61%, p25 80%, median 90%, p75 94%, p90 97% · 24 municipalities202024 municipalities2021: p10 59%, p25 73%, median 82%, p75 90%, p90 95% · 129 municipalities2021129 municipalities2022: p10 63%, p25 72%, median 84%, p75 92%, p90 96% · 143 municipalities2022143 municipalities2023: p10 51%, p25 69%, median 82%, p75 92%, p90 96% · 197 municipalities2023197 municipalities2024: p10 62%, p25 72%, median 84%, p75 91%, p90 95% · 296 municipalities2024296 municipalities2025: p10 59%, p25 69%, median 80%, p75 89%, p90 94% · 394 municipalities2025394 municipalities2026: p10 64%, p25 73%, median 82%, p75 90%, p90 95% · 409 municipalities2026*409 municipalities
Share of direct awards among mayors’ offices with at least 30 contracts in the year. Dot: median. Bar: 25th to 75th percentile. Line: 10th to 90th. *Current year, with data through November 14, 2026. Source: SECOP II, datos.gov.co.
YearMunicipalitiesP25MedianP75
20192484%96%100%
20202480%90%94%
202112973%82%90%
202214372%84%92%
202319769%82%92%
202429672%84%91%
202539469%80%89%
2026*40973%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.

← Back to writing