topic Re: Advice on efficiently cleansing and transforming delta table in Data Engineering

Advice on efficiently cleansing and transforming delta table

lawrence009 — Tue, 20 Dec 2022 22:04:13 GMT

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?

Re: Advice on efficiently cleansing and transforming delta table

daniel_sahal — Thu, 22 Dec 2022 13:15:46 GMT

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.

Re: Advice on efficiently cleansing and transforming delta table

Jfoxyyc — Thu, 29 Dec 2022 07:42:05 GMT

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.