SfSkills Domains Docs GitHub

Home / architect / analytics-data-architecture

analytics-data-architecture

Use this skill when designing CRM Analytics dataset architecture, planning dataflow or Recipe ELT strategies, implementing incremental extraction patterns, or integrating external data lakes (Snowflake, BigQuery, Redshift).

Open the skill folder on GitHub

Analytics Data Architecture

Use this skill when architecting CRM Analytics data pipelines end-to-end: designing datasets for scale, choosing the right ELT strategy, implementing incremental extraction where native support is absent, and integrating external cloud data sources. It activates when the practitioner needs to make structural decisions — not just configure an existing setup.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

Dataset Row Limits and the 2-Billion-Row Ceiling

CRM Analytics datasets have a per-dataset row cap that is org-allocation-dependent, with the practical ceiling at 2 billion rows per dataset. Exceeding this limit causes the dataflow or Recipe writing to that dataset to fail; the platform does not silently truncate. The architectural response is to split large datasets by time window or business dimension (e.g., current-year vs. historical), implement archival datasets with reduced granularity, and filter aggressively in the ELT layer before data lands in the dataset — not at SAQL query time.

The total row count across all datasets in an org also has an allocation. Architects must track both per-dataset and org-wide consumption, especially when onboarding new data sources.

The 60-Run Rolling Window

The platform allows at most 60 combined dataflow and Recipe runs within any rolling 24-hour window. This is not a calendar-day reset — it is a true rolling window. An org that runs 60 jobs between 08:00 and 09:00 cannot run another job until 08:00 the following day for each slot to clear.

This is the most common architectural bottleneck in mature CRM Analytics orgs. The correct design responses are:

ELT Strategy: Push Work into the Dataflow Layer, Not SAQL

SAQL (Salesforce Analytics Query Language) executes at dashboard query time. Every user interaction with a dashboard that triggers a SAQL query recomputes from the stored dataset. Joins, aggregations, and complex filters placed in SAQL run repeatedly at runtime and do not scale.

The correct ELT strategy is to perform all joins, filters, augments, and computed columns during the dataflow or Recipe run. The dataset that lands in CRM Analytics should be query-ready: denormalized, pre-joined, and pre-filtered to the relevant time window. SAQL should only perform final aggregations and simple filters that vary per user interaction.

This distinction is sometimes called "push-down ELT": move the heavy lifting upstream into the dataflow/Recipe layer where it runs once per refresh cycle, not once per user query.

Incremental Extraction: Dataflows vs. Recipes

Dataflows support incremental extraction natively through the Data Sync replication layer. When a Salesforce object is synced using incremental mode, Data Sync tracks the SystemModstamp of the last-replicated record. On subsequent syncs, only records with a newer SystemModstamp are replicated. The dataflow then processes the delta. This requires the source object to have SystemModstamp indexed and the Data Sync connection configured for incremental mode in Data Manager.

Recipes do NOT support native incremental loads. Every Recipe run reads its full input dataset from scratch — there is no built-in mechanism to process only changed rows. The correct workaround is the snapshot-join technique:

  1. On first run, write the output dataset as the "current snapshot."
  2. On subsequent runs, join the full current source data against the prior snapshot dataset.
  3. Use a computed field comparing LastModifiedDate (from source) against the snapshot date to identify new or changed records.
  4. Union the unchanged rows from the snapshot with the changed rows from the current source pull.
  5. Write the merged result as the new snapshot, overwriting the previous one.

This approach requires careful schema management — the snapshot dataset schema must match the Recipe output schema exactly, or the union step fails.

Common Patterns

Pattern 1: Snapshot-Join Incremental for Recipes

When to use: A Recipe refreshes a large dataset (millions of rows) from a Salesforce object and full reprocessing takes too long or consumes too much run budget.

How it works:

  1. Create a Recipe that reads the source object (e.g., Opportunity) and writes the first full load to opp_snapshot dataset.
  2. On the next scheduled Recipe run, the Recipe reads both the live source and opp_snapshot.
  3. A Join node compares LastModifiedDate from the live source against the stored snapshot_date field in opp_snapshot.
  4. Records where LastModifiedDate > snapshot_date are treated as changed; all others come from opp_snapshot unchanged.
  5. An Append node unions both sets; a Formula node stamps today() as the new snapshot_date.
  6. The output overwrites opp_snapshot.

Why not the alternative: Without this pattern, every Recipe run reprocesses the full Opportunity table. In orgs with 5M+ Opportunity records, this can take 20–40 minutes per run and consumes a full slot from the 60-run window.

Pattern 2: Push-Down ELT — Denormalize in Dataflow, Not SAQL

When to use: Dashboard queries are slow because SAQL is joining two or more datasets at runtime, or because complex SAQL expressions (e.g., windowed calculations) are running per user interaction.

How it works:

  1. Identify the join keys between the source datasets (e.g., Opportunity.AccountId → Account.Id).
  2. Add an Augment transformation in the dataflow to embed the Account fields needed by the dashboard directly into the Opportunity dataset.
  3. Add a Computeexpression transformation to pre-calculate derived fields (e.g., Days_to_Close = DATEDIFF(CloseDate, CreatedDate)).
  4. Write a single flat dataset to CRM Analytics.
  5. Update dashboard SAQL to remove multi-dataset joins — queries now target the single flat dataset.

Why not the alternative: Multi-dataset SAQL joins are evaluated on every query execution. They cannot be cached across users and scale poorly as dataset row counts grow.

Pattern 3: External Data Lake Integration via Remote Connections

When to use: The org needs to query or ingest data from Snowflake, BigQuery, or Amazon Redshift alongside Salesforce object data.

How it works:

  1. In Analytics Studio, navigate to Data Manager → Connections.
  2. Create a Remote Connection of the appropriate type (Snowflake, BigQuery, or Redshift). Enter connection credentials, project/database identifiers, and authentication parameters.
  3. Once the Remote Connection is saved, it appears as an input source available to Recipes.
  4. In a Recipe, add the Remote Connection as an input node and select the target table or view.
  5. Join, filter, and transform the external data alongside Salesforce object data within the Recipe.
  6. Write the output to a CRM Analytics dataset. Note: the output lives in CRM Analytics — it does NOT write back to the external lake.

Why not the alternative: Remote Connection configuration belongs in Data Manager, not in dataflow JSON. Dataflow JSON does not have a native connector type for Snowflake, BigQuery, or Redshift. Attempting to configure these sources in dataflow JSON will fail.

Decision Guidance

SituationRecommended ApproachReason
Large Salesforce object (1M+ rows), needs near-daily refreshData Sync incremental mode + dataflowNative SystemModstamp-based incremental; no custom workaround required
Large dataset in Recipe, full reprocessing too slowSnapshot-join technique in RecipeRecipes have no native incremental; snapshot-join is the only supported simulation
Dashboard queries joining two datasets at runtimeAugment in dataflow (push-down ELT)Pre-join at refresh time; SAQL executes once-per-query, not once-per-refresh
Run budget approaching 60-run limitConsolidate dataflows; use tiered schedulesEach dataflow run counts equally; fewer, larger runs are more efficient
External Snowflake/BigQuery/Redshift source neededRemote Connection in Data ManagerDataflow JSON has no connector type for these sources
Dataset approaching 2B row capSplit by time window or business dimensionPlatform fails the write — no auto-truncation; must design around the cap
Pre-calculated KPIs needed across many dashboard queriesComputeexpression in dataflow or RecipeCompute once at ELT time; eliminates repeated SAQL formula evaluation per user

Step-by-step instructions for an AI agent or practitioner working on this task:

  1. Inventory datasets and run volumes. List every dataflow and Recipe in the org with its row count, estimated growth rate, and current refresh schedule. Map total scheduled runs per 24-hour window against the 60-run budget.
  2. Identify ELT placement issues. Review existing SAQL queries in dashboards for joins, augments, and heavy computations. Flag any that could be pre-computed in the ELT layer and move them upstream into dataflow or Recipe transformations.
  3. Design the incremental strategy. For each large dataset, determine whether it is sourced from a dataflow (use Data Sync incremental mode) or a Recipe (implement snapshot-join). Document the snapshot dataset name, the comparison field (LastModifiedDate), and the schema contract.
  4. Configure external sources. If Snowflake, BigQuery, or Redshift data is needed, create Remote Connections in Data Manager before building any Recipe nodes. Confirm credentials, network access (IP allowlisting for Salesforce IPs if required), and authentication method (OAuth vs. username/password).
  5. Validate against limits. Confirm no individual dataset is projected to exceed 2 billion rows. Confirm combined run schedules do not exceed 60 runs per rolling 24-hour window. Adjust schedules or consolidate dataflows as needed.
  6. Test incremental correctness. For any snapshot-join Recipe, run two consecutive test cycles: first to seed the snapshot, second to confirm only changed records are re-processed. Validate row counts before and after to confirm no duplication or data loss.
  7. Document and schedule. Record dataset ownership, refresh owners, row-limit thresholds, and escalation path for run-budget exhaustion. Set up Dataflow Monitor alerts where available.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. Recipes have no native incremental mode — Every Recipe run reprocesses the entire input dataset. Architectures that claim "only changed rows are processed" in a Recipe without a snapshot-join step are incorrect and will reprocess the full source on every run, consuming the full run slot and runtime every time.
  2. 60-run limit is a rolling 24-hour window, not a calendar-day reset — An org that exhausts its 60-run budget at 10:00 AM cannot run another job until 10:00 AM the following day as each slot clears. Calendar-day assumptions (e.g., "resets at midnight") produce incorrect capacity planning.
  3. Remote Connections for external lakes belong in Data Manager, not dataflow JSON — Snowflake, BigQuery, and Redshift sources are configured as Remote Connections under Data Manager → Connections. Dataflow JSON has no connector node type for these sources. Architects who attempt to embed connection parameters in dataflow JSON will find the dataflow fails to validate.
  4. Remote Connections do not write back to the external lake — CRM Analytics only reads from external sources via Remote Connections. Output datasets are always written to CRM Analytics internal storage. Any architecture that expects analytics transformations to populate the external data warehouse requires a separate ETL pipeline outside CRM Analytics.
  5. Data Sync incremental mode requires SystemModstamp to be indexed — If the source Salesforce object does not have SystemModstamp available or the Data Sync connection is set to full-replace mode, no incremental extraction occurs. This is a silent misconfiguration — the dataflow succeeds but processes full data on every run.

Output Artifacts

ArtifactDescription
Dataset design documentList of datasets with projected row counts, growth rates, split strategy, and row-limit risk rating
ELT strategy mapTable mapping each join/augment/computation to its ELT layer placement (dataflow, Recipe, or SAQL)
Run budget analysisSchedule of all dataflow and Recipe runs plotted against the 60-run rolling window
Incremental pattern specificationPer-dataset incremental approach (Data Sync incremental vs. snapshot-join), including snapshot dataset name and comparison field
Remote Connection setup checklistStep-by-step configuration for each external source in Data Manager

Package files