INTERNSHIP / MARCH–APRIL 2026
SkyQuery.
Making flight data queryable, from PySpark analysis to a conversational API.
01 / Ownership
My work, within a two-person team.
The internship project combined analysis of 15 million historical Expedia flight records with a natural-language query interface. My responsibilities were backend and data engineering.
My contribution
- HDFS upload scripts, Hive table definitions and PySpark analyses.
- Flight connection comparisons, aircraft/runway analysis, and weather, brand and rail enrichment.
- The FastAPI query endpoint, schema context, SQL execution and correction loop.
My teammate’s contribution
Maryam handled frontend and data analysis: the chat interface, SQL display, results table, route map and charts, alongside pricing, network and other analytical tasks.
Team responsibilities and sprint allocation ↗ · My implementation directory ↗
02 / System design
Two workflows with different jobs.
The large-data analysis and the conversational query prototype are separate paths in the published implementation.
Historical analysis
- Store flight records in HDFS and define Hive tables.
- Use PySpark for joins, enrichment and route comparisons.
- Export findings for analysis and presentation.
Conversational prototype
- The frontend sends a question and selected tables to FastAPI.
- A language model generates SQL using a static schema description.
- The service checks the query prefix, executes against SQLite, and returns up to 100 rows.
The endpoint and some service names still refer to HiveQL, but the linked query-service revision uses SQLite. The 15-million-record dataset describes the analytics project; it is not a verified row count for the prototype’s SQLite database.
Hive table definitions ↗ · Schema context ↗ · Query execution ↗
03 / Technical decisions
Make failures visible and work bounded.
Give SQL generation schema context
The prompt includes table and column descriptions, and requests SQL in the execution engine’s dialect. This supplies concrete context, but does not prove that a generated answer is correct.
Limit the correction loop
For retryable execution failures, the next generation receives the previous query and error. The route makes at most three attempts in total. Validation failures return a client error instead of being retried. Successful responses include the generated SQL, columns, execution time and correction count.
Process connection comparisons by flight date
The PySpark splicing analysis filters nonstop flights for one date, caches that working set, joins possible legs, and releases the cache after collecting the result. Candidate connections must have a layover between 90 and 480 minutes.
04 / Recorded result
A concrete analysis, with its limits.
The recorded splicing run processed 95 flight dates and collected 950 comparison rows. One retained comparison for LGA → CLT → ATL showed a combined fare of $795.70 against a direct fare of $2,057.61: a difference of $1,261.91.
These are historical fare comparisons, not realized passenger savings or confirmed bookable itineraries. The script retains up to ten rows per date before its final sort, and the log contains repeated rows. The count is not 950 unique routes, and the largest retained difference is not a proven global optimum.
This produces an inspectable analytical result: a reader can follow the joins and connection constraints, then compare the output with the recorded run.
05 / Collaboration & delivery
An interface between two areas of ownership.
The repository records three Agile/Scrum sprints, with 14 stories assigned to me and 17 to Maryam. Our integration boundary is visible in the code: her frontend sends the question and table selection; my API returns the query and structured results used by the interface.
I presented the platform and analysis findings to the team. The source links below show the request and response contract that connected our two areas of work.
Frontend API integration ↗ · Request and response contract ↗
06 / What I would improve
Turn prototype boundaries into explicit guarantees.
- Query execution: use a read-only database connection, parsed statement validation and execution limits. The current SELECT/WITH prefix check alone is not a complete security boundary.
- Analysis quality: deduplicate candidates, rank before truncating, and align fare search snapshots before making stronger savings claims.
- Reproducibility: document the SQLite dataset and align endpoint names with the actual execution engine.
- Verification: add regression cases for invalid SQL, exhausted attempts and duplicate itinerary rows.
These are next steps, not features claimed as completed during the internship.
07 / Evidence boundaries
Separate dataset scale from query coverage.
The 15-million-record dataset size is reported by the project documentation. The saved splicing output samples up to ten qualifying rows per date before sorting, so the 950 findings are not an exhaustive cheapest-itinerary search. The published conversational executor uses SQLite and static schema descriptions; it does not demonstrate live Hive execution over that dataset.