Analytics External Data

Analytics External Data

Use this skill when you need to bring data from outside Salesforce into CRM Analytics — whether via programmatic CSV upload using the External Data API, prebuilt Data Connectors to cloud warehouses, or Live Datasets for read-through queries against external systems. This skill covers three distinct paths and the architectural tradeoffs between them.

Before Starting

Gather this context before working on anything in this domain:

• Confirm which external data path applies: External Data API (programmatic CSV upload), Data Connector (scheduled materialized sync), or Live Dataset (read-through at query time).
• Determine whether data must be materialized inside CRM Analytics or whether querying the external system at runtime is acceptable. This is the single most consequential decision — it controls performance, freshness, and cost.
• Check CRM Analytics limits: dataset row limits (250 million rows per dataset), External Data API file size limits (per-chunk 10 MB, total 40 GB per job), and Live Dataset query timeout constraints driven by the external system.

Core Concepts

CRM Analytics supports three fundamentally different mechanisms for ingesting or accessing external data. Conflating them causes architectural mistakes that are expensive to reverse.

Path 1: Analytics External Data API (InsightsExternalData SObject)

The External Data API lets you programmatically upload CSV data into CRM Analytics to create or replace materialized datasets. It uses the InsightsExternalData and InsightsExternalDataPart SObjects exposed via the standard REST or SOAP API.

The upload sequence is strict and non-negotiable:

1. Create an InsightsExternalData record with a MetadataJson field containing a JSON schema that defines the dataset structure — field names, types (Text, Numeric, Date, etc.), and dataset name.
2. Upload data rows as InsightsExternalDataPart records, each containing a chunk of gzip-compressed CSV. Chunks must not exceed 10 MB each.
3. Set Action on the InsightsExternalData record to Process to trigger ingestion.

The metadata JSON schema step is mandatory. If you skip it or upload rows before the schema record exists, the job fails. The resulting dataset is a standard materialized CRM Analytics dataset — it is refreshed only when you re-run the upload job.

When to use: Integration pipelines (MuleSoft, Apex, external ETL tools) that need to push data into CRM Analytics on a schedule or event-driven basis. Suitable for data that does not reside in a supported Data Connector warehouse.

Path 2: Data Connectors (Materialized, Scheduled Refresh)

Data Connectors provide prebuilt connections to external cloud warehouses: Snowflake, Google BigQuery, Amazon Redshift, and others. They are configured through CRM Analytics Data Manager using a Remote Connection object.

Data Connectors work by pulling data from the external warehouse on a scheduled refresh cycle and materializing it as a standard CRM Analytics dataset. After ingestion, queries run against the materialized dataset inside CRM Analytics — not against the warehouse. This means:

• Query performance is predictable and fast (data is local to CRM Analytics).
• Data freshness is bounded by the refresh schedule, not real-time.
• Storage is consumed inside CRM Analytics for the materialized copy.

A Remote Connection is a configuration object in Data Manager. It stores credentials and connection details. It does not upload data by itself. You must create a Data Connector recipe or dataflow that references the Remote Connection to actually pull and materialize data.

When to use: When the external warehouse supports a prebuilt connector, data volume fits within refresh windows, and users need fast dashboard query performance rather than real-time freshness.

Path 3: Live Datasets (Read-Through, Not Materialized)

Live Datasets are a read-through query mechanism. When a CRM Analytics dashboard queries a Live Dataset, the query is translated and executed against the external system at runtime. No data is materialized inside CRM Analytics.

Key behavioral differences from materialized datasets:

• Data is always current — there is no refresh lag.
• Query performance is entirely dependent on the external system's response time and concurrency limits.
• CRM Analytics applies its query translation layer (SAQL to external query dialect), which may not support all SAQL constructs.
• Live Datasets cannot be joined with materialized datasets in the same SAQL query without federation workarounds.

Live Datasets require a Remote Connection. However, having a Remote Connection does not automatically create a Live Dataset — they are separate configuration steps.

When to use: When data freshness is critical and the external system can reliably serve low-latency queries at dashboard load time. Avoid when external system SLAs are inconsistent or data volumes make per-query execution expensive.

Tableau Bridge

For on-premises or network-restricted data sources, Tableau Bridge acts as a relay between CRM Analytics and data behind a firewall. Bridge is installed on-premises and manages the secure tunnel. This is the recommended path when the external data source is not cloud-accessible.

Streaming Data

For event-driven or near-real-time streaming data, CRM Analytics supports ingestion via the External Data API in micro-batches. True streaming (sub-minute latency) is not natively supported in CRM Analytics — use Salesforce Data Streams or a streaming pipeline that batches into the External Data API.

Common Patterns

Pattern 1: Programmatic CSV Upload via External Data API

When to use: An integration layer (Apex scheduled job, MuleSoft flow, Python ETL script) needs to push data from a system not supported by prebuilt connectors into CRM Analytics.

How it works:

1. Construct the metadata JSON schema defining field names and types.
2. POST to /services/data/vXX.X/sobjects/InsightsExternalData to create the header record with MetadataJson set.
3. Compress CSV data rows into 10 MB gzip chunks.
4. POST each chunk to /services/data/vXX.X/sobjects/InsightsExternalDataPart referencing the parent InsightsExternalData ID.
5. PATCH the InsightsExternalData record setting Action to Process.
6. Poll the Status field until it reaches Completed or Failed.

Why not the alternative: Standard Data Loader and Bulk API target Salesforce objects (Accounts, Contacts, custom objects) — they cannot write into CRM Analytics datasets.

Pattern 2: Snowflake Live Dataset for Real-Time Querying

When to use: Finance or operations team needs a CRM Analytics dashboard that always reflects current Snowflake data — refresh lag is unacceptable.

How it works:

1. Create a Remote Connection in Data Manager pointing to the Snowflake account with OAuth or username-password credentials.
2. Create a Live Dataset referencing a specific Snowflake table or view via the Remote Connection.
3. Build SAQL queries against the Live Dataset — queries execute against Snowflake at dashboard load time.
4. Monitor Snowflake query logs to validate latency and concurrency impact.

Why not the alternative: Scheduling a Data Connector refresh every 15 minutes introduces lag and consumes CRM Analytics dataset storage. For sub-hour freshness requirements with manageable query volumes, Live Datasets avoid the overhead.

Decision Guidance

Recommended Workflow

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

1. Identify the data path — Confirm whether the requirement calls for External Data API (CSV push), Data Connector (materialized sync), or Live Dataset (read-through). Ask about freshness requirements, source system type, and whether data must be stored inside CRM Analytics.
2. For External Data API: Construct the metadata JSON schema first, referencing the field types supported by CRM Analytics (Text, Numeric, Date, Dimension). Do not upload data rows until the InsightsExternalData header record is created and confirmed.
3. For Data Connectors: Create the Remote Connection in Data Manager, test connectivity, then build a Recipe or Dataflow that references the connection. Set a refresh schedule aligned with business freshness needs.
4. For Live Datasets: Create the Remote Connection, then create the Live Dataset pointing at the target table or view. Build a test SAQL query to validate latency before wiring into dashboards.
5. Validate data fidelity — After any ingestion, run a row-count and spot-check query against both the source and the CRM Analytics dataset to confirm completeness.
6. Monitor and alert — Set up Data Manager job failure notifications. For External Data API jobs, build status polling into the integration layer to catch Failed states before they cause silent data gaps.

Review Checklist

Run through these before marking work in this area complete:

• Correct data path selected (External Data API vs. Data Connector vs. Live Dataset) based on freshness and source system constraints
• For External Data API: metadata JSON schema created before data row upload; all fields typed correctly
• For Data Connectors: Remote Connection tested; refresh schedule configured; materialized dataset row count validated
• For Live Datasets: query latency tested under realistic concurrency; Snowflake/BigQuery query costs assessed
• Data Manager job failure notifications configured
• No use of Data Loader or Bulk API targeting CRM Analytics datasets (wrong tool)

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

1. InsightsExternalData metadata must precede data rows — Creating InsightsExternalDataPart records before the parent InsightsExternalData header record has a valid MetadataJson will cause the entire job to fail with a schema validation error. Always create and confirm the header first.
2. Live Datasets do not cache or materialize — Every dashboard load triggers a live query against the external system. If the external system is slow, unavailable, or rate-limited, the dashboard fails or times out. This surprises teams who assume CRM Analytics buffers the data.
3. Remote Connection ≠ data upload — A Remote Connection is a credential/configuration object. Creating one does not move or stage any data. Data movement requires a separate Recipe, Dataflow, or Live Dataset that references the connection.
4. Data Connector refresh does not guarantee incremental load — By default, many Data Connector recipes perform full refresh. Implementing incremental loads requires custom SAQL filter logic in the Recipe input node, anchored on a watermark field (e.g., LastModifiedDate).
5. External Data API chunk limit is 10 MB compressed — Files larger than 10 MB must be split into multiple InsightsExternalDataPart records. Sending a single part larger than 10 MB causes a hard API error.

Output Artifacts

Related Skills

• analytics-data-architecture — for end-to-end CRM Analytics pipeline design, incremental load strategy, and dataset schema planning
• analytics-dataflow-development — for building and tuning Dataflows that consume externally ingested datasets
• analytics-recipe-design — for Recipe-based ingestion from Data Connectors into materialized datasets
• middleware-integration-patterns — for selecting MuleSoft or other middleware as the layer that drives External Data API uploads