Silver and Gold Modelling at Billions of Events a Day: Nested or Exploded?

Silver and Gold Modelling at Billions of Events a Day: Nested or Exploded?

Click to open full size.

At billions of nested events a day, “keep silver nested” and “explode silver into entity tables” are both wrong as blanket rules. Keep one 1:1 nested silver table as the system of record, and explode an entity into its own table only when queries filter, join or aggregate at that entity’s grain. Two questions get mixed up here. Rows: exploding an array turns 2B rows into 100B, and row count drives sort, shuffle and join cost. Bytes: a fat sub-object costs the same whether it’s nested or flattened, but someone still has to store and rewrite it. Answer both once per entity, not once per pipeline.

The Scenario

One Kafka topic feeds the lakehouse. Every event is deeply nested: a header, a few one-to-one sub-objects, and several arrays of child objects. Fully flattened, one event holds hundreds of data points.

Illustrative figures: assumed, not measured.

The layers so far:

The question: in silver, do you explode the arrays into 10–20 entity tables (one of them 100B rows a day), or keep 2B rows a day with the nested parts as STRUCT, ARRAY, MAP or VARIANT?

What the Docs Say, and Where They Stop

The docs give the options. They don’t give a rule for choosing at 100B rows a day, hundreds of columns and zero tolerance for loss. The rest of this post is that rule, built from how the storage and engines actually behave.

The Three Options

flowchart LR
    K["Kafka topic"] --> B1["bronze1
raw, 1 row per record"]
    B1 --> B2["bronze2
typed, nested, 1:1"]
    subgraph A["A: nested silver"]
        SA["silver.event
2B rows/day
structs + arrays + variant"]
    end
    subgraph B["B: exploded silver"]
        SB1["silver.entity_1
100B rows/day"]
        SB2["silver.entity_2 … entity_20"]
    end
    subgraph C["C: hybrid (recommended)"]
        SC["silver.event
system of record"] --> SC1["silver.entity_hot
only hot entities"]
    end
    B2 --> SA
    B2 --> SB1
    B2 --> SB2
    B2 --> SC

Option C is A with targeted parts of B. The rest of the post is about where to draw the line.

How It Works Underneath

Five mechanisms decide the line. Each one explains a row of the table above.

1. Nested structs are already columns

In Parquet, a struct is not a blob. Every leaf field is stored as its own column chunk, with repetition and definition levels recording where it sits in the nesting. Reading header.customer_id reads that one leaf, not the whole header. (See /parquet/spark/data-engineering/2026/10/29/Parquet-Under-The-Hood/ for the layout.)

So keeping a one-to-one part nested costs exactly what flattening it would. Flattening header.customer_id into a top-level customer_id gives the same Parquet column and the same bytes. That says nothing about whether those bytes belong in the table at all: a 4 TB/day struct is still 4 TB/day once flattened (see Fat 1:1 Sub-Objects). And liquid clustering can use a struct field directly, at any depth, with dot notation.

silver.event (one row per event)

  event_id | header.ts | header.customer_id | device.os | items: ARRAY<STRUCT<sku, qty, price>>
  ---------+-----------+--------------------+-----------+-------------------------------------
  e1       | 10:00     | c42                | ios       | [{s1,1,9.0},{s7,2,4.5}, … 50]
  e2       | 10:01     | c17                | android   | [{s3,1,2.0}, … 48]

  Parquet column chunks:  event_id | header.ts | header.customer_id | device.os | items.sku | items.qty | items.price
                          one column per leaf, even inside the array

The 1:1 parts (header, device) never need exploding: there’s nothing to explode. The rows decision is only about the arrays. Fat 1:1 parts raise a different, bytes decision, covered below.

2. Array elements can’t drive file skipping

The arrays are also stored as leaf columns (items.sku), so column pruning still works. What you lose is file skipping:

A query like WHERE exists(items, i -> i.sku = 's7') on the nested table must read the items.sku column in every file of the date range. Clustering can’t help, because a file’s rows each hold 50 different SKUs.

Rule: if your important queries filter or join on a field that lives inside an array, that array is a candidate entity table. If they only read it after filtering on header fields, keep it nested.

3. Exploding multiplies rows, writes and maintenance, not just storage

Going from 2B rows to 100B rows doesn’t multiply everything by 50. Some costs follow bytes, and the child fields’ bytes are the same before and after the explode. Others follow rows, and those grow 50×.

So “100B rows means many more files” is mostly not true; the file count follows bytes. “100B rows are harder to manage” is true: everything that touches rows one by one (explode, cluster sort, MERGE, joins) gets 50× the work, on every write and every rewrite.

Storage is usually the smallest part of the bill. When an entity table is clustered by keys that keep a parent’s children together, the repeated parent columns (event_id, event_ts) become long runs of identical values, which Parquet’s dictionary and run-length encodings store compactly. The bigger costs are elsewhere:

At 10–20 entities, fully exploding means running 10–20 high-volume tables that each need clustering, compaction and recovery. That’s the hidden cost of option B, and the reason to explode only where reads pay it back.

A cheaper middle step: the narrow entity table. Explode only the keys and the few fields people filter on (event_id, event_ts, sku, qty), clustered for those filters. Leave the full child detail nested in silver.event and join back on event_id for the rare wide read. Most of the read win, a fraction of the width.

4. Fan-out to many tables: one stream per sink, or one foreachBatch

Every entity table needs an exactly-once path from the event table. Two documented ways, with opposite trade-offs:

flowchart LR
    subgraph S["One stream per sink (Databricks' recommendation)"]
        E1["silver.event"] --> Q1["stream 1
checkpoint 1"] --> T1["entity_items"]
        E1 --> Q2["stream 2
checkpoint 2"] --> T2["entity_devices"]
        E1 --> Q3["stream 3
checkpoint 3"] --> T3["entity_parties"]
    end
    subgraph F["One foreachBatch, many writes"]
        E2["silver.event"] --> QF["stream
one checkpoint"]
        QF -->|"txnAppId + txnVersion"| U1["entity_items"]
        QF -->|"txnAppId + txnVersion"| U2["entity_devices"]
        QF -->|"txnAppId + txnVersion"| U3["entity_parties"]
    end

At a few hot entities, separate streams are simpler and isolate failures. If you insist on many entities, the repeated reads of a 2B-row-a-day table are the argument for foreachBatch, and the txnAppId rule becomes part of your runbook.

def write_entities(batch_df, batch_id):
    batch_df.persist()
    for name, build in ENTITY_BUILDERS.items():          # each returns an exploded DataFrame
        (build(batch_df).write.format("delta").mode("append")
            .option("txnAppId", f"silver_entities_v3_{name}")  # change on new checkpoint
            .option("txnVersion", batch_id)
            .saveAsTable(f"silver.entity_{name}"))
    batch_df.unpersist()

(spark.readStream.table("silver.event")
    .writeStream.foreachBatch(write_entities)
    .option("checkpointLocation", "/Volumes/ops/chk/silver_entities_v3")
    .start())

5. Gold materialized views refresh incrementally only on the right shapes

Gold is where nested silver gets its final bill. A materialized view (MV) over silver is refreshed incrementally (only changed data) or fully (re-run the whole query). Databricks runs a cost analysis and picks the cheaper one, and the event log’s planning_information shows which technique it used.

The incremental refresh docs don’t list explode or other generators. So a gold MV that explodes a 50-element array from silver.event is untested territory at 100B rows a day. Set REFRESH POLICY INCREMENTAL STRICT on it, which fails instead of silently falling back to a full recompute. If it fails, that’s your evidence the entity belongs in silver as a table.

CREATE MATERIALIZED VIEW gold.daily_sku_sales
  REFRESH POLICY INCREMENTAL STRICT
  SCHEDULE EVERY 1 HOUR
AS SELECT event_date, sku,
          SUM(CAST(qty * price AS DECIMAL(18,2))) AS revenue,
          COUNT(*)                                AS lines
   FROM silver.entity_items
   GROUP BY event_date, sku;

Two more MV limits shape gold: MVs don’t support identity columns or surrogate keys, and standalone MVs run only in triggered mode. Dimensions that need surrogate keys stay as tables.

Fat 1:1 Sub-Objects: A Bytes Problem, Not a Rows Problem

Take one 1:1 struct in silver.event that’s 4 TB a day on its own. There’s nothing to explode, and flattening it changes nothing. It’s a pure bytes problem: about 1.5 PB a year in that one column, before replication.

So it’s cheap to read and expensive to store and rewrite. The fix depends on who reads it:

Look inside it first. If most of the 4 TB is a few large fields (long strings, blobs, embedded payloads), splitting off or dropping just those fields does most of the work.

Map of Structs vs an Entity Table: Which Parquet Benefits Survive?

A common payload shape is a map keyed by an entity ID:

-- nested, in silver.event
parties MAP<STRING, STRUCT<role: STRING, amount: DECIMAL(18,2), country: STRING>>

-- exploded, as an entity table
silver.entity_parties (event_id STRING, event_date DATE, party_id STRING,
                       role STRING, amount DECIMAL(18,2), country STRING)
CLUSTER BY (event_date, party_id)

Same data. The question is which of the usual Parquet and Delta benefits (column pruning, predicate pushdown, compression, file skipping) still apply to the map. Short answer: you keep the ones that read less of each column, and lose the ones that skip data.

How a map is stored

Parquet stores a map as a repeated key_value group with a required key and an optional value. Every leaf becomes its own column chunk, exactly like a struct, but repeated: one value per map entry, with repetition levels marking where each row’s entries start.

Benefit by benefit

What that adds up to

So a map of structs is as good as an entity table for scanning and storing, and much worse for finding. At 2B events a day, “finding” means every query on a map field reads that leaf column for the whole time range.

Same answer on Iceberg

Switching the table format doesn’t rescue the map. Iceberg has the same split for the same reason: its skipping metadata needs one value per row.

The last row is the one to remember. Both formats treat struct fields as first-class columns and map entries as opaque. That’s why turning a small fixed-key map into a struct gets you everything back, on either format.

Three map shapes, three answers

flowchart TD
    A{"Is the key set small
and known in advance?"} -->|yes| S["Model as a STRUCT with named fields
pushdown, stats and clustering work"]
    A -->|"no: open-ended IDs"| B{"Do queries filter, group
or join on the key or a value field?"}
    B -->|no| M["Keep the MAP
pruning + compression only"]
    B -->|yes| E["Entity table
CLUSTER BY (event_date, id)"]

Choosing: map of structs or entity table

When the key set is open-ended, weigh these per map. Most maps land clearly on one side; if one row points hard at the entity table, that usually decides it.

Two common traps:

• Exploding “for the dashboards” when dashboards only read gold aggregates. Build the aggregate MV from a one-off explode in a job instead of a 100B-row silver table.
• Keeping the map “to save rows” when every gold job explodes it. You pay the explode CPU on every run and still get no skipping. If the map is exploded everywhere downstream, explode it once in silver.

Checking it on your own data

Run EXPLAIN FORMATTED on a map query and on the same query against the entity table:

EXPLAIN FORMATTED
SELECT event_id, parties['p9'].amount
FROM silver.event
WHERE event_date = '2026-10-30' AND parties['p9'].amount > 1000000;

The behaviour above is from open-source Spark and Delta. On Databricks, Photon runs the scan with its own engine, so confirm with the query profile rather than assuming.

Grain: Write It Down Per Table

Every table gets one sentence: “one row per …”, and a key that enforces it. At this scale, an unstated grain is how duplicates and fan-out joins get into gold.

Two rules that matter at 100B rows:

• Carry the parent key and a position into every child row. posexplode (or inline plus a sequence number) gives item_seq, so the child key is deterministic. A replay produces the same keys, and gold can reconcile.
• Never join two exploded entities of the same event directly. 50 items × 10 devices is 500 rows per event. Aggregate each to the event (or a shared key) first, then join. This is the classic 1:N fan-out, multiplied.

Table Layouts at This Scale

Clustering by access pattern

Liquid clustering is the layout for every silver and gold table here, Delta or managed Iceberg. Keys come from the filters each table actually serves. (Mechanics in /databricks/delta-lake/data-engineering/performance/lakehouse/2026/10/26/Liquid-Clustering-Under-The-Hood/.)

File sizes

At 100B rows a day an entity table passes 10 TB quickly. Existing files aren’t re-optimized when the target grows, so expect a mix of sizes in older data. Don’t repartition(n) or coalesce(n) right before the write; let optimized writes and clustering size the files.

Delta or Iceberg

Pick by who else reads and writes the tables. The modelling decision is the same in both.

VARIANT for the volatile parts

Use typed structs for the parts of the payload with a stable schema, and VARIANT for the parts that change often or vary by event type. Databricks recommends VARIANT over JSON strings for semi-structured data.

Shredding doesn’t apply to VARIANT inside arrays or maps. A volatile array of children is a stronger candidate for exploding, or for an entity table with a VARIANT column per child.

Gold: Facts, MVs and Metric Views

Gold is a dimensional model. The choice is how each gold object is built and kept fresh.

Metric views: define the measure once

A plain view fixes its aggregation and grouping when you create it. A metric view separates measures from the fields you group and filter by, and the engine generates the correct aggregation at query time. Ratios and distinct counts stay right at any grain, because they’re recomputed, not re-summed.

CREATE OR REPLACE VIEW gold.sales_metrics
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1
source: gold.fact_sales_line
joins:
  - name: product
    source: gold.dim_product
    on: source.product_key = product.product_key
fields:
  - name: Event Date
    expr: event_date
  - name: Category
    expr: product.category
measures:
  - name: Revenue
    expr: SUM(revenue)
  - name: Buyers
    expr: COUNT(DISTINCT customer_key)
  - name: Revenue per Buyer
    expr: SUM(revenue) / COUNT(DISTINCT customer_key)
$$;

SELECT `Category`, MEASURE(`Revenue per Buyer`) AS rpb
FROM gold.sales_metrics
WHERE `Event Date` >= '2026-10-01'
GROUP BY ALL;

Joins live in the YAML, not in the query. Metric views can also materialize pre-computed aggregations and rewrite queries to use them; that feature is marked experimental, so treat it as a performance layer, not the only copy of a number.

How gold populates from each silver option

flowchart TD
    SE["silver.event
nested, 1:1"] -->|"header grain:
plain select"| GF1["gold.fact_event"]
    SE -->|"hot entity:
stream + posexplode"| SI["silver.entity_items"]
    SI --> GF2["gold.fact_sales_line"]
    SE -->|"cold entity:
explode in a view or batch job"| GC["gold.cold_entity_report"]
    D["gold.dim_* tables
identity surrogate keys"] --> GF1
    D --> GF2
    GF2 --> MV["gold MVs
INCREMENTAL STRICT"]
    GF1 --> MET["metric views"]
    GF2 --> MET

Zero Fault Tolerance at Billions a Day

The docs cover exactly-once streaming into Delta. They don’t cover what it takes to keep 20 tables consistent through bugs, replays and schema changes. These are the rules that do.

The hybrid model makes every one of these cheaper: there’s one dedupe point, one table to rebuild from bronze, and entity tables are pure functions of it.

When to Explode an Entity

Decide per entity, using what queries do with it.

Hard limits that force the choice

• Liquid clustering: up to four keys; no struct, map or array columns as keys, and no elements inside maps or arrays.
• VARIANT: not a clustering, partition or Z-order key; no comparisons, grouping, ordering or set operations on the VARIANT itself.
• Variant shredding: not for VARIANT inside arrays or maps.
• Materialized views: no identity columns or surrogate keys; incremental refresh needs serverless.

flowchart TD
    A{"Is it 1:1 with the event?"} -->|yes| S["Keep as struct in silver.event"]
    A -->|"no: it's an array"| B{"Do important queries filter
or join on a child field?"}
    B -->|no| C{"Aggregated at child grain
by many consumers?"}
    C -->|no| N["Keep nested
explode in gold views when needed"]
    C -->|yes| E
    B -->|yes| D{"Do they read most child fields?"}
    D -->|yes| E["Full entity table in silver
CLUSTER BY child filter keys"]
    D -->|no| W["Narrow entity table
keys + filter fields, join back on event_id"]
    E --> G["Gold MVs on top
INCREMENTAL STRICT"]
    W --> G

Checking It Works

1. Reconcile row counts daily. SUM(size(items)) per event_date in silver.event must equal COUNT(*) per event_date in silver.entity_items, and COUNT(DISTINCT event_id) must equal COUNT(*) in silver.event.
2. Check file skipping on the top queries. In the query profile, compare files read with files pruned. A child-field filter on the nested table that reads every file is your signal to explode that entity.
3. Check MV refresh technique. Query the pipeline event log for planning_information events. Anything you expected to be incremental showing FULL_RECOMPUTE needs a fix, or INCREMENTAL STRICT to make it fail loudly.
4. Check layout. DESCRIBE DETAIL shows clustering keys; confirm the keys are in delta.dataSkippingStatsColumns when the table has hundreds of columns.

Sources

• Data Modeling Best Practices & Implementation on a Modern Lakehouse, Databricks blog, 20 October 2022 (star schema, informational PK/FK, identity surrogate keys, Z-order and sparing partitioning)
• What is the medallion lakehouse architecture?, Databricks documentation (bronze, silver, gold contents; nested data options in silver; gold as a dimensional model; large history in silver)
• Query variant data and Variant vs JSON strings, Databricks documentation (VARIANT limits, recommendation over JSON strings, case-sensitive paths)
• Use variant shredding, Azure Databricks documentation, updated 11 September 2026 (shredding requirements, defaults, Delta and Iceberg properties, array and map limitation)
• Use liquid clustering for tables, Databricks documentation (key types, struct fields with dot notation, four-key limit, hierarchical clustering, clustering on write thresholds, CLUSTER BY AUTO, managed Iceberg support)
• Data skipping, Databricks documentation (first 32 columns, delta.dataSkippingStatsColumns, string truncation, predictive optimization stats)
• VACUUM, Databricks documentation (7-day default retention, delta.deletedFileRetentionDuration)
• Control data file size, Databricks documentation (autotuned file sizes, optimized writes, auto compaction)
• Delta table streaming reads and writes, Databricks documentation (exactly-once, one stream per sink, txnAppId/txnVersion, checkpoint and ignoreMissingFiles warnings)
• Materialized views and Incremental refresh for materialized views, Databricks documentation (row tracking, serverless, supported constructs, DECIMAL casting, refresh policy, planning_information, no identity columns)
• Unity Catalog metric views, Create a metric view, Metric view YAML syntax and Materialization for metric views, Databricks documentation (measures vs fields, MEASURE(), YAML v1.1, joins, experimental materialization)
• Parquet logical types: MAP and LIST, Apache Parquet format spec (map stored as a repeated key_value group with a required key)
• Delta transaction log protocol: per-file statistics and Delta source DataSkippingReader.scala (SkippingEligibleDataType: min/max only for numeric, date, timestamp, string and VARIANT)
• Apache Iceberg table spec (manifest lower_bounds/upper_bounds by field ID; partition source columns cannot be in a map or list) and Iceberg source ParquetMetrics.java (no bounds for list and map fields)
• Apache Spark source: ParquetFilters.scala (pushdown only for non-repeated primitive fields; map and list removed), SelectedField.scala (nested pruning through map lookups keeps the key type), SQLConf.scala (spark.sql.mapKeyDedupPolicy, default EXCEPTION)
• Parquet nested encoding, Apache Parquet documentation (structs and arrays stored as leaf columns with repetition and definition levels)