Grain, Lookups, and Joins: The Mental Model

The one concept that makes or breaks a CRM Analytics dataset: grain. Understand grain, why you start from your lowest-level object, and how lookups and joins pull related data onto each row without multiplying it.

Grain, Lookups, and Joins: The Mental Model

This lesson has no video on purpose — it's the concept to slow down and internalize before you build anything. Every dataset problem I've debugged over the years traces back to one of two things: the wrong grain, or a join that quietly multiplied rows. Get this right and the tools in the next lessons become obvious.

What is grain?

Grain is the question: what does one row represent?

When you build a dataset "about opportunities," the grain is one row per opportunity. That's your core object — your lowest, most detailed level. Everything else you bring in (the account, the owner) is supporting information attached to that opportunity row. The grain never changes: one opportunity, one row.

Say it out loud before you build: "one row of this dataset is one _____." Opportunity. Case. Account. Activity. If you can't finish that sentence cleanly, you're not ready to build the dataset yet — you're still deciding what it's about.

Start from the lowest object

Here's the rule that saves you every time: start with your lowest-grain object and look up the hierarchy.

In Salesforce, an Opportunity looks up to an Account (via AccountId) and to a User owner (via OwnerId). An Account sits higher — one account has many opportunities. So:

Start with Opportunity ✅

From an opportunity you can look up to its Account and its owning User. One row per opportunity, enriched with account and user detail. Exactly what you want.

Start with Account ❌

From an account you can only look up — but there's nothing above it here. You can't reach 'down' to opportunities. You'd be stuck with one row per account.

This is why the object you start with is a design decision, not an afterthought. If your dashboard is about opportunities, start with Opportunity. If you genuinely need one-row-per-account, that's a different dataset with a different grain — and that's fine, just be deliberate about it.

Lookups vs joins

You'll see the word join in the tools, but for baseline datasets what you almost always want is a lookup.

A lookup attaches fields from a higher-level, "parent" object onto each child row. Bring the account's name and industry onto every opportunity — without changing the grain. One opportunity stays one row.
A join (in the fuller relational sense) can match rows many-to-many and multiply them. Join opportunities to their line items and suddenly one opportunity becomes five rows — one per line item. Sometimes that's what you want; often it's a bug that inflates your sums.

The classic mistake: you join a parent to a child expecting one row and get many, so every sum(Amount) is now double- or triple-counted. When a total looks too big, check your grain first. Nine times out of ten a lookup got treated like a fan-out join.

Why Dataset Builder feels "safe"

The next lessons build the same dataset two ways. The tools differ in exactly this area:

Dataset Builder → lookups

It only lets you look *up* the relationship tree and figures out the join keys for you. Hard to get the grain wrong — which is why beginners should start here.

Recipes → full control

You choose the objects, the join type, and the keys yourself. Far more powerful, but you own the grain — and the mistakes.

There's a dedicated deep-dive on lookups, grain, and joins in the Data Manager / data-expert material, and it's worth watching once you're comfortable here. But the one thing to carry into the next lessons is this: decide your grain first, start from the lowest object, and prefer lookups that keep one row per record. Now let's build that opportunities dataset with Dataset Builder.

Discussion

No comments yet — be the first to start the discussion.