Migrating old solution to new optimal delta lake setup

JohanBringsdal — Wed, 28 Jun 2023 21:08:44 GMT

Hi Databricks community!

I have previsouly worked on a project that easily could be optimized with Databricks. It is currently running on Azure Synapse, but the premise is the same.

Ill describe the scenario here:

1. Data owners send a constant flow of JSON files into a datalake location (Gen 2 Blob Container). The files that are being sent consist individually of one business-record which is between 50 to 500 kbs per file (Very small).

2. As these files are populated on the datalake, they are in the same process partitioned down to the minute level (so, year, month, day, hour, minute), which is heavily over-partitioned.

3. The incremental loading of the incomining files work completely fine, as they are written down in parquet files (not delta) on the lake, and then processed into an SQL Table.

4. This means that a full load is currently impossible because there are millions of files, and thousands of directories to traverse.

So essentially my question is, what would be the best way to move away from this old solution, and move into a full lakehouse setup? Is there an optimal way to pre-process millions of small JSON files in a once off load to keep them in Delta in a sort of landing layer?

Ill take any suggestions with open hands!

Thanks

topic Migrating old solution to new optimal delta lake setup in Data Engineering

Migrating old solution to new optimal delta lake setup