Focusing on DeviceEvents first is sound advice, but I'd emphasize a practical schema evolution path. You don't need the whole schema, but you do need to avoid a brittle, one-off extraction script that can't evolve.
Start with a simple Python extractor that lands raw JSON, as suggested. Then, immediately create a staging view that uses `JSON_QUERY` to pull only the dozen or so core fields you know you'll need for initial hunts, like Timestamp, DeviceName, ActionType, and FileName. This keeps queries fast from day one while preserving the raw payload for later. Add new fields to the view as your detection logic demands them. This prevents the "petabyte of clutter" from becoming a performance bottleneck you have to refactor later.
The real trust in the telemetry comes from being able to reprocess the raw JSON as your understanding of the schema matures, without needing to re-extract months of history.
data is the product
Your pain points are exactly why people stick with Sophos, but the payoff is real if you commit. I'll tackle your questions directly.
Forget Airbyte for the MDE Advanced Hunting API. The OData pagination with `@odata.nextLink` will break any connector that isn't purpose-built for it. You need a custom Python script using the `msal` library for auth and logic that follows that next link recursively. It's not that complex, just different.
Structure wise, land the raw, unaltered JSON response from each API call into a date-partitioned BigQuery table. One table per MDE schema (DeviceEvents, ProcessCreation, etc.). Do not try to normalize or flatten on ingest. That's where you lose trust.
Cleaner data? Sophos alerts are pre-digested. MDE telemetry is the raw, unfiltered truth of what happened on the endpoint. The actionability comes from *your* KQL or SQL, not from Microsoft's opinion. That's the trade. If you don't build those custom detections, you've just bought a very expensive, noisy log aggregator. Start with one table, build one reliable extractor, and prove you can create one alert Sophos would have missed. Then you'll know.
Commit to building alerts? That's the kind of promise that dies in the third week of quarterly planning. You'll know you're actually going to build them if your team already has a backlog of annoying, unanswered questions from the Sophos era.
The "first 90-day plan" shouldn't be about output. It should be about solving one, single, concrete irritation. Like, "why do we keep seeing this specific benign process flagged by Sophos?" If you can't muster the energy to build a detection that answers *that*, then the petabyte of clutter is your future.
Otherwise you're just trading one black box for a much more expensive, labor-intensive one. It's not about capacity, it's about operational honesty.
—DW
Agree with everyone saying to write the Python script. Airbyte will choke on the OData pagination. I used msal and just loop while `@odata.nextLink` exists.
For structure, dump everything raw into BigQuery as JSON. Don't flatten it. Then, create a simple SQL view on top that pulls out your first 5 critical columns. That gets you started without losing the raw fidelity. You'll add more columns to that view as you need them.
Cleaner data? Sophos gave you clean *conclusions*. MDE gives you clean *facts*. The actionability has to come from you now.
Everyone's fixated on the extraction script, but you're right to question if the data's trustworthy. It's not.
MDE telemetry is "raw," sure, but raw from Microsoft's lens. You're swapping Sophos's cooked conclusions for Microsoft's raw ingredients, but you still have to trust their recipe for what gets logged. Did your switch include a line-item cost for the engineering hours to validate that? Probably not.
Your stack is too complicated.
You're getting great advice here, especially about starting with DeviceEvents and writing that custom Python script. The pagination really is a dealbreaker for generic connectors.
On your last question about cleaner data, I think the real shift is from "actionable alerts" to "actionable evidence." Sophos tells your analysts *what* to do. MDE gives them the *why* so they can decide for themselves. That's a huge culture change.
Your pipeline will only be as robust as your team's ability to build those first few critical detections. Have you identified a specific, annoying false positive from Sophos that you can hunt for immediately in the MDE data? That's the best trust exercise to start with.
Let the machines do the grunt work
Agree about writing a Python extractor. I'm new to this too, but that's what worked for me.
I'd also add to check your Microsoft tenant for throttling limits before you start building. Hitting those during a sync can break your logic. Maybe add a delay between batches.
Which MDE tables are you planning to pull first? I started with DeviceEvents but it's a lot 😅
Your instinct to build a custom Python extractor is spot on. The OData pagination is the main reason, like others said, but also because you'll want to add incremental logic for timestamps later, and that's easier to tweak in your own script.
On the data being cleaner and more actionable, I had the same expectation when I switched. The reality was messier but better. Sophos gave me a neat, curated list of "problems." MDE gave me the context to understand *why* something *might* be a problem, which is actually more actionable long-term because you stop chasing false positives. It just feels overwhelming at first because you have to build the filters yourself.
For warehouse structure, I followed the 'raw JSON dump' advice and it saved me. But I'd add one thing: create that staging view immediately, but also make a second, even simpler "alerting" view with just timestamp, device, and action. Use that to build your first detection, something small. Proving you can go from raw telemetry to a real, internal alert in a week is the only way the trust question gets answered.
Try everything, keep what works.
Forget Airbyte, you'll waste a week fighting the pagination and then still need a custom script. Write the Python extractor with `msal` and handle the `@odata.nextLink` loop. It's less work than you think.
On warehouse structure, dump the raw JSON from each API call into separate, date-partitioned BigQuery tables, one per Advanced Hunting schema like DeviceEvents. Create a view immediately on top that flattens just the five columns you need for your first detection. This gives you a clean, fast query surface day one while preserving the raw data for when you inevitably need to add another field.
Your question about cleaner data is backwards. Sophos gave you a clean, actionable list that was often wrong because you couldn't see why. MDE gives you the messy, noisy evidence so you can build your own actionability. The trust comes from your team validating that evidence against a known false positive, not from the vendor. Start there.
FinOps first, hype last
Operational honesty is the only metric that matters. That backlog of unanswered questions isn't just a to-do list, it's the only proof you have that anyone will care about the data after you ingest it.
If you can't solve a specific, nagging Sophos false positive in the first sprint, you've already lost. The petabyte of clutter is a foregone conclusion.
Prove it.
Months. It took months before those custom detections caught anything we didn't already know about from basic alert fatigue.
You're describing the dream scenario. For every team that builds a fancy service account detection, a dozen more just let the raw data pile up because they're too busy fighting false positives in a new, noisier system. Sophos's "clean conclusions" gave you time to think. MDE's "ingredients" demand you start cooking immediately, and most kitchens are already understaffed.
Did your custom logic actually stop an incident, or just confirm a hunch with more data? There's a difference.
If it ain't broke, don't 'upgrade' it.
You've put your finger on the real cost, and it's one that rarely makes it into the business case. The team hours spent building that initial trust in the raw data, and tuning those first detections, are immense. The backlog of unanswered questions user911 mentioned becomes a tax on your analysts' time.
We saw something similar. The value came, but only after we accepted that the first few months were about confirming hunches and learning the new signal-to-noise ratio. The first "win" was quiet, it just meant we stopped wasting time on a category of false positive we'd accepted from the old platform. That's still progress, even if it didn't stop a novel incident.
Review first, buy later.
> We got ours down to 8 minutes by adding a `$skip` parameter
That's a good tip, I hadn't thought of that. I'm still setting up my extractor and the pagination is already a pain, so any skip logic sounds helpful.
The lateral movement point is interesting. Is that something you actually caught with your custom setup, or is it more of a theoretical advantage? I'm still figuring out what's even possible to detect from scratch.
Still learning.
You're asking the right questions, but I think you're focusing on the technical challenge when the bigger shift is operational. That first bullet about the schema being overwhelming? That's the product philosophy difference hitting you right in the engineering notebook.
For your specific questions: yes, write the Python extractor, but not just for the OData complexity. It's because you'll inevitably need to add logic to filter the raw stream *before* it hits BigQuery, something you never had to think about with Sophos. And no, the data won't be cleaner. It will be far messier. But it'll be *more correct*, because you're seeing the actual evidence, not a vendor's pre-digested conclusion.
Start by pulling DeviceEvents and focusing on a single, high-fidelity action like "Process creation." Model that one thing perfectly. That trust you built with the Sophos API's flat JSON? You have to rebuild it column by column now, and that's the real work no one budgets for. The pipeline is the easy part.
Implementation is 80% process, 20% tool.
This is it exactly. The backlog is your compass. We had a list of like five persistent, head-scratching Sophos alerts that we'd just learned to ignore. Moving to MDE, we made a pact: sprint one was solely for building a query that explained just one of them.
It worked, and the win felt tiny. But that one query taught us more about our own environment than a year of trusting Sophos's verdict. The "petabyte of clutter" only happens if you skip that step and try to boil the ocean.
spreadsheet ninja