From 14 Databases to a Public API: Open Data on Azure Synapse and API Management

Last updated: October 2026

Public agencies often hold data that researchers, journalists and developers want, and the obstacle is rarely volume. It is that the data lives in many systems, built at different times by different people, and nobody can produce one authoritative version of it.

This article describes the platform we built for a state transportation agency, covered in our government open data case study. The agency held about 380 million rows of traffic, accident, maintenance and bridge inspection data in 14 databases across four technology stacks, answered 15 to 20 FOIA requests a month in three to six weeks each, and had been cited by a state audit for poor data accessibility. Project details come from that engagement. The code is a simplified illustration written for this article.

Profiling is the real project

The sources were Oracle 12c for traffic management, SQL Server 2016 for accident reporting, Microsoft Access for bridge inspections, and CSV files on an FTP server set up in 2014. We budgeted two weeks for profiling and spent three. The Access databases alone held 47 tables, about 30% of which were duplicates with slightly different schemas that someone had created years earlier and never removed.

Working out which tables were canonical and which were abandoned copies decided everything downstream, so we recommend treating profiling as a deliverable in its own right on any public-sector consolidation, with time to ask the people who have maintained the data for years.

One pipeline per source

Azure Data Factory handled ingestion with 14 separate pipelines, one per source. Oracle and SQL Server were straightforward. Two sources needed more care.

• Access databases on a network share. ADF's Microsoft Access connector runs only on a self-hosted integration runtime, and the Access ODBC driver (the 2016 version, 16.00.5378.1000 or later) must be installed on the integration runtime machine. Setting it up took half a day and avoided building a separate ingestion path.
• CSV files whose format had changed three times in ten years, with no record of which format applied to which files. We identified each file's format from its column count and header pattern, then parsed it with the matching layout.

import csv

LAYOUTS = {
    # header signature -> layout name (illustrative signatures)
    ("COUNT_ID", "ROUTE", "MILEPOST", "AADT", "YEAR"): "v2014",
    ("STATION", "ROUTE", "MILEPOST", "COUNT_DATE", "VOLUME"): "v2017",
    ("STATION_ID", "ROUTE", "MP", "COUNT_DATE", "VOLUME", "DIRECTION"): "v2020",
}

def detect_layout(path):
    with open(path, newline="") as f:
        header = tuple(h.strip().upper() for h in next(csv.reader(f)))
    layout = LAYOUTS.get(header)
    if layout is None:
        raise ValueError(f"Unknown layout in {path}: {len(header)} columns {header}")
    return layout

The important part of this function is the error for an unknown header. A file in a fourth, unrecorded format should stop and be looked at instead of being parsed with the nearest guess.

Validate everything, quarantine failures

The main investment was a data quality framework of 45 business rules that every record must pass before it reaches the serving layer. Accident records must have a valid county code, traffic counts cannot exceed the physical capacity of the road segment, and dates cannot be in the future. Records that fail land in a quarantine table for analyst review.

-- Each rule is a condition that a valid record must meet.
-- Rows with failed_rule IS NULL continue to the serving layer;
-- the rest are written to the quarantine table with the rule they failed.
SELECT a.*,
       CASE
           WHEN c.county_code IS NULL                     THEN 'INVALID_COUNTY'
           WHEN a.accident_date > CAST(GETDATE() AS DATE) THEN 'FUTURE_DATE'
           WHEN a.vehicles_involved < 0                    THEN 'NEGATIVE_VEHICLES'
       END AS failed_rule
FROM staging.accidents a
LEFT JOIN ref.counties c
       ON c.county_code = a.county_code;

On the first run, about 8% of historical records failed at least one rule, mostly because of bad county codes in older data. That was no surprise to the senior analysts, who had been correcting the same problems by hand during FOIA exports for years. Encoding the rules meant each correction happened once instead of every time someone needed the data.

Serving with Synapse serverless SQL

The curated data is served through Synapse serverless SQL pools, which query files in the data lake directly and charge only for the data each query processes, about $5 per TB in East US, rounded up to the nearest MB with a 10 MB minimum per query. Daily, weekly and monthly limits on data processed can be set with sp_set_data_processed_limit, which is worth doing for a pool that answers public requests.

-- Query curated Parquet files directly.
SELECT TOP 100 county_code, accident_date, severity
FROM OPENROWSET(
    BULK 'curated/accidents/*.parquet',
    DATA_SOURCE = 'opendata_lake',
    FORMAT = 'PARQUET'
) AS rows;

-- Materialise a frequently requested extract once (CETAS).
CREATE EXTERNAL TABLE extracts.accidents_by_county_year
WITH (
    LOCATION = 'extracts/accidents_by_county_year/',
    DATA_SOURCE = opendata_lake,
    FILE_FORMAT = parquet_format
)
AS
SELECT county_code, YEAR(accident_date) AS accident_year, COUNT(*) AS accidents
FROM OPENROWSET(BULK 'curated/accidents/*.parquet', DATA_SOURCE = 'opendata_lake', FORMAT = 'PARQUET') AS rows
GROUP BY county_code, YEAR(accident_date);

Microsoft recommends a UTF-8 collation such as Latin1_General_100_BIN2_UTF8 for Parquet text columns, which also enables segment pruning. Serverless pools support SELECT, CETAS and DDL for views and security, but not tables or DML, so all transformation happens before the data reaches the curated folders.

Publishing the API

For external users, the agency's data is published through Azure API Management over the serverless SQL pools. Researchers and developers query current data through an authenticated REST API instead of downloading 2 GB CSV files, and the open data portal's static downloads were replaced with live, API-backed endpoints.

API Management does not query databases itself; it fronts an API, so a small API layer sits between it and the data. If you build this today, note that Microsoft's Data API builder, which generates REST and GraphQL APIs from configuration, supports Synapse dedicated SQL pools but not serverless pools. For a serverless source, an Azure Functions layer is the documented route, and API Management can import a Function App as an API in any tier, adding keys, rate limits and a developer portal.

Within three months of launch, more than 200 researchers, journalists and app developers had registered for the API, far more than the handful we had expected in the first quarter.

Internal reporting, FOIA, and Fabric for new builds

Internally, analysts use Power BI dashboards connected to Synapse, with row-level security so that county staff see only their own county's data. FOIA requests now come from the same curated dataset: an analyst searches the platform, applies filters and exports, and a request that used to take weeks takes hours. FOIA response time fell from three to six weeks to about 48 hours, the 14 databases became one platform, and the state audit deficiency was cleared.

One note for anyone starting a similar project now. Microsoft's Synapse SQL documentation advises those new to data warehousing to start with Fabric Data Warehouse, and its Data Factory documentation points new users to Fabric Data Factory. The approach above, profiling, per-source pipelines, rule-based quarantine and an API layer, carries over unchanged; our comparison of Microsoft Fabric and Snowflake covers the platform choice.