How to Ingest Website Data into a Data Warehouse Without Duplicate Records

Website extraction is only the first half of an ingestion system. The harder half is ensuring that a retry, URL alias, redirected page, parser update, or repeated source row does not quietly multiply records in your warehouse.

The reliable approach is to model website ingestion as an idempotent, versioned pipeline. A rerun should lead to the same curated state, not another copy of every page. That requires more than a crawler setting: it requires explicit identities, an append-only raw layer, deterministic hashes, staging deduplication, and a merge whose source has one row per target key.

This guide describes a warehouse-friendly pattern for teams that need to ingest website data into a data warehouse without creating duplicate records.

Start with three identities, not one

Most duplicate bugs begin when one field is asked to represent several different concepts. A URL is not automatically a page identity, and a page identity is not a particular fetched version of that page.

Use separate identifiers for these roles:

Concept	Example	Why it exists
Discovered URL	HTTPS://docs.example.com/a/../guide?ref=nav	Preserves exactly what the crawler found. Useful for traceability and debugging.
Canonical URL	https://docs.example.com/guide	A normalized candidate identity for the logical page.
Page ID	sha256(site_id + canonical_url)	A deterministic warehouse key for one logical page within one site.
Content hash	sha256(normalized_extracted_content)	Identifies an observed content version of that page.
Crawl ID	sha256(ingestion_run_id + request_url + fetched_at)	Identifies one fetch observation in the raw layer.

URI syntax rules support normalizations such as lowercasing a scheme and host, handling percent encoding consistently, and removing dot segments. But URI normalization does not prove that two URLs refer to the same resource. Treat a canonical URL as a carefully defined candidate key, not universal proof of equivalence. Keep the originally discovered URL alongside it. RFC 3986

A simple default key design is:

page_id      = sha256(site_id + "|" + canonical_url)
content_hash = sha256(normalized_extracted_content)
version_id   = sha256(page_id + "|" + content_hash)

This separation gives the pipeline useful behavior:

• A repeated crawl of unchanged content has the same page_id and content_hash.
• A changed page has the same page_id but a new content_hash.
• Two pages that happen to contain identical text still remain distinct because their page_id values differ.
• A retry can be safely deduplicated using deterministic values rather than timing alone.

Define canonicalization as a documented policy

Canonicalization should be predictable, testable, and scoped to the site you are ingesting. Typical policies include resolving relative links to absolute URLs, normalizing host and scheme casing, removing dot segments, and deciding which query parameters matter to the business.

Be conservative with parameter removal. Removing utm_source may be appropriate for an analytics page dimension; removing id=123 probably is not. Likewise, do not collapse http and https, www and apex domains, or trailing-slash variants unless the site’s behavior supports that decision.

When URL aliases are common, retain an alias mapping rather than silently merging pages. A mapping can record:

observed_url -> canonical_url -> page_id

That preserves evidence when a later investigation shows that a normalization rule was too aggressive.

Keep every crawl observation in a raw layer

Do not send extracted rows directly to a curated pages table. First land the response in an append-only raw table or object store, then transform it downstream.

This follows the useful distinction between raw, source-faithful data and refined datasets. Databricks describes its bronze layer as raw and incrementally appended, with provenance that supports auditing and reprocessing. Databricks medallion architecture

For website ingestion, a raw_page_crawls table might contain:

create table raw_page_crawls (
  crawl_id string,
  ingestion_run_id string,
  site_id string,
  source_url string,
  canonical_url string,
  fetched_at timestamp,
  http_status integer,
  response_headers_json string,
  response_body string,
  raw_body_hash string,
  etag string,
  last_modified string,
  parser_version string,
  recorded_at timestamp
);

The raw layer may intentionally contain repeated fetches. That is not a failure. It is an observation log: two requests can legitimately produce two records, even if their bodies match. The duplicate-prevention requirement applies primarily to curated business tables, while the raw layer preserves what happened and enables reprocessing after parsing logic changes.

Store the parser version with each raw observation. If extraction rules change, you can re-run the transformation from retained source material and explain why a derived title, body, or hash differs from a prior run.

Detect change before spending work—but verify it with a hash

HTTP provides conditional retrieval mechanisms that can reduce needless transfers. ETag and Last-Modified describe a representation, while If-None-Match and If-Modified-Since let a client ask whether its known representation is still current. A 304 Not Modified response means a new response body is not needed. RFC 9110

A practical fetch decision looks like this:

1. Look up the most recent validator for the canonical URL.
2. Send conditional request headers when a prior ETag or modification time exists.
3. Record the resulting status and headers in the raw layer.
4. If a body is received, extract normalized content and calculate content_hash.
5. Let the warehouse-side hash comparison make the final decision about whether a new version exists.

Validators are useful optimization signals, not substitutes for data modeling. Publishers can change validator behavior, and representation metadata does not replace your requirement to make merges unambiguous. A content hash calculated from the normalized extracted fields is the final guard against treating unchanged content as a new version.

Choose the normalization boundary deliberately. If the analytics use case is article text and title, hash those fields rather than volatile markup, navigation, or timestamps. If parser behavior changes, either version the normalization contract or expect a controlled set of new content hashes.

Deduplicate in staging before you merge

A common failure mode is sending a batch with multiple rows for the same logical page directly into a MERGE. For example, a retry and a scheduled crawl may overlap; a source export may repeat rows; or one run may discover several aliases that map to the same canonical URL.

First, create a staging model that standardizes URLs, computes keys, and removes exact page-version repeats:

with prepared as (
  select
    site_id,
    canonical_url,
    sha256(concat(site_id, '|', canonical_url)) as page_id,
    sha256(normalized_extracted_content) as content_hash,
    fetched_at,
    ingestion_run_id,
    title,
    normalized_extracted_content,
    parser_version
  from raw_page_crawls
  where http_status = 200
),
latest_observation_per_version as (
  select *
  from prepared
  qualify row_number() over (
    partition by page_id, content_hash
    order by fetched_at desc, ingestion_run_id desc
  ) = 1
)
select * from latest_observation_per_version;

This result has one selected observation for each (page_id, content_hash) version. It is a good source for a historical page_versions table.

But it is not yet a valid source for a current-page table, because a single batch can still include several versions of the same page_id. Add a second reduction for the current representation:

with version_deduped as (
  select * from stg_page_crawls
),
latest_page_state as (
  select *
  from version_deduped
  qualify row_number() over (
    partition by page_id
    order by fetched_at desc, ingestion_run_id desc
  ) = 1
)
select * from latest_page_state;

That second check is essential. BigQuery documents that a MERGE can error when multiple source rows match a target row for an update or delete. Its MERGE operation is atomic, which makes it a strong building block once the source grain is correct. BigQuery DML syntax

Maintain a version table and a current table

A layered warehouse schema makes query behavior obvious:

raw_page_crawls     Immutable fetch observations, including bodies and headers
stg_page_crawls     Canonicalized, parsed, and deduplicated versions
page_versions       One row per (page_id, content_hash)
dim_pages_current   One row per page_id, representing the latest chosen version

Merge versions by the combined key:

merge into page_versions as target
using stg_page_crawls as source
on target.page_id = source.page_id
and target.content_hash = source.content_hash
when not matched then insert (
  page_id, content_hash, canonical_url, title, content,
  first_seen_at, parser_version
) values (
  source.page_id, source.content_hash, source.canonical_url, source.title,
  source.normalized_extracted_content, source.fetched_at, source.parser_version
);

Then merge the one-row-per-page current staging result by page_id:

merge into dim_pages_current as target
using stg_pages_current as source
on target.page_id = source.page_id
when matched and source.fetched_at >= target.fetched_at then update set
  canonical_url = source.canonical_url,
  content_hash = source.content_hash,
  title = source.title,
  content = source.normalized_extracted_content,
  fetched_at = source.fetched_at,
  ingestion_run_id = source.ingestion_run_id
when not matched then insert (
  page_id, canonical_url, content_hash, title, content, fetched_at, ingestion_run_id
) values (
  source.page_id, source.canonical_url, source.content_hash, source.title,
  source.normalized_extracted_content, source.fetched_at, source.ingestion_run_id
);

The same principle appears in incremental transformation tooling: a declared unique key establishes the model grain and allows existing rows to be updated instead of blindly appended. Duplicate keys in either incoming or target data are still a failure condition to address, not something an incremental model automatically repairs. dbt incremental models

Ready to validate the extraction side before you build the warehouse models? Start with a representative URL, inspect the fetched output and metadata, then wire the resulting observations into your raw layer. Create an account.

An honest PagePith demonstration

The supplied PagePith proof shows a request for https://www.rfc-editor.org/rfc/rfc3986.html completed at the fetch tier. The recorded content length is 150935, and the returned Markdown excerpt begins with the RFC’s Network Working Group header and the title Uniform Resource Identifier (URI): Generic Syntax.

That is useful evidence for the first boundary in this architecture: capturing a fetched document that can become a raw ingestion observation. The proof reports title: null, so it also illustrates why downstream schemas should allow nullable or independently extracted metadata rather than assuming every source will produce every field.

The proof does not establish crawl scheduling, canonicalization rules, conditional requests, warehouse connectivity, deduplication, or merge behavior. Those controls belong in the pipeline design described above and should be tested with your own target URLs, schemas, and warehouse.

Make retries safe with run-level metadata

Idempotency is the outcome of several controls working together:

• deterministic page_id and content_hash values;
• immutable raw observations retained by ingestion_run_id;
• a staging query that selects one row per intended merge key;
• a version merge keyed by (page_id, content_hash);
• a current-page merge keyed by page_id; and
• a clear rule for which observation wins when fetches arrive out of order.

Add run-level observability from the start. At minimum, record the ingestion run ID, crawl ID, source URL, canonical URL, page ID, content hash, fetch timestamp, HTTP status, validators, parser version, and record status. These fields turn a vague report of “duplicates appeared” into a queryable question: did the source repeat a page, did canonicalization change, did the parser alter content, or did the batch violate its expected grain?

Avoid treating streaming deduplication features as the primary correctness mechanism. For example, BigQuery characterizes insertId-based streaming deduplication as best effort and time-limited, and recommends manual processing when strict deduplication is needed. Use deterministic keys and staging logic for correctness instead. BigQuery streaming documentation

Pre-deployment checklist

Before operating the pipeline on a full site, verify these conditions:

1. Canonicalization tests exist. Include casing, dot segments, fragments, meaningful query parameters, and known aliases.
2. Raw data is retained. A parser update should not require recrawling every page to rebuild curated tables.
3. Version grain is explicit. Enforce one row per (page_id, content_hash) in page_versions.
4. Current grain is explicit. Enforce one row per page_id in dim_pages_current.
5. Merge sources are unique. Assert source uniqueness immediately before each merge.
6. Retries are tested. Run the same batch twice and confirm that curated row counts and keys do not change.
7. Out-of-order behavior is defined. Decide whether fetched_at, a source version, or another ordering field decides the current row.
8. Deletion is modeled separately. A missing URL in one crawl is not automatically a deletion; use an explicit status or confirmation policy.

Duplicate-free website ingestion is not about preventing every repeated fetch. It is about preserving observations while ensuring that each curated table has a precise grain and an unambiguous merge source. With that separation, retries become routine operations instead of a source of warehouse corruption.

Start validating your website ingestion workflow with PagePith.