Skip to main content
How-to

Extracting Order History

Pull UltraCart order data out of BigQuery and into your own system, such as a loyalty portal or a customer data platform, with a one-time backfill followed by an ongoing sync. The same patterns work for customers, auto orders, and items.

Working examples for each entity ship in the package repository under examples/.

Pick the right dataset first​

This is where most first extracts go wrong. Two questions decide the dataset name:

Is the account a parent of linked accounts? If so, its order history lives in the ultracart_dw_linked datasets, which span every merchant ID under the parent. The base ultracart_dw dataset can be completely empty for an administrative-only parent, so a query against it returns zero orders for an account that has hundreds of thousands. Filter by merchant_id to narrow a linked dataset to one child account, or omit the filter to get all of them.

Do you need customer names and emails? Those live only in the taxonomy-gated _medium and _high datasets. Everywhere else they appear as *_hash columns, which have no matching SDK property and drop silently during hydration, leaving billing.first_name undefined. The full mapping is in the API reference.

merchant_id is case sensitive in the data, so ACME and acme are different values even though the project ID is always lowercased.

Backfill the full history​

Stream the whole history once to seed your store. Because query() is an async iterator, this holds one page in memory no matter how many rows come back:

const sql = `SELECT * FROM ultracart_dw.uc_orders
             WHERE merchant_id = @mid
             ORDER BY creation_dts`;

for await (const order of ucbq.query(sql, {
  params: { mid: 'ACME' },              // <- your merchant ID, exact case
  model: UltraCartApi.Order,
})) {
  await upsertIntoYourStore(order);     // idempotent on order_id
}

Make the write idempotent on order_id so a re-run after a failure is safe.

Resist the urge to chunk a backfill by date to bound it. The _linked tables are views, and they do not push row predicates down to partition pruning, so each date chunk rescans the whole table and N chunks cost roughly N times a single pass. One streaming pass is the cheapest option.

Keep it current​

After the backfill, pull only what changed since the last run, tracked with a watermark you persist between runs.

A creation_dts filter catches new orders only. It misses later edits such as refunds, shipments, and status changes, because the deduped view has no last-modified column. For most systems that is not good enough, so prefer the change data capture pattern below.

warning

creation_dts, refund_dts, and RecordTime are BigQuery DATETIME columns with no zone. The package emits ISO strings with a trailing Z on read, and BigQuery rejects that Z in a DATETIME comparison with "Invalid datetime string". Strip it with the exported helper before using a value as a filter parameter.

const { toBigQueryDatetime } = require('@ultracart/bigquery-sdk');

// '2026-06-25T21:41:40Z' -> '2026-06-25T21:41:40'
const params = { since: toBigQueryDatetime(watermark) };

Two details keep the boundary correct:

  • Overlap, then dedupe. Compare with >= against a watermark shifted back a few minutes, and dedupe by order_id on write. The upsert handles this, and it stops you dropping orders that share the boundary timestamp.
  • Advance the watermark last. Persist it only after the batch commits.

Catch updates with the streaming changelog​

The underlying changelog table keeps one row per version of each record, stamped with RecordTime. Everything that changed since a given time is the set of IDs with a changelog row newer than that time, and the current state of those records comes from the view, in a single query:

SELECT * FROM ultracart_dw.uc_orders
 WHERE merchant_id = @mid
   AND order_id IN (
         SELECT order_id
           FROM ultracart_dw_streaming.uc_order_streaming
          WHERE merchant_id = @mid
            AND RecordTime > @since
       )

This catches new and updated records, including edits to orders that are years old. Each entity joins its view to its own uc_*_streaming changelog on that entity's ID column: order_id, customer_profile_oid, auto_order_oid, or merchant_item_oid.

Two things to get right:

  • No partition_date filter on the changelog subquery. partition_date is the week the order was created, not the week it changed, so a recently edited old order sits in an old partition and a partition filter would silently drop it. Scanning RecordTime is columnar and costs a few megabytes.
  • Advance the watermark to the time the run started, not to the newest row seen, so anything written mid-run gets re-examined next time.

Deletes are ignored by this pattern. A deleted order's newest changelog row is an IsDelete row, which the view filters out, so the record simply stops being returned. Handle hard deletes separately if your system needs them.

Control what it costs​

Selecting fewer columns is the main lever, and on linked accounts it is the only one.

Column pruning works through the views because BigQuery storage is columnar. On a measured parent account, selecting four columns instead of SELECT * dropped one query from 1.352 GB to 0.166 GB. Row filters do not have the same effect: through a _linked view, a SELECT * scans the same bytes whether you filter on creation_dts, partition_date, merchant_id, or nothing at all. Those WHERE clauses still limit the rows you get back correctly, they just do not reduce bytes scanned.

Three habits follow from that:

  • Run dryRun() before any query you have not run before.
  • List the columns you actually need instead of SELECT *, especially for a repeating sync.
  • Pick a sync cadence deliberately, since a frequent incremental sync against a linked view rescans the selected columns every run.

To get real partition pruning, query the underlying partitioned table directly instead of the linked view.

Next​

Was this page helpful?