How do you build a data ingestion pipeline for many sources?

01Why outside data needs a product schema, not a copy

When a product depends on records from many outside websites, partners or vendors, the first instinct is to copy everything. Pull every field from every source, store it, and decide later. It feels safe because nothing is lost. This is part of our larger guide, AI integration for SaaS: add AI without a rebuild.

In practice it creates a mess the product cannot use. One source calls it a listing, another a unit, a third a property. One stores price as text with currency symbols, another as a number with no period. Some fields mean the same thing under different names; some share a name and mean different things. An AI feature reading this layer will produce fluent output on top of contradictions.

The fix is to decide what the product needs before deciding what to collect. The core schema is the product's view of the domain. Sources are mapped into it, not the other way round.

02Start the data ingestion pipeline with a small core schema

Most domains fit a simple shape: a parent record, its child records and their media. In housing that is a building, its units and their photos. In retail it might be a product, its variants and their images. In training it might be a course, its lessons and their files.

Keep the first version small. Include the fields the product will show, filter or compute on, and nothing else. Add a source field and a pulled-at date to every record. Every other field can wait until a feature needs it.

03Write field rules down

For each source, write a mapping document a product manager can read. For every target field: which source field feeds it, how the value is transformed, what happens when it is missing, and which source wins when two disagree.

This document does three jobs. It lets the client check the logic before anything is loaded. It lets a new engineer add a source without guessing. And it turns quality problems into specific questions: is the rule wrong, or is the source wrong?

Teams that use a transformation tool often formalize this as a staging layer. dbt's guidance, for example, recommends one staging model per source table, where columns are renamed and types are cast consistently, and joins and aggregations are kept out of that layer. The principle holds with any tool: standardize each source on its own before combining them.

04Deliver in stages and check each one

We deliver outside data in layers, and each layer is reviewed before the next is added. This is the order we follow.

1. 01

   Pull a small sample from each source.

   Map it into the core schema and review it with the client, field by field, against the live source.

2. 02

   Deliver the summary layer at full scale.

   Check counts by source and region, required fields and duplicates.

3. 03

   Add the detail layer.

   Check that every child record links to a parent and that no parent lost its children.

4. 04

   Attach media.

   Check that links resolve and that each item belongs to the right record.

5. 05

   Add reference data.

   Contacts, regions and codes, matched to existing records.

6. 06

   Build outbound formats last.

   Feeds and exports for partners are generated from the checked core.

7. 07

   Automate the checks.

   Turn every manual check into a rule that runs on each refresh and alerts when it fails.

The last step is what makes the pipeline last. Validation tools such as Great Expectations describe the idea well: expectations are verifiable assertions about data, run against each batch, much like unit tests for code. Counts within a range, required fields present, keys unique, prices positive. When a source changes its page layout, the check fails before a customer sees the damage.

05Plan for growth and refresh

Scope grows. A pilot covering one city becomes a national dataset. A handful of sources becomes dozens. Two decisions keep that growth manageable.

First, make every source a module that follows the same pattern: pull, map, check, load. Adding a source should mean writing a new mapping, not changing the pipeline.

Second, plan the refresh from the start. Outside data goes stale. Decide how often each layer is refreshed, how removed records are handled and how a changed record is detected. A product that shows last quarter's listings as current loses trust quickly, and an AI feature built on stale data repeats the mistake at scale.

06When AI comes in

Only once the core layer is trusted should AI features read from it: search in plain language, summaries of long records, matching between users and listings, or classification of free text. The model reads clean, linked records with known sources, and its answers can point back to them. For a wider view of how AI fits into an existing product, see adding AI agents to an existing SaaS product without rebuilding it.

07What we see in production

For an affordable rental housing venture, we started with data before any software. We collected property listing data from several large property management websites and delivered it as CSV files. We then extended it to deeper property detail pages, then to public housing authority contacts for all states, and then to a rental listings feed format the client could push to a third-party listing portal. The data scope grew with each delivery round, from one metro area to a far larger listing set, and the staged order meant each round built on data the client had already reviewed.

The data also shaped the schema: some properties had several bedroom, bathroom and square foot combinations, so in the affordable housing listings work we delivered unit models as data rather than one flat table per property, and when the scope grew to a far larger listing set, the full records followed within two weeks.

The listings website followed the same shape. Its data model has properties, units and media tables, with test data mapped into the database before the screens were finished. We built it in milestones: project setup and property management first, then search, discovery and communication, then a round of client revisions, with a full QA cycle before the client demo. There was no AI in the delivered scope. The data foundation is what any later AI feature, such as matching renters to units, would depend on.

Frequently asked questions