Scenario:
I have a Source which maintains the transactions data. They have around 900 columns and based on the requirements of the new business, they add additional columns.
We are a BI team and we only extract around 200 columns which are required for our reporting. But when new business is launched / new analysis is required, sometimes users approach us and request us to pull extra columns from the source.
Current Design:
We have created a table with extra columns for future columns as well.
We are maintaining a 400 column table with the future column names like str_01, str_02...., numer_01, numer_02... date_01, date_02... etc.
We have a mapping table which maps the columns in our table and columns in Source table. Using this mapping table, we extract the data from source.
Problem:
Recently, we have reached the 400 column limit of our table and we won't be able to onboard any new columns. One approach that we can implement is to modify the table to increase the columns to 500 (or 600) but I am looking for other solutions on how to implement ETL / design the table structure for these scenarios.
I suppose your additional columns are measures, not dimensions. So you can keep the dimensions in the individual columns and include them into sort key, and store measures in JSON, accessing them whenever you need. Also if you can distinguish between frequently used measures vs. occasional you can store the frequently used ones in columns and the occasional ones in JSON. Redshift has native support for extracting the value given the key, and you also have the ability to set up Python UDFs for more complex processing.
ETL for a frequently changing Table structure