HERMES Table Schema#

HERMES publishes one interface for analysis, events_enriched, a view that carries every column of the table underneath it under clear, stable names. The underlying operational table is documented at the end of this page only as a reference for translating older queries.

What a row is#

A row is one NDT measurement that has an accompanying traceroute and belongs to a group HERMES analyzed on that date, carried together with everything HERMES computed around it. A group is the set of measurements from one access network (ASN), in one metro area, against one M-Lab site, over one IP version (see Grouping). Rows are written for every analyzed group, not only for groups that degraded; whether a group degraded is recorded in the performance.anomaly fields.

A row is not an event. A performance event is a property of a group — an access network, in a metro area, testing against one M-Lab site, on one day — and it is expressed across the many rows belonging to that group. To reason about events, aggregate by the group key:

GROUP BY partition_date, client.asn, client.metro, server.site, ip_version

Both tables are partitioned by partition_date. The field partition_date is the analysis date, not necessarily the date the underlying traceroute was measured: path measurements are drawn from a lookback window and attached to the day being analyzed. The field that records when the measurement itself was taken is measurement_time.

That lookback has a consequence for every count and per-measurement statistic. Each partition carries the measurements from the analysis date and the seven days before it, so one measurement typically appears in eight consecutive partitions, and only about one row in eight belongs to the analysis date itself. The group-level fields (performance.baseline.*, performance.anomaly.*) are computed from the right data and repeat on every row of the group. But COUNT(*), or a median of performance.ndt_rtt_ms, over a whole partition mixes a week of measurements. To work with the analysis date’s own measurements, add:

AND DATE(measurement_time) = partition_date

Do not use that filter to deduplicate across a range of dates without thinking it through: a measurement is attached to a date only if its group was analyzed that day, so the filter drops groups that were not analyzed on the day they were measured.

Field summary#

Every field in both tables, in one place. Dotted names are nested record fields, addressed in SQL exactly as written here. The sections after this one explain what the records are for and how to read them.

Fields of the events_enriched table#

performance.tests.mann_whitney.download and .upload carry the same fields as .rtt; performance.tests.wasserstein.upload carries the same fields as .download.

On client_to_server_path, loop_detected and unresponsive_within_as use the same definitions but describe the reverse path as measured, before HERMES removes ambiguous hops and truncates the path at the first AS re-entry. A loop therefore never appears in the published reverse hops, and these two flags are how you learn that one was measured. They are NULL for analysis dates processed before this definition was introduced.

client_to_server_path carries every field listed above for server_to_client_path, with direction = client_to_server and measurement_method = reverse_traceroute, plus the fields below. Its hops do not carry baseline_consistency_flag.

Fields of the events_with_as_and_geoloc table#

The 79 flat columns of the operational table, each with the view field that exposes it.

The hop records forward_updated_node_details and reverse_updated_node_details carry the pre-rename field names: addr, rtts, associated_asn, associated_org, associated_peeringdb_name, associated_ixp, place, cc, score, speed_of_internet_fiber, and facilities_info. The view’s equivalents are listed in the events_enriched table above.

How to read the events_enriched table#

The stable published interface. Field names here are a contract; the underlying table’s names are not. Every field is listed in the field summary above — this section explains what the six records are for and how to read them.

Because HERMES measures in both directions with two different tools, the path records name the direction they were actually measured in rather than borrowing traceroute’s “forward” and “reverse”. server_to_client_path is scamper, measured from the M-Lab server. client_to_server_path is reverse traceroute, measured back toward the server. Hop order and RTT vantage point are preserved as measured in both.

The client and server records#

The two endpoints of the measurement. Beyond location, client carries how the measurement was grouped: group_label names the group, grouping_granularity says whether that group was formed at city or metro level, and geo_source says which database placed the client. Those three are what let you tell measurements from the city and metro periods (see the changelog) apart, and they are the fields to group on when you span it.

server.geo_source is always server_metadata. M-Lab server locations come from M-Lab’s own records rather than IP geolocation, so they are not subject to the caveats that apply to the hop locations further down.

The performance record#

The measurement itself, the group’s baseline, and the distance between them.

The comparison that matters is between performance.* and performance.baseline.* — not between one group and another. Networks and locations differ enormously in what is normal for them, and HERMES is built on the change, not the level.

Read every baseline next to baseline.measurement_count and baseline.unique_client_ip_count. A baseline resting on the minimum admissible sample is a far weaker reference than one resting on thousands of measurements, and the row tells you which you have.

The anomaly record holds three different kinds of value. The *_significant fields are HERMES’s verdict: whether the group’s change passed the statistical tests and the size gate. The *_anomalous_sample_fraction fields say how much of the group was affected; the *_difference_* fields say how far the group’s median moved. A large difference with a small fraction usually means a few very bad measurements, while a large difference with a large fraction means the whole group moved. loss_ratio is, despite its name, a 0/1 verdict like the *_significant fields.

tests holds the results behind the verdicts: Mann-Whitney U for RTT, download, and upload; Welch’s t for RTT; and Wasserstein distance for download and upload. analysis_day holds the group’s RTT and download percentiles over every NDT measurement that day, which is the distribution the verdict was reached on. A median you compute over this view’s rows will differ from it, because rows exist only for measurements with a traceroute and include the lookback week.

Very large groups are not actually tested. When either the analysis-day or the baseline sample has more than 20,000 measurements, the test implementations return a p_value of 1e-10 and zeros in every other field, without running the test. Treat a p_value of exactly 1e-10 with a zero statistic as “not tested”, not as overwhelming evidence.

The path records#

Both records carry the same fields, distinguished by direction and measurement_method. client_to_server_path adds a revtr record describing the reverse traceroute measurement itself — which system produced the path, why it stopped, why it failed, and whether a vantage point in the client’s AS was attempted.

as_path, country_path, metro_path, and ixp_path answer most path questions without unnesting hops, and they are far cheaper to query. Reach for the hop array only when you need per-hop detail.

detour_ratio is distance_km / geodesic_distance_km: how much further traffic travelled than the straight line between endpoints. Always read it next to geolocation_coverage. A path where a third of hops could be geolocated still reports a distance, and that distance is a lower bound assembled from the hops that happened to be placed — not a measurement of the route.

The hops arrays#

One record per hop, ordered by TTL, carrying the hop’s address and RTT alongside everything HERMES inferred about it: its network, its organization and PeeringDB name, IXP membership, a location, and a reverse DNS name.

A geolocated hop is an inference, not an observation. geo_source, geo_score, geo_partition_date, and distance_rtt_check exist so you can judge how much to trust each one. distance_rtt_check is the most useful of these: it compares the measured RTT against the lowest RTT physically possible for the geolocated distance, and when the measurement beats physics, the geolocation is wrong rather than the physics.

Two things to watch when filtering. The per-hop flags — distance_rtt_check, above_baseline_flag, increasing_latency_flag, baseline_consistency_flag — are strings with fixed values, not booleans, so they need string comparisons. And geo_score uses -1 to mean private or unplaced rather than low confidence, so it should be excluded rather than averaged.

The reverse path’s hops carry three fields the forward path’s do not: revtr_hop_type, uses_interdomain_symmetry, and is_fishy_type_4. The middle one matters most — a hop inferred by assuming interdomain path symmetry is weaker evidence than one measured directly. They do not carry baseline_consistency_flag.

For how each annotation is produced and where it fails, see Traceroute enrichment.

The quality record#

Whether this row’s evidence should be trusted at all, and how much data stands behind the group.

A path that never reached the client, or reached only its AS, still carries useful information — but it cannot support a claim about the last mile. Filter on quality before drawing any conclusion about where a problem sits.

How to read the events_with_as_and_geoloc table#

The operational table underneath the view: 79 flat columns, written directly by the pipeline. The view exposes every one of them, so there is no need to query this table for analysis. This section exists to translate queries written against its original names.

Its column names are historical, and several of them describe the data inaccurately. The published view exists partly to correct that, so the mapping below is also the list of names worth being careful with.

What the view corrects, beyond renaming#

Four of these are not cosmetic. If you query the operational table directly, they are yours to handle.

forward_ and reverse_ do not describe the endpoints. Scamper’s “forward” path is measured from the M-Lab server toward the client; reverse traceroute’s “reverse” path is measured from the client toward the server. The view names each path by the direction it was actually measured in, and exposes direction and measurement_method on both, rather than reversing arrays and misrepresenting where the RTTs were taken.

speed_of_internet_fiber is not a speed. It is a calculated lower-bound round-trip time in milliseconds, derived from the hop’s distance at an assumed 200,000 km/s. The view exposes it as fiber_lower_bound_rtt_ms and records the assumed speed separately as propagation_speed_km_s.

Cumulative distance ran the wrong way on reverse paths. The operational table accumulated reverse-path distance in descending TTL order. The view recomputes it in measured order, so cumulative_distance_km increases along the exposed path for both directions, and adds segment_distance_km for the hop-to-hop step.

Grouping provenance is NULL on historical rows. Rows written before the provenance columns existed have NULL detection_granularity, src_group_label, and client_geo_source. The view projects those as city, the legacy city label, and maxmind — the known historical facts — rather than rewriting the physical table. Querying the operational table directly, you must handle the NULLs yourself.

Legacy name map#

Within the hop records:

ttl, rdns_name, latitude, longitude, metro, clli, geo_source, geo_partition_date, ixp_partition_date, cumulative_distance_km, distance_rtt_check, above_baseline_flag, increasing_latency_flag, baseline_consistency_flag, and is_fishy_type_4 keep their names. is_interdomain_symmetry and is_fishy_type_4 are written with a FALSE default, so they are never NULL.

The view also adds fields the operational table does not have at all: direction, measurement_method, segment_distance_km, geodesic_distance_km, detour_ratio, total_hop_count, responsive_hop_count, geolocated_hop_count, geolocation_coverage, as_path, country_path, metro_path, and ixp_path.

Changelog#

2026-09-30 — statistical tests corrected, and every group tested#

From analysis date 2026-09-30, detection uses corrected implementations of two of its three statistical tests:

• Welch’s t computed its p-value with an error that overestimated it by up to about 0.2 when the t statistic was roughly between 1 and 1.8.
• Mann-Whitney U applied its continuity correction in the wrong direction, which underestimated the p-value for small groups.
• Both skipped the test and reported a p_value of 1e-10 whenever either sample exceeded 20,000 measurements. Every group is now actually tested.

The size of the change, measured on 2026-08-04 by running detection both ways on identical inputs: about 5% fewer groups flagged for RTT (3,713 to 3,526) and about 3% fewer for download throughput (2,764 to 2,684). Almost all of the RTT change comes from small groups. Upload and loss verdicts are essentially unchanged.

Earlier analysis dates keep the verdicts and test results produced by the previous implementation. Treat 2026-09-30 as a methodological boundary, in the same way as the grouping change below: event counts shift there for methodological reasons, not because network performance changed.

The same date is the first on which client_to_server_path.loop_detected and client_to_server_path.unresponsive_within_as are populated. They are NULL before it.

2026-08-01 — client grouping and geolocation source changed#

From 2026-08-01, client grouping moved from city to metro granularity, and the client geolocation source moved from MaxMind to IPinfo. Both changed on the same date.

Dates added later by backfill were computed with the new method, so the published data has three periods by partition_date:

This affects any analysis spanning 2025-08-01 or 2026-08-01. Group counts and event counts shift at those boundaries for methodological reasons, not because the Internet changed. In events_enriched, client.grouping_granularity and client.geo_source tell you which regime a row belongs to; city-period rows are projected as city and maxmind, and have no client.metro. In the underlying table the corresponding columns are NULL for the city period.

If your query spans the boundary, group on client.group_label together with client.grouping_granularity rather than assuming one key applies throughout.

Expect detected event counts to roughly halve at the boundary, from about 2,900 to about 1,450 per day, because metro groups aggregate measurements that city groups split apart. That drop reflects the change in grouping, not a change in network performance.