Skip to content

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?

  1. Acustomer_id
  2. Bevent_date
  3. Cevent_ts
  4. 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.

Question 2 · choose 1

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?

  1. AThe statement fails with an analysis exception, because the location is a non-empty directory
  2. BThe table is created over the old files, and the query's rows are added next to them
  3. CThe old files are replaced, so the table holds exactly the query's rows
  4. 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.

Question 3 · choose 1

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?

  1. ARun spark.catalog.refreshTable("daily_counts") before the query
  2. BCall daily.cache() before creating the view
  3. CReplace createOrReplaceGlobalTempView with createGlobalTempView
  4. 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.

Question 4 · choose 1

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?

  1. Adf.writeTo("sales.daily").partitionedBy(F.col("day")).create()
  2. Bdf.writeTo("sales.daily").append().partitionedBy(F.col("day"), F.col("store"))
  3. Cdf.writeTo("sales.daily").partitionBy("day").save()
  4. Ddf.write.partitionedBy("day").saveAsTable("sales.daily")
Show the answer and why
  • Adf.writeTo("sales.daily").partitionedBy(F.col("day")).create()

    Correct

    writeTo returns a DataFrameWriterV2, and partitionedBy partitions the output table created by create, createOrReplace or replace.

  • Bdf.writeTo("sales.daily").append().partitionedBy(F.col("day"), F.col("store"))

    Incorrect

    append appends the data to the output table and creates nothing; partitionedBy applies only to a table created by create, createOrReplace or replace.

  • Cdf.writeTo("sales.daily").partitionBy("day").save()

    Incorrect

    partitionBy and save belong to the DataFrameWriter (df.write); the V2 builder uses partitionedBy and create.

  • Ddf.write.partitionedBy("day").saveAsTable("sales.daily")

    Incorrect

    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.

Question 5 · choose 1

A developer created the temporary view sales_v and cached it. What does spark.catalog.dropTempView("sales_v") do?

  1. AIt drops the view but keeps its cached data for later queries
  2. BIt drops the view, uncaches it, and returns True
  3. CIt deletes the table that the view reads from
  4. 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.

Question 6 · choose 1

A developer runs spark.sql("CREATE TEMPORARY VIEW sales.recent AS SELECT * FROM sales.orders WHERE day > '2026-11-01'"). What happens?

  1. AThe view is created in the schema sales, next to sales.orders
  2. BThe view is created as a global temporary view named recent
  3. CThe qualifier is ignored and the view is created as recent
  4. 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).

Databricks documentation

Practice domain 2 →Practice all domains →