Best Practices for Batch Data Transforms

Plan and Test Before Scheduling in Production

Validate your design before deployment and update transforms after major changes.

• Start with smaller data volumes. Validate transform logic on a representative subset before scaling up.
• Test at production scale in a sandbox. Before scheduling in production, test your transform in a sandbox first, especially when you're approaching the recommended limits on joins or total nodes.
• Revisit transforms after major changes. Data growth, new sources, schema updates, or changes to match rules can push your transform beyond its original design assumptions.
• Keep each transform focused. Break up large transforms into smaller ones and run them in sequence, with each transform's output feeding the next. Several smaller transforms typically perform better than one monolithic transform with many joins, wide records, and large aggregations.

Keep Your Data Narrow

Processing only the data you need minimizes resource usage and speeds up your transforms.

• Select only the columns you need on each input node. You can always add more later. The fewer columns your transform processes, the faster it runs.
• Pass only required fields into joins and aggregations. Drop unused columns as early in the pipeline as possible.
• Avoid very wide outputs. Send only the fields you actually need to the target DLO or DMO.

Filter Early and Aggressively

Filtering reduces the volume of data flowing into expensive operations like joins and aggregations.

• Add filters before joins. Immediately before every join node, add a filter node to remove records with null or empty join keys. See the example below.
• Add filters before aggregations. Before an aggregation node, add a filter node that removes invalid or out-of-range records, such as bad dates, unexpected statuses, or impossible amounts.

Example filter expression for excluding empty join keys:

trim(customer_id__c) != '' AND customer_id__c IS NOT NULL

Handle Nulls and Empty Values Consistently

Inconsistent treatment of empty strings, whitespace, and sentinel values like 'N/A' or 'UNKNOWN' can cause unpredictable join behavior.

• Represent missing info in one way. Null is the most common. Convert other variants to your chosen representation before join nodes.
• Use default values when necessary. Substitute defaults like 0 or '' only when that's semantically meaningful for your data. Otherwise, leave values null and exclude them with filters.

Note

Don’t compare a field to null using =,!=, or <>. These comparisons don’t match null values, so rows with a null in that field will be silently excluded or included when you don’t expect it. Always use field ISNULL(field) or field ISNOTNULL(field) instead.

Understand Which Nodes Do the Most Work

Some nodes do significantly more work than others. Reducing how many heavyweight nodes you use, and how much data flows through them, has the biggest impact on performance.

Use Well-Defined Join Keys

Poorly defined join keys are one of the most common causes of slow transforms and memory failures.

• Use fully qualified keys. Apply fully qualified keys on all primary and foreign key fields involved in joins, so keys are interpreted consistently across sources.
• Watch for duplicates on primary keys. When the same key appears multiple times on both sides of a join, the result grows multiplicatively. If a key appears 100 times on each side, an inner join produces 10,000 rows for that key alone. This is known as an exploding join.
• De-duplicate or aggregate before joining. When uniqueness can't be guaranteed at the source, de-duplicate or aggregate before joining.

Watch for Data Skew

Data skew happens when a single key value appears far more often than others. Common examples include a "catch-all" account, an 'UNKNOWN' segment, or a default ID like 000000. The records pile up unevenly during processing, which can exhaust memory and cause your transform to fail.

Rule of thumb: If any single join key value accounts for more than 5–10% of your rows, treat it as a special case.

Mitigations:

• Process outlier keys in a separate transform. For example, handle 'UNKNOWN' IDs in their own transform.
• Summarize high-volume data down to the level of detail you actually need before joining it to smaller reference or lookup tables.

Recommended Limits Per Transform

These are guidelines, not hard limits. Transforms can exceed them, but doing so should prompt extra performance testing.

If you need more joins than recommended, consider pre-joining small reference datasets upstream or persisting intermediate results to a DLO.

External Source Considerations

External sources behave differently under load depending on type.

• Accelerated external sources behave like local DLOs. Data can be processed in parallel, which scales well to large volumes.
• Live external sources are processed as a single stream rather than in parallel, which can lead to memory issues with large volumes.

If you're working with large datasets from a live external source, consider whether an accelerated source is a better fit.