Dynamically Union Tables Together
The Scenario: I'm creating a job that is intended to be iterated through for a number of source tables....so ONE job that can be run over and over again and work to pull over each source table into an ODS in redshift. Our data is sharded, so as part of that process, for each table one step needs to union 5 shards together into a final table.
The Question: How do I union tables together dynamically so that the union works on each iteration of source table that i'm trying to pull over. For example, if the first iteration of my job is pulling over the 'clients' table, i need the process to union all 5 shards of the clients table, but then when the iterator moves on to pull over the 'sales' table, i need it to union together the 5 shards for the sales table. Using the multi-table input component, you have to specify which columns to select for the union - which wont work since each iteration (for each table) will have a different set of columns. I think that component would work perfectly if i didn't have to specify which columns need to be part of the union - if there was just an option for "all columns". Anyone run into this before?