Multi-schema file ingestion
Files from different senders rarely agree on column order, names or units. A SparkJob's object-store read lines
them up for you, in one batch, before your SQL runs. This page covers the read arguments that do it.
Columns are matched by name, never by position. Add path_pattern to turn folders into columns, columns to map
each sender onto one schema, and row_errors to send bad rows to a quarantine table.
| Argument | What it does |
|---|---|
schema_mode | Matches CSV columns by header name (by_name, the default), or rejects files that differ (strict) |
path_pattern | Turns parts of the file path (vendor, date) into columns |
columns | Maps each sender's column names and types to one canonical schema |
column_groups | Folds many sparse columns into one MAP column |
row_index, row_errors | Adds row numbers and per-row errors, so you can quarantine bad rows |
1. Columns matched by header name
Spark's own CSV reader matches columns by position. If one file has amount,timestamp and another
timestamp,amount, the values end up in the wrong columns. StreamWright groups the files by header and combines the
groups by column name (unionByName, missing columns allowed). This is the default, schema_mode: by_name.
orders_east.csv
amount,customer_id,timestamp
120.00,cust_101,2026-10-19T09:00:00Z
orders_west.csv
timestamp,customer_id,amount
2026-10-19T09:15:00Z,cust_102,85.50
Result:
customer_id | amount | timestamp |
|---|---|---|
cust_101 | 120.00 | 2026-10-19T09:00:00Z |
cust_102 | 85.50 | 2026-10-19T09:15:00Z |
With schema_mode: strict, every file must have the same header. A file that differs is rejected: on_mismatch: error (the default) fails the batch, and on_mismatch: skip lists it in _file_errors and
reads the rest.
2. Columns from the file path (path_pattern)
path_pattern is a regular expression with named groups. Each group becomes a column (unless you set
inject_path_vars: false).
s3://lake/incoming/orders/vendor_a/2026/10/orders_01.csv
s3://lake/incoming/orders/vendor_b/2026/10/orders_02.parquet
steps:
- name: events
connection: lake_storage
operation:
service: object
method: read
arguments:
path: "incoming/orders/"
format: auto
path_pattern: "orders/(?P<vendor>[^/]+)/(?P<year>\\d{4})/(?P<month>\\d{2})/"
inject_path_vars: true
sql: "SELECT vendor, year, month, order_id, amount FROM input"
Result:
vendor | year | month | order_id | amount |
|---|---|---|---|---|
vendor_a | 2026 | 10 | ord_501 | 120.00 |
vendor_b | 2026 | 10 | ord_502 | 85.50 |
3. One schema for different senders (columns)
When senders use different names or units for the same data, map each one to a canonical schema. columns.by names
the column (here a path variable) that says which mapping a file uses.
Here vendor_a sends dollars and vendor_b sends cents:
vendor_a.csv
TxID,TotalUSD
tx_1001,150.00
vendor_b.csv
order_ref,price_cents
tx_1002,4500
steps:
- name: harmonized_data
connection: lake_storage
operation:
service: object
method: read
arguments:
path: "incoming/orders/"
format: auto
path_pattern: "orders/(?P<vendor>[^/]+)/"
inject_path_vars: true
columns:
by: vendor
vendor_a:
- header: "TxID"
canonical: "transaction_id"
type: "string"
- header: "TotalUSD"
canonical: "amount"
type: "double"
vendor_b:
- header: "order_ref"
canonical: "transaction_id"
type: "string"
- header: "price_cents"
canonical: "amount_cents"
type: "double"
sql: "SELECT transaction_id, COALESCE(amount, amount_cents / 100.0) AS amount, vendor FROM input"
Result:
transaction_id | amount | vendor |
|---|---|---|
tx_1001 | 150.00 | vendor_a |
tx_1002 | 45.00 | vendor_b |
4. Folding sparse columns (column_groups)
Some files carry many optional attribute columns (attr_color, attr_size, ...). Instead of one table column each,
fold them into a single MAP column.
- Each group takes exactly one
globorregex. - Values are cast to the group's
type. Empty (null) entries are left out, unless you setkeep_nulls: true. strip_prefixis optional; without it, the map keys are the full column names.
item_id,name,attr_color,attr_size
item_1,T-Shirt,blue,large
item_2,Coffee Mug,white,small
arguments:
path: "products/"
format: csv
column_groups:
- name: attributes
glob: "attr_*"
type: "string"
strip_prefix: "attr_" # Strips "attr_" so keys become "color", "size"
Result:
item_id | name | attributes (MAP<STRING, STRING>) |
|---|---|---|
item_1 | T-Shirt | {"color": "blue", "size": "large"} |
item_2 | Coffee Mug | {"color": "white", "size": "small"} |
5. Row numbers and bad rows (row_index, row_errors)
Use these to write good rows to your table and bad rows to a quarantine (dead-letter) table:
row_indexadds each row's number within its file.row_index: row_idcounts from 0;{name: row_id, start: 1}counts from 1.row_index: truenames the column_row_index.row_errors: trueadds_row_errors: the names of thecolumnswhose value did not fit theirtype, or that are empty while markedrequired: true. A row with no problem has an empty list.- Every row also has
_source_file, the file it came from.
transaction_id,amount
tx_01,100.00
tx_02,invalid_number
steps:
- name: validated_data
connection: lake_storage # provider: s3
operation:
service: object
method: read
arguments:
path: "incoming/transactions/"
format: csv
columns: # a plain list applies to every file
- { header: transaction_id, canonical: transaction_id, type: string }
- { header: amount, canonical: amount, type: double }
row_index: { name: row_id, start: 1 }
row_errors: true
outputs:
# Good rows -> the Delta Lake table
- name: valid_transactions
connection: lake_delta # provider: deltalake
operation:
service: table
method: write
arguments:
table_name: "transactions"
path: "s3://example-lake/delta/transactions"
mode: append
sql: "SELECT transaction_id, amount FROM validated_data WHERE size(_row_errors) = 0"
# Bad rows -> a quarantine table, for alerts and debugging
- name: quarantine_transactions
connection: lake_delta
operation:
service: table
method: write
arguments:
table_name: "transactions_quarantine"
path: "s3://example-lake/delta/transactions_quarantine"
mode: append
sql: "SELECT _source_file, row_id, _row_errors FROM validated_data WHERE size(_row_errors) > 0"
The quarantine table gets the bad row with its file, row number and the column that failed:
_source_file | row_id | _row_errors |
|---|---|---|
transactions.csv | 2 | ["amount"] |
More read arguments
| Argument | What it does | Default |
|---|---|---|
vocabulary | The allowed canonical columns and types, as a list of {name, type}. A columns rule outside it is an error | none |
on_unmapped_column | A file column no columns rule maps: ignore, warn (the file is marked drift) or reject the file | warn |
on_mismatch | A rejected file (unknown format, no matching schema, a missing required column): error fails the batch, skip lists it in _file_errors | error |
inject_path_vars | Add the named groups of path_pattern as columns | true |
A read takes at most runtime.limits.max_files_per_batch files per batch (default 2000) and
runtime.limits.read_groups distinct header layouts (default 200).
Built-in views
Each batch also registers these temporary views. Query them in an output's sql, for example to log schema drift
to a table:
| View | What it holds |
|---|---|
_schema_report | One row per file read: step, path, status (ok, drift or rejected), mapped, unmapped, missing_optional, header_hash |
_file_errors | Files that were rejected or could not be read: step, path, reason |
_commits | The outputs written so far in the batch: output, table_name, version, rows, batch_id |
A file is marked drift when it has unmapped columns, or when its header differs from the last one accepted for
the same columns mapping.
See also
- Spark jobs overview
readsteps on a DuckDBJob take most of the same file arguments.