Getting Campfire Financials Into Snowflake Without a Nightly Job

What to replicate, and at what grain

Start with the general ledger, because everything else reconciles to it: journal entries and their lines, the chart of accounts, and the entities, departments, and custom dimensions those lines reference. Campfire exposes all of this through its REST API, alongside accounts receivable, accounts payable, cash management, bank reconciliation, and revenue recognition, so the warehouse model can follow the shape of the ledger rather than a flattened summary.

Then add the subledgers people actually query: invoices, credit memos, payments, bills, and revenue schedules. If you report recognized against deferred revenue, the revenue recognition objects are the most valuable thing in the whole feed, because rebuilding them from invoices is guesswork.

Resist pre-aggregating on the way in. A row in Snowflake that cannot be traced to a journal line is a row someone will eventually challenge, and you will not be able to defend it. Land the ledger grain, model on top of it in the warehouse, and keep the Campfire identifiers and timestamps on every row so a trial balance built in Snowflake ties back to what produced it.

How changes get there, and why the nightly job is the problem

A nightly extract has two failure modes that both show up at the worst time. It is stale by design, so during close week every number in the warehouse is describing yesterday. And it does its heaviest work exactly when the ledger is busiest, because it re-reads whole tables regardless of how little changed.

Campfire gives a sync engine two better options. Its webhooks fire when a record is written, so a change can be applied to Snowflake within seconds. Its list endpoints support last_modified_at filtering and sorting, which covers the initial backfill and acts as a safety net if a webhook delivery is ever missed. Several Campfire list endpoints, including the payment ones, are explicitly documented as being built for syncing into external systems. Used together, the load on the ledger scales with how much changed rather than with how big the tables are.

The practical result is that the warehouse stops having a nightly window. There is no batch to fail, no 6am rerun, and no gap where a controller and an analyst are looking at different numbers. If you want the wider comparison, we covered real-time sync versus batch ETL separately.

The return leg: modeled data back into Campfire

Replication is only half of it. The interesting work in a warehouse is the modeling, and a model that lives only in a dashboard rarely changes what anyone does. A two-way sync closes that loop by writing results back onto the Campfire record they belong to.

• Usage-based revenue. Model consumption in Snowflake, then write the computed amount back so it can be invoiced from the ledger rather than pasted in.
• Allocations and classifications. Derive a department or dimension in the warehouse where the joins are easy, then apply it to the Campfire record so reporting inside the ledger agrees with reporting outside it.
• Collections signals. Score accounts in Snowflake using product and support data, then surface the result where the AR team already works.
• Reconciliation flags. Detect the mismatch in the warehouse and write a flag back, so the exception is handled in the ledger rather than in a spreadsheet.

Each of these needs the same guardrails as any other write into a ledger: a scoped API user, field-level conflict handling so a model does not overwrite something a human just corrected, and an audit log showing what was written and why. For a broader view of the warehouse side, see our data warehouse integration page.