Best Approach for Handling ETL Processes in Databricks

[itsmejoeyong]

I am currently managing nearly 300 tables from a production database and considering moving the entire ETL process away from Azure Data Factory to Databricks.

This process, which involves extraction, transformation, testing, and loading, is executed daily.

Given this context, I am unsure whether it's more efficient to:

1. Create 300 individual notebooks or Python scripts—one for each table—providing great isolation and easier debugging if something breaks.
2. Implement a single script with a loop that processes all tables, potentially simplifying management but increasing complexity in debugging.

My questions are:

1. Which approach would you recommend in this situation?
2. Are there any better alternatives that I might be overlooking?
3. Is there a real benefit over .py scripts vs notebooks? I'm considering sticking to notebooks as I find it easier to debug (can run things cell by cell) for any newbies we might be onboarding in the future.
4. Is it optimal to create very long loops in Spark/Databricks?

Additional context:

• Data is around 50GB.
• We're using a Standard spark instance on Azure.
• We're writing onto ADLS Gen2

Thank you for your insights!

[Brahmareddy · Accepted solution]

Hi,

Instead of 300 individual files or one massive script, try grouping similar tables together. For example, you could have 10 scripts, each handling 30 tables. This way, you get the best of both approches—This way you will have a freedom of easy debugging without having too many files to manage.

Start with Notebooks and once everything’s running smoothly, consider converting your notebooks into .py scripts. 

One more tip - look into using Delta Lake in Databricks. It makes managing your data easier and more reliable.

Give a try.

[itsmejoeyong]

Thank you Brahmareddy!

Not too sure why I never thought of that 🙄!

[Brahmareddy]

You are welcome, Joeyong!. Good day.