Over the past few months, I’ve been building out a Security Data Warehouse—a centralized place where we pull in data from a variety of sources: Microsoft Defender logs, sign-in events, compliance snapshots, and more.
For most of these, we’ve built custom API connectors or pull data directly from log streams. But I ran into a recurring issue…
Not Every Source Wants to Play Nice
Some of the most valuable datasets just weren’t easy to get via code. I’m talking about:
- Reports exported as CSVs from third-party portals
- Files uploaded once a week to a secure blob
- Excel sheets emailed monthly with compliance gaps
None of these had a clean API. And honestly, the effort to build and maintain code for something that changes monthly just wasn’t worth it.
I needed something that was:
- Secure by default
- Easy to plug into my Azure environment
- Flexible enough to handle “weird” formats
- And, ideally… cheap
That’s When I Gave Azure Data Factory (ADF) a Shot
I’d heard about ADF before. I assumed it was a heavyweight enterprise tool with a learning curve. But after getting my hands dirty with it, I realized I was wrong—it’s actually a low-code, cloud-native data pipeline tool that fits perfectly into modern Azure setups.
Even better: it’s built on an ELT (Extract-Load-Transform) model, which makes a huge difference when working with security data.
Source: https://learn.microsoft.com/en-us/azure/data-factory/introduction
Quick Recap: ETL vs. ELT
Here’s the deal:
| ETL (Extract-Transform-Load) | ELT (Extract-Load-Transform) |
| Data gets transformed before it’s loaded into the warehouse. | Data lands raw in the warehouse, then gets transformed. |
| Works great for small pipelines with custom business logic. | Scales like crazy—ideal for big logs and telemetry data. |
| Slower to ingest, more rigid. | Fast to land data, flexible to process on demand. |
For security workloads—where you might be moving millions of records per day and want to preserve the raw audit logs—ELT just makes more sense.
What I Did with ADF
Once I decided to give ADF a go, here’s how I wired it up:
- Linked my SharePoint and Blob containers as sources
- Set up pipelines to watch for new files or run on schedules
- Landed those files into staging tables in our Azure SQL database
- Then used stored procedures to clean, filter, and join the data
All this without writing more than a handful of lines of T-SQL. Most of the work was done in the ADF visual designer. Bonus: I wired secrets into Key Vault, so nothing sensitive ever touched the pipeline code.
Pricing: Why It’s So Cheap You Should Just Try It
Here’s the part that surprised me the most.
Azure Data Factory uses consumption-based pricing, meaning I only pay when the pipeline actually runs. There’s:
- No server to host
- No need to scale or size infrastructure
- No licensing fees upfront
Some examples from my own runs:
| Activity | Cost (approx.) |
| One pipeline run | ~$0.001 |
| Copying 1M rows | ~$0.25 |
| Spark transformation (rarely needed) | ~$0.20/vCore-hour |
So for a weekly CSV pull and staging process? I’m paying pennies.
Why It’s a No-Brainer for Security Teams
Here’s what ADF helps me do—without writing code:
- Pull a vulnerability report from a vendor’s secure blob
- Import compliance scans from monthly exports
- Schedule ingestion so I don’t have to remember anything
- Run transformations using SQL I already know
And I can see every run, every failure, every row moved—right in the Azure portal.
You Can Try This Today — For the Cost of a Coffee
Seriously, you can spin up a test pipeline, land some data, and see it appear in your warehouse in under 30 minutes. And the cost? Less than your favorite drink at Starbucks.
If you’re working with security data that’s messy, ad-hoc, or hard to automate, give Azure Data Factory a shot. It’s become my go-to tool for getting “weird” data into our warehouse—securely, reliably, and with almost no maintenance.
Final Thoughts
Security data is unpredictable. Not everything is streamed, clean, or even structured. As someone who’s built automation, wrestled with flat files, and lived in the world of APIs—I can tell you ADF filled a real gap.
It’s not flashy. It’s not hyped. But it just works.
And that makes it my secret weapon.
