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:
- First Name
- Last Name
- 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?