0

I have to implement a Marketing Cloud Solution that will have millions of records in a single Data Extension (DE). Most DEs will have 20-30 million records with some DEs going over 100 M. I know how to write a query following the best practices, but I'm not sure that MC can work with so much data.

Taking a simple example with DE A having 20M records and DE B having 30M records. A flag (checkbox field) is updated in DE A by API calls for all records and all records from DE A are in DE B. DE B has the same checkbox and needs to be updated daily based on data from DE A. Can a simple Update query process all those records and update DE B before reaching the 30min limit?

It's even possible to process this many records? To do a simple update on a 30M record DE?

  • If the answer is 'Yes', what happens if I need to do a simple Join between DE A and DE B and update the result in DE C? It's still safe to assume the limit will not be reached?
  • If the answer is 'No', what is a way to process so many records in MC? I know it should involve multiple DEs and Queries, but I have no idea how the data can/should be split and put back together. Can you please provide some information?

Similar question for SSJS activities. From what I know it's not possible to process this many records using SSJS, but please let me know if I'm wrong or if you have any idea about how I can split the data to be able to process it in SSJS.

And before you ask, yes I tried talking to Salesforce about this, but to no avail. Salesforce is avoiding a clear response on this subject, all they could tell me was to follow best practice and everything will work, but I'm not so sure 'best practice' will be enough taking into consideration the volume.

If you ever faced this kind of problem or if you know how to work with large volumes of data in MC, please tell me how you did it and what was the approach.

Thank you!

Improve this question

1 Answer 1

Reset to default
3

Generally speaking, none of the SFMC APIs are built for bulk data operations -- that includes the platform languages -- AMPscript and SSJS.

Query Activities are the most performant option for dealing with that many records.

The primary constraint any methods is the 30 minute timeout window. All of the solutions I've seen at this scale involve breaking the operations into steps that will consistently perform their part in under 30 minutes.

For Queries -- using _customObjectKey is my preferred method. That is outlined here in this answer. The idea with this is to break up the volume by processing it in groups, where the groups are assigned using an indexed column. The _customObjectKey value is semi-sequential, so leveraging the modulo operator to split the source is pretty straightforward and performant.

For Script Activities -- I wouldn't even try processing that volume. If you're undeterred, you can limit the run time using @Gortonington's method outlined here on his blog post and use an update to keep track of processed rows, then you can essentially stack copies of the Script Activity that only process the unprocessed rows until complete.

Improve this answer
1
  • 3
    Agree 100% with the above, and would like to add a note on the importance of the right indexes on Data Extension. E.g. when joining two DEs, OP needs to ensure the fields joined on are indexed. This can be easily done by making them Primary Keys. Indexing improves the processing time significantly.
    – Lukas Lunow
    Mar 2 at 21:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .