Stripe Data in Your Warehouse: SQL for Snowflake and BigQuery

Quick answer

Current MRR and paying customers per currency, on Stripe Data Pipeline in Snowflake. The table is the same one Sigma uses.

-- Snowflake (Stripe Data Pipeline)
-- Paying customers and MRR per currency
with per_customer as (
  select customer_id, currency, sum(mrr_change) as mrr
  from stripe.subscription_item_change_events_v2_beta
  group by customer_id, currency
)
select
  currency,
  count_if(mrr > 0) as paying_customers,
  sum(mrr) / 100 as mrr
from per_customer
group by currency
order by mrr desc;

Change stripe to the database and schema your data share created, for example stripe_share.stripe.

Schema assumptions

Source	Tables used	Notes
Stripe Data Pipeline	STRIPE.subscription_item_change_events_v2_beta, STRIPE.balance_transactions, STRIPE.charges, STRIPE.customers	Same names and columns as Sigma. Minor units. merchant_id on every table. Test data in STRIPE_TESTMODE.
Fivetran	SUBSCRIPTION_HISTORY (id, customer_id, status, _fivetran_active), INVOICE, INVOICE_LINE_ITEM (subscription_item_id, price_id, amount, currency, type, proration), PRICE (recurring_interval, recurring_interval_count), CHARGE	Singular names. Timestamps in the Stripe account timezone.
Airbyte	subscriptions, invoices, charges (customer, invoice, status, amount, created, livemode)	API field names; created is Unix epoch seconds.

Copy-ready SQL

1. MRR by month (BigQuery, Data Pipeline)

Ending MRR per month in BigQuery syntax. Change: your_project.stripe to your dataset.

-- BigQuery (Stripe Data Pipeline)
with monthly as (
  select
    date_trunc(date(local_event_timestamp), month) as month,
    currency,
    sum(mrr_change) as mrr_change
  from `your_project.stripe.subscription_item_change_events_v2_beta`
  group by month, currency
)
select
  month,
  currency,
  sum(mrr_change) over (partition by currency order by month) / 100 as ending_mrr
from monthly
order by month desc, currency

Example output (made-up data):

month	currency	ending_mrr
2026-09-01	usd	18420.0
2026-08-01	usd	17150.0

2. Gross volume, fees and net per month (Snowflake, Data Pipeline)

What you charged, what Stripe kept, and what reached your balance. Change: nothing.

-- Snowflake (Stripe Data Pipeline)
select
  date_trunc('month', created) as month,
  currency,
  sum(amount) / 100 as gross,
  sum(fee) / 100 as fees,
  sum(net) / 100 as net
from stripe.balance_transactions
where type = 'charge'
group by 1, 2
order by 1 desc;

3. Revenue by Stripe account (Snowflake, Data Pipeline)

When several Stripe accounts share one warehouse. Change: nothing.

-- Snowflake (Stripe Data Pipeline)
select
  merchant_id,
  currency,
  date_trunc('month', created) as month,
  sum(net) / 100 as net
from stripe.balance_transactions
where type = 'charge'
  and created >= dateadd(month, -3, current_date)
group by 1, 2, 3
order by month desc, net desc;

4. Current MRR from Fivetran tables (Snowflake)

Fivetran has no MRR change log. Take each active subscription item’s latest paid subscription line and normalise it to a month. Change: stripe to your Fivetran schema.

-- Snowflake (Fivetran Stripe schema "stripe")
with latest_line as (
  select li.currency, li.amount, p.recurring_interval, p.recurring_interval_count
  from stripe.invoice_line_item li
  join stripe.invoice i on i.id = li.invoice_id
  join stripe.price p on p.id = li.price_id
  join stripe.subscription_history s
    on s.id = li.subscription_id and s._fivetran_active and s.status in ('active', 'past_due')
  where li.type = 'subscription'
    and not li.proration
    and i.status = 'paid'
  qualify row_number() over (partition by li.subscription_item_id order by i.created desc) = 1
)
select
  currency,
  round(sum(amount * case recurring_interval
    when 'month' then 1
    when 'year' then 1 / 12
    when 'week' then 52 / 12
    when 'day' then 365 / 12
  end / recurring_interval_count) / 100, 2) as mrr
from latest_line
group by currency
order by mrr desc;

Example output (made-up data):

CURRENCY	MRR
usd	18310.42

5. Active subscriptions by status from Fivetran tables (BigQuery)

A sanity check that your sync is complete. Change: your_project.stripe to your dataset.

-- BigQuery (Fivetran Stripe dataset "stripe")
select status, count(*) as subscriptions
from `your_project.stripe.subscription_history`
where _fivetran_active
group by status
order by subscriptions desc

6. Failed charges per day from Airbyte tables (BigQuery)

Airbyte keeps Unix timestamps; convert them first. Change: the dataset name.

-- BigQuery (Airbyte Stripe dataset "airbyte_stripe")
select
  date(timestamp_seconds(created)) as day,
  failure_code,
  count(*) as failed_charges
from `your_project.airbyte_stripe.charges`
where status = 'failed'
  and livemode
  and timestamp_seconds(created) >= timestamp_sub(current_timestamp(), interval 30 day)
group by day, failure_code
order by day desc, failed_charges desc

7. Revenue by signup cohort, joined to your users table (Snowflake)

The query only a warehouse can answer: how much each month’s signups have paid you since. Change: app.users and its columns to your own table; the join assumes you store the Stripe customer ID on the user.

-- Snowflake (Stripe Data Pipeline + your app tables)
select
  date_trunc('month', u.created_at) as signup_month,
  count(distinct u.id) as signups,
  count(distinct ch.customer_id) as paying_customers,
  sum(ch.amount - ch.amount_refunded) / 100 as revenue_to_date
from app.users u
left join stripe.charges ch
  on ch.customer_id = u.stripe_customer_id
  and ch.status = 'succeeded'
group by 1
order by 1 desc;

Example output (made-up data):

SIGNUP_MONTH	SIGNUPS	PAYING_CUSTOMERS	REVENUE_TO_DATE
2026-09-01	640	22	1078.00
2026-08-01	588	35	3430.00