Published November 14, 2026 · By Henry Palma

Spark SQL in PySpark: Temp Views and a Safe Managed Delta Table Write (Part 3 of 3)

Part 3 of a PySpark series: query DataFrames with Spark SQL temp views, match the DataFrame results, and save a managed Delta summary with mode error.

Spark SQL and the PySpark DataFrame API run on the same engine, so the same question should give the same answer in both. This post follows Part 3 of the video series on Databricks Free Edition: register a temporary view, query it with SQL, check the results against the DataFrame version, and save one managed Delta summary that refuses to overwrite anything.

Watch on YouTube

The notebook, source file and cheat sheet are in the Part 3 folder on GitHub. The notebook rebuilds the same eight fictional orders used in Part 1 and Part 2, so it stands alone. Use a Python notebook on Serverless compute and run the six code cells in order.

The number to reconcile against

The input check shows six valid rows (quantity and price both present), five region and category buckets, and 142.50 in revenue. Every SQL check and the saved table must land on that same total.

Query a DataFrame with a temporary view

valid.createOrReplaceTempView("valid_orders")

sqlhigh = spark.sql(""" SELECT orderid, customer, region, category, ordertotal FROM validorders WHERE ordertotal >= 30.00 ORDER BY ordertotal DESC, order_id DESC """) ```

A temporary view lasts for the session and stores no data by itself. It lets you write SQL against a DataFrame you have already shaped and trust, without creating a permanent object.

The query returns orders 108 and 104, at 30.00 each, totaling 60.00. This is a stricter cut than Part 1's 25.00 threshold, so fewer rows remain.

Aggregate in SQL and compare

sql_summary = spark.sql("""
SELECT region, category,
       COUNT(*) AS order_count,
       CAST(SUM(order_total) AS DECIMAL(18,2)) AS revenue
FROM valid_orders
GROUP BY region, category
ORDER BY region, category
""")

The result matches the DataFrame aggregation from Part 2 bucket for bucket: EAST Books 12.00, NORTH Books 20.00, NORTH Toys 60.00, SOUTH Toys 25.00 and WEST Books 25.50, totaling 142.50.

If SQL and DataFrame results ever disagree, stop before writing anything. The cause is usually a filter, a null rule or a join duplication, not the SQL engine.

Create an isolated destination

The notebook creates a new schema named btquery plus a random suffix in your current catalog and prints the fully qualified table name:

demo_schema = "bt_query_" + uuid4().hex[:12]
schema_fqn = qi(catalog_name) + "." + qi(demo_schema)
table_fqn = schema_fqn + "." + qi("region_category_revenue")
spark.sql(f"CREATE SCHEMA {schema_fqn} COMMENT 'Build and Teach fictional query/analyze Part 3 demo'")

qi quotes each identifier with backticks. Copy the printed name, because your suffix will be different and you need it for verification. If you lack permission to create a schema, choose a catalog you can write to rather than working around access controls.

Write once with mode error

region_category.write.format("delta").mode("error").saveAsTable(table_fqn)

mode("error") refuses to replace a table that already exists. It is not overwrite and not append, and there is no drop. Think of it as fail loud on the write side: a second write to the same name stops instead of quietly replacing the summary.

This is a managed Delta table, so its data lives in the catalog's managed storage. There is no external LOCATION. If the write already succeeded, skip that cell and run verification. Rerunning the whole notebook would create another small demo schema.

Verify the stored table

checks = spark.sql(f"""
SELECT COUNT(*) AS bucket_count,
       SUM(order_count) AS total_orders,
       SUM(revenue) AS total_revenue
FROM {table_fqn}
""")

The actual run matched all five expected buckets and the totals: 5 buckets, 6 orders, 142.50 revenue. The cell then printed PASS and a SELECT you can copy into a SQL cell. This check reads the saved table, not a DataFrame variable still in memory. If PASS does not print, read the assertion before rerunning any write.

Key takeaways

Temporary views let you use SQL on DataFrames without creating permanent objects.
The same query in SQL and in the DataFrame API should match. If not, find the difference before you write.
Use a unique schema and mode("error") for new managed tables, so nothing gets replaced.
Reconcile before writing, then verify the stored result with SQL.

Across the three parts you shape rows and check nulls, aggregate and rank, then query in SQL and save a checked summary on Free Edition serverless.