Extract, load, transform

We can just mirror your stuff to analytics.

I mean, and five years ago we were talking about, is it like ELT? Is it ETL? Where do we do the transforms? But it was like a given that you were going to pay a data engineer, that you were going to have a data engineering pipeline, that you were going to do some kind of transformation logic.

You were going to move this data somewhere.

And like,

That whole chain from raw logs to a finished report is exactly the kind of job Azure Data Factory was built to run.

You'll hear two acronyms in this space, ETL and ELT, and neither one should scare you.

ETL stands for extract, transform, load, and it's really just the order of operations.

Extract means grab the data, transform means clean and shape it, and load means put it where it needs to go.

ELT does the same three things in a slightly different order.

Once you've got the idea, the letters take care of themselves.

The building blocks, pipelines and activities.

Opening, the data is moving, but who is in control? Your sales file arrives at 7am, customer records update at 8, and the app data shows up whenever it feels like it, while someone on your team is opening spreadsheets, pressing refresh, fixing columns, and hoping the report tells the same story as yesterday.

You might think ETL and ELT are coding terms for data engineers, but they're just two different ways to order the same data handling.

The real tension here is who decides what happens first, what waits, and what happens when a step fails.

Welcome to the M365FM podcast, where we explore the people, ideas, and technologies shaping the future of work.

We're looking at fabric data pipelines, simply explained.

from raw data at the source to data people can actually use.

ETL and ELT, two routes for the same data.

Imagine you run a chain of stores, and every morning each store sends yesterday's sales file that looks close enough but not quite the same.

Many, many companies are using that.

No ETL, no ELT.

Let's use the data where it lives, so to speak.

And then the third one is ignoring the organization's capacity to act on the intelligence.

So first thing, it's ETL versus LT.

Usually, medallion architecture approach is used when we have an ELT data processing.

ELT works in such a way that we firstly load all of our data and then we're doing the transformations like we're doing in a silver or gold layer.

So it's not an ETL, it's an ELT when we talk about medallion architecture.

Then, just a small example how those layers would work.

We have orders, let's say, in our database.

And that's basically how we got into that.

It's definitely a long history and metadata has gained a lot of visibility in recent years, particularly starting around 2018 when we had the fragmentation of the ecosystem away from a lot of the monolithic vertically integrated platforms like Informatica, et cetera, and into a lot of the cloud data warehouses, the introduction of tools like DBT and ELT workflows and a lot of the investment in the various open source components that caused the knowledge of the data estate to be fragmented across all of them.

And so then there was a push to try and stitch that back together through these various metadata systems.

And I'm wondering if you can talk through some of the key pieces of friction and fragmentation that exist in the data ecosystem in particular, and some of the ways that your long history maybe shows that everything old is new again, or maybe some of the new tensions that are arising up because of the proliferation of new tools and new systems.

So first of all, there is another differentiator that I forgot to mention on the first part, which is how do you get the data lineage within each of those? And a lot of what we ask for is what we call data lineage by design.

So meaning that you have to prove where it comes from, from the design.

You can have an ETL tool, an ELT tool.

You can have a script.

So I started building this deck.

But then I found when I was talking about the architectures and I talk about things like data governance and cleaning the data and ELT versus ETL and mastering data and customers within, wait a minute, I don't know what those concepts are.

So I would start explaining those concepts before they jumped into architecture.

And I said, you know what? This would be good in the book.

And this will always ultimately believe that our dashboards are dead or something.

OK, so when it comes to the The main issue about dashboards, and I think going back to the modern data stack and just the analytics world, is that our goal as a community and where we live has been to pull data in, ETL, ELT, whatever, into my data warehouse, build my dashboards, and I am done.

Success.

And therefore, I want Power BI.