Skip to main content

​
Overview

Stats.Warehouse is a SQL Server Database Project (.sqlproj) targeting Azure SQL (SqlAzureV12). It defines the complete schema — tables, stored procedures, user-defined functions, table-valued types, and views — for the NuGet download-statistics data warehouse. The warehouse follows a classic star schema design: a central fact table (Fact_Download) is surrounded by dimension tables for date, time, package, client, platform, and operation. A parallel star for NuGet CLI tool distributions (Fact_Dist_Download) mirrors the same pattern for tracking tool releases. Raw IIS/CDN log files are aggregated by external jobs and loaded into this warehouse via table-valued parameters passed to the Ensure* and StoreLogFileAggregates stored procedures. Reporting stored procedures then query the warehouse to produce the download reports surfaced on nuget.org.
This project contains no application code. It is a pure SQL Server Data Tools (SSDT) database project. The compiled output is a .dacpac that is deployed to the warehouse SQL server. All logic lives in T-SQL.

​
Role in the System

Upstream: Stats Pipeline

External stats-collection jobs (e.g., Stats.ImportAzureCdnStatistics, Stats.AggregateCdnDownloadsInGallery) parse CDN log files and call the Ensure* procedures and StoreLogFileAggregates to populate the warehouse.

Downstream: Download Reports

Reporting jobs call the DownloadReport* and SelectTotalDownloadCounts* procedures to generate the JSON download-report blobs that nuget.org reads for package download badges and statistics pages.

Downstream: Gallery Sync

GetDirtyPackageIds and the Cursors table form a cursor-based change-detection pattern used by gallery-sync jobs to identify packages whose download counts have changed and need to be pushed back to the Gallery database.

Downstream: Dist Reports

View_Dist_ReleaseDate and Fact_Dist_Download track NuGet CLI tool distribution downloads, feeding release-date and version-alias metadata to tooling dashboards.

​
Schema: Tables

​
Schema: Programmability

​
Key Stored Procedures

​
Functions

​
Views

​
Notable Patterns and Implementation Details

Upsert via Table-Valued Parameters. All Ensure* procedures accept a TVP (table-valued parameter), attempt a bulk insert of new rows using EXCEPT to exclude already-existing ones, and return the full set of surrogate IDs. This is the standard pattern for idempotent dimension loading across the warehouse.
Cursor-based incremental processing. The Cursors table holds named DATETIME positions. GetDirtyPackageIds reads the GetDirtyPackageId cursor, finds facts added since that position, and returns changed package IDs. UpdateDirtyPackageIdCursor advances the position after processing. This decouples the warehouse write path from the gallery sync read path.
Hardcoded download-count correction in SelectTotalDownloadCounts. The procedure subtracts exactly 21,000,000 from the raw sum to correct for historical over-counting. This magic number is not documented within the SQL and must not be changed without understanding the original data-quality issue it compensates for.
StoreLogFileAggregates uses a T-SQL cursor (row-by-row). The procedure iterates the TVP with a DECLARE CURSOR loop rather than a set-based merge, which limits throughput at high log-file volumes. This is a known implementation quirk.
Dimension_Client.ClientCategory and ClientVersion are persisted computed columns. ClientCategory is computed by dbo.GetClientCategory() and persisted at write time, making it indexable and report-ready without runtime function calls. The version triple (Major.Minor.Patch) is similarly persisted as ClientVersion.
Download reports exclude Crawlers, Unknowns, and future NuGet major versions. The DownloadReport* procedures filter out ClientCategory IN ('Crawler', 'Unknown') and also exclude NuGet clients with Major > 10 — a forward-looking guard against unrecognized future client versions inflating counts.
Snapshot isolation is enabled (AllowSnapshotIsolation = True in the project properties). Most reporting procedures use (NOLOCK) hints throughout for non-blocking reads, accepting the trade-off of potentially reading uncommitted data in exchange for query concurrency on the heavily-written fact tables.

​
Dependencies

This is a pure SSDT database project. It has no NuGet package dependencies and no internal project references.
The .sqlproj uses TargetFrameworkVersion v4.7.2 and ToolsVersion 4.0 purely as MSBuild scaffolding — there is no runtime .NET component. The project output is a .dacpac file.