Learn
Ingestion & profiling
A business spreadsheet is not a database table, and a model that has to guess where the tables are will guess wrong.
How a messy workbook becomes clean tables, and documents become cited sections, before any model is involved.
Stage 1 of 10. The first two stages, INGEST and PROFILE, read your files and measure them. No language model runs here: everything is code and SQL.
The problem it solves
Section titled “The problem it solves”A business spreadsheet is not a database table. The coffee roaster’s workbook has a title-less Orders sheet, and a Monthly sheet the owner keeps by hand, with two tables side by side on it. Dates like 03/04/2026 could mean March 4th or April 3rd. One cell is a thousand times too big. If you paste a sheet like this into a chatbot as text, it has to guess where the tables are and what the columns mean, and it guesses silently.
So before anything is analysed, the engine turns each file into something it can query exactly, and writes down every problem it finds as a data-quality item that the report shows you.
What happens on the example
Section titled “What happens on the example”Step through it: each step marks what it produced.
INGEST · PROFILE · the coffee-roaster run
In
- email-0412.eml447 bytes
- ops-notes.docx2,160 bytes
- price-list.pdf1,943 bytes
- sales-2026.xlsx587,340 bytes
INGEST · PROFILE
- 1 · Find the tablesEach sheet is scanned for blocks of cells with a header row; one sheet can hold several tables.
- 2 · Split the documentsPDF pages, Word headings and the email body become sections with their position.
- 3 · ProfileTypes, dates, keys and outliers are measured in SQL; problems become data-quality items.
Out
- T1 Orders!A1:H62616,260 rows
- T2 Monthly!A1:F76 rows
- T3 Monthly!H1:L76 rows
- document sections9email-0412.eml 1 · ops-notes.docx 4 · price-list.pdf 4
- date range2026-01-01 → 2026-06-30day grain
- Q1 date_format_ambiguitylowOrders · column date
- Q2 unit_errorhighMonthly · J3
- entities11Hotel Group LLC + OOO Hotel Group
From the job's DuckDB tables: files, sources, profile_tables, quality_issues, entities.
Tables, not text. The workbook is read as a grid of cells. The engine looks for rectangular blocks with a header row, so it found 3 tables in one file: Orders!A1:H6261 with 6,260 rows, and two tables on the Monthly sheet (Monthly!A1:F7 and Monthly!H1:L7). Each table is loaded into DuckDB, an embedded SQL database that lives in one file per job.
Columns get types. The Orders table’s columns were typed as: date (date), order_id (id), customer (category), channel (category), product (category), qty (integer), amount (currency), status (category). A type is what lets SQL add up amount and group by channel.
Documents become sections. The PDF is split by page, the Word file by heading, the email keeps its headers. That gave 9 sections, each with its position, so a quote can later point at exactly where it came from. The text is stored but not read by any model yet.
Profiling finds the mess. The profiler measured every column in SQL and raised 2 data-quality items:
| Item | Kind | Where | What the profiler wrote |
|---|---|---|---|
| Q1 | date_format_ambiguity | Orders · column date | 2290 dates could be day-first or month-first; read day-first because 3768 values only fit that order |
| Q2 | unit_error | Monthly · J3 | "Packaging" row 3 is about 1,000× the column median; likely a unit or decimal-point error |
Names are matched. Customers are written differently in different rows. The profiler found 11 entities (customers and products) and merged spellings that mean the same one: “Hotel Group LLC” (4 rows, match 0.99) and “OOO Hotel Group” (8 rows, match 1) are both OOO Hotel Group. That matters later, when an email from “Hotel Group” has to be connected to rows in the sheet.
The ideas behind it
Section titled “The ideas behind it”Structure before meaning
Section titled “Structure before meaning”A language model reads a spreadsheet pasted as text one token at a time. It has no reliable way to tell a header from a total row, or one table from the next. Detecting regions in the grid is ordinary code, and it is either right or visibly wrong. The rest of the pipeline relies on that: a number in the report is a SQL query over a known table and known rows.
Profiling
Section titled “Profiling”Profiling means measuring the data before trusting it: counting nulls and distinct values, finding the date range and the grain (day, month), looking for duplicate keys, and flagging values far from the rest. The ×1000 cell above is caught with a robust rule: a value about 1,000 times the column’s median is almost always a unit or decimal-point slip, not a real cost.
Ambiguous dates
Section titled “Ambiguous dates”03/04/2026 fits both day-first and month-first. The profiler counts the values that fit only one order (a day above 12 settles it) and reads the whole column that way, then reports how many dates were ambiguous. It never guesses quietly.
On real public data
Section titled “On real public data”The same checks run on three public datasets in the real-data evals. From the latest committed results (evals/results/2026-10-23-baseline-real.json, in evals/results/):
- Electronic card transactions, September 2026: Suppressed values: Data_value is blank for 2000-01 to 2002-09 (G3: 264 lines). Q1 missing_value: 264 rows (N403), SQL 264; cites rows 2, 3, 4… (pass)
- Online Retail II: Cancellations: invoice numbers starting with C (G1: 2,089 lines, 816 invoices). Q1 prefixed_id: 2089 rows (N120), SQL 2089; cites rows 58, 117, 118… (pass)
- Online Retail II: Negative quantities (G1: 2,431 lines). Q2 negative_quantity: 2431 rows (N121), SQL 2431; cites rows 58, 117, 118… (pass)
- Online Retail II: Missing customer IDs (G1: 21,051 lines). Q3 missing_id: 21051 rows (N122), SQL 21051; cites rows 156, 157, 158… (pass)
Try it
Section titled “Try it”aa metrics runs ingestion and profiling on your files and stops before any analysis. It needs no model:
bun run aa metrics examples/coffee-roaster --yes --currency USD --today 2026-07-01The first tutorial runs the whole job.
In the code
Section titled “In the code”packages/ingest/src/regions.ts: finds table regions in a sheet’s grid.packages/ingest/src/docs/pdf.ts,docx.tsandeml.ts: PDF pages, Word headings and emails into sections.packages/profile/src/profile.sql.ts: the profiling queries.packages/profile/src/dates.tsandrules.ts: date order and the data-quality rules.packages/profile/src/entities.ts: matching names to entities.- The spec:
docs/specs/ingestion-and-profiling.md.
Go deeper: Ingest and table detection and Profiling and data quality.
Further reading
Section titled “Further reading”- Abedjan, Golab, Naumann, Profiling relational data: a survey, The VLDB Journal, 2015. doi:10.1007/s00778-015-0389-y
- DuckDB documentation: the database every job runs in.
This stage computes no facts yet: its outputs are tables, sections and data-quality items. Facts start with the metric layer.