BigQuery data sharing

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 for the full view inventory and BigQuery changelog for changes to this surface. The sales API guide describes a separate REST interface; its camel-case response fields are not BigQuery column names.

Access and scope

Karma supplies a Google Cloud project, a customer dataset of the form customer_location_<ROOT_LOCATION_ID>, 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:

SELECT table_name
FROM `<PROJECT_ID>.<CUSTOMER_DATASET>.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

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 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 JOINs where a missing related record should not erase the purchase.

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:

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 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 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:

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

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 <MEMBER_JSON_PATH> below with its JSON path. Reach the tab with LEFT JOINs 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.

SELECT p.id AS purchase_id,
  JSON_VALUE(t.metadata, '<MEMBER_JSON_PATH>') AS tab_partner_member_number
FROM `<PROJECT_ID>.<CUSTOMER_DATASET>.purchases` AS p
LEFT JOIN `<PROJECT_ID>.<CUSTOMER_DATASET>.tab_members` AS tm
  ON tm.uuid = p.paying_tab_member_id
LEFT JOIN `<PROJECT_ID>.<CUSTOMER_DATASET>.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

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.

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 `<PROJECT_ID>.<CUSTOMER_DATASET>.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

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.

SELECT p.id AS purchase_id, pli.id AS line_id, pli.item_type
FROM `<PROJECT_ID>.<CUSTOMER_DATASET>.purchases` AS p
JOIN `<PROJECT_ID>.<CUSTOMER_DATASET>.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');

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 `<PROJECT_ID>.<CUSTOMER_DATASET>.purchases` AS p
JOIN `<PROJECT_ID>.<CUSTOMER_DATASET>.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

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.

SELECT table_name, column_name, data_type, is_nullable
FROM `<PROJECT_ID>.<CUSTOMER_DATASET>.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'purchases'
ORDER BY ordinal_position;

The field reference 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.