Databricks SQL warehouse with inconsistent order of data

Question

We have a use case where clients are reading the data from Databricks SQL warehouse.

Client fire a query to read 1000 records per request from a table of 60k records.

Below is the sample query they use

--> select id,name,city from employee where name is not null LIMIT 1000 OFFSET 1000

When client calls 60times to read the complete data set they are seeing duplicates as though the actual data set is not having any duplicates.

What i read is when we use offset clause order-by is mandatory but we cannot ask the consumers to do that. Is there is way where we can maintain the ordering with in warehouse than asking client to update the query ?

Answer 1

Hello,

In order to maintain the ordering, you could create a view or a materialized view on the employee table that includes an ORDER BY clause :

CREATE VIEW ordered_employee AS
SELECT id, name, city
FROM employee
WHERE name IS NOT NULL
ORDER BY id;

Otherwise if you have control over the API or middleware that clients use to access the Databricks SQL warehouse, you could implement a layer that automatically adds an ORDER BY clause to incoming queries.

Also you could create a stored procedure that clients can call instead of querying.

Hope this helps

If this answer helped you, please accept it, so others can benefit from it too.

Regards