learn.fttgsolutions.com · Cheat Sheets
Unify. Engineer. Analyse.
| %sql | %sql
SELECT * FROM my_catalog.my_schema.orders LIMIT 10; | Run a SQL cell in any notebook language context. Results display as a table automatically. |
| %python / %scala / %r | %python
df = spark.table('orders')
df.show(5) | Switch the language of a single cell, overriding the notebook default. |
| %fs | %fs ls /mnt/my-storage/
%fs mkdirs /mnt/my-storage/output/
%fs rm -r /mnt/my-storage/tmp/ | Run Databricks File System (DBFS) commands. Shorthand for dbutils.fs calls. |
| %sh | %sh
pip install great_expectations
cat /etc/hosts | Execute shell commands on the driver node. Changes are local to the driver and not persisted across restarts. |
| %run | %run ./utils/helpers | Execute another notebook inline — variables and functions defined there become available in the current notebook. |
| %md | ## Section Title
Explain what this cell does. | Render Markdown in the notebook for documentation. Supports headers, bold, code blocks, and tables. |
| CREATE TABLE (Delta) | CREATE TABLE my_catalog.my_schema.orders (
order_id BIGINT,
customer_id BIGINT,
amount DOUBLE,
order_date DATE
)
USING DELTA
LOCATION 'abfss://container@storage.dfs.core.windows.net/orders/'; | Create a managed or external Delta table. Delta is the default format in Databricks — ACID transactions, versioning, and schema enforcement built in. |
| MERGE INTO (upsert) | MERGE INTO orders AS target
USING updates AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *; | Upsert — update matching rows and insert new ones atomically. The most common CDC pattern in Delta Lake. |
| Time Travel | -- By version
SELECT * FROM orders VERSION AS OF 5;
-- By timestamp
SELECT * FROM orders
TIMESTAMP AS OF '2024-01-01'; | Query any previous version of a Delta table. History is retained until VACUUM removes old files. |
| DESCRIBE HISTORY | DESCRIBE HISTORY orders; | Show all versions of a Delta table — who changed it, when, and what operation was run. |
| OPTIMIZE | OPTIMIZE orders;
OPTIMIZE orders ZORDER BY (customer_id, order_date); | Compact small files into larger ones to improve read performance. ZORDER co-locates related data for faster filtering. |
| VACUUM | VACUUM orders RETAIN 168 HOURS; | Remove old data files no longer referenced by any table version. Default retention is 7 days (168 hours). Do not set below 7 days with concurrent reads. |
| RESTORE | RESTORE TABLE orders TO VERSION AS OF 3;
RESTORE TABLE orders TO TIMESTAMP AS OF '2024-01-15'; | Roll back a Delta table to a previous version. Creates a new entry in the table history. |
| Read data | df = spark.read.format('delta').load('/mnt/data/orders')
df = spark.read.csv('/mnt/data/file.csv', header=True, inferSchema=True)
df = spark.table('my_catalog.my_schema.orders') | Load data into a DataFrame from Delta, CSV, Parquet, JSON, or any registered table. |
| Select & filter | from pyspark.sql.functions import col
df.select('order_id', 'amount') \
.filter(col('amount') > 100) \
.show(10) | Chain select and filter transformations. Spark is lazy — nothing executes until an action like show() or write is called. |
| withColumn | from pyspark.sql.functions import col, lit, when
df = df.withColumn('tax', col('amount') * 0.2)
df = df.withColumn('tier', when(col('amount') > 500, 'high').otherwise('low')) | Add or replace a column using an expression. Use when/otherwise for conditional logic. |
| GroupBy & aggregate | from pyspark.sql.functions import sum, avg, count
df.groupBy('region') \
.agg(
sum('amount').alias('total'),
avg('amount').alias('avg'),
count('*').alias('orders')
).show() | Group rows and compute aggregates. Import functions from pyspark.sql.functions — not Python builtins. |
| Join | result = orders.join(customers, on='customer_id', how='left') | Join two DataFrames. how can be 'inner', 'left', 'right', 'outer', 'semi', or 'anti'. |
| Window functions | from pyspark.sql.window import Window
from pyspark.sql.functions import rank, lag
w = Window.partitionBy('region').orderBy(col('amount').desc())
df = df.withColumn('rank', rank().over(w)) | Apply ranking, lag/lead, and running totals per partition without collapsing rows. |
| Write data | df.write \
.format('delta') \
.mode('overwrite') # or 'append', 'merge'
.partitionBy('order_date') \
.saveAsTable('my_catalog.my_schema.orders_out') | Persist a DataFrame as a Delta table. Use partitionBy on high-cardinality filter columns to improve query pruning. |
| Run SQL | spark.sql('SELECT region, SUM(amount) FROM orders GROUP BY region').show() | Execute SQL from Python using spark.sql(). Returns a DataFrame you can chain further transformations on. |
| Create temp view | df.createOrReplaceTempView('orders_view')
# Use in SQL
spark.sql('SELECT * FROM orders_view LIMIT 10').show() | Register a DataFrame as a temporary SQL view scoped to the current Spark session. |
| CREATE TABLE AS SELECT | CREATE OR REPLACE TABLE my_schema.summary
USING DELTA AS
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region; | Create a new Delta table populated from a query result in one statement. |
| Three-level namespace | -- catalog.schema.table
SELECT * FROM my_catalog.sales.orders; | Unity Catalog adds a catalog layer above schemas. All object references use the three-level format. |
| CREATE CATALOG | CREATE CATALOG IF NOT EXISTS prod_catalog
COMMENT 'Production data'; | Create a top-level catalog. Each workspace can access multiple catalogs governed by Unity Catalog. |
| GRANT privilege | GRANT SELECT ON TABLE my_catalog.sales.orders
TO `data-analysts-group`;
GRANT USE CATALOG ON CATALOG my_catalog
TO `data-analysts-group`; | Grant fine-grained access on catalogs, schemas, tables, or views. Always grant USE CATALOG and USE SCHEMA alongside object-level grants. |
| Data lineage | -- Automatic — view in Catalog Explorer UI
-- Filter by table name → Lineage tab | Unity Catalog automatically tracks column-level data lineage across notebooks, jobs, and SQL queries. |
| Cluster types | All-purpose cluster → interactive notebooks, shared
Job cluster → single job, auto-terminated
SQL warehouse → SQL analytics, BI tools | All-purpose clusters are billed per hour and shared. Job clusters start fresh for each run and are cheaper for automated pipelines. SQL warehouses are optimised for SQL workloads. |
| Spark config (notebook) | spark.conf.set('spark.sql.shuffle.partitions', '200')
spark.conf.get('spark.sql.shuffle.partitions') | Override Spark settings at runtime. Lower shuffle partitions for small datasets (default 200 is too high for most cases). |
| Adaptive Query Execution | -- Enabled by default in DBR 7.3+
spark.conf.set('spark.sql.adaptive.enabled', 'true') | AQE dynamically adjusts join strategies, coalesces small partitions, and handles skew at runtime. |
| dbutils.secrets | token = dbutils.secrets.get(scope='my-scope', key='api-token') | Retrieve secrets from Databricks Secret Scopes (backed by Azure Key Vault or Databricks). Never hardcode credentials in notebooks. |
| Start a run | import mlflow
with mlflow.start_run():
mlflow.log_param('lr', 0.01)
mlflow.log_metric('accuracy', 0.95)
mlflow.sklearn.log_model(model, 'model') | Log parameters, metrics, and artefacts to an MLflow experiment. Runs are tracked automatically in the Databricks UI. |
| Autologging | mlflow.autolog() # before model.fit()
model.fit(X_train, y_train) | Automatically log parameters, metrics, and the model for supported frameworks (sklearn, XGBoost, PyTorch, etc.). |
| Register model | mlflow.register_model(
model_uri='runs:/<run_id>/model',
name='my-churn-model'
) | Add a trained model to the MLflow Model Registry for versioning, staging, and production promotion. |
| Load model | model = mlflow.sklearn.load_model('models:/my-churn-model/Production') | Load a registered model by name and stage. Swap stages without changing code. |
| Create job (UI) | Workflows → Create Job → Add task
Task type: Notebook / Python script / dbt / SQL
Cluster: Job cluster (recommended)
Schedule: Cron or triggered | Jobs orchestrate one or more tasks with dependencies, retries, and alerts. Use job clusters to keep costs predictable. |
| Task dependencies | Task A → Task B → Task C
↘ Task D | Define a DAG of tasks. A downstream task starts only when all its upstream tasks succeed. |
| dbutils.notebook.run | result = dbutils.notebook.run(
'/path/to/notebook',
timeout_seconds=300,
arguments={'env': 'prod', 'date': '2024-01-01'}
) | Run another notebook programmatically and pass parameters. Returns the value of dbutils.notebook.exit(). |