Spark Dev Assoc · Domain 2 of 7

Spark Dev Assoc Domain 2: Using Spark SQL

Reading and writing data with Spark SQL: JDBC and file sources, querying files directly, save modes, partitioned and bucketed persistent tables, and temporary views that make DataFrames queryable with SQL.

Exam weight
20%
Official objectives
4
Estimated questions estimate
~9
Suggested study time estimate
10–14 hrs

About 11 min read Reviewed October 2026

Overview

Domain 2 accounts for 20% of the exam and has four official objectives. It covers reading from and writing to sources such as JDBC databases and files, including overwriting output and partitioning it by column. It covers running SQL directly against ORC, JSON, CSV, text and Delta files, and choosing a save mode. It also covers saving persistent tables with sorting and partitioning, and registering DataFrames as temporary views.

Most questions here are about the exact API: which option enables parallel JDBC reads, which save mode is the default, what happens when a table already exists, or how long a view lives. Small differences, such as the global_temp prefix or the backticks around a file path, decide the right option.

How it's tested

Expect code-completion and outcome questions. Some show a write call and ask what happens when the target exists; others give a requirement, such as parallel database reads or a view visible to another notebook session, and ask which method or option satisfies it. Pay attention to defaults and to the scope of every name created.

  • Choose the save mode

    A target path or table already exists, and you pick append, overwrite, ignore or the default error behaviour.

  • Complete the reader

    A spark.read or spark.sql call is missing an option, format string or path syntax, and you fill the gap.

  • Scope the name

    You decide whether a temp view, global temp view or saved table is visible in a given session or after restart.

Reasoning it through

A job writes a daily DataFrame to an existing Parquet path with df.write.parquet(path) and fails. The cause is the default save mode, errorifexists, which refuses to write when data is already there. Adding mode('append') keeps old files and adds new ones, while mode('overwrite') replaces them. Because the requirement is to keep history, append is the answer rather than overwrite.

Objectives 4

Official objectives from the Databricks outline, in outline order.

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 columnJDBC and file I/O, overwrite and partitionBy.
What it means
This objective covers using spark.read and df.write with built-in sources. For JDBC you set url, dbtable or query, and credentials. For files you choose a format such as parquet, csv, json or delta. Writes can replace data with mode('overwrite') and split output into folders with partitionBy('col').
What to understand
A plain JDBC read uses one connection and one partition. To parallelise it, set partitionColumn, lowerBound, upperBound and numPartitions; the bounds only decide stride and do not filter rows. partitionBy on write creates one directory per distinct value, which helps queries filtering on that column but harms performance when the column has very many values.
How it shows up
Look for a slow database read that needs parallel options, or a write that must produce folders per date.
Common confusion
Thinking lowerBound and upperBound filter the rows read; they only set partition ranges.
2.2 Execute SQL queries directly on files, including ORC Files, JSON Files, CSV Files, Text Files, and Delta Files, and understand the different save modes for outputting data in Spark SQLSQL on file paths and the four save modes.
What it means
This objective covers querying files without first creating a table, using syntax such as SELECT * FROM parquet.`/path` or json.`/path`, and choosing among the save modes append, overwrite, ignore and errorifexists when writing results.
What to understand
The format prefix and backtick-quoted path tell Spark how to read the location. Direct queries use default reader options, so a CSV with a header row is better read through spark.read with option('header', True) or a view defined with options. errorifexists (also written error) is the default; ignore skips the write silently when data exists.
How it shows up
A query uses a format prefix, and you judge whether it works, or a write targets existing data and you pick the mode.
Common confusion
Assuming ignore appends; it writes nothing when the target already exists.
2.3 Save data to persistent tables while applying sorting and partitioning to optimize data retrievalsaveAsTable with partitionBy, bucketBy and sortBy.
What it means
This objective covers writing DataFrames as catalog tables with df.write.saveAsTable('name'), so they survive the session and can be queried by name. Layout options include partitionBy for directory partitioning, and bucketBy with sortBy for hash bucketing with sorted files inside each bucket.
What to understand
bucketBy and sortBy only work with saveAsTable, not with a plain path save. Partitioning helps when queries filter on low-cardinality columns such as date. In Databricks the default table format is Delta Lake, and managed tables live in Unity Catalog; liquid clustering is the modern Delta alternative to static partitioning, though the exam objective is phrased in core Spark terms.
How it shows up
A requirement for faster lookups by a column is given, and you choose the write option that organises files accordingly.
Common confusion
Using sortBy with save(path); it requires bucketBy and saveAsTable.
2.4 Register DataFrames as temporary views in Spark SQL, allowing them to be queried with SQL syntaxcreateOrReplaceTempView and global temp views.
What it means
This objective covers making a DataFrame available to SQL. df.createOrReplaceTempView('v') registers a view in the current SparkSession, and spark.sql('SELECT ... FROM v') then returns a DataFrame. createOrReplaceGlobalTempView registers it in the global_temp database for every session in the same application.
What to understand
Temp views store no data; they hold the query plan and are evaluated each time they are used. A local view disappears when its session ends, and a global one disappears when the application stops. createTempView fails if the name exists, while createOrReplaceTempView overwrites it. Global views must be referenced as global_temp.name.
How it shows up
Expect questions about view lifetime, the global_temp prefix, or the difference between a view and a saved table.
Common confusion
Thinking a temp view copies data; it is a named plan.

High-yield concepts

The terms, rules and formulas this domain turns into questions most often.

  • Default save mode

    errorifexists, also written error.

    Remember
    Writing to existing data fails unless a mode is set.
    Tested as
    Write outcome questions.
    Trap
    Assuming the default is overwrite.

  • mode('ignore')

    Skip the write if the target exists.

    Remember
    No error and no new data.
    Tested as
    Save mode questions.
    Trap
    Treating it as append.

  • JDBC parallel read options

    partitionColumn, lowerBound, upperBound, numPartitions.

    Remember
    All four are needed together.
    Tested as
    Read performance items.
    Trap
    Using the bounds as filters.

  • partitionBy on write

    Creates a folder for each value of the column.

    Remember
    Best for low-cardinality filter columns.
    Tested as
    Layout questions.
    Trap
    Partitioning on a unique ID.

  • bucketBy with sortBy

    Hashes rows into a fixed number of buckets with sorted files.

    Remember
    Requires saveAsTable.
    Tested as
    Persistent table questions.
    Trap
    Calling it on a path write.

  • SQL on files

    SELECT * FROM format.`path` reads files without a table.

    Remember
    Backticks around the path.
    Tested as
    Direct query questions.
    Trap
    Using quotes instead of backticks.

  • createOrReplaceTempView

    Session-scoped view over a DataFrame plan.

    Remember
    Gone when the session ends.
    Tested as
    View scope questions.
    Trap
    Expecting it in another session.

  • Global temp view

    Application-scoped view in the global_temp database.

    Remember
    Query as global_temp.name.
    Tested as
    View scope questions.
    Trap
    Omitting the prefix.

  • saveAsTable

    Writes data and registers the table in the catalog.

    Remember
    Survives restarts.
    Tested as
    Persistence questions.
    Trap
    Confusing it with a temp view.

  • Delta format

    Default table format on Databricks with ACID transactions.

    Remember
    Query paths with delta.`path`.
    Tested as
    Format and SQL-on-files items.
    Trap
    Treating Delta as a plain Parquet folder.

Side by side

Concepts candidates mix up, compared on the point that decides the answer.

Save modes

ConceptMeaningKey differenceCommon trap
errorifexists Fail if data exists Default behaviour Expecting overwrite by default
append Add new data alongside existing data Keeps history Using it when replacement is wanted
overwrite Replace existing data Removes old data Using it for incremental loads
ignore Do nothing if data exists Silent no-op Confusing it with append

Temp view vs global temp view vs table

ConceptMeaningKey differenceCommon trap
Temp view Named plan in one session Session lifetime, no stored data Expecting other sessions to see it
Global temp view Named plan in global_temp Application lifetime Forgetting the global_temp prefix
Persistent table Data plus catalog entry Survives restarts Thinking a view stores data the same way

partitionBy vs bucketBy

ConceptMeaningKey differenceCommon trap
partitionBy Folder per column value Works with path or table writes Choosing a high-cardinality column
bucketBy Fixed number of hash buckets Only with saveAsTable Using it on save(path)

Exam traps

Each trap is an answer that looks right. Learn the clue that should change your mind.

  1. Overwrite is default

    What candidates pick
    Expect df.write.parquet(path) to replace existing files.
    Why it looks right
    Many tools overwrite silently.
    Why it's wrong
    Spark's default save mode raises an error when the destination already holds data.

    Clue to watch for The question says the path already exists and no mode is set.

  2. Bounds filter rows

    What candidates pick
    Use lowerBound and upperBound to read only a range of IDs.
    Why it looks right
    The names sound like a filter.
    Why it's wrong
    They only define partition strides; rows outside the range still land in the first or last partition.

    Clue to watch for The requirement mentions parallelism, not filtering.

  3. Ignore means append

    What candidates pick
    Choose ignore to add rows to an existing table.
    Why it looks right
    Ignore sounds harmless.
    Why it's wrong
    Ignore skips the write entirely when the target exists, so no rows are added.

    Clue to watch for The scenario needs new data to appear.

  4. Global view without prefix

    What candidates pick
    Query a global temp view as SELECT * FROM v.
    Why it looks right
    Local views work that way.
    Why it's wrong
    Global temporary views are registered in global_temp and must be qualified with that database.

    Clue to watch for The view was created with createGlobalTempView.

  5. Views hold data

    What candidates pick
    Say a temp view caches the DataFrame's rows.
    Why it looks right
    Tables hold data, and views look like tables.
    Why it's wrong
    A temp view stores only the logical plan and recomputes it on every query unless cached separately.

    Clue to watch for The question asks what is stored when the view is created.

  6. Bucket a path

    What candidates pick
    Write df.write.bucketBy(8, 'id').parquet(path).
    Why it looks right
    Other write options work with paths.
    Why it's wrong
    Bucketing metadata lives in the catalog, so bucketBy and sortBy require saveAsTable.

    Clue to watch for Options include both save(path) and saveAsTable.

Answer framework

Apply these steps when a Spark SQL read, write or view question appears.

  1. Identify the source or target type, such as a JDBC database, a file format or a catalog table, because each one has its own reader and writer options.
  2. For writes, check whether the destination already exists and apply the save mode rules, remembering that errorifexists is the default.
  3. For JDBC reads, confirm whether parallelism is required and whether all four partitioning options are supplied together.
  4. For layout, choose partitionBy for low-cardinality filter columns, and bucketBy with sortBy only when saving as a table.
  5. For SQL access, decide the needed scope: session view, application-wide global view, or a persistent table in the catalog.

Practice question

A notebook creates a view with df.createGlobalTempView('sales_v'). A second SparkSession running in the same Spark application must now query that view using spark.sql. Which query runs successfully?

Want more like this? The Spark Dev Assoc practice test has questions for every domain.

Checklist

0 of 11

Tick each one you can do without notes. Your ticks stay in this browser.

Study strategy

Begin with writes, because save mode questions are frequent and easy marks once memorised. In a notebook, write a small DataFrame to a path, then write again with each mode and inspect the folder after each attempt. Note which call fails, which adds files and which does nothing.

Then practise reads. Query a Parquet folder and a JSON folder directly with the format-prefix syntax, and compare a direct CSV query with a spark.read call that sets header and inferSchema. If you have access to a database, try a JDBC read with and without partitionColumn and count the tasks in the Spark UI; otherwise learn the four options and what each controls.

Finally, work through table and view scope. Create a temp view, a global temp view and a saved table, then open a new session with spark.newSession() and see which names resolve. Write the rules on one card and revisit them before mock exams.

FAQ

How much of the exam covers Using Spark SQL?

Using Spark SQL is 20% of the scored exam, roughly nine of 45 questions, built on four official objectives about sources, SQL on files, tables and views.

What is the default save mode in Spark?

errorifexists, also written error. If the target path or table already contains data, the write fails until you set append, overwrite or ignore.

How do I read a JDBC table in parallel?

Set partitionColumn to a numeric, date or timestamp column, provide lowerBound and upperBound, and set numPartitions. Spark issues one query per partition range.

Can I run SQL on a file without creating a table?

Yes. Use the format as a prefix and the path in backticks, for example SELECT * FROM parquet.`/data/sales`.

When should I use partitionBy?

When queries regularly filter on a column with relatively few distinct values, such as date or country, so Spark can skip whole folders.

How long does a temporary view last?

A local temp view lasts as long as its SparkSession; a global temp view lasts until the Spark application stops.

Other Data & Analytics certifications

Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.

Test your Domain 2 knowledge

Test yourself on save modes, parallel JDBC reads, direct file queries and view scope with timed questions in the exam format.

Take the Spark Dev Assoc practice test Check your Spark Dev Assoc readiness

Keep going