Query Tax data

Stripe Tax data includes various components that work together to provide a comprehensive view of your tax liability through automated calculations with Payment Links, Checkout, Subscriptions, Invoices, Custom integrations and as a platform. All tax-specific data is available in the Tax section of the schema, and the primary table is tax_transactions.

To explore tax data further, you can use additional tables that represent the components of a tax_transaction, such as tax_transaction_line_items, tax_transaction_shipping_costs, and tax_transaction_jurisdiction_details.

This diagram represents all components created during an automated tax calculation and how it relates to the original object that triggered calculation. Amounts in the examples below appear in minor currency units (for example, cents instead of dollars).

Tax transactions

Each row in the tax_transactions table represents a single tax_transaction object. Tax transactions record the assumed or reduced tax liability for a transaction or reversal. You can report on every tax transaction created with automated tax calculations.

This table is our recommended starting point for creating reports on your tax liability. You need to join on the other tax tables below for your tax liability amounts, but this table joins your data to other products like invoices and checkout sessions. Joining to non-tax tables by source_id and source_type provides the object that triggers the creation of the tax transaction.

Tax transactions have a one-to-one relationship with their original source object. The following example retrieves a list of reportable tax transactions with their original source of invoices, which you can then expand on with query billing data.

select
  tx.id as tax_transaction_id,
  tx.posted_at as tax_transaction_posted_at,
  inv.id as invoice_id,
  inv.total as invoice_total
from
  tax_transactions tx
  inner join invoices inv on inv.id = tx.source_id
limit
  5

Tax transactions metadata

Tax transactions might have metadata set with your custom integrations. Each row in tax_transactions_metadata returns one key-value pair.

Tax transaction items

Tax line items and tax shipping costs make up the total amounts and tax liability for tax transactions. Tax line items and tax shipping costs are in their own tables, but they both contribute to amounts and total tax liability.

Tax line items

Tax line items represent line items that contribute to the sale of goods for the transaction. Each row in tax_transaction_line_items represents a single tax line item associated to the tax_transaction.

Tax shipping costs

Tax shipping costs represent shipping items that contribute to the shipment of goods for the transaction. Each row in tax_transaction_shipping_costs represents a single shipping cost associated to the tax_transaction.

The following example retrieves a list of all amounts and tax liability for a given tax_transaction.

with tax_amounts as (
  select
    li.tax_transaction_id,
    li.id,
    li.amount,
    li.amount_tax,
    li.tax_behavior,
    li.currency,
    'line_item' as item_type
  from
    tax_transaction_line_items li
  union all
  select
    sc.tax_transaction_id,
    sc.id,
    sc.amount,
    sc.amount_tax,
    sc.tax_behavior,
    sc.currency,
    'shipping_cost' as item_type
  from
    tax_transaction_shipping_costs sc
)
select
  id,
  amount,
  amount_tax,
  tax_behavior,
  currency,
  item_type
from
  tax_amounts
where
  tax_transaction_id = 'tax_d2e5470dC63u'

Tax transaction line items metadata

Tax transaction line items might have metadata set with your custom integrations. Each row in tax_transaction_line_items_metadata returns one key-value pair.

Tax transaction jurisdiction details

Each row in the tax_transaction_jurisdiction_details table represents a jurisdiction which makes up the liability for the tax transaction item (tax_transaction_line_item or tax_transaction_shipping_cost).

Understanding jurisdiction details

This table provides more in-depth information based on each jurisdiction. A single tax transaction item can have multiple jurisdictions which make up the amounts for a tax transaction item.

Summing amount_taxable or amount_non_taxable across all jurisdiction details doesn’t always equal the tax transaction item’s amount.

amount_taxable: This is the amount up to the tax transaction item’s amount that is taxable. Multiple jurisdictions can have the same taxable amount.

amount_non_taxable: This is the amount up to the tax transaction item amount that is non-taxable. Multiple jurisdictions can have the same non-taxable amount.

amount_tax: This is part of the tax transaction item amount_tax. Summing this across all jurisdiction details equals the transaction item’s amount_tax.

currency: This is the presentment currency that defines amount_taxable, amount_non_taxable and amount_tax. Summing across different currencies yields unexpected results.

filing_currency: This is the filing currency used by the applicable tax authority which defines filing_amount_taxable, filing_amount_non_taxable and filing_amount_tax. Summing across different filing currencies yields unexpected results.

The following example retrieves all jurisdiction details for a given tax transaction item.

select
  jd.amount_taxable,
  jd.amount_non_taxable,
  jd.amount_tax,
  jd.taxability_reason,
  jd.jurisdiction_level,
  jd.jurisdiction_name,
  concat(jd.jurisdiction_country, '-', jd.jurisdiction_state) as jurisdiction_location
from
  tax_transaction_jurisdiction_details jd
where
  li.id = 'tax_li_52d37cdd6f7'

Example queries

For additional examples, please reference the Tax section of query template library in Sigma sidebar.

Tax liability by month

This example summarises the tax liability imposed on line items and shipping costs grouped by month and currency.

with tax_amounts as (
  select
    li.tax_transaction_id,
    li.amount,
    li.amount_tax,
    li.tax_behavior,
    li.currency
  from
    tax_transaction_line_items li
  union all
  select
    sc.tax_transaction_id,
    sc.amount,
    sc.amount_tax,
    sc.tax_behavior,
    sc.currency
  from
    tax_transaction_shipping_costs sc
),
tax_liability as (
  select
    date_format(date_trunc('month', posted_at), '%Y-%m-%d') as month,
    currency as presentment_currency,
    sum(
      (

Tax itemised jurisdiction details

This template itemises tax transaction jurisdiction details for the previous month to produce a customisable version of the Tax itemised export. Review the comments throughout the template to learn more about how it is customisable for your needs.

with tax_amounts as (
  select
    li.id,
    li.source_line_item_id,
    li.amount,
    li.amount_tax,
    li.tax_behavior,
    li.tax_code,
    li.currency,
    li.quantity_decimal
  from
    tax_transaction_line_items li
  union all
  select
    sc.id,
    -- Shipping costs do not have source line item IDs
    '' as source_line_item_id,
    sc.amount,
    sc.amount_tax,
    sc.tax_behavior,
    sc.tax_code,
    sc.currency,
    -- Shipping costs do not have a quantity
    '' as quantity_decimal
  from