A web scraping API removes the hardest part of collection: proxies, rendering, retries and blocks. What it does not remove is everything that happens after the response arrives, and that is where most pipelines actually fail.
The failures are quiet. Duplicate rows from a retry nobody deduplicated. A price column full of strings like "$16.99" that someone cast to a number in a dashboard. A site redesign that turned a field to null three weeks ago. An extractor bug that cannot be fixed without paying to fetch everything again.
This guide is about the load side: getting scraping API output into a SQL database reliably, at volume, without losing the ability to explain or replay what you stored. The examples use PostgreSQL and the Shifter Web Scraping API, and the patterns carry over to any SQL database.
Start from the response you actually get
With the Shifter Web Scraping API you choose between two response shapes.
Raw HTML, where you parse on your side. Or structured JSON, by passing extract_rules that map CSS selectors to fields:
curl "https://scrape.shifter.io/v1?api_key=YOUR_API_KEY&url=https://shop.example.com/p/42&render_js=1&extract_rules=%7B%22title%22%3A%7B%22selector%22%3A%22h1%22%2C%22output%22%3A%22text%22%7D%2C%22price%22%3A%7B%22selector%22%3A%22.price%22%2C%22output%22%3A%22text%22%7D%7D"
# {"title": "Example Product", "price": "$19.99"}Two properties of that output shape your schema. A field whose selector matches nothing comes back as null rather than failing the request, so a missing element and a broken selector look identical in the response. And text outputs are display strings, so prices, ratings and dates arrive formatted for humans, not typed for a database. The rules syntax, including list extraction for result pages, is in the extraction rules docs.
Three layers, not one table
The design that survives contact with production separates what you received from what you concluded.
| Layer | Contents | Why it exists |
|---|---|---|
| Raw landing | Every successful response, as received, with fetch metadata | Replay extraction without re-fetching |
| Typed observations | Parsed, typed, validated values with a parse status | What analysts and applications query |
| Current state | The latest value per entity, derived from observations | Fast reads for products and dashboards |
The raw layer is the one teams skip and regret. Credits are spent on successful requests, so a bug in your parsing that is only fixable by fetching again costs the whole crawl twice. Land the response first, parse second, and a parser fix becomes a replay query.
The landing table
CREATE TABLE scrape_raw (
job_id text PRIMARY KEY,
source_url text NOT NULL,
market text NOT NULL,
fetched_at timestamptz NOT NULL,
http_status smallint NOT NULL,
body jsonb NOT NULL,
body_hash text NOT NULL,
extractor_version text NOT NULL
);
CREATE INDEX scrape_raw_url_time ON scrape_raw (source_url, fetched_at DESC);A few deliberate choices in there.
job_id is the idempotency key, computed before the request from the URL and the scheduling window, so a retry of the same logical job lands on the same key instead of creating a second row. extractor_version records which set of extraction rules produced the body, which is what lets you tell a site change from a rules change later. market records where the observation was made, because the same URL can return different content per country. And the API key is never stored anywhere in the request metadata you persist, since credentials in a database are credentials in every backup.
Loading idempotently
import hashlib
import json
import os
import psycopg
import requests
from psycopg.types.json import Jsonb
API = "https://scrape.shifter.io/v1"
RULES = {
"title": {"selector": "h1", "output": "text"},
"price": {"selector": ".price", "output": "text"},
}
EXTRACTOR_VERSION = "product-v3"
INSERT_RAW = """
INSERT INTO scrape_raw
(job_id, source_url, market, fetched_at, http_status, body, body_hash, extractor_version)
VALUES (%s, %s, %s, now(), %s, %s, %s, %s)
ON CONFLICT (job_id) DO NOTHING
"""
def job_id(url: str, market: str, window: str) -> str:
return hashlib.sha256(f"{url}|{market}|{window}".encode()).hexdigest()
def fetch(url: str, market: str) -> requests.Response:
params = {
"api_key": os.environ["SHIFTER_API_KEY"],
"url": url,
"render_js": 1,
"country": market,
"extract_rules": json.dumps(RULES),
}
return requests.get(API, params=params, timeout=90)
def land(conn: psycopg.Connection, url: str, market: str, window: str) -> None:
resp = fetch(url, market)
if resp.status_code != 200:
raise RuntimeError(f"{resp.status_code} for {url}")
body = resp.text
with conn.cursor() as cur:
cur.execute(
INSERT_RAW,
(
job_id(url, market, window),
url,
market,
resp.status_code,
Jsonb(json.loads(body)),
hashlib.sha256(body.encode()).hexdigest(),
EXTRACTOR_VERSION,
),
)ON CONFLICT (job_id) DO NOTHING is what makes the load safe to retry. Whether the failure was the network, your worker, or the database, running the job again cannot produce a duplicate. For MySQL the equivalent is a unique key with INSERT IGNORE or ON DUPLICATE KEY UPDATE.
Throughput: decouple fetching from loading
The fetch side has a hard ceiling set by your plan’s concurrency cap, and requests above it return 429. The database has its own ceiling, and it is usually the one teams hit first by opening a connection and a transaction per scraped page.
Put a queue between them. Fetch workers, sized to your concurrency cap, write responses to the queue. A small number of loader workers drain it in batches. For steady volumes, psycopg’s executemany in batches of a few hundred rows is enough. For large backfills, COPY the batch into an unlogged staging table and merge it in one statement:
INSERT INTO scrape_raw
SELECT * FROM scrape_raw_staging
ON CONFLICT (job_id) DO NOTHING;That turns thousands of round trips into one, and keeps the idempotency guarantee intact.
For long renders, the API can deliver asynchronously: pass webhook=<URL> and the response is posted to your endpoint when ready. Make that receiver idempotent on job_id too, because any HTTP delivery can end up retried by one side or the other.
Map API errors to pipeline behaviour
Status codes are not all retry candidates, and a loader that treats them uniformly either spams a broken configuration or gives up on transient failures. The full table is in errors and limits.
| Status | Pipeline behaviour |
|---|---|
408, 422, 500 | Retry with exponential backoff |
429 | Back off and reduce worker concurrency |
400, 401, 403 | Configuration error: send to a dead-letter queue and alert, never retry |
509 | Credits exhausted: stop the fetch stage and alert, retrying cannot help |
Failed requests and target 4xx or 5xx responses are not charged, and the API already retries transient failures up to three times before returning, so your own retries cost time rather than credits. They still cost time, which is why the backoff matters.
Typing the observations
This is where display strings become data, and where most silent errors are introduced.
CREATE TABLE price_observation (
source_url text NOT NULL,
market text NOT NULL,
observed_at timestamptz NOT NULL,
price_amount numeric(12,2),
currency char(3),
raw_price text,
parse_status text NOT NULL,
job_id text NOT NULL REFERENCES scrape_raw (job_id),
PRIMARY KEY (source_url, market, observed_at)
);Three rules keep it honest.
Keep the raw string next to the parsed value. raw_price is what lets you audit a suspicious number without re-fetching.
Parse per market, not globally. "1.299,00" and "1,299.00" are the same price under different conventions, and a currency symbol is not a currency: $ is US, Canadian and Australian dollars depending on the storefront. Resolve the ISO code from the symbol and the market together.
Record why a value is null. A parse_status of missing, unparseable or ok separates “the page had no price” from “our parser failed”, which the API’s null cannot tell you on its own.
For current state, derive rather than maintain. In PostgreSQL:
CREATE VIEW price_current AS
SELECT DISTINCT ON (source_url, market) *
FROM price_observation
WHERE parse_status = 'ok'
ORDER BY source_url, market, observed_at DESC;A derived view cannot drift out of sync with the history it summarises.
Detect schema drift before your users do
Sites change their markup, and a changed selector does not throw an error. It returns null, the request succeeds, a credit is spent, and the row lands looking valid.
The defence is a null-rate monitor per field, per source, per extractor version. Compute the share of missing parse statuses for each field over a rolling window and alert when it moves sharply from its baseline. A price field that goes from 2% missing to 60% missing overnight is a redesign, and catching it the same day is the difference between a patched selector and three weeks of unusable history.
When you fix it, bump extractor_version and replay the affected raw rows through the new parser. That is the payoff for landing raw responses.
Retention and partitioning
Raw landing tables grow fastest and are read least. Partition them by fetch date, keep them long enough to cover your realistic replay window, and drop old partitions rather than deleting rows. Observation tables are the historical record and usually earn a longer retention, partitioned the same way.
What to monitor
| Metric | What it catches |
|---|---|
| Successful fetches versus rows landed | Loader losses between the API and the database |
Duplicate job_id conflicts | Retry storms or scheduling overlap |
| Null rate per field and extractor version | Markup changes and broken selectors |
| Queue depth and load lag | A loader falling behind the fetch stage |
Credits consumed versus rows that parsed ok | Money spent on responses you could not use |
That last metric is the cost view that matters: credits per usable row, not credits per request. Usage and error rate for the API itself are visible in the panel under Web Scraping API.
FAQ
Should I store HTML or extracted JSON in the raw layer?
Extracted JSON is far smaller and usually sufficient. Store HTML only for sources where you expect to change extraction logic often, and give that table a short retention.
Is JSONB good enough to query directly?
For exploration, yes. For anything a product or dashboard depends on, promote fields into typed columns, where the database can enforce types and use ordinary indexes.
How do I avoid paying for pages that have not changed?
Every successful request costs a credit, so the saving has to come from fetching less, not from writing less. Use cheap signals such as a listing or sitemap page to decide which detail pages need fetching at all.
Does this work with the Amazon API as well?
Yes. Its responses are already structured JSON, so extraction rules drop out, but prices still arrive as display strings and the landing, typing and drift patterns apply unchanged.
The bottom line
A scraping API solves collection. Your database design decides whether what you collected stays trustworthy. Land every successful response with an idempotency key and an extractor version, type values per market while keeping the raw string, record why a value is null, derive current state instead of maintaining it, and watch null rates per field so markup changes surface the same day.
For Amazon specifically, the options are compared in the best web scraping APIs for Amazon monitoring, and a real estate version of the same pipeline is in how real estate companies use web scraping APIs. The product is on the Web Scraping API page, with plans on the pricing page.