Deduplicate Meta Lead Form Submissions

MediumSQL · PostgreSQL

Problem

Meta's lead ingestion pipeline for Facebook Lead Ads occasionally creates duplicate submission records for the same lead and form. Write a SQL query to identify the canonical record to keep for each duplicated lead and return only the duplicate rows that should be removed.

Requirements

Treat records as duplicates when they share the same lead_id and form_id.
Preserve the earliest valid submission by ranking rows using submitted_at ascending, then ingested_at ascending, then submission_id ascending.
Return only rows that should be deleted, along with campaign and advertiser context.
Exclude rows tied to inactive campaigns so cleanup only applies to active data.

Schema

lead_submissions

Column	Type	Description
submission_idPK	INT	Unique submission row ID
lead_id	VARCHAR(20)	Lead identifier from Meta lead capture
form_id	VARCHAR(20)	Lead form identifier
campaign_id	INT	Associated ad campaign
submitted_at	TIMESTAMP	Time the lead was submitted
ingested_at	TIMESTAMP	Time the row landed in the warehouse
email	VARCHAR(100)	Lead email address
phone	VARCHAR(20)	Lead phone number
source_surface	VARCHAR(30)	Meta surface where the lead originated

campaigns

Column	Type	Description
campaign_idPK	INT	Campaign identifier
advertiser_id	INT	Owning advertiser
campaign_name	VARCHAR(100)	Campaign name
status	VARCHAR(20)	Campaign status
start_date	DATE	Campaign start date

advertisers

Column	Type	Description
advertiser_idPK	INT	Advertiser identifier
advertiser_name	VARCHAR(100)	Advertiser name
vertical	VARCHAR(50)	Advertiser industry
region	VARCHAR(30)	Primary region

Tableslead_submissionscampaignsadvertisers