Amazon Seller Partner to BigQuery: How to Move Your Data

Moving Amazon Seller Partner data into BigQuery is how sellers stop reconciling spreadsheets downloaded from Seller Central. Orders, settlements, returns and traffic in one place, joined against advertising spend and your own cost data, so that questions about actual profitability have an answer.

This guide covers the managed path with Airbyte. Two things make this connector unlike most: many of its streams are asynchronous report requests rather than simple API reads, which changes how rate limiting behaves, and one specific stream fails against BigQuery in a way the documentation calls out by name.

Amazon Seller Partner to BigQuery at a glance:

Why move data from Amazon Seller Partner to BigQuery?

Two situations account for most of these pipelines.

The first is true profitability. Amazon tells you what sold. It does not know your cost of goods, your shipping contracts or your advertising spend across other channels, and margin per product is the number that decides what you stock. That calculation needs Amazon data sitting next to data Amazon has never seen.

The second is history and reconciliation. Settlement reports and financial events are what your finance team reconciles against, and doing that across quarters in a spreadsheet is how errors happen. A warehouse makes it a query, and keeps the history after Amazon's own reporting windows have moved on.

What do you need before you start?

The Amazon side takes longer to arrange than the BigQuery side, so start it first:

A developer application and a refresh token. Authentication uses Login with Amazon, so you need an application identifier, a client secret and a refresh token generated from it. Getting a developer application approved is an Amazon process rather than an Airbyte one, and it is the long pole in this project. The Amazon Seller Partner source documentation walks through it.

A deliberate start date. Entered as a date, and if you leave it blank the connector defaults to two years ago. On a busy seller account that is a very large first sync against a source with strict rate limits, so set it to what you actually need.

A shortlist of report types. Amazon publishes a great many, and each one you enable is another asynchronous report to request and collect on every sync. Pick the reports your finance and merchandising teams name, not everything that looks interesting.

A BigQuery service account and dataset. With the BigQuery User and BigQuery Data Editor roles. Set the dataset location to match the datasets holding your cost and advertising data, since it cannot be changed after creation and BigQuery only joins datasets in the same location.

If you run more than one connection against the same Amazon account, the documentation recommends staggering their schedules so they do not overlap, because they share the same rate limits.

How do you build an Amazon Seller Partner to BigQuery pipeline in Airbyte?

Step 1: Get the Amazon application approved

Register the developer application, obtain the Login with Amazon credentials and generate a refresh token. Start this before anything else, because it involves Amazon's approval process rather than a form you fill in, and it is the step most likely to delay the project by weeks rather than hours.

Step 2: Configure the Amazon Seller Partner source

Click Sources in the left navigation, then New Source, and select Amazon Seller Partner, following adding a source. Supply the application identifier, client secret, refresh token, region and start date. Then set the report options, and pay attention to the period in days values, because some streams cap it: the general all-orders and fulfilled-shipments reports at 30 days, the returns-by-return-date report at 60. Exceed those and the value is adjusted down automatically.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key, and pick a loading method. Batched standard inserts is the default and is adequate for most seller accounts, since report data arrives daily rather than continuously.

Step 4: Set sync modes, with one exception

Click Connections, then New connection, and choose a sync mode per stream. Incremental works normally across most of the catalogue. The exception is ListFinancialEvents, which must use append rather than deduplication against BigQuery, for the reason in the next section. Set that one deliberately rather than accepting a default.

Why does ListFinancialEvents fail against BigQuery?

Because of a genuine mismatch between how Amazon returns the data and how BigQuery deduplicates. The SP-API returns aggregated event lists per time range rather than individual events with unique identifiers, so the stream has no primary key, and every top-level field is an array such as the shipment event list or the refund event list.

Airbyte's deduplication in BigQuery uses a clustering clause, and BigQuery requires non-JSON, non-array types for it. Point deduplication at a stream made entirely of arrays and the sync fails with an error saying the clustering expression must be groupable but the type is JSON. It is a clear error once you have seen it and completely opaque the first time.

The fix is to use append rather than deduplication for that stream specifically. That means the raw table accumulates one record per time range per sync, so build a downstream view that unnests the arrays into individual events and deduplicates there. That modelling step is worth doing regardless, because a table of nested event lists is not something your finance team can reconcile against directly.

How should you handle report rate limits?

Report streams work differently from ordinary API reads. The connector asks Amazon to create a report, waits for it to be produced, then collects it. Report creation is the rate limited operation, which is why enabling many report types multiplies your exposure rather than just your runtime.

Two settings help directly. Setting the maximum done report age, between one and twenty-four hours, lets the connector reuse a recently completed report instead of creating a new one, which cuts creation calls and is the single most effective lever if you sync frequently. And a separate option makes the sync fail fast with an actionable configuration error once the retry budget is exhausted, rather than backing off and retrying, which is useful when persistent rate limiting means the configuration itself needs changing.

By default the connector applies its own backoff and retry strategy and, if attempts are exhausted, raises a transient error so the sync may be rescheduled automatically. That is the right default for occasional limits and the wrong one when something is structurally too aggressive, because it hides the problem behind retries.

What about data Amazon revises after the fact?

Amazon updates report data after a sync has already completed, which means a figure you captured this morning may not match what the same report says tomorrow. On settlement and returns data in particular, that is normal rather than a fault.

The connector offers a report stream lookback window in hours, which re-fetches previously synced data on each sync. It defaults to zero, meaning lookback is disabled, so this is something you turn on rather than something that protects you by default. Note it does not apply to the monthly sales and traffic report or the vendor sales report, because those use monthly or date-only report boundaries.

Decide with your finance team how far back to look and whether the warehouse should hold the latest figure or a record of what each sync saw. Reconciliations tend to hinge on exactly that distinction, and it is much easier to settle before someone builds a monthly report on top.

Frequently asked questions

My sync fails saying the clustering expression must be groupable. What now?

That is the ListFinancialEvents stream in deduplication mode. It has no primary key and its top-level fields are arrays, which BigQuery cannot cluster on. Switch that stream to append and unnest the arrays downstream.

How do I reduce rate limiting on report streams?

Set the maximum done report age so the connector reuses recently completed reports rather than creating new ones, enable fewer report types, and stagger the schedules of any connections sharing the account.

Why do yesterday's numbers keep changing?

Amazon revises report data after a sync completes. Set a report stream lookback window, which is disabled by default, to re-fetch recent data on each sync.

Why was my period in days changed automatically?

Some report streams cap it. The general all-orders and fulfilled-shipments reports allow 30 days and the returns-by-return-date report 60, and a larger value is adjusted down to the maximum for that stream.

Can I do this without writing code?

The Airbyte setup is UI-driven, and the Amazon side is an application approval process. You will want a BigQuery view that unnests the financial events arrays, which is a short piece of SQL.

Get your Amazon Seller Partner data into BigQuery

Start the Amazon application approval before anything else, set a real start date rather than accepting two years, enable only the reports someone named, and set ListFinancialEvents to append rather than deduplication. Then turn on a lookback window, because Amazon revising figures after the fact is normal and the default protects you from none of it.

Airbyte's connector catalog includes 600+ pre-built connectors, so marketplace data can sit alongside your advertising and cost sources in one warehouse. For another commerce source landing in BigQuery, see Shopify to BigQuery, and for attribution data on the same customers, Adjust to BigQuery.

Start syncing now →