saveAsTable with partitionBy, bucketBy and sortBy

1.Persistent tables with saveAsTable

A DataFrame lives only as long as your session. To keep its contents so that other queries and sessions can use them, you write it to a table that is registered in the catalog. The DataFrameWriter that you get from df.write does this with saveAsTable(name, format=None, mode=None, partitionBy=None, **options). The simplest call needs only a name:

spark.createDataFrame([
    (100, "Alice"), (120, "Bob"), (140, "Tom")],
    schema=["age", "name"]
).write.saveAsTable("tblA")

That call creates a managed table: the platform decides where its files are stored. An external table points at a storage location that you choose. In SQL you add the EXTERNAL keyword, and the CREATE TABLE reference says that "When creating an external table you must also provide a LOCATION clause." The practical difference shows up when you drop the table. Dropping an external table leaves its files where they are. With the v2 writer, setting tableProperty("location", ...) creates an external (unmanaged) table. If you leave out the format, Databricks uses its default: in SQL, "If USING is omitted, the default is DELTA."

The mode parameter (or the chained .mode() method) controls what happens when the target already exists. On a table, each mode has some details that the exam likes to test:

Column matching also matters. insertInto places columns by position. saveAsTable is different: it "uses column names to find the correct column positions." So if a DataFrame has the right columns in a different order, it still lands correctly through saveAsTable in append mode.

Sources123

2.partitionBy: one directory per value

A persistent table helps retrieval when its files are arranged so that a query can skip the ones it doesn't need. partitionBy(*cols) is the first tool for this. It splits the output by the values of the columns you name. The reference describes the result: "the output is laid out on the file system similar to Hive's partitioning scheme." Each distinct value becomes a directory named column=value, and the partition column moves out of the data files and into the path.

import tempfile, os
with tempfile.TemporaryDirectory(prefix="partitionBy") as d:
    spark.createDataFrame(
        [{"age": 100, "name": "Alice"}, {"age": 120, "name": "Ruifeng Zheng"}]
    ).write.partitionBy("name").mode("overwrite").format("parquet").save(d)

    # Read one partition as a DataFrame.
    spark.read.parquet(f"{d}{os.path.sep}name=Alice").show()

partitionBy works with path-based writes (save, parquet, orc) and also with saveAsTable. You can chain it as a method, or pass it as the partitionBy argument of saveAsTable. It accepts several columns, as in partitionBy("year", "month"), which gives a directory hierarchy. Choose columns that queries commonly filter on, because a filter on a partition column is what lets whole directories be skipped.

df.write \
    .mode("overwrite") \
    .format("parquet") \
    . ? ("year") \
    .saveAsTable("partitioned_table")

Sources4

3.bucketBy and sortBy: sorting inside buckets

Partitioning breaks down when a column has many distinct values, because you get one directory per value. Bucketing takes a different approach. bucketBy(numBuckets, col, *cols) hashes the column values into a fixed number of buckets. The bucketBy reference says the layout is similar to Hive's, "with a different bucket hash function and is not compatible with Hive's bucketing." It also limits where bucketing can be used: it is "Applicable for file-based data sources in combination with DataFrameWriter.saveAsTable." You can't bucket a plain save(path). A bucketed layout exists only as a persistent table.

spark.createDataFrame([
    (100, "Alice"), (120, "Alice"), (140, "Bob")],
    schema=["age", "name"]
).write.bucketBy(1, "name").sortBy("age").mode(
    "overwrite").saveAsTable("sorted_bucketed_table")

In this example, sortBy is paired with bucketBy and the result is written with saveAsTable. That matches what the method does: "each bucket" only makes sense once there are buckets. When the example reads the table back, it still calls .sort("age"). The on-disk sort is a storage optimisation, not a promise about the order of query results. The writer's own method table puts the four layout methods side by side:

Sources56

4.The same layout in SQL and DataFrameWriterV2

Each writer method has a counterpart in CREATE TABLE [USING]. Among the table clauses are PARTITIONED BY, CLUSTER BY, LOCATION and a bucketing clause. In that clause, sorting appears inside the bucket definition, just as sortBy belongs with bucketBy:

clustered_by_clause
  { CLUSTERED BY ( cluster_column [, ...] )
    [ SORTED BY ( { sort_column [ ASC | DESC ] } [, ...] ) ]
    INTO num_buckets BUCKETS }

Note the vocabulary clash. SQL CLUSTERED BY ... INTO n BUCKETS is bucketing, which corresponds to bucketBy. SQL CLUSTER BY and the clusterBy method are a separate clustering feature.

The third route is DataFrameWriterV2, which you reach with df.writeTo(table). Its partition method is spelled partitionedBy, and it can "Partition the output table created by create, createOrReplace, or replace using the given columns or transforms." Transforms let you partition on a value derived from a column without adding that column yourself:

from pyspark.sql.functions import years, months, days

df.writeTo("my_table") \
    .partitionedBy(years("date"), months("date")) \
    .create()

V2 also gives you finer control when rewriting partitioned data. Instead of the all-or-nothing mode("overwrite"), overwritePartitions() replaces only the partitions the DataFrame touches, and overwrite(condition) replaces only the rows that match a filter. The reference also notes that for Databricks tables and Delta Lake, V2 offers "More fine-grained control over partitioning" and "Conditional overwrite capabilities".

Sources3

5.When partitioning actually helps retrieval

Every earlier section treated partitioning as a way to skip data. On Databricks that only works when the table is large enough. The guidance says that partitioning below the minimum sizes "is likely to negatively affect query performance rather than improve it." The thresholds are based on size:

The reason is file count. "Tables with fewer, larger partitions tend to outperform tables with many smaller partitions." A high-cardinality partition column creates many small directories, and that costs more than pruning saves. A small Delta table loses little by skipping partitioning: "By using Delta Lake, unpartitioned tables automatically use ingestion time clustering", which gives benefits similar to partitioning on a datetime field.

One more caution concerns the directory layout from the partitionBy section. Spark writes Hive-style partition directories for Parquet, but "Hive-style partitioning is not part of the Delta Lake protocol." Workloads should not reach into a Delta table's column=value folders the way the Parquet example read name=Alice. Use the supported table APIs instead.

Sources7

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.If you don't set a mode, saveAsTable appends to (or overwrites) an existing table.Why is that wrong?

   The default mode is error/errorifexists, so writing to an existing table without a mode throws an exception.

   Covered in Persistent tables with saveAsTable

2. 2.saveAsTable, like insertInto, matches DataFrame columns to table columns by position.Why is that wrong?

   saveAsTable matches columns by name. insertInto is the one that matches by position.

   Covered in Persistent tables with saveAsTable

3. 3.sortBy on the writer sorts the whole DataFrame, so queries on the table return rows in that order.Why is that wrong?

   sortBy orders the data within each bucket on disk. It is a storage layout, and query results still need an explicit sort.

   Covered in bucketBy and sortBy: sorting inside buckets

4. 4.Partitioning always speeds up queries, so even small tables should be partitioned on their filter column.Why is that wrong?

   Databricks says not to partition tables under 1 TB and to keep each partition at 1 GB or more. Below those sizes, partitioning tends to hurt performance.

   Covered in When partitioning actually helps retrieval