We have a requirement where in there are 20+ sources that are bringing in data to marketing cloud through API, we want to use a single data extension and use the same to configure a journey.

I have the following solutions to handle this request but the business team isn't willing to consider any of these due to resource crunch on the data end

Solution 1: Simple and straight forward Create a number of data extensions for each source and use a SQL activity to merge all the de's to one single data extension. The business do not want to go by this route as it involves maintenance.

Solution 2: FTP approach I have requested the team to check if there is any possibility to batch the API calls and drop final merged file into FTP.

I wanted to check if there is any other way to handle this. Multiple files writing to same data extension at the same time.

  • You could create an automation with non concurrent script activities that run after each other. Thats a standard functionality of salesforce that scripts / activities only start after the last one has been successful/processed. Could you also write to that single dataextension with sql queries that will be processed one after each other or do you have to use a bigger query because the functionality cannot be solved in smaller queries? Do you have a time limit or something? Why does your company regards solution 1 a big maintenance (i regard this as quite common) Jun 12, 2019 at 8:46
  • I would recommend doing this consolidation outside SFMC and then pushing the data in as a single source to SFMC. To keep it inside the SF family, you may want to check out Datorama (salesforce.com/products/marketing-cloud/marketing-intelligence) which is designed for this specific purpose. Or you could even look at building a middleware solution to combine everything received into a scheduled file drop or bulk API to keep it a clean integration. Jun 12, 2019 at 13:24
  • @JohannesSchapdick - I cannot create an automation in this case as the data is coming through API's. If it is a file drop the automation with non concurrent script activities would have been a ton of help. No, we can't query the data as it resides outside marketing cloud. No time limit here, the api call runs at any moment as soon as the event took place. The company would like to minimize the use of Marketing cloud in case if there are any changes to the requirement in the near future. Thanks for your suggestion.
    – reddy
    Jun 12, 2019 at 14:14
  • @Gortonington - thanks for your reply! I was actually looking for a middleware solution. Bulk API sounds interesting but have no idea where to start on this, can you please share any solution that may apply for this scenario. Thank you!
    – reddy
    Jun 12, 2019 at 14:20
  • By bulk API, I meant APIs that include multiple entries in the payload for example: developer.salesforce.com/docs/…. I would highly recommend doing a CSV drop to an FTP if possible rather than API for large imports. This will also keep your solution agnostic and not dependent on SFMC. Jun 12, 2019 at 14:46

1 Answer 1


I suggest making the different sources write data to different data extensions, so you have one data extension for each source.

Then, you could get data from different data extensions with a single SQL query using UNION or UNION ALL and write the results to one target data extension. This query could then be scheduled to run multiple times a day or how often is relevant to keep your data up to date.

Note that for this to work, the data you get from the different sources needs to be in the same format. It doesn't mean the data in each data extension needs to be in the same format, source one can have different columns than source two, but the columns retrieved from the different sources need to be in the same format.

Let's look at a simplified example. Let's say we have 3 sources of contacts, and we want to get one data extension with all contacts.

The data extensions have the following fields:

Source 1:

  • First Name
  • Last Name
  • Email

Source 2:

  • Name
  • E-mail

Source 3:

  • First Name
  • E-mail Address

Let's say we are only interested in the email for those contacts. Our target data extension has one Email column.

We could then make an SQL query that looks like this:

SELECT Email as Email FROM [Source 1]
SELECT E-mail as Email FROM [Source 2]
SELECT [E-mail Address] as Email FROM [Source 3]

Using UNION would remove duplicate emails. Alternatively, you could use UNION ALL to keep duplicates.

Note there are also solution out there, like DESelect, that make it very easy to merge data from different sources and build the kind of complex queries one would get by merging data from 20 sources. The recurring execution of those queries can also be scheduled within that tool.

Could you tell us what the concern is regarding maintenance in your solution 1?

You must log in to answer this question.

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