AeroTrack AI – Predictive Aircraft Maintenance
Comprehensive aviation logistics platform that forecasts maintenance needs using Fivetran, BigQuery, and Vertex AI — minimizing downtime, reducing costs, and improving fleet safety.
Visit websiteInspiration
The aviation industry loses billions annually to unscheduled maintenance and flight delays. Traditional maintenance either reacts after failures or follows rigid schedules that waste resources. We saw an opportunity to transform this paradigm: by leveraging Fivetran and Google Cloud’s AI suite, AeroTrack AI forecasts future maintenance needs so managers can schedule interventions before problems arise—reducing cost, minimizing downtime, and enhancing safety.
What It Does
AeroTrack AI is a comprehensive full‑stack solution for airline logistics managers, built on a complete, automated data pipeline that transforms raw flight telemetry into actionable maintenance schedules and a conversational interface. Managers get a unified fleet view, predictive maintenance planning, and real‑time insights from live BigQuery tables.
Core Pipeline
• Fivetran Connector: Continuously ingests live flight data into BigQuery
• Vertex AI Forecasts: Predicts future flight hours and maintenance windows
• Scheduled SQL: Automatically populates upcoming maintenance schedules
Manager Interfaces
• Web Dashboard (Next.js): Unified fleet view, real-time flight status, and predictive maintenance list
• Conversational AI (Gemini): Ask questions like “Which planes need an A-Check?” against live BigQuery data
How We Built It
AeroTrack AI is architected as a fully automated, end‑to‑end pipeline with sponsor technologies strategically implemented across ingestion, warehousing, AI, scheduling, and application layers.
Data Ingestion (Fivetran)
Custom Fivetran Connector (Python SDK) pulls live flight data from the OpenSky Network API and upserts it into BigQuery.
Warehousing & Transformation (BigQuery)
Raw data lands in live_flights. A Scheduled Query (sql/1_create_training_data.sql) aggregates it into a clean time‑series daily_flight_hours table.
Predictive AI (Vertex AI)
Vertex AI AutoML trains a time‑series forecasting model on daily_flight_hours. Batch Prediction jobs write results to predictions_* tables in BigQuery.
Scheduling Logic (BigQuery)
A second Scheduled Query (sql/2_populate_maintenance_schedule.sql) identifies the latest predictions and inserts tasks into maintenance_schedules using a robustINSERT ... WHERE NOT EXISTS pattern for demo reliability.
Application Layer (Next.js & Vertex AI)
Managers interact through a Next.js frontend embedding a Vertex AI Agent (Gemini) connected to a BigQuery Data Store. The agent generates SQL dynamically to answer natural language questions aboutlive_flights and maintenance_schedules in real time.
Challenges We Overcame
Our primary challenge was building a reliable scheduling query. The initial MERGE statement failed silently during testing—because it correctly determined no new maintenance tasks were needed given demo thresholds. We pivoted to a more robust, demo‑friendly INSERT ... WHERE NOT EXISTS approach, which proved significantly more stable. A classic real‑world data engineering challenge.
Accomplishments
We delivered a complete, end‑to‑end, production‑ready AI application within a compressed timeframe—from a custom Fivetran connector ingesting live data, to an AI model generating predictions, to a Gemini‑powered conversational agent querying those predictions. Watching the agent dynamically generate SQL and querymaintenance_schedules was a breakthrough moment for the team.
What’s Next
The roadmap focuses on data enrichment and model sophistication—adding Fivetran connectors for weather, flight schedules, and FAA maintenance directives. This expanded foundation will enable component‑level maintenance forecasting for engines, landing gear, and avionics based on operating conditions and environmental factors.
Built With
• Fivetran
• Gemini
• Google BigQuery
• Google Cloud
• Next.js
• OpenSky Network API
• Python
• React
• SQL
• TypeScript
• Vertex AI Agent Builder
• Vertex AI AutoML
• Vertex AI Batch Prediction
Try it out
flighttracker-5loh.vercel.app
GitHub Repo