Flattening VARIANT column.

[ABINASH]

Hi Team, I am facing an issue, i have a json file which is around 700kb and it contains only 1 record, so after reading the data and flattening the file the record is now 620 million. Now while i am writing the dataframe into delta lake it is taking 24 hours to load the data. Could you please suggest a way where i can optimize and decrease the write time. also we are trying using VARIANT data type but stuck in flattening as normal flattening function made for json/struct is not working.

[samshifflett46]

Hey @ABINASH, The JSON file being flattened to 620 million records seems like the area of optimization would be to restructure the JSON file. My initial thought being that the JSON file is extremely nested which is causing a large amount of redundant information within the flattening. I would try finding the highest cardinality column in the flattened table and then extracting that data from the JSON file before flattening it. You could do this for a number of columns and simply have a foreign key between two tables that can be joined back together at a later period.

Could you present a snippet of the JSON file?