Stripe to BigQuery: how to load payments data your team can actually query

How do you connect Stripe to BigQuery?

You connect Stripe to BigQuery by reading Stripe objects through the paginated REST API and writing them into BigQuery tables, because there is no native BigQuery destination inside Stripe. Something sits in the middle: a managed connector, a scheduled script against the API, or Stripe's own data pipeline export.

The Stripe API returns 100 objects per page and walks forward with a starting_after cursor, and it enforces rate limits (roughly 100 read requests per second in live mode). That matters the moment you backfill. Pulling several years of charges by hand means thousands of sequential page requests, careful cursor bookkeeping, and retry logic for the calls that get throttled or time out. It is slow and brittle, which is exactly why most teams do not hand-roll it past the first prototype.

Practically, the connection has three parts. First, a restricted Stripe API key with read scope on the objects you replicate. Second, a BigQuery landing spot: a project, a dataset, and a service account that can create tables and run MERGE. Third, the transfer itself, which should page through each object type, land raw JSON or typed columns in a staging table, and merge into the target. The managed path is the Stripe to BigQuery integration, which owns the pagination, the watermark, the staging table, the MERGE, and the retries. If you are also loading accounting data, the same pattern runs through the QuickBooks to BigQuery connector, and if you are weighing warehouses, the equivalent write-up for the other one is Stripe to Snowflake.

How do you load Stripe data into BigQuery incrementally?

Load Stripe incrementally by keeping a watermark of the last created or updated value you saw, requesting only newer objects on each run, and MERGE-ing the result into BigQuery keyed on the object id. You can also replay the events stream from your last cursor when you need every state change, not just the current row.

There are two ways to get changed records. Filtering on created is simple and works for append-heavy objects like charges and balance_transactions. For objects that mutate after creation (subscriptions, invoices, disputes), the events stream is more honest: it emits a record every time something changes, so you catch the update to a subscription that was created last year. Store the last event id or timestamp as your cursor and resume from it.

Whichever source you use, the load into BigQuery follows the same shape. Land the batch in a staging table, then collapse duplicates before you merge, because a single batch can carry several versions of the same object (an invoice that went from draft to open to paid). Keep the newest version per id:

Step	What runs	Why it matters
Dedupe the batch	ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated DESC), keep rank 1	Stops a multi-version batch from creating duplicate target rows
MERGE on id	MERGE INTO target USING staging ON target.id = staging.id	Makes retries idempotent, so a re-run after a timeout produces the same state
Advance the watermark	Store the max created / updated or last event id	Next run resumes exactly where this one stopped

Overlap the window slightly on each run (re-pull the last few minutes) so an object committed out of clock order is not skipped. Because the MERGE is keyed on the id and idempotent, re-reading a handful of rows costs nothing and buys you correctness. One BigQuery detail worth knowing: streaming inserts sit in a buffer for a short while before they can be merged or updated, so most teams load Stripe with batch load jobs into staging and merge from there rather than streaming row by row.

How do you calculate Stripe fees and net payouts in BigQuery?

Calculate real fees and net revenue by joining balance_transactions to the charge, not by reading the charge alone. The charge tells you the gross amount, but the processing fee and the net amount that actually settles live on the balance_transaction, along with the link to the payout that swept it into your bank account.

Every charge, refund, and dispute produces a balance_transaction. That row carries three numbers you want: amount (gross), fee (what Stripe kept), and net (what you actually earned). It also carries a payout reference once the money is paid out. So the finance-grade model is a join: charge to balance_transaction on the charge's balance_transaction id, then balance_transaction to payout on the payout id. Sum net grouped by payout to reconcile a single bank deposit down to the individual charges that composed it.

Two traps here. First, all three amounts are in minor units, so divide by 100 before you sum, and respect zero-decimal currencies (more on that below). Second, refunds and disputes create their own negative balance_transactions, so net revenue for a period is the sum of every balance_transaction, positive and negative, not just the charges. If your dashboard shows gross charges as revenue, it is overstating the number by the entire Stripe fee line plus refunds.

How do Stripe fields map to BigQuery types?

Map Stripe ids to STRING, amounts in minor units to NUMERIC after dividing by 100, Unix timestamps to TIMESTAMP in UTC, and the nested metadata object to JSON so you can query keys without flattening first. The two that cause silent errors are amounts (minor units, and zero-decimal currencies) and timestamps (epoch seconds, not a date string).

Start with the object-to-table layout, then the field types.

Stripe object	BigQuery table	Notes
charge / payment_intent	stripe_charges	Gross amount and status. Join to balance_transaction for fee and net
balance_transaction	stripe_balance_transactions	Holds fee, net, and the payout link. The source of truth for real revenue
payout	stripe_payouts	One bank deposit. Reconcile against summed balance_transaction net
refund	stripe_refunds	Creates a negative balance_transaction. Subtract from period revenue
invoice	stripe_invoices	Line items drive MRR. Separate recurring items from one-time charges
subscription	stripe_subscriptions	Status and interval. Filter to active when computing MRR
customer	stripe_customers	Join key for everything. Watch for deleted customers flagged in the object

Stripe field / type	BigQuery type	Notes
amount (minor units)	NUMERIC	Divide by 100 for most currencies to get major units. Never store money as FLOAT64
currency (ISO code)	STRING	Lowercase in Stripe (usd, jpy). Drives whether you divide by 100 at all
zero-decimal amount (JPY, KRW)	NUMERIC	Already whole units. Do not divide by 100, or you shrink revenue 100x
created / updated (Unix seconds)	TIMESTAMP	Epoch seconds. Use TIMESTAMP_SECONDS(created) and treat as UTC
id (ch_, pi_, in_)	STRING	The natural MERGE key. Stable and unique per object
metadata (key-value object)	JSON	Store raw so you can query metadata.order_id without a reload
status (enum-like)	STRING	New values appear over time, so validate downstream rather than constraining
livemode (boolean)	BOOL	Keep it so test-mode rows never leak into finance reports

The zero-decimal case is worth repeating because it is a real financial bug, not a rounding nuisance. A JPY charge of 5000 means 5000 yen, not 50. If your loader divides every amount by 100, Japanese revenue silently reads at one percent of actual. Branch on the currency code, or store the minor-unit value untouched and apply the divisor in a reporting view where the logic is visible and testable. While you are shaping the finance dataset, remember that revenue that arrives as a PDF invoice rather than a Stripe object can be extracted into structured rows and landed in the same dataset, so a manual wire transfer sits next to card revenue instead of going missing.

How do you build MRR from Stripe data in BigQuery?

Build MRR from active subscriptions and their invoice line items, normalizing every billing interval to a monthly amount and excluding one-time charges. A yearly plan contributes its annual price divided by twelve, a monthly plan contributes its price directly, and canceled or past-due subscriptions drop out. Then reconcile the total against Stripe's own reporting.

The mechanics: read subscriptions for status and the current plan, and read invoice line items for the amount and interval actually billed. For each active subscription, convert the recurring amount to a monthly figure based on its interval (month stays as is, year divides by 12, week multiplies by roughly 4.33). Sum across active subscriptions and you have committed MRR. Keep one-time invoice items, setup fees, and proration adjustments out of the recurring number, or your MRR will jump every time someone upgrades mid-cycle.

Handle discounts and currency explicitly. A 20% coupon reduces the recurring amount, so apply it before you roll up. If you sell in multiple currencies, convert to a single reporting currency at a defined rate rather than adding raw amounts. Finally, reconcile: your computed MRR should track Stripe's billing dashboard within a small margin. When it drifts, the usual culprits are trials counted as active, canceled subscriptions still summed, or an interval you forgot to normalize.

How much does it cost to sync Stripe to BigQuery?

BigQuery bills on two axes: storage for the bytes you keep, and query cost measured either as on-demand bytes scanned or as reserved slots. Stripe volumes are usually modest, so storage is a rounding error. The real lever is how much data your queries read, and that is a table design question, not a row-count question.

Cut the scan. Partition each table by DATE on the created or updated timestamp so a query for last month reads one month of partitions instead of the whole history, and cluster on the columns you filter and join most (customer id, status, currency). A dashboard that scans a single day's partition costs a tiny fraction of one that scans every charge you have ever loaded. Select only the columns you need, since on-demand pricing charges for the bytes in the columns you touch, not the rows. If your query volume is steady and heavy, a slot reservation gives you a flat monthly compute price instead of per-query billing.

On the pipeline side, our pricing is flat by plan (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a heavy sales month does not produce a surprise invoice. See pricing for the full breakdown. Partition and cluster on the warehouse side, keep the plan flat on the pipeline side, and the total cost of a Stripe to BigQuery sync stays predictable as you grow.