I have more than 2 sources with the same db schema, done the EL part and moved the data from sources to the same target. Now I want to merge these schemas, append the same tables into one:

- schema_from_src_a: 
  - table_1
  - table_2
- schema_from_src_b:
  - table_1
  - table_2
... the list goes on there are hundreds of tables, the source is a relational db, and tables has keys...

- final_schema:
  - table_1
  - table_2

Is there an easy way to achieve this, or do I have to write some sort of union all logic for each table, and generate new keys consistently ? since the original keys will not be unique across sources.

If I understand the question correctly, I might have done this by mistake once. As long as the tables (streams) are actually named the same, they will go into the same table regardless of what the source is. So it should work to define two different sources, a and b, and write them to the same destination.

haven't done any mapping before can I do something like this:

src_a:
- table_1.primary_key -> 'a_' + table_1.primary_key
src_b:
- table_1.primary_key -> 'b_' + table_1.primary_key

I have only added some quite trivial columns in mappings, don’t know any examples of actually manipulating the keys. I take it the source is a database or something?

yes, the source is a postgres instance. this looks promising: https://github.com/MeltanoLabs/meltano-map-transform/blob/main/examples/README.md I think we can manipulate the keys on the fly

Personally I'd use something like dbt and union after

For the most part I would too, unless I really knew the sources and felt confident about the mapping. Writing to the same table feels like higher risk, especially when doing the mapping with the keys.