Data Extraction

Introduction to Data Extraction

Data extraction is the platform's ingestion pipeline. It takes a structured file or payload arriving from outside, pulls named values out of it, transforms them, and writes them into a datastore.

Where to find it

Architect Panel → Data:

• Data Extraction — the extraction definitions, their field mappings and transformations
• Datastores — the destination the extracted data is written to

What it is for

Anywhere data arrives repeatedly in a machine-readable form and somebody would otherwise rekey it: a supplier's nightly stock file, a partner's CSV of referrals, an API posting orders, a monitoring system e-mailing an XML report.

What it is not

It does not read scanned documents. There is no optical character recognition here, no reading of invoices or identity documents from images, and no confidence scoring of recognised text.

This matters because the two are easy to confuse. Extraction expects data that is already structured — the value is in a known position or under a known path. If your source is a photograph of a delivery note, extraction is not the tool.

Three ways data arrives

• API — a caller posts the payload to the platform.
• E-mail — a message arrives at a monitored address, and its attachment is processed.
• File upload — somebody uploads the file, or a routine places it.

The same extraction definition serves all three. The input method is recorded on each run, so you can tell how a particular batch arrived.

How a run works

1. Data arrives by one of the three methods.
2. The extraction definition identifies the format and reads it.
3. Each mapped field is located by its path or column and pulled out.
4. Transformations run against the extracted values.
5. The result is written to the destination datastore.
6. The run is logged, and an optional callback runs afterwards.

Everything is logged

Each run writes a log entry recording the input method and what happened. This is the first place to look when a nightly feed produces nothing — it will usually tell you the file arrived and a path did not match, which is a different problem from the file never arriving.

The destination can be elsewhere

A definition names its destination explicitly, including the host and database. Extraction is therefore usable for loading data into somewhere other than the local datastore, though the common case is much simpler than that.

Worked example

A distributor receives a supplier's stock file as CSV each night by e-mail. One extraction definition maps four columns — supplier part code, description, free quantity and price — into a staging datastore, converting the price from pence to pounds on the way. Nobody opens the e-mail; the log confirms 2,400 rows processed at 03:12.

Recommendations

• Confirm your source is structured before choosing extraction.
• Start with one file and a handful of fields, then widen.
• Check the log after the first live run, and after any change at the sender's end.
• Load into a staging datastore first where the data is not fully trusted.

Setting Up an Extraction Rule

An extraction definition is the mapping between what arrives and where it goes. Most of the work is in the field mapping.

Where to find it

Architect Panel → Data:

• Data Extraction — definitions, field mappings and transformations
• Datastores — the destination datastore and its field names

What a definition holds

• Name — how you will recognise it later.
• Destination — the host, database and datastore to write into.
• Field mappings — one row per field, pairing a source path with a destination field.
• Transformations — ordered actions applied to extracted values.
• Callback — an optional function run after a successful extraction.

Paths depend on the format

For JSON and XML, a path walks the structure — order/customer/reference descends through the nesting. For CSV, spreadsheets and delimited files, the source is a column.

Get the path from a real file, not from the sender's documentation. Documentation describes the intended shape; the file tells you the actual one, and the two differ more often than anybody expects.

Map the minimum first

Start with the fields you cannot do without, prove the pipeline end to end, then add the rest. A definition with forty mappings that fails somewhere is much harder to diagnose than one with four that works.

Transformations

Transformations are ordered actions applied to a value after it is extracted — trimming, reformatting, converting units, substituting codes. They run in sequence, so order matters: trimming after a length check is not the same as trimming before it.

Keep transformation to shape rather than meaning. Reformatting a date is a transformation; deciding which of two suppliers a code belongs to is business logic, and it belongs in the destination datastore's own rules where it is visible.

A missing path is an error, not a blank

When a mapped path does not exist in the incoming data, the run reports it rather than quietly writing nothing. That is the behaviour you want: a silently empty field is how a feed runs for a month before anybody notices half the data is missing.

It also means a sender changing their format breaks the run visibly, which is the earliest you could possibly find out.

Testing

1. Get a genuine file from the sender, not a hand-made sample.
2. Run it and read the log.
3. Check the destination records field by field for the first file.
4. Run a second, different file — ideally one with a missing optional value.
5. Only then connect the live feed.

Ask for a file with edge cases

Empty optional fields, a record with unusual characters, the longest value they will ever send. Senders will provide one if asked, and it is far better than discovering the limits in production.

Worked example

A referral partner posts JSON to the API. The definition maps referral/id, referral/person/name, referral/person/dob and referral/urgency into a referrals datastore, with a transformation normalising the date format and another mapping the partner's urgency words onto the internal scale. Three test payloads are run, including one with no urgency supplied, before the partner is given the live endpoint.

Recommendations

• Build paths from a real file.
• Map a few fields, prove the pipeline, then widen.
• Keep transformations to formatting, not decisions.
• Test with an edge-case file before going live.

Supported File Formats

Extraction reads five formats. All of them are structured: the value must be locatable by a path or a column, not recognised from an image.

Where to find it

Architect Panel → Data:

• Data Extraction — where the format is chosen per definition

CSV

Comma-separated values, mapped by column. The most common format for scheduled feeds, and the easiest for a sender to produce.

One practical constraint: CSV must be supplied as a file, not as raw text in a payload. A sender posting CSV content in the body of an API call needs to send it as a file instead.

JSON

Mapped by path, descending through the structure with a separator — order/lines/reference. The natural choice for API integrations, and the format that handles nesting and optional values most gracefully.

Spreadsheets

XLS and XLSX, mapped by column like CSV. Useful when the sender is a person rather than a system, because it is what they already have.

Be aware that spreadsheets carry formatting, and formatting is where surprises live — a reference column that Excel has interpreted as a number will have lost its leading zeros before the file ever reaches you. Ask senders to format such columns as text.

XML

Mapped by path, like JSON. Common in established B2B integrations and in anything with a formal schema behind it.

DAT

Delimited, line-oriented files, with support for multiple record types in one file — the shape where a header line, several detail lines and a trailer line each have a different layout.

This is the format that appears when integrating with older systems, and the multi-record-type support is the reason it is worth having: those files cannot be treated as a simple table.

Choosing

You usually do not — the sender does. Where you can influence it, prefer JSON or XML for anything with nesting or optional fields, and CSV for flat tabular data. Both are unambiguous in a way spreadsheets are not.

Agree the format in writing

Not just the file type, but the field list, whether a header row is present, the date format, the character encoding, and what an empty value looks like. Every one of those has caused an integration to fail on its first real file, and all of them are cheap to agree in advance.

Encoding

Ask for UTF-8. A file in another encoding will usually load and then present as corrupted characters in names and addresses, which is the kind of problem that gets noticed by a customer rather than by a monitor.

Worked example

A local authority receives three feeds: a nightly CSV of housing benefit changes, a partner posting referrals as JSON, and a legacy finance system producing a DAT file with header, detail and trailer records. One extraction definition handles each, and the DAT definition maps the three record types separately.

Recommendations

• Prefer JSON or XML where the data has structure.
• Send CSV as a file, never as raw text in a payload.
• Agree encoding, dates and empty values in writing.
• Ask spreadsheet senders to format reference columns as text.