This is in some ways tangential to Meltano, but I have been working on a snowflake->SQL Server pipeline, and when sketching the SQL Server target it appeared that bulk load via azure storage would be the most efficient. Since Snowflake can dump to azure storage, it would be inefficient to pipe each record in this case. But I like the singer/meltano scaffolding. I think it would be feasible to write a tap+sink that uses records of references to the blobs written instead of records of data. Has anyone done anything like this? I’m a little stuck on the metadata part. The “record” would be something like

{'type': 'RECORD', 'stream': 'ECON_DATASETS', 'record': {'FILE_NAME': '<azure://abc.blob.core.windows.net/export/<...>.csv>', 'SIZE_BYTES': 851, 'MD5': 'b67e6ac5d0e68ffb3b4feb192bcd58f7', 'LAST_MODIFIED': 'Sun, 25 Sep 2022 14:00:52 GMT'}, 'version': 1, 'time_extracted': '2022-09-25T15:31:53.209274Z'}

, but I would like to append the actual data schema too. Especially since plain SQL Servers seems to prefer CSVs.

This is very timely! On Friday we released support for the BATCH message time in our SDK. This allows taps and targets to send files, pretty much like you describe, except under a new message type. Initially the feature supports .jsonl and .jsonl.gz formats. We went with jsonl add our first implementation because it does not require extensive configuration like csv. Jsonl is slightly less preformant, but more interoperable, and the performance/size trade-off is somewhat compensated by having support for gzip out of the gate - reducing io and network costs of the more verbose jsonl format. We will be announcing this on Monday (tomorrow)!

So basically the same tap or target can potentially receive both record and batch data? This is amazing, and quite the timing.

Yes! The way it works is basically: 1. The user configures in the tap a preference for batch messaging. 2. The target will either accept the batch messages, or fail if batch is not supported.

He is working on batch on the tap-snowflake side? If so, I’d be happy to help with a target-mssql. As mentioned I have been sketching a little based on transferwise’s target-postgres, which is quite readable. It might make sense to start with a primitive record-type implementation while the batch type matures a little.

He's working simultaneously on the snowflake tap and target together as a pair. 👍