Spark SQL Complete Guide: Essential SQL Syntax for the Databricks Exams

On the Databricks certification exams (DEA, DEP, Spark Developer), precise knowledge of Spark SQL syntax is what separates passing from failing. In particular, the Data Engineer Associate exam's ELT with Spark SQL & Python (29%) domain tests your ability to read and write SQL queries directly. This article systematically organizes the syntax that shows up on the exams — CREATE TABLE, MERGE INTO, window functions, CTEs, Delta Lake operations, higher-order functions, PIVOT/UNPIVOT — with runnable SQL code.

Exam Frequency Map

The table below summarizes how often each Spark SQL construct appears on the Databricks certification exams. Use it to prioritize your study.

Table Operations

There are three main CREATE patterns for Delta Lake tables. Be sure you understand the difference between managed and external tables, and whether DROP TABLE removes the underlying data.

CREATE TABLE USING DELTA (Managed Table)

A CREATE TABLE statement without LOCATION creates a managed table. Unity Catalog manages where the data lives, and DROP TABLE deletes both the metadata and the data files.

-- Managed table: created from a schema definition
CREATE TABLE catalog_name.schema_name.orders (
  order_id   BIGINT,
  customer_id BIGINT,
  order_date DATE,
  amount     DECIMAL(10, 2),
  status     STRING
) USING DELTA
COMMENT 'Customer order transactions'
TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);

CREATE TABLE ... LOCATION (External Table)

Providing an external storage path via the LOCATION clause creates an external table. DROP TABLE only removes the metadata; the data files remain in external storage.

-- External table: the data lives at the given path
CREATE TABLE catalog_name.schema_name.events (
  event_id   BIGINT,
  event_type STRING,
  event_ts   TIMESTAMP,
  payload    STRING
) USING DELTA
LOCATION 's3://my-bucket/bronze/events';

CREATE OR REPLACE TABLE & CTAS

CREATE OR REPLACE TABLE (CORT) recreates an existing table by overwriting it. CTAS (CREATE TABLE AS SELECT) infers the schema from the SELECT result, so you do not need to declare column definitions.

-- CTAS: turn a SELECT result into a table (schema inferred automatically)
CREATE OR REPLACE TABLE silver.daily_summary AS
SELECT
  order_date,
  COUNT(*)          AS order_count,
  SUM(amount)       AS total_amount,
  AVG(amount)       AS avg_amount
FROM bronze.orders
WHERE status = 'completed'
GROUP BY order_date;

Temp View vs Global Temp View

-- Session scope (disappears when the session ends)
CREATE OR REPLACE TEMP VIEW v_active_users AS
SELECT * FROM users WHERE is_active = true;

-- Cluster scope (referenced via global_temp.)
CREATE OR REPLACE GLOBAL TEMP VIEW v_summary AS
SELECT region, COUNT(*) AS cnt FROM sales GROUP BY region;

SELECT * FROM global_temp.v_summary;

Data Manipulation (INSERT / COPY INTO)

INSERT INTO vs INSERT OVERWRITE

INSERT INTO appends to existing data. INSERT OVERWRITE deletes the existing data in the table (or partition) before writing. Note that in Delta Lake, behavior changes based on whether you specify a partition.

-- Append: existing data is kept
INSERT INTO silver.orders
SELECT * FROM bronze.orders_raw
WHERE process_date = current_date();

-- Overwrite: replace the whole table
INSERT OVERWRITE silver.orders
SELECT * FROM bronze.orders_raw;

-- Overwrite by partition (dynamic partition overwrite)
INSERT OVERWRITE silver.orders
PARTITION (order_date)
SELECT order_id, customer_id, amount, status, order_date
FROM bronze.orders_raw;

COPY INTO

COPY INTO loads data from external files (CSV/JSON/Parquet, etc.) into Delta Lake tables. It automatically tracks which files have already been processed, giving it idempotency that prevents the same file from being ingested twice. It is best suited for small batches of up to a few thousand files; for large-scale or continuous ingestion, Auto Loader is the recommended choice.

COPY INTO silver.transactions
FROM 's3://data-lake/landing/transactions/'
FILEFORMAT = JSON
FORMAT_OPTIONS (
  'inferSchema'  = 'true',
  'mergeSchema'  = 'true',
  'dateFormat'   = 'yyyy-MM-dd'
)
COPY_OPTIONS (
  'mergeSchema' = 'true',
  'force'       = 'false'   -- setting true disables duplicate-ingest protection
);

MERGE INTO (Most Frequently Tested)

MERGE INTO matches a source and a target table on an ON condition, then runs INSERT, UPDATE, and DELETE in a single statement through three branches: WHEN MATCHED, WHEN NOT MATCHED, and WHEN NOT MATCHED BY SOURCE. It is the single most frequently tested SQL construct across the Databricks exams.

Basic Upsert Pattern

MERGE INTO gold.customers AS target
USING staging.customer_updates AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
  UPDATE SET
    target.name       = source.name,
    target.email      = source.email,
    target.updated_at = current_timestamp()
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, created_at, updated_at)
  VALUES (source.customer_id, source.name, source.email,
          current_timestamp(), current_timestamp());

Conditional MERGE & DELETE Branches

MERGE INTO gold.products AS t
USING staging.product_feed AS s
ON t.product_id = s.product_id
-- Conditional UPDATE: only when the update date is newer
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET *
-- Soft-delete rows that are missing from the source
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET t.is_active = false, t.deleted_at = current_timestamp()
-- Insert new rows
WHEN NOT MATCHED BY TARGET THEN
  INSERT *;

Implementing SCD Type 2

SCD Type 2 is a dimension-management pattern that preserves change history. You expire the existing record by setting its end_date, then INSERT a new version row.

MERGE INTO dim.customers AS t
USING (
  SELECT s.*, true AS is_new
  FROM staging.customers s
  JOIN dim.customers d
    ON s.customer_id = d.customer_id
   AND d.is_current = true
   AND (s.name <> d.name OR s.email <> d.email)
  UNION ALL
  SELECT s.*, true AS is_new
  FROM staging.customers s
  WHERE NOT EXISTS (
    SELECT 1 FROM dim.customers d
    WHERE d.customer_id = s.customer_id
  )
) AS source
ON t.customer_id = source.customer_id AND t.is_current = true
WHEN MATCHED THEN
  UPDATE SET t.is_current = false, t.end_date = current_date()
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, start_date, end_date, is_current)
  VALUES (source.customer_id, source.name, source.email,
          current_date(), NULL, true);

Delta Lake Operational Commands

OPTIMIZE & ZORDER

OPTIMIZE is a compaction command that merges small files to improve read performance. Combined with ZORDER BY, it colocates data by the specified column values (placing rows with the same column values physically close together), which improves data-skipping efficiency on filter queries.

-- Basic compaction
OPTIMIZE gold.sales;

-- Co-locate data with ZORDER
OPTIMIZE gold.sales ZORDER BY (region, product_id);

-- Limit to specific partitions with a WHERE clause
OPTIMIZE gold.sales
WHERE order_date >= '2026-01-01'
ZORDER BY (customer_id);

VACUUM

Physically removes old data files that are no longer referenced by the current table version, reducing storage costs. Time Travel to the deleted versions becomes unavailable.

-- Default: delete unreferenced files older than 168 hours (7 days)
VACUUM gold.sales;

-- Specify the retention period explicitly
VACUUM gold.sales RETAIN 720 HOURS;  -- 30 days

-- DRY RUN: only list the files that would be deleted (nothing is deleted)
VACUUM gold.sales DRY RUN;

DESCRIBE HISTORY & DESCRIBE DETAIL

-- Transaction history (version, operation, timestamp)
DESCRIBE HISTORY gold.sales;
DESCRIBE HISTORY gold.sales LIMIT 5;

-- Table details (file count, size, partitions, and so on)
DESCRIBE DETAIL gold.sales;

RESTORE TABLE & Time Travel

-- Restore to a specific version (this actually rolls the data back)
RESTORE TABLE gold.sales TO VERSION AS OF 12;

-- Restore as of a timestamp
RESTORE TABLE gold.sales TO TIMESTAMP AS OF '2026-03-20 10:00:00';

-- Time Travel: read a past version only (no restore)
SELECT * FROM gold.sales VERSION AS OF 12;
SELECT * FROM gold.sales TIMESTAMP AS OF '2026-03-20';

-- Referring to a table with Time Travel syntax (@ notation)
SELECT * FROM gold.sales@v12;

Window Functions

Window functions perform aggregations, ranking, and lead/lag references without collapsing rows like GROUP BY does. The OVER clause specifies the partition and ordering.

Comparing Ranking Functions

The table below shows how each function's output differs on the same dataset (salary descending within department).

When Bob and Carol both earn 85000: ROW_NUMBER assigns 2 and 3 in arbitrary order. RANK gives both 2 and the next value is 4 (skipping 3). DENSE_RANK gives both 2 and the next value is 3 (no skip).

Deduplication with ROW_NUMBER

-- Get only each customer's latest order (CTE + ROW_NUMBER pattern)
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM silver.orders
)
SELECT order_id, customer_id, order_date, amount
FROM ranked
WHERE rn = 1;

LAG / LEAD (Previous/Next Row References)

-- Compute the month-over-month revenue difference
SELECT
  month,
  product_id,
  revenue,
  LAG(revenue, 1)  OVER (PARTITION BY product_id ORDER BY month) AS prev_month,
  LEAD(revenue, 1) OVER (PARTITION BY product_id ORDER BY month) AS next_month,
  revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month) AS mom_diff
FROM gold.monthly_sales;

Running Aggregates (SUM / AVG OVER)

-- Cumulative revenue and moving average
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue,
  AVG(daily_revenue) OVER (ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM gold.daily_sales;

CTE (Common Table Expressions)

A CTE defines a named result set in a WITH clause that subsequent SELECTs can reference. Unlike subqueries, the same CTE can be referenced from multiple locations, which significantly improves query readability.

Chaining Multiple CTEs

WITH active_customers AS (
  SELECT customer_id, name, region
  FROM gold.customers
  WHERE is_active = true
),
recent_orders AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM silver.orders
  WHERE order_date >= add_months(current_date(), -3)
  GROUP BY customer_id
),
customer_summary AS (
  SELECT
    ac.customer_id,
    ac.name,
    ac.region,
    COALESCE(ro.total_spent, 0) AS total_spent_3m
  FROM active_customers ac
  LEFT JOIN recent_orders ro ON ac.customer_id = ro.customer_id
)
SELECT region, COUNT(*) AS customer_count, SUM(total_spent_3m) AS region_revenue
FROM customer_summary
GROUP BY region
ORDER BY region_revenue DESC;

Window Function + CTE: A Common Exam Pattern

You cannot filter on a window-function alias directly in a WHERE clause (SQL evaluates WHERE before SELECT). The standard pattern is to compute the window function in a CTE and then filter in the outer query.

-- Get the top 3 earners in each department
WITH salary_rank AS (
  SELECT
    employee_id, name, department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department ORDER BY salary DESC
    ) AS dr
  FROM hr.employees
)
SELECT employee_id, name, department, salary
FROM salary_rank
WHERE dr <= 3
ORDER BY department, dr;

Higher-Order Functions (TRANSFORM / FILTER / REDUCE)

Spark SQL's higher-order functions apply lambda expressions element-wise to array-typed columns. They let you express EXPLODE → aggregation → COLLECT_LIST chains in a single line, which is powerful for nested-structure ETL.

TRANSFORM

Applies a function to each element of an array, returning a new array.

-- Double each element
SELECT TRANSFORM(ARRAY(1, 2, 3, 4), x -> x * 2);
-- Result: [2, 4, 6, 8]

-- Add 10% tax to each item in the order
SELECT
  order_id,
  TRANSFORM(item_prices, price -> price * 1.10) AS prices_with_tax
FROM orders;

FILTER

Returns a new array containing only the elements that match the condition.

-- Keep only the even numbers
SELECT FILTER(ARRAY(1, 2, 3, 4, 5, 6), x -> x % 2 = 0);
-- Result: [2, 4, 6]

-- Keep only items priced at 1,000 yen or more
SELECT
  order_id,
  FILTER(item_prices, p -> p >= 1000) AS high_value_items
FROM orders;

REDUCE

Folds the array elements with an accumulator, collapsing them into a single value.

-- Sum the array elements
SELECT REDUCE(ARRAY(1, 2, 3, 4), 0, (acc, x) -> acc + x);
-- Result: 10

-- Get the maximum value
SELECT REDUCE(
  ARRAY(35, 12, 88, 47), 0,
  (acc, x) -> CASE WHEN x > acc THEN x ELSE acc END
);
-- Result: 88

PIVOT / UNPIVOT

PIVOT turns rows into columns (row → column); UNPIVOT turns columns into rows (column → row). These are used for cross-tabulation reports and ELT data reshaping.

PIVOT

-- Pivot quarterly revenue into columns
SELECT *
FROM (
  SELECT region, quarter, revenue
  FROM gold.quarterly_sales
)
PIVOT (
  SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);
-- Result: region | Q1 | Q2 | Q3 | Q4

UNPIVOT

-- Turn columns back into rows
SELECT *
FROM gold.quarterly_sales_wide
UNPIVOT (
  revenue FOR quarter IN (Q1, Q2, Q3, Q4)
);
-- Result: region | quarter | revenue (one row per quarter)

Practical Exam Tips

• MERGE INTO syntax: Memorize the three branches — WHEN MATCHED, WHEN NOT MATCHED, and WHEN NOT MATCHED BY SOURCE. The UPDATE SET * and INSERT * wildcard forms are also frequently tested.
• The window-function WHERE pitfall: "You need a CTE to filter on a ROW_NUMBER() alias" is asked again and again. Make sure you understand why you can't write WHERE rn = 1 directly (SQL evaluation order).
• OPTIMIZE vs VACUUM: OPTIMIZE = "merge files → improve read performance"; VACUUM = "delete unused files → save storage." The side effect of VACUUM is that Time Travel to old versions stops working.
• INSERT OVERWRITE vs INSERT INTO: OVERWRITE replaces existing data; INTO appends. The scope changes depending on whether you specify a partition.
• COPY INTO idempotency: Running the same file twice does not re-ingest it. Watch out for force = true, which disables this idempotency — a frequently tested gotcha.
• RESTORE vs Time Travel: RESTORE actually writes the table back to a previous version, while Time Travel (VERSION AS OF) only reads from a previous version.

Sample Question

Frequently Asked Questions