Application databases weren't designed for analysis. Often the interesting data arrives as JSON: one big blob per event or per record, with nested objects, arrays inside arrays, and fields that exist for some rows and not others. Nobody can query it comfortably, so analysts write the same tangled expressions again and again, each slightly differently, and the reports drift apart.
A star schema fixes that. I have rebuilt data this way a few times, and it has consistently cut the number of "why don't these two reports match?" conversations. Here's the approach.
First, keep the raw data
Before anything else, land the JSON exactly as it arrives in a raw table. Don't clean it on the way in. When something goes wrong later, and it will, you want to be able to rebuild everything from the original.
A raw table might have only a few columns: an id, the time it was received, and the payload as JSON or a string.
Decide what one row means
This step is the one people skip, and it causes most of the trouble. A fact table needs a clear grain, which is a sentence completing "one row in this table is a single...". One order. One order line. One session. One video play.
If you can't say it in a sentence, you're about to build something that double-counts. Pick the lowest level you will need to answer questions about. You can always add up from a lower level. You can't split a total back down.
Find the facts and the dimensions
Facts are the things that happened, with numbers you can add up: quantity, price, duration. Dimensions are the things you describe them by: who, what, where, when.
For an order system you might end up with:
fact_order_lines: one row per item in an order, with quantity, unit price and discount.dim_customer: one row per customer.dim_product: one row per product.dim_date: one row per calendar day.
Each fact holds keys pointing at the dimensions. That's the star shape: a fact in the middle and dimensions around it.
Flatten the JSON
Now the actual unpacking. In BigQuery, for example, you can pull values out of a JSON column and expand arrays into rows:
SELECT
JSON_VALUE(payload, '$.order.id') AS order_id,
JSON_VALUE(payload, '$.user.id') AS customer_id,
TIMESTAMP(JSON_VALUE(payload, '$.created_at')) AS ordered_at,
JSON_VALUE(item, '$.sku') AS sku,
SAFE_CAST(JSON_VALUE(item, '$.qty') AS INT64) AS quantity,
SAFE_CAST(JSON_VALUE(item, '$.price') AS NUMERIC) AS unit_price
FROM raw.events,
UNNEST(JSON_QUERY_ARRAY(payload, '$.order.items')) AS item
WHERE JSON_VALUE(payload, '$.type') = 'order_created'
Two things to notice. UNNEST turns each item in the array into its own row, which is what gives you the order-line grain. And SAFE_CAST returns null instead of failing when a value is malformed. In real data some will be, and you'd rather count and investigate those rows than have the whole job fall over.
Deal with the awkward parts
Fields that come and go. Handle missing values explicitly. Decide whether a missing discount means zero or unknown, and write that decision down.
Changing structure. Backends change. A field gets renamed, or a number turns into a string. Keep an eye on this with checks that count how many rows have each shape, and alert you when a new one appears.
Duplicates. Events can arrive twice. Choose a unique key, usually an event id, and deduplicate on it, keeping the latest. Do this before building anything on top.
History in dimensions. If a customer changes city, should old orders show the old city or the new one? For most businesses the current value is fine. If you need the history, look into slowly changing dimensions and decide deliberately.
Test before you trust it
I add a few checks to every model I build. Row counts in the fact table match the raw data after allowing for known exclusions. Keys are unique where they should be. No fact row points at a dimension row that doesn't exist. Totals, such as revenue for yesterday, agree with another source you trust.
These take an hour to write and have caught real problems every time.
What you gain
Once the model exists, queries get shorter, because the hard work is done once. A question like "revenue by product category by month" is a few joins and a group by, and different analysts get the same answer. Reports get quicker because they read from tidy tables. And new team members can learn the structure in an afternoon instead of reading old queries to guess what the JSON means.
It isn't glamorous work, but it's where a lot of the value in analytics is made.