Associate Developer for Apache Spark · Domain 2: Using Spark SQL · 20% of the exam
Task 2.3: Save data to persistent tables while applying sorting and partitioning to optimize data retrieval; Register DataFrames as temporary views in Spark SQL, allowing them to be queried with SQL syntax
saveAsTable with partitionBy, bucketBy and sortBy, managed against external table paths, and temporary against global temporary views and their lifetimes.
Study it
Persistent tables with partitioning, bucketing and sorting
Lesson coming
Temporary and global temporary views
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
On a Spark cluster with a Hive metastore, a team saves a 2-billion-row events DataFrame as the persistent table sales.events. Each row has an event_ts timestamp; queries filter on event_date (about 700 values) and look up single customer_id values (40 million values). The table is written with df.write.partitionBy(<columns>).saveAsTable("sales.events"). Which partitioning column fits?
Acustomer_id
Bevent_date
Cevent_ts
Devent_date and customer_id, in that order
Show the answer and why
Acustomer_id
Incorrect
partitionBy creates a directory per value, so it has limited applicability to a column with 40 million values.
Bevent_date
Correct
partitionBy creates a directory structure per value, which suits a low-cardinality column that queries filter on.
Cevent_ts
Incorrect
event_ts, the event timestamp, has close to one value per row, the high-cardinality case partitionBy is limited for.
Devent_date and customer_id, in that order
Incorrect
Nesting customer_id under each date still creates a directory per customer value, which partitionBy is limited for.
Partitioning works with both save and saveAsTable. partitionBy creates a directory structure, one directory per partition value, so it has limited applicability to columns with high cardinality. event_date, with a few hundred values that queries filter on, is the column that fits; customer_id and timestamps would create millions of directories.
In open-source Spark, a developer runs CREATE TABLE marts.daily USING parquet PARTITIONED BY (day) LOCATION '/data/marts/daily' AS SELECT * FROM staging.daily. The directory /data/marts/daily already holds Parquet files from an earlier export, and no legacy settings are changed. What happens?
AThe statement fails with an analysis exception, because the location is a non-empty directory
BThe table is created over the old files, and the query's rows are added next to them
CThe old files are replaced, so the table holds exactly the query's rows
DThe table is created over the old files, and the SELECT is skipped
Show the answer and why
AThe statement fails with an analysis exception, because the location is a non-empty directory
Correct
For CREATE TABLE AS SELECT with LOCATION, Spark throws an analysis exception if the location exists as a non-empty directory.
BThe table is created over the old files, and the query's rows are added next to them
Incorrect
Spark does not create the table over a non-empty location for CREATE TABLE AS SELECT; it throws an analysis exception.
CThe old files are replaced, so the table holds exactly the query's rows
Incorrect
Overwriting the location happens only when a legacy setting is enabled; by default the statement fails.
DThe table is created over the old files, and the SELECT is skipped
Incorrect
CREATE TABLE AS SELECT populates the table with the select's data, and with a non-empty location it fails instead.
CREATE TABLE ... USING ... AS SELECT populates a data source table from the query, and LOCATION sets where its data is stored. If the given location already exists as a non-empty directory, Spark throws an analysis exception; only the legacy setting spark.sql.legacy.allowNonEmptyLocationInCTAS makes it overwrite the directory instead.
A developer runs, in one SparkSession:
daily = spark.read.table("sales.orders").groupBy("day").count()
daily.createOrReplaceGlobalTempView("daily_counts")
spark.sql("SELECT * FROM daily_counts").show()
The last line fails because the view is not found. Which change fixes it?
ARun spark.catalog.refreshTable("daily_counts") before the query
BCall daily.cache() before creating the view
CReplace createOrReplaceGlobalTempView with createGlobalTempView
DQuery the view as global_temp.daily_counts
Show the answer and why
ARun spark.catalog.refreshTable("daily_counts") before the query
Incorrect
The view exists; it is not found because it was looked up without its database name, which refreshing does not change.
BCall daily.cache() before creating the view
Incorrect
Caching changes how the data is stored, not where the view's name lives.
CReplace createOrReplaceGlobalTempView with createGlobalTempView
Incorrect
Both register a global temporary view, which is still looked up in the global_temp database.
DQuery the view as global_temp.daily_counts
Correct
A global temporary view is tied to the system database global_temp, and it must be referred to by its qualified name.
A global temporary view is tied to a system preserved database, global_temp, and must be referred to with the qualified name, for example SELECT * FROM global_temp.daily_counts. An unqualified name looks for a table or a local temporary view instead.
A developer wants to create a new table sales.daily from the DataFrame df with the DataFrameWriterV2 API, partitioned by its day column; pyspark.sql.functions is imported as F. Which call does that?
df.write returns a DataFrameWriter, whose method is partitionBy; partitionedBy belongs to DataFrameWriterV2.
DataFrame.writeTo(table) creates a write configuration builder for v2 sources, used to append to, create or replace tables. DataFrameWriterV2.partitionedBy(col, *cols) partitions the output table created by create, createOrReplace or replace with the given columns or transforms, so the table data is stored by those values for efficient reads.
A developer created the temporary view sales_v and cached it. What does spark.catalog.dropTempView("sales_v") do?
AIt drops the view but keeps its cached data for later queries
BIt drops the view, uncaches it, and returns True
CIt deletes the table that the view reads from
DIt returns False, because a cached view cannot be dropped
Show the answer and why
AIt drops the view but keeps its cached data for later queries
Incorrect
If the view has been cached, dropping it also uncaches it.
BIt drops the view, uncaches it, and returns True
Correct
dropTempView drops the local temporary view, uncaches it if it was cached, and returns true if the view was dropped.
CIt deletes the table that the view reads from
Incorrect
Dropping a temporary view removes the view, not the data it reads.
DIt returns False, because a cached view cannot be dropped
Incorrect
Caching does not prevent dropping; the view is dropped and uncached.
Catalog.dropTempView(viewName) drops the local temporary view with the given name in the catalog. If the view has been cached before, it is also uncached. It returns true if the view is dropped successfully and false otherwise; querying the name afterwards raises an AnalysisException.
A developer runs spark.sql("CREATE TEMPORARY VIEW sales.recent AS SELECT * FROM sales.orders WHERE day > '2026-11-01'"). What happens?
AThe view is created in the schema sales, next to sales.orders
BThe view is created as a global temporary view named recent
CThe qualifier is ignored and the view is created as recent
DThe statement is rejected, because a temporary view can be qualified only with session or system.session
Show the answer and why
AThe view is created in the schema sales, next to sales.orders
Incorrect
A temporary view does not live in a regular schema; the qualifier sales is not accepted.
BThe view is created as a global temporary view named recent
Incorrect
GLOBAL is not in the statement, so it asks for a local temporary view.
CThe qualifier is ignored and the view is created as recent
Incorrect
A qualifier other than session or system.session is rejected, not ignored.
DThe statement is rejected, because a temporary view can be qualified only with session or system.session
Correct
For a temporary view the name may be optionally qualified with the session schema, and any other qualifier is rejected with INVALID_TEMP_OBJ_QUALIFIER.
In CREATE [GLOBAL] TEMPORARY VIEW, a temporary view's name may be qualified only with the session schema, session or system.session; any other qualifier is rejected with INVALID_TEMP_OBJ_QUALIFIER. A persistent view, by contrast, may be qualified with a database name (or a catalog and database).