Is there a way to target the same data extension from multiple sources overcoming deadlock conflict and timeout issues?

Question

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.

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]
UNION
SELECT E-mail as Email FROM [Source 2]
UNION
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?