Data Splitting and JSON Shredding – DP-203 Azure Data Engineer Exam Guide
Introduction
Data Splitting and JSON Shredding are essential techniques for Azure Data Engineers working with semi-structured data. In the DP-203 exam, you will encounter questions that test your understanding of how to decompose complex, nested data structures into flat, queryable formats suitable for analytics workloads. This guide covers what these concepts are, why they matter, how they work in Azure, and how to tackle related exam questions confidently.
Why Data Splitting and JSON Shredding Are Important
Modern data pipelines frequently ingest data in semi-structured formats such as JSON, Avro, or Parquet. These formats often contain nested objects and arrays that are not directly compatible with traditional relational analytics tools. Without proper handling:
• Query performance suffers – Querying deeply nested JSON documents is slow and resource-intensive.
• Data becomes difficult to join – Nested arrays cannot be easily joined with other relational tables.
• Storage costs increase – Storing raw, unsplit JSON in analytics layers wastes compute and storage resources.
• Business users are blocked – Analysts and BI tools expect flat, tabular data for reporting and dashboarding.
Data Splitting and JSON Shredding solve these problems by transforming nested, hierarchical data into normalized or flattened relational structures.
What Is Data Splitting?
Data Splitting refers to the process of breaking a single complex data record into multiple related records or tables. For example, an order document containing an array of line items can be split into an Orders table and an OrderLineItems table, linked by a foreign key. This normalization makes it easier to query, aggregate, and join data efficiently.
Key scenarios for data splitting include:
• Separating header-level data from detail-level data
• Breaking arrays into individual rows (also known as exploding arrays)
• Distributing data across multiple target tables in a data warehouse
What Is JSON Shredding?
JSON Shredding is the specific process of parsing a JSON document and extracting its individual fields, nested objects, and arrays into relational columns and rows. The term shredding comes from the idea of tearing apart a hierarchical document into flat, tabular pieces.
In Azure, JSON Shredding is commonly performed using:
• Azure Synapse Analytics – Using T-SQL functions like OPENJSON(), JSON_VALUE(), and JSON_QUERY()
• Azure Databricks – Using PySpark or Spark SQL functions like explode(), from_json(), get_json_object(), and schema inference
• Azure Data Factory / Synapse Pipelines – Using mapping data flows with flatten transformations
• Azure Stream Analytics – Using GetArrayElements() and CROSS APPLY for real-time JSON shredding
How Data Splitting and JSON Shredding Work in Azure
1. Using T-SQL in Azure Synapse Analytics (Serverless or Dedicated SQL Pools)
The OPENJSON() function is the primary tool for shredding JSON in T-SQL. It converts a JSON array or object into a set of rows and columns.
• JSON_VALUE(expression, path) – Extracts a scalar value from a JSON string. Returns a single value (nvarchar).
• JSON_QUERY(expression, path) – Extracts an object or array from a JSON string. Returns a JSON fragment.
• OPENJSON(jsonExpression [, path]) – Parses JSON text and returns objects and properties as rows and columns. Supports explicit schema definition using a WITH clause.
Example scenario: You have a JSON file in Azure Data Lake Storage containing customer orders with nested line items. Using Synapse serverless SQL pool, you can use OPENROWSET to read the file and OPENJSON with CROSS APPLY to shred the nested line items array into individual rows.
2. Using PySpark in Azure Databricks or Synapse Spark
• explode() – Takes an array column and creates a new row for each element in the array. This is the most commonly used function for splitting arrays.
• explode_outer() – Similar to explode() but retains rows where the array is null or empty (producing null values instead of dropping the row).
• posexplode() – Like explode() but also returns the position (index) of each element.
• from_json() – Parses a JSON string column into a struct type using a specified schema.
• col("field.*") or select("field.*") – Used to flatten struct columns by selecting all nested fields as top-level columns.
A typical pattern: Read JSON into a DataFrame, use select with dot notation to access nested fields, use explode to flatten arrays, and then write the result to a Delta table or data warehouse.
3. Using Azure Data Factory Mapping Data Flows
The Flatten transformation in ADF Mapping Data Flows allows you to take nested JSON structures and unroll them into flat rows without writing code. You specify which hierarchy to unroll, and ADF generates the appropriate rows. This is particularly useful for no-code/low-code ETL scenarios.
4. Using Azure Stream Analytics
For real-time streaming scenarios, Azure Stream Analytics supports:
• GetArrayElements() – Extracts elements from a JSON array in a streaming input.
• CROSS APPLY – Used with GetArrayElements to join the parent record with each array element, producing one row per element.
Key Concepts to Understand
• Schema-on-Read vs Schema-on-Write: JSON shredding is often performed in a schema-on-read approach, especially in serverless SQL pools where you define the schema at query time rather than at ingestion time.
• Star Schema Modeling: After shredding JSON, the resulting flat tables often feed into star schema designs with fact and dimension tables in a dedicated SQL pool.
• Medallion Architecture: In Databricks and Delta Lake patterns, JSON shredding typically occurs during the Bronze-to-Silver transformation layer. Raw JSON is ingested into Bronze, shredded and cleaned in Silver, and aggregated in Gold.
• Handling Nulls and Missing Fields: When shredding JSON, fields may be missing or null. Use explode_outer() instead of explode() in Spark, or handle nulls with ISNULL/COALESCE in T-SQL to avoid losing rows.
• Performance Considerations: Shredding large JSON files is compute-intensive. Consider partitioning data, using columnar formats like Parquet or Delta after shredding, and avoiding repeated shredding by materializing results.
Common Exam Scenarios
1. Flattening nested arrays: You are given a JSON structure with nested arrays and asked which function or transformation to use to produce individual rows. The answer typically involves OPENJSON with CROSS APPLY (T-SQL) or explode() (Spark).
2. Extracting scalar values from JSON: Questions may ask how to extract a single value from a nested JSON path. The answer is JSON_VALUE() in T-SQL or dot notation / get_json_object() in Spark.
3. Choosing the right tool: Scenarios may describe a requirement (e.g., real-time vs. batch, code vs. no-code) and ask you to pick the appropriate Azure service. Stream Analytics for real-time, ADF Flatten for no-code, Spark for large-scale batch, Synapse SQL for ad-hoc queries.
4. Handling missing data during shredding: Questions about preserving rows when arrays are empty. The answer is explode_outer() in Spark or using LEFT JOIN with OPENJSON in T-SQL.
5. Writing shredded data to optimized formats: After shredding, the exam may test whether you know to store results in Parquet or Delta format for downstream analytics efficiency.
Exam Tips: Answering Questions on Data Splitting and JSON Shredding
• Know the function names precisely: The exam frequently tests whether you can distinguish between JSON_VALUE() (returns scalar), JSON_QUERY() (returns object/array), and OPENJSON() (returns rowset). Memorize the distinctions.
• Remember CROSS APPLY: In T-SQL, OPENJSON is almost always used with CROSS APPLY to join the shredded rows back to the parent record. If you see CROSS APPLY as an answer option with OPENJSON, it is very likely correct.
• Differentiate explode() from explode_outer(): If the question mentions preserving rows with null or empty arrays, choose explode_outer(). If it just asks about flattening arrays, explode() is sufficient.
• Watch for the Flatten transformation: When the scenario describes a no-code or low-code requirement in ADF, the Flatten transformation in Mapping Data Flows is the correct choice, not T-SQL or Spark.
• Consider the architecture layer: If the question asks where in a medallion architecture JSON shredding should occur, the answer is typically in the Bronze-to-Silver transition, where raw data is cleaned and normalized.
• Read the JSON structure carefully: Exam questions often include a sample JSON snippet. Look at whether the target data is in a nested object (use dot notation or JSON_VALUE) versus an array (use OPENJSON/explode). The structure dictates the correct function.
• Think about data types: JSON_VALUE returns nvarchar(4000) by default. If the question involves large text values exceeding 4000 characters, JSON_QUERY or OPENJSON may be more appropriate. The exam may test this edge case.
• Look for partition and performance hints: If a question discusses performance optimization after shredding, the correct answer usually involves writing to Parquet/Delta format, partitioning by a key column, or materializing the shredded result rather than repeatedly querying raw JSON.
• Eliminate distractors: Functions like STRING_SPLIT() are for splitting delimited strings, not JSON. Do not confuse string splitting with JSON shredding. Similarly, PIVOT is for rotating rows to columns, not for shredding nested data.
• Practice with sample data: Before the exam, practice writing OPENJSON queries and Spark explode operations on sample nested JSON data. Hands-on familiarity makes it much easier to answer scenario-based questions quickly and accurately.
Summary
Data Splitting and JSON Shredding are foundational skills for the DP-203 exam. They involve transforming nested, semi-structured JSON data into flat, relational formats using tools like OPENJSON and CROSS APPLY in T-SQL, explode() in Spark, Flatten in ADF, and GetArrayElements in Stream Analytics. Understanding when and how to apply each technique—along with performance and architecture considerations—will prepare you to confidently answer related exam questions.
