Skip to content

Two-File Keyed JOIN (concat + pre_pass + LOOKUP)

View on GitHub

What

Enrich a fact table that carries only a foreign key (orders → customer_id) with the human details from a separate dimension table (customers → name, city) — a real relational JOIN across two sources.

Synthetic / teaching example

The data here is constructed, not sourced. joined_input.csv is the two tables already stacked into one file with a _type marker (customer rows + order rows). The problem class — facts keyed to a separate lookup table — is universal; the rows are not.

Why interesting

bxp reads one file per pass, so a cross-file join needs the two sources brought together first. Once they share a file with a row-type marker, a keyed join is just a pre_pass over the dimension rows plus a LOOKUP on the fact rows — no database, no JOIN SQL. This generalises the single-file real-world/gtfs-stops-selfjoin (a self-join) to two distinct sources.

flowchart TD
    IN["joined_input.csv<br/><small>customer + order rows, _type marker</small>"] --> PP["pre_pass<br/><small>_type = 'customer'</small>"]
    PP --> IDX[("index<br/><small>name / city by customer_id</small>")]
    IN --> ROWS["order rows<br/><small>_type = 'order'</small>"]
    IDX -.->|LOOKUP customer_id| ROWS
    ROWS --> OUT["enriched orders<br/><small>+ customer_name / customer_city</small>"]

Problem class documented in. (sources for the problem class — not for the data)

  • The star-schema fact/dimension split is the foundational pattern of every relational and analytics database (Kimball dimensional modelling); resolving a foreign key to its row is the single most common data-prep step.

The trick

(see inline comments in sample.json)

  1. Stack the two files with a _type marker. Here it ships ready-made; to produce it from two separate files inside bxp, see ../multi-stage-etl, which builds the same shape with combined_output.
  2. pre_pass over _type = 'customer' — index name/city by customer_id (the dimension side).
  3. LOOKUP([customer_id], …) on the order rows — resolve the foreign key. row_rules emits only _type = 'order'; the dimension rows were just the lookup source.

Final result

Each order gains its customer's name and city. An order whose key is not in the dimension (C-9) keeps empty details — unmatched rows stay visible, not silently dropped:

order_id,customer_id,customer_name,customer_city,amount
1001,C-1,Acme s.r.o.,Praha,1250.5
1002,C-2,Globex a.s.,Brno,980
1003,C-1,Acme s.r.o.,Praha,540
1004,C-9,,,75

Sample data

Run it with bxp-cli --config ./sample.json --template two_file_join — the stacked input and the full commented template:

{
  // Teaching example — synthetic data. The classic relational JOIN: a fact table
  // (orders) carries only a foreign key (customer_id); the human details
  // (name, city) live in a separate dimension table (customers). bxp processes
  // ONE file per pass, so the two sources are first stacked into one file with a
  // `_type` marker column — `customer` rows and `order` rows together. (Here the
  // combined file is shipped ready-made; for how to PRODUCE it from two separate
  // files inside bxp, see ../multi-stage-etl, which builds the same shape with
  // `combined_output`.)
  //
  // With both record types in one file, a pre_pass indexes the dimension rows
  // and the main pass enriches each fact row via LOOKUP — a real keyed join.
  conversion_templates: {
    two_file_join: {
      data_dir:          ".",
      file_pattern_in:   ".csv",
      file_pattern_out:  ".csvx",

      // PRE_PASS — scan the `customer` rows only and index name+city by
      // customer_id. This is the dimension side of the join.
      pre_pass: {
        when:   "[_type] = 'customer'",
        key:    "[customer_id]",
        values: {
          cust_name: "[name]",
          cust_city: "[city]"
        }
      },

      input_schema: {
        $order_id:    "[order_id]",
        $customer_id: "[customer_id]",
        $amount:      "[amount]",

        // THE JOIN — resolve the foreign key to the dimension's values. An order
        // whose customer_id is not in the dimension (C-9) gets "" — LOOKUP
        // returns empty for an unknown key, so unmatched rows are visible, not
        // dropped.
        $name: "LOOKUP([customer_id], 'cust_name')",
        $city: "LOOKUP([customer_id], 'cust_city')"
      },

      // Emit only the fact (order) rows; the dimension rows were just the lookup
      // source.
      row_rules: [ { when: "[_type] = 'order'", rows: [ {} ] } ],

      output_schema: {
        order_id:      "$order_id",
        customer_id:   "$customer_id",
        customer_name: "$name",
        customer_city: "$city",
        amount:        "$amount"
      }
    }
  }
}

_type,customer_id,name,city,order_id,amount
customer,C-1,Acme s.r.o.,Praha,,
customer,C-2,Globex a.s.,Brno,,
customer,C-3,Initech,Ostrava,,
order,C-1,,,1001,1250.50
order,C-2,,,1002,980.00
order,C-1,,,1003,540.00
order,C-9,,,1004,75.00

order_id,customer_id,customer_name,customer_city,amount
1001,C-1,Acme s.r.o.,Praha,1250.5
1002,C-2,Globex a.s.,Brno,980
1003,C-1,Acme s.r.o.,Praha,540
1004,C-9,,,75