What you will be able to do
- Query ORC, JSON, CSV, text and Parquet files from SQL with read_files, and say what schema each format returns
- Query a file or Delta table by path with format.
pathsyntax, and say what you lose compared with read_files or a temporary view - Pick the DataFrameWriter save mode (append, overwrite, error/errorifexists, ignore) that matches what you want when the target already has data
- Tell INSERT INTO apart from INSERT OVERWRITE when writing results with SQL
Key concept
Querying files without a table — Spark SQL can read files where they sit. You name a path and a format, and the files come back as a table you can query, with no table created first. Which method you use, read_files, a format.path reference or a temporary view, controls whether you can also pass a schema and reader options.
1.read_files: one SQL function for many file formats
Normally SQL reads from tables. On Databricks you can also run SQL against files in storage, and the main tool for that is the read_files table-valued function. It is available in Databricks SQL and Databricks Runtime 13.3 LTS and above. Pass it a path, plus options given as named parameters (option => value), and it returns the file contents as a table. One function handles most of the formats this objective lists. It also covers Parquet, XML, Avro and raw binary files.
| Format | Schema of the result |
|---|---|
| TEXT | Fixed: a single value column of type STRING |
| BINARYFILE | Fixed: path, modificationTime, length, content |
| JSON, CSV, XML, PARQUET, AVRO, ORC | Inferred from the file contents, or given explicitly with the schema option |
The path can point to a single file or to a directory. For a directory, read_files finds every file underneath it recursively unless you supply a glob such as *, ?, [a-z] or {ab,cd} to narrow the search. If you don't give a schema, the function infers one schema that fits all the files it found, and that means reading all of them unless the query has a LIMIT. The notebook and the SQL editor add a LIMIT to SELECT queries automatically when you leave it out. Hive-style directories such as /column_name=column_value/ become partition columns. If a column name appears both in the directory path and inside the data, the directory value is used.
No. read_files provides a hidden _metadata column with file_path, file_name, file_size, file_modification_time and block details, but SELECT * does not include it. You have to ask for it by name:
SELECT * EXCEPT (content), _metadata
FROM read_files('/Volumes/my_catalog/my_schema/my_volume', format => 'binaryFile');Checkpoint 1 of 9· Check yourself
A colleague says SELECT * FROM read_files('/Volumes/c/s/v/logs', format => 'json') already includes the source file name for every row. What is correct?
read_files does expose a _metadata column with file_name and file_path, but SELECT * leaves it out. You have to name it in the select list.
“This column is not included in SELECT * results and must be explicitly selected.”Source: docs.databricks.com
Sources1
2.ORC, JSON, CSV and text files from SQL
The call looks the same for every format. Only the format value and a few format-specific options change. ORC is a columnar format that uses built-in indexes and statistics to skip data it doesn't need. Querying it takes nothing more than a path and format => 'orc':
SELECT * FROM read_files(
'/Volumes/<catalog>/<schema>/<volume>/reviews_orc',
format => 'orc'
)JSON is read in single-line mode by default, where each line holds one complete JSON object. When a single record spans several lines, turn on multiLine:
SELECT * FROM read_files(
'/Volumes/<catalog>/<schema>/<volume>/reviews_json',
format => 'json',
multiLine => true)For CSV, read_files is the method Databricks recommends to SQL users. The usual options are header and a parser mode. PERMISSIVE is the default and fills unparseable fields with nulls. DROPMALFORMED drops the bad lines, and FAILFAST stops the read at the first malformed line. Be careful with partial schemas: CSV has no column names stored in the file, so a schema that doesn't match the file layout puts values into the wrong columns.
SELECT * FROM read_files(
's3://<bucket>/<path>/<file>.csv',
format => 'csv',
header => true,
mode => 'FAILFAST')Text is the simplest format. Each line of each file becomes one row, held in a single value column of type StringType, which suits log parsing and raw ingestion.
SELECT * FROM read_files(
'/Volumes/<catalog>/<schema>/<volume>/review_comments',
format => 'text'
)Checkpoint 2 of 9· Match them up
Match each read_files format to how it behaves
Tap a term, then the definition that fits it.
Text is the one format here with a fixed schema. CSV matches columns by position, JSON needs multiLine for records that span lines, and ORC is a columnar format with built-in indexes.
“TEXT: Returns a fixed schema with a single value (STRING) column.”Source: docs.databricks.com
3.Format.path references and temporary views
read_files is not the only way to query a file from SQL. You can also put the path in the FROM clause, using the format name as a qualifier and the path in backticks. The SQL table-reference docs show this with a CSV file:
-- Return a data set from a storage location using a credential.
> SELECT * FROM `csv`.`spreadsheets/data.csv` WITH(CREDENTIAL some_credential);The path form is short, but for CSV you pay for that: the docs list two limits when you skip read_files and temporary views. You can't specify data source options, and you can't specify the schema. If you need options and still want a name you can query repeatedly, create a temporary view with USING <format> and put the path and reader options in OPTIONS. The view exists only for the current session and is dropped when the session ends.
CREATE TEMPORARY VIEW diamonds
USING CSV
OPTIONS (path "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv", header "true", mode "FAILFAST");
SELECT * FROM diamonds;JSON works the same way with USING json. Databricks still prefers read_files over USING JSON, because read_files lets you specify a schema and more file-processing options.
Checkpoint 3 of 9· Check yourself
You need SQL over CSV files with header => true and an explicit schema, and you don't want to create a persistent table. Which approach do the docs recommend?
read_files is the recommended way for SQL users to read CSV, and it accepts both reader options and a schema. A plain path query accepts neither.
“Databricks recommends the read_files table-valued function for SQL users to read CSV files.”Source: docs.databricks.com
Checkpoint 4 of 9· Exam question
A data engineer has a directory of newline-delimited JSON files at `/mnt/raw/events/` and wants to inspect the data without first creating a table or DataFrame. They run: ```sql SELECT count(*) FROM json.`/mnt/raw/events/` ``` The directory contains 3 files, each holding 100 valid JSON records that share a consistent schema. What does this query return?
Correct answer: B — 300, because `json.`path`` treats the whole directory as one relation and Spark infers a merged schema from a sample of records across all files it lists
- A. No table registration step is required; the `format.`path`` syntax lets Spark SQL read a directory of files directly as a relation without a `CREATE TABLE` statement.
- B. Referencing `json.`path`` in SQL treats the whole directory as a single relation, and Spark infers one schema by sampling records across the files before scanning all of them, so all 300 matching rows are counted.
- C. The directory reference is not limited to a single file; Spark lists and reads every file under the path that matches the format, so all three files contribute rows.
- D. Schema inference samples records to build one schema, but it does not silently drop files with mismatched schemas during the count; a genuinely incompatible file would typically surface a read error rather than being skipped quietly.
- E. No such `WITH SCHEMA` clause exists for this syntax; schema is inferred automatically from the JSON content unless the caller supplies an explicit schema through the DataFrame reader API instead.
4.Delta files: query the table by its path
Delta is not in read_files' format list. The list covers JSON, CSV, XML, TEXT, BINARYFILE, PARQUET, AVRO and ORC. Delta is still the format you will query most often, because it is the default on Databricks, while Apache Spark defaults to Parquet. A Delta table directory isn't a loose collection of files. You query it either by its name in the metastore or by its storage path using the delta. prefix:
SELECT * FROM default.people10m -- query table in the metastore
SELECT * FROM delta.`/tmp/delta/people10m` -- query table by pathCheckpoint 5 of 9· Check yourself
Your Delta table lives at /tmp/delta/people10m and has no entry in the metastore. Which statement queries it by its storage path?
The delta. prefix with the location in backticks queries a Delta table by path. default.people10m is the metastore name form, and Delta is not in read_files' format list.
“SELECT * FROM delta.`/tmp/delta/people10m` -- query table by path”Source: docs.delta.io
The pattern is the same: format name as a qualifier, then the location in backticks. The difference is that delta.path reads a Delta table stored at that location. The DataFrame equivalent is spark.read.format("delta").load(path).
For anything registered as a table, Databricks recommends querying by table name. The path form is for a Delta table that has no name in the metastore, or when you only know its storage location. The path form also works with time travel, as in the example below.
SELECT * FROM delta.`/tmp/delta/people10m` VERSION AS OF 1235.Save modes: what happens when the target already exists
Reading files is half of the objective. The other half is writing results out, and the key question is what Spark does when data is already at the target. DataFrameWriter.mode() controls this, and it accepts four behaviours, with two names for one of them.
| saveMode value | Behaviour if data exists |
|---|---|
| 'append' | Append to existing data |
| 'overwrite' | Overwrite existing data |
| 'error' or 'errorifexists' | Throw an exception |
| 'ignore' | Silently skip the write |
The reference example writes one row with overwrite, which replaces whatever was in the directory, then adds a second row with append. Reading the directory back returns both rows. Note that the mode is set on the writer before save(), and that format() and mode() can be chained in either order.
import tempfile
with tempfile.TemporaryDirectory(prefix="mode") as d:
# Overwrite the path with a new Parquet file
spark.createDataFrame(
[{"age": 100, "name": "Alice"}]
).write.mode("overwrite").format("parquet").save(d)
# Append another DataFrame into the Parquet file
spark.createDataFrame(
[{"age": 120, "name": "Sue"}]
).write.mode("append").format("parquet").save(d)Checkpoint 6 of 9· Fill the gap
This ORC write should replace whatever is already at the path. Which mode completes it?
df.write.format("orc").mode(" ? ").save("/Volumes/<catalog>/<schema>/<volume>/reviews_orc")overwrite replaces the existing data. append would add to it, ignore would skip the write, and errorifexists would fail because data is present.
Source: docs.databricks.comCheckpoint 7 of 9· Exam question
A engineer receives a CSV export where the first line holds column names and numeric columns should not be read as strings. They write: ```python df = spark.read.csv("/mnt/raw/sales.csv") df.createOrReplaceTempView("sales") spark.sql("SELECT avg(amount) FROM sales") ``` The query fails because `amount` is read as a string and the header row `product,amount,region` appears as a data row. Which change fixes both problems?
Correct answer: C — Add the `header` and `inferSchema` options to the CSV reader so the first row becomes column names and numeric columns are inferred as doubles rather than strings
- A. Casting inside the SQL string would fix the type for that one query, but the header row `product,amount,region` still appears as a data row and pollutes the average, so the underlying read is still broken.
- B. Reading as plain text and manually parsing commas abandons the structured CSV reader entirely and reintroduces the same header and typing problems by hand, with far more code to maintain.
- C. Setting `header` to `True` tells the reader to treat the first line as column names rather than data, and `inferSchema` scans the data to assign numeric types such as double instead of defaulting every column to string.
- D. Filtering out a row that matches the header text is a fragile workaround that still leaves every column as a string, so the average would still fail or return an incorrect result without an explicit cast.
- E. Querying `csv.`path`` directly still applies Spark's CSV defaults, which do not treat the first line as a header or infer types unless those same reader options are supplied, so the failure would persist.
SQL has its own version of the append/overwrite choice for tables, set by the keyword after INSERT. INSERT INTO is additive: the new rows go in alongside the existing ones. INSERT OVERWRITE with no PARTITION clause truncates the whole table before inserting the first row. With a partition spec, only the matching partitions are truncated. Dynamic-partition INSERT OVERWRITE requires the target to be a Delta Lake table.
Checkpoint 8 of 9· Check yourself
INSERT OVERWRITE sales SELECT * FROM staging_sales runs with no PARTITION clause. What happens to rows in sales that have no match in staging_sales?
Without a partition spec, INSERT OVERWRITE truncates the entire table first, so only the query's rows are left.
“Without a partition_spec the table is truncated before inserting the first row.”Source: docs.databricks.com
Checkpoint 9 of 9· Exam question
A team stores historical clickstream data as ORC files under `/mnt/archive/clicks/` and wants a quick row count for a specific `event_date` partition column without creating a table. Which SQL statement queries the ORC files directly and filters on that column?
Correct answer: D — `SELECT count(*) FROM orc.`/mnt/archive/clicks/` WHERE event_date = '2026-01-15'`, treating the path as a relation and filtering the resulting rows
- A. There is no `ORC_TABLE` table-valued function in Spark SQL; file-based relations for built-in formats are addressed with the `format.`path`` syntax, not a function call.
- B. `read_orc` is not a Spark SQL function; reading files by format from SQL uses the `format.`path`` relation syntax rather than a reader function invoked in the `FROM` clause.
- C. There is no `LOAD ORC ... AS` statement in Spark SQL; loading a file-based relation for querying is done with the `format.`path`` syntax, not a two-step load-then-alias statement.
- D. Spark SQL supports `format.`path`` syntax for several built-in formats including ORC, so prefixing the path with `orc.` and backticks turns the directory into a queryable relation that a normal `WHERE` clause can filter.
- E. Format is not declared with a trailing `USING` clause on a quoted path string; the format name is prefixed onto the backticked path itself, as in `orc.`path``.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.SELECT * over read_files already includes the source file path and name for each row.Why is that wrong?
The file-level fields are in a separate _metadata column, which SELECT * leaves out. You have to select _metadata by name.
Covered in read_files: one SQL function for many file formats
2.Querying a CSV file directly by path in SQL still lets you set header, delimiter or a schema.Why is that wrong?
Reading CSV with SQL directly, without read_files or a temporary view, allows neither data source options nor a schema.
Covered in Format.
pathreferences and temporary views3.mode('ignore') raises an error when the target already contains data.Why is that wrong?
ignore skips the write silently. Only 'error' / 'errorifexists' throws an exception when data exists.
Covered in Save modes: what happens when the target already exists
4.INSERT OVERWRITE with no PARTITION clause replaces only the rows that match the incoming data.Why is that wrong?
Without a partition spec, the whole table is truncated before the first row is inserted.
Covered in Save modes: what happens when the target already exists
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Supports reading JSON, CSV, XML, TEXT, BINARYFILE, PARQUET, AVRO, and ORC file formats.”
↩︎ read_files: one SQL function for many file formats“read_files discovers all files under the provided directory recursively unless a glob is provided”
↩︎ read_files: one SQL function for many file formats“read_files attempts to infer a unified schema across the discovered files, which requires reading all the files unless a LIMIT statement is used.”
↩︎ read_files: one SQL function for many file formats“the value that is read from the partition value is used instead of the data value.”
↩︎ read_files: one SQL function for many file formats“This column is not included in SELECT * results and must be explicitly selected.”
↩︎ Exam trap 1“This column is not included in SELECT * results and must be explicitly selected.”
↩︎ Checkpoint“TEXT: Returns a fixed schema with a single value (STRING) column.”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/query/formats/orcOfficial docs
“Use read_files to query ORC files directly from cloud storage using SQL without creating a table.”
↩︎ ORC, JSON, CSV and text files from SQL“Use read_files to query ORC files directly from cloud storage using SQL without creating a table.”
↩︎ Key concept - 3.
“In single-line mode (the default), each line of the output contains one complete JSON object.”
↩︎ ORC, JSON, CSV and text files from SQL“Databricks recommends using read_files instead of USING JSON because read_files allows the specification of schema and additional file processing options.”
↩︎ Format.pathreferences and temporary views - 4.https://docs.databricks.com/aws/en/query/formats/csvOfficial docs
“CSV has no column-name metadata, so Spark maps schema fields to columns by position”
↩︎ ORC, JSON, CSV and text files from SQL“If you use SQL to read CSV data directly without using temporary views or read_files, the following limitations apply:”
↩︎ Format.pathreferences and temporary views“The view is visible only to the current session and is dropped when the session ends.”
↩︎ Format.pathreferences and temporary views“You can't specify the schema for the data.”
↩︎ Exam trap 2“Databricks recommends the read_files table-valued function for SQL users to read CSV files.”
↩︎ Checkpoint - 5.
“The text format reads each line of a text file as a row in a DataFrame with a single value column of type StringType.”
↩︎ ORC, JSON, CSV and text files from SQL - 6.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-table-referenceOfficial docs
“SELECT * FROM `csv`.`spreadsheets/data.csv` WITH(CREDENTIAL some_credential);”
↩︎ Format.pathreferences and temporary views - 7.https://docs.databricks.com/aws/en/query/formatsOfficial docs
“Databricks uses Delta Lake as the default protocol for reading and writing data and tables, whereas Apache Spark uses Parquet.”
↩︎ Delta files: query the table by its path - 8.https://docs.delta.io/delta-batchSecondary source
“SELECT * FROM delta.`/tmp/delta/people10m` -- query table by path”
↩︎ Delta files: query the table by its path - 9.https://docs.databricks.com/aws/en/queryOfficial docs
“For all data registered as a table, Databricks recommends querying using the table name.”
↩︎ Delta files: query the table by its path - 10.
“Specifies the behavior when data or table already exists.”
↩︎ Save modes: what happens when the target already exists“'error' or 'errorifexists' (throw an exception if data exists)”
↩︎ Exam trap 3“'ignore' (silently skip if data exists)”
↩︎ Prediction - 11.
“If you specify INTO all rows inserted are additive to the existing rows.”
↩︎ Save modes: what happens when the target already exists“Without a partition_spec the table is truncated before inserting the first row.”
↩︎ Save modes: what happens when the target already exists“Without a partition_spec the table is truncated before inserting the first row.”
↩︎ Exam trap 4