2 August 2024
Introduction
In 2020, the aviation industry faced unprecedented challenges due to the pandemic, and WINGX was no exception. On a daily basis, we ingest 400M rows and scan 168B rows for processing. Our existing SQL server warehouse struggled to keep up with the increasing volume and frequency of data ingestions, leading to performance degradation and extended processing times. We needed a scalable, cost-effective, and efficient solution to optimize data processing, reduce time to market, and handle the growing complexity of onboarding new data suppliers. In the following blog, I’ll cover how we addressed these challenges and cut our data processing time by 70%.
The Challenge
Our existing SQL server warehouse was designed for pre-2020 data volumes and use cases.
With the pandemic, we saw a drastic increase in data volume and frequency of ingestions. Our major issues included the inability to process historical data in a timely fashion, handle concurrency efficiently at the source, identify chronological duplicates, and blend missing data bits. Our setup, which once worked perfectly, began to falter. Our key challenges included:
- Performance Degradation: Our views on views architecture became sluggish, especially with historical data sets. Pulling data from our BI layer started taking longer, increasing our time to market significantly.
- Increased Data Volume: We transitioned from monthly and weekly data ingestions to daily ingestions due to the pandemic, resulting in a significant increase in data volume.
- Scalability Issues: Onboarding new data suppliers was difficult. Our architecture, though well-designed for its initial purpose, couldn’t handle the increased data demands and concurrency.
- Cost and Safety Concerns: We wanted a solution that reduced our reliance on on-prem hardware and was cost-effective.
Initial Attempts
Initially, we upgraded our hardware, increasing RAM to 1 TB and adding 64 cores. While this helped temporarily, it wasn’t a scalable solution.
Exploring Solutions
We began exploring cloud-based solutions, specifically AWS tools like Glue, Athena, and S3. However, Athena connectors were too slow for our BI needs, and the costs associated with Postgres, Aurora, and Snowflake were prohibitive.
Discovering Firebolt
Our turning point came when we discovered Firebolt. We were intrigued by its promise of efficient data ingestion and processing, combined with cost-effectiveness. We decided to put it to the test with a proof of concept (POC).
The POC
For the POC, we provided Firebolt with 15 years’ worth of data stored in S3. We were dealing with millions of rows of data daily, transforming 7 to 11 columns into 170 columns through various joins and calculations. The key was to handle this efficiently without compromising on speed or cost.
We shared our view statements, which represented our processing logic, and asked them to transform raw tables into final tables efficiently. Our goals were clear:
- Historical Data Processing: How quickly could Firebolt process our historical data?
- Daily Data Processing: What would be the time and cost for daily data processing?
Results
The results were impressive.
Processing time: Firebolt reduced our processing time by approximately 70-77%. Our daily data processing, which previously took around 16 hours, was now completed in about 4 hours (2 hours on the Firebolt side and 2 hours on our semantic layer).
Query response time: We’re able to query millions of rows while only scanning a fraction of the data using indexes, bringing query response times down to milliseconds.
Moving to production
We implemented Firebolt in phases, starting with replicating our on-prem legacy setup on Firebolt. This gave us immediate, tangible benefits:
- Scalability: We could now scale horizontally and vertically, adjusting node sizes as needed.
- Cost Efficiency: Firebolt’s pricing model, based on engine uptime, allowed us to manage costs effectively.
- Operational Overhead: The onboarding process was simplified, enabling even less technically inclined team members to contribute quickly.
- Faster Time to Market: Our time to market reduced significantly, due to the simplicity of data ingestion and adding new models and data sources as opposed to our on-prem sql server platform. We are now able to onboard new datasets and suppliers in a week instead of months, without limitations.
- Automated Ingest Process: By using Firebolt, we could automate the data ingestion process, pulling data directly from S3 with simple SQL queries.
- Configurability: We could create production-grade databases within 2-3 hours, ensuring quick recovery and data availability.
The Architecture
Configurations: Dictate data usage, data points, and mappings.
Python: Translates configurations into SQL queries using SQLAlchemy.
Firebolt Python SDK: Sends queries to Firebolt, accessing external data on AWS.
Semantic Layer: Data is served to clients through dashboards and reports, deployed and scheduled using Prefect, running daily ingestions.
Use-cases
At WINGX, we provide detailed analytics to the aviation industry, helping clients with strategy, planning, and execution. Our key use-cases include:
- Aircraft Positioning and Market Analysis: Private jet companies optimize their aircraft positioning based on large datasets, interactively identifying peak seasons and demand trends to station aircraft strategically in a timely manner.
- Operational Insights for Fixed-Based Operators (FBOs): FBOs improve their ground services operations, such as passenger handling and aircraft servicing, using our real-time operational insights.
- Fuel Management: Fuel providers track flight numbers and fuel usage to manage inventory and forecast demand effectively and promptly.
- Custom Reporting and Data Cuts: We deliver tailored reports and data cuts, providing clients with actionable insights into flight operations and market trends. Firebolt’s ability to handle complex queries at high speed allows us to generate these reports quickly, ensuring our clients have access to the most up-to-date information.
Conclusion
Our journey with Firebolt has been transformative. From struggling with data volumes and performance issues to achieving scalable, efficient, and cost-effective data processing, Firebolt has proven to be the right solution for our needs. This experience underscores the importance of adapting to new technologies and continuously seeking better solutions to meet evolving data demands.
We hope our story provides valuable insights and inspires other engineering teams facing similar challenges.
