Databricks SQL Query

Task

Constraints

• SELECT queries ONLY — no INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
• Always fully qualify table names:

  catalog.schema.table

• Always list columns explicitly — no

  SELECT *

• Include

  LIMIT

  for exploratory queries
• Use CTEs for complex multi-step queries

Verify-Query-Validate Cycle

1. Plan (think before writing SQL)

• What tables do I need?
• Do I know the column names, or do I need to verify with

  dbx describe

  ?
• If joining tables, what is the join path and cardinality?
• Check memory for previously discovered join paths

2. Verify (non-optional for unfamiliar tables)

dbx describe

dbx describe <catalog>.<schema>.<table>

3. Query

dbx query "SELECT col1, col2 FROM catalog.schema.table WHERE condition ORDER BY col1 LIMIT 100"

dbx query -f query.sql

dbx query "SELECT ..." --format csv -o results.csv

4. Validate

• Is the row count reasonable (> 0 and not suspiciously high)?
• Do sample values look like real data?
• If joining, are there unexpected duplicates (wrong join cardinality)?

5. Iterate if needed

1. Check column types with

   dbx describe

2. Sample raw data with

   dbx sample

3. Adjust query and re-execute

Example: Simple Aggregation

main.analytics.orders

order_id

order_date

SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(order_id) AS order_count
FROM main.analytics.orders
WHERE order_date >= '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month

Example: Multi-Table Join

orders.customer_id = customers.customer_id

dbx describe

SELECT
  c.customer_id,
  c.name,
  SUM(o.order_amount) AS total_value
FROM main.analytics.customers c
JOIN main.analytics.orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_value DESC
LIMIT 20

Output Format

Query

-- The SQL that was executed

Results Summary

Raw Results