Scaling Your Joins: The Composite Key Strategy

In our previous lessons on Stitching Data, we joined datasets using a single unique identifier, like a device_id or a hostname. This works perfectly when one name equals one unique row of data. However, as you build more advanced dashboards, you will encounter scenarios where you need multiple pieces of information to identify a single metric. This is the "Multi-Field" Challenge.

Consider a Disk Usage dashboard:

• A single host (e.g., server-01) has multiple mount points (e.g., the root drive /, a database drive /var/lib/data, and a backup drive /mnt/backups).
• If you attempt to join your metrics to your inventory using only the host column, Grafana's transformation engine will see three different rows for server-01 and won't know which drive capacity belongs to which usage metric.

Since the Join by field transformation—which we introduced in the Stitching Data lesson—focuses on selecting one field to align data, we need a way to combine our identifiers.

The solution is the Composite Join Key.

A composite key is a single column created in your SQL query specifically to serve as a "hook" for Grafana's UI. We do this by concatenating (sticking together) multiple identifying fields into one unique string.

In PostgreSQL, we use the || operator to merge columns. By combining host and mount into a new column called join_key (e.g., server-01:/data), we provide Grafana with a single, unique string that aligns high-velocity metrics with static metadata perfectly.

The Workflow:

1. Query A (The Base): Pull the disk usage percentages and generate a join_key.
2. Query B (The Lookup): Pull the disk capacities and generate the exact same join_key.
3. The UI: Use this join_key to stitch the data together and then refine the view so the technical plumbing remains hidden from the user.

Our base query provides the heartbeat of the dashboard. We continue to use the $__time macros we established in the Writing Join-Ready Queries lesson to ensure the time-axis is formatted correctly for Grafana.

-- Query A: Time-series usage metrics (The Base)
SELECT
  $__time(ts),
  host,
  mount,
  host || ':' || mount AS join_key, -- The Composite "Hook"
  used_pct
FROM public.metrics_disk
WHERE $__timeFilter(ts)
ORDER BY 1;

Pro-Tip: Why use the : separator?

Adding a character like : or - between your fields is a best practice. It makes the key easier to read during development and ensures that a host named web1 with a mount 01 doesn't accidentally collide with a host named web and a mount 101. Without the separator, both would appear as web101, causing the join to fail!