Why Your Data Platform Needs a Strategy for JSON
- Jun 30
- 5 min read
Semi-structured data is no longer a niche engineering concern. It is arriving at the heart of your data platform every day, and the decisions your teams make about how to handle it will determine how agile, resilient, and trustworthy your data infrastructure becomes over the next few years.
The rise of JSON — the most common format for this kind of data — is not a coincidence. Two structural shifts in modern software have driven it. First, organisations have moved to API-first architectures, meaning data no longer arrives in neat, predictable table exports. When your systems query a customer record or an order history, they typically receive back a structured packet of JSON rather than a flat file. Second, the microservices era produced a landscape of loosely coupled systems, each generating its own JSON outputs. The data your platform receives today is, in many cases, the cumulative exhaust of that architectural trend.
What has changed in the industry's favor is that the tooling to handle this kind of data inside the warehouse has caught up considerably. Capabilities like the variant data type in platforms such as Snowflake mean that organizations no longer have to pre-process everything before it reaches the warehouse. That is significant, because pre-processing adds complexity, brittleness, and cost.
The question your teams should be asking: split it, or store it?
One of the most common decisions your data engineers face is whether to immediately break JSON data down into its individual components — customers here, orders there, products somewhere else — or to land it in the warehouse as-is and extract what is needed later.
The answer, as with most things in data architecture, depends on context.
The general principle that Alex and I advocate is to extract and process late. Land the data early, break it into its logical objects — separating customers from orders from products — but resist the urge to flatten everything down to column-level immediately. That flattening should happen downstream, where business rules live, and only to the extent that downstream use actually requires it.
There is a practical reason for this. If a particular data set is queried twenty times a day, it is far more efficient to flatten it once at load than to have it re-processed on every query. But if a data set is rarely accessed, leave it as a JSON blob. Build a view for the team that needs it, extract the fields they require, and avoid adding unnecessary structural complexity to your core platform.
Schema drift: where JSON becomes a genuine strategic asset
Here is where the conversation becomes particularly relevant for senior leaders. One of the most disruptive events in any data pipeline is schema drift — when a source system changes its data structure without warning. A few extra columns are added to a CRM table; some fields are removed; an API response evolves. In a traditional columnar architecture, this breaks things.
JSON provides a natural buffer against this. Because the data arrives as a self-describing structure, your ingestion layer does not need to know in advance every field that will be present. You store the JSON as it arrives. You validate it when you need to. The schema drift problem does not disappear, but it is pushed downstream to a point where your team can handle it with business logic rather than emergency pipeline fixes.
This leads naturally into the question of data contracts — the formal agreements between data producers and consumers about what a JSON payload will contain and in what format. Those contracts can be applied at ingestion, before data enters the warehouse, or validated afterwards before feeding critical downstream systems.
For high-stakes outputs — the CEO's dashboard, regulatory reporting — you want that validation rigorous and early. For lower-priority data flows, a more permissive approach may be justified.
How to load JSON well
In practice, a robust loading pattern looks like this. When a complex JSON payload arrives — say, customers nested with their orders and the products within those orders — the first step is to break it into its conceptual objects: customers, orders, products. Each of those objects then loads into the appropriate area of your data warehouse as its own JSON packet, with no further flattening at that stage.
Within each landing area, the pattern is to store known metadata — the primary key, record source, load timestamp — in proper structured columns, and place the full JSON object as a single variant column. This approach maps closely to how a satellite works in a Data Vault: structured metadata alongside a payload that contains all the descriptive detail.
One technical pitfall worth your team's attention is the issue of JSON tag ordering. Unlike a database column, the order of fields within a JSON object is not guaranteed to be consistent between extracts. If your team is using hash-based change detection — comparing a checksum of today's record against yesterday's to determine whether anything has changed — a reordered but otherwise identical JSON object will appear to have changed when it has not.
The solution is to normalise the JSON structure before processing, ensuring a consistent field order that your hashing logic can rely on.
Data quality is not optional
The relative flexibility of JSON can create a false sense of security around data quality. JSON supports fewer native data types than a typical database, which means engineers frequently encounter values that have been forced into strings — an integer stored as text, a date stored as a free-form field. Without a defined schema governing what each field should contain, these inconsistencies accumulate silently and surface painfully downstream.
Perhaps the most common and consequential issue is the handling of nulls and empty strings. An empty string in JSON does not mean the same thing as a null, but they are often used interchangeably by source systems. Getting this wrong produces data quality problems that propagate through every downstream process. Your teams need a clear, organisation-wide policy on how to handle this — and that policy needs to be documented and understood by everyone touching the pipeline.
Traceability is non-negotiable
One final principle that deserves emphasis at leadership level: every piece of data extracted from a JSON payload must remain traceable to its source. That means every record should carry the name of the source file and the date and time of extraction. Lose that provenance, and you lose the ability to audit, diagnose, or explain your data when it matters most. It is a small discipline with outsized consequences.
The bottom line
The semi-structured data challenge is not going away. If anything, it will intensify as API-driven architectures and microservices continue to proliferate. The organizations that handle this well will be those that adopt a consistent, principled approach — landing data early, processing late, protecting against schema drift, enforcing data quality at the right points, and maintaining rigorous traceability throughout.
The tooling to support this has never been better. The question is whether your data strategy is keeping pace.
Ready to take the next step?
If this has raised questions about how your organization is handling semi-structured data, or about the maturity of your data platform more broadly, there are three ways we can help.
Talk to us.
If you want a direct conversation about your data architecture and where the pressure points are, get in touch. We work with senior data leaders and C-suite executives to cut through complexity and build platforms that perform.
Download our white papers.
We publish in-depth guidance on Data Vault, Medallion architecture, AI readiness, and modern data platform design — written for practitioners and leaders alike.
Take the Data Transformation Readiness Assessment.
Not sure where your platform stands? Our free diagnostic evaluates your organisation across four critical dimensions — platform foundation, reporting and insight speed, operational agility, and strategic positioning — and gives you a personalised report with a clear picture of where to focus. It takes less than ten minutes.
