CRM Analytics Data Platform Best Practices

Optimizing your CRM Analytics (CRMA) data platform means balancing performance, data limits, and processing time. The modern CRMA architecture is built on Data Prep (Recipes) and Data Sync; legacy Dataflows are in maintenance mode and should be migrated to Recipes over time. 

A strong governance and architectural foundation ensures you deliver the right data to the right audience — driving trust and business value while preventing the proliferation of duplicate datasets.

How to use this guide. It serves two audiences. For customers and admins, it provides governance frameworks, design patterns, and the why behind each practice. For engineering and AI agents (e.g., Claude Code / CRMA MCP), it provides hard constraints, metadata behaviors, and syntax. Platform limits vary by edition and license and several are configurable; the authoritative source for your org is always the in-product Usage dashboard (Analytics → Data Manager) and the official CRM Analytics Limits and Allocations page. Where this guide quotes a number, treat it as a typical default unless stated otherwise.

Jump to…

• Analytics Governance & Operating Models
• Data Sync (Replication) Best Practices
• Data Prep & Recipes Best Practices
• Legacy Dataflows & Migration Best Practices
• Troubleshooting & Maintenance
• Platform Limits Reference
• Direct References & Official Guides

Analytics Governance & Operating Models

Defining the right balance between flexibility and control is critical for scaling your analytics solutions.

Choose an operating model that fits your organization:

• Centralised — Datasets and dashboards governed entirely by a core IT/BI team.
• Distributed — Core datasets governed centrally; business units build and maintain their own dashboards.
• Hybrid — Datasets governed by a corporate Center of Excellence (CoE); dashboard creation shared between the CoE and business units.

Enforce naming standards. Consistent naming makes pipelines easy to troubleshoot, makes field meaning clear to end users, and — increasingly — makes assets legible to AI agents and LLMs that operate on your metadata. A workable convention links a recipe to the dataset it produces:

• Recipes: Rcp_[BusinessUnit]_[PrimarySource]_[Description] — e.g. Rcp_Sales_SFDC_OpptyPipeline
• Datasets: Ds_[BusinessUnit]_[Description] — e.g. Ds_Sales_Opportunities

Protect dataset identity. Avoid reusing the same label or API name across different datasets, and avoid renaming a dataset that downstream dashboards or recipes already reference. Duplicate or re-pointed identifiers are a common problem in multi-team orgs and silently break lineage.

Document lineage. Keep a record of which recipe produces which dataset, and which dashboards consume it. A naming convention that ties a recipe to its output dataset (above) makes this largely self-documenting. (CRMA’s Recipe Inspector and dependency/lineage tooling help here).

Design row-level security (RLS) deliberately. Enrich datasets for security by leveraging role and account hierarchies. Use the flatten transformation in a recipe to expand a hierarchy (e.g. UserRole) into the multi-value column that a security predicate then references.

Engineering context — Security predicates. Row-level security is enforced by a predicate string set on the dataset (re-applied via the recipe output node). The predicate must reference column API names exactly. Illustrative examples (adapt to your actual columns):

• Ownership: ‘OwnerId’ == “$User.Id”
• Role hierarchy (after a flatten on UserRole): ‘Roles’ == “$User.UserRoleId”
• Team / multi-value: ‘TeamMembers’ == “$User.Id”

Retire unused fields at the sync/recipe boundary. Every synced field is memory a recipe has to move, and every carried-through field is real cost per run. Periodically audit which dataset fields are actually referenced by dashboards, lenses, and downstream recipes — and drop or stop syncing the rest.

Read “unused” as a lower bound, not a verdict. Field-usage tools (including Recipe Inspector’s field references and any AI-agent equivalent) can only see fields explicitly named in SAQL, XMD, and widget config. A field surfaced as “not referenced” is a strong candidate for retirement — but confirm it isn’t being pulled dynamically or via computed field references before you drop it. If your tool caps the number of consumer dashboards it scans (say, 40), the “not referenced” list will over-count; that cap must be surfaced in the report itself.

Data Sync (Replication) Best Practices

Data Sync decouples data extraction from your transformation layer. Pre-pulling data means recipes don’t spend execution time pulling records straight from the CRM core.

Enforce incremental sync by default. For local Salesforce objects, incremental sync captures only changed records, drastically reducing runtimes. It tracks change via the SystemModstamp field.

Run a full sync when the changeset is large. Incremental sync is most efficient for small, frequent deltas. As a rule of thumb, choose a full sync when a single run’s changeset exceeds roughly 20% of the object’s records, or ~1M records — at that scale a full sync is often faster and cleaner than a large incremental delta.

Run periodic full syncs to correct drift. Incremental sync keys off SystemModstamp, which does not capture physically hard-deleted records in Salesforce — so deleted rows can linger in CRMA. Schedule a full sync weekly or monthly during off-peak hours to reset data integrity.

Filter and reduce at the sync level. Don’t bring dirty or unnecessary data into CRMA. Apply hard filters in the Data Sync connection settings using valid SOQL WHERE clause syntax (e.g. CreatedDate >= LAST_N_YEARS:3, or IsDeleted = false). Reduce synced fields to only what’s strictly required to preserve memory and speed downstream recipes.

Bring external data in at the lowest grain you actually need. For external sources, replicate at the lowest level of detail a dashboard or exploration will actually use — and pre-aggregate at the source when you can. If transactional rows are only ever consumed as customer-, user-, or date-level aggregates, aggregate before the data enters CRMA rather than carrying every row through sync, recipe, and dataset. (Local Salesforce objects are pulled at record grain by design; this is primarily an external-source discipline.)

Group and schedule strategically. Use connections to group objects for scheduling. CRMA issues bulk requests when pulling from Salesforce; schedule syncs during low-traffic windows when few other bulk jobs run.

API-consumption note. Syncs through the standard Salesforce local connector (SFDC_LOCAL) do not consume your org’s Bulk API entitlement. External and org-to-org connectors do count against the relevant API limits — factor this in when scheduling high-frequency external syncs.

Enable failure notifications. Turn on Data Sync failure notifications in Analytics Settings so the team is alerted the moment a sync fails — rather than discovering it later via stale dashboard data.

Let CRMA cache for you. CRMA automatically caches synced data so downstream recipes don’t re-convert it on every run, which speeds up recipe execution. Keep incremental sync on and synced fields lean to get the most from this.

Verify the UI label. Earlier drafts referenced a Connections-tab option worded “Optimize data sync for future runs.” Confirm the exact current label in your org before citing it to customers; the underlying behavior (caching synced data to accelerate recipes) is the durable point.

Data Prep & Recipes Best Practices

Recipes (Data Prep) are the visual standard for transforming data in CRMA. They run on a multi-tenant, Spark-based engine, so keeping them lean is key.

Filter early, drop columns immediately. The golden rule of CRMA data volume: reduce the footprint as close to the input node as possible. Add a filter node right after the input and use Drop Columns in your first step to shed unneeded fields. (Limit: a single dataset can hold a maximum of 5,000 fields, including up to 1,000 date fields — but the goal is far fewer.)

Read from replicated data, not live sources. A recipe’s Input nodes should point to data that Data Sync has already replicated, not to a direct/live connection queried at recipe runtime. Direct-query inputs make every recipe run wait on the source system, count against API limits (for external/org-to-org connectors), and forgo CRMA’s sync cache.. Reserve direct-query inputs for the rare case where the recipe genuinely must read live, un-replicated data.

Understand join types to avoid row inflation. Minimize joins wherever possible.

• Use Lookup for typical 1-to-many or many-to-1 relationships (e.g. adding Account details to an Opportunity).
• Be cautious with Left/Right/Inner Joins on non-unique keys — they can multiply row counts, slowing recipes and pushing datasets toward row-allocation limits.
• CROSS joins should be an explicit design decision, not an accident — they multiply every row on the left by every row on the right. If a static analysis flags one, treat it as an error until proven intentional.

Consolidate output nodes — avoid the “API tax” of duplicate loads. A common anti-pattern is five recipes each re-pulling the same base Account or Opportunity data. Every duplicate load costs API time, network I/O, and downstream cache warming. Load the base object once, then branch the output — either via multiple Output nodes in the same recipe, or via staged data (below) so other recipes can consume it without re-pulling.

Leverage staged data. If heavy processing rules are shared by multiple downstream recipes, output that intermediate result as staged data (consumed by other recipes’ input nodes) rather than registering a user-facing dataset. Note the correct term is staged data, not a “staged dataset” — staged data is intermediate, retained for a short window for recipe-to-recipe consumption, and is not a queryable user dataset.

Speed up dataset registration with an append strategy. For large or incrementally-growing outputs, the Existing Dataset (Append) pattern — and the newer Advanced Append Output node — let a recipe add new rows to an existing dataset instead of rebuilding and re-registering the whole dataset every run, cutting registration time. Pair it with incremental sync for external sources. Confirm the exact node/option name and its availability in your org before relying on it.

Optimize calculations and minimize shuffling. Reduce multi-value (MV) columns where possible, and prefer a computeExpression (or formula field) over computeRelative when aggregating multiple criteria.

Relabel columns in the dataset XMD, not in a recipe transform. Field relabeling (friendly display names) belongs in the dataset’s XMD, applied at query/display time — not in a recipe Transform node. Renaming columns inside the recipe adds transform overhead to every run for a purely cosmetic change the XMD carries for free. Reserve in-recipe renames for cases where a downstream node, join key, or output genuinely needs the new API name.

Formula hygiene — write for the recipe engine, not for SAQL. Recipe Transform formulas use a different dialect from SAQL. The following tokens are valid in SAQL but do not parse in a recipe Transform formula and are common causes of silent-fail or migration errors:

• == (use =)
• && and || (use AND, OR)
• <> (use !=)
• toDate() (use date_from_string() or INTERVAL arithmetic)
• index_of(), number_to_string() (no direct recipe equivalent — refactor)
• _sec_epoch (the seconds-since-epoch column doesn’t exist in a recipe formula context)

A related trap: NULLIF() is not supported in the CRMA recipe SQL dialect. Rewrite as a CASE expression with coalesce().

Handle nulls explicitly in CASE. CASE WHEN x = ‘A’ THEN 1 ELSE 2 END will silently send nulls into the ELSE branch, misclassifying them. Add a WHEN x IS NULL THEN … branch or wrap the input in coalesce(x, ”).

Watch out for divide-by-zero. Any division should guard the denominator — CASE WHEN denom = 0 THEN NULL ELSE num / denom END, or use coalesce(NULLIF(denom, 0), 1)-style guards in dialects that support them.

Use rolling date windows, not hardcoded years. Filters and formulas that pin a literal year (year = ‘2024’ or WHEN year = ‘2025’ THEN ‘2025’) drift silently each year and produce empty datasets in January. Use current_date() / year(current_date()) / INTERVAL arithmetic instead.

Date vs DateTime functions matter. Dataset Date fields are stored as DateTime under the hood. Functions like add_months() and trunc() behave differently against a Date vs a DateTime. When in doubt, use INTERVAL arithmetic or date_diff on epoch/DateTime columns. Avoid manual Unix-timestamp math (to_unix_timestamp(a) – to_unix_timestamp(b)) / 86400) — date_diff(‘day’, b, a) is clearer, correct across time-zones, and does not lose precision on daylight-saving boundaries.

Derive dates once; lean on runtime date dimensions. Don’t materialise many variants of the same date (month, quarter, fiscal year, week-start, …) as separate recipe columns. CRMA’s standard date dimensions expose those parts at dashboard runtime from a single date field. Every extra derived date column is a load that the engine carries on every run and a field the dataset counts against its limit. Materialise a derived date in the recipe only when a filter, join key, or security predicate actually needs it upstream.

Prefer window functions to self-joins. Any pattern that self-joins a table to itself to find “the previous row,” “the first value in a group,” or “the running total” is a window-function target. Use the Multiple-Row Formula node with lag(), lead(), first_value(), last_value(), or aggregate windows. Self-joins on large tables are the single most common cause of avoidable row explosion.

Reserve computeRelative for genuine sequence-tracking. Even where computeRelative is technically viable, prefer the newer Multiple-Row Formula (window functions). computeRelative forces Spark to partition and sort the data — a shuffle — which is expensive. Reserve for cases where you’re actually tracking a per-partition sequence (e.g. historical stage progression), not for aggregations that a window function or computeExpression can handle without a shuffle.

Use event-based scheduling. Where possible, trigger a recipe automatically after its upstream data sync completes rather than at a fixed clock time. Event-based scheduling removes timing guesswork and the risk of running on stale inputs — and helps avoid the queue-and-cancel problem described in “Troubleshooting & Maintenance”.

Match refresh cadence to the latency requirement. Run recipes and syncs only as often as the business actually needs the data. An hourly schedule where stakeholders consume the dashboard once a day burns run allocation, API capacity, and compute for freshness no one uses — and tightens the schedule toward the queue-and-cancel problem. Set cadence from the real data-latency SLA, not from habit.

Naming and defaults discipline. Beyond the org-level Rcp_ / Ds_ conventions, keep the discipline inside recipes too:

• Rename transformation nodes from their defaults (FORMULA1, TRIM0, JOIN2) to describe what they do. Default names hide the pipeline’s intent from every future reader — human and AI.
• Give every Output node a name that matches its dataset — avoid two Output nodes writing to the same dataset name (either from a copy-paste, or from two recipes both writing Rcp_Sales_Pipeline) — this silently overwrites results and is one of the sharpest deterministic red flags an audit can surface.
• Very large recipes (>20 nodes) or long dependency chains (>10 deep) are usually a signal to split — see Section 4, “Break up monoliths.”

Diagnose with Recipe Inspector. When a recipe is slow, use Recipe Inspector (Data Manager → Jobs Monitor → Transform Data on a recipe job) to read node-level metrics — rows in/out, duration, and status per transform — and pinpoint the hotspot (e.g. a join explosion) instead of guessing.

Engineering context — Spark mechanics.

• computeRelative behaves like a SQL window function: it partitions and sorts the data, forcing Spark to shuffle data across cluster nodes — an expensive operation. Reserve computeRelative for genuine sequence-tracking needs (e.g. historical stage progression).
• Multi-value columns multiply processing cost: every row must be expanded across each value in the MV column, so wide MV columns inflate the volume the engine moves per row. Keep MV columns to the minimum the use case requires.

Edit safely. To test changes without affecting production dashboards: save the recipe as a new copy, make and verify your changes, then overwrite the production recipe.

Legacy Dataflows & Migration Best Practices

Dataflows are in maintenance mode — they receive no new features. Recipes are the recommended path for all new development, and existing dataflows should be migrated over time. (There is no announced end-of-life or forced cutover date; plan migration on your own timeline.)

Use the conversion tool — then review. Data Manager provides a Convert to Recipe action that maps most legacy nodes (e.g. augment → Join/Lookup, computeExpression → Formula). It is tool-assisted, not a clean one-click port: it converts only eligible transformations, flags the rest with warnings, appends a suffix to dataset names, and leaves the original dataflow intact. Always review and test the result.

Audit post-migration formulas. Legacy dataflows used SAQL-like syntax. After upconversion, verify text, number, and date formulas for null handling and string concatenation under the Recipe engine (e.g. confirm legacy case when logic maps correctly to Recipe Formula nodes). Apply the formula-hygiene rules from Section 3 — many post-migration failures come from SAQL tokens (==, &&, <>, toDate(), index_of(), _sec_epoch, NULLIF()) that survived the conversion.

Remove dead branches. A frequent artefact of converted or hand-edited dataflows is a chain of transformations that doesn’t reach an Output/Save/Register node. Every dead branch is compute that runs on every execution and returns nothing. Trace the graph from Output nodes backwards — anything not reachable can be deleted.

Retire the three-step flag pattern. Old dataflows commonly compute a flag column upstream, then filter downstream on that flag. Inline the logic directly in the Filter node and drop the intermediate helper column — the flag was only ever a workaround for older SAQL constraints.

Re-apply security predicates via the output node. A commonly missed issue: a security predicate set manually in the UI is erased every time the recipe regenerates the dataset. Always configure the predicate on the recipe Output node so it survives each run.

Break up monolithic dataflows. Admins once built huge single dataflows (hundreds of nodes) to dodge scheduling conflicts. Recipes handle parallel processing and event-based orchestration far better — split monoliths into smaller, modular recipes.

Don’t “lift and shift.” Migration is an opportunity to rethink your data model and metrics using CRMA’s strengths — not a 1:1 replica of legacy structure. Re-evaluate joins, grain, and which datasets you actually need.

Troubleshooting & Maintenance

Isolate unknown errors. When a recipe fails without a clear error node, break it into smaller test recipes to pinpoint where the engine is choking. Recipe Inspector node metrics often locate the problem faster.

Detect join explosion by measurement, not by guess. The most reliable signal is rows_out / rows_in at a join node on the last successful run — surfaced by Recipe Inspector. A ratio of:

• >1.5× is a warning: often intentional (a legitimate one-to-many), but worth a look.
• >5× is almost always a bug: an accidental non-unique-key join, an unintended CROSS join, or a self-join that should be a window function).

Reporting the exact “180K rows in → 4.2M rows out at Join_Opportunity_LineItems (23×)” is far more useful than “this recipe has joins that might be slow.” Never simulate this ratio from static JSON — if the last run failed or hasn’t happened, say so and suppress the finding.

Prevent canceled runs. Tightly packed event-based or time-based schedules cause jobs to queue and get canceled. Extend buffers between recipe runs and data sync runs, and prefer event-based triggers over overlapping fixed schedules.

Resolve numeric overflows. If rows fail on numeric overflow, adjust the field’s precision (total digits) and scale (decimal places). Alternatively, remove unused geolocation (latitude/longitude) fields from sync connections — they can introduce unexpected rounding behavior.

Monitor org-wide consumption. Watch total dataset row usage on the Usage dashboard. Org-wide row capacity is large but finite and license-dependent; exhausting it causes syncs, recipes, and refreshes to fail. Audit and retire unused/orphaned datasets regularly.

Don’t register logging or debug datasets as user datasets. Datasets that exist only for IT monitoring, troubleshooting, or recipe debugging shouldn’t be registered as user-facing outputs — they consume row allocation, clutter the asset list, and surface as orphans in usage audits. Keep recipe outputs limited to datasets that drive a business decision or action; strip debug/logging registrations before promoting a recipe to production.

Platform Limits Reference

Limits vary by edition and license (CRM Analytics vs. Data Pipelines / “Sonic”), and several are configurable or enforced as ranges. Always confirm against your org’s Usage dashboard and the official Limits and Allocations page. The values below are typical defaults.

⚠️ Common misconception — clear this up. There is no “10GB or 10M rows per recipe run per output connector” hard limit. The 10M-row figure is the monthly recipe-output allowance on the Data Pipelines license; the 10GB figure belongs to the Medium connector input tier (20M rows / 10GB per object) and to certain output-connector daily volume tiers. Conflating these into a single “per-run” rule is a frequent error — and one an AI agent operating on your pipelines must not encode.

Engineering note. Documented values and code-enforced values diverge for a handful of limits (e.g. concurrent dataflow runs, local-sync runtime). For any agent that acts on limits (scheduling, refactoring), treat the in-org Usage dashboard as source of truth and verify rather than hard-coding.

Direct Data & Data Cloud Integration

The patterns above cover the dominant “sync in → recipe → dataset” model. Two adjacent patterns — Direct Data (live query) and Data Cloud (Data 360) integration — carry their own considerations:

• Direct Data is for low-volume, interactive lookups, not bulk analytics. Per current limits (verify on the Limits page), live queries return a capped result set (on the order of a few thousand rows) and time out after a short interval. Don’t use Direct Data for high-volume analytical workloads.
• Complex joins on Direct Data for Data 360 can break dashboard filters; the common workaround is to pre-compute aggregates as Calculated Insights in Data Cloud and query those.
• Security predicates and sharing inheritance are not available with Direct Data for Data 360 — account for this in your security design.
• Prefer the Data Cloud Connector over Direct Data for Data Cloud integration where performance and the security model matter, reserving Direct Data for genuine live-lookup needs.

These figures and behaviors evolve quickly as Data 360 matures — confirm against current documentation before designing on them.

Direct References & Official Guides

Written by