An employer writes a role once, and it is then advertised in many places, so a dataset gathered across job boards and career sites holds duplicate job postings until they are removed. This guide sets out why the same role appears many times, how the copies are removed, which keys identify one posting, and how to load daily postings into a warehouse table that stays free of duplicates, with the queries that prove it. It is written for the engineer or analyst who owns that table.
Why one role becomes many postings
An employer publishes a role from its applicant tracking system to its own careers page. From there the posting is repeated in three ways.
- Copies on other sites. Eurostat's Web Intelligence Hub collects online job advertisements from sources that include private job portals, public employment service portals and recruitment agencies. It flags duplicates through job descriptions, location and metadata, and merges the information from duplicates posted on several portals.
- Copies on one site. Google's guidance on job posting markup tells a site that hosts the same job posting at several URLs to declare a canonical URL on each copy.
- Repetition the employer chooses. The same guidance lets one posting carry several job locations, and the schema.org JobPosting type has a property for the number of positions open under one posting. Google's Cloud Talent Solution suggests splitting a job with more locations than it allows into several jobs, each with its own requisition ID.
The first two are duplicates to remove. The third is a modelling decision: the postings are real, and whether five postings are five roles depends on your question.
Copies also differ in how close they are: Eurostat's deduplication challenge for online job advertisements set out four kinds, each calling for a different test.
| Kind of duplicate | How the challenge defines it | The test that finds it |
|---|---|---|
| Full | The same job title and description | An exact match on normalised title and text |
| Semantic | The same position and job characteristics, expressed differently | Similarity of the description text |
| Temporal | A semantic duplicate retrieved on a different date | The same comparison across a window of days |
| Partial | The same position, without the same characteristics | A shared identifier, or a review by hand |
The three stages that remove duplicate postings across job boards and career sites
Duplicate job postings are removed in three stages. Exact identifiers come first: one posting ID or one canonical URL within a source is one posting. Normalised fields come second: employer, title and location are compared across sources inside a window of days. Text comes third: descriptions are compared for near-copies. Matched listings are then merged into one record.
The order runs from the most certain test to the least: an identifier needs no judgement, fields need a rule and text needs a threshold. The last two make both kinds of error, distinct openings merged and copies kept.
Two published methods show the stages at work.
- Lightcast's documentation describes two steps: each source is checked for advertisements new to it, then normalised fields including job title, company and location are compared across sources within a 60-day window. It counts total postings before deduplication and unique postings after.
- Google's Cloud Talent Solution refuses a new job with the same company, language code and requisition ID as an existing one, and its search can cluster highly similar jobs, judged by title, job category and location, showing one representative of each cluster higher up.
The window is part of what a source means by a posting: after it, the same advertisement counts as new. Ask any source for its length.
The deduplication algorithm a job aggregator runs on its listings
A deduplication algorithm for the listings of a job aggregator runs in five steps. It normalises every listing, groups listings that share an employer and a place into candidate sets, scores each pair in a set on title and description, links pairs above a threshold into clusters, and keeps one listing from each cluster as the record.
- Normalise. Put text in lower case, without accents or punctuation, and reduce words to their roots. The account of Eurostat's challenge lists these steps, with named entity recognition to find organisations and locations. Then resolve the employer's name to a company, which is entity resolution.
- Block. Compare only listings that share a company and a country: every pair in a set of 10,000 would be 49,995,000 comparisons.
- Score. A shared source identifier or canonical URL settles a pair. Otherwise compare titles and descriptions: the same account names MinHash, which estimates the similarity of two sets, and meaning-based embeddings compared by cosine similarity against a threshold.
- Cluster. Treat matched pairs as links and take the connected groups: when A matches B and B matches C, all three are one posting.
- Keep one. Keep the listing on the employer's own site where there is one, with the earliest first-seen date, the latest last-seen date and every source address.
Test the result by hand. Grade 100 clusters for distinct roles merged and 100 unmatched listings of one employer for copies kept, and report both rates: a threshold that lowers one raises the other.
Deduplication keys for job postings: canonical URL, title, company, location and syndication
No single key identifies a job posting across sites. A canonical URL names a preferred page and exists only where the publisher declares one. Title, company and location together catch syndicated copies, and they also merge distinct openings. The employer's own identifier for the job is exact, and it is found at the employer's source.
| Key | What it identifies | What it catches | Where it fails |
|---|---|---|---|
| The employer's identifier for the job | One posting on the employer's board | Every later read of that posting, edits included | A copy on another site can arrive without it |
| Canonical URL | The preferred address among pages with duplicative content | Copies of one posting under several addresses | A site that republishes the role can declare its own |
| Company, title and location | A candidate role | Syndicated copies on other sites | Merges two openings with one title in one office; splits on a reworded title or a recruiter's name |
| Hash of the description | One exact text | Full duplicates | Any rewording |
| Similarity of the description | Nearly identical texts | Semantic duplicates | Template text shared by different roles |
Google's markup guidance lists the identifier, which it describes as the one the hiring organisation gives the job, among its recommended properties, while title, hiring organisation, job location, date posted and description are listed as required. RFC 6596 defines the canonical link relation as naming the preferred address among resources with duplicative content, and allows the target to sit on a different host or domain.
Syndication is why the third row exists: an employer's board is offered as a feed, and each site that takes it publishes the role again at its own address.
Postings read at the company's own source arrive once
Fokals Job Postings is sourced first-party, at the employer's own careers page. A posting read there is the original, so it arrives once, and the matching across sites has no copies to work on. Each posting is one record with a stable posting ID, the address of the public posting and the company ID of the employer, as the data dictionary sets out. The note on reading job postings at the source covers attribution and dates, and the methodology states how postings are opened and closed.
Two properties of the record matter for a load. The posting ID is stable, so the table is merged on it. Repetition the employer chose is kept: one posting for each location of a role is delivered as published, each with its own posting ID.
What remains is the load. Job Postings describes a state: a file for a period holds one row for every posting open at any time in that period, so a posting open all week is in seven daily files. The hiring dataset page describes the product.
How to load daily job postings data into a data warehouse and deduplicate it
To load daily job postings data into a data warehouse without duplicates, land every file unchanged, reduce each batch to one row for each posting ID, and merge the batch into a current table on that key. A merge compares the batch with the table, so repeats inside the batch have to be removed first.
The engines document that last point themselves.
- Snowflake's reference for MERGE states that when the source holds duplicate values and no target row matches, every copy is inserted, so the target holds as many copies of the row as the source did. When several source rows would update one target row the result is nondeterministic, and by default the statement returns an error.
- Delta Lake's guide to deduplication with merge says the incoming data must itself be free of duplicates.
- dbt's guide to incremental models advises, when an incremental model runs into trouble, confirming that the unique key has one row for each value in the existing table and in the new rows alike, and warns that a null in its columns can leave rows unmatched and produce duplicates.
Five practices follow.
- Land first. Append every file as delivered to a landing table, with the time it was taken. It is the audit trail and the source of any rebuild.
- Take the key from the grain. The data dictionary gives one row for each posting, so the key is the posting ID alone.
- Rank inside the batch. Keep one row for each posting ID, from the file taken last.
- Let the later state win. A posting gains a close date, so a matched row is updated, and only by a file taken at or after the one that wrote it.
- Overlap on purpose. Read the last few days again on each run. A state that moved after your last pull then reaches the table, and the key absorbs the repeats.
The SQL is generic, and you will adapt the dialect. job_postings_landing and job_postings_current are tables of your own, with the columns of Job Postings (job_postings) plus exported_at: the time the file was taken, not the time it was loaded.
merge into job_postings_current t
using (
select *
from (
select l.*,
row_number() over (
partition by l.posting_id
order by l.exported_at desc
) as copy_rank
from job_postings_landing l
where l.exported_at > (
select coalesce(max(exported_at), timestamp '1900-01-01 00:00:00')
from job_postings_current
)
) ranked
where copy_rank = 1
) s
on t.posting_id = s.posting_id
when matched and s.exported_at >= t.exported_at then update set
-- every column that can change; these are the ones this guide uses
title = s.title,
locations = s.locations,
last_seen_at = s.last_seen_at,
closed_at = s.closed_at,
labels = s.labels,
exported_at = s.exported_at
when not matched then insert (
-- list every column you keep
posting_id, company_id, url, title, locations, country,
posted_at, first_seen_at, last_seen_at, closed_at,
found_on_first_read, labels, exported_at
) values (
s.posting_id, s.company_id, s.url, s.title, s.locations, s.country,
s.posted_at, s.first_seen_at, s.last_seen_at, s.closed_at,
s.found_on_first_read, s.labels, s.exported_at
);The inner query takes the landed rows newer than anything already merged and ranks the copies of each posting_id by exported_at. Only the first reaches the merge, so a target row meets at most one source row.
Incremental ingestion, deduplication and data modelling for daily job postings in a warehouse
Ingest incrementally and model daily job postings in three layers. Each run reads only what follows its bookmark into a landing table that only grows. A current table holds one row for each posting ID, merged from the landing table. A daily table of counts for each company is written once for each closed day.
| Layer | One row for | Key | Write rule |
|---|---|---|---|
| Landing | Each posting in each file | None: repeats are expected | Append only |
| Current postings | Each posting | Posting ID | Merge; the file taken last wins |
| Daily counts | Each company and closed UTC day | Company ID and day | Insert once; a repeat changes nothing |
The third layer is delivered. Hiring Activity (company_hiring_daily) holds each company's open, new and closed postings for every closed UTC day, written once after the day closes and never revised. Load it instead of counting landed rows, which the worked example shows to be far off.
Keep the first-read flag on every current row. It marks a posting already published when the company's board was first observed: leave it out of any count of new postings.
Ingestion has two routes through the same landing table and merge. The postings feed of the REST API runs oldest first from a time you set, so the last cursor is the bookmark for an incremental sync. The bulk exports return any dataset for any period. The guide to keeping a warehouse in step covers the bookmark and pacing.
A worked example: seven daily files for Acme Robotics
Acme Robotics is an invented company, and every figure below is illustrative.
Assumptions:
- One file is taken for each of seven days: the Job Postings rows of Acme Robotics, one row for every posting open at any time in the day.
- 40 postings are open when day 1 begins.
- 11 postings open: 3 on day 2, 5 on day 4, 2 on day 5 and 1 on day 7.
- 7 postings close: 4 on day 3, 1 on day 5 and 2 on day 6. Each is in the file of the day it closes, with its close date, and in no later file.
- The file of day 4 is landed twice in one batch, because the job was retried.
| Day | Open at start | Opened | Rows in the file | Closed |
|---|---|---|---|---|
| 1 | 40 | 0 | 40 | 0 |
| 2 | 40 | 3 | 43 | 0 |
| 3 | 43 | 0 | 43 | 4 |
| 4 | 39 | 5 | 44 | 0 |
| 5 | 44 | 2 | 46 | 1 |
| 6 | 45 | 0 | 45 | 2 |
| 7 | 43 | 1 | 44 | 0 |
The working:
- A file holds the postings open at the start of its day plus those opened during it, so the seven files hold 40 + 43 + 43 + 44 + 46 + 45 + 44 = 305 rows.
- The second copy of day 4 adds 44, so 349 rows are landed.
- The distinct postings are the 40 open at the start plus the 11 opened: 51.
- 349 less 51 leaves 298 landed rows that repeat a posting already held, 85% of what was landed.
- After the merge the current table holds 51 rows: 7 with a close date and 44 open, which is 40 plus 11 less 7.
- Landed rows with no close date number 349 less 7, or 342, against 44 open postings: close to eight times the true figure.
- On day 4 the batch holds 88 rows. Ranking leaves 44: 39 match postings in the table and 5 are inserted. Without it the 5 would be inserted twice, and each of the 39 would meet two source rows.
Three tests that prove the table holds no duplicates
Run these after every load, and stop the pipeline when one fails. The first returns every key that occurs more than once in the current table, and it must return no rows.
select posting_id, count(*) as copies
from job_postings_current
group by posting_id
having count(*) > 1;The second sets the landed rows beside the distinct keys and the current table. The last two figures must be equal: in the example the three are 349, 51 and 51.
select
(select count(*) from job_postings_landing) as rows_landed,
(select count(distinct posting_id) from job_postings_landing) as postings_landed,
(select count(*) from job_postings_current) as rows_current;The third is the merge itself, run a second time with no new file. It must insert no rows and leave the count where it was.
Frequently asked questions
What is job posting deduplication?
Job posting deduplication is the work of deciding which listings describe the same vacancy and keeping one record for each. It is needed wherever postings are collected from more than one site, because an employer's role is copied to job boards and advertised again. A sound process keeps the earliest first-seen date and the addresses of all the copies.
Is a canonical URL enough to deduplicate job postings?
No. A canonical URL names the preferred address among pages with duplicative content, and the publisher of each page declares it. It joins the copies one site holds under several addresses. A site that republishes the role is free to declare its own address, so matching across sites still needs the employer, the title, the location and the text.
Which key should a warehouse table of job postings be merged on?
Merge on the stable identifier the source gives each posting, and on no value derived from text. In Fokals Job Postings that is the posting ID, one for each posting on the employer's own board. A key built from title, company and location changes when the employer edits the title, and it collides when two openings share all three.
Is the same job posted in several locations a duplicate?
That depends on the question. An employer may publish one posting for each location of a role, and each is a real posting with an address of its own. Counted as demand for labour in each place, they are separate. Counted as roles, they are one. Keep the postings as published and derive the count of roles in a query.
Why does a merge still leave duplicate rows in the table?
A merge compares the incoming batch with the table, and it leaves the batch itself unexamined. When one batch holds a posting twice and the table holds no row for it, both copies are inserted. Reduce the batch to one row for each key before the merge, and check that the key holds no empty values.
How do you check a job postings table for duplicates?
Group the table by its key and return every key that occurs more than once: the result should be empty. Then compare the count of rows in the table with the count of distinct keys in the files you loaded. Last, run the load again on the same files and confirm that the count of rows stays the same.
The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.
What this page says about the products it names was checked against their public documentation on 7 October 2026. Product and company names are trademarks of their owners. Fokals is not affiliated with them or endorsed by them.