Composite keys pattern

What is a composite key?

A composite key is a join key made of more than one column. No single column is unique on its own, but the combination is.

For example, a shipment is identified by its order and the warehouse it ships from. An order can ship from several warehouses, so order_id alone repeats in shipments. The pair (order_id, warehouse_id) is unique.

Composite key between order_lines and shipments

[HTML node] — order_lines: highlighted columns take part in the relationship
[HTML node] — shipments: highlighted columns take part in the relationship

order-lines → shipments (many_to_one order_id = order_id warehouse_id = warehouse_id)

Sample data:

order_lines

id	order_id	warehouse_id	qty
l1	o1	w1	2
l2	o1	w2	1
l3	o2	w1	4

shipments

order_id	warehouse_id	shipped_on	carrier
o1	w1	2025-01-03	DHL
o1	w2	2025-01-05	UPS
o2	w1	2025-01-04	DHL

The question: how many units did each carrier ship?

Solution: one `on {}` block per column

Declare a many_to_one relationship with one on {} block per column pair. A row matches only when every pair is equal.

Step 1: Define models

First, define the underlying models for both tables to expose their dimensions and measures. This ensures that the fields are correctly typed and ready to be joined.

order_lines.model.aml
Model order_lines {
  type: 'table'
  table_name: 'ecommerce.order_lines'
  data_source_name: 'your_datasource_name'

  dimension id {
    type: 'text'
    primary_key: true
  }

  dimension order_id { type: 'text' }
  dimension warehouse_id { type: 'text' }
  dimension qty { type: 'number' }

  measure total_qty {
    label: 'Total Quantity'
    type: 'number'
    definition: @aql sum(order_lines.qty);;
  }
}

shipments.model.aml
Model shipments {
  type: 'table'
  table_name: 'ecommerce.shipments'
  data_source_name: 'your_datasource_name'

  dimension order_id { type: 'text' }
  dimension warehouse_id { type: 'text' }
  dimension shipped_on { type: 'date' }
  dimension carrier { type: 'text' }
}

Step 2: Define the relationship

Next, define the relationship between the two models using the composite key columns. This tells Holistics how to join the tables when they are queried together.

order_line_shipment.relationship.aml
Relationship order_line_shipment {
  type: 'many_to_one'
  on { from: r(order_lines.order_id)     to: r(shipments.order_id) }
  on { from: r(order_lines.warehouse_id) to: r(shipments.warehouse_id) }
}

The many_to_one claim applies to the pair. Each order line matches at most one shipment, because (order_id, warehouse_id) is unique in shipments. For a three-column key, add a third on {} block.

Step 3: Add it to a dataset

Then, add the models and relationship to your dataset to make them available for querying. This exposes the unified data structure to your business users.

fulfillment.dataset.aml
Dataset fulfillment {
  data_source_name: 'your_datasource_name'

  models: [order_lines, shipments]

  relationships: [
    relationship(order_line_shipment, true)
  ]
}

Step 4: Query

Finally, run a query to see how Holistics joins the tables and aggregates the data. You can now explore the combined fields seamlessly.

explore {
  dimensions { carrier: shipments.carrier }
  measures { qty: sum(order_lines.qty) }
}

Holistics emits one join with both conditions:

LEFT JOIN shipments
  ON  order_lines.order_id     = shipments.order_id
  AND order_lines.warehouse_id = shipments.warehouse_id

Result:

carrier	qty
DHL	6
UPS	1

The total is 7, the same as the sum of order_lines.qty. Each order line is counted once.

Key points

Declare every column of the key. The many_to_one claim is only true over the full combination. Leave a column out and the join fans out.
Uniqueness is your claim. Holistics does not check that the combination is unique on the to side. If it is not, numbers are wrong in the same way as any false many_to_one.
Works on every database. The emitted SQL is a plain multi-column equality join.

What is a composite key?​

Solution: one on {} block per column​

Step 1: Define models​

Step 2: Define the relationship​

Step 3: Add it to a dataset​

Step 4: Query​

Key points​

Additional resources​