Semi-structured data handling (JSON) in dbt - Deep Dive

Overview - Semi-structured data handling (JSON)

What is it?

Semi-structured data is a type of data that does not fit neatly into tables but still has some organization, like JSON. JSON (JavaScript Object Notation) stores data in key-value pairs and nested structures, making it flexible. Handling JSON means extracting and transforming this data so it can be analyzed or stored in databases. dbt helps transform JSON data inside your data warehouse using SQL and built-in functions.

Why it matters

Many modern data sources like APIs, logs, and event streams produce JSON data. Without tools to handle JSON, this data would be hard to analyze or combine with traditional tables. If we ignored JSON, we would miss insights hidden in flexible data formats and waste valuable information. Handling JSON well lets businesses unlock rich, detailed data for smarter decisions.

Where it fits

Before learning this, you should understand basic SQL and relational databases. After mastering JSON handling, you can learn advanced data modeling, performance tuning, and integrating APIs with dbt. This topic bridges raw data ingestion and clean, usable analytics-ready tables.

Mental Model

Core Idea

Semi-structured JSON data is like a flexible container of nested information that needs to be unpacked and flattened to fit into structured tables for analysis.

Think of it like...

Imagine a toolbox with many compartments inside, each holding different tools and parts. To use the tools effectively, you need to open the compartments and organize the parts on a workbench. JSON is the toolbox, and handling it means unpacking and arranging the parts for easy use.

JSON Data Structure
┌─────────────┐
│ Root Object │
├─────────────┤
│ Key: Value  │
│ Key: Object │───┐
│ Key: Array  │───┼─ Nested structures
└─────────────┘   │
                  ▼
           ┌─────────────┐
           │ Nested Keys │
           │ Nested Array│
           └─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding JSON Basics

Concept: Learn what JSON is and how its structure works with keys, values, objects, and arrays.

JSON stores data as text with pairs like "key": value. Values can be simple (strings, numbers) or complex (objects, arrays). Objects are like dictionaries with keys and values. Arrays are ordered lists of values. This flexible format allows nesting data inside data.

Result

You can read and recognize JSON data structures and understand their components.

Understanding JSON's flexible structure is essential because it explains why JSON data needs special handling compared to flat tables.

2

Foundation

Basics of dbt and SQL for JSON

Concept: Learn how dbt uses SQL to transform data and the basics of querying JSON inside SQL.

dbt runs SQL queries inside your data warehouse. Many warehouses support JSON functions like extracting keys or flattening arrays. You write SQL SELECT statements that use these functions to access JSON fields. dbt models organize these queries into reusable transformations.

Result

You can write simple SQL queries in dbt that select and extract JSON fields.

Knowing how dbt and SQL interact with JSON lets you start turning flexible data into structured tables step-by-step.

3

Intermediate

Extracting Nested JSON Fields

🤔Before reading on: do you think you can directly select nested JSON keys as columns in SQL? Commit to your answer.

Concept: Learn how to use JSON extraction functions to access nested keys inside JSON objects.

Most SQL warehouses provide functions like JSON_EXTRACT or -> operators to get nested values. For example, to get a user's city from a JSON address object, you write something like json_column->'address'->>'city'. This extracts the nested value as a string or JSON type.

Result

You can write SQL that pulls out nested JSON values as separate columns.

Understanding how to navigate nested JSON is key to flattening complex data into usable columns.

4

Intermediate

Flattening JSON Arrays with dbt

🤔Before reading on: do you think JSON arrays can be directly expanded into multiple rows in SQL? Commit to your answer.

Concept: Learn how to convert JSON arrays into multiple rows using SQL functions like UNNEST or LATERAL FLATTEN.

JSON arrays hold lists of items. To analyze each item separately, you need to expand the array into rows. SQL functions like UNNEST (BigQuery, Snowflake) or LATERAL FLATTEN (Snowflake) let you do this. In dbt, you write models that join or select from these expanded rows.

Result

You can transform JSON arrays into multiple rows for detailed analysis.

Flattening arrays unlocks the ability to analyze repeated or list data inside JSON, which is common in event logs or user actions.

5

Intermediate

Handling Missing or Optional JSON Fields

Concept: Learn how to safely query JSON fields that might not exist or be null.

JSON data often has missing keys or null values. Using functions like COALESCE or CASE WHEN in SQL helps provide defaults or handle missing data gracefully. This prevents errors or incorrect results when fields are absent.

Result

Your queries become robust and handle real-world messy JSON data without breaking.

Knowing how to handle missing fields prevents bugs and ensures your data models are reliable.

6

Advanced

Optimizing JSON Transformations in dbt

🤔Before reading on: do you think complex JSON queries always run fast in warehouses? Commit to your answer.

Concept: Learn techniques to improve performance when querying and transforming JSON data in dbt models.

Complex JSON extraction and flattening can slow queries. Techniques include selecting only needed fields, using CTEs to break down steps, caching intermediate results, and leveraging warehouse-specific JSON functions optimized for speed. dbt's incremental models can also help by processing only new data.

Result

Your dbt models run faster and scale better with large JSON datasets.

Performance tuning is crucial for production use because JSON queries can become expensive and slow without care.

7

Expert

Advanced Nested JSON and Schema Evolution

🤔Before reading on: do you think JSON schemas stay the same over time in production? Commit to your answer.

Concept: Understand how to handle deeply nested JSON and evolving schemas that change structure over time.

In real systems, JSON data can have multiple nested levels and change shape as new fields are added or removed. Techniques include recursive flattening, dynamic SQL generation in dbt, and schema versioning strategies. Using dbt macros to adapt to schema changes helps maintain models without breaking.

Result

You can build resilient dbt models that handle complex, changing JSON data reliably.

Handling schema evolution and deep nesting is a key skill for maintaining long-term data pipelines with JSON.

Under the Hood

When dbt runs a model with JSON data, it sends SQL queries to the data warehouse. The warehouse parses JSON strings into internal JSON types or text. Functions like JSON_EXTRACT navigate the JSON tree to return values. Flattening arrays uses set-returning functions that expand nested lists into rows. dbt compiles models into SQL and manages dependencies to build tables incrementally.

Why designed this way?

JSON was designed for flexible data exchange, not rigid tables. Warehouses added JSON support to handle modern data sources without forcing schema upfront. dbt builds on this by letting analysts write SQL transformations declaratively, making JSON handling accessible and maintainable. This design balances flexibility with the power of SQL analytics.

dbt Model Execution Flow
┌───────────────┐
│ dbt Compiles  │
│ SQL with JSON │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Data Warehouse│
│ Parses JSON   │
│ Executes SQL  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ JSON Functions│
│ Extract/Flatten│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Result Tables │
│ Structured    │
│ Data Ready    │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can you treat JSON data exactly like a normal SQL table column? Commit yes or no.

Common Belief: JSON data is just like any other column and can be queried directly without special functions.

Reality: JSON data is stored as text or JSON type and requires special extraction or flattening functions to access nested values.

Why it matters: Treating JSON like normal columns leads to errors or empty results, blocking data analysis.

Quick: Does flattening a JSON array always keep the original row count? Commit yes or no.

Common Belief: Flattening JSON arrays does not change the number of rows in the result.

Reality: Flattening expands each array element into its own row, increasing the total row count.

Why it matters: Misunderstanding this causes incorrect joins or aggregations, leading to wrong analytics.

Quick: Is it safe to assume JSON schemas never change in production? Commit yes or no.

Common Belief: JSON data schemas are fixed and stable over time.

Reality: JSON schemas often evolve, with fields added, removed, or nested differently.

Why it matters: Ignoring schema changes breaks dbt models and causes pipeline failures.

Quick: Can you always use the same JSON extraction function across all warehouses? Commit yes or no.

Common Belief: JSON functions are standardized and work the same in every SQL warehouse.

Reality: Each warehouse has its own JSON syntax and functions, requiring different SQL code.

Why it matters: Assuming uniformity leads to broken queries and wasted debugging time.

Expert Zone

1

Some warehouses store JSON as native types with indexing, making queries faster, while others treat JSON as plain text, affecting performance.

2

Using dbt macros to abstract JSON extraction lets you write portable code across different warehouses with varying JSON syntax.

3

Incremental dbt models combined with JSON flattening can drastically reduce processing time by only handling new data.

When NOT to use

If JSON data is simple and flat, converting it to structured tables before dbt transformations might be easier. For extremely large or deeply nested JSON, consider specialized ETL tools or JSON-specific databases instead of pure SQL transformations.

Production Patterns

In production, teams use dbt to build layered models: raw JSON ingestion tables, intermediate flattened tables, and final analytics tables. They use tests to catch schema changes and macros to handle JSON differences across environments. Incremental runs and snapshots help manage large JSON datasets efficiently.

Connections

NoSQL Databases

Related data storage format

Understanding JSON handling in dbt helps bridge SQL analytics with NoSQL databases like MongoDB that store data as JSON documents.

API Data Integration

Builds on JSON data exchange

APIs often return JSON; mastering JSON transformations in dbt enables seamless integration of API data into analytics workflows.

XML Data Processing

Similar semi-structured data handling

Techniques for parsing and flattening JSON share concepts with XML processing, broadening skills in handling semi-structured data formats.

Common Pitfalls

#1 Trying to select nested JSON keys directly without extraction functions.

Wrong approach:

SELECT json_column.address.city FROM table;

Correct approach:

SELECT json_column->'address'->>'city' AS city FROM table;

Root cause:

Misunderstanding that JSON is stored as text and requires functions to access nested data.

#2 Flattening JSON arrays but forgetting it multiplies rows, causing incorrect joins.

Wrong approach:

SELECT * FROM table JOIN UNNEST(json_column.array) AS item ON table.id = item.id;

Correct approach:

Use proper keys to join or aggregate after flattening to avoid row explosion.

Root cause:

Not realizing flattening expands data and changes row counts.

#3 Ignoring missing JSON fields and not handling nulls, causing query errors.

Wrong approach:

SELECT json_column->>'optional_field' AS field FROM table WHERE field = 'value';

Correct approach:

SELECT COALESCE(json_column->>'optional_field', 'default') AS field FROM table WHERE field = 'value';

Root cause:

Assuming all JSON keys exist in every record.

Key Takeaways

JSON is a flexible, nested data format that requires special SQL functions to extract and flatten for analysis.

dbt enables transforming JSON data inside data warehouses by writing SQL models that unpack and organize JSON fields.

Flattening JSON arrays expands data into multiple rows, which is powerful but requires careful handling to avoid errors.

Handling missing or evolving JSON schemas is essential for building robust, production-ready data pipelines.

Performance optimization and abstraction with dbt macros make JSON transformations scalable and maintainable.

Practice

1. What is the main purpose of using JSON functions in dbt when working with semi-structured data?

easy

A. To convert JSON data into CSV files automatically

B. To encrypt JSON data for security

C. To create new JSON files from scratch

D. To extract and manipulate nested data inside JSON columns

Solution

1. Step 1: Understand JSON data in dbt

   JSON data often contains nested information that needs to be accessed or transformed.

2. Step 2: Purpose of JSON functions

   JSON functions allow extracting specific parts of this nested data for analysis or transformation.

3. Final Answer:

   To extract and manipulate nested data inside JSON columns -> Option D

4. Quick Check:

   JSON functions = extract nested data [OK]

Hint: JSON functions help get nested info from JSON columns [OK]

Common Mistakes:

• Thinking JSON functions create files
• Assuming JSON functions encrypt data
• Confusing JSON extraction with file conversion

2. Which of the following is the correct syntax to extract a value from a JSON column named data using dbt's SQL with PostgreSQL syntax?

easy

A. SELECT data->name AS name FROM table_name;

B. SELECT data->>'name' AS name FROM table_name;

C. SELECT data['name'] AS name FROM table_name;

D. SELECT data->>name AS name FROM table_name;

Solution

1. Step 1: Identify PostgreSQL JSON extraction syntax

   PostgreSQL uses ->> to extract JSON text values by key as text.

2. Step 2: Apply correct syntax to extract 'name'

   The correct syntax is data->>'name' to get the text value of the 'name' key.

3. Final Answer:

   SELECT data->>'name' AS name FROM table_name; -> Option B

4. Quick Check:

   PostgreSQL JSON text extraction = data->>'key' [OK]

Hint: Use ->> with quotes for JSON text extraction in PostgreSQL [OK]

Common Mistakes:

• Using square brackets instead of ->>
• Missing quotes around the key name
• Using -> instead of ->> for text extraction

3. Given the following dbt SQL snippet using Snowflake syntax:

SELECT
  data:id::string AS user_id,
  data:attributes:age::int AS age
FROM users

What will be the output columns for a row where data contains {"id": "U123", "attributes": {"age": 30}}?

medium

A. user_id = 'U123', age = 30

B. user_id = 'id', age = 'attributes:age'

C. user_id = NULL, age = NULL

D. user_id = 'U123', age = NULL

Solution

1. Step 1: Understand Snowflake JSON extraction

   Snowflake uses colon notation data:id to access JSON keys and casting (::string, ::int) to convert types.

2. Step 2: Apply extraction to given JSON

   For {"id": "U123", "attributes": {"age": 30}}, data:id::string extracts 'U123', and data:attributes:age::int extracts 30.

3. Final Answer:

   user_id = 'U123', age = 30 -> Option A

4. Quick Check:

   Snowflake JSON path extraction = correct values [OK]

Hint: Use colon paths and cast to get JSON values in Snowflake [OK]

Common Mistakes:

• Confusing JSON keys with strings
• Not casting extracted values
• Using wrong path syntax

4. You wrote this dbt SQL code to extract a nested JSON value:

SELECT
  data->'user'->>email AS email
FROM users

But you get an error. What is the likely cause?

medium

A. Using ->> after -> on a JSON object without parentheses

B. Using -> instead of ->> for text extraction

C. Missing quotes around 'email' key

D. The JSON column 'data' is not valid JSON

Solution

1. Step 1: JSON path operators require quoted keys

   Keys in -> and ->> must be string literals enclosed in single quotes.

2. Step 2: Identify syntax issue

   In data->'user'->>email, email lacks quotes and is interpreted as an identifier (e.g., column name) instead of a JSON key, causing a syntax error. Correct syntax: data->'user'->>'email'.

3. Final Answer:

   Missing quotes around 'email' key -> Option C

4. Quick Check:

   JSON keys must be quoted strings [OK]

Hint: Always quote JSON keys in -> and ->> operators [OK]

Common Mistakes:

• Forgetting parentheses in chained JSON extraction
• Misquoting JSON keys
• Assuming JSON column is invalid without checking

5. You have a JSON column info in a dbt model with records like:

{"user": {"id": "U1", "tags": ["a", "b", "c"]}}

How would you write a dbt SQL expression to create a new row for each tag using PostgreSQL JSON functions?

hard

A. SELECT user_id, tag FROM table_name, json_array_elements_text(info->'user'->'tags') AS tag;

B. SELECT user_id, unnest(info->'user'->'tags') AS tag FROM table_name;

C. SELECT user_id, json_each_text(info->'user'->'tags') AS tag FROM table_name;

D. SELECT user_id, explode(info->'user'->'tags') AS tag FROM table_name;

Solution

1. Step 1: Identify function to expand JSON arrays in PostgreSQL

   PostgreSQL uses json_array_elements_text() to turn JSON arrays into a set of text rows.

2. Step 2: Apply function to nested JSON array

   Extract the 'tags' array with info->'user'->'tags' and expand using comma syntax in FROM: SELECT user_id, tag FROM table_name, json_array_elements_text(info->'user'->'tags') AS tag.

3. Final Answer:

   SELECT user_id, tag FROM table_name, json_array_elements_text(info->'user'->'tags') AS tag; -> Option A

4. Quick Check:

   Use json_array_elements_text() to expand JSON arrays [OK]

Hint: Use FROM table, json_array_elements_text(...) AS tag to explode JSON arrays in PostgreSQL [OK]

Common Mistakes:

• Using unnest() on JSON instead of arrays
• Using json_each_text() which is for objects
• Using explode() which is not PostgreSQL function