Marketing Cloud Engagement Data Extension Segmentation at Scale

What are the benefits to doing this?

Improved Performance: Breaking down large queries into smaller, more manageable chunks can prevent timeouts and improve the efficiency of SQL queries, especially in automations.

Targeted Processing: You can process specific segments of your data independently, which is useful for A/B testing, phased rollouts, or addressing specific data issues.

Workload Management: Distributing the workload across multiple queries or activities can help manage server load and resource consumption.

How does it work?

_CustomObjectKey: This is a hidden, automatically assigned, and incrementing numerical identifier for each row in a Data Extension.

Modulo Operator (%): The modulo operator returns the remainder of a division. For example, _CustomObjectKey % 4 would return 0, 1, 2, or 3.

Segmentation: By using _CustomObjectKey % N = X, where N is the number of desired segments and X is a specific remainder (from 0 to N-1), you can divide your data into approximately equal-sized groups.

What should I consider before using this?

Approximate Distribution: While _CustomObjectKey is sequential, its distribution might not always be perfectly even across all segments, especially if records are frequently added or deleted.
Data Extension Type: This method is most effective with standard data extensions where _CustomObjectKey is consistently assigned.
Alternative Segmentation: For precise, mathematically balanced segmentation, consider using techniques like NTILE() or custom ranking logic if the slight unevenness of modulo-based segmentation is unacceptable.

Example application of splitting a Data Extension into 4 groups

The only requisite of creating and applying this will be the creation of each individual target Data Extension.   After these have been created, there are no other pre-requisites to applying this more performant segmentation method.

Query 1: Group 1 

SELECT *
FROM YourDataExtension
WHERE _CustomObjectKey % 4 = 0;

Query 2: Group 2

SELECT *
FROM YourDataExtension
WHERE _CustomObjectKey % 4 = 1;

Query 3: Group 3

SELECT *
FROM YourDataExtension
WHERE _CustomObjectKey % 4 = 2;

Query 4: Group 4

SELECT *
FROM YourDataExtension
WHERE _CustomObjectKey % 4 = 3;