I am importing fact and dimension tables from SQL Server to Azure Data Lake Gen 2.
Should I save the data as "Parquet" or "Delta" if I am going to wrangle the tables to create a dataset useful for running ML models on Azure Databricks ?
What is the difference between storing as parquet and delta ?
Delta is storing the data as parquet, just has an additional layer over it with advanced features, providing history of events, (transaction log) and more flexibility on changing the content like, update, delete and merge capabilities. This link delta explains quite good how the files organized.
One drawback that it can get very fragmented on lots of updates, which could be harmful for performance. AS the AZ Data Lake Store Gen2 is anyway not optimized for large IO this is not really a big problem. Some optimization on the parquet format though will not be very effective this way.
I would use delta, just for the advanced features. It is very handy if there is a scenario where the data is updating over time, not just appending. Specially nice feature that you can read the delta tables as of a given point in time they existed.
This is useful for having consistent training sets (to always have the same training dataset without separating to individual parquet files). In case for the ML models handling delta format as input may could be problematic, as likely only few frameworks will be able to read it in directly, so you will need to convert it during some pre-processing step.
Delta Lake uses versioned Parquet files to store your data in your cloud storage. Apart from the versions, Delta Lake also stores a transaction log to keep track of all the commits made to the table or blob store directory to provide ACID transactions.
Reference : https://docs.microsoft.com/en-us/azure/databricks/delta/delta-faq
As per the other answers Delta Lake is a feature layer over Parquet.
Consider - do you need Delta features? if you are just reading the data & wrangling elsewhere Delta is just extra complexity for little additional benefit.
Also Parquet is compatible with almost every data system out there, Delta is widely adopted but not everything can work with Delta.
Consider using parquet if you don't need a transaction log.
We extract data daily and replace it with the Delta file. However, it re-creates the same number of parquet files every time though there is a minor change to data. Do anyone know why it re-creates all data files?
