Global broadband, one partition at a time

Ookla publishes Speedtest results for the world's fixed and mobile networks as open data: quarterly aggregates, one row per map tile, stored as Hive-partitioned Parquet in a public S3 bucket. It is laid out that way on purpose, because the engines people usually point at it (DuckDB, Amazon Athena, Apache Spark) all read partitioned Parquet from object storage.

I wanted to see how far a plain .NET console app gets on the same data. So I took one partition, type=mobile/year=2024/quarter=4, landed its 3,551,267 rows in a .dfc file, grouped them, sorted them, added the quarter before it, and wrote the answer back out as Parquet. Two NuGet packages, no warehouse, no cluster. Every figure and table on this page is the output of that run on 1.0.11, including the parts that surprised me.

What is actually in the bucket

The bucket is s3://ookla-open-data in us-west-2, you can read it without credentials, and it is listed on the AWS Registry of Open Data. The performance data sits under one prefix with three partition levels:

parquet/performance/type={mobile,fixed}/year=YYYY/quarter={1,2,3,4}/

Each leaf holds a single Parquet object. Here are the four mobile partitions for 2024 and one fixed partition, sizes measured with an HTTP HEAD:

That last column is the whole argument for partitioning. If all I care about is mobile in the fourth quarter of 2024, I read 195 MB, not every quarter Ookla has ever published. The partition values live in the key instead of inside the file, so picking them is string work I do before a single byte moves.

One thing to know before you write any code: there is no country column. The geography here is the tile itself, a 16-digit quadkey plus the longitude and latitude of its center. A country breakdown means joining in a boundary dataset, and I did not do that here. Everything below is answered out of the file as published.

Two packages and one file

dotnet add package Datafication.S3Connector
dotnet add package Datafication.Storage.Velocity

Datafication.S3Connector pulls in Datafication.Core and the CSV, JSON, Excel and Parquet connectors, because it works out the object's format from its extension and hands off to the matching reader. That is the design I wrote about in Seven connectors, one shape: the S3 connector is a composite, not a parser.

The load itself is a configuration object and one call.

using Datafication.Connectors.S3Connector;
using Datafication.Core.Data;
using Datafication.Storage.Velocity;

var config = new S3ConnectorConfiguration
{
    Region = "us-west-2",
    BucketName = "ookla-open-data",
    ObjectKey = "parquet/performance/type=mobile/year=2024/quarter=4/" +
                "2024-10-01_performance_mobile_tiles.parquet"
    // AccessKeyId and SecretAccessKey left null: anonymous read
};

var options = new VelocityOptions
{
    DefaultCompression = VelocityCompressionType.LZ4,
    AutoCompactionEnabled = false
};

using (var velocity = new VelocityDataBlock("ookla_mobile.dfc", options))
{
    var connector = new S3DataConnector(config);
    try
    {
        await connector.GetStorageDataAsync(velocity, batchSize: 50000);
    }
    finally
    {
        connector.Dispose();
    }

    await velocity.FlushAsync();
    Console.WriteLine($"{velocity.RowCount:N0} rows");
}

3,551,267 rows

GetStorageDataAsync is the streaming entry point every connector carries. It reads the object in batchSize chunks and appends each one to the storage target instead of building the whole table in memory first. GetDataAsync() is the other option and hands you a DataBlock. It works here, but it holds all 3.5 million rows at once, and one of those columns is a polygon in well-known text.

Info() on the result shows the schema the loader actually produced:

Twelve columns for an eleven-column file. RowGroup is the extra one. The Parquet connector adds it and records which Parquet row group each row came from, which is how you learn without reaching for another tool that this object has seven of them. Grouping on it is a decent first check that the whole object arrived. While you are looking at that table, note the null count on avg_lat_down_ms. That zero comes back later, and not in a good way.

Seventy-two segments, one file

A batch size of 50,000 over 3,551,267 rows is 72 batches, and each append writes a new segment file rather than rewriting the one before it. After FlushAsync() the directory holds ookla_mobile.dfc plus ookla_mobile.dfcs1 through .dfcs71, 262,654,748 bytes in total.

Seventy-two segments is exactly the case that was broken until eight days ago. Every DFC segment used to carry its own string table with IDs starting at 1, so the same ID meant a different string in a different segment, and GroupByAggregate over a string column returned nonsense the moment a file was split. That is the bug 1.0.11 fixed with a global string table, and every grouped result below leans on it.

Here is the first thing that surprised me. The DFC file is bigger than the Parquet it came from: 262,654,748 bytes against 195,139,957. That is the trade I signed up for. DFC carries a row-group index with per-column minimum, maximum and null counts, an internal row ID, and the tombstone and write-ahead sidecars that make the file updateable in place, which is the whole point of the format I described in Designing DFC. Parquet gets written once and never edited, so it pays for none of that.

Asking the file questions

Every query method on VelocityDataBlock builds up a plan and hands the block back. Nothing reads the file until you call Execute().

var v = await VelocityDataBlock.OpenAsync("ookla_mobile.dfc", options);

var means = v.Mean("avg_d_kbps", "avg_u_kbps", "avg_lat_ms").Execute();
v.ClearQueryPlan();

var totals = v.Sum("tests", "devices").Execute();
v.ClearQueryPlan();

var fast = v.Where("avg_d_kbps", 100000L, ComparisonOperator.GreaterThanOrEqual)
            .Count("quadkey")
            .Execute();
v.ClearQueryPlan();

Read that first row carefully. It is the mean over tiles, and every tile counts once whether it holds one test or nine thousand. Weight by tests instead and you get 124,475.13 kbps, which tells you the busier tiles skew faster. Neither number is wrong. They answer different questions, and the file will give you either one.

Ranked questions have the same shape. Sort followed by Head is the pattern the bounded-heap work in 1.0.10 was built for, and it reads about the way you would say it out loud:

var top = v.Where("tests", 1000L, ComparisonOperator.GreaterThanOrEqual)
           .Select("quadkey", "tile_x", "tile_y", "avg_d_kbps", "avg_u_kbps", "tests")
           .Sort(SortDirection.Descending, "avg_d_kbps")
           .Head(8)
           .Execute();

Only 242 tiles in the whole quarter carry a thousand tests or more. These are the fastest eight of them.

Where the speed is

A quadkey is a hierarchy: the first digit is one of four world quadrants, the first two are one of sixteen, and so on down to the full sixteen digits. So truncating the key is a geographic rollup, and it is the only one I get without a boundary dataset. Three digits gives 64 possible zones, of which this quarter uses 38.

Computed columns need one round trip, and this is where I got stuck for a few minutes. Compute adds the column to the materialized result, but a GroupByAggregate in the same plan cannot see a column that does not exist on disk yet. So project the columns you need, compute, save, and group the saved file:

using var projected = v.Select("quadkey", "avg_d_kbps", "avg_u_kbps", "tests").Execute();
v.ClearQueryPlan();

using var withZone = projected.Compute("zone", "LEFT(quadkey, 3)");
using var zones = await VelocityDataBlock.SaveAsync("ookla_zones.dfc", withZone, options);

var byZone = zones.GroupByAggregate("zone", new Dictionary<string, AggregationType>
             {
                 { "quadkey", AggregationType.Count },
                 { "avg_d_kbps", AggregationType.Mean },
                 { "tests", AggregationType.Sum }
             })
             .Sort(SortDirection.Descending, "count_quadkey")
             .Head(12)
             .Execute();

SaveAsync writes the five-column projection as one 85,576,198-byte file, a third of the twelve-column original. GroupByAggregate names its outputs after the aggregate, so the result columns are count_quadkey, avg_avg_d_kbps and sum_tests. Twelve zones out of 38 hold 92.4 percent of the tiles:

Those bounds are arithmetic, not annotation. A three-digit quadkey is a fixed box in the Web Mercator tile grid.

Tile counts and mean speeds point in opposite directions here. Zone 120 has the most tiles, 879,456, and averages 102.19 Mbps. Zone 030 has 106,828 and averages 213.48. Coverage and speed are not the same measurement, and one row per tile makes that hard to miss.

The shape of the distribution

The same two-step gets you a histogram. Bucket the download column into 25 Mbps bands, then count:

using var narrow = v.Select("quadkey", "avg_d_kbps").Execute();
v.ClearQueryPlan();

using var bucketed = narrow.Compute("d_bucket", "FLOOR(avg_d_kbps / 25000) * 25");
var histogram = bucketed.GroupByAggregate("d_bucket", "quadkey", AggregationType.Count)
                        .Sort(SortDirection.Ascending, "d_bucket");

FLOOR and arithmetic are both in the expression language, so the whole bucket is one string. The result has 161 rows, one per occupied band, and the highest is the band starting at 4,725 Mbps. The tail runs a long way past a gigabit.

Adding the quarter before it

A segmented format means the second partition costs an append, not a rebuild. Same connector, different key, same block:

var q3 = new S3ConnectorConfiguration
{
    Region = "us-west-2",
    BucketName = "ookla-open-data",
    ObjectKey = "parquet/performance/type=mobile/year=2024/quarter=3/" +
                "2024-07-01_performance_mobile_tiles.parquet"
};

var q3Connector = new S3DataConnector(q3);
try { await q3Connector.GetStorageDataAsync(v, batchSize: 50000); }
finally { q3Connector.Dispose(); }
await v.FlushAsync();

var stats = await v.GetStorageStatsAsync();

Two quarters is 7,324,925 rows and 148 segment files. CompactAsync() merges them back into one:

await v.CompactAsync();

Mean("avg_d_kbps") returns 111,820.71 kbps before the compaction and 111,820.71 after it, which is the assurance I was actually after.

Two things in that table are easy to misread, and I misread both of them the first time. CanCompact is false the whole way through, because it reports whether there are deleted rows to purge, not whether there are segments to merge. Nothing was deleted here, so the flag stays false while CompactAsync() still has 148 files of useful work in front of it. And the byte total barely moves, 1,277,997 bytes out of 541,902,226, because merging files reclaims per-file overhead and nothing else when no rows are dead. Compaction here buys you a single file to open, not a smaller one.

Handing the answer back

The result is a DataBlock like any other, so it goes out through the Parquet sink. The sink returns bytes and takes no path, on purpose: it does not assume the destination is a local disk.

using Datafication.Sinks.Connectors.ParquetConnector;

var bytes = await byZone.ParquetSinkAsync();
await File.WriteAllBytesAsync("zones.parquet", bytes);

Twelve rows of answer, 1,052 bytes, readable by whatever the rest of the team already uses. Three and a half million rows in, a kilobyte out.

What to watch for

Six things this run turned up that the documentation does not put in front of you.

Nulls arrive as zeros. The Parquet source has 89,345 nulls in avg_lat_down_ms and 52,757 in avg_lat_up_ms. After loading, Info() reports zero nulls in both, and exactly 89,345 rows hold the value 0. The source contains no genuine zero in that column, so the nulls became zeros on the way in. Check that a column is dense before you average it.

Computed columns are not groupable in the same plan. Compute puts the column on the materialized result, so GroupByAggregate("zone", ...) in the same chain fails with Group by column 'zone' does not exist. Execute first, or save and reopen, the way I did above.

S3DataConnector has a Dispose() but does not implement IDisposable. using var connector = new S3DataConnector(config); will not compile against 1.0.11. Use try and finally.

A prefix is not an object key. GetDataAsync() throws NotSupportedException when ObjectKey names a prefix. A whole year means ObjectKey = "parquet/performance/type=mobile/year=2024/", AllowMultipleSegments = true, and GetStorageDataAsync.

OpenAsync does not remember how the file was written. OpenAsync(path) with no options gets a fresh VelocityOptions, and anything you append afterwards is written with those defaults rather than the DefaultCompression the file was created with. Pass the same options object every time you open.

Dispose before you touch the files. Memory-mapped handles on Windows can hold a segment open past the last read, so a move or a delete can fail. Dispose the block and any cursors first, and retry with backoff.

The whole thing

Roughly forty lines, start to finish.

using Datafication.Connectors.S3Connector;
using Datafication.Core.Data;
using Datafication.Sinks.Connectors.ParquetConnector;
using Datafication.Storage.Velocity;

var options = new VelocityOptions
{
    DefaultCompression = VelocityCompressionType.LZ4,
    AutoCompactionEnabled = false
};

var config = new S3ConnectorConfiguration
{
    Region = "us-west-2",
    BucketName = "ookla-open-data",
    ObjectKey = "parquet/performance/type=mobile/year=2024/quarter=4/" +
                "2024-10-01_performance_mobile_tiles.parquet"
};

using (var velocity = new VelocityDataBlock("ookla_mobile.dfc", options))
{
    var connector = new S3DataConnector(config);
    try { await connector.GetStorageDataAsync(velocity, batchSize: 50000); }
    finally { connector.Dispose(); }
    await velocity.FlushAsync();
}

using var v = await VelocityDataBlock.OpenAsync("ookla_mobile.dfc", options);
using var projected = v.Select("quadkey", "avg_d_kbps", "avg_u_kbps", "tests").Execute();
v.ClearQueryPlan();

using var withZone = projected.Compute("zone", "LEFT(quadkey, 3)");
using var zones = await VelocityDataBlock.SaveAsync("ookla_zones.dfc", withZone, options);

var byZone = zones.GroupByAggregate("zone", new Dictionary<string, AggregationType>
             {
                 { "quadkey", AggregationType.Count },
                 { "avg_d_kbps", AggregationType.Mean },
                 { "tests", AggregationType.Sum }
             })
             .Sort(SortDirection.Descending, "count_quadkey")
             .Head(12)
             .Execute();

await File.WriteAllBytesAsync("zones.parquet", await byZone.ParquetSinkAsync());

Ookla publishes this data for engines that read partitioned Parquet out of object storage, and a console app with two package references is now one of them. The century of weather I put together before this said the same thing about CSV. What I like here is not that it works. It is that the whole pipeline, from a bucket I do not own to a file I can query and update, fits on one screen.

The dataset lives at registry.opendata.aws/speedtest-global-performance, and the packages are on NuGet. If you point this at a partition I did not, I would like to hear what you find in it.