Databricks SQL Flip Columns

Functions

▦ → SQL SCALAR → 1

Returns a single value;

CREATE FUNCTION ... RETURNS DOUBLE

🐍 PYTHON ⚙

Complex logic;

LANGUAGE PYTHON AS $$ ... $$

▤ TABLE-VALUED ▦

Returns rows;

RETURNS TABLE(...)

→ TEMPORARY

Session-only;

CREATE TEMPORARY FUNCTION

Functions

A function is a reusable code object that returns either a single value or a table-like result set.

SQL scalar: returns one value, such as a calculation.
Python: supports more complex custom logic.
Table-valued: returns rows that can be queried like a table.
Temporary: exists only during the current session.

Example

CREATE FUNCTION tax_rate(amount DOUBLE) RETURNS DOUBLE RETURN amount * 0.08;

Stored Procedures

⚙️↔️⚙️

➜Multi-step SQL operations

↕Variables + control flow

⇄IN / OUT / INOUT parameters

▸CALL
dev_catalog.etl_schema.proc_name(...)

CREATE PROCEDURE refresh_summary( IN source_date DATE, OUT rows_processed INT)

ⓘ Preview feature — requires Runtime 17.0+

Stored Procedures

A stored procedure packages multiple SQL statements into a reusable workflow.

Good for repeatable ETL and refresh logic.
Can use variables and control flow.
Can accept input parameters and return output parameters.
Runs with the CALL command.

Example

CALL dev_catalog.etl_schema.refresh_summary( source_date => '2025-01-01' );

Alter Catalog

🔑 Transfer Ownership

SET OWNER TO team_name

🏷️ Apply Tags

SET TAGS (environment = 'prod')

⚡ Predictive Optimization

ENABLE PREDICTIVE OPTIMIZATION

Alter Catalog

ALTER CATALOG changes governance and metadata settings for a Unity Catalog catalog.

Transfer ownership: changes who owns the catalog.
Apply tags: adds governance and classification metadata.
Predictive optimization: enables automatic performance optimization.

Example

ALTER CATALOG sales SET OWNER TO finance_team; ALTER CATALOG sales SET TAGS ('environment' = 'prod');

Alter Table

➕

Add Column

Add new fields to existing table

✎

Rename Column

Requires Delta column mapping

🗑️

Drop Column

Remove columns safely

🔒

Add Constraint

PRIMARY KEY / FOREIGN KEY / CHECK

☑ All operations preserve existing data

Alter Table

ALTER TABLE modifies an existing Delta table without recreating the table.

Add column: add new fields to the schema.
Rename column: change a column name.
Drop column: remove an unwanted field safely.
Add constraint: enforce data quality rules.

Example

ALTER TABLE customers ADD COLUMN loyalty_level STRING; ALTER TABLE customers ADD CONSTRAINT valid_level CHECK (loyalty_level IS NOT NULL);