Why the globe needed its own data pipeline

The GitHub homepage globe visualizes live open source activity, but the data behind it isn’t pulled from production databases. Querying GitHub’s operational stores directly would be far too expensive and risk impacting site performance. Instead, the team built a pipeline that aggregates events from the data warehouse, filters for interesting repositories, geocodes contributor locations, and serves the results back to the Rails app—all on a regular schedule.

Sourcing events from the warehouse, not production

GitHub’s data warehouse is populated on a schedule from production data, sanitized and packaged for analytical querying via Presto. For the globe, the warehouse queries target the Apache Kafka event stream, which arrives much more frequently than the daily snapshots of MySQL tables.

Every meaningful action on GitHub is recorded as an event, defined in protobuf format. A merged pull request event, for example, carries the pull request entity along with all of its attributes. That data lands in the warehouse for every merge. A query for merged pull requests from the past day looks like standard SQL against that event stream:

SELECT
  pull_request.created_at,
  pull_request.updated_at,
  pull_request.id,
  issue.number,
  repository.id
FROM kafka.github.pull_request_merge
WHERE
  day >= CAST((CURRENT_DATE - INTERVAL '1' DAY) AS VARCHAR)

Additional queries pull in the supporting data needed for the globe, but the core pattern is the same: filter recent events from the stream, then enrich them.

Ranking repositories by health, not popularity

Raw activity data isn’t enough—the homepage should showcase work that’s interesting and worth exploring. GitHub’s Data Science team built a model that scores repository “health” from 30-plus weighted features. A healthy repository isn’t simply one with many stars; the score also considers current activity levels and how approachable the project is for new contributors.

The model produces a numerical score queryable in the warehouse:

SELECT repository_id
FROM data_science.github.repository_health_scores
WHERE 
  score > 0.75

By joining that score with the event query, the pipeline can restrict results to merged pull requests from repositories above a certain health threshold:

WITH
healthy_repositories AS (
  SELECT repository_id
  FROM data_science.github.repository_health_scores
  WHERE 
    score > 0.75
)

SELECT
  a.pull_request.created_at,
  a.pull_request.updated_at,
  a.pull_request.id,
  a.issue.number,
  a.repository.id
FROM kafka.github.pull_request_merge a
JOIN healthy_repositories b
ON a.repository.id = b.repository_id
WHERE
  day >= CAST((CURRENT_DATE - INTERVAL '1' DAY) AS VARCHAR)

Accounts exhibiting spammy behavior are filtered out as well, but the health score is the primary gate for what appears on the globe.

Geocoding public profile text, not IPs

GitHub profiles have an optional free-text location field. Two-thirds of users leave it blank; for those who fill it in, the text may be a real city, a joke, or a fictional place. The team chose to geocode this user-provided text rather than infer location from IP addresses, so only data users explicitly made public is used.

The pipeline sends location strings to Mapbox’s forward geocoding API via their Ruby SDK. A query like “New York City” returns a large payload, but three fields matter:

result = Mapbox::Geocoder.geocode_forward("New York City", MAPBOX_OPTIONS)
result[0]["features"][0].slice("text", "relevance", "center")

=> {"text"=>"New York City", "relevance"=>1, "center"=>[-73.9808, 40.7648]}

Notably, Mapbox normalizes the input: querying “NYC” returns the same result, with text still set to “New York City.” The globe displays this normalized text so viewers see consistent place names regardless of how users typed them, and capitalization or misspellings are handled automatically.

The center field provides the longitude and latitude pair used for plotting. The relevance score indicates Mapbox’s confidence in the match. Free-text locations can be ambiguous, so the pipeline discards any result with a relevance score below 1, the maximum value.

Mapbox also offers a batch geocoding endpoint, which lets the pipeline resolve multiple locations in a single request:

MAPBOX_ENDPOINT = "mapbox.places-permanent"

query_string = "{San Francisco};{Berlin};{Dakar};{Tokyo};{Lima}"

Mapbox::Geocoder.geocode_forward(query_string, MAPBOX_OPTIONS, MAPBOX_ENDPOINT)

Once geocoding is complete, each featured activity is serialized as a compact JSON object for the globe’s JavaScript client. A pull request opened in San Francisco and merged in Tokyo, for example, is represented with short keys to minimize the payload size and speed up page loads:

{
   "uml":"Tokyo",
   "gm":{
      "lat":35.68,
      "lon":139.77
   },
   "uol":"San Francisco",
   "gop":{
      "lat":37.7648,
      "lon":-122.463
   },
   "l":"JavaScript",
   "nwo":"mdn/browser-compat-data",
   "pr":7937,
   "ma":"2020-12-17 04:00:48.000",
   "oa":"2020-12-16 10:02:31.000"
}

Scheduling the pipeline with Airflow

The geocoding and warehouse queries run multiple times a day so the globe stays fresh. Scheduling is handled by Apache Airflow, which runs workflows—called Direct Acyclical Graphs, or DAGs—as a sequence of discrete tasks. Each task completes before the next is scheduled, and tasks can pass data along the chain.

The globe DAG has four high-level steps:

  1. Query the data warehouse.
  2. Geocode locations from the results.
  3. Write the results to a file.
  4. Expose the file to the GitHub Rails app.

The first two steps are the query and geocoding logic described above. For file output, the DAG writes to HDFS, the distributed file system from Apache Hadoop. From there, the file is uploaded to Munger, an internal service that exposes data science results back to the Rails application powering github.com.

In the Airflow UI, each column represents one full run of the DAG. A column mid-execution shows the completed build_home_page_globe_table task in dark green and the scheduled write_to_hdfs task in dark blue. Since the Airflow instance runs many DAGs throughout the day, there may be a wait before the scheduler picks up the next task. A fully successful run shows every task green across the column.

The result is a repeatable pipeline that turns GitHub’s high-volume event stream into a small, current, and carefully filtered set of activities—ready to be rendered as arcs across a spinning globe.