Using dbt to Transform OpenSky Flight Data

Building an OpenSky Flight Data Pipeline with dbt Core

Recently, I built an OpenSky Flight Data Pipeline that ingests aircraft state vectors into PostgreSQL and transforms them into analytics-ready datasets using dbt Core. Along the way, I discovered that dbt isn't just about writing SQL, it brings software engineering practices like modularity, testing, documentation, and dependency management to analytics.

In this article, I'll walk through how I used dbt, the concepts I learned, and how I organized my project.

The Problem

The OpenSky Network provides aircraft state vectors containing information such as:

• ICAO24 identifier
• Callsign
• Origin country
• Latitude and longitude
• Speed
• Altitude
• Heading
• Vertical rate
• Flight status

Although the data is rich, it isn't immediately suitable for analytics. It contains inconsistent formatting, duplicate records, missing values, and measurements that need conversion.

Rather than writing one large SQL script, I wanted to build a modular transformation pipeline that was easy to maintain, test, and extend.

What is dbt?

dbt (Data Build Tool) is an open-source transformation framework that transforms data already stored in your data warehouse or database.

Unlike ingestion tools, dbt does not move data. Instead, it focuses on the Transform stage of the ELT workflow.

With dbt, you write SQL models while it handles the engineering around them, including:

• Running models in the correct order
• Managing dependencies
• Testing data quality
• Generating documentation
• Building lineage graphs
• Integrating with Git

Think of it as applying software engineering principles to SQL.

Why Not Just Use PostgreSQL?

You can absolutely build transformation tables manually using SQL.

The challenge comes when your project starts growing.

Questions quickly arise:

• Which table should run first?
• How do you avoid duplicating transformation logic?
• How do you document your models?
• How do you validate data quality?
• How do you understand model dependencies?

dbt solves these problems by introducing modular models, dependency management, testing, and documentation.

Project Architecture

My transformation pipeline follows a layered architecture.

bronze.all_state_vectors
            │
            ▼
stg_state_vectors
            │
            ▼
state_vectors
      ├───────────────┐
      │               │
      ▼               ▼
performance      location
      │               │
      ▼               ▼
status          latest
        │
        ▼
snapshot_summary
        │
        ▼
country_statistics

Every downstream model builds upon another model using ref(), allowing dbt to automatically determine the execution order.

---

Building the Silver Layer

1. stg_state_vectors

The staging model is responsible for cleaning the raw OpenSky data.

It performs tasks such as:

• Trimming text fields
• Removing duplicate rows
• Validating latitude and longitude values
• Standardizing country names
• Casting columns to appropriate data types

This model becomes the foundation for every downstream transformation.

2. state_vectors

After cleaning the raw data, I created an enriched model containing derived metrics such as:

• Speed in km/h
• Speed in knots
• Altitude in feet
• Flight status
• Aircraft movement classification
• Compass heading
• Snapshot date
• Snapshot hour

Rather than recalculating these metrics repeatedly across multiple queries, they're computed once and reused everywhere else.

Purpose-Built Models

Instead of creating one massive analytics table, I broke the transformations into smaller, focused models.

Model	Purpose
silver_aircraft_performance	Speed, altitude, climb rate, heading
silver_aircraft_location	Latitude, longitude, timestamps
silver_aircraft_status	Operational state and movement
silver_aircraft_latest	Latest snapshot for each aircraft
silver_snapshot_summary	Fleet-level metrics
country_statistics	Country-level aggregations
airborne_flights	Aircraft currently airborne
airport_surface_activity	Aircraft currently on the ground

Keeping each model focused makes them easier to understand, test, and maintain.

One of My Favourite Features: ref()

Instead of referencing tables directly like this:

SELECT *
FROM state_vectors;

dbt encourages referencing models using ref().

SELECT *
FROM {{ ref('state_vectors') }}

Using ref() has several advantages:

• Automatically creates model dependencies
• Executes models in the correct order
• Makes refactoring easier
• Builds lineage documentation automatically

As projects grow, this becomes incredibly valuable.

Working with Sources

The raw OpenSky table is defined as a source.

sources:
  - name: bronze
    schema: bronze

    tables:
      - name: all_state_vectors

Instead of hardcoding table names, models reference the source using:

SELECT *
FROM {{ source('bronze', 'all_state_vectors') }}

Using sources improves documentation, lineage, and maintainability.

Testing

One of the features I appreciated most was dbt's built-in testing.

For example:

columns:
  - name: icao24
    tests:
      - not_null

  - name: flight_status
    tests:
      - accepted_values:
          values:
            - Airborne
            - Ground

Running:

dbt test

automatically validates these rules and reports any failures.

Having tests alongside transformation logic makes it much easier to catch issues before downstream models are affected.

Documentation

Generating documentation is incredibly simple.

dbt docs generate
dbt docs serve

dbt creates an interactive documentation website containing:

• Models
• Sources
• Column descriptions
• Tests
• Lineage graph

One of my favourite moments during this project was watching the lineage graph grow as I added more models.

It provides a clear visual representation of how every transformation connects together.

Commands I Used

Throughout the project, these were the commands I used most frequently.

dbt debug
dbt compile
dbt run
dbt test
dbt docs generate
dbt docs serve

Each command serves a different purpose, from validating configuration to building documentation.

Lessons Learned

This project completely changed how I think about SQL transformations.

Instead of writing one massive SQL query, I built a collection of small, reusable models that are easier to understand, maintain, and test.

My biggest takeaways were:

• Build small, reusable models.
• Keep transformations modular.
• Document everything.
• Add tests early.
• Let dbt manage dependencies instead of doing it manually.
• Treat analytics projects like software engineering projects.

Repository

The complete project is available on GitHub:

https://github.com/jelimo-charity/opensky-flight-data-pipeline/tree/main/dbt

Feel free to explore the models, documentation, and project structure.

---

If you're already comfortable writing SQL and want to build more maintainable analytics pipelines, I highly recommend learning dbt.

Beyond simplifying transformations, dbt encourages practices that make data projects easier to scale, collaborate on, and maintain over time. It bridges the gap between traditional SQL development and modern data engineering by making transformations modular, testable, and well documented.

For me, this project wasn't just about transforming flight data—it was about learning a better way to build analytics pipelines.