4.2 Direct File SQL, Save Modes, and Persistent Layout

Databricks Certified Associate Developer for Apache Spark — Free Study Guide 2026 Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Spark Architecture and Execution

1.1 Spark Benefits, Modules, and DataFrame/Dataset Concepts 1.2 Driver, Executors, and Cluster Managers 1.3 Execution Hierarchy: Applications, Jobs, Stages, and Tasks 1.4 Lazy Evaluation, Transformations vs Actions, and Resilient Lineage

2 Chapter 2: Shuffles, Memory and Persistence

2.1 Narrow vs Wide Dependencies and the Shuffle Mechanism 2.2 Memory Management, Fault Tolerance, and Garbage Collection 2.3 Caching, Persistence, and StorageLevels

3 Chapter 3: Spark SQL Sessions, Tables and Views

3.1 SparkSession, Catalogs, Databases, and Namespaces 3.2 Managed vs External Tables and CTAS 3.3 Views: Temporary vs Global Temporary Views

4 Chapter 4: SQL Sources and Stored Layout

4.1 JDBC Reads, Writes, and Parallelism 4.2 Direct File SQL, Save Modes, and Persistent Layout

5 Chapter 5: SQL Queries and Analytics

5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions 5.2 Built-in SQL Expressions and an Integrated Query 5.3 Window Functions and Complex Joins in Spark SQL 5.4 SQL Join Semantics and Integrated Analytics

6 Chapter 6: DataFrame Construction and Columns

6.1 DataFrame Construction, Schemas, and StructTypes 6.2 Column Operations: Selecting, Renaming, Deriving, and Casting 6.3 Complex Nested Types and User-Defined Functions

7 Chapter 7: DataFrame Validation and Inspection

7.1 Row Operations: Filtering, Sorting, and Deduplication 7.2 Handling Missing and Null Data 7.3 Schema Inspection, Row Iteration, and List Conversion

8 Chapter 8: Aggregations and Dates

8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting 8.2 Dates, Unix Epoch, and Time-Zone Conversions

9 Chapter 9: DataFrame Joins and File IO

9.1 DataFrame Joins and Combining Datasets 9.2 Data Sources, SaveModes, and Partitioning

10 Chapter 10: Shared Variables and Stateful Processing

10.1 Broadcast Variables, Accumulators, and Closures 10.2 Stateless UDFs, Stateful Operators, and StateStores

11 Chapter 11: Troubleshooting and Tuning

11.1 Diagnosing Performance with the Spark UI & Execution Plans 11.2 Data Skew and Partition Tuning: repartition vs coalesce 11.3 Join Optimization and Broadcast Hash Joins

12 Chapter 12: Structured Streaming

12.1 Streaming Architecture, Sources, and Sinks 12.2 Output Modes, Triggers, and Checkpointing 12.3 Event-Time Processing, Watermarking, and Windowing 12.4 Streaming Deduplication With and Without Watermarks

13 Chapter 13: Spark Connect and Deployment

13.1 Spark Connect Architecture & Client-Server Decoupling 13.2 Working with Spark Connect: Connections and Best Practices

14 Chapter 14: Pandas API on Spark

14.1 Pandas API on Apache Spark: Core Architecture and Syntax 14.2 Pandas-on-Spark Operations, Indexing, and Optimization

4.2 Direct File SQL, Save Modes, and Persistent Layout

Key Takeaways Explain This Topic AI

Use provider. path syntax for supported direct file SQL; quotes around a string path are not a table identifier.

CSV/text need deliberate parsing and schema choices; Delta queries require Delta support.

partitionBy organizes stored directories, while repartition changes distributed execution partitions.

Save modes govern existing targets; file overwrite is not automatically transactional.

Last updated: October 2026

SQL without a registered table

A file path can be queried through a supported data-source provider without first saving a persistent catalog table. Spark SQL uses the provider name followed by a dot and a backtick-delimited path. This is an identifier syntax, not a quoted string literal in the FROM clause. It is useful for examining an export or applying a one-off transformation to data already stored in a supported format. SELECT region, SUM(amount) AS revenue

• FROM parquet.`/data/orders`
• GROUP BY region;

• SELECT * FROM orc.`/data/events_orc`;
• SELECT * FROM json.`/data/events_json`;
• SELECT * FROM csv.`/data/events_csv`;
• SELECT value FROM text.`/data/application_logs`;
• SELECT * FROM delta.`/data/delta_orders`;

These examples assume accessible paths and installed providers. Delta Lake is a transactional table format built on data files and a log; plain Apache Spark needs the Delta dependency/configuration. Do not read a Delta table as raw Parquet and assume that deleted, replaced or uncommitted files have been excluded correctly. A Delta reader resolves the log-defined snapshot.

Parsing is part of correctness

A direct CSV query uses default reader options unless an options-aware relation is defined. A header can otherwise become a data row and columns can appear as _c0, _c1 and so on. CSV values default to strings unless type inference or a supplied schema changes them. JSON typically infers its schema; line-delimited JSON differs from a multiline JSON document. Text exposes each line through a string value column, rather than interpreting a CSV or JSON payload automatically.

Use a temporary data-source view when column names, types or reader options matter: CREATE OR REPLACE TEMPORARY VIEW csv_orders (

• order_id BIGINT, region STRING, amount DOUBLE
• )
• USING csv
• OPTIONS (path '/data/orders_csv', header 'true', mode 'FAILFAST');

• SELECT region, COUNT(*) AS n, SUM(amount) AS revenue
• FROM csv_orders
• GROUP BY region;

Equivalent Python code can use spark.read.schema(...).option("header", True).csv(path) followed by createOrReplaceTempView(). The view registers a queryable relation; it does not copy source rows into driver memory or persist a new table. For permissive parsing, explicitly inspect malformed-record and null rates rather than treating successful execution as proof of valid input.

Four write modes

• Mode Existing destination Important limitation
• error/errorifexists Fail Default protection against an existing target
• append Add output Does not deduplicate repeat runs
• overwrite Replace according to provider/options Generic file writes are not a transaction

ignore Skip writing if target exists Does not validate that existing content is the desired version cleaned = spark.sql("SELECT order_id, region, amount FROM csv_orders WHERE amount >= 0")

• (cleaned.write.format("parquet")
• .mode("overwrite")
• .partitionBy("region")
• .save("/data/clean_orders"))

The action writes distributed output files. It does not promise one file per region, and overwrite scope depends on provider, table/path operation and partition-overwrite settings. A static overwrite can remove more data than the incoming partition subset; a dynamic partition overwrite policy differs. Name the intended scope before choosing an option. Delta provides transactional commits, but its overwrite and schema policies still need deliberate configuration.

Three meanings often confused

Execution partitions are the units processed by tasks. repartition(8, "region") changes row distribution and normally introduces a shuffle. Directory partitioning via write.partitionBy("region") organizes persisted paths such as region=north/. It can enable partition pruning on a later filtered read. It does not mean a single executor permanently owns north, or guarantee one directory equals one task.

Sorting also has distinct scopes. orderBy establishes a global result ordering, often using range exchange and local sorts. sortWithinPartitions orders each current execution partition and does not establish a global order across all output files. A later query of a table has no guaranteed presentation order without its own ORDER BY, even if files were sorted when written.

• Persistent tables with partitioning and sorting (cleaned.write.format("parquet")
• .mode("overwrite")
• .partitionBy("region")
• .bucketBy(8, "order_id")
• .sortBy("order_id")
• .saveAsTable("orders_layout_demo"))

For a compatible Spark catalog/provider this records table metadata, directory partitions, hash buckets and within-bucket sorting. bucketBy/sortBy in this writer API require saveAsTable, not a generic path save. Bucket count is a logical layout parameter, not a guaranteed physical file count after multiple writers or appends. Delta and Unity Catalog do not universally support these legacy bucket APIs; verify provider capabilities.

A compatible later join can reuse recognized bucket distribution and avoid an exchange, while a mismatch may still need a shuffle or sort. The actual plan is the evidence. Excessive directory cardinality can produce many tiny files: partitioning by near-unique order_id usually makes poor directories, even though it could be a useful join/bucket key in a supported layout.

For a scenario, ask whether the requirement is to read a path, parse its records, reduce scanned directories, organize compatible joins, or display ordered results. Each requires a different API decision. See load/save and direct-file SQL and CSV parsing .