Entities and the join graph

Before you can define a single metric, the layer needs to know how the tables connect. When someone asks for revenue (which lives in order_items) sliced by region (which lives in customers), the layer must find the path: order_items → orders → customers. That map of tables and their join keys is the join graph.

Percolate's join graph

order_items ──(order_id)── orders ──(customer_id)── customers
     │
     └────(product_id)──── products

subscriptions ──(customer_id)── customers
marketing_spend  (standalone: channel + month only)

Each edge is a join condition. The layer stores these as data so it can automatically connect any metric's home table to whatever dimension tables a question needs — walking the shortest path and adding exactly the joins required, no more.

Why the graph matters for correctness

• One-to-many joins fan out rows. Joining orders to order_items multiplies each order by its line items. If a metric counts orders after that join, it over-counts — unless it uses COUNT(DISTINCT order_id). The graph plus careful metric definitions prevent this.
• Not every table reaches every other. marketing_spend doesn't join to products — so "marketing spend by product category" is meaningless, and the layer should refuse it rather than invent a number.
• The path must be unique enough. Ambiguous join paths produce ambiguous numbers; a well-designed graph has a clear route from each fact to each dimension.