Data Engineering: Working with semi-structured data types.
In our previous post, we demonstrated how to parse comma-separated data into a structured file format within the Snowflake project that utilizes the medallion architectural framework.
In this post, we’ll introduce another file format that you’ll frequently encounter in your data engineering projects: the JSON File Format.
JSON (JavaScript Object Notation) is an open standard file format used for sharing data.
It employs human-readable text to store and transmit data objects, which consist of attribute-value pairs and arrays.
This file format is commonly used for transmitting data between web applications and servers, making it very popular and suitable for our discussion and demonstration.
To explain in more detail, we have three common types of JSON: JSON objects, JSON nested objects, and JSON arrays.
JSON objects:
“User_Name” :{
“Fist_Name”: “Henry”,
“Last_Name”: “Godson”
}
Nested JSON objects:
“User_Informations”: {
“Date”: “2024-05-21”,
“Sports”:
{“Football”: “Good”,
“Swimming”: “Excellent”
}
“Name”: “Henry
}
JSON arrays: (JSON are written inside the square brackets).
“Employees”:
[
{
“First_Name”: “Henry”,
“Last_Name”: “Godson”
},
{
“First_Name”: “Endy”,
“Last_Name”: “Junior”
},
]
We also have JSON Array in the simple format as “Favourit_Sports”: [“Football”, “Baseball”].
While certain data migration tools, such as Azure Data Factory, possess built-in intelligence for transforming data from one format to another, most data engineering ETL projects necessitate data engineers to perform similar transformations using either SQL or Python.
To transform a JSON array using the Medallion Architectural Framework, we have two major approaches:
Copy to Bronze and Transform: In this approach, we copy the JSON file into the Bronze layer and then transform the data to a structured format before moving it into the Silver table.
Direct Transformation to Bronze: The second approach involves transforming the data directly to a structured format and depositing it into the Bronze layer straight from the staging environment.
This approach is recommended, and we’ll demonstrate how to achieve it in our upcoming post. Stay tuned!
![]()
3
