Onboard a new data source by letting an agent draft the ETL

The Problem

Connecting a new data source to a platform is slow, manual work. Someone has to read an unfamiliar API, find the right endpoints, work out authentication and pagination, then map every field to the target schema. After that come the quiet traps: currency stored in cents where the schema implies units, timestamps in the wrong timezone, lookup tables that need clarifying, and records that look like sales but are really refunds or voids.

When a platform integrates many sources, this repeats for every one. Onboarding becomes a recurring engineering bottleneck, and it does not scale with the number of sources.

The obvious idea is to point a code-generating agent at the API and have it write the ETL. What we found: it works for the mechanical parts and fails on the parts that need judgement, unless a person stays in the loop.

What the Solution Does

A five-step agent pipeline that drafts a source integration, with a human checkpoint after each step.

1. Explore the API. Read the spec, find endpoints, resolve authentication, infer currency units and timezone conventions.
2. Map the fields. Produce a field-by-field mapping document from the source to the target schema.
3. Generate the ETL. Draft the loader code, including pagination, upsert, and datetime normalization.
4. Validate. Check syntax with a dry run, then reconcile the loaded data: receipt totals against line items, median prices, and timezone warnings.
5. Build the analytics layer. Assemble the target tables, enriched with the reference data the platform needs.

An engineer approves the output of each step before the next one runs. That checkpoint is the point of the design. It earns its place.

Where It Fits

This makes sense if you:

• Onboard new third-party source APIs on a recurring basis.
• Have an OpenAPI spec or clean sample exports for each source.
• Have a defined target schema for the data to land in.
• Want to compress the manual exploration and first-draft coding, with engineers still reviewing.

This is probably not the right time if you:

• Need a hands-off, fully automatic integration with no human review.
• Have no documented spec and no sample data for the source.
• Are integrating a single, stable source once, where manual work costs less than the setup.
• Need a proven, production-grade product today. This is still an early pattern.

Business Value

Less manual first-draft work. The agent handles the mechanical exploration and the first cut of the mapping and loader code. That is where a lot of onboarding time goes.

A faster first pass. In a prototype, getting from a spec to a draft mapping and ETL ran in tens of minutes per source, given a defined target schema. That is a best-case figure on documented inputs. The draft still needs review.

Engineers stay in control. Because a person approves each step, the agent’s mistakes surface at review, before they reach production data.

Clear scope. This is a genesis-stage pattern. Today its value is compressing the first draft and learning where an agent needs help. It does not replace an integration team.

How It Works

1. Explore the API

The agent reads the spec, identifies the endpoints that carry the data you want, and works out authentication, pagination, currency units, and timezone conventions. It writes what it found into a short document an engineer can check.

2. Map the fields

The agent drafts a field-by-field mapping from the source to the target schema. This is the step that most needs review: field names can be misleading, and the correct mapping often depends on how the source actually uses a field, beyond its declared type.

3. Generate the ETL

From the mapping, the agent drafts a loader: HTTP calls with pagination, upsert into the store, and datetime normalization. The draft is meant to be read and corrected before it ships.

4. Validate

A dry run checks that the code is syntactically sound. A reconciliation pass then checks the loaded data against itself: do receipt totals match their line items, are prices in a sane range, do timestamps land in the expected window.

5. Profile the data, then build

The hard lesson from the prototype is that reading the API schema is not enough. The agent has to profile the real data before it trusts its mapping: count distinct values, look for cross-field groupings, and check for negative or null values. A short set of queries over the sample data catches the data-model errors a schema alone hides.

Stack

An LLM agent (Claude) driven by structured skills, Python for the ETL, a database such as SQLite or your warehouse, and an HTTP client. The skill definitions and explicit per-step validation are what keep the agent grounded.

What You Need to Make This Work

Data. An OpenAPI spec or representative sample export for each source, plus a clearly defined target schema with field meanings written down.

Integrations. Access to the source API or its exports, and write access to the target store.

Hardware. None special.

Team. An engineer on your side to review each step. They also supply the business rules the agent cannot guess, such as id conventions and field semantics.

Implementation Roadmap

1. Prototype on one source (1-2 weeks)

Run the pipeline against one documented source and a defined target schema. Output: a draft mapping and ETL, plus a list of the gaps that need your rules.

2. Harden and add data profiling (2-4 weeks)

Add the data-profiling step, encode the source-specific rules the prototype surfaced, and review the reconciliation output against known-good figures.

3. Production scope (sized separately)

Move from sample exports to the real source API, add monitoring, and decide which steps stay manual. This is a separate scope, sized once the prototype shows what is reliable.

Keep in Mind

• This is an early pattern. It rests on a single prototype. There is no track record of production deployments yet. Treat the speed figures as best-case on clean inputs.
• Schema reading is not data understanding. Agents make confident data-model errors, such as treating a device as a location or counting refunds as sales. Profiling the real data is what catches them.
• The human checkpoint is required. The value comes from a faster first draft that a person reviews. The person stays.
• Field semantics and id rules have to come from you. No agent can guess a convention that was never written down. Where the rules are missing, the output will have visible gaps.

FAQ

Can the agent integrate a source fully on its own?

No, and that is the main finding. It drafts the mechanical parts well and makes judgement errors on data modeling. A person reviewing each step is part of the design.

What does it need to start?

A spec or sample export for the source and a defined target schema with field meanings. The clearer the target, the better the draft.

Is this a product we can buy?

Not yet. This is a genesis-stage pattern from an internal prototype. We can run it as an engagement and tell you what it does and does not handle.

How is this different from a managed ETL connector?

Managed connectors cover known sources with prebuilt mappings. This pattern targets unfamiliar or custom APIs where no connector exists, by drafting the mapping and code, with review.

Ready to Discuss?

If you onboard new source APIs often and want to compress the first-draft work, we can run this pattern against one of your sources. We will show you where it helps and where it still needs your engineers.

Discuss your project