Composite keys pattern

What is a composite key?Copy Anchor Link

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.

Sample data:

order_lines

shipments

The question: how many units did each carrier ship?

Solution: one key pair per columnCopy Anchor Link

Declare a many_to_one relationship. Put the first column pair in from and to, and add one on {} block for each extra pair. A row matches only when every pair is equal.

Step 1: Define modelsCopy Anchor Link

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.

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);;
  }
}

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 relationshipCopy Anchor Link

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

Relationship order_line_shipment {
  type: 'many_to_one'
  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 second on {} block.

Step 3: Add it to a datasetCopy Anchor Link

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.

Dataset fulfillment {
  data_source_name: 'your_datasource_name'

  models: [order_lines, shipments]

  relationships: [
    relationship(order_line_shipment, true)
  ]
}

Step 4: QueryCopy Anchor Link

Finally, run a query to see how AMQL 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) }
}

AMQL 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:

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

Key pointsCopy Anchor Link

• 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. AMQL 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.

Additional resourcesCopy Anchor Link

• Back to overview
• How joins work
• AML Relationship reference
• Fan-out issues