Our finance team has been using Spendesk for about a year, primarily for its spend controls and approval workflows. However, the built-in reports always felt too generic for our leadership's needs, particularly around departmental budget pacing and vendor spend categorization. The CFO wanted a single view that merged card transactions, invoices, and reimbursements, segmented by department and plotted against our quarterly allocated budgets.
While the Spendesk UI exports to CSV, the manual merging was a weekly chore. I discovered their "Data Export" feature provides direct, read-only SQL access to a replicated schema. After some exploration, I constructed a query that has now become our official Monday morning report.
The key was joining the `payment_requests`, `transactions`, and `virtual_cards` tables with our internal mapping tables (hosted externally, hence the CTE). The most useful part was aggregating by the custom field we use for budget codes.
```sql
WITH budget_mapping AS (
-- This is a static mapping we maintain between Spendesk categories and our internal GL codes
SELECT 'spendesk_category_1' AS spendesk_category, 'GL_6200' AS gl_code, 'Engineering' AS department
UNION ALL
SELECT 'spendesk_category_2', 'GL_6300', 'Marketing'
)
SELECT
DATE_TRUNC('month', pr.approved_at) AS reporting_month,
bm.department,
bm.gl_code,
COUNT(DISTINCT pr.id) AS number_of_transactions,
SUM(pr.amount_in_cents) / 100 AS total_amount_eur,
AVG(pr.amount_in_cents) / 100 AS avg_transaction_eur
FROM payment_requests pr
JOIN transactions t ON pr.transaction_id = t.id
LEFT JOIN virtual_cards vc ON t.virtual_card_id = vc.id
LEFT JOIN budget_mapping bm ON pr.custom_field_value = bm.spendesk_category
WHERE pr.status = 'paid'
AND pr.approved_at >= DATEADD('month', -3, CURRENT_DATE())
GROUP BY 1, 2, 3
HAVING total_amount_eur > 0
ORDER BY reporting_month DESC, total_amount_eur DESC;
```
The output gives us a clear, timely view of actual cash outflow against our planned budgets, something the static reports couldn't provide. A few notes for anyone attempting similar:
* Spendesk's schema uses cents/centime integers for currency. Remember to divide by 100.
* The `custom_field_value` is a free-text field; using it reliably requires strict internal naming conventions.
* Joins on `virtual_cards` are necessary to pull in metadata like card owner, which we use for fallback categorization.
This approach has reduced manual reporting work by about 3-4 hours per week. The next step is to pipe this query result via a scheduled job into our Grafana dashboard for real-time visualization alongside our operational metrics.
- kelly
Data is not optional.
Interesting approach. I've used the Data Export feature for pulling raw data, but I hadn't considered using a CTE for an external mapping table. That's a clever way to keep the master list elsewhere and just reference it in the query.
One thing I'd be curious about: how are you handling the refresh schedule for that budget_mapping CTE? Are you manually updating the static list within the query each period, or is it pulling from a linked external database view? I've found latency there can be a headache.
Also, did you run into any issues with the transaction statuses? When joining payment_requests and transactions, I sometimes see duplicates if a request spawns multiple partial payments, and had to add a filter for the most recent 'settled' state.
Benchmarks or bust
So you're using a static CTE. That's a maintenance bomb waiting to go off. Someone *will* forget to update it next quarter and the CFO's report will be wrong.
Why not use a permanent table in your external database and just reference it via the Data Export's external connection? The static SQL blob is a vanity metric for "look I solved it" but creates real operational risk.
If it's not a retention curve, I don't care.
Operational risk is one thing, but your alternative assumes the external connection's latency and availability are zero. They aren't.
A static CTE in a report reviewed weekly fails loud and obviously when it's wrong. A silently stale external table linked over a network doesn't. Which is the bigger bomb?
Sometimes a simple, documented manual step is less risky than an opaque external dependency.
Show me the methodology.
You're spot on about the "fails loud" aspect. That visibility is a weird kind of safety net for processes that are still somewhat manual.
My team actually takes this a step further for critical reports. We embed the "last updated" date for the CTE data right in the report header. If that date is stale, it's the first thing the reader sees, forcing a conversation about why the refresh didn't happen. It turns a potential data error into a process compliance check.
I'd still argue an external table is better long-term, but only if you can build monitoring on that connection. If you can't guarantee that, your simple manual step is definitely the pragmatic choice.
Ask me about my RFP template
The "last updated" date in the header is a smart escalation. It moves the failure from a data integrity problem to a procedural one, which management is often better equipped to address.
My caveat would be that this relies on the CFO actually reading the header. In my experience, executives often skip straight to the charts and totals. To mitigate that, we also added a conditional flag column *within* the data itself, right next to the budget amounts, that shows "BUDGET MAPPING STALE" if the `last_updated` exceeds a threshold. It breaks the visual flow and forces attention.
Your final point about monitoring is the real clincher. An external table without health checks is less reliable than a documented, if manual, process. The simpler system often has a higher mean time between failures, even if its mean time to repair is longer.
Show me the bill.
Nice work getting the CFO on board -- that's usually the hardest part of any reporting pipeline. I run a similar setup for our own budget tracking, and the static CTE approach is more pragmatic than some in this thread give it credit for, especially when the mapping changes infrequently and the report is read by a human every week.
One thing I'd add from experience: watch out for the `payment_requests` to `transactions` join cardinality. If your Spendesk setup allows partial captures or multiple settlements on a single request (like travel advances that get reconciled in pieces), you'll get inflated billed amounts if you don't deduplicate. I had to add a `ROW_NUMBER() OVER (PARTITION BY request_id ORDER BY transaction_created_at DESC) AS rn` and filter to `rn = 1` to get the actual settled state. That also helped with the "pending" vs "settled" status confusion that user894 mentioned.
Curious: did you use a custom field directly on the `payment_requests` table for the budget code, or are you mapping from a Spendesk category to GL code via the CTE? I've found that attaching the budget code at the request level (via the API or a custom field) reduces the need for the external mapping table altogether, but it requires discipline during request creation.
API whisperer
You're absolutely right about the cardinality issue with partial settlements. In my own Spendesk reporting, I found that even with the latest transaction filter, you can still encounter edge cases with refunds or corrections creating multiple settled records for a single request_id. My solution was to aggregate transactions by `payment_request_id` and sum the `amount_settled` field, filtering for only transactions where `status = 'settled'`. This collapses the many-to-one join safely.
Regarding your question, I used a custom field on the `payment_requests` table for the initial budget code mapping, but the CTE was still necessary for our GL code translation and departmental hierarchy, which changes more often than our Spendesk categories. The custom field reduces mapping surface area but doesn't eliminate it entirely.
Test it yourself.
Aggregating on `amount_settled` is a solid approach that handles the multiplicity cleanly. One extra check I'd add is a conditional to exclude `settled` transactions with a negative `amount_settled` from that sum, unless you specifically want net amounts after refunds. Sometimes a refund posts as a separate `settled` row with a negative value, and summing it all could underreport the original spend.
I like your hybrid model of a custom field plus a CTE. It's a good reminder that mapping strategy can be layered - the custom field handles the stable, Spendesk-level logic, and the CTE manages the external, volatile mapping rules that change with the finance team's chart of accounts.
api first
Love the use of a custom field for budget codes, that's a great way to anchor your mapping. It makes the CTE much more manageable.
One thing that helped us was adding a validation step in the CTE itself, like a simple check that all active spend categories have a mapping. Catching a missing mapping before the CFO sees the report saves some Monday morning panic! 😅
Curious, did you use a custom field on the virtual card objects too, or just on payment requests?
Happy customers, happy life.
Good point on the fail-loud vs silent stale data. That's exactly why we keep our project budget mapping in a simple CSV that gets checked into version control with the report script. If the numbers are off, the diff is obvious and the fix is tracked.
Do you think there's a middle ground? Maybe a scheduled job that validates the external table's freshness and sends an alert before the weekly report runs, instead of relying on the connection to fail.
That's a really smart hybrid approach, keeping the CSV in version control. We do something similar for our department mappings - seeing a diff in the pull request is a great fail-loud moment for the team.
A scheduled freshness check is definitely the ideal middle ground. The tricky part is getting the alert to the right person at the right time. We tried it once, but the alert went to a shared engineering inbox and got lost. Now, we have the alert ping the report owner directly in Slack *and* add a warning flag directly in the Looker dashboard if the data is stale. It moves the alert closer to the consumption point.
Have you found a good way to automate the CSV refresh, or is the manual step part of the charm?
Congrats on the CFO buy-in, that's huge! I've been playing with a similar setup for my team's AWS cost reports.
The join using custom fields for budget codes is a great idea. I'm curious about the performance though - do you find any slowdown when the mapping CTE gets larger, or is it still pretty snappy for the Monday report?
Good question. It's been snappy so far. Our mapping table is still under 200 rows, which feels trivial for the database.
But I'm a little worried about scale. If we start mapping every single vendor or project code, I could see it slowing down. Have you hit a point with your AWS mappings where performance started to matter?
Yeah, 200 rows is definitely in the comfort zone. I started hitting noticeable lag with my AWS reports once the mapping table passed about 5,000 entries - that's when a simple join started to feel heavy.
A trick I use now is to keep a separate, lean mapping for high-level categories (like `department` or `project_code`) and leave the detailed vendor mapping to a separate lookup that only gets joined when drilling down. Most of the time, the CFO just wants the rolled-up view anyway, right?
Curious, what's your gut feel for when you'll cross the 1000-row threshold?