Learn the Databricks interface by using it

Everything in Databricks hangs off one left sidebar. It never changes, whatever screen you are on, and it is the fastest way to build a mental model: each entry is a different kind of object, not a different app. Click through the rail below — the panel on the right explains what each entry gives you and what to try first.

The three things that trip people up on day one

In SAS the engine was already running when you opened the client. Here you rent it by the minute, so you start it, and it stops itself when you walk away. There are two kinds and picking the wrong one is the most common first-day mistake.

A notebook is a stack of cells. Each cell holds code or text, runs on its own, and prints its result directly underneath. Unlike a SAS program, order is your responsibility: a cell remembers whatever ran before it, so re-running things out of order is how people confuse themselves. Run the simulation below top to bottom.

{{ c.code }}

What the toolbar and cell chrome actually do

Unity Catalog names everything in three parts: catalog.schema.table. If you know SAS, a schema is your libref and a table is your dataset — the extra level on the front is the environment or domain boundary. Browse the tree, then read the columns panel; this is exactly the screen you use to check a table before querying it.

Files live in a Volume — a governed folder inside a schema, reachable at /Volumes/catalog/schema/volume/. Upload one from Catalog › your schema › Create › Volume, or drag it into an existing volume. Then read it into a DataFrame, which is the Spark equivalent of a work dataset in memory.

CSV

df = (spark.read.format("csv")
        .option("header", True)
        .option("inferSchema", True)
        .option("sep", ",")
        .load("/Volumes/main/raw_bronze/landing/claims_2024.csv"))

display(df)

Excel

import pandas as pd

pdf = pd.read_excel("/Volumes/main/raw_bronze/landing/rates.xlsx",
                    sheet_name="2024", header=0)
df  = spark.createDataFrame(pdf)

SAS datasets (.sas7bdat)

import pandas as pd

pdf = pd.read_sas("/Volumes/main/raw_bronze/landing/claims.sas7bdat",
                  format="sas7bdat", encoding="latin-1")
df  = spark.createDataFrame(pdf)

# For files too big for one machine, install the spark-sas7bdat library
# on the cluster and read it in parallel instead:
df = spark.read.format("com.github.saurfang.sas.spark").load(path)

Snowflake

options = {
  "sfUrl":       "acme.eu-west-1.snowflakecomputing.com",
  "sfUser":      dbutils.secrets.get("acme", "sf_user"),
  "sfPassword":  dbutils.secrets.get("acme", "sf_pwd"),
  "sfDatabase":  "ANALYTICS",
  "sfSchema":    "PUBLIC",
  "sfWarehouse": "LOAD_WH",
}

df = (spark.read.format("snowflake").options(**options)
        .option("dbtable", "DIM_CUSTOMER").load())

One habit to unlearn: there is no row-by-row loop. You describe the whole column at once and Spark works out the loop. Everything a DATA step does with IF, WHERE, retained variables and formats becomes a column expression. Switch the target language below — SAS on the left stays put so you can read across.

{{ r.sas }}

{{ r.target }}

This is the module where SAS habits hurt most. Everything you did with BY processing — flagging the first row of a group, retaining a running total, lagging a value, ranking, transposing — has one answer here: a window. You declare which rows belong together (partitionBy) and in what order (orderBy), and the calculation walks them for you. No sort step, and it works across the whole cluster.

from pyspark.sql import Window as W, functions as F

{{ r.sas }}

{{ r.target }}

Every table you create is a Delta table by default. Practically, that buys you three things a plain file never had: updates and deletes, transactions that either finish or leave nothing behind, and a full history you can query. That is what makes MERGE INTO — the update/insert pattern you would have written as a MODIFY or a two-step PROC SQL — safe here.

The upsert you will use constantly

MERGE INTO main.claims_silver.claims AS tgt
USING updates_today AS src
  ON tgt.claim_id = src.claim_id

WHEN MATCHED AND src.status <> tgt.status THEN
  UPDATE SET tgt.status = src.status, tgt.updated_at = current_timestamp()

WHEN NOT MATCHED THEN
  INSERT *

Writing a table, and looking backwards

(clean_df.write
   .format("delta")
   .mode("overwrite")
   .saveAsTable("main.claims_silver.claims"))

# what happened to this table, and undoing it
DESCRIBE HISTORY main.claims_silver.claims;
SELECT * FROM main.claims_silver.claims VERSION AS OF 12;
RESTORE TABLE main.claims_silver.claims TO VERSION AS OF 12;

If your day is queries and charts, this is the half of the product you will live in — no Python required. The SQL editor is a query, a warehouse selector and a result grid; a dashboard is a canvas of those queries plus filters.

SELECT product_line,
       count(*)          AS claims,
       round(sum(amount)) AS total_eur
FROM   main.analytics_gold.claims_enriched
WHERE  claim_date >= '2024-01-01'
GROUP  BY product_line
ORDER  BY total_eur DESC

A notebook you re-run by hand every Monday is a job waiting to happen. Declarative pipelines (formerly Delta Live Tables) let you describe the tables instead of the steps: you write one function per table, and the pipeline works out the order, the dependencies and the retries. The graph it draws is the interface — each box is a table, each arrow a dependency.

import dlt
from pyspark.sql import functions as F

@dlt.table(name="claims_raw")
def claims_raw():
    return (spark.readStream.format("cloudFiles")
              .option("cloudFiles.format", "csv")
              .load("/Volumes/main/raw_bronze/landing/"))

@dlt.table(name="claims_clean")
@dlt.expect_or_drop("valid_amount", "amount > 0")
def claims_clean():
    return (dlt.read("claims_raw")
              .withColumn("claim_date", F.to_date("claim_date", "yyyy-MM-dd"))
              .filter(F.col("status") != "TEST"))

For anything that is not a table-building pipeline — running a notebook nightly, chaining three of them, emailing on failure — use Jobs on the same page: add tasks, draw dependencies, set a schedule, open a run to read its logs.

Compute is metered, so slow code has a price tag. You do not need to understand the engine to avoid the expensive mistakes — five habits cover almost everything an analyst hits.

Symptom to lever

-- compact small files and co-locate rows you filter on
OPTIMIZE main.claims_silver.claims;
ALTER TABLE main.claims_silver.claims CLUSTER BY (claim_date, product_line);

-- what is this query actually doing? (Query profile in the SQL editor shows the same)
EXPLAIN SELECT * FROM main.claims_silver.claims WHERE claim_date > '2024-06-01';

Nobody switches off a SAS job because the Databricks version looks right. You run both for a while and prove they agree — and when they disagree, you need to know within minutes whether it is a real defect or a rounding artefact. Work the checks in this order; each one is cheap and rules out a whole class of difference.

sas = spark.table("main.validation.claims_from_sas")   # exported and loaded once
new = spark.table("main.claims_silver.claims_enriched")

print(sas.count(), new.count())
print(sas.select("claim_id").distinct().count(),
      new.select("claim_id").distinct().count())

# 3 — column-by-column profile, side by side
def profile(df, label):
    return df.agg(
        F.sum("amount").alias("total"),
        F.min("claim_date").alias("first_date"),
        F.max("claim_date").alias("last_date"),
        F.sum(F.col("policy_id").isNull().cast("int")).alias("null_policy"),
    ).withColumn("source", F.lit(label))

display(profile(sas, "sas").unionByName(profile(new, "databricks")))

# 4 — the rows that actually differ, tolerant of floating point
key = ["claim_id"]
cmp_cols = ["policy_id", "product_line", "band"]
a = sas.select(*key, *cmp_cols, F.round("amount", 2).alias("amount"))
b = new.select(*key, *cmp_cols, F.round("amount", 2).alias("amount"))

display(a.exceptAll(b))   # in SAS, not in Databricks
display(b.exceptAll(a))   # in Databricks, not in SAS

Two people can type in the same notebook at the same time, which is either useful or alarming depending on what you expected. Three interface habits keep collaborative work sane.

Who can do what

GRANT USE CATALOG ON CATALOG main            TO `analysts`;
GRANT USE SCHEMA  ON SCHEMA  main.claims_silver TO `analysts`;
GRANT SELECT      ON TABLE   main.claims_silver.claims TO `analysts`;

SHOW GRANTS ON TABLE main.claims_silver.claims;

dbutils.widgets.text("start_date", "2024-01-01")
start = dbutils.widgets.get("start_date")

df = spark.table("main.claims_silver.claims").filter(F.col("claim_date") >= start)

Do this one in a real workspace, in a notebook you create yourself. It uses every screen from the modules above: a volume, a cluster, a notebook, the catalog, and finally the SQL editor to check your own work.

The whole thing, if you want to read it first

import pandas as pd
from pyspark.sql import functions as F

# 1 — read the SAS dataset from a volume
pdf = pd.read_sas("/Volumes/main/raw_bronze/landing/claims.sas7bdat",
                  encoding="latin-1")
claims = spark.createDataFrame(pdf)

# 2 — clean: trim, retype, convert SAS dates, band the amounts
claims = (claims
  .withColumn("policy_id", F.trim(F.col("policy_id")))
  .withColumn("amount",    F.col("amount").cast("double"))
  .withColumn("claim_date", F.expr("date_add(to_date('1960-01-01'), cast(claim_date as int))"))
  .withColumn("band", F.when(F.col("amount") < 500, "small")
                       .when(F.col("amount") < 5000, "medium")
                       .otherwise("large"))
  .filter(F.col("status") != "TEST"))

# 3 — join the policy dimension from Snowflake or the catalog
policies = spark.table("main.raw_bronze.policies").select(
    "policy_id", "product_line", "region")
enriched = claims.join(policies, on="policy_id", how="left")

# 4 — write a governed Delta table
(enriched.write.format("delta").mode("overwrite")
   .saveAsTable("main.claims_silver.claims_enriched"))

# 5 — check it, then go look at it in Catalog
display(spark.sql("""
  SELECT product_line, band, count(*) AS n, round(sum(amount)) AS total
  FROM main.claims_silver.claims_enriched
  GROUP BY product_line, band ORDER BY total DESC"""))

Shown for macOS; use Ctrl in place of ⌘ on Windows. Press Shift+? in any notebook for the live list.

Spark SQL names are given; in PySpark the same function lives on pyspark.sql.functions — F.substring, F.instr, and so on.