Spark SQL and data sources

A temporary view is only a name on a lazy plan. It is scoped to the session and stores no bytes. It is the closest Spark analogue of a dbt ref(). It does not cache or persist anything.

Source: https://spark.apache.org/docs/4.2.0/sql-getting-started.html

overwrite replaces the existing data. append would add a second full copy on top of the existing data. This doubles counts and sums. It is the classic refresh-vs-append bug.

Source: https://spark.apache.org/docs/4.2.0/sql-data-sources-load-save-functions.html#save-modes

The format.path syntax (parquet/orc/json/csv/text) lets SQL read a file path directly. It is like pointing Athena at an S3 prefix. If you reuse the data, register a temp view so the name is stable.

Source: https://spark.apache.org/docs/4.2.0/sql-data-sources-load-save-functions.html#run-sql-on-files-directly

inferSchema needs an extra pass and may give columns the wrong type. For example, it may read a zero-padded id as an integer, or give an empty column the wrong type. An explicit StructType is deterministic and cheaper. It is the same discipline as typing a dbt source.

Source: https://spark.apache.org/docs/4.2.0/sql-data-sources-csv.html

partitionBy writes one directory for each value. A query that filters that column skips whole directories (partition pruning). This is the analogue of Hive/Glue partitions or a Redshift zone map.

Source: https://spark.apache.org/docs/4.2.0/sql-data-sources-load-save-functions.html

If you partition by a high-cardinality key like order_id, Spark creates a directory for each id and a flood of tiny files (the small-file problem). Partition by low-cardinality columns that queries filter on (date, company), and sort within the partitions.

Source: https://spark.apache.org/docs/4.2.0/sql-data-sources-load-save-functions.html

For a managed table, Spark owns the data lifecycle, so DROP TABLE deletes the files. An external table (created with an explicit LOCATION/path) keeps its files on DROP. This is the same managed-vs-external distinction as in Iceberg/Glue.

Source: https://spark.apache.org/docs/4.2.0/sql-data-sources-hive-tables.html

Spark SQL and the DataFrame API are two front ends over the same Catalyst-optimized engine. The same query compiles to the same plan in both. Choose the one that reads more clearly.

Source: https://spark.apache.org/docs/4.2.0/sql-programming-guide.html