For anyone using a pipeline like source data -> S3, Specrtrum to query S3 in Redshift, and DBT to materialize queries from source data into meaningful tables, what do y'all use for managing the schemas for your source data in S3? Do you use Glue? How do you set these up (just the AWS UI, Terraform, something in your meltano project, etc)? I haven't used Spectrum but I have used Athena, and I suspect in both places you have to run

MSCK REPAIR TABLE

when new partitions are created - do you let Glue handle it or do that on a cron job (as part of meltano or external)? It seems like since Singer exports the schema with the data, it could be managed by a

target-s3

that goes ahead and sets up table schemas and refreshes partitions after the data is loaded but I haven't found one

There’s been a few discussions similar to this on here in the past.

I think first Meltano needs to agree on a standard s3-target, as you say

Sounds like it's time to create an issue about adding OOTB support for one (or more) of the S3 targets so that we can compare the options (https://github.com/vitoravancini/target-s3, https://github.com/faumel/target-s3-avro, https://github.com/transferwise/pipelinewise-target-s3-csv) and decide which provides the best starting point to support multiple file types and manage schemas/partitions! Would either of you like to create the issue and kick off the discussion?

Happy to do so, @douwe_maan!

Yep

Thanks, y'all! I added some thoughts to the issue. I'd want to get a good understanding of the whole workflow before making a recommendation on an OOTB s3 target but we'd potentially be willing to contribute one if there isn't a good one available