Skip to main content

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.

In short

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.

ArgumentWhat it does
schema_modeMatches CSV columns by header name (by_name, the default), or rejects files that differ (strict)
path_patternTurns parts of the file path (vendor, date) into columns
columnsMaps each sender's column names and types to one canonical schema
column_groupsFolds many sparse columns into one MAP column
row_index, row_errorsAdds 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_idamounttimestamp
cust_101120.002026-10-19T09:00:00Z
cust_10285.502026-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:

vendoryearmonthorder_idamount
vendor_a202610ord_501120.00
vendor_b202610ord_50285.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_idamountvendor
tx_1001150.00vendor_a
tx_100245.00vendor_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 glob or regex.
  • Values are cast to the group's type. Empty (null) entries are left out, unless you set keep_nulls: true.
  • strip_prefix is 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_idnameattributes (MAP<STRING, STRING>)
item_1T-Shirt{"color": "blue", "size": "large"}
item_2Coffee 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_index adds each row's number within its file. row_index: row_id counts from 0; {name: row_id, start: 1} counts from 1. row_index: true names the column _row_index.
  • row_errors: true adds _row_errors: the names of the columns whose value did not fit their type, or that are empty while marked required: 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_filerow_id_row_errors
transactions.csv2["amount"]

More read arguments​

ArgumentWhat it doesDefault
vocabularyThe allowed canonical columns and types, as a list of {name, type}. A columns rule outside it is an errornone
on_unmapped_columnA file column no columns rule maps: ignore, warn (the file is marked drift) or reject the filewarn
on_mismatchA rejected file (unknown format, no matching schema, a missing required column): error fails the batch, skip lists it in _file_errorserror
inject_path_varsAdd the named groups of path_pattern as columnstrue

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:

ViewWhat it holds
_schema_reportOne row per file read: step, path, status (ok, drift or rejected), mapped, unmapped, missing_optional, header_hash
_file_errorsFiles that were rejected or could not be read: step, path, reason
_commitsThe 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​