Data pipelines & warehousing

Marketplace data does not arrive as a table. Amazon gives you report files you request and then poll for, or, with Marketing Stream, an hourly push into your own AWS account. That is durable storage and useless to the person holding the question: nobody on a PPC team can query a raw S3 bucket.

The work is moving that pile somewhere queryable, and it turns less on engineering than on a few decisions taken at ingest time that you do not feel for months, then feel all at once — as a query bill, or as a warehouse quietly disagreeing with the marketplace it was built from.

Where to start, depending on what you already have

• Nothing built yet — the warehouse choice, mostly a question about the stack you already run.
• Data in S3 and nobody able to query it — the transfer, the landing table, the modelling on top.
• A working pipeline and a bill you did not expect — the line items, and the places they blow up.
• More than one seller account — the network layer, nobody's first thought and everybody's eventual problem.

Choosing the warehouse

Both warehouses will hold SP-API and Ads data without complaint, so the BigQuery and Snowflake comparison settles it on the three things that actually differ: the stack you already run, your query patterns, and which cost model suits them. BigQuery fits most sellers; Snowflake pulls ahead if you are already on it or running heavy analytics constantly. Spending a fortnight on the decision is the only clearly wrong answer.

Getting the data in and making it usable

Most of the build is one configuration screen and a few careful details. Wiring S3 into BigQuery with the Data Transfer Service runs the whole path: a read-only AWS IAM user, the dataset and landing table, the transfer itself, then modelling the raw hourly rows into something clean and hanging Google Sheets off the result — that last step being the one the ads team actually touches. Scheduling notes and the errors that show up on the first run are at the end, where you will need them.

Keeping the bill boring

Marketing Stream costs nothing from Amazon's side, but it delivers into your AWS account and that bill is real. What the AWS side actually costs itemises every line the pipeline touches, gives realistic monthly ranges, and then separates the steady-state number from the places bills genuinely blow up — the difference between what the pipeline costs and what a misconfigured one costs you.

Once it is more than one account

Run twenty seller accounts off one server and, from the marketplace's side, twenty ostensibly independent businesses are making authenticated calls from one IP address — and marketplaces treat shared infrastructure as a signal that the accounts are connected. Why each account needs its own egress IP argues that stable matters as much as separate, covers the provisioning trap where you are handed the wrong kind of address and told it succeeded, and names the failure nobody plans for: the proxy account running out of money. Transient failures through the proxy get their own section, because they are normal and worth designing around.

These came out of pipelines built for clients rather than from reading documentation, which is why they spend more words on quiet failure than on the happy path. The same work end to end is what the reporting automation service covers.