Partitioning Scraped Datasets by Crawl Date #

Partitioning is usually introduced as a query optimisation, and for a scraped dataset that undersells it. The partition key determines whether a retention sweep is a metadata operation or a full scan, whether a reprocessing run rewrites one day or the whole history, and whether a compaction job can safely run at all. This guide covers choosing and implementing the scheme, as part of structured data sinks and warehousing in the Pipeline Storage, Deduplication & Monitoring section.

Problem Framing #

Three operations dominate the life of a scraped dataset, and all three are cheap or expensive depending on the partition key.

Crawl date is the axis along which the data becomes immutable.

Retention expiry. Deleting everything older than 180 days is a partition drop when the data is partitioned by date, and a full-table delete with an index scan when it is not. On a large dataset that is the difference between seconds and hours, and between a job that runs nightly and one that quietly stops being run.

Reprocessing. Fixing an extractor bug means recomputing the records produced by the affected build. Partitioned by crawl date, that is a rewrite of a few partitions; partitioned by source domain, it is a rewrite of everything.

Compaction. Small files accumulate as batches land. Compaction rewrites them into larger ones, and it can only run safely on partitions nobody is writing to — which requires a partition boundary that closes.

Crawl date satisfies all three, because it is the axis along which the data becomes immutable. Yesterday’s crawl will not gain new rows tomorrow, which makes yesterday’s partition safe to compact, safe to expire, and cheap to reprocess.

Step-by-Step Implementation #

1. Partition by crawl date, not by content date #

Readers only ever see a published path, so a partial write is invisible.

The distinction matters more than it appears. A listing published in 2019 and crawled today belongs in today’s crawl partition, not in a 2019 partition. Partitioning by content date produces unbounded partition growth — every crawl writes to hundreds of historical partitions — and destroys the immutability property that compaction and expiry depend on.

from datetime import datetime, timezone

def partition_values(record: dict) -> dict:
    """Crawl date is the partition axis; content date is an ordinary column."""
    fetched = datetime.fromisoformat(record["fetched_at"]).astimezone(timezone.utc)
    return {"crawl_date": fetched.date().isoformat()}

Always derive the partition from a UTC timestamp. A local-time partition shifts its boundary twice a year, producing two days that are not 24 hours long and a set of downstream queries that quietly miss rows.

2. Keep the cardinality sane #

A second partition level is occasionally justified — usually source host — but only when the number of distinct values is small and stable. Partitioning by a high-cardinality field produces thousands of tiny partitions, and the metadata overhead exceeds the data.

MAX_SECOND_LEVEL = 50

def partition_spec(record: dict, tracked_hosts: frozenset[str]) -> dict:
    """Second level collapses infrequent sources so partition count stays bounded."""
    values = partition_values(record)
    host = record["host"]
    values["source"] = host if host in tracked_hosts else "other"
    return values

The same collapsing of infrequent values that keeps metric label cardinality bounded applies here, for the same reason: an unbounded key produces an unbounded number of objects, and the system degrades on the count rather than on the volume.

A useful rule of thumb: aim for partitions in the hundreds of megabytes. Partitions much smaller than that mean the scheme is too fine; ones much larger mean it is too coarse and query pruning is not helping.

3. Write to a staging path, publish atomically #

A reader that lists a partition directory while a write is in progress sees a partial dataset. Writing under a temporary prefix and moving on completion means the published path only ever contains complete data.

import pyarrow as pa
import pyarrow.parquet as pq
import pyarrow.fs as fs

def write_partition(table: pa.Table, root: str, values: dict, batch_id: str) -> str:
    partition_path = "/".join(f"{k}={v}" for k, v in values.items())
    staging = f"{root}/_staging/{batch_id}/{partition_path}"
    published = f"{root}/{partition_path}"

    pq.write_to_dataset(table, root_path=staging, compression="zstd",
                        existing_data_behavior="overwrite_or_ignore")

    filesystem = fs.LocalFileSystem()
    filesystem.create_dir(published, recursive=True)
    for info in filesystem.get_file_info(fs.FileSelector(staging, recursive=True)):
        if info.type == fs.FileType.File:
            filesystem.move(info.path, f"{published}/{batch_id}-{info.base_name}")
    filesystem.delete_dir(f"{root}/_staging/{batch_id}")
    return published

Prefixing published filenames with the batch identifier makes a batch removable later — a reprocessing run can delete exactly the files a bad build produced, without touching anything else in the partition.

4. Compact closed partitions only #

from datetime import date, timedelta

TARGET_FILE_BYTES = 256 * 1024 * 1024
COMPACT_AFTER_DAYS = 2                   # give late-arriving batches time to land

def compactable_partitions(root: str, today: date) -> list[str]:
    cutoff = today - timedelta(days=COMPACT_AFTER_DAYS)
    return [p for p in list_partitions(root)
            if date.fromisoformat(p["crawl_date"]) <= cutoff
            and p["file_count"] > 1
            and p["total_bytes"] / p["file_count"] < TARGET_FILE_BYTES / 4]

def compact(root: str, partition: dict) -> None:
    table = pq.read_table(partition["path"])
    staging = f"{root}/_compact/{partition['crawl_date']}"
    pq.write_table(table, f"{staging}/part-0.parquet", compression="zstd",
                   row_group_size=1_000_000)
    swap_directory(staging, partition["path"])       # atomic replace

The two-day delay before compacting is the important detail. A crawl that runs past midnight, a retry that lands the next morning, or a backfill can all write into a partition after its date has passed, and compacting immediately means those writes land in a directory that is being rewritten.

5. Expire by dropping partitions #

def expire_partitions(root: str, retention_days: int, today: date) -> list[str]:
    cutoff = today - timedelta(days=retention_days)
    dropped = []
    for partition in list_partitions(root):
        if date.fromisoformat(partition["crawl_date"]) < cutoff:
            delete_directory(partition["path"])
            dropped.append(partition["crawl_date"])
    audit.write(event="partition_expiry", root=root, dropped=dropped,
                cutoff=cutoff.isoformat())
    return dropped

This is the payoff. Expiry becomes a directory removal and an audit entry rather than a scan, which is what makes the retention control cheap enough to run reliably — the property the retention sweep depends on.

Note what partition expiry does not handle: an erasure request for a specific individual, which needs a targeted deletion inside surviving partitions. Partitioning makes the bulk case cheap and leaves the targeted case exactly as it was.

Verification & Testing #

A local-time boundary produces two malformed days every year.

def test_partition_uses_crawl_date_not_content_date():
    record = {"fetched_at": "2026-06-14T23:30:00+00:00", "published_at": "2019-01-02"}
    assert partition_values(record) == {"crawl_date": "2026-06-14"}

def test_partition_boundary_is_utc():
    record = {"fetched_at": "2026-06-14T23:30:00-05:00"}      # 04:30 UTC on the 15th
    assert partition_values(record) == {"crawl_date": "2026-06-15"}

def test_reader_never_sees_a_partial_partition(root, reader):
    with concurrent_write(root, rows=100_000):
        rows = reader.count(root)
        assert rows in (0, 100_000)                # never a partial count

def test_expiry_drops_only_old_partitions(root):
    seed_partitions(root, dates=["2026-01-01", "2026-06-01", "2026-06-14"])
    dropped = expire_partitions(root, retention_days=30, today=date(2026, 6, 15))
    assert dropped == ["2026-01-01"]

Monitor two numbers: files per partition and bytes per partition. Rising file counts mean compaction is not keeping up; shrinking partition sizes mean the scheme has become too fine — often because a second-level key gained values.

Compliance & Operational Guardrails #

Partition expiry is driven by the retention class recorded in the registry, not by a constant.
Every expiry writes an audit record naming the partitions dropped and the cutoff applied.
Targeted erasure operates inside surviving partitions; partition drops do not substitute for it.
Compaction runs only on closed partitions, and never on one currently receiving writes.
Published paths contain only complete data; partial writes live under a staging prefix.

Common Mistakes #

Partitioning by content date. Every crawl writes to hundreds of historical partitions and nothing is ever immutable.
Partitioning by a high-cardinality key. Partition count explodes and metadata overhead dominates.
Compacting the current partition. Concurrent writes are lost or duplicated.
Local-time partition boundaries. Daylight-saving transitions produce two malformed days a year.
Assuming partition drops satisfy erasure. They handle bulk expiry only; a subject’s records inside live partitions still need targeted deletion.

Frequently Asked Questions #

Daily, hourly, or monthly partitions? #

Daily suits most crawls. Hourly is justified when a single day’s output exceeds a few gigabytes or when reprocessing needs finer granularity; monthly when the crawl is small and daily partitions would each be a few megabytes. Size the choice so partitions land in the hundreds of megabytes.

How does this interact with upserts? #

Awkwardly, which is why the columnar dataset should be a projection rather than the source of truth. Keep the transactional store authoritative and regenerate affected partitions from it, rather than trying to update rows in place inside a columnar file. That division is the arrangement described in structured data sinks and warehousing.

Should the partition column also be stored in the row data? #

No — it is encoded in the path, and duplicating it wastes space and creates a second source of truth that can disagree after a move. Most readers reconstruct partition columns from the path automatically. Where a reader does not, derive it once at read time rather than storing it twice.

How should a backfill interact with the partition scheme? #

Write backfilled records into the crawl-date partition of the crawl that produced them, not the one they logically belong to. A backfill run today produces today’s partition, with the content date preserved as an ordinary column. That keeps partitions immutable after they close, which is the property compaction and expiry both depend on, and it keeps the backfill traceable — the batch identifier in the filenames says exactly which run produced which rows.

What if queries mostly filter on content date rather than crawl date? #

Add a sort or clustering key on the content date within each partition rather than changing the partition axis. Columnar formats prune on row-group statistics, so ordering rows by content date within the file gives most of the benefit of partitioning on it without any of the immutability cost. Partition for the write and lifecycle patterns; cluster for the read patterns.

Structured Data Sinks and Warehousing — sink selection and idempotent writes.
Writing Scraped Data to Parquet With PyArrow — the file format these partitions contain.
Enforcing Retention Windows With Scheduled Jobs — the sweep that partition drops make cheap.