topic Order of delta table after read not as expected in Data Engineering

Order of delta table after read not as expected

felix_counter — Fri, 28 Jul 2023 07:00:01 GMT

Dear Databricks Community,

I am performing three consecutive 'append' writes to a delta table, whereas the first append creates the table. Each append consists of two rows, which are ordered by column 'id' (see example in the attached screenshot). When I read in the dataframe after all appends have completed, the rows are ordered with respect to column 'id' in the following order: '1, 2, 5, 6, 3, 4'. My expectation is '1, 2, 3, 4, 5, 6', as the original data has been ordered by 'id', and the appends to the delta table happened in order '1, 2', '3, 4', and '5,6'.

Is this behavior expected?

Is there a way to obtain the same order upon read in which the data has been appended to the table?

Thanks a lot for your consideration and help.

Re: Order of delta table after read not as expected

Lakshay — Fri, 28 Jul 2023 12:52:30 GMT

@felix_counter

While fetching the data from a delta table, the order is not maintained as the data is fetched parallely. This is an expected scenario. If you want to display the data in a certain order, it is advised to query the data with the order by or sort by clause.

Re: Order of delta table after read not as expected

Tharun-Kumar — Tue, 01 Aug 2023 05:22:47 GMT

@felix_counter

Adding on to Lakshay's answer, you have to rewrite your query as

from pyspark.sql.functions import col df.orderBy(col("id")).show()

Re: Order of delta table after read not as expected

felix_counter — Wed, 02 Aug 2023 11:49:23 GMT

Thanks a lot @Lakshay and @Tharun-Kumar for your valued contributions!