Databricks interview questions for Data Analyst roles

A Databricks cluster (now usually called classic compute) is a set of cloud virtual machines running Apache Spark: one driver that plans the work and worker nodes that run it in parallel.

You choose the Databricks Runtime version, the machine types, the number of workers or an autoscaling range, an access mode and an idle time after which it shuts itself down. There are two common kinds:

• All-purpose compute for interactive work in notebooks, shared by a team and started and stopped by hand.
• Jobs compute, created for one job run and terminated when the run finishes, which costs less per hour.

Databricks also offers serverless compute, where it runs and scales the machines for you and starts in seconds, and SQL warehouses for SQL queries and dashboards.

Read the files through Unity Catalog (a volume, an external location or an external table), give them an explicit schema, and let Spark broadcast whichever side is small. If the join runs repeatedly, load the files into a Delta table first and join two tables.

1. Reach the files through Unity Catalog

Unity Catalog governs cloud storage, so the notebook reads paths it has been granted rather than keys in code:

• Volumes for files: /Volumes/main/landing/partners/prices/.
• External locations for paths in your own buckets, such as s3://my-bucket/prices/.

2. Read with a schema, then join

from pyspark.sql import functions as F

prices = (spark.read.format("csv")
          .option("header", "true")
          .schema("product_id STRING, price DECIMAL(10,2), valid_from DATE")
          .load("/Volumes/main/landing/partners/prices/"))

orders = spark.read.table("main.sales.orders")
joined = orders.join(prices, "product_id", "left")

In SQL, read_files does the same, and Databricks recommends it for reading CSV from SQL:

SELECT o.*, p.price
FROM main.sales.orders o
LEFT JOIN read_files('/Volumes/main/landing/partners/prices/',
                     format => 'csv', header => true,
                     schema => 'product_id STRING, price DECIMAL(10,2), valid_from DATE') p
USING (product_id);

An explicit schema avoids an extra pass over the files to infer types, and avoids surprises such as IDs read as integers.

3. Make the join cheap

• Small file side: Spark broadcasts a small table to every executor so the large Delta table is never shuffled. Adaptive query execution does this automatically at run time when it sees the side is small; F.broadcast(prices) or a BROADCAST hint forces it.
• Filter early: filter the Delta table on its clustering or partition columns and select only the columns you need before the join, so Delta skips files.
• Large on both sides: a sort-merge join is normal. Watch the Spark UI for skew (a few tasks much slower than the rest); adaptive query execution splits skewed partitions.
• Avoid row formats for large data: CSV and JSON are slow to parse and cannot skip data. If the file side is large, convert it.

4. If the join is regular, ingest first

Read the files once into a bronze Delta table with Auto Loader (it picks up only new files), clean them into a silver table, and join two Delta tables. In a Lakeflow declarative pipeline the join becomes a materialized view that refreshes when either side changes. This is faster for every later query, and the file data gains history, statistics and access control.

5. Keep compute near the data

Run the workspace in the same cloud region as the bucket: cross-region reads are slower and add transfer charges.

In short: governed access, an explicit schema, a broadcast for the small side, and Delta tables for anything you join more than once.

Starfire first decides what a missing day means, then picks the window that matches. A time-based window (RANGE BETWEEN INTERVAL 2 DAYS PRECEDING AND CURRENT ROW) already averages only the readings that fall in the last three days, whatever the gaps. If a missing day should count as zero, or should be visible as a row, she first builds a complete calendar with sequence() and joins the data to it.

Sample data: readings on 1, 2, 5 and 6 October; 3 and 4 October are missing.

1. A row-based window is the trap

AVG(value) OVER (ORDER BY reading_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

This averages the last three rows. On 5 October it would include 1 and 2 October, a week's span, because the rows for 3 and 4 do not exist. Only use it when every period is present.

2. A time-based window handles gaps correctly

SELECT reading_date, value,
       AVG(value) OVER (ORDER BY reading_date
                        RANGE BETWEEN INTERVAL 2 DAYS PRECEDING AND CURRENT ROW) AS avg_3_days
FROM readings;

The frame is defined by dates, not row positions, so on 5 October it holds only the readings from 3 to 5 October, which is just 5 October. Result: 10, 15, 30, 35. Add COUNT(value) OVER (...) to show how many readings each average rests on, so a one-reading "average" is visible.

3. A full calendar, when missing days need rows or values

WITH bounds AS (
  SELECT min(reading_date) AS first_day, max(reading_date) AS last_day FROM readings
),
days AS (
  SELECT explode(sequence(first_day, last_day, INTERVAL 1 DAY)) AS day FROM bounds
),
filled AS (
  SELECT d.day, r.value
  FROM days d LEFT JOIN readings r ON r.reading_date = d.day
)
SELECT day, value,
       AVG(value)              OVER w AS avg_present_days,
       AVG(coalesce(value, 0)) OVER w AS avg_missing_as_zero,
       COUNT(value)            OVER w AS days_with_data
FROM filled
WINDOW w AS (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY day;

Every day now has a row, so a row-based window equals a time-based one. AVG ignores NULL, so avg_present_days matches the RANGE result, while avg_missing_as_zero treats a missing day as zero (right for sales, wrong for a temperature sensor). Computing the bounds in their own step keeps explode out of the aggregate query. For many series, cross join the calendar with the list of series IDs and add PARTITION BY series_id to the window.

4. Carrying the last value forward

For readings that stay valid until the next one (a price, a stock level), fill each gap with the previous value:

coalesce(value, last_value(value, true) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))

The true makes last_value skip NULLs.

Which to choose: RANGE when missing means no data; a calendar with zeros when missing means nothing happened; forward fill when missing means unchanged. Saying which one fits and why is what interviewers listen for. (These queries were run on Apache Spark 4.2 with the sample data above.)

Databricks scales compute in three ways: classic clusters add and remove worker nodes with autoscaling, SQL warehouses add and remove whole clusters as queries queue up, and serverless compute is scaled by Databricks with no settings at all.

1. Classic clusters: autoscaling workers

You set a minimum and maximum number of workers. Databricks adds workers when tasks are waiting and removes them when they are idle.

• Optimized autoscaling (Premium plan and above) goes from minimum to maximum in at most two steps, and can scale down even while the cluster is busy by checking which workers hold shuffle files still needed. On jobs compute it scales down after 40 seconds of low use; on all-purpose compute after 150 seconds.
• Standard autoscaling (the standard plan) starts by adding 8 nodes and grows exponentially, and scales down only when 90% of nodes have not been busy for 10 minutes.
• Autoscaling local storage attaches more disk to a worker before it runs out of space.
• Auto termination stops an idle all-purpose cluster entirely after the minutes you choose; jobs compute ends with its run.

Autoscaling suits workloads that vary. Streaming jobs on classic compute are usually better with a fixed size, or with Lakeflow pipelines, which have their own autoscaling for streaming.

2. SQL warehouses: more clusters for more queries

A warehouse has a size (2X-Small up to 5X-Large), which sets how big each cluster is, and a range of clusters.

• Serverless warehouses use Intelligent Workload Management: each query's needs are predicted, queries are queued if capacity is short, and clusters are added quickly as wait times rise.
• Pro and classic warehouses add clusters based on the estimated load of running and queued queries, scale up if a query waits 5 minutes, and scale down after 15 minutes of low load.

The size helps single large queries; more clusters help many concurrent users.

3. Serverless compute for notebooks, jobs and pipelines

Databricks runs the machines, starts them in seconds and scales them to the work. There is no cluster to size, and you pay only while work runs.

4. Choosing machines

On classic compute you still choose worker types: memory-optimised for large joins and caching, compute-optimised for CPU-heavy transformations, GPU instances for deep learning, and Photon for faster SQL and DataFrame work.

The result is that capacity follows demand: more machines during heavy work, few or none when idle, and less time spent sizing clusters by hand.

A full scan usually means Delta Lake cannot skip files for the query's filter. Thanos should check whether the data is laid out by the filtered columns (liquid clustering), whether those columns have statistics, and whether the table has many small files, and then let predictive optimization keep it that way.

First, confirm the cause. Open the query profile in the SQL warehouse's query history. Compare files read with files pruned and look at the filter: a filter on a column that is wrapped in a function (for example date(event_time) = ...) or compared with a different type often prevents skipping.

1. Liquid clustering

Databricks recommends liquid clustering for all new tables, in place of partitioning and Z-ordering. It sorts data by the columns you choose so files hold narrow ranges of them, and Delta can skip the rest.

ALTER TABLE events CLUSTER BY (customer_id, event_date);
OPTIMIZE events FULL;   -- recluster existing data once

On Unity Catalog managed tables, CLUSTER BY AUTO lets Databricks choose the keys from the actual query patterns. Liquid clustering cannot be combined with partitioning or ZORDER; partitioned tables need converting first.

2. Statistics for data skipping

Delta stores minimum, maximum and null counts per file when data is written. On external tables only the first 32 columns get statistics by default, so a filter on column 40 skips nothing. Name the needed columns with delta.dataSkippingStatsColumns, then run ANALYZE TABLE events COMPUTE DELTA STATISTICS so existing files get them too. Managed tables with predictive optimization pick statistics columns from real filters automatically.

3. Small files

Thousands of small files slow every query. Run OPTIMIZE, and turn on optimized writes and auto compaction for tables written often in small batches.

4. Predictive optimization

On Unity Catalog managed tables, predictive optimization runs OPTIMIZE, VACUUM and ANALYZE when needed, so the layout does not decay again.

5. Photon and predictive I/O

SQL warehouses run on Photon, whose predictive I/O can skip data on any column. That is why bloom filter indexes, once a common answer here, are now deprecated.

6. The query itself

Select only needed columns, filter on raw column values rather than expressions, and check join order and broadcast joins in the profile.

A good answer starts with the query profile and ends with liquid clustering and predictive optimization, rather than reaching first for ZORDER.

Ronan parses the date strings with to_date (or try_to_date) and a pattern that matches how they are actually written, then stores the result as a real DATE column so every query uses the parsed value.

1. Find out what the strings look like

SELECT order_date_raw, count(*) FROM raw.orders GROUP BY ALL ORDER BY 2 DESC LIMIT 20;

Mixed formats (10/27/2026, 2026-10-27, 27.10.2026) are the usual cause of "wrong" dates, and so is guessing dd/MM when the source writes MM/dd.

2. Parse with the right pattern

SELECT order_date_raw,
       to_date(order_date_raw, 'MM/dd/yyyy') AS order_date
FROM raw.orders;

Patterns are case-sensitive: MM is month, mm is minutes; yyyy is the year. Use to_timestamp when the string includes a time.

3. Don't let bad rows fail the query

With ANSI mode on (the default on current Databricks), to_date raises an error on a string it cannot parse. try_to_date returns NULL instead, which also lets you handle several formats:

SELECT order_date_raw,
       coalesce(
         try_to_date(order_date_raw, 'MM/dd/yyyy'),
         try_to_date(order_date_raw, 'yyyy-MM-dd'),
         try_to_date(order_date_raw, 'dd.MM.yyyy')
       ) AS order_date
FROM raw.orders;

Rows that are still NULL are the ones to look at.

4. Format only for display

date_format(order_date, 'dd MMM yyyy') turns a date back into a string for a report. Keep dates as DATE for filtering, sorting and joining; formatting them as strings early is what causes many of these problems.

5. Fix it once, at the source of the analysis

Instead of parsing in every query, write a cleaned table or view with the parsed column:

CREATE OR REPLACE TABLE clean.orders AS
SELECT * EXCEPT (order_date_raw),
       coalesce(try_to_date(order_date_raw, 'MM/dd/yyyy'),
                try_to_date(order_date_raw, 'yyyy-MM-dd')) AS order_date
FROM raw.orders;

In a pipeline, the same expression goes in the silver step, with an expectation such as order_date IS NOT NULL so unparseable rows are counted or quarantined.

In PySpark the same functions exist: F.to_date("order_date_raw", "MM/dd/yyyy"), and F.try_to_date in recent versions.

Once dates are real DATE values, functions such as year(), date_trunc('MONTH', ...) and datediff() work as expected.

Why this matters in an interview: the question is less about one function than about where the fix belongs. Parsing in every analyst's query spreads the same assumption into many places, and when the source changes its format, each query breaks in a different way. Fixing the column once, testing it with a count of unparsed rows, and having everyone read the clean table keeps one definition of the date for the whole team.

Bane can stop using get_json_object altogether: Databricks lets you query a JSON string with a path written after a colon (raw:customer.address.city), and for new tables it recommends storing the JSON as the VARIANT type, which is faster to query and keeps the same path syntax.

1. The colon syntax on JSON strings

SELECT raw:customer.name::string        AS customer_name,
       raw:customer.address.city::string AS city,
       raw:items[0].sku::string          AS first_sku,
       raw:total::decimal(10,2)          AS total
FROM main.raw.orders;

A path after : reads a field, dots go deeper, and [0] picks an array element. Results come back as strings, so cast with ::. For a nested object, from_json(raw:customer.address, 'street string, city string') returns a typed struct.

2. Store it as VARIANT

CREATE TABLE main.bronze.orders AS
SELECT parse_json(raw) AS data FROM main.raw.orders;

SELECT data:customer.name::string AS customer_name,
       try_variant_get(data, '$.total', 'decimal(10,2)') AS total
FROM main.bronze.orders;

VARIANT stores semi-structured data in a binary form that is parsed once on write, so queries do not re-parse text on every read. It needs Databricks Runtime 15.4 or later. try_variant_get returns NULL instead of failing when a value has the wrong type. schema_of_variant_agg(data) shows the combined schema across rows, which helps when documenting a messy source.

3. Arrays become rows

SELECT o.data:order_id::string AS order_id,
       item.value:sku::string  AS sku,
       item.value:qty::int     AS qty
FROM main.bronze.orders o,
     LATERAL variant_explode(o.data:items) AS item;

For a typed array column (from from_json or a struct), use explode or inline instead.

4. Flatten once in silver

The cleanest fix is to extract the fields once, into proper typed columns in a silver table (or a view):

CREATE OR REPLACE TABLE main.silver.orders AS
SELECT data:order_id::string AS order_id,
       data:customer.name::string AS customer_name,
       data:total::decimal(10,2) AS total,
       data AS raw_data
FROM main.bronze.orders;

Analysts then query ordinary columns, statistics and clustering work on them, and the raw VARIANT is still there for fields nobody has extracted yet.

In PySpark, F.parse_json, F.variant_get and F.try_variant_get do the same, or F.from_json(col, schema) when the structure is fixed and known.

Notebooks are for exploring and building, in Python, SQL, Scala or R on general-purpose compute; Databricks SQL is for SQL analysis, dashboards and BI tools on SQL warehouses, tuned for many concurrent queries.

Databricks notebooks

• Cells in several languages in one document: Python to clean data, SQL to query it, then a model or chart, with results shown under each cell.
• Run on serverless compute or a cluster, with the full Spark API and any Python library.
• Suited to exploration, data engineering, data science and machine learning, and to work that mixes code and explanation.
• Shared and co-edited by a team, versioned with Git folders, and scheduled as job tasks.

Databricks SQL

• A SQL editor for writing and saving queries, with autocomplete over Unity Catalog tables.
• SQL warehouses: compute built only for SQL, running Photon and scaling out with more clusters as more people query. Serverless warehouses start in seconds.
• AI/BI dashboards and alerts for sharing results and watching metrics, and Genie spaces where business users ask questions in plain language.
• Connections for BI tools such as Power BI and Tableau, which query the same warehouses.

How they differ

They overlap more than they used to. Notebooks can run SQL cells on a SQL warehouse, the SQL editor shows charts, and both read the same tables under the same Unity Catalog permissions. A common pattern: an engineer prepares tables in a notebook or pipeline, and analysts query them in the SQL editor and build dashboards on them.

A good answer names the deciding question: if the work needs more than SQL, or is still being explored, use a notebook; if it is SQL that many people will run or see, use Databricks SQL.

Fast alone and slow on Monday at 9 points to queueing: everyone opens the dashboard at once and queries wait for room on the SQL warehouse. Confirm it on the warehouse's Monitoring tab (queued queries, Peak Queued Queries, scheduling time in each query's duration). The fix is more clusters at the peak, a serverless warehouse that scales quickly, and fewer, cheaper queries behind the dashboard. Only if single queries are slow too does the cluster size or the query itself need work.

1. Separate waiting from working

If running time is normal and scheduling time is long, the queries are fine. They are waiting their turn.

2. Give the peak enough clusters

• Serverless warehouse (Databricks' default advice): it predicts each query's needs, queues only when there is no room, and adds clusters quickly when waits grow. Set a maximum cluster count high enough for the peak.
• Pro or classic: these allow 10 concurrent queries per cluster and add clusters from the estimated minutes of queued work (1 cluster for 2 to 6 minutes of load), or when a query has waited 5 minutes. For a sharp Monday peak that reacts late; raise the minimum cluster count for that window, or move to serverless.
• Keep the cluster size for single-query speed: increase it only if the query profile shows spills to disk.

3. Make the dashboard ask for less

A dashboard with 15 visuals sends 15 or more queries per viewer. 40 people opening it at 9:00 is hundreds of queries at once.

• Point visuals at a small summary table or materialised view refreshed early on Monday, instead of the raw order lines.
• Remove visuals nobody reads, and share datasets between visuals where you can.
• Let the result cache work for you: identical queries over unchanged tables are answered from cache for up to 24 hours, so a refresh of the summary table at 7:00 followed by identical dashboard queries means most viewers hit the cache. Updating the underlying table invalidates it, so avoid refreshing that table during the peak.

4. Then fix any slow query

For queries slow even alone, the query profile's top operators show the cost: a scan reading the whole table because the date filter does not prune, a join multiplying rows, or a sort spilling to disk.

What interviewers want: telling queueing from slow queries using scheduling versus running time, the warehouse monitoring page and Peak Queued Queries, cluster count for concurrency against cluster size for single-query speed, and reducing the load a dashboard creates through summary tables and the result cache.

The dashboard was published with the default Share data permissions, so every viewer's queries run with your permissions, and you can see all regions. A dashboard filter is not security either: a viewer can change it. Put the rule on the data with a Unity Catalog row filter on region, give each manager access to the table, and publish with Individual data permissions so each manager's own identity is applied when the queries run.

1. Why the manager saw everything

Compute always runs on the publisher's credentials, so managers do not need warehouse access in either mode. The draft dashboard always uses the viewer's permissions, which is why your testing in draft may have looked correct.

2. Put the rule on the table

-- 1 group per region, or a mapping table of manager to region
CREATE FUNCTION sales.security.region_filter(region STRING)
RETURN is_account_group_member('sales-admins')
    OR is_account_group_member(concat('region-', lower(region)));

ALTER TABLE sales.gold.orders SET ROW FILTER sales.security.region_filter ON (region);

Now any query on orders by a member of region-north returns only North rows, whether it comes from this dashboard, a notebook, Genie or an export. Keep the function simple; row filters run for every row, and simple SQL expressions are the fastest. If many tables need the same rule, an ABAC policy on a governed tag applies it to all of them; a dynamic view is the option when managers should only ever see a curated, joined version.

3. Grant and publish

1. Grant the managers' groups SELECT on the table (and USE CATALOG, USE SCHEMA).
2. Publish with Individual data permissions.
3. Share the published dashboard with the managers' groups.
4. Test as a manager, not as yourself: open the published version with a test account in region-north.

4. When Share data permissions is the right choice

For a company-wide dashboard where every viewer may see the same numbers, including people with no access to the tables, Share data permissions is simpler: nobody needs grants, and you can publish with a service principal's credentials so it does not depend on your personal account. It is the wrong choice whenever viewers must see different data.

What interviewers want: the difference between the 2 publishing options, why a dashboard filter is not access control, a row filter enforced in Unity Catalog for every tool, testing as the viewer, and when shared permissions are appropriate.

Genie writes SQL from what it can read: table and column descriptions, the business definitions you give it, and examples. Here it has too many tables and no definition of revenue or active customer, so it guesses. Narrow the data (ideally a few prejoined views or a metric view), define revenue and active customers as SQL expressions, add example SQL for the hard questions, keep text instructions short, and measure the result with benchmark questions before and after each change.

1. Find out why each answer differs

Open the generated SQL for the wrong answers and compare it with the finance dashboard's query. Typical differences:

• Gross instead of net revenue, or refunds and tax included.
• order_date used where finance uses invoice_date.
• "Last quarter" taken as the last 90 days instead of the previous calendar or fiscal quarter.
• Joining orders to order lines and summing a header total, counting it once per line.

Each of these is a missing definition, not a bad model.

2. Shrink and clean the data

Databricks advises starting with 5 or fewer tables. Replace the 14 raw tables with 2 or 3 views shaped for sales questions, or a metric view that already defines measures and dimensions. Write clear Unity Catalog descriptions for every column left, hide columns that invite mistakes (such as gross_amount_incl_tax when nobody should use it), and add synonyms such as "client" for customer.

3. Define the business terms in SQL

Databricks puts these in this order of preference: SQL first, text last. Long lists of text instructions lose effect and start to conflict, for example an instruction to round to 2 decimals while an example query rounds to 0.

4. Make it ask instead of guess

For questions that need a detail, write the clarification rule with its trigger, the missing detail, the action and the question to ask: "When users ask about revenue without a time range or currency, ask: Which period and currency do you want?"

5. Measure, then widen

• Turn the questions finance can answer into benchmarks with the expected SQL, and run them after each change.
• Watch the Monitoring tab and user feedback for new failures, and add examples for the questions that fail.
• Add tables only when a real question needs them.

What interviewers want: reading the generated SQL to find the cause, fewer and better-described tables, metrics defined once as SQL expressions, example queries over text instructions, clarification rules, and benchmarks to prove accuracy rather than spot checks.

Use ai_classify with the 5 labels and a description for each, run it once over the tickets and store the result in a table (the function cannot be used inside a view). Before trusting the chart, label a random sample of 200 by hand, compare, and fix the label descriptions where the two disagree. Then classify only new tickets each day.

1. Write labels a stranger could apply

The descriptions do most of the work. Vague labels such as "account" and "billing" overlap:

'{
  "billing": "Charges, invoices, refunds, failed payments",
  "bug": "Something in the product is broken or behaves wrongly",
  "how_to": "Asks how to do something the product already supports",
  "account": "Login, password, profile, team members, permissions",
  "cancellation": "Wants to cancel, downgrade or close the account"
}'

2. Try it on a sample first

SELECT ticket_id, body,
       ai_classify(body, :labels,
         MAP('version', '2.1',
             'instructions', 'Support tickets from a B2B invoicing app. Choose the main reason.',
             'enableConfidenceScores', 'true')) AS result
FROM support.tickets
TABLESAMPLE (200 ROWS);

In version 2.1 the result is a VARIANT: the label is result:response[0].value, the confidence result:response[0].confidence_score, and result:error_message is null on success. A ticket that cannot be classified returns NULL; count those rather than dropping them silently.

3. Check against people

Have 2 people label the same 200 tickets without seeing the model's answer. Compare:

• Agreement per label: if "account" and "billing" are often swapped, rewrite those 2 descriptions and run the sample again.
• Low confidence: read the tickets under, say, 0.6; they often show a missing label, such as "feature request". Add it rather than forcing tickets into the closest one.
• Where the 2 people disagree with each other, the label itself is unclear, and the model cannot do better than they do.

Report the agreement rate with the chart, so readers know how far to trust a 2-point change.

4. Run it for real and keep it cheap

CREATE TABLE support.ticket_reasons AS
SELECT ticket_id, created_at,
       ai_classify(body, :labels, MAP('version', '2.1', 'instructions', '...')) AS result
FROM support.tickets;

Then classify only tickets not yet in ticket_reasons on a daily schedule. Each call costs model inference on top of the warehouse, so re-classifying 200,000 tickets for every chart refresh wastes money. Pin the version: version 2.1 changed the output shape from 2.0, and a query reading response[0] as a string would break.

5. When to choose something else

• Many tickets already labelled by people: a classifier trained on them may be cheaper and more accurate.
• Several reasons per ticket: set multilabel to true and chart each reason, knowing totals no longer add up to 100%.

What interviewers want: clear label descriptions, a sample checked against human labels before rollout, reading confidence and NULLs, results stored in a table and computed once, version pinning, and saying how accurate the chart is.

Write the alert query so it does the comparison itself: yesterday's orders against the average of the same weekday over the past 4 weeks, returning 1 when the ratio falls below a bound such as 0.7. Trigger the alert when the value is 1, schedule it after the nightly load (or run it as a task right after the load job), treat an empty result as a failure, and send it to a channel the team watches.

1. Why a fixed threshold fails

"Alert if orders < 5,000" fires every Sunday and misses a 40% drop on a busy Monday. Orders follow the week, so compare like with like.

2. The query

WITH daily AS (
  SELECT order_date, COUNT(*) AS orders
  FROM sales.gold.orders
  WHERE order_date >= current_date() - INTERVAL 29 DAYS
    AND order_date < current_date()
  GROUP BY order_date
),
yesterday AS (
  SELECT orders FROM daily WHERE order_date = current_date() - INTERVAL 1 DAY
),
baseline AS (
  SELECT AVG(orders) AS usual FROM daily
  WHERE dayofweek(order_date) = dayofweek(current_date() - INTERVAL 1 DAY)
    AND order_date < current_date() - INTERVAL 1 DAY
)
SELECT CASE
         WHEN y.orders IS NULL THEN 1                       -- no rows for yesterday at all
         WHEN y.orders < 0.7 * b.usual THEN 1
         ELSE 0
       END AS breach,
       y.orders AS yesterday_orders,
       ROUND(b.usual) AS usual_orders
FROM baseline b LEFT JOIN yesterday y ON true;

Alerts do not accept query parameters, so the dates come from current_date(), not from a parameter. Returning both counts lets the notification show them.

3. The alert settings

4. Tune it so people keep reading it

• Back-test: run the query for each of the past 90 days and count how often 0.7 would have fired. Adjust until it would have caught real incidents and little else.
• Expect public holidays to fire; a small calendar table to exclude them keeps trust.
• If many alerts run at 07:30, stagger them by a few minutes: a workspace runs 250 alert runs at once by default and skips the rest.

5. Beyond the count

The same pattern covers revenue per order, the share of orders with a null region, or the latest loaded_at older than 6 hours. For broad coverage, Unity Catalog data quality monitoring with anomaly detection can send alerts without one query per metric.

What interviewers want: a seasonal baseline instead of a fixed number, the comparison done in SQL with a 1 or 0 result, schedule tied to the load, an empty result treated as a failure, notifications that reach a team, and back-testing the threshold.

Yes for a single query: a query on a Delta table reads one committed version of the table, so concurrent writes never give it a half-written or mixed view. If you need several queries to see the same version, pin it explicitly with time travel.

Why one query is consistent. Delta Lake commits each write as a new table version in its transaction log. A reader resolves which version is current when its query starts and reads only the files of that version. Writers that commit afterwards create later versions, which the running query ignores. Databricks describes this as readers seeing a consistent snapshot of the table the job started with.

Where it can still surprise you. A DataFrame is a plan, not data. df = spark.read.table("my_delta_table") followed by two actions, such as df.count() and then df.write..., can resolve the table again and see a newer version if a writer committed in between. The two results may then disagree.

Ways to get one fixed snapshot for a whole analysis:

1. Pin the version (or timestamp) with time travel:

   version = spark.sql("DESCRIBE HISTORY my_delta_table LIMIT 1").first()["version"]
   df = spark.read.option("versionAsOf", version).table("my_delta_table")
   

   In SQL: SELECT * FROM my_delta_table VERSION AS OF 123, or on Databricks my_delta_table@v123. Every query using that version sees identical data.

2. Materialise what you need once, for example df.cache() followed by an action, or write the intermediate result to a table, then run all later steps against that.

3. Run the whole analysis as one query (one SQL statement or one DataFrame action) where possible.

Limits to know: time travel needs the old data files, so a version older than the table's file retention (7 days by default) can be removed by VACUUM, and history older than the log retention (30 days by default) disappears. Do not run VACUUM to get consistency: it only deletes old files and makes older versions unreadable.

Analysts collaborate in Databricks through shared notebooks that several people can edit at once, Git folders for version control and review, Unity Catalog for shared governed data, and dashboards and Genie spaces for sharing results.

1. Work in the same notebook

• Several people can edit the same notebook, even the same cell, at the same time and see each other's changes.
• Comments with @mentions keep discussion next to the code it is about.
• Notebook permissions are Can read, Can run, Can edit and Can manage, so a reviewer can read without changing anything.

2. Version control with Git folders

• A Git folder clones a GitHub, GitLab, Azure DevOps or Bitbucket repository into the workspace. Each analyst works on a branch, commits and opens a pull request, so changes are reviewed before they reach main.
• Shared helper code lives in Python files in the repository and is imported, not copied between notebooks.

3. Share data, not copies

• Tables live in Unity Catalog, so everyone queries the same governed version. Access is granted to groups (USE CATALOG, USE SCHEMA, SELECT), and lineage shows where each table came from.
• An analyst who builds a useful table saves it to a shared schema with a description, instead of passing CSV files around.

4. Share results

• SQL editor: queries can be saved and shared.
• AI/BI dashboards: interactive dashboards published to colleagues, including business users without a workspace seat, each seeing data under the publisher's or their own permissions.
• Genie spaces: an analyst sets up a space over chosen tables with instructions and example queries, and business users ask it questions in plain language.

5. Automate what works

• A notebook that becomes a regular report turns into a Lakeflow Job with a schedule and notifications, owned by the team rather than one person's laptop.

A typical flow: an engineer publishes a cleaned table in Unity Catalog and grants the analysts' group read access; one analyst explores it in a notebook on a branch in a Git folder; a colleague reviews the pull request; the team builds a dashboard from the finished queries and publishes it; the refresh runs as a scheduled job.

Aggregate in Spark first, then chart the small result: group by the column and count, display() the result and add a bar chart from the + menu above it. Only the counts reach the browser, so the size of the table does not matter.

1. Group, count and chart

from pyspark.sql import functions as F

counts = (spark.read.table("main.sales.orders")
          .groupBy("status")
          .agg(F.count("*").alias("orders"))
          .orderBy(F.desc("orders")))
display(counts)

Or in a SQL cell:

SELECT status, count(*) AS orders
FROM main.sales.orders
GROUP BY status
ORDER BY orders DESC;

Above the result, choose + and then Visualization, pick Bar, put the column on the X axis and the count on the Y axis, and save. The chart stays with the cell and refreshes when the cell is run again.

2. Let the chart aggregate for you

You can also display() the raw column and set the bar chart's Y axis to Count. Databricks then aggregates over the whole dataset, not just the first 64,000 rows shown in the table. Grouping yourself is still clearer and lets you add a WHERE filter or a share column.

3. Data profile

From the same + menu, Data Profile gives summary statistics and a distribution for every column, including the most frequent values of categorical columns. It is the fastest first look at a new table.

4. Many categories

If the column has hundreds of distinct values, chart the top 20 and group the rest:

WITH c AS (SELECT status, count(*) AS n FROM main.sales.orders GROUP BY status)
SELECT CASE WHEN rank() OVER (ORDER BY n DESC) <= 20 THEN status ELSE 'Other' END AS status,
       sum(n) AS orders
FROM c GROUP BY 1 ORDER BY orders DESC;

5. Two categorical columns

Group by both columns and use a stacked bar chart or a heatmap, or use df.crosstab("col_a", "col_b") for a contingency table.

What to avoid: calling toPandas() on the full table or collecting it to the driver to plot. That moves every row to one machine, which is slow and can run out of memory.

Why this is efficient: the expensive part, scanning the table and counting, runs in parallel on the cluster or SQL warehouse, close to the data. What comes back is one row per category, a few kilobytes even for a table of billions of rows. If the counts are needed again, save them as a small summary table or a materialized view and chart that, rather than recounting the large table each time someone opens the notebook.

Lois creates a Lakeflow Job with her notebook as a task, adds a scheduled trigger that runs once a day, and adds the Daily Planet's address under the job's notifications for success and failure.

1. Create the job

• In the workspace sidebar open Jobs & Pipelines and create a job.
• Add a task of type Notebook and choose the notebook. Use serverless compute if the workspace has it; otherwise a jobs cluster, which is created for the run and shut down afterwards and costs less than an all-purpose cluster.
• Pass any values the notebook needs as task parameters, which arrive as widgets.

2. Schedule it

• Under Schedules & Triggers, add a Scheduled trigger: every day at, say, 06:00, in the team's time zone, or as a Quartz cron expression such as 0 0 6 * * ?.

3. Add the email alerts

• Under the job's Job notifications, add newsroom@dailyplanet.example and tick the events: Success and Failure for this case. Start, Duration warning (needs a duration threshold) and Streaming backlog are also available.
• Job-level notifications are not sent for retried tasks. To hear about every failed attempt, add notifications on the task too; Mute notifications until the last retry keeps them quiet until the final attempt.
• Skipped and cancelled runs can be muted.
• For a team channel instead of email, an admin sets up a Slack, Microsoft Teams, PagerDuty or webhook destination, which then appears in the same list.

4. The same job as code

With the Databricks CLI, define the job in JSON:

{
  "name": "Daily Planet report",
  "tasks": [{
    "task_key": "report",
    "notebook_task": { "notebook_path": "/Workspace/Shared/reports/daily" }
  }],
  "schedule": { "quartz_cron_expression": "0 0 6 * * ?", "timezone_id": "America/New_York" },
  "email_notifications": {
    "on_success": ["newsroom@dailyplanet.example"],
    "on_failure": ["newsroom@dailyplanet.example"]
  }
}

databricks jobs create --json @daily-report.json

A task with no compute settings runs on serverless compute, where the workspace has it. For jobs kept in Git, a Databricks Asset Bundle describes the same job in YAML and deploys it with databricks bundle deploy.

Good practice

• Send alerts to a group address, not to one person.
• Make the notebook fail loudly on bad data (raise an exception), so the failure email means something.
• Set a timeout and retries on the task so a hung run does not block the next day's.