Join Grain Before Join Syntax
Prevent accidental row multiplication in tracking queries.
Evidence tier describes my hands-on relationship with the content. Official sources describe platform contracts/documentation. Production observations should not automatically be treated as official limits.
Most “my counts exploded” problems are grain problems. Define what one row represents in every source before writing the JOIN.
State the grain of each source in plain language.
Join on the full key required by the event relationship.
Pre-aggregate many-to-many event sources before combining them.
Use IsUnique only when it matches the metric you actually want.
Classic multiplication
Joining _Open and _Click directly by SubscriberKey can multiply rows because one subscriber can have many opens and many clicks. Aggregating after the multiplication can hide the symptom while still making the query expensive.
Safer pattern
Pre-aggregate each event source to the grain you need, then join the smaller results.
/* Stage 1: one row per subscriber */ SELECT SubscriberKey, MAX(EventDate) AS LastOpenDate FROM _Open WHERE IsUnique = 1 GROUP BY SubscriberKey /* Stage 2: repeat for clicks, then join the staged DEs */
Email tracking keys
For subscriber-level send logging and tracking joins, Salesforce guidance commonly uses JobID, ListID, BatchID and SubscriberID together where applicable.