# BigQuery data sharing (/docs/bigquery) Use this guide to build a **repeatable export from your own customer dataset**. It is a read-only reporting surface, not the transaction API. Start with the [field reference](/docs/bigquery-fields) for the full view inventory and [BigQuery changelog](/docs/bigquery-changelog) for changes to this surface. The [sales API guide](/docs/sales-data) describes a separate REST interface; its camel-case response fields are not BigQuery column names. ## Access and scope [#access-and-scope] Karma supplies a Google Cloud project, a customer dataset of the form `customer_location_`, and access for your approved Google identity. Use the project and dataset **for the environment you are integrating with**; development, staging, and production are separate. Your organization might not have every environment provisioned. Ask Karma for the exact identifiers and access rather than constructing them from another environment's IDs. The customer dataset contains **views**, not a second copy of the operational database. They read replicated tables and restrict rows to the customer's location tree through `location_members`. `location_members` lists locations in scope; its `id` is a location ID, not a purchase ID. Tables with no direct `location_id` are scoped by their parent purchase, tab, dispatch, item, or account. Do not query the upstream `analytics_eupublic` dataset: the customer views are the access boundary. Data may appear after the original transaction and is not an atomic cross-table snapshot. To see which views your dataset actually provides: ```sql SELECT table_name FROM `..INFORMATION_SCHEMA.VIEWS` ORDER BY table_name; ``` Replace both angle-bracketed identifiers before running these examples. BigQuery parameters cannot substitute for project, dataset, or table identifiers. Access to the customer dataset does not imply access to any upstream dataset. ## Model and joins [#model-and-joins] | Subject | Grain and join | | ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `purchases` | One payment/purchase row; `id` is its stable primary key **within one environment**. A tab can have several purchases. | | `purchase_line_items` | One recorded line; `purchase_id = purchases.id`. Child/add-on lines can have `parent_line_item_id` and their own `origin_item_id`. | | `purchase_payment_method` | Payment-method detail for a purchase; join on `purchase_id`. Use a `LEFT JOIN`: enrichment can arrive later or be absent. | | `purchase_refunds` | One refund attempt/result; `purchase_id` points back to the **original** purchase. A confirmed sale remains confirmed after a refund; technical zero-refunds may instead reference failed purchases. | | `purchase_refund_line_items`, `purchase_refund_vats` | Monetary refund allocations/VAT; join to `purchase_refunds.id` via `purchase_refund_id`. An amount-only allocation does not establish returned quantity; an explicit product-quantity refund may record it in `quantity_cents`, advertised in both checked development and staging customer views on October 12, 2026. | | `tab_members` → `tabs` | `purchases.paying_tab_member_id = tab_members.uuid`, then `tab_members.tab_id = tabs.uuid`. These are separate from the purchase/receipt identity. | | `kitchen_dispatches` → `kitchen_dispatch_items` | `kitchen_dispatch_items.kitchen_dispatch_id = kitchen_dispatches.uuid`. Kitchen activity describes orders/fulfilment, not proof of payment. | | `items`, `variants` | Catalogue records and their variants; a purchase line's `origin_item_id` can resolve to `items.id`. Prices and metadata on a current catalogue item are not a historical snapshot of the sale. | | `discounts` | Current location-owned discount and voucher definitions, scoped to customer locations. Not a record that a discount was actually applied; verify this view exists in your dataset before using it. | | Tab ledger (planned) | `tabs.ledger_account_id = ledger_accounts.id`; charges, discounts, and payments hang off the account. `item_charges.kitchen_dispatch_item_id` links a charge to its kitchen line; `purchases.discharge_id` links a payment to its discharge; `tab_purchase_discounts.purchase_intent_id = purchases.intent_id` links a discount to the payment it came with (`scope = 'payment'`: in that payment's `applied_total_discount_amount_cents`). Not yet in any customer view; see the [changelog](/docs/bigquery-changelog). | **Price-cluster customer views verified October 19 in dev/test:** `price_clusters` records the owning location, name and version; `price_cluster_entries` maps catalogue items to configured prices; `price_cluster_locations` maps assigned locations; and `price_cluster_applications` tracks active location/item combinations and previous overrides. Join child views on `cluster_id = price_clusters.id`. Views restrict cluster ownership and assigned/application target locations through the customer's `location_members`. They describe current configuration, **not** historical sale prices. The checked EH scopes currently contain no matching price-cluster rows. Production availability is separate; consult the [BigQuery changelog](/docs/bigquery-changelog) and verify your own dataset. Many relationships are one-to-many. Joining purchases to payment methods, lines, refunds, and tabs all at once and then summing amounts can multiply rows. Aggregate each child table **at its own grain** before joining the aggregates. Keep `LEFT JOIN`s where a missing related record should not erase the purchase. ## Finality, types, and money [#finality-types-and-money] `purchases.state` and `purchase_refunds.state` each use `pending`, `canceled`, `confirmed`, and `failed` (one **l** in `canceled`). Only `confirmed` is the finalized sale or refund state. There are no purchase states named `settled`, `captured`, `refunded`, or `partially_refunded`. PSP settlement, cash-register registration, and downstream reporting can occur later than payment confirmation. `purchase_line_items.type` is `product`, `tip`, or `amount`. `tip` is gratuity; `amount` is an unallocated amount payment against a tab, not a catalogue product. For an **article-level goods export**, select `product`, then exclude non-goods `item_type` values such as `service_fee`, `marketplace_fee`, and `marketplace_discount`. Include `surplus` products unless your business specifically excludes that category; `is_surplus` is not a canceled-sale flag. A bag or deposit sold as an ordinary catalogue product requires your own article classification. A payment made as an `amount` line can still pay for real goods on the tab: excluding the line from an article export must not silently remove that payment from reconciliation. Allocate tab goods deliberately and avoid exporting them once per partial payment. Unlike `type`, **`purchase_line_items.item_type` is extensible text, not a closed database enum**. It is also separate from the current catalogue's inventory `itemType`. These are the current reporting classifications, not a promise that no additional value can be introduced: | Sales-line `item_type` | Goods export | | ------------------------------ | ---------------------------------------------------------------------------- | | `regular` or historical `NULL` | Include, subject to article classification. | | `surplus` | Include unless your business explicitly excludes surplus goods. | | `service_fee` | Exclude; service surcharge, not goods. | | `marketplace_fee` | Exclude; marketplace fee, not goods. | | `marketplace_discount` | Exclude; separate financial discount line, not goods. | | Unrecognised value | Alert and hold the affected export for classification; retain it for replay. | The documented non-goods deny-list is appropriate for **recognised** values. For an irreversible downstream product upload, check for unknown values first; do not silently accept a new fee as goods or discard a real product. The [BigQuery changelog](/docs/bigquery-changelog) records observed changes; no separate advance-notification SLA is established here. Amounts ending in `_cents` use **integer minor currency units**, except `quantity_cents`, which measures hundredths of one unit (`150` means 1.5); do not apply floating-point currency arithmetic. `purchases.currency_code` identifies the currency. `vat_rate_basis_points` is in basis points (`1200` means 12%). Prices include VAT. `quantity` is a whole-unit compatibility field. `final_amount_cents` is a **per-unit, post-discount** price; `vat_amount_cents` is per-unit VAT. On current finalized lines, `total_amount_cents` is the **line total before the line's discount** and `applied_total_discount_amount_cents` is that discount: the monetary line total is `total_amount_cents - applied_total_discount_amount_cents`. On tabs with a ledger account (`tabs.ledger_account_id` set) the discount is every discount on the line, given at payment or before it (at the till or by an integration's price lock), and `pre_discount_vat_amount_cents` is the VAT of `total_amount_cents`. On other tabs it is the payment-time discount (vouchers and promotions applied when paying); a discount given on the item before payment is already in `final_amount_cents` and `total_amount_cents`, so neither column shows it. Never multiply `total_amount_cents` by quantity or subtract that discount twice. Legacy/null totals, fractional quantities, modifiers, tips, and amount payments need their own reconciliation path; `purchases.amount_cents` is nullable (including on ordinary POS purchases), so it is not a universal sale-total field. A goods-line subtotal is not necessarily the payment total. `purchase_vats` provides per-rate purchase VAT totals. `applied_modifications` contains inline pricing/modification detail; it is not a separate sellable line. On tabs outside the tab ledger it can also list payment-time discounts and vouchers as negative adjustments; lines of ledger tabs (`tabs.ledger_account_id` set) list none, so take a line's discount from `applied_total_discount_amount_cents`. A separately priced catalogue add-on can instead be a child `purchase_line_items` row. Count each recorded goods line once, but do not independently add inline adjustments again. `article_no` is **not** a guaranteed partner SKU. Resolve `origin_item_id` to `items.id` and agree on a customer-specific key in `items.metadata`; JSON keys are case-sensitive. Current catalogue data can change after a purchase. For purchase-level VAT reporting, use `purchase_vats.vat_amount_cents` grouped by `vat_rate_basis_points` for a confirmed purchase. The per-unit `purchase_line_items.vat_amount_cents` and whole-line `total_vat_amount_cents` have different grains; reconstructing VAT by rounding each line can differ from the purchase-level VAT total. Reconcile line-level detail to the purchase totals and keep refund VAT in `purchase_refund_vats` separate from the original purchase VAT. **Historical discounted VAT:** September 22 staging purchase `1500000125` records 185.00 SEK gross, a 60.00 SEK discount, 19.80 SEK summed line VAT and 13.39 SEK purchase VAT. Its historical line VAT is pre-discount. The checked discounted examples from September 24 onward and the current discount writers use post-discount line VAT; this observation does not establish an exact rollout timestamp or backfill historical rows. Newer `pre_discount_vat_amount_cents` records pre-discount VAT separately and is nullable historically. Purchase `1500000130` records 48.12 SEK summed line VAT versus 48.11 SEK purchase VAT, illustrating rounding differences even without a discount. Use the purchase-level record for purchase VAT; do not rewrite stored VAT from today's catalogue settings. The line's `applied_total_discount_amount_cents` is the recorded discount: every discount on ledger tabs, the payment-time discount elsewhere. Current `discounts` definitions describe configuration (including voucher and gift-card types), not which coupon or calculation produced a particular sale's discount; do not join a current definition to a historical purchase and recompute its price. Available line metadata can describe applied modifications but the current customer views do not promise a complete discount-calculation trace. The planned tab-ledger views name each discount on a tab line with its source, including discounts given before payment; see the [changelog](/docs/bigquery-changelog) for availability. For surplus article classification, `purchase_line_items.is_surplus` is the recorded sale-line flag; `item_type` can additionally describe the catalogue category. Do not substitute current `items` catalogue values for historical sale-line flags. Refund amounts are positive magnitudes in the source; negate **confirmed** refunds only when creating a signed ledger. A partial financial refund does not prove that any products were returned. An explicit refunded product quantity can be recorded in `purchase_refund_line_items.quantity_cents` (hundredths of a unit), but amount-only and historical allocations have `NULL` there. As of October 8, 2026, it is advertised as `INT64` in the checked **development and staging** customer views; no production customer view was available to verify. Check your own view before querying it, and do not infer returned units from refund amounts. `purchase_applied_fees` are commercial/payment fees, not products; do not add them to an article export. For split/partial tab payments, a tab can have several purchases. Export each payment with its own `purchases.id` and each recorded product allocation once; do not repeat the tab's full goods on every purchase. The current amount-payment path allocates goods proportionally into `product` lines, including zero-priced add-ons. Older amount placeholders may be allocated later when the tab closes. An amount-only purchase needs reconciliation before its underlying goods can be exported; confirmation alone does not promise that every historical payment already has product allocations. A verified staging example, read on October 12, is tab `0f7a9115-11ca-4925-8eb6-fbeec596ca56`: | Purchase | Tender | Bun | Latte | Milk | Paid total | | ------------ | ---------------- | ---------------------- | ---------------- | --------------- | ---------- | | `1500000208` | Prepaid code `2` | 0.50 units / 20.50 SEK | 0.50 / 27.50 SEK | 0.50 / 0.00 SEK | 48.00 SEK | | `1500000209` | Adyen card | 0.50 units / 20.50 SEK | 0.50 / 27.50 SEK | 0.50 / 0.00 SEK | 48.00 SEK | Each allocation has `quantity_cents = 50`. Across the two purchases, export one bun, one latte and one milk, for 96.00 SEK. Partial amount-allocation quantities can be derived proportions; they are not evidence of which physical items a particular payer consumed. Split-payment add-ons can be flat product lines with `parent_line_item_id = NULL`. In the example, milk's `kitchen_dispatch_item_id` still resolves to a kitchen row whose `parent_kitchen_dispatch_item_id` identifies the latte. To recover a purchase-line parent, resolve that kitchen parent **within the same purchase**. Do not assume a unique purchase-line parent if multiple allocations share one kitchen ID; flag ambiguous matches rather than multiplying rows. A zero-price add-on can still affect food cost and must not be dropped because its price is zero. `purchase_payment_method` enrichment can be absent: nine confirmed prepaid purchases in the checked staging data had no method row. Split tender in the example is separate purchases on one tab, not two tender rows on one purchase. Use a `LEFT JOIN` and account for method-row cardinality before joining lines. `prepaid_type_code` snapshots the configured **string** code, `payment_method_id` is the Karma prepaid-type UUID, and `brand` is a label. Join configuration by `payment_method_id = prepaid_types.id`; codes are not globally unique across locations, and historical snapshots can differ from edited configuration. Agree a code-to-account mapping with the customer's finance team; paying with prepaid does not itself exclude the goods sale or create a second sale. No sale-line ordinal guarantees business order: sort by `created_at`, then `id` for stable presentation, not a contractual preparation sequence. **Third-party article mapping:** when `origin_item_id` is missing, a populated `kitchen_dispatch_item_id` can resolve through `kitchen_dispatch_items.item_id` to the catalogue. In checked staging purchase `1500000108`, that path recovers partner articles `000026`, `000050` and `000060`, even though all three purchase lines lack `origin_item_id`. The recorded monetary allocation remains 153.00 SEK on the latte and zero on the bagel/focaccia. Recovering article identity does not establish a corrected price split or historical backfill. Keep catalogue joins optional, alert on unresolved article mappings, and do not invent prices from today's catalogue or discard zero-price goods. The catalogue remains mutable. An order/tab `tab_type` or kitchen `is_takeaway` flag is not by itself a verified tax classification. `over_the_counter` is an order mode, not proof of eat-in. The checked customer `locations` view has no timezone column; obtain an explicit shop-to-IANA-timezone mapping before adding outlets outside the assumed timezone. **Internal tenders and training mode:** a purchase with no external PSP round-trip can still be a legitimate confirmed sale using prepaid, cash or gift-card settlement. Do not exclude it because card details or external provider references are absent. The checked customer schema has no dedicated terminal training-mode field providing a reliable sales exclusion. Staging fixtures do not establish production exclusion rules: for example, amount-only purchase `1500000137` is explicitly a Swish simulator fixture. Tipping is controlled by the location's `tip` setting; disabling future tip collection does not remove historical tip lines. ## Partner member identity and refund limitations [#partner-member-identity-and-refund-limitations] An integration can store an attached partner member number in `tabs.metadata`; the JSON key is partner-specific, not a universal schema field. Confirm the actual key with Karma and replace `` below with its JSON path. Reach the tab with `LEFT JOIN`s through `purchases.paying_tab_member_id = tab_members.uuid` and `tab_members.tab_id = tabs.uuid`; confirmed purchases can have no tab member. Keep member numbers as strings. ```sql SELECT p.id AS purchase_id, JSON_VALUE(t.metadata, '') AS tab_partner_member_number FROM `..purchases` AS p LEFT JOIN `..tab_members` AS tm ON tm.uuid = p.paying_tab_member_id LEFT JOIN `..tabs` AS t ON t.uuid = tm.tab_id WHERE p.state = 'confirmed'; ``` This is a **mutable tab attachment**, not an immutable member identity for every split-bill payer. Historical purchase metadata can contain provider-specific member IDs; do not assume those keys persist in new purchases. In the checked partner payment flow, the internal quote/loyalty payload is intentionally omitted from new purchase metadata, while generic payment metadata can remain. Newer verified per-payment finalize context is stored in the internal integration log, which is **not among the checked customer views**. A guaranteed per-payment member snapshot needs a separate export contract; do not substitute Karma user IDs or card-matching IDs for a partner member number. The checked refund API requires an original purchase and refunds through its tender. The customer refund view has no independent **actual refund payout method** column. Joining the original purchase retrieves the original tender; it does not prove a card sale was refunded in cash. Cross-tender refunds need an explicit product and reporting contract. A missing paper/fiscal receipt is different from an absent original sale record. A refund can reference its original purchase with nullable fiscal fields, but the current refund contract requires an original purchase ID/intent. A return with no original Karma sale is not supplied as an unlinked refund or negative purchase under that contract. Do not fabricate an original ID. Monetary line/VAT allocations alone do not guarantee physical goods were returned; explicit refunded quantities can remain unknown. ## IDs and time [#ids-and-time] Use `purchases.id` as the idempotency key for a **per-purchase** export. It remains the same when that row is updated; `purchase_refunds.purchase_id` ties refund records to it, while `purchase_refunds.id` identifies the individual refund. Namespace keys by environment if you combine environments. A recreated database/import is outside the normal purchase lifecycle. `purchases.cr_receipt_id` is a separate, nullable fiscal cash-register reference and may be populated later. It is not a universal sale key or the human-readable running receipt number. Nor is a tab UUID a per-purchase receipt number. `purchases.purchased_at` is the payment-confirmation time. The PostgreSQL source stores it as UTC in a timestamp-without-time-zone column; the BigQuery view exposes a `TIMESTAMP`. Serialize the **instant** with an explicit UTC offset, such as `Z`. Compute an outlet business date in the outlet's configured IANA timezone, not from the server timezone or a timezone-free display string. `purchase_refunds.cr_registered_at` is nullable and records later fiscal **refund** registration; it is not the original sale time. `created_at`, `updated_at`, and event times serve different purposes; do not replace a sale-time filter with a change-time filter without changing the report's meaning. ```sql SELECT id AS purchase_id, FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%E3SZ', purchased_at, 'UTC') AS sold_at_utc, DATE(purchased_at, 'Europe/Stockholm') AS local_business_date FROM `..purchases` WHERE state = 'confirmed'; ``` `Europe/Stockholm` is an **example**, not a default for every location. Supply the outlet's configured timezone to compute its local reporting day. ## Example: extract goods and refunds [#example-extract-goods-and-refunds] This query preserves the line grain and reports a post-discount amount for rows that have a modern `total_amount_cents`. It deliberately **does not** claim that goods lines sum to a payment when tips, amount payments, adjustments, or missing historical totals are present. Before an irreversible upload, run this check over the **same sale window**. An output row means the item's classification needs review; retain the affected purchase for replay before proceeding. The non-goods deny-list in the extraction below does not itself quarantine unknown values. ```sql SELECT p.id AS purchase_id, pli.id AS line_id, pli.item_type FROM `..purchases` AS p JOIN `..purchase_line_items` AS pli ON pli.purchase_id = p.id WHERE p.state = 'confirmed' AND pli.type = 'product' AND COALESCE(pli.item_type, 'regular') NOT IN ('regular', 'surplus', 'service_fee', 'marketplace_fee', 'marketplace_discount') AND p.purchased_at >= TIMESTAMP('2026-09-01T00:00:00Z') AND p.purchased_at < TIMESTAMP('2026-10-01T00:00:00Z'); ``` ```sql SELECT p.id AS purchase_id, p.location_id, p.currency_code, p.purchased_at, pli.id AS line_id, pli.parent_line_item_id, pli.origin_item_id, pli.quantity_cents, pli.vat_rate_basis_points, pli.total_amount_cents - COALESCE(pli.applied_total_discount_amount_cents, 0) AS paid_line_cents FROM `..purchases` AS p JOIN `..purchase_line_items` AS pli ON pli.purchase_id = p.id WHERE p.state = 'confirmed' AND pli.type = 'product' AND COALESCE(pli.item_type, 'regular') NOT IN ('service_fee', 'marketplace_fee', 'marketplace_discount') AND pli.total_amount_cents IS NOT NULL AND p.purchased_at >= TIMESTAMP('2026-09-01T00:00:00Z') AND p.purchased_at < TIMESTAMP('2026-10-01T00:00:00Z'); ``` For a financial refund export, start from `purchase_refunds` filtered on `state = 'confirmed'`, join `purchases` on `purchase_id`, and key each refund by its own `id`. Prefer `psp_refunded_amount_cents` for the recorded provider result and fall back to `amount_cents` only where the former is absent; do not treat two missing amounts as zero. A confirmed *technical* zero-refund can reference a failed original purchase: check the joined purchase state and recorded amount before treating it as a negative sale. The fiscal receipt can describe a later processing step. Monetary allocations in `purchase_refund_line_items` alone are not returned-quantity evidence; where available, `quantity_cents` records explicitly selected product quantities when present. ## Keeping an export correct [#keeping-an-export-correct] 1. Backfill confirmed purchases over your desired **sale-time** window. Store by environment and `purchases.id`; store refunds independently by environment and `purchase_refunds.id`. 2. Re-read a recent window for late confirmation, payment-method enrichment, fees, fiscal details, refunds and legacy tab allocation. Thirty minutes is not an immutability guarantee. Use an overlap and upserts; do not assume cross-table writes or BigQuery replication arrive together. A source `updated_at` is useful for changes to **that table**, but does not automatically advance when a child/refund row changes. Resolve allocation exceptions before sending data to a destination that cannot correct a sale later. 3. Compare purchase totals, goods lines, tips, amount-payment lines, confirmed refunds, and fees as **different categories**, not one sum. Check expected currencies and outlet timezones. Do not count pending or failed refunds. 4. Check view metadata as well as sample data before changing a connector schema. A newly replicated column can be queryable before a customer view's stored schema / `INFORMATION_SCHEMA.COLUMNS` is refreshed. Do not infer a type from one `SELECT * LIMIT 1` row, especially when it is `NULL`. 5. Select the columns you need rather than relying on `SELECT *`. Views can contain operational `JSON` or `STRUCT` fields that a connector cannot ingest. The upstream `printer_jobs.rendered_content` is raw `BYTES`, but it was **removed from the checked development/staging customer views on October 8, 2026**; do not depend on the raw upstream payload being customer-visible. Confirm your own view's metadata before using a connector. 6. If your incremental ingestion only upserts rows it sees, it cannot by itself detect a row later physically removed from a merge-mode source. Reconcile keys against periodic full reads when deletion or void detection matters; do not assume a delete event without a separately verified signal. ```sql SELECT table_name, column_name, data_type, is_nullable FROM `..INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'purchases' ORDER BY ordinal_position; ``` The [field reference](/docs/bigquery-fields) is a dated example, not a substitute for querying your own view schema. Report a missing column, stale view schema, or type mismatch with the environment, dataset, view, column, expected/observed types, and a minimal read-only query. Changes and known limitations are tracked in the [BigQuery changelog](/docs/bigquery-changelog).