Lakewright solutions / brief
Convert NACHA ACH files to queryable Apache Iceberg tables
NACHA ACH files — 94-character fixed-width bank records — become typed, queryable Iceberg tables with one scan-approve-load pass.
What is a NACHA file?
The fixed-width format U.S. banks use for ACH payment batches: file header (type 1), batch headers (5), entry details (6), addenda (7), control records (8/9). 94 characters per line, positions defined by the NACHA operating rules — no headers, no delimiters.
How are columns derived?
From the record-type layout: routing numbers, account (maskable), amount (implied decimal, converted), individual name, trace number. Account numbers are flagged sensitive at scan; plans default them to masked. Batch/file control totals are available for reconciliation checks downstream.
The steps
lakewright scan sftp-drop/payroll/ # NACHA detected, PII flagged lakewright plan sftp-drop/payroll/ --out plans lakewright approve plans/ach_payroll.plan.yaml lakewright load plans/ach_payroll.plan.yaml --rest $ICEBERG_CATALOG
Query from Snowflake, Databricks, Trino, or DuckDB — it's standard Iceberg. BAI2 balance reporting files follow the same pattern.
FAQ
The question that matters
"Which originating companies had return rates above one percent last quarter, and what did it cost us?"
Four originators cross the threshold, and the ordering by *rate* is nearly the inverse of the ordering by *dollars*.
The worst by percentage is a small-volume originator whose returns barely register in absolute terms. The one that matters is third by rate but first by value — a high-volume originator whose sub-two-percent return rate represents the largest share of returned funds in the quarter. The assistant returned both rankings and the returned value per originator, because the semantic view carries amount as a summed metric and knows the company name is a dimension.
Worth stating plainly to anyone reviewing this: the account numbers behind those originators were hashed before the data ever reached the warehouse, so the entire analysis ran without a single account number entering the analytical environment.
Want this run against your data?
We start with a read-only scan and an inventory report — no installation on your systems, nothing leaves your environment. Most engagements produce findings the team didn't know about in the first afternoon.