Hiprup

What is the difference between structured, semi-structured, and unstructured data, and how would your approach change for each?

Data is classified by how much schema it carries. That single property decides how you store it, how you query it, and how much work stands between raw data and a number you can put in a report.

  • Structured — fixed rows and columns with declared types, as in a relational table or a clean CSV; queryable directly with SQL, and the great majority of analyst work lives here.

  • Semi-structured — self-describing records such as JSON, XML or log events, where the schema travels with each row and may differ between rows; queried with path extraction and flattening, so missing keys are normal and must be handled.

  • Unstructured — free text, images, audio and PDFs with no schema at all; nothing can be aggregated until the content is converted into structured features, historically by NLP and now often by an LLM extraction step.

  • How the approach shifts — structured data starts at analysis, semi-structured starts at parsing and flattening plus schema-drift checks, unstructured starts at extraction and then needs the extraction itself validated.

  • Effort and reliability — cost and error risk rise sharply from left to right, so pin the business question to structured sources where possible and treat extracted fields as estimates, not facts.

Key terms: schema-on-write, schema-on-read, JSON, flattening, schema drift, feature extraction, NLP, data lake

-- STRUCTURED: schema is fixed and known, so query it directly
SELECT customer_id,
       SUM(net_amount) AS revenue
FROM   fact_orders                      -- every column and type declared up front
GROUP  BY customer_id;


-- SEMI-STRUCTURED: JSON payload, keys vary per row, extract on read
-- raw_events.payload looks like:
--   {"event":"checkout","utm":{"source":"email"},"items":[{"sku":"A1"},{"sku":"B2"}]}
SELECT
    payload:event::string        AS event_name,
    payload:utm.source::string   AS utm_source,   -- key absent -> NULL, not an error
    ARRAY_SIZE(payload:items)    AS item_count    -- nested array must be measured or flattened
FROM   raw_events
WHERE  payload:event::string = 'checkout';

-- Guard against schema drift: if upstream renames utm.source, this silently goes to 100% NULL
SELECT COUNT(*)                                              AS rows_total,
       COUNT(payload:utm.source::string)                     AS rows_with_source,
       1 - COUNT(payload:utm.source::string) / COUNT(*)      AS null_rate  -- alert if it jumps
FROM   raw_events
WHERE  event_date = CURRENT_DATE - 1;


-- UNSTRUCTURED: free text has no schema; build features before aggregating
SELECT
    review_id,
    LENGTH(review_text)                                          AS char_count,
    CASE WHEN LOWER(review_text) LIKE '%refund%' THEN 1 ELSE 0 END AS mentions_refund
FROM   product_reviews;
-- Keyword flags are a crude first pass. Real analysis needs an NLP or LLM extraction step
-- producing columns such as topic and sentiment -- and those columns are estimates,
-- so sample and manually verify before reporting them as fact.

The three blocks show why the classification matters in practice rather than in theory. The structured query needs no preparation because the schema is already enforced, so the analyst starts at the aggregation. The semi-structured block extracts values by path, and the comments mark the two things that bite you: an absent key returns NULL instead of raising an error, and a nested array cannot be aggregated until it is measured or flattened.

The null-rate query that follows is the defensive habit that catches schema drift — if an upstream team renames utm.source, the extraction keeps running and quietly returns NULL for every row, so monitoring the null rate is what turns a silent failure into an alert. The unstructured block shows that free text supports only crude keyword flags until an extraction step creates real columns, and the closing comment makes the key point that those extracted fields are estimates carrying an error rate, not measured facts.

Do not stop at the three definitions — every candidate gets those right, and the differentiator is the second half of the question. Say explicitly what changes in your workflow: structured data lets you start analysing, semi-structured adds parsing and drift monitoring, unstructured adds an extraction step whose accuracy you then have to defend.

Mentioning schema drift and silent NULLs is the single strongest signal here because it only comes from having been burned by it. Expect follow-ups on where each is stored (warehouse vs lake) and on how you would analyse a column of customer reviews, where the right answer is to extract structured features first and validate them on a sample.