MAJOR ASSIGNMENT: TASK 2
INTRODUCTION
This is the final part of the major practical assignment of DATA3404 analysing the air traffic data set. You will
find links to the supplied data and starting SQL and Spark code in the ‘Major Assignment’ section in Canvas
modules.
STARTING SCENARIO
The questions you will be analysing have been changed to limit the amount of work required for this task while
still focusing on the optimisation analysis you need to perform. These questions have been completed in a SQL
form, and a Spark form. The work that has been done for both of these approaches has been fairly
questionable – the output is correct, but there appears to be inefficiency in the way the queries are
constructed. Your task here is to identify where those inefficiencies might be, and to correct them.
DATASETS
The datasets have been modified – the supplied SQL file will allow you to import the tweaked datasets into
PostgreSQL, and the supplied Task 2 Bootstrap file will obtain the same datasets in their raw CSV format. The
primary difference is in data cleaning – to avoid the need to redo the work you have already shown you can
perform in Task 1, these source files have been cleaned ahead of time.
QUESTIONS
QUESTION 1
Determine the airport that is flown to (destination) most often. Of the airlines who have flights recorded to
that airport, which does so using the largest number of different aircraft.
QUESTION 2
Determine the origin airport which is the most common origin airport for each airline. For these pairs of most
common origin airport <-> airline, identify the airline whose most common origin airport represents the
highest % of total flights for that airline.
ALTERNATE VERSIONS
Your task is to modify these queries such that they are more efficient. You should do this using a systematic
process to help you write your report:
• Analyse the current operation of these queries using the techniques covered for SQL and Spark
• Identify something that can be improved, and change the query to support that and thus to be more
efficient
• Ensure the output is unchanged - and that you anticipate it would be unchanged for any valid inputs,
i.e. do not rely on the specific data you have available for testing.
• If you are unable to identify an improvement, you should write something that is equivalent in output
but involves a different execution.
• Observe an improvement both from the SQL execution plan, the Spark DAG, and the SparkMeasure
analysis.
• You may wish to use the larger flights datasets to check this, particularly the very large dataset – this
will make it clear whether there is a speed improvement. Note that these versions have not been
cleaned as the flights_small_cleaned dataset has. To achieve similar results, dropna should be applied
to the dataframe when it is loaded, the column names should be corrected, and inner joins should be
used when connecting with airline/airport/aircraft data.
• In the event that any bugs in the provided queries are discovered, they should be treated as working –
that is, your modification should retain any bugged behaviour rather than try to correct it to the
stated question.
• The cache clearing in the Spark code should be retained – do not use caching that requires the queries
to be run multiple times to achieve efficiency. The efficiency improvement should come from the
query being run for the first time. Caching occurring within the query itself is fine.
Note that each question should be approached by the group as a whole. Each re-written version should have
the authors who contributed to that version indicated in a comment.
Note also that there will be quite a lot that you might identify and change – you do not need to optimise these
queries to be perfectly efficient! All you need to do is to identify one aspect of inefficiency in each question
and correct it. However, there are a couple of things that are the same for both questions, particularly with
Spark – you will need to use a different approach for the two rather than cutting and pasting an improvement
that applies to both questions for your improvement to count.
WHAT TO SUBMIT
In the ‘report’ submission box you should submit a pdf report with the following information:
• Your group name, including tutorial code and group number
• An analysis for each question using the following headings:
o SQL
▪ Initial State
Describe the execution of the query as provided. Write a relational algebra
expression tree which describes this query. Use the execution plan from PostgreSQL
to support your analysis of what the query is doing.
▪ Alternative Version
Describe what your alternative version has changed. Indicate this by comparing the
different relational algebra expression trees and execution plans between the
versions.
o Spark
▪ Initial State
Describe the execution of the query as provided. Use the DAG output from
Databricks/Spark as well as the SparkMeasure output to support your analysis of
what this query is doing. You may wish to break down the notebook further into
different cells to achieve this – be careful that in doing so you don’t change the way
the query operates.
▪ Alternative Version
Describe what your alternative version has changed. Indicate this by comparing the
DAG and SparkMeasure output between the versions.
▪ Optional for higher marks: A discussion on how the original and alternative versions
of the queries would perform if being executed on a multiple-node cluster. Identify
any issues to do with parallelism/distributed joins that might impact either version
differently, and discuss whether you believe your alternative version would still be
an improvement with more data on a highly parallel cluster (e.g. the very large
dataset executed on 100+ nodes). This will be speculative, but you should have
enough knowledge about the way distributed joins work to identify some key issues.
o There is no need to include the query output itself as this should not change from what is
provided.
• A group contribution statement with the following headings:
o Your group members’ names and SIDs
o Which alternative versions they contributed to
o Whether they contributed meaningfully to the analysis and report (yes or no)
• This does not have a strict page limit, but you should keep it relevant to ensure feedback can be
useful. In particular:
o Don’t include large code dumps. You are already submitting all of your code. Use 1 or 2 line
snippets if you absolutely must. Remember that including your code is not explaining your
code.
o Don’t keep writing more in the hope that some of what you include will be correct. You are
more likely to run into issues including incorrect information than you are to gain by
including correct information.
In the ‘code’ submission box you should submit a zip folder with the following contents:
• A single .SQL file that contains your alternative versions. Use /*comments*/ to indicate where each
question starts and ends, and the name and SID of the group members who contributed to that
question.
This .SQL file must be able to be run against a Postgres installation that has had the datasets imported
with the provided schema
• A single Jupyter notebook that contains all of your completed questions. Use markdown cells to
indicate the same information as for the SQL file.
This notebook must be able to be run attached to a Databricks Community cluster that has had the
Assignment Bootstrap Task 2 notebook run on it, with SparkMeasure installed, and no other
configurations made.

MARKING
The marking guide is broken up into rubric items, that follow the EMRN pattern described in the first lecture.
• SQL Q1+Q2 (2% + 2%)
o E: A valid alternative version has been written that has a different execution. The SQL
statements are clean, idiomatic, and elegant.
o M: A valid alternative version has been written with a different execution.
o R: The alternative version gives invalid output or does not have a different execution plan,
but the statement is close enough that some direct feedback can be given for a corrected
revision to be made
o N: The alternative version is incorrect or has the same execution, and the statement is not
sufficiently close to correct for more directed feedback than ‘see the course material’, or is
missing.
• SQL Q1+Q2 Improvement (0.5% + 0.5%)
o This is a simple yes or no tied to a result of E or M for SQL Q1 and Q2. If the valid alternative
version represents a tangible improvement in efficiency, this mark is awarded.
• Spark Q1+Q2 (2% + 2%)
o E: A valid alternative version has been written that has a different execution. The Spark code
is clean, idiomatic, and elegant.
o M: A valid alternative version has been written with a different execution.
o R: The alternative version gives invalid output or does not have a different execution plan,
but the code is close enough that some direct feedback can be given for a corrected revision
to be made
o N: The alternative version is incorrect or has the same execution, and the code is not
sufficiently close to correct for more directed feedback than ‘see the course material’, or is
missing.
• Spark Q1+Q2 Improvement (0.5% + 0.5%)
o This is a simple yes or no tied to a result of E or M for Spark Q1 and Q2. If the valid
alternative version represents a tangible improvement in efficiency, this mark is awarded.
• Report SQL (2% + 2%) – Q1 and Q2
o E: All required analysis is present and valid. Analysis shows clear insight into the underlying
SQL execution engine, and the way declarative SQL statements are interpreted and
optimised into an execution plan.
o M: All required analysis is present and valid. Analysis shows a basic understanding of the SQL
execution engine, and the way declarative SQL statements are interpreted and optimised
into an execution plan.
o R: All required analysis is present. Analysis is understandable, but has errors that need to be
corrected based on specific feedback.
o N: Any required analysis is missing, or analysis shows clear lack of understanding of the way
SQL statements are executed.
• Report Spark (2% + 2%) – Q1 and Q2
o E: All required analysis is present and valid. Analysis shows clear insight into the underlying
Spark dataframe execution engine, and the way Spark code is interpreted and optimised into
an execution plan.
o M: All required analysis is present and valid. Analysis shows a basic understanding of the
Spark dataframe execution engine, and the way Spark code is interpreted and optimised into
an execution plan.
o R: All required analysis is present. Analysis is understandable, but has errors that need to be
corrected based on specific feedback.
o N: Any required analysis is missing, or analysis shows clear lack of understanding of the way
Spark code is executed.
• Report Parallelism Discussion (2%)
o E: Discussion identifies valid differences between the alternative versions when executing in
parallel, or identifies that there are no differences. Discussion shows clear insight into
parallelism concerns, particularly shuffles and distributed joins.
o M: Discussion identifies valid differences between the alternative versions when executing in
parallel, or identifies that there are no differences. Discussion does not include incorrect
information about parallelism concerns.
o R: Discussion is present and shows some insight, but has invalid observations or information
that must be corrected based on specific feedback.
o Discussion is missing, unclear, or does not show any understanding of parallelism concerns.
A result of (E)xemplary for a line item yields 100% of the available marks. (M)eets Expectations yields 75%.
(R)evision Required yields 0%, but with the chance to resubmit that aspect (and that aspect only) for re-
grading by the resubmission due date, to then receive the full 75% if successful. (N)ot Sufficient For Feedback
yields 0% with no chance to resubmit.
GROUP MANAGEMENT
The same group management recommendations and outcomes apply for Task 2.
If a group member is not contributing correctly the following outcomes will occur:
• Didn’t contribute to any rewritten query: excluded from group marks, 0 for the assessment
• Contributed to a rewritten query but did not otherwise participate: 50% of the earned marks. i.e. a
mark of 7/10 will become 3.5