How do you build an AI data pipeline from outside sources?

01Why outside data is the real work

Many SaaS products depend on data they do not control: government sources, partner feeds, vendor APIs and public websites. Each arrives in its own format, on its own schedule, with its own idea of what an identifier is. Before any model can summarize, score or answer questions, those records have to agree with each other. This is part of our larger guide, AI integration for SaaS: add AI without a rebuild.

This work is easy to underrate. Google researchers who interviewed AI practitioners working on high-stakes systems found that data cascades, chains of problems that start with poor data and surface late, were close to universal in their sample. The pattern is familiar: the team rushes to the model, the outputs look plausible, and months later someone finds that two sources disagreed about which record was which.

An AI layer makes this worse, not better. A model fed inconsistent records will still write fluent answers. The inconsistency simply becomes harder to see.

02Start with source research and a reviewed schema

We do not start with a user interface or a prompt. We start with two documents the client can read and correct.

The first is a source map. For each source: what it contains, how it is accessed (API, bulk file or scraping), how often it changes, what identifiers it uses, what its terms allow and what is known to be missing or wrong.

The second is the data model: the real-world entities the product is about and how every source maps onto them. This is where the hard questions surface early. Is a person in one source the same person in another? Does a place mean a postal code, a legal boundary or a coordinate? Which source wins when two disagree?

Have the client's domain experts review the schema before the ETL is written. Changing a diagram is cheap. Changing a loaded database with links between millions of rows is not.

03Build the AI data pipeline in a fixed order

Once the model is agreed, we build the pipeline in steps that each have a check.

1. 01

   Land the raw data as it arrives.

   Keep an untouched copy of every source pull, so a mapping mistake can be fixed and replayed without fetching again.

2. 02

   Standardize each source on its own.

   Rename fields, cast types and normalize units and codes, one source at a time. Do not join yet.

3. 03

   Resolve entities.

   Link each source's records to the shared entities using the agreed keys, then fuzzy matching where keys are missing, then the manual mapping table.

4. 04

   Record provenance.

   Store the source, the date pulled and the rule that produced each value.

5. 05

   Validate.

   Check counts, required fields, value ranges and links between entities after every run.

6. 06

   Expose a clean layer to the product and the AI.

   Features read only from this layer, never from raw source tables.

Geography is a good example of step three. Mapping an address to the right area often means a point-in-polygon search against official boundaries, such as the U.S. Census Bureau's TIGER/Line files, which carry geographic entity codes and legal boundaries as of the start of each year, plus a geocoding fallback when an address is incomplete.

04Plan for sources that fight back

Some sources only exist as web pages. Some change their format without notice. Some limit how often you can pull. Plan for these from the first week.

• Scraped sources need monitoring. Alert when record counts drop, when required fields come back empty or when page structure changes.
• Start with a few sources. Map, extract and check a small set of high-value sources before adding the long tail. Each new source then follows a known pattern.
• Keep manual mappings on purpose. Edge cases such as special regions, merged entities or renamed records are often few in number. A reviewed table of exceptions is cheaper and more reliable than a clever rule that is wrong for them.
• Separate data from formulas. If scores or reports depend on formulas that the client's experts are still defining, keep those formulas outside the ingestion code so they can change without reloading data.

05Where the AI fits once the data is ready

With a clean, linked layer, the AI can do the parts it is good at. It can phrase answers, summarize long records, classify text and explain a score. It should not compute the numbers. Keep calculations in code over the clean data, and let the model read the results and describe them. That way every figure a user sees can be traced to a source and a rule.

This is also what lets a product cite its answers. If provenance is stored with the data, the AI can show where each fact came from. If it is not, citations become guesses. For more on why this matters in production, see why AI agents fail in production.

06What we see in production

For a U.S. civic transparency platform that scores elected representatives, we started with the data model and the data sources before the interface. Our business analyst studied the client's scoring method and wrote it up, and the team explored sources, drafted the entity relationship diagram and SQL for client review, and documented the ETL flow. Votes, bills, financial disclosures and campaign finance arrived in different shapes from different government sources, some only scrapable, and all had to be linked to the same representative and bill.

Address lookup was built on Census district geometry with a geocoding fallback. Special cases such as at-large states, the capital district and territories needed handling of their own, and 12 representatives needed a manual mapping. In a final round of fixes, score views gained explainability, data quality notes and source attribution, because trust depended on showing where every number came from.

On that civic transparency platform, our plan put LLM bill summaries after the core data milestones and AI assisted scoring in the last one, once votes, bills and finance were linked, and we moved the production database into the client's own database partway through the build.

For a trade and economic research company planning an intelligence portal, we pushed for a paid discovery phase before any build estimate. Some sources held around 1,600 indicators for almost 250 countries over 50 years, and each needed its own mapping and standardization. We proposed starting with a few selected sources, standardizing each programmatically, and agreeing a centralized data store and interchange structure before adding more.

For a nutrition planning app, the same principle applied at a smaller scale: calories, ingredient weights and daily values came from food data and code calculations, and the model only phrased the result.

Frequently asked questions