Skip to content

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

Sample questions

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?

  1. Achecked = df.withColumn("ok", F.assert_true(F.col("amount") >= 0, "negative amount"))
  2. Bdf.select(F.assert_true(F.col("amount") >= 0, "negative amount")).collect()
  3. Cdf.select(F.assert_true(F.col("amount") < 0, "negative amount")).collect()
  4. Ddf.na.drop(subset=["amount"]).collect()
Show the answer and why
  • Achecked = df.withColumn("ok", F.assert_true(F.col("amount") >= 0, "negative amount"))

    Incorrect

    withColumn is a transformation, and transformations are lazy; no action ever uses checked, and the job writes df, so the assertion never runs.

  • Bdf.select(F.assert_true(F.col("amount") >= 0, "negative amount")).collect()

    Correct

    assert_true returns null when its condition is true and throws an exception with the given message otherwise, and collect runs it on every row.

  • Cdf.select(F.assert_true(F.col("amount") < 0, "negative amount")).collect()

    Incorrect

    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.

Question 2 · choose 1

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?

  1. AIts columns are sku, Q1, Q2 and Q3; for B, Q2 is NULL and Q3 is 4
  2. BIts columns are sku, Q1 and Q2; for B, Q2 is 0
  3. CIts columns are sku, Q1 and Q2; for B, Q2 is NULL, and B's Q3 amount appears nowhere
  4. 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.

Question 3 · choose 1

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?

  1. Atop.filter(F.col("salary") > 5000)
  2. Btop.filter(F.col("max(salary)") > 5000)
  3. Ctop.filter(F.col("max_salary") > 5000)
  4. 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.

Question 4 · choose 1

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?

  1. Adf.filter(F.col("code").like("[A-Z][A-Z][A-Z]"))
  2. Bdf.filter(F.col("code").contains("[A-Z]{3}"))
  3. Cdf.filter(F.col("code").startswith("^[A-Z]"))
  4. 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 ^).

Question 5 · choose 1

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?

  1. AOne row per region, with the sum of amount for that region
  2. BA DataFrame with one row that holds the sum of amount over all rows
  3. CAn error, because agg can only be called on the result of groupBy
  4. 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.

Question 6 · choose 1

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?

  1. Acounts.filter(F.col("count") > 1)
  2. Bcounts.filter("email > 1")
  3. Ccounts.filter(F.count("email") > 1)
  4. 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.

Practice domain 3 →Practice all domains →