How to use Kedro for Lakehouse ETL ?

[nthanhtung]

How to use Kedro for Lakehouse ETL ?

Lakehouse Architecture

• Lakehouse = data lake + data warehouse
• Providers: Databricks (Multi Cloud) , Synapse Workspace (Only Azure)
  https://www.youtube.com/watch?v=d7Px_MZV05I

Requirement for ETL Tool

Easy for traditional database developer, citizen developer to adopt

• Support SQL: easy to learn/code/maintain/migrate from legacy code
• Change Data Capture for incremental load
• Schema drift, Data Quality
• Linage graph for object dependency
• Parameterized, reusable objects to avoid duplicate code
• Alert, Monitoring, Log
• Failover, Retry
• Managed service: avoid headache for VM, Docker
• No code, low code UIUX: setup environment, scale up/out workload, DevOps
• Security: Private Vnet, MFA linked with organization account
• Easy to Integrate with other components in Data Platform to build end to end data pipeline in 1 tool (event hub trigger -> process data using spark -> train ML model -> generate result -> trigger Power BI refresh -> send alert Slack to user)

Available ETL Tool that can support the above requirement

Azure Data Factory

• Code free ETL as a service: https://www.youtube.com/watch?v=CZQOxPY7UuA&t=440s

Databricks Delta

• SQL support ACID for data warehouse : https://docs.databricks.com/delta/index.html
• Delta live table: https://www.youtube.com/watch?v=6Q8qPZ7c1O0&t=326s | https://www.youtube.com/watch?v=cjXkhmxi-aY&t=858s
• Auto loader: https://www.youtube.com/watch?v=kNG-5uRY4o8&t=474s
• Multi job dependency management: https://www.youtube.com/watch?v=HNqPtUQV534 , https://www.youtube.com/watch?v=1AotjGNLo3Q&t=1232s

[datajoely]

Hi @nthanhtung you ask a very good question - something which truly makes us ask what Kedro should and shouldn't be. As the product has evolved, my view is that it is best described as a 'ML engineering framework'.

What does this mean in practice? Kedro provides a mechanism for organising your Python ML process in a structured, battle-tested pattern in a data-centric way of working with batch data.

Going through your requirements, I've gone through how I think Kedro fits into the world you describe:

#	Point	Comment
1	Support SQL: easy to learn/code/maintain/migrate from legacy code	Sort of - but only to pull data outside of SQL into the Python (or Python API) world.
2	Change Data Capture for incremental load	Somewhat, via the ParittionedDataSet and IncrementalDataSet
3	Schema drift, Data Quality	Not out of the box, via custom hooks
4	Lineage graph for object dependency	Yes, 1st level (table level) not 2nd level (schema level).
5	Parameterized, reusable objects to avoid duplicate code	Yes, watch this space for how Modular Pipelines and micropackaging evolve
6	Alert, Monitoring, Log	Not out of the box, possible via hooks
7	Failover, Retry	Not out of the box, possible via hooks
8	Managed service: avoid headache for VM, Docker	Nope - It's a development framework, we want to work within the managed services
9	No code, low code UIUX: setup environment, scale up/out workload, DevOps	Kedro doesn't concern itself with infra. This topic can also mean many different things to different people.
10	Security: Private Vnet, MFA linked with organization account	See points 8,9
11	Easy to Integrate with other components in Data Platform to build end to end data pipeline in 1 tool (event hub trigger -> process data using spark -> train ML model -> generate result -> trigger Power BI refresh -> send alert Slack to user)	Kedro wants to own the hidden complexity between the "process data using spark -> train ML model -> generate result" stages. From a scheduler point of view this could be 1 node, from a Kedro point of view this could be very complex at a more micro level.

To summarise I think point 11 is the most important here - there is a lot of complexity in the middle part of that process that at a macro level is just 'a part of the ETL process', but something Kedro tries to focus on.

A mature system could look something like this:

• Scheduler (airflow/prefect/dagster etc.)
• Platform / Execution engine (Snowflake/Databricks delta/Big Query) etc.
• SQL based Data engineering - Developed in something like dbt
• ML components - Pipelines developed in Kedro exposed as a API endpoints triggered by the scheduler.
• Data reliability / observability - Provided by tools like Monte Carlo
• If you're really sophisticated, use tools like gantry.io and fiddler.ai to manage the lifecycle of models.

[nthanhtung]

@datajoely

A specific use case: enterprise lakehouse data processing using spark in production

Data layer in Databricks Lakehouse: Bronze -> Silver -> Gold : https://docs.microsoft.com/en-us/azure/architecture/solution-ideas/articles/azure-databricks-modern-analytics-architecture

Layer	Native Data Processing Tool with no/low code UIUX
Bronze: Ingest 500 ERP tables, 100 Kafka topics to staging tables	Auto Loader & Azure Data Factory
Silver: clean, deduplicate data in staging and merge into foundation tables	Notebook & delta live table ETL
Gold: calculate metrics, extract features and merge/overwrite into use case tables for ML, BI	Notebook & delta live table ETL

Support tool:

• Data Governance: Full Auto Data Linage , Glossary, Sensitive Data Tracking: Azure Purview https://azure.microsoft.com/en-us/services/purview/#overview
• CICD : Azure DevOps

1. Key features of Native Data Processing Tool : Point 1 -> 7 in the above reply
2. In this specific use case, What features of Kedro has/is better, that native tools don't have, that I should convince my client to use.
3. And if I use that features, how many working hours I can save to build a pipeline and deploy to production
4. And how much money spending for computing power I could save by gaining how much performance?
5. Kedro has a strong philosophy that production code should not be written or managed in notebooks -> but Databricks notebook has many rich features vs Jupiter Notebook (point 1 -> 7), and I did see many folks in large tech savvy enterprise use Databricks notebook to process data for production workload

Databricks in many ways has to design for the most general of purposes -> Not really

In Databricks website, they position themself as: "All your data, analytics and AI on one lakehouse platform"
They are not only target execution engine, They continuously introduce specific features to serve specific use cases:

1. Delta table with ACID, photon engine, SQL endpoint for enterprise graded data warehouse on data lake, even with row-level security defined by only using SQL
2. Delta live table ETL for data quality control, data linage, ETL workflow -> similar features to this Monte Carlo demo clip: https://www.youtube.com/watch?v=sMjmzpwPIhU&t=1520s
3. Auto loader for streaming change data capture automatically
4. Even create dashboard visualization directly in databricks
5. ....etc....

Same for Synapse Workspace: build specific features, combine all data products including Spark, Data Factory, SQL DB, Power BI, no-code Auto ML, data governance into 1 place
Databricks/Microsoft both have open architecture for external tool, given the native tools is not sufficient for the use cases

Vendor lock issue -> Not really

"Vendor lock-in is when someone is essentially forced to continue using a product or service regardless of quality, because switching away from that product or service is not practical."
"In economics, vendor lock-in, also known as proprietary lock-in or customer lock-in, makes a customer dependent on a vendor for products and services, unable to use another vendor without substantial switching costs."

Given data platform vendor = Databricks/Microsoft

For now

1. If there's better lakehouse platform than Databricks/Synapse, better ETL tool than Azure Data Factory, Delta live table,
2. and the new tool is proven to be time saving, cost saving
3. I will still be able to migrate from old tool to new tool

In the future

1. Assumption: Enterprise become too dependent on single data platform from single vendor
2. The vendor no longer support products -> M$ have good revenue stream from this product -> unlikely to happen
3. The vendor no longer have great engineer to innovate product -> M$ has lots of cash to sponsor for R&D -> unlikely to happen
4. Given 2 = true or 3 = true -> Other players like AWS/GCP are very aggressive to gain revenue, and build better product -> they will help you to migrate away from M$

[datajoely]

I'm really sorry - but I'm not sure what you're asking at this point? Databricks and Kedro are trying to solve different problems, one very broad one very narrow.

[nthanhtung]

Hi,
In this specific use case about spark data processing
What features of Kedro has/is better, that native tools don't have, and the benefits in time saving / cost saving

@datajoely

A specific use case: enterprise lakehouse data processing using spark in production

Data layer in Databricks Lakehouse: Bronze -> Silver -> Gold : https://docs.microsoft.com/en-us/azure/architecture/solution-ideas/articles/azure-databricks-modern-analytics-architecture

Layer	Native Data Processing Tool with no/low code UIUX
Bronze: Ingest 500 ERP tables, 100 Kafka topics to staging tables	Auto Loader & Azure Data Factory
Silver: clean, deduplicate data in staging and merge into foundation tables	Notebook & delta live table ETL
Gold: calculate metrics, extract features and merge/overwrite into use case tables for ML, BI	Notebook & delta live table ETL

Support tool:

• Data Governance: Full Auto Data Linage , Glossary, Sensitive Data Tracking: Azure Purview https://azure.microsoft.com/en-us/services/purview/#overview
• CICD : Azure DevOps

[datajoely]

So Kedro is a data-centric development workflow - Databricks is the execution target/storage layer among other things

[nthanhtung]

for data-centric development workflow, there are several cloud-based managed service (no docker, VM), no/low code UI, native tools:

• Databricks delta live table ETL
• Multiple interdependent Databricks Notebooks orchestrated by ADF, or Databricks Jobs
• Multiple interdependent Synapse Notebooks/ Store Procedures orchestrated by ADF
• ADF wrangling data flow with spark