Vintage Harmonisation (one source, format drifted over time)¶
What
Fold several vintages of the same source — a broker export whose column names, date format and number format changed over the years — into one consistent table.
Synthetic / teaching example
The data here is constructed, not sourced.
trades_2022_legacy.csv (Date/Ticker/Amount, ISO dates, plain numbers) and
trades_2024_current.csv (trade_date/symbol/value, DD.MM.YYYY,
"1 250,50"). The problem class — exports that quietly change shape
between versions — is universal; the rows are not.
Why interesting¶
Long-lived data is never one schema. A bank renames a
column, switches MM/DD to DD.MM, starts grouping thousands — and every old
file is now incompatible with every new one. Analysts keep a pile of
one-off cleanup scripts per era. One template, written against both layouts,
collapses the whole archive into a single normalised table.
Problem class documented in. (sources for the problem class — not for the data)
- Schema/format drift over time is the core motivation for "schema evolution" handling in every data-lake format (Parquet/Avro/Delta) and the reason ETL jobs carry per-vintage mapping tables.
The trick¶
See inline comments in sample.json:
A column absent from a file reads as "", so one template can target every
vintage's column name and pick whichever is present:
- date —
COALESCE([Date], DATE_CONVERT([trade_date], 'DD.MM.YYYY', …))(legacy is already ISO; convert only the current vintage). - ticker —
COALESCE([Ticker], [symbol])(a pure rename). - amount — the same
COALESCEpick, with the EU"1 250,50"normalised to a number. All three lines say "whichever vintage is present" the same way.
combined_output: true then stacks all vintages into one
1-vintage_harmonise-combined.csvx.
Final result¶
2022 and 2024 files, three different format quirks, become one clean table — ISO dates, canonical tickers, numeric amounts:
date,ticker,amount
2022-06-15,AAPL,1000.5
2022-07-01,MSFT,2500
2024-03-15,AAPL,1250.5
2024-04-02,NVDA,3980
Sample data¶
Run it with bxp-cli --config ./sample.json --template vintage_harmonise:
{
// Teaching example — synthetic data. The same source (a broker's trade export)
// changed format over time: the 2022 "legacy" vintage has columns
// Date/Ticker/Amount with ISO dates and plain numbers; the 2024 "current"
// vintage renamed them to trade_date/symbol/value with EU dates (DD.MM.YYYY)
// and space-grouped, comma-decimal numbers. You want every vintage in ONE
// consistent table.
//
// ONE template harmonises both. Because a column absent from a file reads as
// "", each output field picks whichever vintage's column is present and
// converts it to the canonical form. `combined_output: true` then stacks all
// vintages into a single `1-vintage_harmonise-combined.csvx`.
conversion_templates: {
vintage_harmonise: {
data_dir: ".",
file_pattern_in: ".csv",
file_pattern_out: ".csvx",
combined_output: true,
input_schema: {
// DATE — legacy is already ISO; current is DD.MM.YYYY. COALESCE takes
// whichever vintage is present, converting only the one that needs it,
// so both land as YYYY-MM-DD.
$date: "COALESCE([Date], DATE_CONVERT([trade_date], 'DD.MM.YYYY', 'YYYY-MM-DD'))",
// $ticker — just a rename across vintages; COALESCE takes the non-empty one.
$ticker: "COALESCE([Ticker], [symbol])",
// AMOUNT — legacy is plain (1000.50); current is EU '1 250,50'. Same
// COALESCE pick, with the EU form normalised (strip space, swap comma)
// before the single conversion to a number.
$amount: "COALESCE([Amount], REPLACE([value], ' ', '', ',', '.')) * 1"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: {
date: "$date",
ticker: "$ticker",
amount: "$amount"
}
}
}
}