Support for large union-all insertion?

[philippefutureboy]

Hello there!

I have a use-case that I would like to migrate from custom Airflow code to dbt code.

Use Case

I have N source tables with the same table schema (same columns):

client_1.my_table
client_2.my_table
...
client_n.my_table

Which I want to transfer to a single table with row-level access for external access by BI tools:

analytics.my_table

Current implementation

My implementation is to loop through each of the datasets configured (client_1 ... client_n) and do a INSERT ... SELECT on each of the tables concerned. This solution was chosen over a N times UNION ALL query for performance reasons.

The question

What would be the best way to reproduce this behaviour in dbt?
I know dbt by default wraps queries with a create table statement, so I'm wondering if there's an alternative.