Payroll does not match the accounting system

Payroll calculates one line per person for every kind of pay: base salary, overtime, a bonus, each deduction, each tax. Thousands of lines a period in a mid-size company.

The accounting system never sees them. The lines are summed by account and posted as a single journal entry. Correct totals, nothing underneath.

Every departmental question after that point is answered by hand, and the working is thrown away each month.

All four share a shape. Nothing errors. The report runs, looks finished, and is wrong. That is the expensive kind, because it surfaces only when somebody who knows the business says that cannot be right.

None of these is poor. A tie needs both sides in one place, and one side is always somewhere else.

One row per person, per pay component, per period, landed on the customer's own cloud. The ledger lands beside it. The totals are compared on a schedule and the result is kept, so last quarter's check can still be produced.

Grain, the level of detail one row represents, is the whole decision. A row at person and pay component adds up to any question: by department, by pay type, by month, by legal entity. A row that arrives summarised cannot be taken apart again.

Two grains in one pass. Grouping sets tell the database to aggregate at more than one level in a single query, so the account subtotal and the cost center breakdown come back together rather than as two reports somebody has to line up.

-- one query, three levels: grand total, account, cost center within account
WITH lines AS (
  SELECT c.ledger_account,
         COALESCE(cc.cost_center, 'none') AS cost_center,
         f.amount
  FROM fct_payroll_result_line f
  JOIN      dim_date          d  ON f.accounting_date_key = d.date_key
  JOIN      dim_pay_component c  ON f.pay_component_key   = c.pay_component_key
  LEFT JOIN dim_cost_center   cc ON f.cost_center_key     = cc.cost_center_key
  WHERE d.fiscal_period = '2026-05'
    AND c.affects_ledger
),
payroll AS (
  SELECT ledger_account, cost_center, SUM(amount) AS payroll_amount
  FROM lines
  GROUP BY GROUPING SETS ((), (ledger_account), (ledger_account, cost_center))
),
ledger AS (
  SELECT a.ledger_account, SUM(j.amount) AS ledger_amount
  FROM fct_gl_journal_line j
  JOIN dim_date    d ON j.accounting_date_key = d.date_key
  JOIN dim_account a ON j.account_key         = a.account_key
  WHERE d.fiscal_period = '2026-05'
    AND j.source_system = 'Payroll'
  GROUP BY GROUPING SETS ((), (a.ledger_account))
)
SELECT COALESCE(p.ledger_account, l.ledger_account, 'total') AS ledger_account,
       COALESCE(p.cost_center, 'all')                        AS cost_center,
       p.payroll_amount,
       CASE WHEN p.cost_center IS NULL THEN l.ledger_amount END AS ledger_amount,
       CASE WHEN p.cost_center IS NULL
            THEN COALESCE(p.payroll_amount, 0) - COALESCE(l.ledger_amount, 0)
       END                                                   AS difference
FROM payroll p
FULL OUTER JOIN ledger l
  ON p.ledger_account IS NOT DISTINCT FROM l.ledger_account
 AND p.cost_center IS NULL
ORDER BY CASE WHEN p.ledger_account IS NULL THEN 0 ELSE 1 END,
         1,
         CASE WHEN p.cost_center IS NULL THEN 0 ELSE 1 END,
         2

Three things carry the weight. Grouping sets produce a null cost center on subtotal rows, so unmapped lines are coalesced to none before grouping: none is a real gap in the data, null is a subtotal, and conflating them hides the gap inside its own total. The full outer join keeps an account that exists on one side only, which is the failure an inner join silently swallows. And both sides filter on the accounting date, which removes the two-dates cause above.

Sample output, May 2026. The account rows open and close; 6000 is shown open:

Every level rolls up. Each account's cost center rows add to its own subtotal, the three account rows add to the grand total, and the same four departments appear under every account, so one department's full cost is three clicks, not three reports.

The empty cells are the point. The ledger holds no cost center, so those columns can only ever populate at the account level. The 8,420.00 on the none row belongs to a department nobody can name, and the total still ties. Sample values are illustrative, never client data.