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.