Enrichment pipeline architecture: scale from 1k to 100k rows/month (2026)

Most teams want both batch and real-time enrichment from day one. The trap is that the right architecture for each is genuinely different. Batch-first systems optimize for throughput and cost — they process tens of thousands of records in a single nightly pass, are idempotent by design, and can retry failed lookups overnight without anyone noticing. Real-time-first systems optimize for latency — they target sub-2-second response on a single record, can't afford to retry, and need degraded-mode fallbacks when a provider is slow.

You can layer real-time on top of a batch-first system (request → if cached, return; else queue + return placeholder). You can't easily go the other way without rewriting your queue logic. Pick the mode that matches 80% of your traffic, build for it, then layer the other on as needed.

How Derrick handles it: our Sheet integration is batch-first by default (whole columns enriched at once), with on-demand real-time available via the API/MCP for single-row lookups. Same waterfall, two pipelines under the hood.

The first version of every enrichment script uses a for-loop with `requests.get()` — and it works fine, until it doesn't. The breaking point is reliably around 5,000 records: at that volume, even with a fast provider, you're spending 30+ minutes on lookups that should take 5 minutes if you ran them in parallel. The Python script eats memory because it holds all the responses in scope, the connection pool exhausts, and one slow provider blocks all the others.

The fix is async (asyncio + httpx in Python, or any worker queue). But the gotcha is provider rate limits. Most enrichment APIs allow 10-50 concurrent requests; go past and you get 429s in waves. Real-world pattern: async with a semaphore set to 80% of the provider's documented concurrency limit, plus exponential backoff on 429. This gives you 5-10x throughput vs sync, while staying inside the provider's good graces.

How Derrick handles it: we batch and parallelize requests per provider with adaptive concurrency — when 429s spike, we throttle automatically. The Sheet user sees one progress bar, not the dozen worker processes underneath.

The naive waterfall orders providers by cost: try the cheapest first, fall through to expensive ones only when needed. It sounds rational. It's actively bad.

The reason: enrichment cost isn't dominated by per-record price — it's dominated by match rate × latency × the cost of the calls that fail. A €0.02 provider with 25% match rate costs you €0.08 per match plus 4x the latency of a 75%-match-rate €0.05 provider that would have found it on the first try. Cheap-first wastes both money and time.

The correct rule: order providers by expected match rate on YOUR data, with cost as a tiebreaker. Run a 500-record benchmark per provider on a representative sample before you decide the order. Most teams discover the order they would have guessed is wrong by at least one position.

How Derrick handles it: our default waterfall is ordered by observed match rate across our customer base, then re-optimized per ICP cluster behind the scenes. You don't touch the ordering — the system learns from outcomes.

Every production pipeline eventually has a provider go down mid-run. Without idempotency, that means re-running the whole batch — paying again for the records that already succeeded. With idempotency, you re-run safely and only the failed records get re-attempted.

The implementation is simple: tag every enrichment request with a deterministic key (e.g. `sha256(email + provider + date_bucket)`) and have your enrichment client check a local cache before making the network call. Failed records get a separate retry queue with exponential backoff (1s, 4s, 16s, 64s, then dead-letter). On Monday morning, the dead-letter queue tells you exactly which records need manual attention.

Teams that skip this typically discover it after the third "oh no, the script crashed at row 8,400 — do we re-run the whole thing?" Saturday evening. Build it in week one, save yourself fifty hours over the year.

How Derrick handles it: all enrichments are cached by (input, provider, freshness window) — re-running an enrichment on an unchanged Sheet returns instantly without re-billing. Failed records are flagged in the Sheet and retryable in one click.

A pipeline you can't measure is a pipeline you can't fix. Three metrics, tracked over rolling 7-day windows, will catch 95% of the things that go wrong:

1. Match rate per provider — if any provider drops more than 10 points week-over-week, they've changed their data sourcing or you've changed your ICP. Investigate before billing piles up.
2. p95 latency per provider — the average hides the slow tail that's blowing up your real-time SLO. Track p95, alert on 2x baseline.
3. Cost-per-verified-match — the true unit economics. If this is rising while match rate is flat, providers are getting more expensive or your data is getting harder. Either way, you need to know.

These three plug into any monitoring stack (Datadog, Grafana, even a Sheet refreshed nightly). The teams that fly blind eventually rediscover them after a quarter of unexplained cost overruns.

How Derrick handles it: these three live in the in-Sheet dashboard, refreshed in real time as enrichments run. No extra monitoring stack to set up, no extra subscription to pay.