Forum Discussion
Data types in Delta
In the olden days of data warehousing, a developper/modeler would be careful to optimize data types (especially on large fact tables) for a variety of reasons.
I'm wondering if there's still a reason to care about this is a Fabric world.
- I see that the Data warehouse support many T-SQL types
- But these probably map to delta logical types under the hood
- Which themselves map to Parquet premitives... where the smallest type (beside the bit) is an INT32
So... any reasons to keep learning about data type optimization or should we just drop this whole topic altogether?
Well, I did offer some advice as to why we still want to optimise data types. The engine will provide the resources necesarry to process data operations based on data types and precision. The bigger the data type and precision, the bigger the resources are used to process. If you keep your data type sizes to a minimum, this will help the engine only provision what it needs.
Also, if we define our tables with data type sizes in Delta, it will stick to the data type size (e.g. cannot declare a string as 100 and then insert 200 chars in)
8 Replies
That's a great question fredforest personally I'm going to keep optimising data types even in this new world of delta and parquet. One of the best practices I worked with in synapse serverless (which the lakehouse endpoint and warehouse endpoint are built from) was optimising data types. Reason for this was that the data types of the columns being used in a workload would directly impact the amount of cluster resources was given to process the workload. The larger the data type, the more resources were provided. Also with joining data together across different delta tables, still can't beat integers.
Hi fredforest
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.
ThanksHi,
No, beside AndyDDC who shared the same question I had, I have not had any further information on the topic sadly.
Well, I did offer some advice as to why we still want to optimise data types. The engine will provide the resources necesarry to process data operations based on data types and precision. The bigger the data type and precision, the bigger the resources are used to process. If you keep your data type sizes to a minimum, this will help the engine only provision what it needs.
Also, if we define our tables with data type sizes in Delta, it will stick to the data type size (e.g. cannot declare a string as 100 and then insert 200 chars in)
Hi fredforest
Glad that your query got resolved.
Please continue using Fabric Community for any help regarding your queries.
