Advancing Spark - Give your Delta Lake a boost with Z-Ordering

One of the big features of Delta Lake on Databricks (over the open source Delta Lake at Delta.io) is the Optimize command, and with it the ability to Z-Order your data... but what does that actually do? Why would you use it?
In this week's AdvancingSpark, Simon takes us through Z-Ordering, what it is and how you can enjoy the benefits of Data Skipping!
For more info, and the Databricks demo notebook used first, see: docs.databricks.com/delta/opt...
As always, for more blogs, insights and to get in touch for consultancy & training, come and visit us at: www.advancinganalytics.co.uk/

[@dheerajmuttreja]

Hey Simon .. Great explanation with proper use snd demo

[@katetuzov9745]

Brilliant explanation, well done!

[@nadezhdapandelieva3387]

Hi Simon, I like your videos, they are super useful. Can you make some videos on how to optimize jobs and reduce the performance time or how to investigate when optimization is needed on the job?

[@kingslyroche]

good explanation! thanks.

[@nsrchndshkh]

Thank you very much

[@devanssshhh]

hey Thanks its a great video.

[@DebayanKar7]

Awesome-ly Explained !!!!

[@nayeemuddinmoinuddin2186]

Hi Simon - Thanks for this awesome video. One quick question , Do Optimize and Z-order disturb the checkpoint in case of Structured Streaming?

[@vt1454]

Great Videos Simon. One suggestion on background ribbons of slides. The ribbons on your slide templates keep moving and are bit uncomfortable to eyes. Request if this can be static

[@the.activist.nightingale]

Simon is back!!!! Thank you for this awesome video :) Could you make one explaining how we can profile a spark script in order to identify optimizing tuning opportunities ? I always fo the the Spark UI but I'm completely lost. I know one thing for sure, too much is swapping between nodes is bad news :)!

[@AdvancingAnalytics]

Oooh, ok, so a quick tour of the Spark UI and "some things to look out for" when diagnosing spark performance problems? I'll add it to the list - need to thing about what the top ones would be or it'll be two hours long! Simon

[@the.activist.nightingale]

Advancing Analytics You’re the real MVP Simon! TY!!

[@sarmachavali7676]

Hi Simon, Nice video and is useful. I have a quick question, we are replicating huge data from MSSQL Datawarehouse to Deltalake using DLT(including CDC changes) with continuous mode .As part of that, i have specified my zorder is same as primary key; Does this increases the performance of merge operation in (apply statement) or not.How can i check this performance metrics.

[@dmitryanoshin8004]

Can I have partition by date and Zorder by event name? Or partition and Z should be same columns?

[@vishalaaa1]

excellent

[@cchalc-db]

Can you share the NYTaxi notebook?

[@ipshi1234]

Thanks Simon for the great video! I'm curious if I would have to achieve Z-ordering in Delta Lake Synapse, how would I be able to? As the Optimize command is only available on the Databricks runtime? Thank you :)

[@AdvancingAnalytics]

Hey! On the file optimisation level, you could maybe achieve something similar using bucketing - but you wouldn't get the same data skipping benefits. Probably easier to just spin up a databricks cluster over the same data and use that for maintenance jobs (again, Synapse wouldn't do the data skipping part, but your files would be arranged properly) For the indexing/query performance side - Microsoft have been building "Hyperspace", which is an indexing system separate to Delta. This might be the answer for where you can't optimize tables...but it's a very early product, I've not had a go at using it yet! Simon

[@PersonOfBook]

Can you use both partition by and zorder by, on the same column or different columns. And if so, would it be beneficial? Also, why do you enclose spark.read with brackets?

[@AdvancingAnalytics]

Hey - so you /can/ z-order by a column you've partitioned on, but it'll give no benefit as your data is already sorted into those values by the partitioning! And brackets around the spark statement means you can span multiple lines without needing a line escape '\' for every line!

[@preethi7674]

In production environments, do we have to zorder the tables weekly to improve performance?

[@workwithdata6659]

Yes. You will have to z order on regular basis. And there is no guarantee that only new files will be re-written. Running optimize on big tables which get good size of incremental data can be counter productive.

[@AndreasBergstedt]

1st :)