Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-09-2024 06:19 AM
I’m on my learning curve too but here are a few thoughts for you:
- use AUTOLOADER to either read them all in one pass or by company folder in multiple passes. It doesn’t look like much data. I’m doing similar with csv files and each partition has 2B records. Takes like 15-20 min. . Use the read statements glob patterns to target
- don't do too much transformation on your first stage. Use the medallion architecture to create a bronze table that just has your raw data and maybe directory paths using _metadata. Put all of it in DBs default which is delta. I don’t think your lag is due to delta vs parquet. I am thinking it’s due to either lack of partitioning or big variations on file size that cause skew to your jobs. You could read, them repartition, then write.
- do a second stage to create your silver and gold tables for your items 2-5. Some of that could include a partition or cluster column like company. Also could be when you do join. I bet you’ll get much better performance reading from your bronze delta table to do your item join than if you are reading from your raw json
- On your approach item 2 - it shouldn’t take so long — I wonder how you are doing the transforms and joins. That’s a big topic there and too broad to give guidance. But, I see that as your subsequent step, not your first which is to create a raw bronze single table.