Case Study · Data EngineeringFood Delivery / Market Data

Every restaurant in Pakistan, turned into a queryable warehouse

Pakistan's richest restaurant dataset — menus, prices, cuisines, and locations — lives locked inside one app. This pipeline unlocks it: it discovers 29,000+ restaurants from Foodpanda's own sitemap, reverse-engineers the internal vendor API, and lands full menus in a normalized PostgreSQL warehouse ready for price intelligence and discovery products.

AM

Arham Mirkar

DataLayer — Enterprise Data Infrastructure

29,115
Restaurants discovered nationwide
150K+
Menu items ingested
11
Normalized warehouse tables
0
Bot blocks in logged runs
The Problem

The data exists — it just isn't yours

If you want to answer a simple question — what does biryani cost across Karachi, and who's cheapest?— there's no dataset to query. Menus, prices, ratings, and locations for tens of thousands of restaurants sit inside Foodpanda, visible one screen at a time and impossible to analyze in bulk.

Extracting it isn't a matter of a quick scrape. The vendor data is served by an undocumented internal API, gated on your delivery location, and sat behind bot protection. Doing it at national scale — reliably, over hours, without getting blocked — is the actual engineering problem.

The System

Six stages, one national dataset

The pipeline is deliberately boring where it should be and clever only where it has to be. The cleverness is in how it gets clean data out; the rest is disciplined, resumable plumbing.

Discover

Sitemap

Instead of crawling listing pages city by city, the pipeline pulls Foodpanda's own adventure-map sitemap and extracts every restaurant URL — 29,115 unique vendor codes for all of Pakistan in a single pass.

Find the door

Access insight

Foodpanda gates vendor data behind a delivery-range check tied to your coordinates. The key discovery: omitting latitude and longitude skips that gate entirely and returns the full vendor and menu payload — no cookies, no bot puzzle.

Fetch

Internal vendor API

Each code hits the internal v5 vendor endpoint with a nested include that returns the whole menu tree — categories, products, and price variations — in one request, GraphQL-style.

Normalize

Structure

Raw JSON is decomposed into brands, cities, cuisines, vendors, menu sections, products, and price SKUs — with cuisines stored both as a queryable junction table and as JSONB for fidelity.

Load

PostgreSQL

Everything upserts into an 11-table warehouse with ON CONFLICT DO UPDATE, so a re-run refreshes prices and availability instead of duplicating the country.

Endure

Resume + reconnect

A checkpoint every 50 vendors, an auto-reconnecting database wrapper, and a run log mean a multi-hour national crawl can crash, resume, and finish without starting over.

Deep Dive 01

The door nobody locked

Most people try to defeat bot protection head-on — harvesting cookies, solving challenges, rotating proxies. An early prototype here did exactly that with a headless browser. The production pipeline found a simpler path: the API only enforces its delivery-range check when you send coordinates. Leave them out, and it happily returns the full vendor and menu payload.

Coordinate-free access

One nested request returns the entire menu tree — categories, products, and every price variation — without a location, a login, or a bot challenge.

GET pk.fd-api.com/api/v5/vendors/{code}
  ?language_id=1
  &include=menus,menus.menu_categories,
    menus.menu_categories.products,
    ...products.product_variations
# note: no latitude / longitude

Looking like a real client

Requests carry the same headers the web app sends, with session identity that rotates so no single fingerprint gets hot.

x-fp-api-keyFoodpanda web client key
x-global-entity-idPakistan market
perseus-client-id / session-idTimestamp + UUID, refreshed per session
user-agentChrome / Firefox pool

Held at ~0.85s between requests, with user-agent and session rotation every 50 calls and an automatic cooldown on any 403. Across the recorded national crawl, that discipline produced zero bot blocks.

Deep Dive 02

A schema built for questions

A single JSON blob per restaurant is useless for analysis. The payload is decomposed into 11 related tables so you can ask real questions — chain density by city, cuisine pricing, menu-size distributions — with plain SQL.

11-table model

chainscitiescuisinesvendorsvendor_cuisinesmenu_categoriesproductsproduct_variationstopping_groupstoppingssync_log

Brands, cities, and cuisines are shared reference tables; vendors link to them; menu sections, products, and price SKUs hang off each vendor. Cuisines are stored twice on purpose — as a normalized junction for querying and as JSONB for fidelity.

Safe re-runs

Every entity upserts, so re-running the pipeline refreshes prices and availability rather than duplicating the dataset.

INSERT INTO vendors (...)
VALUES (...)
ON CONFLICT (code)
DO UPDATE SET ...;
Deep Dive 03

Built to run for hours

Ingesting a whole country takes hours, and anything that runs for hours will be interrupted. The pipeline treats failure as normal.

Resume checkpoints

Progress is saved every 50 vendors, so a crash costs seconds of re-work, not a fresh start on 29,000 restaurants.

Self-healing DB layer

A wrapper around PostgreSQL detects dropped connections mid-crawl and reconnects transparently — the long run keeps going.

Run accounting

A sync log records what succeeded, skipped, and failed each run, turning a marathon crawl into an auditable job.

29,115
Restaurants in the discovery set
150K+
Menu items ingested in logged runs
~0.85s
Between requests, rotating sessions
11
Normalized warehouse tables
AM

Arham Mirkar

Founder & Data Engineer, DataLayer

“I spent a while fighting the bot protection the hard way before I noticed the API only cared about coordinates when I gave it some. That's the lesson I keep relearning in data work — the elegant path usually beats the brute-force one, if you're patient enough to look for it.”

Under the Hood

Technology

LayerTechnologies
DiscoveryFoodpanda adventure-map sitemap, regex URL extraction → vendor code inventory
AccessReverse-engineered internal v5 vendor API, coordinate-free bypass, browser-faithful headers
EvasionRotating Perseus session IDs, 5-UA pool, session rotation every 50 requests, 0.85s pacing, 403 cooldown + retries
StoragePostgreSQL, 11 normalized tables, ON CONFLICT upserts, JSONB for cuisines / schedules / metadata
ResilienceResume checkpoints, auto-reconnecting DB wrapper, sync_log run accounting, profile-only mode
LanguagePython, requests, psycopg2 (no ORM)

Need a dataset that doesn't exist yet?

If the data is out there in some messy form, it can be discovered, structured, and served. That's the whole job.

Build a data pipelineMore case studies