3 SERP Data ETL Strategies Compared: Realtime / Batch / Streaming

Background

After calling serpbase, how to ETL into a data warehouse? Three architectures: realtime sync, batch processing, streaming pipeline.

1. Realtime Sync (Simple)

import requests
from supabase import create_client
import os

supabase = create_client(os.environ["SUPABASE_URL"], os.environ["SUPABASE_KEY"])

def realtime_sync(query, data):
    today = data["query"]
    for i, item in enumerate(data.get("organic", []), 1):
        supabase.table("serp_results").upsert({
            "date": today,
            "keyword": query,
            "rank": i,
            "title": item.get("title"),
            "url": item.get("link"),
        }).execute()

def pipeline_realtime():
    r = requests.post(
        "https://api.serpbase.dev/google/search",
        headers={"X-API-Key": "sk_xxx"},
        json={"q": "SERP API", "gl": "us", "num": 5},
        timeout=10,
    )
    realtime_sync("SERP API", r.json())

2. Batch Processing (Low Cost)

import schedule

def batch_pipeline():
    queries = ["SERP API", "cheap SERP API", "SERP API selection"]
    today = datetime.now().strftime("%Y-%m-%d")

    for q in queries:
        r = requests.post(
            "https://api.serpbase.dev/google/search",
            headers={"X-API-Key": "sk_xxx"},
            json={"q": q, "gl": "us", "num": 5},
            timeout=10,
        )
        data = r.json()
        for i, item in enumerate(data.get("organic", []), 1):
            supabase.table("serp_results").upsert({
                "date": today,
                "keyword": q,
                "rank": i,
            }).execute()

schedule.every().day.at("03:00").do(batch_pipeline)

3. Streaming Pipeline (Realtime + Batch)

import json
from kafka import KafkaProducer

producer = KafkaProducer(bootstrap_servers="localhost:9092")

def stream_pipeline(query, data):
    for i, item in enumerate(data.get("organic", []), 1):
        producer.send("serp-events", json.dumps({
            "date": datetime.now().isoformat(),
            "keyword": query,
            "rank": i,
            "title": item.get("title"),
        }).encode())

def consumer():
    from kafka import KafkaConsumer
    consumer = KafkaConsumer("serp-events", bootstrap_servers="localhost:9092")
    for msg in consumer:
        event = json.loads(msg.value)
        process(event)

4. 3 Strategies Compared

Dimension	Realtime Sync	Batch Processing	Streaming Pipeline
Latency	Immediate	24h+	Realtime (second)
Cost (1000 calls)	$0.30	$0.30 (once daily)	$0.30 + Kafka cost
Implementation complexity	★	★★	★★★★
Suitable scale	Small	Large	Large
Realtime analytics	✗	✗ (batch)	✓