Normalizing a Stripe Style Webhook Payload in DuckDB

Payment providers send events to your server by webhook, and the sensible first step is to store the raw body. The second step, usually a week later when someone asks for revenue by currency, is to turn that pile of JSON into tables. The shapes are the ones every webhook API has: an envelope with an id and a type, a payload whose structure depends on the type, nested lists, a free-form metadata map, timestamps in epoch seconds, and amounts in minor units.

This article works through that normalization on a log shaped like the events a payment provider sends. The data is synthetic: I generated 61,197 lines of events with the same structure as Stripe's public event format, because real payment data does not belong in a public article, and the structure is what matters.

Read it as it is

create table raw as
select * from read_ndjson('stripe.ndjson', sample_size=-1, maximum_object_size=1048576);

Loading 61,197 lines took 0.55 s. sample_size=-1 makes the reader look at every line to infer types, which matters here: the five event types have different payloads, and a sample of the first thousand lines would miss fields that appear only in rare types. DuckDB built one struct for data with the union of all the keys, and a field that exists in only one type is NULL in the others.

select type, count(*) n from raw group by 1 order by 2 desc;

payment_intent.succeeded         30674
invoice.paid                     15290
customer.subscription.updated     6062
charge.refunded                   4898
payment_intent.payment_failed     4273

Those counts sum to 61,197, and the next query shows why that is not the number of events.

Deduplicate retries first

Webhook providers retry when your endpoint is slow or returns an error, so the same event arrives more than once. The id is the same each time:

select count(*) as lines, count(distinct id) as distinct_events from raw;
-- 61197 | 60000

1,197 lines were repeats. Any count, sum or join over raw is wrong by about 2 percent until they are removed. Keep one row per event id:

create table events as
select * from raw
qualify row_number() over (partition by id order by created) = 1;

The qualify clause filters on a window function without a subquery. It took 0.11 s and left 60,000 rows. Keeping the earliest delivery is a choice. For an event that never changes, any copy is fine. If your endpoint can see an updated copy of the same event (some providers redeliver with a newer pending_webhooks count), decide which wins, and record the delivery count if you care. Do this once, at the boundary, and not in each report.

Pull the payments into columns

With one struct, nested access is dot notation:

create table payments as
select id as event_id, to_timestamp(created) as event_at, type,
       data.object.id as object_id, data.object.customer as customer,
       data.object.currency as currency, data.object.amount as amount_minor,
       data.object.metadata.order_id as order_id
from events
where type in ('payment_intent.succeeded', 'payment_intent.payment_failed', 'charge.refunded');

created is epoch seconds, and to_timestamp makes a real timestamp. No JSON functions were needed because the reader had already turned the nesting into structs, and a query on 60,000 events ran in milliseconds. The columns are typed (the amount is a BIGINT, not text), which is the main benefit of reading the file as structured data instead of storing a JSON string per row.

Minor units are not always cents

Amounts in the payload are in the smallest unit of the currency. For dollars and euros that is a cent, so 1999 means 19.99. For some currencies there is no minor unit. The yen and the Vietnamese dong have none, so 500 means 500 yen. Dividing everything by 100 is the standard mistake, and the totals it gives look reasonable:

currency   n      total_correct      total_if_divided_by_100
eur        7893   2,336,521.93       2,336,521.93
jpy        7811   220,028,671        2,200,286.71
usd        15602  4,496,601.24       4,496,601.24
vnd        7784   220,610,860        2,206,108.60

For euros and dollars the two columns agree. For yen and dong the "divided by 100" total is a hundredth of the real one. A dashboard that sums across currencies without converting is meaningless, and one that divides the zero-decimal ones by a hundred is also quietly wrong. The query that is right:

case when currency in ('jpy', 'vnd') then amount_minor else amount_minor / 100.0 end as amount

The list of zero-decimal currencies comes from the provider's documentation (Stripe's currency list), and it belongs in a table, not in an inline case, once there is more than one report. A handful of currencies use three decimal places, which this sample did not include, so a table with the number of decimals per currency handles all three cases. Keep the minor-unit amount as the stored value and derive the decimal one; the integer never loses precision (designing a JSON API response that maps cleanly to tables covers sending exact values in an API).

Unnest the nested lists

Invoices carry their line items in data.object.lines.data, an array of structs:

select count(*) as invoices, sum(len(data.object.lines.data)) as line_items
from events where type = 'invoice.paid';
-- 14968 | 29881

create table invoice_lines as
select id as event_id, unnest(data.object.lines.data, recursive := true)
from events where type = 'invoice.paid';

The 14,968 invoices became 29,881 rows, one per line, and recursive := true flattened the nested period struct into start and end columns. The description is missing on a third of the lines: 19,949 of 29,881 were not null. As with other nulls from nested data, check whether NULL means "no description" or "this key was not present" before filling it with a default (flattening nested JSON with pandas and DuckDB covers the unnest options in detail).

The metadata map hides the gaps

Providers give each object a free-form metadata map. In the data it was sometimes empty, sometimes {"order_id": "..."} and sometimes had a second key. DuckDB inferred a struct with both keys, and an empty map became a struct with both fields NULL:

select json_keys(to_json(data.object.metadata)) k, count(*) from events group by 1;
-- [order_id, campaign]   60000

All 60,000 events report both keys. The reader's inference cannot distinguish "no campaign key" from "campaign is null" once the type is a struct. For a metadata map whose keys are data, read it as a MAP(VARCHAR, VARCHAR) or keep it as a JSON column, and extract the keys you need (extracting fields from a JSON string column in DuckDB). If a key present means something even when its value is null, a struct is the wrong representation.

The same applies to previous_attributes on update events, which is a partial copy of the object holding only the fields that changed. A missing key there means "unchanged". Converting it to a struct and reading nulls would treat every unchanged field as set to null.

Keep the raw event

Normalized tables are for questions. Keep the original JSON too, in a table with the event id and the received time, so a bug in your normalization can be fixed by running it again, and so a new field can be added later without asking the provider to resend. Store the payload as text or JSON, and index on the id. A common layout is a raw table that is append-only, a deduplicated events table, and views for each report. When the provider adds a field, it appears in the raw table immediately and in the views when someone asks for it.

Verify webhook signatures before storing anything, which has nothing to do with JSON and everything to do with whether the payload is the provider's. The signature is computed over the raw bytes, so verify before you parse, and never re-serialize the parsed body and expect the signature to match (JSON security mistakes in apps and pipelines has more on why parsing and re-serializing changes bytes). If your payload contains secrets, redact them before logging it (hiding secrets from JSON logs). The DuckDB reader options are described in the JSON overview, and the JSON formatter is handy for reading a single event.

When normalizing is the wrong first step

You only need one number. For a single revenue figure by currency, a query over the raw file is enough. Build tables when two reports share them.

The schema is still changing. If the provider's API version is moving, keep the raw table and write views, so a change touches a view and not a pipeline.

You are reconciling with the provider. Their reports are the truth for money. Load their export next to your events and compare, instead of trusting your own normalization.

The volume is large. At billions of events, process in partitions by day and write Parquet, and let the engine read it in place. The same SQL runs.