I rarely write about the data work that I’m doing, so here’s something from that part of the wheelhouse. Had a chance to rework a data pipeline that was ingesting AIS vessel navigation data and it had a few common ‘anti-patterns’ that are worth highlighting. This was in Azure ecosystem, but the principles apply across all the different cloud providers.

How things were setup:

• An Azure Event Hub ingesting AIS messages
• Each message was processed by an Azure function that would:
  • decode the message from base64 and convert it to JSON
  • flatten the json to make it easier to query
  • write it to a parquet file in the Bronze datalake layer

Subsequent pipelines would read all the parquet files, combine them and write to Silver layer and so on.

This setup is the classic example of the small file problem for data lakes. Writing a file per message means the system was generating 90-100k files per day. Using parquet as target file format was greatly exacerbating the issue as the parquet file format overhead (parquet files have a largish header describing file structure and compression metadata) wasn’t compensated by data compression as there were at most 2-3 records a file.

So we had 90-100k files added daily, roughly 9GB in size, and as is usual in datalakes we don’t delete the history, so it’s a 3 TB / year storage just for the incoming messages. Combining the 90k files for reading into Silver layer was also taking a fair bit of time.

So how do you improve this?

By trying to make files bigger :) So we switched:

• the function to append data to separate file per vessel (there’s only so many in the coordinate box we’re interested in)
• made the target file not parquet, but multi-line JSON, so that function would receive a new message, transform JSON and append it to the file it’s supposed to go to, without any extra compression overhead
• and then in the final step we read all the source multi-line JSON files and write them into a single parquet file in the Bronze layer, so that all the upstream logic remained unchanged.
• oh, and did the standard function tweaking to make it batch the messages a bit to avoid firing it too often

This change had a pretty dramatic effect:

• daily file count decreased from 100k to 1k (small coordinate box, not many vessels)
• the daily file size decreased from 9GB to a 0.5 GB
• the resulting parquet file in bronze became ~50 Mb in size (having a lot of data in a single file allows parquet to shine compression-wise)
• downstream pipelines became a lot faster as reading 1 small parquet file is so much faster than reading 100k miniscule ones

So be careful with all the modern data tools, they’re built for large scale so throwing dust at them will grind them to a halt and will also cost a fair bit along the way.