Mixed-Format Bridge (CSV batch + JSON batch → one dataset)¶
What
Combine records that arrive in two different file formats — an old CSV batch and a new JSON batch of the same kind of data — into one unified table.
Synthetic / teaching example
The data here is constructed, not sourced.
legacy.csv (US dates MM/DD/YYYY, US-grouped amounts "1,250.50") and
modern.in.json (different key names, ISO dates). The problem class — a feed
that migrated from CSV to JSON while the old files still matter — is universal;
the rows are not.
Why interesting¶
A template reads one input format (CSV or JSON), so a mixed-format pile
can't be done in a single pass. The fix is one pass per format into the same
output_schema, then a fan-in pass to stack the normalised results — bridging
formats without leaving bxp or hand-writing a merge script.
flowchart TD
C["legacy.csv<br/><small>US MM/DD/YYYY · "1,250.50"</small>"] --> B1["bridge_csv<br/><small>→ id,date,amount</small>"]
J["modern.in.json<br/><small>order_id/ts/total · ISO</small>"] --> B2["bridge_json<br/><small>→ id,date,amount</small>"]
B1 --> U1["*.unified.csv"]
B2 --> U2["*.unified.csv"]
U1 --> CB["combine_unified<br/><small>combined_output</small>"]
U2 --> CB
CB --> R["1-combine_unified-combined.csvx"]Problem class documented in. (sources for the problem class — not for the data)
- Feed/API format migrations (CSV→JSON, v1→v2) are routine; historical batches in the old format have to be bridged to the new pipeline schema — a standard data-engineering backfill task.
The trick — one pass per format, then a fan-in¶
Three templates, run all at once with bxp-cli --config ./sample.json. Each
writes a real file, and each is pinned by a golden, so the three snippets below
are exactly what the run produces. Each pass uses a distinct file_pattern_in,
which is what stops the three from picking up one another's output.
Pass 1 · bridge_csv — the old CSV batch¶
Reads legacy.csv and normalises it to the target schema: US MM/DD/YYYY
becomes ISO, and the , thousands separator is stripped so the amount is a real
number.
Pass 2 · bridge_json — the new JSON batch¶
Reads modern.in.json, whose keys are named differently (order_id / ts /
total) and whose dates are already ISO. Different input, different work — but
the identical output_schema:
Put those two side by side and the bridge is done: two formats, one shape. A template reads one input format, so this is the step that could not have been a single pass.
Pass 3 · combine_unified — stack them¶
combined_output: true over *.unified.csv concatenates the two normalised
batches into one file, header written once. That is Final result below.
Final result¶
A CSV batch and a JSON batch, different dates and number styles, land as one consistent dataset:
Sample data¶
Run it with bxp-cli --config ./sample.json — the two inputs and the full
commented template:
{
// Teaching example — synthetic data. The same kind of records arrive in two
// formats: an old CSV batch (legacy.csv — US dates, US-grouped numbers) and a
// new JSON batch (modern.in.json — different key names, ISO dates). bxp reads
// one FORMAT per template, so each format gets its own pass into the SAME
// output_schema; a final combine pass fans the two normalised files into one
// dataset. Run the whole pipeline with: bxp-cli --config ./sample.json
//
// Glob safety: each template's file_pattern_in is chosen so the three passes
// never pick up each other's files (`legacy.csv` vs `.in.json` vs `.unified.csv`).
conversion_templates: {
// PASS 1 — CSV batch -> unified schema.
bridge_csv: {
data_dir: ".",
file_pattern_in: "legacy.csv",
file_pattern_out: "legacy.unified.csv",
input_schema: {
$id: "[id]",
$date: "DATE_CONVERT([date], 'MM/DD/YYYY', 'YYYY-MM-DD')",
$amount: "REPLACE([amount], ',', '') * 1"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: { id: "$id", date: "$date", amount: "$amount" }
},
// PASS 2 — JSON batch -> the IDENTICAL unified schema (different key names).
bridge_json: {
data_dir: ".",
file_type_in: "json",
file_pattern_in: ".in.json",
file_type_out: "csv",
file_pattern_out: ".unified.csv",
input_schema: {
$id: "[order_id]",
$date: "[ts]",
$amount: "[total] * 1"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: { id: "$id", date: "$date", amount: "$amount" }
},
// PASS 3 — fan the two normalised files into ONE dataset.
combine_unified: {
data_dir: ".",
file_pattern_in: ".unified.csv",
file_pattern_out: ".csvx",
combined_output: true,
input_schema: {
$id: "[id]",
$date: "[date]",
$amount: "[amount]"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: { id: "$id", date: "$date", amount: "$amount" }
}
}
}