Weld
Start free
Klarna model

Payout reconciliation

Every Klarna payout, Klarna's own breakdown of it, and whether the synced transactions add up to that breakdown.

See the SQLAll Klarna models
Grain
klarna_account x merchant_id x payout (payment_reference)
Built from
  1. 2
    Raw tables
  2. 2
    Staging
  3. 1
    Core
  4. 1
    Analytics
Reads from Klarna
payouttransaction
Ships as
Weld and dbt SQL, open source on GitHub
core.klarna.payout_reconciliation
1-- core.klarna.payout_reconciliation
2-- Every Klarna payout, Klarna's own breakdown of it, and whether the synced
3-- transactions add up to that breakdown.
4-- Grain: klarna_account x merchant_id x payout (payment_reference).
5-- Currency: the payout's own. Never sum across currency_code.
6-- Depends on: staging.klarna.payout, staging.klarna.transaction
7--
8-- WHAT THIS IS FOR. The payout is the number on the bank statement. Finance
9-- matches settlement_amount to the bank line, then needs to know what it was
10-- made of: sales, minus returns, minus Klarna's fees and the tax on them, minus
11-- anything held back. Klarna computes that breakdown itself (the totals_ fields),
12-- and this model sets it next to the same figures rebuilt from the transaction
13-- ledger, type by type.
14--
15-- WHY THE TIE-OUT COMPARES ABSOLUTE VALUES. Klarna documents a type for each
16-- transaction but no sign convention for amount, and the totals are documented
17-- as plain amounts. Comparing ABS(sum per type) with ABS(total) is true whichever
18-- convention an account returns, so a difference here always means missing or
19-- extra rows, never a sign misunderstanding.
20--
21-- Only the types with an unambiguous counterpart in the totals are tied out:
22-- SALE, RETURN, FEE, FEE_REFUND, REVERSAL, CHARGE, CREDIT, HOLDBACK and RELEASE.
23-- TAX, COMMISSION and CORRECTION are shown side by side but not tested, because
24-- Klarna splits them across several totals (tax vs tax refund, commission vs
25-- commission reversal, fee correction) with no documented type mapping.
26--
27-- Transactions are aggregated to the payout BEFORE the join, so a payout with
28-- 4,000 transactions still contributes one row and no total is multiplied.
29
30WITH txn AS (
31    SELECT
32        klarna_account,
33        merchant_id,
34        payment_reference,
35        COUNT(*)                                                         AS transactions,
36        COUNT(DISTINCT IF(transaction_type = 'SALE', order_id, NULL))    AS orders_sold,
37        ABS(SUM(IF(transaction_type = 'SALE',       amount, 0)))        AS txn_sale_amount,
38        ABS(SUM(IF(transaction_type = 'RETURN',     amount, 0)))        AS txn_return_amount,
39        ABS(SUM(IF(transaction_type = 'FEE',        amount, 0)))        AS txn_fee_amount,
40        ABS(SUM(IF(transaction_type = 'FEE_REFUND', amount, 0)))        AS txn_fee_refund_amount,

This model and the 3 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

WHAT THIS IS FOR. The payout is the number on the bank statement. Finance matches settlement_amount to the bank line, then needs to know what it was made of: sales, minus returns, minus Klarna's fees and the tax on them, minus anything held back. Klarna computes that breakdown itself (the totals_ fields), and this model sets it next to the same figures rebuilt from the transaction ledger, type by type. WHY THE TIE-OUT COMPARES ABSOLUTE VALUES. Klarna documents a type for each transaction but no sign convention for amount, and the totals are documented as plain amounts. Comparing ABS(sum per type) with ABS(total) is true whichever convention an account returns, so a difference here always means missing or extra rows, never a sign misunderstanding. Only the types with an unambiguous counterpart in the totals are tied out: SALE, RETURN, FEE, FEE_REFUND, REVERSAL, CHARGE, CREDIT, HOLDBACK and RELEASE. TAX, COMMISSION and CORRECTION are shown side by side but not tested, because Klarna splits them across several totals (tax vs tax refund, commission vs commission reversal, fee correction) with no documented type mapping. Transactions are aggregated to the payout BEFORE the join, so a payout with 4,000 transactions still contributes one row and no total is multiplied.

Raw tables it reads

  • raw.klarna.payout
  • raw.klarna.transaction

More Klarna models

Put Klarna in front
of your agents.

Connect Klarna, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us