QuickBooks to Snowflake: how to load financial data your team can actually query

How do you load QuickBooks Online into Snowflake?

You load QuickBooks Online into Snowflake by authenticating to the Intuit v3 REST Accounting API with OAuth2, reading each entity for a company (its "realm"), staging the raw records in Snowflake, and MERGE-ing them into modeled tables keyed on the entity Id. QuickBooks ships no native Snowflake driver, so a managed connector, a scheduled script, or the CDC endpoint has to move the data between them.

The job has three moving parts. First, auth: an OAuth2 app with a client id and secret, a refresh token per company, and the realm id that scopes every request. Second, the read side: the v3 API exposes entities like Invoice, Bill, Payment, JournalEntry, Account, Customer, Vendor, and Item, queried with a SQL-like syntax, plus the general ledger through the reports endpoints. Third, the load: a Snowflake warehouse, a staging table (often a VARIANT column for the raw JSON), and a MERGE into the typed target.

A practical pattern lands the raw payload first and types it inside the warehouse. Write each page of API results into a staging table, flatten the JSON with Snowflake's LATERAL FLATTEN, cast the fields to their target types, then MERGE. The managed path is the QuickBooks to Snowflake integration, which handles the OAuth refresh, pagination, the watermark, the staging table, the upsert, and the retries. The same shape works for other sources, like Stripe to Snowflake, with a different API in front.

How do you map QuickBooks fields to Snowflake types?

Amounts map to Snowflake NUMBER(38, 2) (never FLOAT for money), TxnDate maps to DATE, timestamps such as MetaData.LastUpdatedTime map to TIMESTAMP_NTZ stored in UTC, and the entity Id stays a VARCHAR. Currency codes are short strings and exchange rates are NUMBER. Get these right at load time or reconciliation gets painful later.

QuickBooks returns amounts as JSON strings or decimals, so cast them explicitly rather than trusting an inferred type. Nested line items (an invoice carries a Line array) should flatten into a separate line-level fact table, one row per line, so you can sum at the grain finance actually reports on. Here is the field-level mapping.

QBO field	QBO type	Snowflake type	Note
TotalAmt, Line.Amount	String / decimal	NUMBER(38, 2)	Never FLOAT for money. Scale 2 covers most currencies to the cent; widen scale only if you hold rates or units
TxnDate	String (date)	DATE	Calendar date of the transaction, no time component. Good clustering key for fact tables
MetaData.LastUpdatedTime	String (ISO 8601)	TIMESTAMP_NTZ	Convert to UTC before storing. This is the incremental watermark, so treat it as an absolute point in time
MetaData.CreateTime	String (ISO 8601)	TIMESTAMP_NTZ	Also UTC. Useful for auditing when a record first appeared in the ledger
Id, CustomerRef.value	String	VARCHAR	Intuit ids are opaque strings. Keep them as VARCHAR and MERGE on them; do not cast to a number
CurrencyRef.value	String	VARCHAR	Three-letter ISO code such as USD or EUR. Store it next to every amount
ExchangeRate	Decimal	NUMBER(38, 6)	Converts transaction currency to home currency. Extra scale keeps home-currency totals reproducible
Line (nested array)	Array of objects	Flatten to rows	Use LATERAL FLATTEN into a line-level fact table, one row per line, linked back to the header Id

Cluster the large fact tables (invoice lines, journal lines) on transaction date so pruning skips partitions a report does not need. Snowflake handles micro-partitions automatically, but a good clustering key on high-cardinality date columns is what keeps a quarter-to-date query from scanning every invoice you have ever loaded.

How do you load QuickBooks incrementally?

You load QuickBooks incrementally by filtering each entity on MetaData.LastUpdatedTime as a watermark, or by calling the ChangeDataCapture (CDC) endpoint, which returns every entity changed since a timestamp including deleted ones. Store the last watermark, overlap the window slightly on each run, and MERGE into Snowflake keyed on the entity Id so retries stay idempotent.

The watermark query looks like WHERE MetaData.LastUpdatedTime > :last_watermark, ordered by that timestamp and paged through with STARTPOSITION and MAXRESULTS (the API caps each page near 1000 rows). After a clean run you advance the stored watermark. Two habits keep it honest: overlap the window by a few minutes so records committed slightly out of order are not skipped, and keep the MERGE idempotent so re-reading a handful of rows costs nothing.

The load itself: write the batch to a staging table, collapse it to one row per Id keeping the latest LastUpdatedTime, then MERGE ... ON target.Id = staging.Id WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT. Re-running the same batch after a timeout lands the same state rather than duplicates. This upsert-on-natural-key shape is the whole reason a retry is safe. The table below weighs the three extraction methods so you can pick per entity.

Extraction method	Pros	Cons
Full reload	Simplest to reason about; always self-correcting; fine for small, slow dimensions like Account or Item	Re-reads the whole entity every run; hits throttles and the 1000-row page cap on large companies; wasteful for high-volume transactions
LastUpdatedTime watermark	Moves only changed rows; cheap and fast; easy to checkpoint and resume; the default for invoices, bills, and payments	Does not surface hard deletes on its own; needs an overlap window so out-of-order commits are not missed
ChangeDataCapture (CDC)	Returns inserts, updates, and deletes since a timestamp in one call; keeps the warehouse free of ghost rows	More API surface to handle; still requires MERGE plus delete handling on the load side; timestamp bookkeeping matters

A common split is a watermark load for high-volume transactions, CDC when deletes have to be reflected (a voided invoice should disappear from the warehouse, not linger), and a periodic full reload for tiny dimensions where correctness beats efficiency. Handle CDC deletes by reading the returned entity status and either soft-deleting the row or removing it in the same MERGE.

How do you handle multi-currency and tie out to the general ledger?

Carry both amounts and the rate. QuickBooks stores a home currency and, for multi-currency companies, a transaction CurrencyRef plus an ExchangeRate. Load the transaction-currency amount, the home-currency amount, and the rate into Snowflake so general ledger totals tie back to QuickBooks exactly. Drop any one of the three and reconciliation breaks somewhere down the line.

With a single-currency company this is a non-issue: every amount is already in the home currency. The moment multi-currency is on, a euro invoice posts in EUR but rolls up to the home currency (say USD) at the transaction's exchange rate. Store only the EUR amount and your USD ledger will not match; store only the converted figure and you lose the number the customer actually saw. Keep both plus the rate, and every derived column stays auditable.

Tie-out is the real test. Pull the general ledger through the reports endpoint, sum your modeled journal_lines by account in Snowflake, and confirm the totals match the GL report for the same period. If they drift, the usual culprits are a FLOAT amount column, a missed CDC delete, or a posting period that differs from the TxnDate (a bill dated at month end can post into the next period). Keep both the transaction date and the posting period so reports group either way. Getting clean source documents into QuickBooks in the first place helps here too; if you are still keying in bank activity by hand, a tool that can convert those statements straight to a QuickBooks-ready file keeps the ledger you are replicating accurate at the source.

Should you build this or use a managed integration?

Build it if you have engineers who want to own OAuth refresh, pagination, throttling, the watermark, CDC deletes, the staging flatten, and the MERGE, and keep owning them as Intuit changes the API. Use a managed integration if you would rather define the field mapping once and let the pipeline run. Both land the same modeled tables in Snowflake; the difference is who maintains the plumbing.

The hidden cost of building is not the first sync, it is the second year. The Intuit API evolves, refresh tokens expire, throttles shift, and a schema change can silently flip an incremental job back to a full scan. Someone has to notice and fix that. A managed data integration platform absorbs that maintenance and gives you per-record logs, scheduled incremental sync, and automatic retries out of the box, which is the same reasoning behind the sibling QuickBooks to BigQuery pipeline.

On price, our plans are flat by tier (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a busy month-end close does not produce a surprise line item. You map QuickBooks to Snowflake in the browser with no-code field mapping, set the schedule, and watch the per-record logs. See pricing for the full breakdown, or start a demo to see the entity mapping against your own company file.

Questions

How do you load QuickBooks Online into Snowflake?

Authenticate to the Intuit v3 REST Accounting API with OAuth2, read each entity for a company realm, stage the raw records in Snowflake, then MERGE them into modeled tables keyed on the entity Id. QuickBooks ships no native Snowflake driver, so a managed connector, a scheduled script, or the CDC endpoint sits in the middle and moves the data.

How do you map QuickBooks fields to Snowflake types?

Amounts map to NUMBER(38, 2) (never FLOAT for money), TxnDate maps to DATE, timestamps such as LastUpdatedTime map to TIMESTAMP_NTZ in UTC, and the entity Id stays VARCHAR. Currency codes are short VARCHARs and exchange rates are NUMBER. Flatten nested Line arrays into a line-level fact table with LATERAL FLATTEN, one row per line.

How do you load QuickBooks into Snowflake incrementally?

Filter each entity on MetaData.LastUpdatedTime as a watermark, or call the ChangeDataCapture endpoint which returns every entity changed since a timestamp including deletes. Store the last watermark, overlap the window slightly, and MERGE into Snowflake keyed on the entity Id so a retried batch lands the same state instead of duplicates.

How do you handle QuickBooks pagination and rate limits?

The v3 API uses SQL-like queries with STARTPOSITION and MAXRESULTS and caps each page near 1000 rows, and Intuit enforces per-realm throttles. A production job pages in order, backs off on throttle responses, and checkpoints its position so a mid-run failure resumes rather than restarting the whole pull from zero.

How do you handle multi-currency when loading QuickBooks into Snowflake?

Carry both amounts and the rate. QuickBooks stores a home currency plus, for multi-currency companies, a transaction CurrencyRef and an ExchangeRate. Load the transaction-currency amount, the home-currency amount, and the rate so general ledger totals tie back to QuickBooks. Keep both TxnDate and posting period since they can differ.

Should you build a QuickBooks to Snowflake pipeline or use a managed tool?

Build it if engineers want to own OAuth refresh, pagination, throttling, the watermark, CDC deletes, and the MERGE, and keep owning them as Intuit changes the API. Use a managed integration to define the mapping once and let it run with scheduled incremental sync, per-record logs, and retries. Both land the same modeled tables in Snowflake.