ApX logoApX logo
ModelsCalculatorHardwareCoursesPricing
ApX logoApX logo
ModelsCalculatorHardwareCoursesPricing

All Courses

Introduction to ETL Pipelines
Chapter 1: Understanding ETL Fundamentals
What is Data Integration?
Introducing ETL: Extract, Transform, Load
The Purpose of ETL Processes
Distinguishing ETL from ELT
Common Data Sources and Targets
Identifying ETL Requirements
Quiz for Chapter 1
Chapter 2: The Extraction Stage
Connecting to Data Sources
Full Extraction vs. Incremental Extraction
Working with Structured Data (e.g., Databases, CSV)
Introduction to Semi-Structured Data (e.g., JSON, XML)
Change Data Capture (CDC) Concepts
Handling Extraction Errors
Practice: Simulating Data Extraction
Quiz for Chapter 2
Chapter 3: The Transformation Stage
Why Data Transformation is Necessary
Data Cleaning: Handling Missing Values
Data Cleaning: Correcting Errors
Data Validation Rules
Data Formatting and Standardization
Data Enrichment: Adding Information
Data Structuring: Joining and Splitting Data
Introduction to Data Aggregation
Practice: Applying Simple Transformations
Quiz for Chapter 3
Chapter 4: The Loading Stage
Choosing a Target System
Loading Strategies: Full Load
Loading Strategies: Incremental Load (Append/Update)
Understanding Target Schemas
Schema Mapping: Source to Target
Handling Loading Failures
Data Validation Post-Load
Practice: Data Loading
Quiz for Chapter 4
Chapter 5: Building Simple ETL Pipelines
What is an ETL Pipeline?
Pipeline Workflow and Dependencies
Introduction to ETL Tools Categories
Visual ETL Tools Overview
Scripting for ETL Overview
Scheduling and Automation Basics
Monitoring and Logging Fundamentals
Hands-on Practical: Designing a Simple Pipeline
Quiz for Chapter 5

Introduction to Semi-Structured Data (e.g., JSON, XML)

While structured data, such as the neat rows and columns in databases or CSV files, is common, much of the data generated today doesn't fit cleanly into tables. This introduces semi-structured data, a category that sits between the rigid format of structured data and the complete lack of organization found in unstructured data (like plain text documents or images).

Semi-structured data doesn't conform to a fixed, formal schema like relational database tables, but it does contain tags or other markers to separate semantic elements and enforce hierarchies of records and fields. Think of it as having organizational clues, but not a strict blueprint. This flexibility makes it very useful for systems where the exact structure might evolve or vary.

Two widespread formats you'll frequently encounter when extracting semi-structured data are JSON and XML.

JSON: JavaScript Object Notation

JSON is a lightweight text-based format that's easy for humans to read and write, and easy for machines to parse and generate. It became extremely popular with the rise of web APIs (Application Programming Interfaces) because it maps very naturally to data structures found in many programming languages.

JSON data is built on two primary structures:

  1. Objects: Collections of key-value pairs, enclosed in curly braces {}. Keys are strings, and values can be strings, numbers, booleans (true/false), arrays, or even other nested objects.
  2. Arrays: Ordered lists of values, enclosed in square brackets []. Values in an array can be of any valid JSON type.

Here’s a simple example representing information about a person:

{
  "firstName": "Jane",
  "lastName": "Doe",
  "age": 30,
  "isStudent": false,
  "address": {
    "streetAddress": "123 Main St",
    "city": "Anytown",
    "postalCode": "12345"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "555-1234"
    },
    {
      "type": "work",
      "number": "555-5678"
    }
  ]
}

Notice how the address is a nested object, and phoneNumbers is an array containing multiple phone number objects, each with its own type and number.

XML: Extensible Markup Language

XML is another text-based format designed to store and transport data. It uses tags to define elements and attributes to provide additional information about those elements. XML is hierarchical, meaning elements can be nested within other elements, creating a tree-like structure.

While perhaps less common now for new web APIs compared to JSON, XML is still widely used in configuration files, document formats (like Microsoft Office XML formats), and enterprise systems for data exchange.

Here's the same person information represented in XML:

<person>
  <firstName>Jane</firstName>
  <lastName>Doe</lastName>
  <age>30</age>
  <isStudent>false</isStudent>
  <address>
    <streetAddress>123 Main St</streetAddress>
    <city>Anytown</city>
    <postalCode>12345</postalCode>
  </address>
  <phoneNumbers>
    <phone type="home">
      <number>555-1234</number>
    </phone>
    <phone type="work">
      <number>555-5678</number>
    </phone>
  </phoneNumbers>
</person>

In this XML example, <person>, <firstName>, <address>, etc., are tags defining elements. Notice the type="home" within the <phone> tag; this is an attribute providing extra detail about the phone element.

Extracting Semi-Structured Data

Extracting data from JSON or XML sources presents different challenges compared to pulling data from a relational database table.

  1. Parsing: Raw JSON or XML is just text. The first step in extraction is usually parsing this text into an internal data structure that your ETL tool or script can understand and manipulate. Most programming languages and ETL tools have built-in libraries or components specifically for parsing JSON and XML.
  2. Navigating Hierarchy: Because these formats are hierarchical, you often need to navigate through nested objects or elements to find the specific pieces of data you need. For example, to get Jane Doe's work phone number from the JSON example, you'd need to access the phoneNumbers array, find the object where type is "work", and then get the value associated with the number key.
  3. Schema Variability: While there's some structure, it might not be perfectly consistent across all records or files. Some records might have optional fields that others lack. Your extraction logic needs to be flexible enough to handle this potential variability without failing.

For example, imagine extracting the street address from the JSON structure shown earlier. The process involves accessing the top-level object, then the address key, and finally the streetAddress key within the nested address object.

This diagram illustrates the nested structure common in JSON and XML data, showing objects containing other objects or arrays.

Understanding how to handle these semi-structured formats is essential in modern ETL, as they are common outputs from web services, IoT devices, and various application logs. The extraction stage needs to be equipped to parse these formats and pull out the relevant information before the transformation stage can begin.

  • The JSON Data Interchange Format, T. Bray, Ed., 2017 RFC 8259 (Internet Engineering Task Force (IETF)) - This is the official Internet Engineering Task Force (IETF) standard defining JSON, its syntax, and data types. It serves as the authoritative specification for the format.
  • Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems, Martin Kleppmann, 2017 (O'Reilly Media) - This book provides a comprehensive overview of data processing concepts, covering data models and formats like JSON and XML, and their impact on system design. Chapter 4 addresses these formats.
  • Fundamentals of Data Engineering, Joe Reis, Matt Housley, 2022 (O'Reilly Media) - This recent book guides data engineering practices, covering data ingestion and various data formats, including semi-structured data, for modern data pipelines. Relevant to the extraction stage.

© 2026 ApX Machine Learning