PipeloomStripe to BigQuery
Load Stripe charges, customers, invoices and subscriptions into BigQuery, partitioned and clustered for inexpensive revenue reporting.
The pipeline as it appears on the Pipeloom canvas. You can add transforms or triggers on the same canvas later.
What to know about Stripe into BigQuery
Amounts are integers in minor units
A Stripe charge amount is in the smallest currency unit. BigQuery stores it as INT64, and dividing by 100 gives a FLOAT64 for two-decimal currencies. Convert in a view so reports never show cents as dollars.
Metadata and nested objects are JSON
Stripe's metadata, line items and other nested objects land in BigQuery JSON columns. Read a metadata key with JSON_VALUE(metadata, '$.plan') instead of flattening the table.
Do not let the 30-day Events window cost you history
Stripe guarantees events for 30 days only. Sync at least that often, turn on cursor-age validation for high-volume streams, and never use Full Refresh | Overwrite for Events, which would replace the table with the last 30 days.
Cluster-friendly lookups
Tables are clustered by _airbyte_extracted_at and the primary key, so looking up a customer or charge by id, with a date filter on _airbyte_extracted_at, scans little data.
Example: monthly charge volume by a metadata key
SELECT
DATE_TRUNC(DATE(created), MONTH) AS month,
JSON_VALUE(metadata, '$.plan') AS plan,
SUM(amount) / 100 AS gross
FROM stripe.charges
WHERE _airbyte_extracted_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
GROUP BY 1, 2
ORDER BY 1;Assumes a dataset named stripe, and that you put a plan key in your Stripe metadata. Adjust to your own keys.
Set it up
- Create the Stripe sourceAuthenticate with A restricted Stripe API key with read access, plus your Stripe account ID. You need: Access to the Stripe account you want to replicate, and its account ID. A restricted API key created for Pipeloom with Read permissions only.
- Create the BigQuery destinationAuthenticate with A Google Cloud service account with the BigQuery User and BigQuery Data Editor roles, using a JSON key. You need: A Google Cloud project with BigQuery turned on, and a dataset to write to. Create it in the same location as the datasets you will join against. A service account with the BigQuery User and BigQuery Data Editor roles. For production loads, a Cloud Storage bucket for staging with an HMAC key, and the Storage Object Admin role for the service account.
- Connect them and pick streamsDraw the edge on the canvas and choose streams and sync modes. Stripe supports full refresh and incremental; BigQuery supports every mode: full refresh overwrite, append and overwrite deduped, incremental append and incremental append deduped.
- Run, then scheduleThe first sync loads each stream in full. Then set the schedule.
What lands in BigQuery
- A table per stream with typed columns plus _airbyte_raw_id, _airbyte_generation_id, _airbyte_extracted_at and _airbyte_meta.
- Tables partitioned by day on _airbyte_extracted_at and clustered by that column and the primary key, so filtering on the partition column scans less data.
Type mapping
- object and array → JSON
- string → STRING
- integer → INT64
- number → NUMERIC
- timestamp with time zone → TIMESTAMP
- timestamp without time zone → DATETIME
- boolean → BOOL
When Stripe's schema changes
The destination tables are updated as the source changes. Namespaces map to BigQuery datasets, and invalid characters in names are replaced with underscores.
Streams you can sync
Includes Charges, Customers, Invoices, Subscriptions, Payment Intents, Payouts, Refunds, Balance Transactions and more. The full list is in the reference.
What it costs
A sync is metered at 1 credit per vCPU-minute, and a default sync uses 2.5 vCPUs. As an example, not a benchmark, a sync that takes 3 minutes uses 8 credits. Run 24 times a day, that is about 5,760 credits a month, more than Free's 1,500 but within Starter's 12,000 ($19 a month). Your own sync time decides the real figure, so try the calculator with your numbers.
Questions and fixes
Why must a Stripe sync run at least every 30 days?
Stripe's API cannot list objects changed since a point in time, so incremental syncs read the Events API, and Stripe guarantees events only for the last 30 days. A stream whose cursor is older than that cannot pick up changes older than 30 days. Streams listed in "Streams with API Data Retention Validation" fall back to a full refresh when their cursor is stale, which is recommended for Charges, Invoices, Invoice Items, Payment Intents and Payouts.
Why did my Events history shrink after a full refresh?
Stripe keeps events for 30 days. A Full Refresh | Overwrite sync of the Events stream replaces the destination table with just those 30 days, deleting older rows. Use an append mode for Events if you want to keep history.
Which Stripe streams sync by creation date rather than update date?
Balance Transactions, Customer Balance Transactions, Events, File Links, Files, Setup Attempts, Shipping Rates and Transfer Reversals are not covered by the Events API, so they sync by their created field. They are append-only in practice.
Why does a sync fail with "Quota exceeded for concurrent script queries per project"?
That quota is shared with everything else in the project. Set Job Execution Project ID to a separate project so the connector's jobs count against that quota, or sync fewer streams at once. Data still lands in the dataset under Project ID.
Why does a load fail with "Fail to complete a load job in big query"?
BigQuery load jobs time out after 30 minutes of waiting, and two syncs loading into the same table at once are not supported. Make sure each table is written by one sync, and use an incremental mode to load less per sync.
Should I use the Cloud Storage bucket or standard inserts?
Use the bucket for production because it is faster for large volumes. Standard inserts need nothing to stage and suit small volumes and quick tests. Buckets using customer-managed encryption keys are not supported.
Sync Stripe to BigQuery today
Free forever, no card. Starter is $19 a month for three seats.