Advice on efficiently cleansing and transforming delta table

lawrence009
Contributor

‎12-20-2022 02:04 PM

I have a delta table that is being updated nightly using Auto Loader. After the merge, the job kicks off a second notebook to clean and rewrite certain value using a series of UPDATE statements, e.g.,

UPDATE TABLE foo
  SET field1 = some_value
  WHERE some_condition_is_met

As the table grow, this step is taking longer and longer. I suspect it is scanning through the entire table each time.

Is there a way to make this step more efficient, i.e. scanning only the delta update or append?

Labels:

daniel_sahal
Databricks MVP

‎12-22-2022 05:15 AM

As the table grows it's expectable for query to take a longer time. You can either play with partitioning/table optimization (https://www.youtube.com/watch?v=daXEp4HmS-E) or increase your cluster size.

0 Kudos

Jfoxyyc
Valued Contributor

‎12-28-2022 11:42 PM

I would partition the table by some sort of date that autoloader can use. You could then filter your update further and it'll automatically use partition pruning and only scan related files.