Query tax data

Use Sigma or Data Pipeline to retrieve tax information.

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, tax_transaction_jurisdiction_details, and tax_codes.

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.

Tax codes

Tax codes describe the product category that Stripe Tax used to calculate tax for a line item or shipping cost. Each row in tax_codes represents a single Tax Code object, including its name and description. This table includes all generally available tax codes.

Join tax_codes.id to tax_transaction_line_items.tax_code or tax_transaction_shipping_costs.tax_code to include tax code details in your reports.

Note

These tables provide high level information based on each item. A single tax transaction can have multiple line items and one shipping cost which make up the amounts for a tax transaction.

amount: This is the gross amount of the line item. When tax_behavior is inclusive, it includes tax liability amounts. When tax_behavior is exclusive (the default), it excludes tax liability amounts.

amount_tax: This is the amount of tax liability for the line item.

currency: This is the integration currency that defines amount and amount_tax. Summing across different currencies yields unexpected results.

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 integration 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
 jd.id = 'tax_li_52d37cdd6f7'

Example queries

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

Tax liability by month

This example summarizes 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 integration_currency,
 sum(
 (

Tax itemized jurisdiction details

This template itemizes tax transaction jurisdiction details for the previous month to produce a customizable version of the Tax itemized export. Review the comments throughout the template to learn more about how it’s customizable 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