Fabric Data Pipelines - Simply Explained

0:00 Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. Opening the data is moving, but who is in control? Your sales file arrives at seven a m, customer records update at eight, 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 M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work.

0:42 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. One store writes dates as day, month, year. Another uses. Month. Day. Year. A product name might appear as coffee beans, coffee beans, or coffee beans. One killing. And someone may have sent the same order twice before. Anyone can trust a sales report, that mess needs a routine. This is where ETL starts, and while it sounds more technical than it is, ETL just means extract, transform, load three plain actions in a specific order. Extract means pull the data from where it currently lives, which might be a CSV file in cloud storage, a table in the database, a business app, or an API.

1:28 An API is simply a way for one system to ask another for information, and your data doesn't need to begin in fabric for fabric to work with it. Next comes transform, where you make the data useful by standardizing dates, removing duplicate rows, renaming unclear columns, correcting data types, filtering out test records, or applying business rules like ensuring a returned order doesn't count as revenue, or sending an order without a store ID to a review list instead of the main report. Then comes load where you put the cleaned data somewhere people and tools can query, model, and report on it in fabric. That destination might be a lake house or a warehouse, depending on what your team needs. But for now, just think of it as the place where the prepared data becomes available for analysis.

2:08 So ETL follows this order, pull the data, clean it, then place the cleaned version into its destination. That works well when you know exactly what the final data needs to look like before it lands, you receive the file, apply the rules, and only load the version that meets those rules, meaning less raw materials sitting in the target system because you've dealt with it first. But many teams now use a different route ELT. ELT stands for extract, load, transform. You still pull the sales file from the store, but instead of cleaning everything before it enters the platform, you land the original file first. Then you clean, join, calculate, and shape the data inside fabric. Why would you want the raw file sitting there? Because the original data can matter later. Maybe finance asks why Tuesday's revenue change may be a source system had a problem and you need to rerun the clean up without calling the source again.

2:53 Or maybe a new reporting need appears and the old raw data contains a field you didn't think you needed. ELT gives you a landing zone before you start changing things. It separates. We receive the data from. We prepared the data which makes it easier to trace what happened when something looks wrong downstream. You might think the choice between ETL and ELT is about which acronym sounds more advanced. But it isn't. It's about where the transformation work happens. ETL cleans before the destination while ELT lands first and cleans inside the destination platform. Both routes aim for the same outcome data. People can trust. The sequence changes based on your source, your reporting needs, and whether keeping the original data helps your team investigate and reuse it later.

3:34 Once you understand that order, the next question becomes simple how do you make every step happen in the right sequence without someone chasing files and clicking refresh every morning? What a fabric data pipeline actually does. Most teams assume they can juggle a few data tasks manually, get a file, clean it up, load it, maybe run a report. But the moment you have more than two or three steps, someone forgets something. The file sits in the wrong folder. The refresh runs on stale data. The real tension isn't about doing the work, it's about getting the order right. A fabric data pipeline solves that. It takes all those separate chores and turns them into one managed job. You define the steps, set the order, and let fabric run them as a group. Now here's where most people get tripped up. A pipeline does not do the actual data work.

4:15 Think of it like an airport control tower. The control tower does not fly planes, pack luggage, or check passports. It directs the flow. It decides which plane moves first, which one waits, and what happens when weather causes a delay. A pipeline works the same way. It does not need to copy every file or clean every row. Its job is to coordinate the work. That distinction clears up a lot of confusion. But let me say it again. The pipeline is the director, not the actor. Your sales file might need to move from cloud storage into fabric. Then it needs cleaning. After that, a report or another process may need permission to continue. Those are different jobs, but a pipeline connects them into one dependable sequence. Fabric calls each step an activity. Do not get stuck on the term. An activity is just one action. In the larger routine, you place activities on a visual canvas, connect them and tell fabric what should happen next.

4:59 Most activities fall into three plain language groups moving data, transforming data, and controlling the flow. Data movement means getting data from one place to another. The most common example is the copy data activity. You choose a source, a file, a database, or a business application. Then choose a destination. That destination could be a fabric lakehouse, which is basically your team's storage area. Inside fabric for files and tables. Together, the copy data activity can bring in a file exactly as it arrived, or it can pull records from a source system into a table. Either way, the pipeline logs that the movement happened. That sounds simple, but it removes a pile of invisible work. Without a pipeline, someone has to download a file, rename it, upload it, and message the team when it's ready. With a pipeline, the movement becomes a defined activity that runs the same way every time.

5:42 No reminders needed. Then comes transformation. The work that changes data into something people can actually use. A pipeline can start a data flow. Gen two right after the file lands. Data flow Gen two uses Power Query so you can clean columns, filter rows, change data types, and join data through a visual interface. A pipeline can also start a notebook where a technical team writes custom code for larger scale processing or logic that does not fit neatly into visual steps. You do not need to write that code to understand the pipeline's role. The pipeline simply tells the data flow or notebook when to run, then waits for the result. So the pipeline coordinates the action, the data flow or notebook does the transformation work? The third group is control flow, and this is where a pipeline starts to feel less like a simple scheduler and more like a system with real rules.

6:25 Control flow decides the order of activities, pauses when something needs to finish, sends work down different paths, passes details from one activity to another, and reacts when something fails, say the file copy finishes successfully. The pipeline can then start the data flow. But if the copy fails because the source connection drops, the pipeline takes a different path. It stops. The next step records the error or starts a separate action designed for that problem. You are not building a pile of unrelated tasks. You are building a sequence with decisions. Fabric gives you a visual low code canvas. For this. You drag activities onto the screen and connect them with lines. Each line represents a rule. This step runs. After that step succeeds. This other step runs. If something fails. It is a practical way to see the routine before you trust it with daily data.

7:08 The canvas matters because data work gets confusing when the logic only lives in someone's head. When the steps sit on the canvas, another person can open the pipeline and see the entire process. Data arrives. Data moves. Transformation starts. Then the next task continues. Now here is a common trap. People think a pipeline and a data flow are the same thing, because both appear in fabric and both sit near your data. They are not. One handles the workflow, the other handles the data preparation. The easiest way to tell them apart is to ask one question who is doing the actual work and who is deciding when the work starts? Data flows, notebooks and pipelines. Who does which job? Here is where most tutorials lose you. They dump definitions and expect you to remember the difference. But the difference is actually simple. If you think about who is in charge.

7:48 Start with dataflow g n two. If you have used Power Query in Power BI or Excel, this will feel familiar. A Dataflow Gen2 connects to data, then lets you clean and shape it through a visual editor. You can remove blank rows, rename columns, split a field, change text into dates or numbers, join two lists and filter out records you do not need. It is a good fit when the cleanup rules are clear and your team wants to see the steps without writing code. Each change becomes part of the data flows repeatable process. Next time new data arrives, it applies the same steps again automatically. A notebook handles a different type of job. It is where people write code, often with spark. When the transformation needs custom logic, larger scale processing, or something that does not fit neatly into a visual clean up step.

8:28 That might mean combining a large number of files, applying a complex calculation, or building a process that a data engineer needs full control over. Do not treat a notebook as a mysterious black box. Think of it as a specialist workspace. Dataflow Gen2 handles common cleanup jobs. Visually. Notebooks handle code based work. When visual steps are not enough, the pipeline sits above both of them. It coordinates the work. Here's the head fake. You might think a pipeline replaces a dataflow because it can include a data flow activity. It does not. The pipeline tells the data flow when to run, waits while it runs and decides what happens after it finishes, the data flow still does the actual data cleanup. The same is true for a notebook. The pipeline does not write the spark code or decide how the code works. It starts the notebook at the right moment, tracks whether it succeeded, and then moves to the next part of the routine.

9:13 Picture a simple sales process. First, a copy data activity brings the supplier's daily files into the files area of a fabric lake house. The original files stay there as the received version. Next, the pipeline starts. A notebook that reads those files, applies the business rules, and writes the results into tables. Those tables are likely Delta tables, a stored table format built for reliable data work in the lake house. Instead of leaving data as loose files that every report needs to interpret again, you place it in a table that fabric can query and analyze more easily. That table can then feed your reporting layer. The pipeline can wait until the notebook finishes, then start the activity that refreshes the reporting data. One process in the right order. Bring in the source files, prepare the data, write usable tables, then update reporting.

9:54 This division of work keeps the solution understandable. Dataflow Gen two handles visual preparation. Notebooks handle custom code pipelines manage the sequence across both. Now that the roles are clear, let us follow one practical pipeline from a raw data file to a table. Your reports can actually use a beginner pipeline story. From raw file to usable table, your supplier drops a sales file into cloud storage every morning. That file holds yesterday's orders, products, quantities, discounts, and store locations. Your reporting team needs it before the day begins, but the file can arrive late, contain a bad date, or have a column name that changed without warning. The real tension here isn't about format, it's about trust. How do you build a process that works even when the input is messy? The first step is simple copy data. Move that raw supplier file into the files area of a fabric lakehouse.

10:37 Don't try to polish it yet, just receive it and store it safely. That raw landing area matters more than you might think. You're keeping the original exactly as it came in. If someone asks why a number changed, you can compare the final table with the exact file the supplier sent If the cleanup step fails, you don't need to request the file again or pull it from the source a second time. The received copy is already inside fabric, ready for another attempt. This also separates two questions teams often mixed together. First, did we receive the data? Second, is the data ready for reporting? A successful copy answers the first question. It doesn't promise the second one yet. Once the file lands, the transformation activity can begin. You might use a Dataflow Gen two for visual cleanup or a notebook if your team prefers code. The activity reads the raw file.

11:18 Checks the columns, standardizes dates, removes duplicate rows, and ensures numeric fields like quantity and revenue are actually numbers. Imagine one supplier file calls the product field product name, while another calls it product name. The transformation step can bring both into one standard column name. It can reject rows with impossible dates, flag records with missing store IDs, and apply the rule that canceled orders don't count towards sales. After that work finishes, fabric writes usable tables into the lakehouse. Those are the tables your reporting process can trust. The raw file still exists untouched in the files area, while the clean table becomes the version built for analysis. You're keeping the evidence and producing the working result. But here's the twist order matters. The transformation must not begin while the file is only halfway copied, and reporting must not refresh while the table still holds yesterday's data.

12:04 That's where the connection lines between activities do real work. When you connect one activity to another, you choose the condition that allows the next action to start on success means run the next activity only if this one finished correctly. In our sales example, transformation starts only after copy data succeeds. Then the table building step can continue only after transformation succeeds. That's the normal path, the green path if you like. But dependable automation also plans for the bad path, say the supplier's cloud storage connection. Times out. If the copy data activity can't retrieve the file, the pipeline should not continue into transformation. There's no file to clean. It also shouldn't silently leave yesterday's report in place and act like everything worked. That's how teams start making decisions from stale data without realizing it.

12:46 Instead, you connect an on fail path to an error handling activity. That path might record the failure, send a notification or trigger a separate action that tells the right person exactly which step stopped. The important part is that the failure has a defined root fabric. Gives you for common decision paths on success, on fail, on completion, and on skip on. Success is the happy path, the prior activity finished correctly so the next activity can continue on. Fail handles a problem the earlier activity didn't complete, so this path can report the issue or start a recovery. Step on completion runs after an activity ends. Whether it succeeded or failed, you might use it for a final logging task that needs to record every attempt, good or bad, on.

13:26 Skip applies when fabric doesn't run an activity because the conditions for it weren't met. For example, if the copy activity fails, the transformation activity may be skipped because it's on success condition. Never happened. A skip path can help you handle that outcome clearly instead of leaving it unexplained. Now, not every failure deserves an immediate escalation. Some are temporary. A network connection may drop for a few seconds. The supplier system may be slow to respond. For that, configure retries on the activity, a retry tells fabric. If this step fails, wait a set amount of time and try again. You choose how many attempts it gets and how long fabric waits between them. That gives a temporary issue a chance to clear without someone stepping in at six a m. Retries don't fix every problem.

14:07 They won't repair a missing file or correct a changed password. Still, they stop a brief connection issue from becoming a full pipeline failure. You also need a timeout. A timeout is the guardrail that says an activity can't run forever if the supplier file normally copies in two minutes, but it has been running for an hour, something needs attention. Set a sensible limit so fabric stops the activity, records the problem and sends it down the failure path instead of letting it hang until someone notices. At this point, the pipeline can receive a file, keep the original clean the data, create a usable table, and respond when something goes wrong. It works once. The next question is how you make that routine run again tomorrow without rebuilding it. Running the same pipeline without rebuilding it. You've built the routine. Now you need to decide how it starts.

14:47 An on demand run is the simplest option. You select run when you're testing a change, checking a new source connection, or handling a one off request. Maybe finance needs a file processed now rather than tomorrow morning. An on demand run gives you that control without changing the normal routine. Most daily data work needs a schedule. In fabric, you can set a pipeline to run by the minute, hour, day or week. A store sales pipeline might run every morning after the supplier normally sends its file. A support dashboard might need a refresh every hour. The point isn't to schedule everything as often as possible. It's to match the schedule to when people need the data and when the source data is ready. You can also use an event trigger instead of looking at the clock. Fabric waits for a new file arriving in a data lake. When the file appears, the pipeline starts.

15:28 That works well when file arrival times change from day to day, but you still want the next step to begin as soon as the data lands. Now, repeating a pipeline doesn't mean copying and pasting it every time the file name changes. This is where parameters help. A parameter is information you give the pipeline when it starts, like a file name, folder, path, date, or environment. Think of it as a blank space in the routine that receives a value when the routine runs. Say your sales files follow a pattern. Sales twenty twenty six zero eight twenty five V then sales twenty twenty six zero eight twenty six V then the next day is filed. You don't need a separate pipeline for every date. You build one pipeline with a date parameter when it runs. That parameter tells fabric which days file to process. The design stays the same. The input changes.

16:10 Variables look similar, but they have a different job. A variable lives inside one pipeline run. You might store the time the run started, the current file name or a temporary count. A parameter enters the pipeline from outside. A variable holds information while the pipeline is already working. This becomes useful when one pipeline starts. Another fabric lets you nest pipelines, where a parent pipeline starts smaller pipelines and waits for them to finish. Your parent pipeline might manage the full daily routine. One. Smaller pipeline processes sales. Another process is inventory. A third updates customer records. The parent pipeline decides the order and waits for each part. That depends on the earlier one. That keeps a larger process readable rather than forcing every task onto one crowded canvas. But automation only earns trust when people can see what ran, how long it took, and exactly where it stopped.

16:54 Monitoring errors and the first build. You might think the hard part is building the pipeline, but the real work starts when you need to know what it did. Every time a pipeline runs, fabric gives it a unique run ID, you can treat like a tracking number, letting you trace one specific attempt through the system even when the pipeline runs daily. Inside fabric, you can inspect activity status, run history, timing and output. You see exactly which activity succeeded or failed, how long each step took, and the details it returned. That changes the conversation from the report looks wrong to the copy activity failed at this time and this was the error. Nested pipelines don't hide that detail because from a parent pipeline run you can drill into the pipeline it started and inspect the activities inside it. So if the inventory process failed but sales completed, you can find the exact branch instead of guessing across the entire routine.

17:40 Start smaller than you think. You need to build one copy data activity. Choose one destination. run it, verify the result, then add the next part. A small working pipeline teaches you more than a large diagram full of untested steps. Test both paths before you schedule anything. Confirm that the success path starts the right next activity and then deliberately tests the failure path. So you know what people will see when a source file is missing or a connection can't respond. Use names that explain the job. Copy. Daily sales file tells the next person far more than copy data. One. Add descriptions where the team needs context. Set retry counts for temporary issues and choose timeouts that fit the task instead of accepting a long default without thinking. ETL and ELT describe the order of data work and a fabric pipeline makes that order visible, repeatable, and traceable so the routine doesn't disappear into someone's morning checklist.

18:28 Conclusion build the routine before you automate it. ETL and ELT tell you the order data should follow, and fabric pipelines make that order run again without someone rebuilding it by hand. Subscribe to M365 FM for practical fabric, power platform and AI guidance that helps you shape the future of work. One working process at a time.