Data ingestion at scale: a metadata-driven framework for hundreds of sources
With hundreds of sources, stop writing pipelines and build one framework. Describe each source as metadata: how to connect, what to pull, how often and where it lands. A small set of generic, tested loaders then does the work. Onboarding a source becomes a configuration change and a review, not a project. On a 300+ source platform, this took onboarding from weeks to days.
This guide is drawn from an Empes case study: a metadata-driven ingestion framework built for a global pharmaceutical company. The figures 300+ sources, 70+ TB and onboarding reduced from weeks to days are from that project. Every other number here is hypothetical and labelled as such. The guide describes architecture, not regulatory or compliance advice.
Decide when a framework beats individual pipelines
A pipeline per source is the natural place to start. It is quick to write and easy to understand. It stops working when the number of sources grows faster than the team. Each pipeline brings its own scheduling, error handling, naming and quirks, and every fix has to be made in many places.
The client in the case study had more than 300 sources, including Veeva, SAP, Salesforce, Oracle and PubMed, and more than 70 TB of data. Manual ingestion could not keep up, and the data was siloed and inconsistent. Instead of building 300 individual connectors, we built one ingestion framework driven by metadata.
| Signal | Individual pipelines | Metadata-driven framework |
|---|---|---|
| Number of sources | A handful, rarely changing | Dozens to hundreds, growing |
| Connection types | Every source is different | A few types cover most sources |
| Onboarding a source | Write, test and deploy new code | Add a reviewed configuration record |
| A fix to error handling | Repeated in every pipeline | Made once, applies everywhere |
| Governance and audit | Varies by pipeline | Same run log and lineage for every source |
The economics are simple to sketch. The numbers below are hypothetical:
individual pipelines: total effort = N × effort per pipeline
framework: total effort = framework build + N × effort per source record
hypothetical, N = 300:
pipelines: 300 × 5 days = 1,500 days
framework: 60 days + 300 × 1 day = 360 days
Read the diagram as text
- Individual pipelines: 300 × 5 days: 1,500 days
- Framework: 60 days + 300 × 1 day: 360 days
What matters is not the exact figures but the shape of the curve. The framework has a fixed cost and a small marginal cost, while pipelines have no fixed cost and a large marginal cost. Past a certain number of sources, the framework wins and keeps winning.
Describe each source as metadata
The core of the framework is a source record: a small, declarative description of everything the loaders need to know. Store it as a file in version control, one per source.
# sources/crm_accounts.yaml (illustrative)
source: crm_accounts
system: crm
owner: commercial-data-team
connector: api
connection:
base_url_secret: crm/base_url
auth_secret: crm/token
extract:
object: accounts
mode: incremental
cursor_field: last_modified
schedule: "0 2 * * *"
landing:
layer: raw
format: parquet
contract:
primary_key: [account_id]
required: [account_id, country]
The fields fall into a few groups. A schema for the record is enforced in CI, so a malformed source never reaches production.
| Field group | Answers | Example values |
|---|---|---|
| Identity | What is it, and who owns it? | source name, system, owner team |
| Connector | How do we reach it? | api, sftp, jdbc; secret references, never secrets |
| Extract | What do we take, and how much? | object or table, full or incremental, cursor field |
| Schedule | How often? | a cron expression or an upstream dependency |
| Landing | Where does it go? | layer, format, partitioning |
| Contract | What must be true of the data? | primary key, required columns, expected types |
Keep secrets out of the metadata
Source records reference secrets by name. The secrets themselves live in a secrets manager. This lets you review records openly and copy them between environments without exposing credentials.
Standardise the entry points
Hundreds of sources do not mean hundreds of connection types. In the case study, all sources came through three entry points, API, SFTP and JDBC, and all of them landed in an AWS S3 data lake. Each entry point is one generic loader, written and tested once, that reads a source record and does the work.
| Loader | Typical sources | What it handles for every source |
|---|---|---|
| API | SaaS platforms such as CRM and life-sciences CRM systems, public research APIs | Authentication, pagination, rate limits, incremental cursors, retries |
| SFTP | File drops from partners and vendors | File discovery, checksums, archiving processed files, late or duplicate files |
| JDBC | Operational databases and ERP systems | Connection pooling, incremental queries, type mapping, query timeouts |
Every loader follows the same contract, whatever the source. It writes to the agreed landing path, records the run, and fails loudly. This contract is what makes it possible to add a fourth loader later without changing anything downstream.
Layer the lake so lineage is obvious
The case study used a multi-tier S3 structure with three layers: raw, curated and integrated. The layers make lineage visible in the storage itself. Anyone can see where a record came from and what was done to it.
| Layer | Contains | Rules |
|---|---|---|
| Raw | Exactly what the source sent, partitioned by load | Append-only. Never edited. Kept for replay and audit. |
| Curated | One cleaned, typed version of each source | Deduplicated, standard types and names, contract checks passed |
| Integrated | Data joined across sources into business entities | Modelled for consumption; one definition per entity and metric |
Read the diagram as text
- Raw: exactly what the source sent; append-only, never edited, kept for replay and audit
- Curated: one cleaned, typed, deduplicated version of each source; contract checks passed
- Integrated: business entities joined across sources; one definition per entity and metric
A predictable path convention lets tools and people find data without a catalogue lookup:
s3://LAKE/raw/SYSTEM/SOURCE/load_date=YYYY-MM-DD/RUN_ID/
s3://LAKE/curated/SYSTEM/SOURCE/
s3://LAKE/integrated/DOMAIN/ENTITY/
Because raw is append-only, you can rebuild curated and integrated data at any time from the raw copies. When a transformation bug is found, you fix the logic and replay, without asking any source for the history again.
Transform with version-controlled models
Once data lands, transformation is SQL. The case study used dbt Cloud for modular, testable SQL transformations inside Amazon Redshift. Each model is a file in version control, reviewed like any other code, with tests that run on every change.
curated.crm_accounts ← raw.crm.crm_accounts (typed, deduplicated)
curated.erp_customers ← raw.erp.erp_customers
integrated.customer ← curated.crm_accounts + curated.erp_customers
Generate the boring parts
The source record already knows the primary key, the required columns and the types. Generate the curated-layer models and their tests from it, rather than writing them by hand. Human effort then goes where it matters: the integrated models that encode business meaning.
Tests every source gets
| Test | Fails when |
|---|---|
| Primary key unique and not null | Duplicates or missing keys arrive |
| Required columns not null | The source drops or renames a field |
| Row count within range of the last load | A load is suspiciously empty or large |
| Freshness | The newest record is older than the schedule allows |
| Accepted values on coded fields | A source starts sending a new code nobody has mapped |
Orchestrate generically
With a single framework, there is a single workflow definition, run once per source with that source’s record as its input. The case study ran this as Argo Workflows on Kubernetes, which gives scalable, containerised processing: each run is an isolated container, and hundreds can run in parallel.
for each source record due now:
extract → land in raw (loader chosen by the connector field)
validate → contract checks (from the contract fields)
curate → generated dbt model
record → run log: source, run_id, rows in, rows out, status, duration
Read the diagram as text
- Source record due (schedule in the metadata)
- Extract and land in raw (API, SFTP or JDBC loader)
- Validate the contract. Otherwise: Fails: alert a person, stop this source
- Curate (generated dbt model)
- Record the run (rows in, rows out, status)
Keep scheduling in the metadata, not in the orchestrator’s configuration. Then the source record stays the single place to look when you need to know when and how a source loads.
Treat the platform as code
A framework that hundreds of sources depend on has to be reproducible. In the case study, all infrastructure was defined with AWS CDK, so environments could be recreated and every change was reviewed. GitLab handled code management and automated deployments, and the same pipeline validated source records, ran model tests and deployed.
| What | Lives in | Changed by |
|---|---|---|
| Infrastructure (buckets, clusters, warehouse, roles) | CDK code | Merge request, deployed by CI |
| Loaders and workflow templates | Application code | Merge request, deployed by CI |
| Source records | YAML in the same repository | Merge request, validated against the schema |
| Transformations and tests | dbt project | Merge request, tests run in CI |
Use AI where the work is repetitive and reviewed
The case study also used AI bots in operations: reviewing merge requests, supporting the release process and handling incidents proactively. These tasks suit an assistant: they are frequent and text-heavy, and a person approves the outcome. Let the assistant prepare and summarise. Let people decide.
Measure onboarding, not just volume
Terabytes and source counts describe the size of a platform, not how well it works. The number that shows whether the framework is doing its job is the time it takes to onboard a source. In the case study, the dedicated framework reduced source onboarding from weeks to days.
Track a small set of measures from the run log and the merge-request history. The targets below are hypothetical. Set your own from your baseline.
| Measure | How to calculate it | Hypothetical target |
|---|---|---|
| Time to onboard a source | Days from request to first successful production load | Days, not weeks |
| Share of sources on standard loaders | Sources without a custom loader ÷ all sources | Rising over time |
| Failed runs per week | Failed runs in the run log | Falling, and each one alerted |
| Freshness breaches | Sources whose data is older than their schedule allows | Close to zero |
| Downstream trust | Teams using the integrated layer as their source | Growing |
The last measure is the one the framework exists for. In the case study, the platform became a single source of truth for global analytics, used by teams in Tableau, ThoughtSpot and Google Sheets.
Frequently asked questions
How many sources justify building a framework?
There is no fixed threshold. Watch for the signals instead: new sources arrive every month, most of them share a handful of connection types, and the same fixes are being made in many pipelines. If you have a few sources that rarely change, individual pipelines are simpler. With hundreds, as in the case study this guide draws on, a framework is the only approach that scales.
Does a metadata-driven framework handle sources that do not fit?
Yes, through an escape hatch. Let a source record point to a custom loader when no standard one fits, and keep that loader to the same contract: same landing path, same run log, same tests. If more and more sources need the escape hatch, it is a sign that a new standard loader is missing.
Where should the metadata live?
In version control, next to the code, as plain files such as YAML. A change to a source is then a reviewed merge request with a history, and the same CI pipeline that tests the code can validate every source record against a schema.
What tools does this need?
The pattern does not depend on a tool. The case study used AWS S3 for the lake, Amazon Redshift and dbt for transformation, Argo Workflows on Kubernetes for orchestration, AWS CDK for infrastructure and GitLab for CI/CD. The same layers can be built on other clouds and other orchestrators.
How do you keep a framework like this auditable in a regulated industry?
Keep every raw delivery unchanged, record every run with its source, time and row counts, keep the source definitions and transformations in version control, and deploy only through CI. Together these let you answer where a number came from and what changed when. Your own compliance team decides what is required; this guide describes architecture, not regulatory advice.
Where does AI help in a data platform?
In operations more than in the pipelines themselves. In the case study, AI bots supported merge-request review, the release process and proactive incident handling. These are repetitive, text-heavy tasks with a human approving the result, which is where an assistant reduces load without taking control of the data.
Where to start
List your current sources in a spreadsheet with five columns: system, connection type, frequency, owner and current pipeline. Group the rows by connection type. If a few types cover most of the list, you already know which standard loaders to build first, and the spreadsheet is the first draft of your metadata.