Skip to content

Associate Developer for Apache Spark · Domain 2: Using Spark SQL · 20% of the exam

Task 2.1: Utilize common data sources such as JDBC, files, etc., to efficiently read from and write to Spark DataFrames using Spark SQL, including overwriting and partitioning by column

Reading and writing Parquet, CSV, JSON and JDBC sources with their options, parallel JDBC reads, overwriting output, and partitionBy directory layouts.

Study it

  • Data sources: files, JDBC, overwrite and partitionBy

    Lesson coming

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

A nightly job replaces every row of a MySQL reporting table with df.write.format("jdbc") and mode("overwrite"). After each run the table's indexes are gone and the database team has to recreate them. The schema of df never changes. Which writer option keeps the indexes?

  1. AReplace mode("overwrite") with mode("append")
  2. Boption("createTableOptions", "...") with the index definitions
  3. Coption("truncate", "true")
  4. Doption("isolationLevel", "SERIALIZABLE")
Show the answer and why
  • AReplace mode("overwrite") with mode("append")

    Incorrect

    Append adds the new rows to the existing ones, so the table no longer holds only the latest data.

  • Boption("createTableOptions", "...") with the index definitions

    Incorrect

    createTableOptions sets database-specific table options when Spark creates the table; the overwrite still drops the existing table first.

  • Coption("truncate", "true")

    Correct

    With Overwrite, truncate makes Spark truncate the existing table instead of dropping and recreating it, so metadata such as indexes is kept.

  • Doption("isolationLevel", "SERIALIZABLE")

    Incorrect

    isolationLevel sets the transaction isolation level of the connection; it does not change whether the table is dropped.

When a JDBC write uses SaveMode.Overwrite, Spark by default drops and recreates the target table. The writer option truncate makes it truncate the existing table instead, which can be more efficient and prevents table metadata such as indexes from being removed; it does not work in some cases, for example when the new data has a different schema, and only some dialects support it, MySQL among them. createTableOptions applies only when Spark creates a table, and isolationLevel is the transaction isolation of the connection.

Question 2 · choose 1

A scheduled batch job reads CSV files from a landing folder whose file names carry no dates. Each run must load only the files written since the previous run started, which the job keeps as a timestamp. Which read meets the requirement?

  1. Aspark.read.option("modifiedAfter", last_start).csv(landing)
  2. Bspark.readStream.option("modifiedAfter", last_start).csv(landing)
  3. Cspark.read.option("pathGlobFilter", "*.csv").csv(landing)
  4. Dspark.read.option("ignoreMissingFiles", "true").csv(landing)
Show the answer and why
  • Aspark.read.option("modifiedAfter", last_start).csv(landing)

    Correct

    modifiedAfter includes only files whose modification time is after the given timestamp, for batch reads of file-based sources.

  • Bspark.readStream.option("modifiedAfter", last_start).csv(landing)

    Incorrect

    Structured Streaming file sources do not support the modification time path filters.

  • Cspark.read.option("pathGlobFilter", "*.csv").csv(landing)

    Incorrect

    pathGlobFilter includes files whose names match the glob, so *.csv matches old and new files alike; it does not look at modification times.

  • Dspark.read.option("ignoreMissingFiles", "true").csv(landing)

    Incorrect

    ignoreMissingFiles keeps a job running when files are deleted after the DataFrame is built; it does not select new files.

The generic file source options apply to parquet, orc, avro, json, csv and text. modifiedBefore and modifiedAfter filter the files a batch query loads by modification time, with timestamps in the form YYYY-MM-DDTHH:mm:ss read in the session time zone unless a timezone option is given; Structured Streaming file sources do not support them. pathGlobFilter includes files whose names match a glob, and ignoreMissingFiles tolerates files deleted after the DataFrame is constructed.

Question 3 · choose 1

/data/out/sales holds 30 days of Parquet data, written with partitionBy("day"). A corrected day must replace only that day's data, and the session uses default settings: from pyspark.sql import functions as F fixed = spark.read.parquet("/data/staging/sales").filter(F.col("day") == "2026-11-14") (fixed.write .mode("overwrite") .partitionBy("day") .parquet("/data/out/sales")) What does this write do, and what change meets the requirement?

  1. AIt replaces only day=2026-11-14, because partitionBy limits the overwrite to the days that fixed holds
  2. BIt leaves only 2026-11-14 in the output; add .option("partitionOverwriteMode", "dynamic") to the write
  3. CIt leaves only 2026-11-14 in the output; replace mode("overwrite") with mode("append")
  4. DIt fails because /data/out/sales exists; replace mode("overwrite") with mode("ignore")
Show the answer and why
  • AIt replaces only day=2026-11-14, because partitionBy limits the overwrite to the days that fixed holds

    Incorrect

    With the default static partition overwrite mode, an overwrite with no partition specification removes every existing partition, not only the days in fixed.

  • BIt leaves only 2026-11-14 in the output; add .option("partitionOverwriteMode", "dynamic") to the write

    Correct

    The default mode is static; in dynamic mode Spark overwrites only the partitions that receive data, and the write option takes precedence over the session setting.

  • CIt leaves only 2026-11-14 in the output; replace mode("overwrite") with mode("append")

    Incorrect

    Append adds the corrected rows next to the old rows of 2026-11-14 instead of replacing them.

  • DIt fails because /data/out/sales exists; replace mode("overwrite") with mode("ignore")

    Incorrect

    Overwrite does not fail on existing data, and ignore would skip the write and keep the old day.

spark.sql.sources.partitionOverwriteMode is static by default: an overwrite deletes all partitions that match the partition specification before writing, and a DataFrame write has none, so all 30 days go. In dynamic mode Spark deletes nothing ahead and overwrites only the partitions that have data written into them at runtime. It can be set per write with the partitionOverwriteMode option, which takes precedence over the configuration.

Question 4 · choose 1

A CSV export starts with several lines of notes, each beginning with #, before the data lines. Which reader option makes Spark skip those lines?

  1. Aoption("header", "true")
  2. Boption("mode", "DROPMALFORMED")
  3. Coption("comment", "#")
  4. Doption("nullValue", "#")
Show the answer and why
  • Aoption("header", "true")

    Incorrect

    header uses the first line as column names; it does not skip comment lines.

  • Boption("mode", "DROPMALFORMED")

    Incorrect

    mode decides what happens to records that fail to parse; a note line that parses into fields is still read as data.

  • Coption("comment", "#")

    Correct

    comment sets a single character used for skipping lines beginning with it.

  • Doption("nullValue", "#")

    Incorrect

    nullValue sets the string that represents a null value.

CSV reader options control parsing. comment sets a single character used for skipping lines that begin with it; it is disabled by default. header uses the first line as column names, nullValue sets the string representation of a null value, and mode decides how corrupt records are handled.

Databricks documentation

Question 5 · choose 1

A supplier's CSV files write NA wherever a value is missing, in numeric and text columns alike. Which reader option makes Spark read those cells as null?

  1. Aoption("emptyValue", "NA")
  2. Boption("nullValue", "NA")
  3. Coption("nanValue", "NA")
  4. Doption("comment", "NA")
Show the answer and why
  • Aoption("emptyValue", "NA")

    Incorrect

    emptyValue sets the string representation of an empty value, not of a null.

  • Boption("nullValue", "NA")

    Correct

    nullValue sets the string representation of a null value, and it applies to all supported types, including strings.

  • Coption("nanValue", "NA")

    Incorrect

    nanValue sets the representation of a non-number value for floating-point columns.

  • Doption("comment", "NA")

    Incorrect

    comment takes a single character and skips lines that begin with it.

The CSV option nullValue sets the string representation of a null value; since Spark 2.0.1 it applies to all supported types, including the string type. emptyValue covers empty values, nanValue the representation of a non-number value, and comment skips lines starting with a character.

Databricks documentation

Question 6 · choose 1

A nightly job reads thousands of Parquet files, and now and then one of them is corrupted by the upstream system, which makes the whole read fail. The team prefers to load the readable files and investigate the bad ones later. Which setting fits?

  1. Aoption("ignoreMissingFiles", True)
  2. Boption("recursiveFileLookup", "true")
  3. Coption("ignoreCorruptFiles", "true")
  4. Doption("pathGlobFilter", "*.parquet")
Show the answer and why
  • Aoption("ignoreMissingFiles", True)

    Incorrect

    ignoreMissingFiles covers files deleted after the DataFrame is constructed, not corrupted files.

  • Boption("recursiveFileLookup", "true")

    Incorrect

    recursiveFileLookup loads files from subdirectories; it does not skip corrupted files.

  • Coption("ignoreCorruptFiles", "true")

    Correct

    With ignoreCorruptFiles, Spark jobs continue when they encounter corrupted files, and the contents that have been read are still returned.

  • Doption("pathGlobFilter", "*.parquet")

    Incorrect

    pathGlobFilter selects files by name; a corrupted file with a matching name is still read.

The generic file source options apply to parquet, orc, avro, json, csv and text. Setting the data source option ignoreCorruptFiles (or spark.sql.files.ignoreCorruptFiles) to true makes jobs continue to run when they encounter corrupted files, still returning the contents that have been read. ignoreMissingFiles handles files deleted after the DataFrame was built.

Practice domain 2 →Practice all domains →