The problem that we see is there is no way from Matillion to execute the .SQL file that they produce in order to create the proper schema in Redshift. We have already seen differences month-to-month in the schema, so it's important to be able create the monthly tables in Redshift according to the Create statement they produce.
Any ideas/suggestions are welcome.
2 Community Answers
Ian Funnell —
You should be able to execute the .SQL file from an SQL Script component. The generated SQL contains a CREATE TABLE statement and a COPY (i.e. a bulk load).
Other alternatives might be to use the Matillion S3 Load Generator on the file, or to access the files through Athena/Spectrum. But if the schema keeps changing like you’ve observed then it will be best to use their generated .SQL file.