Square to Snowflake: how to load POS sales data your team can actually query

How do you connect Square to Snowflake?

You connect Square to Snowflake by reading Square objects through the paginated REST API and writing them into Snowflake tables, because there is no native Snowflake destination inside Square. Something sits in the middle: a managed connector, a scheduled script against the API, or a custom export. Each Square object is scoped by location_id, so the reader iterates locations as well as pages.

The Square API walks forward with a cursor token rather than page numbers, returning a batch of objects plus the cursor for the next batch, and it enforces rate limits. That matters the moment you backfill. Pulling several years of payments by hand across a dozen store locations means thousands of sequential cursor requests, careful bookkeeping per location, and retry logic for the calls that get throttled or time out. It is slow and brittle, which is why most teams do not hand-roll it past the first prototype.

Practically, the connection has three parts. First, a Square access token with read scope on the objects you replicate (Payments, Orders, Refunds, Customers, Catalog). Second, a Snowflake landing spot: a database, a schema, a warehouse, and a role that can create tables and run MERGE. Third, the transfer itself, which should page through each object type per location, land the raw JSON or typed columns in a staging table, and merge into the target. The managed path is the Square to Snowflake integration, which owns the pagination, the per-location watermark, the staging table, the MERGE, and the retries. If you are weighing warehouses, the equivalent write-up for the other one is Square to BigQuery, and if you also push sales into accounting, the Square to QuickBooks connector follows the same shape.

How do you load Square data into Snowflake incrementally?

Load Square incrementally by keeping a watermark per location of the last updated_at value you saw, requesting only newer objects with a begin_time to end_time window on each run, and MERGE-ing the result into Snowflake keyed on the object id. Dedupe each batch first so a retry after a timeout produces the same state instead of duplicate rows.

Square objects mutate after creation. An order moves from open to completed, a payment gets a refund attached, so filtering on a create timestamp alone misses updates. Query the time window against updated_at where the object supports it, keep the watermark per location_id (locations sync at different rates and one slow store should not hold back the others), and overlap the window slightly on each run so an object committed out of clock order is not skipped.

Whichever window you use, the load into Snowflake follows the same shape. Land the batch in a staging table, then collapse duplicates before you merge, because a single window can carry several versions of the same object. Keep the newest version per id:

Step	What runs	Why it matters
Dedupe the batch	ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC), keep rank 1	Stops a multi-version batch from creating duplicate target rows
MERGE on id	MERGE INTO target USING staging ON target.id = staging.id	Makes retries idempotent, so a re-run after a timeout produces the same state
Advance the watermark	Store the max updated_at per location_id	Next run resumes exactly where this location stopped

Because the MERGE is keyed on the id and idempotent, re-reading a handful of rows in the overlap window costs nothing and buys you correctness. Keep the full raw payload in a VARIANT column on the staging table so that when Square adds a field, you can shape it into a typed column later without re-pulling history. Snowflake also lets you set a clustering key on high-cardinality filter columns like location_id or the payment timestamp, which keeps large fact tables from scanning everything on a filtered query.

How do you calculate Square fees and net sales in Snowflake?

Calculate real net sales by subtracting Square's processing fees and refunds from gross payments, not by reading the payment amount alone. The gross sits in amount_money.amount, but the fee lives on the payment's processing_fee array (each entry carries its own amount_money and an effective_at time), and refunds are separate objects with their own negative-effect amounts.

So the finance-grade model has three inputs. Gross is the sum of payment amounts. Fees are the sum of every processing_fee entry across those payments (a single payment can carry more than one fee entry, which is why it is an array). Refunds are the sum of the refund objects for the period. Net sales is gross minus fees minus refunds, summed across every location. If your dashboard shows gross payments as revenue, it is overstating the number by the entire Square fee line plus refunds.

Two details keep the math honest. First, all amounts are in minor units, so divide by 100 for USD before you sum, and do it in a reporting view where the divisor is visible and testable rather than baking it into the loader. Second, attribute fees and refunds by their effective_at date, not the original payment date, so a refund issued in a later month lands in that month's net. A single Square seller who just wants a clean personal number rather than a warehouse can track every payout in one place instead, but a multi-location operator reconciling to the books needs the full gross, fee, and refund breakdown.

How do Square API fields map to Snowflake types?

Map Square ids to STRING, money in minor units to NUMBER after dividing by 100, ISO 8601 timestamps to TIMESTAMP_NTZ in UTC, and the raw JSON payload to VARIANT. The two that cause silent errors are money (INTEGER cents, not dollars) and timestamps (RFC 3339 strings, not a native date).

Start with the object-to-table layout, then the field types.

Square object	Snowflake table	Notes
payment	FCT_PAYMENT	Gross amount and status. Join the processing_fee array for real fees
order / line_item	FCT_ORDER_LINE	One row per line item. Explode the order's nested line items on load
refund	FCT_REFUND	Separate object with a negative effect. Subtract from period net sales
customer	DIM_CUSTOMER	Join key for repeat-buyer analysis. Present only when a customer is attached
location	DIM_LOCATION	The store or register. Carry location_id into every fact table

Square field / type	Snowflake type	Notes
amount_money.amount (minor units)	NUMBER	INTEGER cents. Divide by 100 for USD. Never store money as FLOAT
amount_money.currency (ISO code)	STRING	Drives whether and how you divide to reach major units
created_at / updated_at (ISO 8601)	TIMESTAMP_NTZ	RFC 3339 strings. Cast and store in UTC (or use TIMESTAMP_TZ)
id (payment, order, refund)	STRING	The natural MERGE key. Stable and unique per object
location_id	STRING	Scope key. The watermark and clustering key both hang off it
raw payload (JSON)	VARIANT	Keep the full object so new fields need no re-pull, just a new view

The minor-units case is worth repeating because it is a real financial bug, not a rounding nuisance. A payment of amount_money.amount = 5000 in USD means fifty dollars, not five thousand. If a report reads the raw integer as dollars, the day's sales inflate a hundredfold. Store the minor-unit value untouched in NUMBER and apply the / 100 divisor in a reporting view, where the logic is visible and testable, so no one has to guess whether a column is cents or dollars.

How do you handle multiple Square locations in Snowflake?

Handle multiple locations by carrying location_id as a column on every fact table, keeping a separate watermark per location, and modeling each location as a row in DIM_LOCATION. Never fold locations together at load time, because you lose the ability to report per store and to sync each one independently.

Square scopes its reads by location, so a multi-store operator is really running one incremental pipeline per location_id. Track the watermark per location in a small control table: one busy flagship store and one seasonal pop-up will sit at very different points in time, and a single global watermark would either re-scan the fast store or starve the slow one. Loop the locations, advance each watermark on its own, and a location that errors out only re-runs itself on the next pass.

On the reporting side, keeping location_id on every row lets you roll net sales up to the company and drill back down to a single register without a second pipeline. Set the clustering key on location_id (or a composite of location and date) for the large fact tables so a query filtered to one store prunes the rest, and join to DIM_LOCATION for the store name, timezone, and address rather than repeating those attributes on every fact row.

How much does it cost to sync Square to Snowflake?

Snowflake bills on two axes: storage for the compressed bytes you keep, and compute measured in credits while a virtual warehouse runs. Square volumes are usually modest, so storage is a rounding error. The real lever is how long your warehouse stays awake, and that is a configuration question, not a row-count question.

Cut the compute. Set the warehouse to auto-suspend after a short idle period (say sixty seconds) so it stops billing credits the moment a load finishes, and let it auto-resume on the next run. Size the warehouse to the smallest tier that keeps your MERGE within its window, since a larger warehouse burns credits proportionally faster. Set a clustering key on the columns you filter most (location_id, the payment timestamp) so dashboards prune micro-partitions instead of scanning full history, and keep the raw VARIANT in staging rather than exploding every field you may never query.

On the pipeline side, our pricing is flat by plan (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a heavy holiday sales month does not produce a surprise invoice. See flat pricing for the full breakdown. Auto-suspend and cluster on the warehouse side, keep the plan flat on the pipeline side, and the total cost of a Square to Snowflake sync stays predictable as you add locations and volume.