Associate Developer for Apache Spark · Domain 3: Developing Apache Spark DataFrame/DataSet API Applications · 30% of the exam
Task 3.2: Perform data deduplication and validation operations on DataFrames; Perform aggregate operations on DataFrames such as count, approximate count distinct, and mean, summary
distinct and dropDuplicates, dropna, fillna and safe casts for validation, and groupBy aggregates, approx_count_distinct, describe and summary.
Study it
Deduplication, missing values and validation
Lesson coming
Aggregations: count, approx_count_distinct, mean, describe and summary
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
Before a DataFrame df of payments is written, a validation step must make the job fail with the message "negative amount" if any row has amount below 0. The step runs as one line of its own, and the job then writes df. pyspark.sql.functions is imported as F. Which line does that?
assert_true throws when its condition is false, so this fails the job on every amount of 0 or more and passes when every amount is negative.
Ddf.na.drop(subset=["amount"]).collect()
Incorrect
na.drop removes rows with a null amount; it does not check the sign of the values.
pyspark.sql.functions.assert_true(col, errMsg) returns null if the input column is true and throws an exception with the provided error message otherwise. Selecting it over the condition amount >= 0 and running an action evaluates it for every row, so one negative amount fails the job with the message. Transformations such as withColumn are lazy and compute nothing until an action needs their result, so an assertion column that no action reads never fails; a condition written the other way round fails on the valid rows, and na.drop handles nulls.
A developer runs:
from pyspark.sql import functions as F
sales = spark.createDataFrame(
[("A", "Q1", 10), ("A", "Q2", 5), ("B", "Q1", 7), ("B", "Q3", 4)],
"sku STRING, q STRING, amt INT")
res = sales.groupBy("sku").pivot("q", ["Q1", "Q2"]).agg(F.max("amt"))
Which statement describes res?
AIts columns are sku, Q1, Q2 and Q3; for B, Q2 is NULL and Q3 is 4
BIts columns are sku, Q1 and Q2; for B, Q2 is 0
CIts columns are sku, Q1 and Q2; for B, Q2 is NULL, and B's Q3 amount appears nowhere
DIts creation fails, because the data holds a q value, Q3, that the list of values leaves out
Show the answer and why
AIts columns are sku, Q1, Q2 and Q3; for B, Q2 is NULL and Q3 is 4
Incorrect
When values are given, they are the values translated to columns, so Q3 does not become a column.
BIts columns are sku, Q1 and Q2; for B, Q2 is 0
Incorrect
B has no Q2 rows, and max over an empty input returns NULL, not 0.
CIts columns are sku, Q1 and Q2; for B, Q2 is NULL, and B's Q3 amount appears nowhere
Correct
The values list sets the output columns, so Q3 is left out, and max over B's empty Q2 input returns NULL.
DIts creation fails, because the data holds a q value, Q3, that the list of values leaves out
Incorrect
The list names the values that become columns; rows with other values are simply not pivoted into a column.
GroupedData.pivot(pivot_col, values) translates the listed values into columns of the output; without a list, Spark eagerly computes the distinct values first. Here only Q1 and Q2 become columns, so B's Q3 row contributes to none of them. Aggregate functions ignore NULLs, and functions such as max return NULL when the input is empty (count(*) returns 0), so B's Q2 cell is NULL.
A DataFrame df has the columns dept and salary. A developer runs:
from pyspark.sql import functions as F
top = df.groupBy("dept").agg(F.max("salary"))
Which expression keeps only the departments whose highest salary is above
5000?
Atop.filter(F.col("salary") > 5000)
Btop.filter(F.col("max(salary)") > 5000)
Ctop.filter(F.col("max_salary") > 5000)
Dtop.filter(F.max(F.col("salary")) > 5000)
Show the answer and why
Atop.filter(F.col("salary") > 5000)
Incorrect
The aggregated DataFrame has no column named salary, only dept and the aggregate's column.
Btop.filter(F.col("max(salary)") > 5000)
Correct
Without an alias, the aggregate column is named after the expression, max(salary), as the min(age) column in the API reference shows.
Ctop.filter(F.col("max_salary") > 5000)
Incorrect
No alias was given, so no column is named max_salary.
Dtop.filter(F.max(F.col("salary")) > 5000)
Incorrect
top no longer has a salary column for F.max to read; the maximum is already the aggregate's own column.
GroupedData.agg computes the aggregates and returns a DataFrame with the grouping columns and one column per aggregate. Without alias(), the aggregate column takes the expression's name: the API reference shows min(age) for sf.min(df.age) and count(1) for a count of all rows, so here the column is max(salary). Adding .alias("max_salary") inside agg would give a simpler name.
A validation step must keep only the rows of df whose code column is exactly three capital letters, such as ABC, and pyspark.sql.functions is imported as F. Which filter does that?
Adf.filter(F.col("code").like("[A-Z][A-Z][A-Z]"))
Bdf.filter(F.col("code").contains("[A-Z]{3}"))
Cdf.filter(F.col("code").startswith("^[A-Z]"))
Ddf.filter(F.col("code").rlike("^[A-Z]{3}$"))
Show the answer and why
Adf.filter(F.col("code").like("[A-Z][A-Z][A-Z]"))
Incorrect
like matches a SQL LIKE pattern, not a regular expression, so [A-Z] does not work as a character class.
Bdf.filter(F.col("code").contains("[A-Z]{3}"))
Incorrect
contains checks for the literal substring, not a regular expression.
Cdf.filter(F.col("code").startswith("^[A-Z]"))
Incorrect
startswith matches the literal start of the string and is not meant for regex patterns such as ^.
Ddf.filter(F.col("code").rlike("^[A-Z]{3}$"))
Correct
rlike is SQL RLIKE, LIKE with a regular expression, so the anchored pattern accepts exactly three capital letters.
Column.rlike(other) is the SQL RLIKE expression, LIKE with a regular expression, returning a boolean Column for the regex match; ^[A-Z]{3}$ matches exactly three capital letters. Column.like uses SQL LIKE patterns, contains matches a literal substring, and startswith matches a literal prefix (its documentation says not to use a regex ^).
A DataFrame sales has the columns region and amount, with rows for twelve regions. With pyspark.sql.functions imported as F, a developer runs sales.agg(F.sum("amount")) without calling groupBy first. What does it return?
AOne row per region, with the sum of amount for that region
BA DataFrame with one row that holds the sum of amount over all rows
CAn error, because agg can only be called on the result of groupBy
DOne row per partition, each with the sum of amount in that partition
Show the answer and why
AOne row per region, with the sum of amount for that region
Incorrect
Without groupBy there are no groups; agg aggregates the entire DataFrame.
BA DataFrame with one row that holds the sum of amount over all rows
Correct
DataFrame.agg aggregates on the entire DataFrame without groups, as a shorthand for groupBy().agg().
CAn error, because agg can only be called on the result of groupBy
Incorrect
DataFrame.agg exists as a shorthand for groupBy().agg() with no grouping columns.
DOne row per partition, each with the sum of amount in that partition
Incorrect
agg aggregates the entire DataFrame without groups; partitions do not form groups.
DataFrame.agg(*exprs) aggregates on the entire DataFrame without groups and is a shorthand for df.groupBy().agg(). It returns a DataFrame, here with a single row holding the total of amount across all rows.
A developer runs counts = df.groupBy("email").count() to look for emails that appear more than once. With pyspark.sql.functions imported as F, which filter keeps those emails?
Acounts.filter(F.col("count") > 1)
Bcounts.filter("email > 1")
Ccounts.filter(F.count("email") > 1)
Dcounts.filter(F.col("count(email)") > 1)
Show the answer and why
Acounts.filter(F.col("count") > 1)
Correct
GroupedData.count counts the records of each group into a column named count, next to the grouping column.
Bcounts.filter("email > 1")
Incorrect
email holds the addresses; the number of records per email is in the count column.
Ccounts.filter(F.count("email") > 1)
Incorrect
count already ran in the groupBy; a new aggregate cannot be used as a row filter on counts.
Dcounts.filter(F.col("count(email)") > 1)
Incorrect
GroupedData.count names its column count, not count(email).
GroupedData.count() counts the number of records for each group and returns a DataFrame with the grouping columns and a column named count, as the API reference example shows. Filtering that column for values above 1 keeps the emails that occur more than once.