Semi-structured data handling (JSON) in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this dbt SQL code extracting JSON values?

Given a table events with a column event_data storing JSON objects, what is the output of this query?

dbt

select
  event_data:customer:id as customer_id,
  event_data:purchase:amount as purchase_amount
from events
limit 3;

A [{'customer_id': null, 'purchase_amount': null}, {'customer_id': null, 'purchase_amount': null}, {'customer_id': null, 'purchase_amount': null}]

B [{'customer_id': '123'}, {'purchase_amount': 50.0}, {'customer_id': '456'}]

C SyntaxError: invalid JSON path syntax

D [{'customer_id': '123', 'purchase_amount': 50.0}, {'customer_id': '456', 'purchase_amount': 75.5}, {'customer_id': '789', 'purchase_amount': 20.0}]

💡 Hint

Remember that event_data:customer:id extracts the nested id inside customer in the JSON.

The query extracts nested JSON fields correctly using colon notation. Option D shows the expected output with customer IDs and purchase amounts for three rows.

❓ data_output

intermediate

How many rows will this dbt model produce after flattening JSON arrays?

Consider a table orders with a JSON column order_details containing an array of items. What is the number of rows returned by this query?

dbt

select
  order_id,
  item.value:name as item_name
from orders,
  lateral flatten(input => order_details:items) as item;

A Total rows equal to the number of unique item names

B Total rows equal to the number of orders

C Total rows equal to the sum of all items in all orders

D Total rows equal to zero due to syntax error

💡 Hint

Flattening a JSON array creates one row per array element.

The lateral flatten expands each array element into its own row, so the total rows equal the total number of items across all orders.

🔧 Debug

advanced

Why does this dbt model fail with a JSON parsing error?

This dbt model tries to parse a JSON string column but fails. Identify the cause.

dbt

select
  parse_json(event_json) as event_data
from raw_events
where event_json is not null;

A Some rows have invalid JSON strings causing parse_json to fail

B The function parse_json does not exist in dbt SQL

C The column event_json is not a string type

D Missing alias for parse_json output causes syntax error

💡 Hint

Check if all JSON strings are valid and well-formed.

parse_json fails if any string is malformed JSON. The error is due to invalid JSON in some rows.

🧠 Conceptual

advanced

Which JSON data type is best for storing semi-structured event logs in dbt?

You want to store event logs with varying fields and nested data. Which JSON data type should you use in your dbt model?

A VARIANT - supports flexible nested JSON structures

B BOOLEAN - stores true/false event flags

C INTEGER - stores numeric event codes only

D VARCHAR - stores JSON as plain text without parsing

💡 Hint

Consider a data type that supports nested and flexible JSON.

VARIANT type allows storing and querying nested JSON data efficiently in dbt.

🚀 Application

expert

How to extract nested JSON fields and aggregate purchase totals per customer in dbt?

Given a table sales with a JSON column details containing nested customer and purchase info, write a dbt SQL query to get total purchase amount per customer.

A select details.customer.id as customer_id, sum(details.purchase.amount) as total from sales group by customer_id;

B select details:customer:id as customer_id, sum(details:purchase:amount::float) as total from sales group by customer_id;

C select details['customer']['id'] as customer_id, sum(details['purchase']['amount']) as total from sales group by customer_id;

D select customer_id, sum(purchase_amount) from sales group by customer_id;

💡 Hint

Use colon notation and cast amounts to float for aggregation.

Option B correctly extracts nested JSON fields using colon notation and casts amount to float for sum aggregation.

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