LH005 source assessment

What each public record of PyPI downloads is, how each copy derives from the original, and what distorts it, read before and alongside the counts in LH005.md. Every statement names the page it came from and when that page was read (UTC, 26 September 2026). A statement this study inferred rather than read says so. The pages are numbered L1 to L9 here; the driver session added the four sources to the repository's sources.md after this assessment: L1 and L2 are S20, L4, L5 and L8 are S21, L3 is S22 and L7 is S23.

Pages read

Not read: the source code of linehaul, PyPI's log processor, and of pypistats.org and ClickPy. github.com answered 403 inside an established proxy tunnel for https://github.com/pypi/linehaul-cloud-function (17:14:23). raw.githubusercontent.com answered 200 for a linehaul file and for ClickPy's README during a status probe; neither body was read, and the README was deleted unread, because the driver's instruction was to report the refusal and use the documentation pages. Whether reading raw.githubusercontent.com would count as routing around the refusal is left to the driver. Everything below about how an installer fills a field is therefore from documentation or marked as inferred.

The original: BigQuery bigquery-public-data.pypi.file_downloads

• Unit. "Linehaul writes an entry in a bigquery-public-data.pypi.file_downloads table for each download. The table contains information about what file was downloaded and how it was downloaded" (L2). One row is one file download as PyPI's logs record it. Whether a row is a completed transfer or a request, and which responses count, is not stated in L1 or L2 (not checked).
• Population. Downloads of files hosted on PyPI. "This table is populated through the Linehaul project by streaming download logs from PyPI to BigQuery" (L1). The pages read do not name the CDN whose logs are streamed, or say whether any request class is excluded (not stated in L1 or L2).
• Fields. L2 lists timestamp, file.project, file.version, details.installer.name (examples: pip, bandersnatch) and details.python. The full column list is visible here only through ClickPy's staging table (L8, pypi_raw): timestamp, country_code, url, project, file (filename, project, version, type), installer (name, version), python, implementation, distro (name, version, id, libc), system (name, release), cpu, openssl_version, setuptools_version, rustc_version, tls_protocol, tls_cipher and ci (Nullable Bool). That is ClickPy's reading of the export, not PyPI's documentation of it.
• Retention and freshness. Streamed (L1); rows kept from 2016 per L2's examples and ClickPy's holdings. PyPI's own pages state no retention limit (not stated).
• Access. "you'll need a Google account and to enable the BigQuery API on a Google Cloud Platform project. You can run up to 1TB of queries per month using the BigQuery free tier without a credit card" (L2). Licensed "under the Creative Commons License" (L1; which licence is not named on the page).
• Coverage statements by the source itself (L2, "Background"): PyPI does not display download statistics partly because they are "Highly inaccurate", listing "pip's download cache (lowers download counts)", "Internal or unofficial mirrors (can both raise or lower download counts)", "Packages not hosted on PyPI", "Unofficial scripts or attempts at download count inflation (raises download counts)" and "Known historical data quality issues (lowers download counts)"; and a Linehaul bug made totals before 26 July 2018 "lower than actual by an order of magnitude".

Access decision (brief.md): not read directly in this study; no Google account, no credential.

Copy 1: ClickPy (ClickHouse), the copy used for every count by version, installer and CI flag

• Derivation, in ClickHouse's words (L4, January 2026). "Pypi download statistics are publicly available on a BigQuery table, and we have an automated BigQuery job that exports daily data to a Google Cloud Storage bucket." Ingestion now runs through ClickPipes into a staging table pypi_raw with "the Null engine ... We do not need to persist raw rows in ClickHouse. Instead, a materialized view performs the transformation and writes directly to the pypi table". The earlier pipeline (L5, August 2024) ran "a cron job that runs every hour" exporting new rows as Parquet. The two statements differ on cadence; L4 is the later.
• What the transformation keeps and drops (L4's view text and L8's table definitions, observation). The main table pypi.pypi (3,474,546,575,542 rows at 17:15:40) keeps per download: date (a Date, not the timestamp), country_code, project, type, installer (name only; installer version is dropped), python_minor (first two parts of the Python version), system name, version, libc, ci and filename. It drops url, implementation, distro except libc, cpu, openssl, setuptools, rustc and TLS fields. pypi_raw confirms as a Null-engine table with 0 rows: ClickPy holds no raw row.
• The CI flag. In the transformation, CAST(ifNull(ci, 0), 'Enum8(\'false\' = 0, \'true\' = 1, \'unknown\' = 2)') (L4): a download whose client reported no CI value is stored as false. So ClickPy's false means "not flagged as CI", not "known not CI" (observation of the code; the consequence is derived). Which installers report the field, and how they decide, was not read (linehaul and installer sources not read). No unknown row occurs for mcp from 17 January to 29 April 2026 (data/headline_figures.csv).
• Aggregate tables. Materialised views build daily sums per project and version, and by version and installer, file type, Python, system and country (L8). They are SummingMergeTree tables. L6: "If column is not in the primary key and is not summed, an arbitrary value is selected from the existing ones." pypi_downloads_per_day_by_version_by_installer_by_type is ordered by (project, version, date, installer), so its type column is outside the key: when rows for the same project, version, day and installer merge, one arbitrary file type survives (derived from L6 and L8). This study found one such case (LH005.md, Findings) and reads file types only from pypi_downloads_per_day_by_version_by_file_type, whose key includes type.
• Corrections to history. L4: "we noticed discrepancies between BigQuery (the source of data) and ClickHouse. When comparing daily row counts, some historical dates in ClickHouse were missing data", fixed by deleting and re-ingesting affected days. Which days, and whether any in 2026, is not stated. A figure read from ClickPy can therefore change after the read.
• Freshness. On 26 September at 17:18 UTC, the last day held for every cohort package was 2026-09-25 (data/clickpy_coverage.csv).
• Access. Anonymous SQL as user demo. Server limits observed at 17:17:23: at most 10,000 result rows, 1,000,000,000 rows or 50 GB read, 60 seconds per query. The Python Packaging guide names ClickPy as "a public application to visualize download statistics, with free direct SQL access to the underlying open-source ClickHouse database, updated daily" (L2).
• Oddity. The name mcp has downloads from 2016-01-22, 12,278 in all before 20 November 2024 (data/clickpy_mcp_before_current_project.csv), while PyPI's JSON API gives the current project's first file as 2024-11-20 (L9). ClickPy's copy of PyPI's metadata (pypi.projects) holds no row named mcp and one named MCP uploaded in 2012 (a query at 17:19:01). The earlier downloads are presumably of an older project under the same normalised name (inference, not checked); none falls in the periods read. Release times in this study come from L9, not from ClickPy.

Copy 2: pypistats.org

• Derivation. "Download stats are sourced from the Python Software Foundation's publicly available download stats on Google BigQuery" (L3, about). "The data update begins at 01:00:00 UTC and should take about 10 minutes" (L3, FAQs); "All download data is updated once daily" (L3, API).
• Retention. "PyPI Stats retains data for 180 days" (L3). On 26 September at 17:18 UTC, mcp's overall series ran 2026-03-29 to 2026-09-25, 181 days (observation). The first three days of LH002's window had already gone.
• Fields. Daily totals, with and without mirrors; daily series by Python major and minor version and by operating system. No version or installer endpoint: /api/packages/mcp/version and /installer returned 404 (17:18:35, 17:18:36). It cannot say which release was downloaded.
• Mirrors. "The cumulative download counts consider only the download records which are not from a known set of PyPI mirror applications, namely bandersnatch, z3c.pypimirror, Artifactory, and devpi" (L3, FAQs). This study found that, on every day both copies agree on the total, pypistats's "without mirrors" equals ClickPy's total less those four installers and less downloads with no installer name recorded (22 of 22 days; LH005.md, Findings). The page does not mention the second exclusion. That it comes from how the query treats a missing installer name is an inference; pypistats's source was not read.
• Its own coverage statements (L3, FAQs): mirrors "don't report subsequent downloads back to PyPI", so "One might expect that mirrors will mask end-user downloads for more commonly used packages while simultaneously inflating the download counts of less common ones"; "Downloads from CI/CD tools are included in all metrics. There is currently no easy way to attribute downloads to build/deployment tools."

Copy 3: pepy.tech

• Derivation. The site says "Total installs counted across PyPI mirrors, refreshed once a day" (L7). It does not say, on the pages read, that it draws on BigQuery (not stated). It calls downloads "installs"; this study does not.
• Access. The documented API is https://api.pepy.tech with an X-API-Key header (L7); without a key it answered 401 (17:18:41). The site's own path https://pepy.tech/api/v2/projects/mcp answered without a key (17:18:38), an undocumented route, used here once per package. It returns an all-time total and "a per-version daily breakdown for the last 3 months" (L7): 2026-06-27 to 2026-09-25 on the read. It does not reach LH002's window. Longer history and CI filtering ("downloads identified as coming from CI runners (GitHub Actions, GitLab CI, etc.) are excluded") need a paid key (L7); not used.
• Use here. Only as a cross-check on ClickPy's per-version daily counts over the 91 days it holds.

Distortions, and which copy can see each