Azure Data Factory - Simply Explained

0:00 Welcome to the M365 FM podcast where we explore the people, ideas and technologies shaping the future of work. Azure Data Factory is a courier service for your company's data. It picks up raw information from wherever it lives, cleans it on the way, and drops it off somewhere your reports can actually use it. There's a handful of parts inside, and once you can name them, the whole thing stops sounding like jargon. Welcome to the M365 FM podcast, where we explore the people, ideas, and technologies shaping the future of work. By the time we're done, you'll be able to explain pipelines, activities, data sets, linked services, integration runtimes, and triggers to anyone, and you'll know the honest answer to whether you should learn this or its newer sibling fabric. What Azure Data Factory actually is, as your data factory is the cloud service from Microsoft that moves your data around and reshapes it while you get on with your day.

0:44 Microsoft's own documentation calls it the cloud based service for orchestrating data movement and transforming data at scale, which really just means it moves data from one place and gets it ready in another. That single sentence is the whole point of the tool, and everything else is just detail. Look at how data actually piles up inside a company and the problem becomes obvious. There's a database over here, a spreadsheet over there, and a file share in the back office that nobody wants to touch on its own. That data means very little because raw numbers without context don't tell anyone anything useful. Before tools like this existed, teams wrote custom code to glue those sources together, and that code was expensive to build and painful to keep alive. So picture as your data factory as a delivery service with a small prep kitchen attached, it goes out and collects the data, does some cleaning and sorting along the way, and then delivers it to a place where your reports and dashboards can put it to work.

1:32 What saves you real time? Here is the automation, the collection, and the cleaning happen on a schedule without anyone sitting there clicking buttons at seven in the morning. A real example from those same docs makes this concrete picture a gaming company that collects petabytes of game logs from players in the cloud, while customer and marketing details sit on machines on premises. To get any insight, the company has to combine those two worlds, process the result on a big compute cluster, and publish it into a data warehouse where a report can finally read it. That whole chain, from raw logs to a finished report, is exactly the kind of job as your 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.

2:14 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. The first word you need is pipeline and it happens to be the easiest one. A pipeline is a logical grouping of activities that together do one unit of work. That's Microsoft's definition, and it translates to something simple. The easiest way to hold it is as a recipe where each activity is one step on the recipe card. Those steps can run one after another, or several can run at the same time when they don't depend on each other. The documentation also notes that a single pipeline can hold up to about one hundred and twenty activities by default, which is far more than you'll ever need when you're starting out, that limit exists.

2:58 So a pipeline stays readable not to box you in. Activities come in three flavors, and the names mostly tell you what they do. The first group moves data and the star. There is the copy activity, which takes data from a source and drops it into a destination. The second group transforms data and that's where you find data flows, data bricks, and stored procedures. The third group controls the flow, the activities that decide what happens next. A for each loop repeats a step for every item in a list, and if condition branches down one path or another and a lookup fetches a value that a later step can use. Hold on to the word runtime as we go, because that's the piece that does the actual heavy lifting, and we'll get to it in a few minutes. The control activities are what turn a simple recipe into something that can think for itself because they handle chaining, branching, and looping.

3:41 That's what lets a pipeline make a decision instead of marching blindly through a fixed list. You'll also meet parameters and variables along the way, and they're simpler than the words suggest. A parameter is a read only setting you hand to a pipeline when it runs and a variable is a temporary value, the pipeline can change while it works together there. How one pipeline handles many different files. Instead of you building a brand new one every time a file name changes. A quick sketch makes it concrete. Imagine a pipeline with just two activities. The first is a copy activity that pulls a messy CSV file out of blob storage and lands it in Azure SQL. The second is a data flow that trims the columns and fixes the data types. One recipe, two steps, and a job that used to be someone's Monday morning is now automatic. Linked services and data sets.

4:22 The keys and the address. Now that we have steps, those steps need to know where to read from and where to write to. That's what the next two parts handle. And this is the pair that trips people up the most so they're worth pulling apart. A linked service is the connection. Microsoft describes linked services as being a lot like connection strings, and that's the cleanest way to think about them. A linked service holds the information needed to reach something, whether that something is a data store, like a database or a compute resource that does work for you. A data set is the named view of the data itself. It points at the exact table, file, folder, or document you want an activity to use. The documentation sums up the difference in one clean line. The linked service defines the connection to the data source, and the data set represents the structure of the data.

5:02 The image that makes it click is this. The linked service is the key and the address of the building. The data set is the exact mailbox inside. You need both before you can deliver anything, and mixing them up is the source of a lot of beginner frustration. A concrete example makes this stick to move data from blob storage into Azure SQL. You create two linked services, one for Azure Blob and one for Azure SQL. Then you add two data sets, a delimited text data set that points at the blob container and folder, and an Azure SQL table data set that points at the destination table. The linked services carry the keys, and the data sets carry the addresses two of each and the copy activity. Finally has everything it needs. Integration runtimes. The engine room connections and addresses are useful, but on their own they just sit there.

5:43 Something has to actually do the work. And in Azure Data Factory that something is called the Integration Runtime. An integration runtime is the compute that runs your activity. Microsoft describes it as the bridge between the activity and the linked service. Because the activity says what to do and the linked service says where while the runtime supplies the machine that actually runs it. A handy detail is that the runtime can sit in the region closest to your data, which is how the service keeps things fast while keeping data close to where it belongs. There are three kinds, and most beginners only ever meet the first one. There's good news before we even get to them. If you don't pick anything at all, the service uses a default Azure integration runtime that works out the closest region for you.

6:24 You only step in and choose a different runtime when your data sits somewhere the default can't reach. The Azure integration runtime is fully managed and serverless. You install nothing, you patch nothing, and it scales up on its own. When a job gets bigger, it moves data between cloud services and it runs your data flows. The self-hosted integration runtime works differently. You install a small piece of software on a windows machine inside your own network, and it reaches data that lives behind your firewall, like an older database tucked away in a back room that the cloud can't see directly. If your company has on premises data, this is the runtime that gets to it. The third kind is the Azure SSIs Integration Runtime, and it exists for one specific job. When a company already runs SQL Server Integration Services packages.

7:05 This runtime is a managed cluster that runs those existing packages in Azure with little or no change. It's a way to lift old work into the cloud instead of rebuilding it from scratch. Triggers and scheduling making it run on its own. So far, we've assembled steps, connections, addresses, and an engine. What we don't have yet is a start signal, and that's the trigger. A trigger is the unit that decides when a pipeline run begins. A run is just one instance of the pipeline executing, and every run gets its own unique ID. So you can always look up exactly what happened and when. That detail matters more than it sounds, because when something breaks in the middle of the night, the run history is where you find out why. Three trigger types exist and two of them are easy to tell apart. The schedule trigger runs your pipeline on a clock.

7:46 You might set it to fire every morning at six or every Monday at nine. Think of it as an alarm clock for your data. The event based trigger runs when something happens. The classic example is a file landing in storage, and the moment that file arrives, the pipeline springs to life. This one is more like a doorbell than an alarm clock. The tumbling window trigger is the third, and it's the one for people who care about reliability. It fires on a fixed repeating window, and it remembers its state, which means it can fill in gaps from the past, retry when a run fails, and cap how many runs happen at once? The plain way to describe the difference is this the schedule trigger starts a run and moves on while the tumbling window waits for that run to finish and reports back on whether it worked. When a process truly has to be dependable, that waiting is the feature you want.

8:28 Mapping data flows, transforming without code. We keep saying transform, so it's finally time to open that box. This is the part that worries beginners the most, and it turns out to be the friendliest part of the whole platform. Mapping data flows are transformations you build by dragging and clicking. You start with a source. You add steps that change the data and you finish with a destination. There's no code to write and it all happens on a visual canvas that looks a little like a flowchart underneath. Something clever is going on. Your data flow runs on Apache Spark clusters that Azure Data Factory manages for you, and those clusters spin up when the job starts and spin back down when it ends. You never install, size or babysit a spark cluster, which answers the question every beginner asks about whether they need to learn spark to use this tool to get started.

9:09 You really don't. A quick example makes it obvious. Take that messy CSV from earlier. Drop in a source. Add a step to trim the columns you don't need. Add another to fix the data types and land the clean result in a table. Each step is a tile on the canvas, and you can preview the data at every stage while you build, so you're never guessing about what a step is doing to your numbers. One practical note will matter when we get to costs. The smallest data flow cluster is eight cores, so a data flow costs more to run than a simple copy. Powerful, yes, but not free. Azure Data Factory versus fabric pipelines. There's a question that comes up in every beginner conversation about this tool, and it deserves a clear answer. Microsoft now has two versions of this idea, and people want to know which one to learn.

9:49 The older established one is Azure Data Factory, which you set up inside Azure. It's what's called platform as a service, which means you get the pieces and you assemble them, and you pay for what you use as you go. It's mature, it's widely used. And most existing jobs today run on it. The newer one is called Data Factory in Microsoft Fabric, and Microsoft describes it as the next generation of Azure Data Factory. Fabric is software as a service, and it arrives as one connected package built around a single storage layer called One Lake. Instead of assembling separate pieces, you work inside one workspace that already holds your data, your pipelines, and your reports. The differences you'll notice are mostly about friction in fabric. Data sets go away and you connect to your data with simple connections instead.

10:30 There's no publish step, so you just save and run. And you don't manage integration runtimes at all because fabric handles the compute for you. What stays familiar is just as important. The pipeline and activity model is the same, and the expression language is nearly identical. So what you learn in one transfers to the other. Microsoft says roughly ninety percent of the activities you can use in Azure Data Factory are already available in fabric, and fabric adds a few of its own. What surprises people is that Microsoft's own guidance now tells anyone new to data integration to start with fabric, and it says the two platforms keep separate roadmaps, which means new features land in fabric first. That's worth knowing before you decide where to spend your learning time. What it costs and how to keep it cheap. Whichever version you use, someone eventually pays the bill, so the meter is worth understanding.

11:10 The first thing to understand is that the service is pay as you go. There's no fixed server to size and no big upfront commitment. Microsoft's documentation puts it plainly it's consumption based, so you only pay for what you use. Three metres cover almost everything and they map neatly onto what we've already covered. You pay per activity run, which covers the orchestration, the behind the scenes work of starting and tracking each step. You also pay for the data you move, measured in something called data integration unit hours, which is really just a way of charging for the size of the copy and how long it takes, and data flow transformations billed by the V Corps hour, because those spin up that spark compute. We talked about the list prices and these vary by region and by agreement run roughly one dollar per thousand. Activity runs about a quarter per data integration unit hour for movement, and about a quarter per V core hour for general purpose data flow compute.

11:57 The reassuring part for a beginner is this a small daily pipeline that copies a file and loads. It will cost you pennies to a few dollars a month. Not a scary number. The thing that moves the needle is data flows, because each run spins up compute with a minimum of eight cores. So a pipeline that mostly copies stays cheap, while one that transforms heavily costs more. If you want a number before you build anything, Microsoft has a pricing calculator where you plug in activity, runs data movement, and compute hours to see an estimate. The recommended approach is to run a small test on a sample of your data, measure what it actually consumed, and then scale that up to your real volume. That turns a wild guess into a forecast you can plan around to stay in control. Three habits help keep data flows off unless you genuinely need them.

12:37 Watch the consumption view in the monitor tab to see what each run costs, and set a budget alert in Azure so you hear about a surprise before it grows into a problem. Best practices that save you pain. Later, you can build something in Azure Data Factory that works on day one and becomes a headache by month six, and the difference usually comes down to a few habits. Start with something simple. Build a single pipeline with one copy activity and prove the path works from end to end before you add anything clever. A boring pipeline that runs beats a clever one that half works. Reuse. Instead of repeating yourself. Parameters and data sets let you build one pipeline that handles many files so you don't end up copying and pasting the same logic twenty times, and then fixing twenty places when something changes. Build for failure because failures will happen.

13:18 Turn on retries and aim to make your work idempotent, which is a ten dollar word for a simple idea. Idempotent means safe to run twice without duplicating your data. This is exactly why the tumbling window trigger supports retry and backfill, because real data sources go down and real files show up late. Watch what's running. The monitoring view shows you every run, every failure, and what each one consumed. So you catch a broken pipeline from a dashboard instead of from an angry email, and keep your names clear. Future. You and the person who inherits your work has to read these pipelines. So a name that says what a thing does is worth more than a name. That sounds clever. Group related pipelines together and keep the folders tidy because a factory with forty loose pipelines and no structure is its own kind of outage waiting to happen.

13:58 One last warning it's easy to overbuild. You can create dozens of pipelines for a job that a single one would handle, and that's how both confusion and bills creep up on you. So a quick recap and a question for you. Here's the whole picture in one breath. Azure Data Factory moves and reshapes data for you. Pipelines hold the activities linked services hold the connections data sets point at the right table or file integration runtime. Supply the compute and triggers. Decide when everything runs. Data flows let you transform without writing code. Fabric is the newer sibling Microsoft now points newcomers toward, and you pay only for what you use. So the question I'll leave you with is this if you could hand one repetitive data job off to a pipeline tomorrow morning, which job would it be and what would you do with the time you got back?