Quick question: When was the last time you wondered if you’re using the best tool for querying or formatting your Microsoft Lists? If your answer is ‘every time I hit a performance bottleneck,’ you’re definitely not alone. Today, we break down the real pros and cons of CAML, REST, and JSON formatting that every Power Platform professional should know before choosing a data strategy. Spoiler: The most obvious choice might not be the best one.Why CAML Refuses to Die: The Hidden Power of Old-School QueriesLet’s be honest—CAML isn’t winning any popularity contests. But if you’ve ever seen someone suggest tossing it out because “REST does everything now,” there’s a good chance they haven’t run up against the specific corners of Microsoft Lists that still make CAML feel like the only grownup in the room. It’s like seeing a retired engineer hanging around the factory floor, outlasting every new hire. Most pros—especially folks new to Power Platform or SharePoint—just assume REST is supposed to be the replacement for everything legacy. In presentations, REST arrives dressed in a cloud-friendly suit. Documentation after 2018 barely mentions CAML except in a footnote. Teams default to REST endpoints. Power Automate connectors wave the REST API flag. Ask around in most Microsoft 365 circles, and you’ll probably hear that “nobody uses CAML anymore.”But then, quietly, the old XML-based ranger pops up right where you need that one thing REST can barely do. We’ve all hit that point: You open a list, the UI is modern, the Teams app syncs fine, and then you try to pull a filtered report with multiple levels of AND/OR logic. You stack nested lookups, date filters, and a couple quirky calculated columns. Suddenly, REST throws up its hands and gives you either a generic timeout or an error that sends you down two dozen GitHub threads. Try expressing a query with eight different filters and some child-parent relationships. REST will let you write it, sure, but the JSON gets ugly fast—and performance drops like a rock.Not long ago, I watched a Power Platform team migrate an old SharePoint workflow—years of business logic packed into classic designer flows—over to REST-based flows in Power Automate. On paper, it looked simple: swap out CAML for REST queries, update connections, and call it a modernization win. But on day one of testing, the migration choked on a batch operation pulling data from a list containing 75,000 items. With CAML, the old flow sliced off results in neat little pages, kicked out only the columns it needed, filtered everything server-side, and shipped back exactly what the automation required. The REST-based version, running nearly identical logic, ballooned in both response size and query time. And just for fun, it blasted past the 5,000-item list view threshold, which is always a favorite trouble ticket. Tech support spent a week flipping through REST documentation, only to end up re-enabling the CAML-based query for those edge cases.Here’s what stands out about CAML: the way it lets you build queries with complex grouping and advanced filtering that REST, for all its flexibility, still finds clunky. CAML’s structure isn’t just XML for the sake of pain—it actually shapes the query so it can be parsed, executed, and optimized server-side. That’s code running in the SharePoint backend, not your browser or Power Automate client pulling tens of megabytes over the network. When you need to return only the rows that hit three different lookup values, don’t bring along every item in the list, and avoid blowing past thresholds, CAML can quietly make that happen. Try this—auto-approve a set of requests based on five nested business rules, filtering for calculated fields and workflow status, all without loading every entry. Most REST-based setups either throw you back more data than you need or require running extra filtering logic after-the-fact, burning up memory, flow time, and patience.There’s even a well-known backend story in the SharePoint world about massive lists—the real beasts, with hundreds of thousands of rows—where administrators had to script administrative reports. The rumors say that when REST API built the same report, it spun for minutes. Switch back to CAML, and suddenly the data spilled out in seconds. The magic? CAML’s XML is tailor-made for SharePoint’s indexing engine. The query goes straight into crawled indexes, skips rows that don’t fit, and doesn’t waste time serializing the entire dataset in JSON for the client to filter.It’s not just urban legend or stubborn admins refusing to learn new tricks, either. Microsoft’s own documentation—usually quick to point out “modern” options—still quietly signals that CAML queries are the answer for certain types of intricate filters. Community threads from the last year have people swapping code samples for using CAML inside custom PowerShell scripts or complex approval workflows because it simply outperforms the alternatives in more than a few situations.So is betting on CAML smart, or are you just risking future headaches clinging to something scheduled for demolition? The real answer is sneakier: CAML isn’t dead weight, and when you’re staring down advanced filters or those thorny performance edge cases, it consistently pulls its weight, sometimes better than anything REST can offer. You might even call it a secret weapon reserved for those frustrating moments when modern tools suddenly lose their shine.Which raises a bigger question. With so much power hiding in CAML, why does every vendor, every Microsoft roadmap, and most Power Platform solutions still keep steering us toward REST APIs? If the old way works so well behind the scenes, what’s really driving the shift? Let’s see what’s waiting on the other side.REST APIs: The Double-Edged Sword of Modern List AccessLet’s talk REST APIs—the darling of modern development in the Microsoft Lists world. It’s hard to miss how every new integration, extension, or Power Automate connector leans into REST. This is the playbook: Microsoft wants tools to work across the cloud, in browsers, with Teams, and on mobile, so the REST endpoints are everywhere you look. Need to plug your list data straight into Power BI for a quick dashboard? REST endpoint. Got a third-party tool promising smarter notifications or advanced reports? Odds are, they’re hitting the REST API as their gateway into the data layer. Even for developers, REST feels like a friendly handshake—everything works in HTTP, the docs are packed with JSON samples, and most programming environments already know how to speak the language. There’s a reason third parties default here. REST works almost everywhere with minimal fuss. Whether you’re connecting old-school SharePoint sites or shiny new Microsoft Lists spun up through the cloud, the REST API surface is broad enough to cover most use cases. From a cloud readiness standpoint, it’s gold. No obscure XML to chase, just well-documented endpoints and parameters, ready to slot into low-code, pro-code, or even a quick script someone hacks together before a meeting. Modern app architecture almost demands this kind of flexibility, and Microsoft’s whole SaaS ecosystem is built to support it.But under that surface, things get complicated fast. REST’s catch-all approach makes it easy for first-timers to ignore some real pain points until it’s too late. One of the first headaches is API throttling. REST’s openness means that, when your solution gets busy—or if a connector or script starts firing off hundreds of requests in a short window—SharePoint or Microsoft Lists throttle your traffic. It’s not always obvious when it’s happening, either. Sometimes, you get a helpful “429” error. Other times, queries just slow down or silently fail, stalling automations and user actions. Now add in service limits—thresholds on how much data you can return, how many items per call, and max concurrent requests—and you realize REST gives you flexibility but not always predictability.That scales right into performance during bulk data operations. In testing, REST feels slick. Spin up a list, send a query or two, it works. But production is a different animal. Picture a workflow that breezes through functional testing—200 items in a dev list, everything smooth. Then the thing goes live, and the real production list has 40,000 records. Overnight, Power Automate starts spitting out failures, flows get stuck retries, and the support mailbox lights up. The team quickly traces the failures to throttled REST calls. It’s not that your script was wrong; it’s just that REST doesn’t handle sudden volume spikes with much grace, especially when someone is looping through responses or chunking up write operations. Even a simple “bulk update” script turns unruly when each call takes longer and the system shoves back with a throttle warning.REST’s writing operations, too, can become a labyrinth when scale rears its head. Sending a few updates that pass in a basic JSON payload hits no snags, but batch writes? Multiple item updates using REST need careful orchestration—multiple requests, careful attention to concurrency, and error handling that can retry once throttling kicks in. CAML, for all its syntax woes, let you slice and dice server-side, submit a query built for SharePoint’s internal engine, and get exactly the result set you wanted with less client effort. With REST, you’re often left piecing together results on the client side, combining partial responses, and spending more time marshalling data in browser memory or inside cloud flows.That’s the real tradeoff here: REST shifts more processing to the client. So now, instead of SharePoint quietly handling filtering, paging, or joining list data server-side, you’re sending a raw list of items back to your automation or script, then filtering, joining, or formatting after the fact. For some solutions, that’s fine—especially when you want to tap into Power Automate, Teams, or even third-party c
Become a supporter of this podcast: https://www.spreaker.com/podcast/m365-show-podcast--6704921/support.