Understanding Grain, Target Dimension, Reconciliation, Enrichment, and Change Dimension in Orchestrated Campaigns

​@RakeshK1 

Grain and Target Dimension: The Foundation
Grain = What does one row represent?

Customer table → 1 row = 1 Customer
Order table → 1 row = 1 Order
Target Dimension = The schema entity you're currently working with

Start: Targeting Customer (dimension) with Customer grain
After enrichment: Still Customer grain + Customer dimension
After change dimension: Now Order grain + Order dimension

------------------------------------------------
Can Grain and Dimension Differ?
In a typical workflow, they stay aligned. But here's the nuance:

Scenario 1: Enrichment

You're at Customer grain + Customer dimension. You enrich with Order data (full outer join). Now you have:

Grain: Customer (1 row per unique customer)
Dimension: Customer (but with order attributes)
Result: Aligned
Scenario 2: Change Dimension

You shift from Customer (1:N to Orders) to Order as your target. Now:

Grain: Order (1 row per order)
Dimension: Order
Result: Aligned (both shift together)
------------------------------------------------

Reconciliation (Inner Join)
Purpose: Merge data from multiple sources at the same grain level. Resolves 1:1 relationships or validates relationships exist.

Backend behavior: Uses an Inner Join — keeps only rows that match in both datasets.

Example: Reconcile Customer dataset with CRM data

Before: 100 customers
Reconcile (inner join): 95 customers
After: 95 customers (5 excluded — no match in CRM)
Result: Grain stays Customer. Dimension stays Customer. Row count decreases if there are unmatched rows.

When to use: Data quality checks, validating that IDs exist in both systems, or cleaning up incomplete records.
------------------------------------------------

Enrichment / Linking (Full Outer Join)
Purpose: Add attributes from a related entity without changing your grain or target dimension.

Backend behavior: Uses a Full Outer Join — keeps all rows from your primary table and links related data.

Example: Enrich Customers with latest Order data

Before: 100 customers (grain: Customer)
Enrich: Link to Order table
After: 100 customers (grain: Customer)
New attributes added: order_count, last_order_date, order_status, etc.

Key point: Row count stays 100. Grain stays Customer. You're not moving to order-level detail; you're enriching customer records with order context (usually aggregated: "how many orders", "last order date", not individual order rows).
------------------------------------------------

Change Dimension (Right Join)
Purpose: Shift the entire population from one entity to another (e.g., Customer → Order).

Backend behavior: Uses a Right Join — pivots the population to the target dimension.

Example: Shift from Customer to Order (1:N relationship)

Before: 100 customers (grain: Customer, dimension: Customer)
Change dimension: Customer → Order
After: 350 orders (grain: Order, dimension: Order)
Why 350? Each customer had multiple orders. If C1 has 3 orders, C2 has 4, etc., the total explodes to order-level granularity.

What happens to the data?

Each customer row expands into N order rows (1 for each order)
Customer attributes are repeated on each order row
Order-level attributes now become direct columns (not aggregated)
You retain both Customer context + Order detail
When to use: When you need to send to a system at order level, or when an action must be triggered per order (not per customer). E.g., "Send an email for each overdue order" rather than "Send one email per customer".

------------------------------------------------

Your table says it can explode to N records for 1:N relationships. This is context-dependent:

If 1:1 relationship (one Customer ID matches to one record in the other source) → record count stays the same
If 1:N is encountered and you're still at Customer grain → technically it could explode, BUT reconciliation is typically used for 1:1 matching (data sources at the same grain level)
Inner Join behavior → filters out non-matches, so count either stays same or decreases

Change dimension nuance:

HI ​@SatheeskannaK,

Thanks for your answers. Based out of adobe consulting convos, here is where I am.

Grain or Working table or Targeting dimension is always same expect when we create a data model unconventionally like, customer dimension with multiple order rows for each customer making order as grain.

 

Will update future findings here as I learn more.