Semi-structured data handling (JSON) in dbt - Mini Project: Build & Apply

Handling Semi-structured JSON Data in dbt

📖 Scenario: You work as a data analyst at an e-commerce company. Your team stores customer order details in a database where one column contains JSON data about each order's items. You want to extract useful information from this JSON to analyze product sales.

🎯 Goal: Build a dbt model that extracts product names and quantities from a JSON column and calculates the total quantity sold per product.

📋 What You'll Learn

Create a source table with a JSON column named order_items containing order details.

Define a config variable for filtering orders by a minimum quantity threshold.

Write a dbt model using SQL to parse the JSON and aggregate total quantities per product.

Output the aggregated results showing product names and total quantities.

💡 Why This Matters

🌍 Real World

Many companies store order or event details as JSON in databases. Extracting and analyzing this semi-structured data helps understand customer behavior and sales trends.

💼 Career

Data analysts and engineers often need to parse JSON data in SQL to prepare clean datasets for reporting and machine learning.

1

Create source table with JSON data

Create a source table called raw_orders with two columns: order_id (integer) and order_items (JSON). Insert exactly these two rows: (1, '[{"product": "T-shirt", "quantity": 2}, {"product": "Hat", "quantity": 1}]') and (2, '[{"product": "T-shirt", "quantity": 1}, {"product": "Shoes", "quantity": 3}]').

dbt

# Your code here to create raw_orders table with JSON data

Hint

Use create table to define the table and insert into to add the two rows with JSON strings exactly as shown.

2

Define minimum quantity threshold

In your dbt model SQL file, create a variable called min_quantity and set it to 2. This variable will be used to filter products with quantities greater or equal to this value.

dbt

create table raw_orders (
    order_id int,
    order_items json
);

insert into raw_orders (order_id, order_items) values
(1, '[{"product": "T-shirt", "quantity": 2}, {"product": "Hat", "quantity": 1}]'),
(2, '[{"product": "T-shirt", "quantity": 1}, {"product": "Shoes", "quantity": 3}]');

-- Define min_quantity variable
-- Your code here

Hint

Use dbt Jinja syntax {% set min_quantity = 2 %} to define the variable.

3

Parse JSON and aggregate quantities

Write a SQL query in your dbt model that:
1. Selects order_id and unnests the order_items JSON array into rows with columns product and quantity.
2. Filters rows where quantity is greater or equal to min_quantity.
3. Groups by product and sums the quantity as total_quantity.

dbt

create table raw_orders (
    order_id int,
    order_items json
);

insert into raw_orders (order_id, order_items) values
(1, '[{"product": "T-shirt", "quantity": 2}, {"product": "Hat", "quantity": 1}]'),
(2, '[{"product": "T-shirt", "quantity": 1}, {"product": "Shoes", "quantity": 3}]');

{% set min_quantity = 2 %}

-- Write SQL to parse JSON and aggregate quantities
-- Your code here

Hint

Use jsonb_array_elements to unnest JSON arrays and cast quantities to integers for filtering and summing.

4

Display aggregated product quantities

Add a select statement to print the final aggregated table showing product and total_quantity columns.

dbt

create table raw_orders (
    order_id int,
    order_items json
);

insert into raw_orders (order_id, order_items) values
(1, '[{"product": "T-shirt", "quantity": 2}, {"product": "Hat", "quantity": 1}]'),
(2, '[{"product": "T-shirt", "quantity": 1}, {"product": "Shoes", "quantity": 3}]');

{% set min_quantity = 2 %}

select
  item->>'product' as product,
  sum((item->>'quantity')::int) as total_quantity
from raw_orders,
  jsonb_array_elements(order_items::jsonb) as item
where (item->>'quantity')::int >= {{ min_quantity }}
group by product
order by product;

-- Print the results
-- Your code here

Hint

Run the query to see the aggregated product quantities filtered by the minimum quantity.

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