cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Advice on efficiently cleansing and transforming delta table

lawrence009
Contributor

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?

2 REPLIES 2

daniel_sahal
Esteemed Contributor

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.

Jfoxyyc
Valued Contributor

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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!