PySpark and Databricks Interview Questions

Quick answer

Most PySpark and Databricks interview questions come from six areas: DataFrame transformations, Spark SQL with joins and window functions, Spark architecture (driver, executors, stages, shuffle), performance tuning (partitions, skew, broadcast joins, caching), Delta Lake (MERGE, time travel, OPTIMIZE) and Databricks platform features (clusters, Unity Catalog, Auto Loader, Lakeflow Jobs). Freshers are asked definitions and simple DataFrame code; engineers with 3+ years get live coding and scenarios such as "fix a skewed join", "remove duplicates on rerun" or "build an incremental pipeline". Answer with the API you would use, why it is fast, and how you would prove the result.

Key takeaways

1. Know lazy evaluation and the shuffle: most "why is my job slow" questions come back to wide transformations.
2. Coding rounds are standard: practise window functions, deduplication, explode and top-N per group in PySpark.
3. Delta Lake is tested in every Databricks interview: MERGE, time travel, OPTIMIZE, VACUUM and schema evolution.
4. Use current Databricks names: Unity Catalog, Lakeflow Jobs, Lakeflow pipelines, liquid clustering and serverless compute.
5. Experienced roles focus on tuning and design: skew, small files, cost, medallion architecture and CI/CD.

1. PySpark and Databricks interview questions at a glance

Short answer: This page has 150 questions in 10 topic groups. Freshers can start with sections 2 to 4; experienced engineers should focus on sections 5 to 11.

What interviewers check at each level:

2. PySpark interview questions for freshers (basics)

Q1. What is PySpark?

Answer: PySpark is the Python API for Apache Spark, an open-source engine for processing large data in parallel across many machines. It lets you write Spark jobs in Python while the heavy work runs on Spark's distributed engine. Data engineers use it for batch ETL, streaming, SQL analytics and machine learning.

How it works:

• You write Python code using the DataFrame API or Spark SQL.
• The driver turns your code into an optimized plan and splits the work into tasks.
• Executors run the tasks in parallel on the partitions of your data.
• Built-in functions run inside the engine, so Python code is mostly just a description of the work.

Example: A fintech company receives crores of UPI payment records every day. A PySpark job on Databricks reads the raw JSON files from ADLS Gen2, removes failed transactions, adds the payment date and writes clean Delta tables for reporting.

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("upi_daily").getOrCreate()
df = spark.read.json("/mnt/raw/upi/2026-10-20/")
clean = df.filter(F.col("status") == "SUCCESS").withColumn("txn_date", F.to_date("txn_ts"))
clean.write.format("delta").mode("append").saveAsTable("silver.upi_payments")

Interview tip: Say clearly that PySpark is an API and Spark is the engine; many freshers mix the two.

Q2. What is the difference between Apache Spark and Hadoop MapReduce?

Answer: Both process big data in parallel, but Spark keeps intermediate data in memory where possible, while MapReduce writes the output of every map and reduce step to disk. This makes Spark much faster for multi-step and iterative jobs. Spark also gives one engine for batch, SQL, streaming and machine learning.

How it works:

Example: A telecom team ran a five-step customer churn job as five chained MapReduce jobs, each writing to HDFS. Rewritten in PySpark, the same logic became one job with one plan, and only the shuffle steps touched disk.

Interview tip: Do not say Spark replaces Hadoop completely; Spark can still run on YARN and read from HDFS, it replaces only the MapReduce engine.

Q3. What is SparkSession and why do you need it?

Answer: SparkSession is the single entry point to Spark functionality in PySpark. You use it to read data, create DataFrames, run SQL and set configurations. Since Spark 2.0, it replaced the separate SQLContext and HiveContext.

How it works:

• SparkSession.builder creates a session or returns the existing one with getOrCreate().
• spark.read reads files and tables, and spark.sql() runs SQL queries.
• spark.conf.set() changes session settings such as shuffle partitions.
• In Databricks notebooks, a session named spark is already created for you.

Example:

from pyspark.sql import SparkSession

spark = (SparkSession.builder
         .appName("orders_etl")
         .config("spark.sql.shuffle.partitions", "64")
         .getOrCreate())

orders = spark.read.parquet("/data/orders/")
spark.sql("SELECT current_date() AS today").show()

Interview tip: Mention that in Databricks you should not create a new session or stop spark, because the platform manages it.

Q4. What is the difference between RDD, DataFrame and Dataset?

Answer: An RDD is a low-level distributed collection of objects with no schema. A DataFrame is a distributed table with named columns and a schema, optimized by Catalyst. A Dataset is a typed version of a DataFrame available only in Scala and Java; in Python you use DataFrames.

How it works:

Example: Counting orders by city with an RDD needs map and reduceByKey with Python lambdas. With a DataFrame, df.groupBy("city").count() is shorter and runs faster because Spark can optimize it.

Interview tip: Say "in PySpark, a DataFrame is the Python equivalent of Dataset[Row]", which shows you know why Python has no Dataset API.

Q5. What is lazy evaluation in Spark?

Answer: Lazy evaluation means Spark does not run transformations when you write them. It only records them in a plan, and runs the whole plan when you call an action such as count(), show() or write. This lets Spark optimize the full chain before running anything.

How it works:

• Transformations like filter, select and join build a logical plan.
• When an action is called, Catalyst optimizes the plan, for example by pushing filters down to the file scan.
• Spark then creates a job, splits it into stages and runs tasks.
• Without an action, nothing is read or computed.

Example:

df = spark.read.parquet("/data/sales/") # nothing runs yet
big = df.filter(df.amount_inr > 10000) # nothing runs yet
by_city = big.groupBy("city").sum("amount_inr") # nothing runs yet
by_city.show() # action: the job runs now

Interview tip: Mention a side effect: errors such as a wrong file path or bad cast may only appear at the action, so debugging needs care.

Q6. What is the difference between transformations and actions?

Answer: A transformation creates a new DataFrame from an existing one and is lazy. An action triggers the actual computation and either returns a result to the driver or writes data out. Every Spark job starts with an action.

How it works:

• DataFrames are immutable, so each transformation returns a new DataFrame.
• One action can create one or more Spark jobs.

Example: df.filter(F.col("state") == "Telangana").select("customer_id") only builds a plan. Adding .count() makes Spark read the files and return a number to the driver.

Interview tip: Interviewers often ask whether printSchema() is an action. It does not run a job on the data; it reads the schema from the plan, but schema inference while reading can itself trigger a small job.

Q7. What is the difference between narrow and wide transformations?

Answer: In a narrow transformation, each output partition depends on only one input partition, so no data moves between executors. In a wide transformation, an output partition needs data from many input partitions, so Spark must shuffle data across the network. Wide transformations create new stages.

How it works:

• Narrow: select, filter, withColumn, map, union, coalesce.
• Wide: groupBy, join (except broadcast joins), distinct, orderBy, repartition.
• Spark pipelines narrow transformations together inside one stage.
• Each wide transformation adds a stage boundary and shuffle files.

Example: Filtering orders above 5,000 rupees is narrow; each partition is filtered on its own. Summing amount per customer is wide; all rows of one customer must reach the same partition, so Spark shuffles.

Interview tip: Link this to performance: "fewer wide transformations means fewer shuffles", and mention that a broadcast join avoids shuffling the big table.

Q8. Why should you use DataFrames instead of RDDs?

Answer: DataFrames are faster and easier to use because Spark knows their schema and can optimize them with the Catalyst optimizer and the Tungsten engine. RDD code with Python lambdas is a black box to Spark and also pays the cost of moving data between the JVM and Python. Use RDDs only for rare low-level needs.

How it works:

• Catalyst rewrites DataFrame plans: predicate pushdown, column pruning and join reordering.
• Tungsten stores data in a compact binary format and generates efficient code.
• DataFrame built-in functions run inside the JVM, so no Python serialization is needed.
• DataFrames work the same from Python, SQL and Scala.

Example:

# RDD style (slow in Python)
rdd = sc.textFile("/data/orders.csv").map(lambda l: l.split(","))
totals = rdd.map(lambda r: (r[1], float(r[3]))).reduceByKey(lambda a, b: a + b)

# DataFrame style (optimized)
orders = spark.read.option("header", True).csv("/data/orders.csv", inferSchema=True)
totals_df = orders.groupBy("city").agg(F.sum("amount").alias("total"))

Interview tip: Mention that Spark Connect and Databricks serverless compute do not support the RDD API, which is another reason to avoid it in new code.

Q9. How do you read CSV, JSON and Parquet files in PySpark?

Answer: You use spark.read with a format-specific method or format().load(). CSV needs options like header and a schema, JSON can be single-line or multi-line, and Parquet already carries its schema. The result is always a DataFrame.

How it works:

• CSV: set header, sep, and either inferSchema or an explicit schema.
• JSON: by default Spark expects one JSON object per line; use multiLine for pretty-printed files.
• Parquet: no options needed; schema and column statistics come from the file.
• Use mode (PERMISSIVE, DROPMALFORMED, FAILFAST) to control bad records in CSV and JSON.

Example:

csv_df = (spark.read.option("header", True).option("sep", ",")
          .option("mode", "PERMISSIVE").schema(order_schema)
          .csv("/landing/orders/*.csv"))

json_df = spark.read.option("multiLine", True).json("/landing/customers/")

pq_df = spark.read.parquet("/bronze/payments/")
delta_df = spark.read.table("silver.orders") # Delta or catalog table

Interview tip: Say you use FAILFAST in tests and PERMISSIVE with a corrupt-record column in production, so bad rows are captured and not silently lost.

Q10. What is the difference between inferSchema and defining a schema with StructType?

Answer: inferSchema asks Spark to scan the data and guess the column types, while a StructType schema tells Spark the exact columns and types up front. An explicit schema is faster, safer and the right choice for production pipelines.

How it works:

• Inference needs an extra pass over the data, which is slow for large CSV and JSON files.
• Guessing can be wrong, for example PIN codes or phone numbers read as integers and losing leading zeros.
• An explicit schema gives the same types on every run, even if one day's file looks different.
• You can also pass a DDL string such as "order_id STRING, amount DOUBLE".

Example:

from pyspark.sql.types import StructType, StructField, StringType, DoubleType, DateType

order_schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("customer_id", StringType(), True),
    StructField("amount_inr", DoubleType(), True),
    StructField("order_date", DateType(), True),
])
orders = spark.read.option("header", True).schema(order_schema).csv("/landing/orders/")

Interview tip: Mention that the third argument of StructField is nullable, and that Spark does not strictly enforce non-null for file sources, so you still need data quality checks.

Q11. What is the difference between Parquet and CSV?

Answer: Parquet is a compressed, columnar binary format that stores the schema and column statistics, while CSV is plain row-based text with no types. Parquet files are smaller and much faster to query because Spark reads only the needed columns and can skip row groups using statistics.

How it works:

Example: A sales table with 60 columns stored as CSV forces Spark to read every byte even if a report needs only order_date and amount_inr. As Parquet, Spark reads just those two columns.

Interview tip: Add that Delta Lake stores data as Parquet files plus a transaction log, so learning Parquet behaviour helps you understand Delta.

Q12. What is a partition in Spark?

Answer: A partition is a chunk of a DataFrame's rows that one task processes on one executor core. Spark achieves parallelism by processing many partitions at the same time. The number and size of partitions strongly affect performance.

How it works:

• When reading files, Spark creates partitions of up to spark.sql.files.maxPartitionBytes, which defaults to 128 MB.
• After a shuffle, the number of partitions comes from spark.sql.shuffle.partitions, which defaults to 200, though AQE can coalesce them at runtime.
• One partition is processed by one task, and one core runs one task at a time.
• Too few partitions waste cores; too many tiny partitions add scheduling overhead.

Example:

df = spark.read.parquet("/bronze/orders/")
print(df.rdd.getNumPartitions()) # partitions from the file scan
by_city = df.groupBy("city").count() # shuffle -> shuffle partitions
df2 = df.repartition(48, "customer_id") # explicit 48 partitions by key

Interview tip: Do not confuse in-memory partitions with partitionBy folders on disk; interviewers test this difference often.

Q13. Why is collect() dangerous on large data?

Answer: collect() brings every row of a DataFrame from all executors back to the driver's memory. On large data this can crash the driver with an out-of-memory error or hit the spark.driver.maxResultSize limit. Use it only on small, already-aggregated results.

How it works:

• Executors process data in parallel, but the driver is a single process with limited memory.
• collect() and toPandas() both pull all rows to the driver.
• Safer options are show(), take(n), limit(n), or writing the result to a table.
• If you must loop over results, aggregate first or use toLocalIterator(), which brings one partition at a time.

Example:

# Risky on 500 million rows
rows = spark.table("silver.upi_payments").collect()

# Safe alternatives
spark.table("silver.upi_payments").show(20)
top = spark.table("gold.daily_totals").orderBy(F.desc("total_inr")).limit(10).collect()

Interview tip: Say you check the size before collecting and prefer writing results to Delta tables; this is a common real-world driver OOM cause.

Q14. What is the difference between show(), display() and take()?

Answer: show() prints the first rows of a DataFrame as text, 20 by default. take(n) returns the first n rows to the driver as a Python list of Row objects. display() is a Databricks notebook function that renders an interactive table with sorting, charts and download options.

How it works:

• show(n, truncate=False) prints n rows without cutting long strings.
• take(n) and head(n) return Row objects you can use in Python code.
• display(df) works only in Databricks (and some notebook tools), not in plain PySpark scripts.
• All three are actions and trigger a job, but they read only as much data as needed.

Example:

df.show(5, truncate=False) # text output
first_rows = df.take(3) # list of Row objects
print(first_rows[0]["customer_id"])
display(df.filter("amount_inr > 50000")) # Databricks notebook only

Interview tip: Mention that display() code will fail in a spark-submit job or local PySpark, so production code should not depend on it.

Q15. What are the common use cases of Apache Spark?

Answer: Spark is used for large-scale batch ETL, real-time streaming, interactive SQL analytics, machine learning and data preparation for AI. In most companies, it powers the data lake or lakehouse layer that feeds dashboards and models. Databricks, Azure Synapse, Microsoft Fabric, AWS EMR and Google Dataproc all run Spark.

How it works:

• Batch ETL: clean and join data from many sources into Bronze, Silver and Gold tables.
• Streaming: process events from Kafka or Event Hubs with Structured Streaming.
• SQL analytics: query large tables with Spark SQL or Databricks SQL.
• Machine learning: build features and train models with MLlib or Python libraries.

Example: An e-commerce company uses Spark to load daily orders from MySQL, stream clickstream events from Kafka, build a customer 360 table and compute product recommendation features for its app.

Interview tip: Mention one use case from your own project with data size and frequency; interviewers value real context more than a textbook list.

3. DataFrame operations and transformations

Q16. What is the difference between select, withColumn and withColumnRenamed?

Answer: select picks or computes the columns you want in the output. withColumn adds a new column or replaces an existing one while keeping all other columns. withColumnRenamed changes a column's name without changing its data.

How it works:

• select can take column names, expressions and aliases in one step.
• Each withColumn call adds a projection to the plan; many calls in a loop can make the plan very large and slow to analyze.
• Use withColumns (Spark 3.3+) to add many columns at once, or a single select.
• withColumnsRenamed (Spark 3.4+) renames many columns using a dictionary.

Example:

orders2 = (orders
    .select("order_id", "customer_id", "amount_inr", "order_ts")
    .withColumn("gst_inr", F.round(F.col("amount_inr") * 0.18, 2))
    .withColumnRenamed("order_ts", "order_timestamp"))

orders3 = orders2.withColumns({"order_date": F.to_date("order_timestamp"),
                               "is_big": F.col("amount_inr") > 10000})

Interview tip: Mention the "withColumn in a loop" problem; experienced interviewers ask about it because it causes slow planning and even stack overflow errors on wide tables.

Q17. What is the difference between filter() and where()?

Answer: There is no difference; where() is an alias of filter(). Both keep rows that match a condition, and you can pass either a Column expression or a SQL string. Pick one style and use it consistently.

How it works:

• Combine conditions with & (and), | (or) and ~ (not), and wrap each condition in brackets.
• Python and and or do not work on Column objects and raise an error.
• isin(), between(), like(), isNull() and isNotNull() cover most filters.
• Filters on Parquet or Delta sources are pushed down to the file scan.

Example:

big_hyd = orders.filter((F.col("city") == "Hyderabad") & (F.col("amount_inr") > 5000))
south = orders.where(F.col("state").isin("Telangana", "Karnataka", "Tamil Nadu"))
recent = orders.where("order_date >= '2026-10-01' AND status <> 'CANCELLED'")

Interview tip: Interviewers often show code with and between two conditions and ask why it fails; the answer is that Python cannot convert a Column to a boolean, so use & with brackets.

Q18. How do you handle null values in PySpark?

Answer: You use dropna() (or na.drop()) to remove rows with nulls, fillna() (or na.fill()) to replace nulls with default values, and F.coalesce() to pick the first non-null value from several columns. The right choice depends on business rules, not just convenience.

How it works:

• dropna(how="any", subset=[...]) drops rows where any listed column is null; how="all" drops only fully null rows.
• fillna({"city": "Unknown", "loyalty_points": 0}) sets defaults per column; the value type must match the column type.
• F.coalesce(a, b, c) returns the first non-null value per row.
• isNull() and isNotNull() filter nulls; comparing with == None does not work as expected.

Example:

cust = (customers
    .dropna(subset=["customer_id"])
    .fillna({"city": "Unknown", "loyalty_points": 0})
    .withColumn("contact", F.coalesce("mobile", "alt_mobile", F.lit("NA"))))

null_city = customers.filter(F.col("city").isNull())

Interview tip: Say you never blindly drop nulls; you first count them per column and agree with the business whether to drop, default or quarantine those rows.

Q19. How do you use when() and otherwise() in PySpark?

Answer: F.when() with .otherwise() works like a SQL CASE WHEN expression. You chain one or more conditions with values, and otherwise sets the value when no condition matches. If you leave out otherwise, unmatched rows get null.

How it works:

• Conditions are checked in order, and the first match wins.
• Each condition is a Column expression, so use &, | and brackets.
• You can use the result in withColumn, select, groupBy or agg.
• The same logic can be written as a SQL string with F.expr("CASE WHEN ... END").

Example:

orders = orders.withColumn(
    "order_band",
    F.when(F.col("amount_inr") >= 50000, "HIGH")
     .when(F.col("amount_inr") >= 10000, "MEDIUM")
     .when(F.col("amount_inr") > 0, "LOW")
     .otherwise("INVALID"))

Interview tip: Mention the null trap: rows where amount_inr is null match no condition and fall into otherwise, so handle nulls explicitly if they need their own label.

Q20. How do groupBy and agg work in PySpark?

Answer: groupBy() groups rows by one or more columns, and agg() computes one or more aggregate functions for each group, such as sum, count, avg, min and max. It is a wide transformation, so it causes a shuffle.

How it works:

• Spark first does partial aggregation on each partition, then shuffles the partial results by key and finishes the aggregation.
• Use .alias() to give aggregate columns clean names.
• F.countDistinct() gives an exact distinct count; F.approx_count_distinct() is faster for huge data.
• Filter on aggregated values after agg, which is the same as SQL HAVING.

Example:

city_sales = (orders
    .groupBy("city", "order_date")
    .agg(F.sum("amount_inr").alias("total_inr"),
         F.count("order_id").alias("orders"),
         F.countDistinct("customer_id").alias("customers"),
         F.round(F.avg("amount_inr"), 2).alias("avg_order_inr"))
    .filter(F.col("total_inr") > 100000))

Interview tip: Say that F.count("col") skips nulls, while F.count(F.lit(1)) counts every row; interviewers test this difference.

Q21. What is the difference between distinct() and dropDuplicates()?

Answer: distinct() removes rows that are duplicates across all columns. dropDuplicates() does the same with no arguments, but it can also take a list of columns and keep one row for each unique combination of those columns. Both cause a shuffle.

How it works:

• df.distinct() gives the same result as df.dropDuplicates().
• df.dropDuplicates(["order_id"]) keeps one row per order_id, but which row is kept is not guaranteed.
• To keep a specific row, such as the latest update, use a window with row_number() instead.
• In Structured Streaming, combine dropDuplicates with a watermark to limit state.

Example:

unique_rows = orders.distinct()
one_per_order = orders.dropDuplicates(["order_id"]) # arbitrary row kept

from pyspark.sql import Window
w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())
latest = (orders.withColumn("rn", F.row_number().over(w))
                .filter("rn = 1").drop("rn"))

Interview tip: The common mistake is saying dropDuplicates keeps the first row; say it keeps an arbitrary row and use a window when the business needs the latest record.

Q22. What is the difference between union() and unionByName()?

Answer: union() combines two DataFrames by column position, while unionByName() matches columns by name. If the column order differs, union() can silently put data into the wrong columns, so unionByName() is safer. Neither removes duplicates; add distinct() if needed.

How it works:

• Both DataFrames must have the same number of columns for union().
• unionByName(other, allowMissingColumns=True) fills missing columns with null (Spark 3.1+).
• unionAll() is an old alias of union().
• Union is a narrow transformation and does not shuffle.

Example:

jan = spark.read.parquet("/bronze/orders/2026-01/") # order_id, city, amount_inr
feb = spark.read.parquet("/bronze/orders/2026-02/") # order_id, amount_inr, city, channel

risky = jan.union(feb.select("order_id", "city", "amount_inr")) # correct only if order matches
safe = jan.unionByName(feb, allowMissingColumns=True) # channel is null for Jan

Interview tip: Mention that SQL UNION removes duplicates but DataFrame union() keeps them, matching SQL UNION ALL.

Q23. How do you work with arrays and structs, and what does explode() do?

Answer: Arrays hold a list of values in one column and structs hold named nested fields. explode() turns each element of an array (or each key-value pair of a map) into its own row. You read struct fields with dot notation, such as customer.city.

How it works:

• explode() drops rows where the array is null or empty; explode_outer() keeps them with null.
• posexplode() also returns the element's position.
• F.col("items.sku") on an array of structs returns an array of that field.
• F.split(), F.array_contains(), F.size() and F.collect_list() are common array functions.

Example:

# order JSON: {order_id, customer: {name, city}, items: [{sku, qty, price}]}
lines = (orders
    .select("order_id", F.col("customer.city").alias("city"),
            F.explode_outer("items").alias("item"))
    .select("order_id", "city", "item.sku", "item.qty",
            (F.col("item.qty") * F.col("item.price")).alias("line_total_inr")))

Interview tip: Mention the row explosion risk: exploding two arrays one after another multiplies rows, so explode one level at a time and check row counts.

Q24. Which date and timestamp functions do you use most in PySpark?

Answer: The most used ones are to_date, to_timestamp, date_format, current_date, datediff, date_add, add_months, months_between, year, month, dayofweek, trunc and date_trunc. They convert strings to dates, extract parts and do date arithmetic.

How it works:

• to_date(col, "dd-MM-yyyy") parses a string using a datetime pattern.
• date_format(col, "yyyy-MM") turns a date into a formatted string.
• datediff(end, start) returns the number of days between two dates.
• trunc(date, "month") gives the first day of the month, and date_trunc("hour", ts) truncates timestamps.
• Set spark.sql.session.timeZone (for example Asia/Kolkata) so timestamps show in IST consistently.

Example:

orders = (orders
    .withColumn("order_date", F.to_date("order_dt_str", "dd-MM-yyyy"))
    .withColumn("order_month", F.date_format("order_date", "yyyy-MM"))
    .withColumn("days_to_deliver", F.datediff("delivery_date", "order_date"))
    .withColumn("month_start", F.trunc("order_date", "month"))
    .withColumn("due_date", F.date_add("order_date", 7)))

Interview tip: Mention pattern case: MM is month and mm is minutes; mixing them up is one of the most common date bugs.

Q25. Which string functions are commonly used in PySpark?

Answer: Common string functions are trim, upper, lower, initcap, concat_ws, substring, split, regexp_replace, regexp_extract, lpad, length and like. They clean and standardize text such as names, phone numbers and codes before joins and reports.

How it works:

• trim and upper or lower fix spacing and case so joins match.
• regexp_replace removes unwanted characters; regexp_extract pulls out a pattern.
• concat_ws(sep, ...) joins columns and skips nulls, while concat returns null if any input is null.
• split returns an array that you can index or explode.

Example:

cust = (customers
    .withColumn("name", F.initcap(F.trim("name")))
    .withColumn("mobile", F.regexp_replace("mobile", r"[^0-9]", ""))
    .withColumn("mobile", F.substring("mobile", -10, 10))
    .withColumn("pincode", F.lpad(F.col("pincode").cast("string"), 6, "0"))
    .withColumn("full_address", F.concat_ws(", ", "street", "city", "state")))

Interview tip: Mention the concat vs concat_ws null behaviour; it explains many "why is my full address empty" bugs.

Q26. How do you cast column types in PySpark, and what happens with bad values?

Answer: You change a column's type with F.col("x").cast("int") or a type object such as DecimalType(12, 2). What happens to bad values depends on ANSI mode: with ANSI on, an invalid cast raises an error; with ANSI off, it returns null. ANSI mode is on by default from Spark 4.0.

How it works:

• try_cast returns null for malformed input even when ANSI mode is on.
• Use DecimalType for money so rupee amounts do not get floating-point rounding errors.
• printSchema() and df.dtypes show the current types.
• Casting string dates needs to_date with a format if the string is not in yyyy-MM-dd form.

Example:

from pyspark.sql.types import DecimalType

payments = (raw
    .withColumn("amount_inr", F.col("amount").cast(DecimalType(12, 2)))
    .withColumn("txn_count", F.expr("try_cast(txn_count_str AS INT)")))

bad_rows = payments.filter(F.col("txn_count").isNull() & F.col("txn_count_str").isNotNull())

Interview tip: Say you count rows that became null after a cast, because silent nulls from bad data are a classic data quality issue.

Q27. What is the difference between a Python UDF and built-in functions?

Answer: Built-in functions in pyspark.sql.functions run inside Spark's JVM engine and are optimized by Catalyst. A regular Python UDF runs row by row in a separate Python worker, so data must be serialized between the JVM and Python. Built-ins are usually much faster, so use a UDF only when no built-in function can do the job.

How it works:

• Catalyst cannot see inside a UDF, so it cannot optimize the logic or push filters through it.
• Every row is converted from JVM format to Python and back.
• You must declare the return type; a wrong type gives nulls or errors.
• Check F.expr, higher-order functions such as transform and filter, and SQL functions before writing a UDF.

Example:

from pyspark.sql.types import StringType

@F.udf(returnType=StringType())
def mask_mobile(m):
    return None if m is None else "XXXXXX" + m[-4:]

slow = customers.withColumn("mobile_masked", mask_mobile("mobile"))
fast = customers.withColumn("mobile_masked",
                            F.concat(F.lit("XXXXXX"), F.substring("mobile", -4, 4)))

Interview tip: Interviewers like it when you rewrite their UDF with built-ins on the spot, so practise common ones like masking, parsing and code mapping.

Q28. What is a pandas UDF and when do you use it?

Answer: A pandas UDF (vectorized UDF) processes data in batches as pandas Series or DataFrames, using Apache Arrow to move data between the JVM and Python. It is usually much faster than a row-by-row Python UDF. Use it when you need Python logic or libraries that have no Spark built-in equivalent.

How it works:

• Define it with @F.pandas_udf and Python type hints, such as pd.Series -> pd.Series.
• Arrow transfers columns in batches, which cuts serialization cost.
• Grouped logic uses groupBy().applyInPandas(), and batch-level logic uses mapInPandas().
• Built-in functions are still faster when they can do the job.

Example:

import pandas as pd

@F.pandas_udf("double")
def gst_inclusive(amount: pd.Series, rate: pd.Series) -> pd.Series:
    return (amount * (1 + rate / 100)).round(2)

orders = orders.withColumn("total_with_gst", gst_inclusive("amount_inr", "gst_rate"))

Interview tip: Mention applyInPandas for per-group work, such as training one small forecast model per store; it shows you know more than the basic Series UDF.

Q29. What are the save modes when writing a DataFrame?

Answer: Spark has four save modes: append adds data to existing data, overwrite replaces it, errorifexists (also error, the default) fails if data already exists, and ignore does nothing if data already exists. You set it with .mode() on the writer.

How it works:

• On a Delta table, overwrite is an atomic transaction, and old versions stay available for time travel until vacuumed.
• On plain Parquet, an overwrite deletes old files first, so a failure mid-write can leave the folder incomplete.

Example:

(daily_sales.write.format("delta")
    .mode("append")
    .saveAsTable("gold.daily_sales"))

(dim_customer.write.format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .saveAsTable("gold.dim_customer"))

Interview tip: Say that a blind append is not rerun-safe; for incremental loads you prefer Delta MERGE or replaceWhere, so a rerun does not create duplicates.

Q30. What does partitionBy() do when writing data?

Answer: partitionBy() on the DataFrame writer splits output into folders by the values of one or more columns, such as order_date=2026-10-20/. Queries that filter on those columns can then skip whole folders, which is called partition pruning. It controls the layout on disk, not the number of in-memory partitions.

How it works:

• Choose low-cardinality columns that are used in filters, like date or country.
• Avoid high-cardinality columns such as customer_id; they create huge numbers of tiny files.
• Overwriting a partitioned Parquet table replaces all partitions unless you set spark.sql.sources.partitionOverwriteMode to dynamic.
• For Delta tables, use replaceWhere to overwrite only selected data.

Example:

(orders.write.format("delta")
    .mode("overwrite")
    .option("replaceWhere", "order_date >= '2026-10-01' AND order_date < '2026-10-21'")
    .partitionBy("order_date")
    .saveAsTable("silver.orders"))

Interview tip: Mention that Databricks advises not partitioning tables under 1 TB, keeping each partition at least 1 GB, and using liquid clustering for managed tables instead.

4. Spark SQL, joins and window functions

Q31. What are temporary views, and how do you run SQL with spark.sql()?

Answer: A temporary view gives a DataFrame a name so you can query it with SQL through spark.sql(). A temp view lives only in the current SparkSession and is not stored in the catalog. spark.sql() returns a normal DataFrame, so you can mix SQL and the DataFrame API freely.

How it works:

• createOrReplaceTempView("name") creates a session-scoped view.
• createOrReplaceGlobalTempView("name") creates a view shared across sessions in the same application, read as global_temp.name.
• Views store only the query plan, not data.
• For tables that other jobs and users must see, save a real table, such as a Delta table in Unity Catalog.

Example:

orders.createOrReplaceTempView("orders_v")

top_cities = spark.sql("""
    SELECT city, SUM(amount_inr) AS total_inr
    FROM orders_v
    WHERE order_date >= '2026-10-01'
    GROUP BY city
    ORDER BY total_inr DESC
""")
top_cities.show(5)

Interview tip: Say that SQL on a temp view and the same DataFrame code produce the same optimized plan; the view is just a name for the plan.

Q32. What join types does PySpark support, and what are left semi and left anti joins?

Answer: PySpark supports inner, left, right, full outer, cross, left semi and left anti joins. A left semi join returns rows from the left table that have a match on the right, with only left columns. A left anti join returns rows from the left table that have no match on the right.

How it works:

Example:

active_customers = customers.join(orders, "customer_id", "left_semi")
never_ordered = customers.join(orders, "customer_id", "left_anti")
missing_in_target = source_df.join(target_df, "order_id", "left_anti") # reconciliation

Interview tip: Mention that a semi join never duplicates left rows, even if many right rows match, unlike an inner join followed by select.

Q33. What is a broadcast join and when should you use it?

Answer: A broadcast join sends a full copy of the small table to every executor, so the big table is joined in place without being shuffled. Spark does this automatically when a table's estimated size is below spark.sql.autoBroadcastJoinThreshold, which defaults to 10 MB. You can also force it with F.broadcast() or a SQL hint.

How it works:

• The small side is collected to the driver and then sent to all executors, so it must fit comfortably in memory.
• The large side is not shuffled, which saves network and disk I/O.
• AQE can switch a sort-merge join to a broadcast join at runtime if one side turns out to be small.
• Set the threshold to -1 to turn off automatic broadcasting.

Example:

from pyspark.sql.functions import broadcast

enriched = orders.join(broadcast(dim_city), "city_code", "left")

The same hint in Spark SQL:

SELECT /*+ BROADCAST(c) */ o.order_id, c.city_name
FROM orders o JOIN dim_city c ON o.city_code = c.city_code;

Interview tip: Warn against broadcasting large tables; it can cause driver or executor out-of-memory errors and broadcast timeouts.

Q34. How do you join on multiple columns and avoid duplicate columns?

Answer: Pass a list of column names to join() when the key columns have the same names on both sides; Spark then keeps only one copy of each key. When the names differ, use a combined condition with &, and then drop or rename the duplicate columns. Aliases help when both sides share many column names.

How it works:

• df1.join(df2, ["store_id", "sale_date"]) gives one store_id and one sale_date column.
• df1.join(df2, df1.id == df2.id) keeps both id columns, which causes "ambiguous column" errors later.
• Use alias("o") and alias("p"), then select F.col("o.amount") to be clear.

Example:

daily = sales.join(targets, ["store_id", "sale_date"], "left")

o = orders.alias("o")
p = payments.alias("p")
matched = (o.join(p, (F.col("o.order_id") == F.col("p.ref_order_id")) &
                     (F.col("o.customer_id") == F.col("p.customer_id")), "inner")
            .select("o.order_id", "o.customer_id", "o.amount_inr",
                    F.col("p.amount_inr").alias("paid_inr")))

Interview tip: Mention that you check row counts before and after a join, because duplicate keys on the right side silently multiply rows.

Q35. What is the difference between row_number(), rank() and dense_rank()?

Answer: All three number rows within a window partition based on an order. row_number() gives unique numbers 1, 2, 3 even for ties. rank() gives tied rows the same number and leaves gaps (1, 1, 3). dense_rank() gives tied rows the same number with no gaps (1, 1, 2).

How it works:

• Define the window with Window.partitionBy(...).orderBy(...).
• For ties in row_number(), add a tie-breaker column to make the result repeatable.

Example:

from pyspark.sql import Window

w = Window.partitionBy("dept_id").orderBy(F.col("salary").desc())
ranked = (employees
    .withColumn("rn", F.row_number().over(w))
    .withColumn("rnk", F.rank().over(w))
    .withColumn("drnk", F.dense_rank().over(w)))
second_highest = ranked.filter("drnk = 2")

Interview tip: For "second highest salary" questions, say you use dense_rank so ties at the top do not hide the real second-highest value.

Q36. How do lag() and lead() work?

Answer: lag() returns a value from a previous row in the window, and lead() returns a value from a following row. You use them to compare a row with the one before or after it, such as day-over-day sales change. If no such row exists, they return null or a default you provide.

How it works:

• Signature: F.lag(col, offset=1, default=None) and the same for lead.
• The window needs orderBy, and usually partitionBy for each entity.
• Common uses: change from previous value, time gap between events, next status in a flow.

Example:

w = Window.partitionBy("store_id").orderBy("sale_date")

trend = (daily_sales
    .withColumn("prev_sales", F.lag("sales_inr", 1).over(w))
    .withColumn("change_inr", F.col("sales_inr") - F.col("prev_sales"))
    .withColumn("next_sale_date", F.lead("sale_date", 1).over(w))
    .withColumn("gap_days", F.datediff("next_sale_date", "sale_date")))

Interview tip: Mention that a window with no partitionBy moves all data into one partition, which is slow on large data, and Spark logs a warning about it.

Q37. How do you calculate a running total in PySpark?

Answer: You use F.sum() over a window that is partitioned by the group, ordered by date, and framed from the first row up to the current row. Each row then shows the cumulative total so far for its group.

How it works:

• Frame: rowsBetween(Window.unboundedPreceding, Window.currentRow).
• If you only add orderBy, Spark's default frame is a RANGE frame, which treats rows with the same order value as one group and gives them the same total.
• Use ROWS when you need a strict row-by-row total, and add a tie-breaker in orderBy.
• The same pattern with F.avg and rowsBetween(-6, 0) gives a 7-row moving average.

Example:

w = (Window.partitionBy("customer_id")
           .orderBy("txn_ts", "txn_id")
           .rowsBetween(Window.unboundedPreceding, Window.currentRow))

running = upi_txns.withColumn("running_spend_inr", F.sum("amount_inr").over(w))

w7 = Window.partitionBy("store_id").orderBy("sale_date").rowsBetween(-6, 0)
moving = daily_sales.withColumn("avg_7d_inr", F.avg("sales_inr").over(w7))

Interview tip: Explaining the ROWS vs RANGE default frame difference is a strong signal of real experience.

Q38. How do you get the top N records per group?

Answer: Number rows inside each group with a window function ordered by the ranking column, then filter where the number is N or less. Use row_number() for exactly N rows, or dense_rank() if tied rows should all be included.

How it works:

• Partition by the group column, such as category.
• Order by the metric descending, such as revenue.
• Filter on the rank and drop the helper column.
• This needs one shuffle by the group key, which scales much better than looping over groups.

Example:

w = Window.partitionBy("category").orderBy(F.col("revenue_inr").desc(), F.col("product_id"))

top3 = (product_sales
    .withColumn("rn", F.row_number().over(w))
    .filter(F.col("rn") <= 3)
    .drop("rn"))

In Databricks SQL, the QUALIFY clause does the same in one query:

SELECT * FROM product_sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue_inr DESC) <= 3;

Interview tip: Ask the interviewer how ties should be handled before you choose row_number or dense_rank; clarifying requirements scores points.

Q39. How do you pivot and unpivot data in PySpark?

Answer: Pivot turns row values into columns using groupBy().pivot().agg(), such as one column per month. Unpivot does the reverse and turns columns into rows; from Spark 3.4 you can use DataFrame.unpivot() (alias melt()), and older versions use the SQL stack() function.

How it works:

• Pass the list of pivot values, such as ["Jan", "Feb", "Mar"], so Spark does not run an extra job to find distinct values.
• Pivot creates one column per value, so avoid pivoting on high-cardinality columns.
• unpivot(ids, values, variableColumnName, valueColumnName) needs value columns of compatible types.

Example:

monthly = (sales
    .groupBy("region")
    .pivot("month", ["Jan", "Feb", "Mar"])
    .agg(F.sum("amount_inr")))

back_to_rows = monthly.unpivot("region", ["Jan", "Feb", "Mar"], "month", "amount_inr")

old_way = monthly.select("region",
    F.expr("stack(3, 'Jan', Jan, 'Feb', Feb, 'Mar', Mar) AS (month, amount_inr)"))

Interview tip: Mention passing explicit pivot values; it is a small change that interviewers recognise as a performance habit.

Q40. Is Spark SQL faster than the DataFrame API?

Answer: No, they perform the same. Spark SQL and the DataFrame API both create a logical plan that goes through the same Catalyst optimizer and the same execution engine. Choose based on readability and team skills, not speed.

How it works:

• spark.sql("...") and the equivalent DataFrame code produce the same physical plan, which you can confirm with explain().
• Performance differences come from the logic, such as a UDF, an extra shuffle or a missing filter, not from the API.
• Python UDFs are slow in both styles.
• Many teams use SQL for business transformations and the DataFrame API for dynamic, reusable code.

Example:

sql_df = spark.sql("SELECT city, SUM(amount_inr) AS total FROM orders_v GROUP BY city")
api_df = orders.groupBy("city").agg(F.sum("amount_inr").alias("total"))

sql_df.explain() # same physical plan
api_df.explain() # as this one

Interview tip: Prove the point with explain() in the interview; showing identical plans is more convincing than just saying it.

Q41. How do you remove duplicates and keep the latest record using a window?

Answer: Partition by the business key, order by the update timestamp in descending order, assign row_number(), and keep only row number 1. This keeps exactly one latest record per key, which dropDuplicates cannot guarantee.

How it works:

• The business key decides what counts as a duplicate, such as order_id.
• Add tie-breakers, such as file name or ingestion time, in case two rows have the same timestamp.
• Run this before a MERGE into a Delta table, because MERGE fails when several source rows match the same target row.

Example:

w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc(),
                                           F.col("ingest_ts").desc())

latest_orders = (raw_orders
    .withColumn("rn", F.row_number().over(w))
    .filter(F.col("rn") == 1)
    .drop("rn"))

Interview tip: Link this to MERGE; saying "I deduplicate the source before MERGE to avoid the multiple-match error" shows production experience.

Q42. What is a null-safe join and when do you use eqNullSafe?

Answer: A null-safe join treats two nulls as equal when matching keys. In a normal join, null == null is not true, so rows with null keys never match. eqNullSafe() in PySpark, or the <=> operator in SQL, makes null match null.

How it works:

• Normal equality returns null when either side is null, and the row is dropped from an inner join.
• a.eqNullSafe(b) returns true when both are null, and false when only one is null.
• Common uses: composite keys with optional parts, and comparing two versions of a table to find changes.

Example:

joined = src.join(tgt,
    src.customer_id.eqNullSafe(tgt.customer_id) & src.branch_code.eqNullSafe(tgt.branch_code),
    "full")

changed = src.join(tgt, "id").filter(~src.email.eqNullSafe(tgt.email))

The SQL version uses the null-safe equality operator:

SELECT s.id FROM src s JOIN tgt t ON s.id = t.id WHERE NOT (s.email <=> t.email);

Interview tip: Mention change detection: with plain !=, a change from null to a value is missed, which is a common SCD2 bug.

5. Spark architecture and internals

Q43. What are the driver, executors and cluster manager in Spark?

Answer: The driver is the process that runs your main program, builds the execution plan and schedules tasks. Executors are processes on worker nodes that run the tasks and hold cached data. The cluster manager, such as YARN, Kubernetes or Spark Standalone, gives the application its machines and resources.

How it works:

• The driver creates the SparkSession, turns code into jobs, stages and tasks, and tracks progress.
• Each executor has a number of cores (task slots) and memory, and runs many tasks during its life.
• Executors send results and status back to the driver.
• On Databricks, the platform manages the cluster for you; a cluster has one driver node and zero or more worker nodes.

Example: A job runs on a cluster with one driver and 4 workers, each with one executor of 8 cores. The cluster can run 32 tasks in parallel. If the input has 320 partitions, the stage finishes in about 10 waves of tasks.

Interview tip: Say that the driver is a single point of failure for the application; if the driver dies, the whole job fails, while a failed executor's tasks are retried elsewhere.

Q44. What is the difference between a job, a stage and a task?

Answer: A job is created for each action, such as count() or write. A job is split into stages at shuffle boundaries. Each stage is made of tasks, one task for each partition, and tasks are the units that actually run on executor cores.

How it works:

• Job: all work needed to produce the result of one action. Some actions create more than one job.
• Stage: a set of narrow transformations that can run together without a shuffle.
• Task: one stage's work on one partition, run on one core.
• Stages that depend on a shuffle must wait until the previous stage writes its shuffle output.

Example:

result = (spark.read.parquet("/bronze/orders/") # stage 1: scan + filter
            .filter("status = 'DELIVERED'")
            .groupBy("city") # shuffle -> stage 2
            .agg(F.sum("amount_inr").alias("total")))
result.write.mode("overwrite").parquet("/gold/city_totals/") # action -> job

Interview tip: Interviewers often ask "how many stages will this code create?"; count the wide transformations, and remember that AQE may change the final plan at runtime.

Q45. What is a DAG in Spark, and what do the DAG scheduler and task scheduler do?

Answer: A DAG (directed acyclic graph) is the graph of operations Spark must run to compute a result, with no cycles. The DAG scheduler splits this graph into stages at shuffle boundaries and submits them in the right order. The task scheduler then launches the tasks of each stage on executors through the cluster manager.

How it works:

• Each DataFrame keeps its lineage, the list of steps used to build it.
• The DAG scheduler works out stage dependencies and also prefers to run tasks where the data is located.
• The task scheduler assigns tasks to free executor cores and retries failed tasks.
• If an executor is lost, Spark uses the lineage to recompute only the missing partitions.

Example: For "read orders, join with customers, group by state", the DAG has two scan stages, a shuffle for the join, and another shuffle for the group by. The Spark UI shows this as a visual DAG on the job page.

Interview tip: Mention lineage-based fault tolerance; it explains why Spark does not need to copy every intermediate result to recover from failures.

Q46. What is a shuffle in Spark, and why is it expensive?

Answer: A shuffle is the redistribution of data across executors so that all rows with the same key end up in the same partition. It is needed for operations such as groupBy, join, distinct and repartition. It is expensive because it involves disk writes, network transfer, serialization and sorting.

How it works:

• Map-side tasks write shuffle files to local disk, grouped by target partition.
• Reduce-side tasks fetch their blocks from all map outputs over the network.
• The number of reduce partitions comes from spark.sql.shuffle.partitions (default 200), which AQE can coalesce.
• Too-large partitions spill to disk; skewed keys make one task much slower than others.

Example:

# Two shuffles: one for the join, one for the groupBy
report = (orders.join(customers, "customer_id")
                .groupBy("state").agg(F.sum("amount_inr").alias("total")))

# One shuffle less: broadcast the small customers table
report2 = (orders.join(F.broadcast(customers), "customer_id")
                 .groupBy("state").agg(F.sum("amount_inr").alias("total")))

Interview tip: Say you look at "Shuffle Read" and "Shuffle Write" in the Spark UI stage page to find which step moves the most data.

Q47. What is the Catalyst optimizer, and what are its phases?

Answer: Catalyst is Spark SQL's query optimizer that turns DataFrame and SQL code into an efficient execution plan. It works in four phases: analysis, logical optimization, physical planning and code generation. Because of Catalyst, well-written DataFrame code and SQL get the same optimizations automatically.

How it works:

• Analysis: resolves table and column names and types using the catalog, producing a resolved logical plan.
• Logical optimization: applies rules such as predicate pushdown, column pruning, constant folding and combining filters.
• Physical planning: creates one or more physical plans and chooses join strategies, such as broadcast hash join or sort-merge join.
• Code generation: produces compact Java bytecode for the chosen plan (whole-stage code generation, part of Tungsten).

Example:

df = (spark.read.parquet("/silver/orders/")
        .select("order_id", "city", "amount_inr", "order_date")
        .filter("order_date = '2026-10-20'"))
df.explain(mode="extended") # parsed, analyzed, optimized and physical plans

The optimized plan shows the date filter pushed into the Parquet scan and only four columns read.

Interview tip: Mention that Catalyst cannot optimize inside Python UDFs, which links the theory to a practical coding rule.

Q48. What is Project Tungsten in Spark?

Answer: Tungsten is the Spark project that improves CPU and memory efficiency of execution. It stores rows in a compact binary format, manages memory explicitly (including off-heap), uses cache-friendly algorithms and generates optimized code for whole stages. It works behind the scenes for DataFrames and SQL.

How it works:

• Binary row format: data is kept in a compact form instead of many Java objects, which reduces garbage collection.
• Explicit memory management: Spark tracks execution and storage memory itself and can use off-heap memory.
• Whole-stage code generation: several operators are fused into one generated function, avoiding virtual calls per row.
• In explain() output, operators marked with an asterisk and a number, like *(1), are part of a code-generated stage.

Example: A filter, project and partial aggregation over a Parquet scan appear in the physical plan as one code-generated stage, so each row passes through all three steps in a single tight loop.

Interview tip: Keep the answer short and practical: Tungsten is why DataFrames beat RDDs in speed and memory use, and Databricks Photon goes further with a native vectorized engine.

Q49. What is Adaptive Query Execution (AQE)?

Answer: AQE re-optimizes a query while it runs, using real statistics collected at shuffle boundaries. It is enabled by default since Apache Spark 3.2. Its three main features are coalescing small shuffle partitions, switching join strategies (for example sort-merge to broadcast) and splitting skewed partitions in joins.

How it works:

• Coalesce partitions: merges small post-shuffle partitions toward spark.sql.adaptive.advisoryPartitionSizeInBytes (default 64 MB).
• Switch join strategy: converts a sort-merge join to a broadcast hash join when one side is smaller than the broadcast threshold at runtime.
• Skew join: a partition is skewed if it is larger than 5 times the median and larger than 256 MB (default settings); AQE splits it into smaller tasks.
• Key setting: spark.sql.adaptive.enabled (true by default).

Example:

spark.conf.get("spark.sql.adaptive.enabled") # 'true'
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true") # default is already true
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "128m")

In the Spark UI, an AQE plan shows AdaptiveSparkPlan isFinalPlan=true and nodes like AQEShuffleRead.

Interview tip: Say AQE reduces the need to hand-tune spark.sql.shuffle.partitions, but it does not fix every skew, such as skew in a groupBy, where salting may still help.

Q50. How do you read Spark execution plans with explain()?

Answer: df.explain() prints the physical plan Spark will run, and explain(mode=...) shows more detail. You read it to check join strategies, filter pushdown, column pruning and shuffles (shown as Exchange). It is the fastest way to understand why a query is slow before running it.

How it works:

• Modes: simple (physical plan), extended (parsed, analyzed, optimized and physical), codegen, cost and formatted.
• Read the plan from bottom (scan) to top (output).
• Exchange means a shuffle; BroadcastExchange means a table is broadcast.
• PushedFilters and PartitionFilters in the scan show what was pushed to the file source.
• Join nodes show BroadcastHashJoin, SortMergeJoin or ShuffledHashJoin.

Example:

q = (orders.filter("order_date = '2026-10-20'")
           .join(F.broadcast(stores), "store_id")
           .groupBy("region").agg(F.sum("amount_inr")))
q.explain(mode="formatted")

In the output, look for PartitionFilters on order_date, a BroadcastHashJoin, and one Exchange for the group by.

Interview tip: Mention that with AQE the final plan is decided at runtime, so you also check the SQL / DataFrame tab in the Spark UI for the actual plan.

Q51. How does Spark's memory model work (execution vs storage memory)?

Answer: Spark splits executor heap memory into a unified region shared by execution memory (shuffles, joins, sorts, aggregations) and storage memory (cached data and broadcast variables), plus user memory and 300 MB of reserved memory. Execution and storage can borrow from each other, but execution can evict cached blocks only down to a protected limit.

How it works:

• Unified region M = spark.memory.fraction (default 0.6) of (heap minus 300 MB).
• Protected storage R = spark.memory.storageFraction (default 0.5) of M; cached blocks inside R are never evicted by execution.
• Storage cannot evict execution memory.
• The remaining 40% is user memory for your data structures and Spark's internal metadata.
• Off-heap overhead memory (spark.executor.memoryOverhead) is separate and covers things like Python workers and native buffers.

Example: With an 8 GB executor heap: (8,192 - 300) x 0.6 is about 4.7 GB unified memory, and about half of that is protected for cache. Heavy caching plus a large join can still cause spills to disk when execution needs more.

Interview tip: Mention that the docs recommend keeping these defaults in most cases; tune data and partitions first, not memory fractions.

Q52. Why do Spark executors or the driver fail with OutOfMemory errors?

Answer: Executors usually run out of memory because one task gets too much data, from skewed keys, too few or too large partitions, huge broadcast tables or exploding rows. The driver usually runs out of memory because of collect(), toPandas(), large broadcasts or very large query plans. The fix depends on which one failed.

How it works:

• Executor OOM causes: data skew, large partitions after a shuffle, broadcasting a big table, heavy Python UDF memory, caching too much.
• Container killed for exceeding memory: often a lack of overhead memory, common with PySpark and pandas UDFs.
• Driver OOM causes: collecting big results, millions of small files to list, or very large broadcasts.
• Common fixes: more or better-sized partitions, salting or AQE for skew, removing large broadcasts, bigger nodes only as a last step.

Example: A daily job failed with executor OOM at the join stage. The Spark UI showed one task reading 18 GB while others read 200 MB. One customer ID was a default dummy value. Filtering and handling that key separately fixed the failure without adding memory.

Interview tip: Always first say "I check whether it is the driver or an executor, and which stage", before suggesting more memory.

Q53. How does spark-submit work, and what is the difference between client and cluster deploy mode?

Answer: spark-submit is the command-line tool that launches a Spark application on a cluster with settings for the master, deploy mode, resources and code files. In client mode, the driver runs on the machine where you ran the command. In cluster mode, the driver runs inside the cluster, which is the usual choice for production jobs.

How it works:

• --master picks the cluster manager: yarn, k8s://..., spark://host:7077 or local[*].
• --deploy-mode client is good for interactive work and debugging; cluster keeps the job alive if your laptop or edge node disconnects.
• Resource flags include --executor-memory, --executor-cores, --num-executors and --driver-memory.
• --py-files ships extra Python modules, and --conf sets any Spark property.

Example:

spark-submit \
  --master yarn --deploy-mode cluster \
  --driver-memory 4g --executor-memory 8g --executor-cores 4 --num-executors 10 \
  --conf spark.sql.shuffle.partitions=200 \
  --py-files utils.zip \
  daily_sales_etl.py --run_date 2026-10-20

Interview tip: Mention that on Databricks you do not use spark-submit day to day; you run notebooks, Python scripts or wheels as Lakeflow Jobs (earlier called Databricks Workflows) tasks.

Q54. How do you read the Spark UI to debug a slow job?

Answer: Start at the Jobs tab to find the slow job, open its slowest stage, and look at task metrics such as duration, shuffle read, spill and GC time. Then check the SQL / DataFrame tab for the query plan. Most slowness comes from skew, spills, too many small tasks or a missing broadcast.

How it works:

• Jobs and Stages: timeline, failed stages, and the summary metrics table (min, median, max) per stage.
• Skew sign: max task duration or shuffle read far higher than the median.
• Spill sign: "Spill (Memory)" and "Spill (Disk)" columns show data that did not fit in memory.
• Executors tab: GC time, failed tasks and memory use per executor.
• Storage tab: what is cached and how much; Environment: effective configs.

Example: A stage had 200 tasks with a median time of 4 seconds but a maximum of 25 minutes. The task with the longest time had 40 times more shuffle read. That pointed to key skew in the join, fixed by enabling AQE skew join and salting the hot key.

Interview tip: Talk in terms of "median vs max" in the task summary table; it shows you actually read the UI, not just the job status.

Q55. What is Spark Connect?

Answer: Spark Connect is a client-server architecture, introduced in Apache Spark 3.4, that lets a thin client send DataFrame operations to a remote Spark server as unresolved logical plans. The client does not run the driver, so applications are lighter, easier to upgrade and better isolated. Newer versions of Databricks Connect are built on Spark Connect.

How it works:

• The client connects with an sc:// connection string, for example via SparkSession.builder.remote().
• The server receives the plan, optimizes and runs it, and streams results back using Apache Arrow.
• Most DataFrame and SQL APIs work, but RDD APIs and SparkContext are not supported.
• Client crashes or memory problems do not take down the shared Spark driver.

Example:

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.remote("sc://localhost:15002").getOrCreate()
df = spark.range(10).withColumn("double_id", F.col("id") * 2)
df.show()

Interview tip: Link it to real life: if your code uses sc.parallelize or df.rdd, it will not run on Spark Connect-based compute, so stick to DataFrame APIs.

6. Performance tuning and optimization

Q56. How do you choose the right number of partitions and shuffle partitions?

Answer: Aim for partitions of roughly 100–200 MB each and enough tasks to keep every executor core busy, usually 2–4 tasks per core. For reads, Spark splits files using spark.sql.files.maxPartitionBytes (default 128 MB). For joins and aggregations, Spark uses spark.sql.shuffle.partitions, which defaults to 200 in Apache Spark.

How it works:

• Too few partitions means big tasks, spill to disk and idle cores.
• Too many partitions means thousands of tiny tasks and scheduling overhead.
• With Adaptive Query Execution (AQE), on by default since Spark 3.2, Spark merges small shuffle partitions at runtime toward the advisory size (64 MB by default).
• On Databricks you can set spark.sql.shuffle.partitions to auto so the platform picks the count for you.

Example: A job shuffles 100 GB of UPI payment data. 100 GB divided by about 200 MB per partition gives roughly 500 shuffle partitions.

spark.conf.set("spark.sql.shuffle.partitions", 500)
daily = payments.groupBy("merchant_id", "txn_date").agg(F.sum("amount_inr").alias("total_inr"))
daily.write.mode("overwrite").saveAsTable("main.payments.daily_merchant_totals")
# Spark UI: the shuffle stage shows up to 500 tasks, or fewer if AQE coalesces them

Interview tip: Never say "200 is always fine"; explain that you size partitions from data volume and cores, then check task durations in the Spark UI.

Q57. What is the difference between repartition() and coalesce()?

Answer: repartition(n) does a full shuffle and can increase or decrease partitions, giving evenly sized partitions. coalesce(n) only reduces partitions by merging existing ones without a full shuffle, so it is cheaper but can leave partitions uneven.

How it works:

Example: Reduce output files after a filter, and spread data by state before a wide aggregation.

small_out = orders.filter("order_date = '2026-10-01'").coalesce(8)
small_out.write.mode("overwrite").parquet("/Volumes/sales/raw/out/orders_daily")

by_state = orders.repartition(64, "state") # hash partitioned by state

Interview tip: Mention that coalesce(1) pushes all upstream work into one task, so for a single output file on large data, repartition(1) can actually be faster because earlier stages stay parallel.

Q58. What is data skew, and how does salting fix it?

Answer: Data skew happens when a few keys hold most of the rows, so one or two tasks run far longer than the rest. Salting adds a random number to the hot key so its rows spread over many partitions, and you replicate the smaller side to match.

How it works:

• In the Spark UI, skew looks like one task taking minutes while the median takes seconds, or one task with much larger shuffle read.
• First try AQE skew join handling (spark.sql.adaptive.skewJoin.enabled, on by default with AQE). It splits a partition that is larger than 5 times the median and above 256 MB by default.
• If AQE is not enough, or the skew is in an aggregation, use salting or a broadcast join.

Example: One large merchant has most of the payments. We salt the big side with 8 values and explode the small side 8 times.

SALT = 8
big_s = payments.withColumn("salt", (F.rand() * SALT).cast("int"))
salts = spark.range(SALT).select(F.col("id").cast("int").alias("salt"))
small_s = merchants.crossJoin(salts)
joined = big_s.join(small_s, ["merchant_id", "salt"]).drop("salt")

Interview tip: Say you check AQE and broadcast options before salting, because salting adds code complexity and multiplies the small table.

Q59. What is the broadcast join threshold, and when do you change it?

Answer: spark.sql.autoBroadcastJoinThreshold decides the largest table size Spark will broadcast automatically in a join; the default is 10 MB. Below that size, Spark sends a full copy of the small table to every executor and avoids shuffling the big table. Setting it to -1 disables automatic broadcast.

How it works:

• Spark uses table statistics or the estimated plan size to decide.
• AQE can also switch a sort-merge join to a broadcast hash join at runtime if the real size turns out small.
• You can force broadcast with F.broadcast(df) or the SQL BROADCAST(t) join hint, even above the threshold.
• Broadcasting a table that is too big can cause driver or executor out-of-memory errors.

Example: A 60 MB product dimension joins a 2 TB sales fact. Raise the threshold or use a hint.

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", 100 * 1024 * 1024) # 100 MB
result = sales.join(F.broadcast(products), "product_id", "left")
result.explain() # look for BroadcastHashJoin

Interview tip: Interviewers want you to confirm the join type with explain() instead of assuming the hint worked.

Q60. What is the difference between cache() and persist(), and what storage levels exist?

Answer: cache() stores a DataFrame using the default storage level, while persist() lets you choose the level. For DataFrames the default is memory and disk (shown as MEMORY_AND_DISK_DESER in recent PySpark); for RDDs the default cache() level is MEMORY_ONLY. Both are lazy and fill only when an action runs.

How it works:

Example: A cleaned customer DataFrame is reused by three reports, so we persist it once.

from pyspark import StorageLevel
clean = customers.filter("is_active = true").dropDuplicates(["customer_id"])
clean.persist(StorageLevel.MEMORY_AND_DISK)
clean.count() # action materialises the cache
print(clean.storageLevel)
clean.unpersist() # free memory when done

Interview tip: Say you cache only data that is reused and expensive to recompute, and always unpersist(); also note that serverless compute on Databricks does not support cache() or persist().

Q61. What are predicate pushdown and partition pruning?

Answer: Predicate pushdown sends your filter down to the data source, so Parquet or Delta skips row groups and files that cannot match. Partition pruning skips whole folders when you filter on a partition column. Both reduce the data Spark reads from storage.

How it works:

• Parquet and Delta keep min/max statistics, so a filter like amount_inr > 50000 can skip blocks.
• If a table is partitioned by order_date, a filter on order_date reads only matching folders.
• Delta also does data skipping using file-level statistics in the transaction log.
• Pushdown breaks if you wrap the column in a UDF or a function that hides it, such as casting the partition column.

Example: Check the plan for pushed filters and partition filters.

orders = spark.read.parquet("/Volumes/sales/raw/files/orders") # partitioned by order_date
q = orders.filter((F.col("order_date") == "2026-10-01") & (F.col("amount_inr") > 50000))
q.explain()
# Look for PartitionFilters: [order_date = 2026-10-01] and PushedFilters: [GreaterThan(amount_inr,50000)]

Interview tip: Mention that you verify pruning in explain() or the Spark UI "files read" metric, not by assumption.

Q62. What is the small files problem, and how do you fix it?

Answer: The small files problem happens when a table is made of thousands of tiny files, often a few KB each. Every file adds open, listing and metadata cost, so reads become slow even when total data is small.

How it works:

• Common causes: streaming micro-batches, too many shuffle partitions on write, and over-partitioning by a high-cardinality column.
• Fixes on write: coalesce() or repartition() before writing, and avoid partitioning by columns like customer_id.
• Fixes on Delta: run OPTIMIZE to compact files; on Databricks, enable optimized writes and auto compaction, or let predictive optimization run OPTIMIZE for Unity Catalog managed tables.
• Spark also packs small files into one read partition using spark.sql.files.openCostInBytes (4 MB by default).

Example: A streaming table of UPI events has 400,000 tiny files. We compact it.

OPTIMIZE main.payments.upi_events;
DESCRIBE DETAIL main.payments.upi_events; -- check numFiles before and after

Interview tip: Interviewers like it when you fix the cause (write pattern, partition column) and not only run OPTIMIZE as a band-aid.

Q63. Which file formats and compression codecs do you prefer for Spark, and why?

Answer: For analytics, use columnar formats such as Parquet, or Delta Lake, which stores data as Parquet plus a transaction log. Columnar formats read only the columns a query needs and keep statistics for skipping. Snappy is the common default codec because it is fast and splittable inside Parquet.

How it works:

• Codecs: Snappy (fast, moderate size), ZSTD (smaller files, slightly more CPU), Gzip (small, but a plain .csv.gz file cannot be split across tasks).

Example: Land raw CSV from a partner in Bronze, then store it as Delta in Silver so dashboards read only needed columns.

raw = spark.read.option("header", True).csv("/Volumes/sales/raw/files/partner_orders/")
raw.write.format("delta").mode("append").saveAsTable("main.sales.silver_orders")

Interview tip: Mention that one big gzip CSV is read by a single task, which is a common reason a "simple" load is slow.

Q64. Why should you avoid Python UDFs, and what are the alternatives?

Answer: Plain Python UDFs are slow because each row is serialised from the JVM to a Python worker and back, and the Catalyst optimizer cannot see inside them. Built-in functions in pyspark.sql.functions run inside the JVM, can be optimised and also run on Photon on Databricks.

How it works:

• Order of preference: built-in functions, then SQL expressions with F.expr, then pandas UDFs (vectorised with Apache Arrow), and plain Python UDFs only as a last resort.
• UDFs block predicate pushdown when used in filters.
• Photon does not run UDFs, so that part of the plan falls back to normal Spark.

Example: Masking a mobile number with built-ins instead of a UDF.

# Slow: udf(lambda m: "XXXXXX" + m[-4:])
masked = customers.withColumn(
    "mobile_masked",
    F.concat(F.lit("XXXXXX"), F.substring("mobile", -4, 4))
)

Interview tip: Say "I first search the built-in functions list"; many candidates write UDFs for things like regex, dates and JSON parsing that already exist as built-ins.

Q65. What is Photon, and when does it help?

Answer: Photon is the Databricks native vectorised query engine, written in C++, that runs SQL and DataFrame operations faster without code changes. The Catalyst optimizer still plans the query; Photon replaces the execution layer for supported operators.

How it works:

• Photon is the default engine on SQL warehouses and is on for serverless compute; on classic compute it is enabled by default and controlled by the "Use Photon Acceleration" checkbox.
• It speeds up scans, joins, aggregations and writes to Delta and Parquet, so ETL and BI queries gain the most.
• UDFs, RDD APIs and Dataset APIs are not supported, and those parts fall back to standard Spark.
• Very short queries (a couple of seconds) see little benefit.

Example: A nightly job joins 1 billion sales rows with dimensions and writes a Gold Delta table. With Photon, the heavy joins and aggregations run in native code. You confirm it in the Spark UI, where Photon operators are shown in a different colour, or in the query profile on SQL warehouses.

Interview tip: Mention that Photon costs more DBUs per hour, so you prove the speed gain lowers total cost before turning it on for every job.

Q66. How do you choose cluster size and use autoscaling on Databricks?

Answer: Start from the data volume and workload type, pick a node type that matches (memory-optimised for big joins and caching, compute-optimised for CPU-heavy transforms), then tune using the Spark UI. Autoscaling lets Databricks add or remove workers between a minimum and maximum you set.

How it works:

• On Databricks, each worker node runs one executor.
• Enable autoscaling for variable workloads; use a fixed size when the job is steady and predictable.
• Set auto-termination on all-purpose compute so idle clusters stop.
• Classic autoscaling has limits scaling down Structured Streaming jobs; Databricks recommends Lakeflow pipelines with enhanced autoscaling for streaming.
• Do not enable Spark dynamic allocation together with Databricks autoscaling.
• For many jobs, serverless compute removes sizing decisions completely.

Example: A daily 300 GB ETL runs on job compute with autoscaling from 2 to 8 workers. The Spark UI shows heavy spill, so we move to memory-optimised nodes instead of adding more workers.

Interview tip: Interviewers listen for "measure first": look at spill, GC time and idle cores before adding nodes.

Q67. What is the difference between checkpoint and cache in Spark?

Answer: cache() keeps data for reuse but keeps the full lineage, so Spark can recompute lost partitions. checkpoint() writes data to reliable storage and cuts the lineage, so the plan becomes short. Use cache for reuse and checkpoint for very long or iterative plans.

How it works:

• localCheckpoint() truncates lineage but stores data on executors, so it is faster and less reliable.
• Structured Streaming checkpoints are a different feature: they store offsets and state for recovery.

Example: An iterative loop that adds columns 50 times creates a huge plan.

spark.sparkContext.setCheckpointDir("/Volumes/ops/tmp/chk")
df = base
for i in range(50):
    df = df.withColumn(f"f{i}", F.col("amount_inr") * i)
    if i % 10 == 9:
        df = df.checkpoint() # eager by default, cuts lineage

Interview tip: Do not confuse RDD/DataFrame checkpointing with streaming checkpointLocation; interviewers check this often, and sparkContext is not available on serverless compute.

Q68. What is your step-by-step checklist for tuning a slow Spark job?

Answer: First find where time is spent in the Spark UI, then fix the biggest problem: reading too much data, shuffling too much, skew, spill or bad joins. Change one thing at a time and measure again.

How it works:

1. Read less: filter early, select only needed columns, check partition pruning and data skipping.
2. Fix files: compact small files with OPTIMIZE; use Delta or Parquet instead of CSV.
3. Join smart: broadcast small tables; check the join type in explain().
4. Shuffle right: keep AQE on, size shuffle partitions, remove unneeded distinct() or orderBy().
5. Handle skew: AQE skew join, broadcast or salting.
6. Avoid UDFs: replace with built-ins so Photon and Catalyst can help.
7. Memory: watch spill and GC; cache only reused data.
8. Compute: right node type, autoscaling, Photon, or serverless.

Example: A 2-hour job had one stage at 95% of the time. The Spark UI showed one task reading 40 GB while others read 200 MB. Enabling a broadcast join for the 50 MB dimension removed the shuffle and the job finished in 20 minutes.

Interview tip: Tell a story with a measured before and after; numbers from your own project impress more than a list of configs.

7. PySpark coding interview questions

Q69. How do you find the second highest salary in each department?

Answer: Use a window partitioned by department and ordered by salary descending, apply dense_rank(), and keep rows where the rank is 2. dense_rank() handles ties correctly, so two people with the same top salary do not push the real second salary out.

How it works:

• Window.partitionBy("dept") restarts the ranking for each department.
• dense_rank() gives 1, 1, 2 for salaries 90k, 90k, 80k; row_number() would give 1, 2, 3.
• Departments with only one distinct salary return no row.

Example: Employee data with a tie in IT.

from pyspark.sql import functions as F, Window
emp = spark.createDataFrame(
    [("Ravi", "IT", 90000), ("Anu", "IT", 90000), ("Kiran", "IT", 80000),
     ("Meena", "HR", 60000), ("Suresh", "HR", 55000)],
    ["name", "dept", "salary"])
w = Window.partitionBy("dept").orderBy(F.col("salary").desc())
second = emp.withColumn("rk", F.dense_rank().over(w)).filter("rk = 2").drop("rk")
second.show()

Expected output: Kiran (IT, 80000) and Suresh (HR, 55000).

Interview tip: Always ask "how should ties be treated?" before choosing dense_rank(), rank() or row_number(); that question alone shows maturity.

Q70. How do you remove duplicates but keep only the latest record for each key?

Answer: Use row_number() over a window partitioned by the business key and ordered by the update timestamp descending, then keep row number 1. dropDuplicates() alone cannot guarantee which copy survives, so it is not safe for "keep latest".

How it works:

• Partition by the key, for example customer_id.
• Order by updated_at descending, and add a tie-breaker column if two rows can share the same timestamp.
• Filter rn = 1 and drop the helper column.

Example: A CDC feed sends several versions of the same customer.

from pyspark.sql import functions as F, Window
cdc = spark.createDataFrame(
    [(101, "Hyderabad", "2026-10-01 09:00:00"), (101, "Pune", "2026-10-03 11:30:00"),
     (102, "Chennai", "2026-10-02 08:15:00")],
    ["customer_id", "city", "updated_at"]).withColumn("updated_at", F.to_timestamp("updated_at"))
w = Window.partitionBy("customer_id").orderBy(F.col("updated_at").desc())
latest = cdc.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn")
latest.show()

Expected output: customer 101 with city Pune and customer 102 with city Chennai.

Interview tip: Mention that you would run this dedup step before a Delta MERGE, because MERGE fails when several source rows match the same target row.

Q71. How do you write a word count program in PySpark?

Answer: Split each line into words, explode the array into rows, normalise case, and group by word with a count. The DataFrame version is shorter and faster than the classic RDD version.

How it works:

• F.split(col, regex) turns a line into an array of words.
• F.explode() creates one row per word.
• F.lower() and a filter remove case differences and empty strings.

Example: Count words in a few customer feedback lines.

from pyspark.sql import functions as F
lines = spark.createDataFrame(
    [("UPI payment failed",), ("payment successful",), ("UPI refund pending",)], ["line"])
words = (lines
         .select(F.explode(F.split(F.lower("line"), r"\s+")).alias("word"))
         .filter(F.col("word") != ""))
counts = words.groupBy("word").count().orderBy(F.desc("count"), "word")
counts.show()

Expected output: payment = 2, upi = 2, and failed, pending, refund, successful = 1 each.

Interview tip: For a text file, start with spark.read.text(path), which gives one column named value; interviewers often ask you to adapt the code to a file.

Q72. How do you calculate a running total in PySpark?

Answer: Use F.sum() over a window that is partitioned by the group, ordered by date, and framed from the first row to the current row. This gives a cumulative total that resets for each group.

How it works:

• rowsBetween(Window.unboundedPreceding, Window.currentRow) sets the frame.
• Without an explicit frame, an ordered window uses a range frame, which adds all rows with the same date together; use rows when you want row-by-row totals.
• Partition by customer or store so the total restarts for each one.

Example: Running sales per store in INR.

from pyspark.sql import functions as F, Window
sales = spark.createDataFrame(
    [("S1", "2026-10-01", 5000), ("S1", "2026-10-02", 7000), ("S1", "2026-10-03", 3000),
     ("S2", "2026-10-01", 4000), ("S2", "2026-10-02", 6000)],
    ["store", "sale_date", "amount_inr"])
w = (Window.partitionBy("store").orderBy("sale_date")
     .rowsBetween(Window.unboundedPreceding, Window.currentRow))
sales.withColumn("running_inr", F.sum("amount_inr").over(w)).show()

Expected output: S1 running totals 5000, 12000, 15000; S2 running totals 4000, 10000.

Interview tip: Explain the difference between rows and range frames; that detail separates strong candidates from average ones.

Q73. How do you find users who logged in on 3 or more consecutive days?

Answer: Use the "gaps and islands" trick: number each user's distinct login dates with row_number(), subtract that number from the date, and consecutive dates get the same group value. Then group by user and that value, and keep groups with 3 or more days.

How it works:

• Remove duplicate logins on the same day first.
• For dates 1, 2, 3 October with row numbers 1, 2, 3, the date minus row number is the same day for all three.
• A break in dates changes the group value.

Example: App login data.

from pyspark.sql import functions as F, Window
logins = spark.createDataFrame(
    [("u1", "2026-10-01"), ("u1", "2026-10-02"), ("u1", "2026-10-03"), ("u1", "2026-10-05"),
     ("u2", "2026-10-01"), ("u2", "2026-10-03")], ["user_id", "login_date"]
).withColumn("login_date", F.to_date("login_date")).distinct()
w = Window.partitionBy("user_id").orderBy("login_date")
grp = logins.withColumn("rn", F.row_number().over(w)).withColumn("grp", F.expr("date_sub(login_date, rn)"))
streaks = (grp.groupBy("user_id", "grp")
           .agg(F.min("login_date").alias("start"), F.max("login_date").alias("end"),
                F.count("login_date").alias("days"))
           .filter("days >= 3"))
streaks.show()

Expected output: one row for u1, from 2026-10-01 to 2026-10-03 with days = 3; u2 has no streak.

Interview tip: Say out loud why distinct() comes first: duplicate logins on one day break the row number logic.

Q74. How do you flatten a nested JSON in PySpark?

Answer: Use dot notation to pull fields out of structs, explode() to turn arrays into rows, and select("item.*") style expansion to split a struct into columns. For deep or changing schemas, write a small loop that walks the schema and flattens structs automatically.

How it works:

• Struct fields: F.col("customer.address.city").
• Arrays: F.explode("items") drops rows with empty or null arrays; F.explode_outer("items") keeps them with nulls.
• A schema-driven loop checks each field's type (StructType or ArrayType) and flattens it.

Example: An order JSON with a customer struct and an items array.

from pyspark.sql import functions as F
data = [("""{"order_id": 1, "customer": {"name": "Asha", "city": "Hyderabad"},
             "items": [{"sku": "P1", "qty": 2}, {"sku": "P2", "qty": 1}]}""",)]
schema = "order_id INT, customer STRUCT<name: STRING, city: STRING>, items ARRAY<STRUCT<sku: STRING, qty: INT>>"
orders = spark.createDataFrame(data, ["raw"]).select(F.from_json("raw", schema).alias("o")).select("o.*")
flat = (orders
        .select("order_id", F.col("customer.name").alias("customer_name"),
                F.col("customer.city").alias("city"), F.explode_outer("items").alias("item"))
        .select("order_id", "customer_name", "city", "item.sku", "item.qty"))
flat.show()

Expected output: two rows for order 1 (Asha, Hyderabad), one with sku P1 and qty 2, one with sku P2 and qty 1.

Interview tip: In a real job, read files with spark.read.schema(schema).json(path) and an explicit schema, because schema inference scans the data and can guess types differently on each run.

Q75. How do you split a comma-separated column into multiple rows?

Answer: Use F.split() to turn the string into an array and F.explode() to create one row per value. Add F.trim() to remove spaces around each value.

How it works:

• F.split("skills", ",") returns an array column.
• F.explode() repeats the other columns for each array element.
• Use F.posexplode() if you also need the position of each value.
• Use explode_outer to keep rows where the column is null.

Example: Trainee skills stored as one text column.

from pyspark.sql import functions as F
df = spark.createDataFrame(
    [("Ravi", "PySpark, SQL, Azure"), ("Divya", "Python,Databricks")], ["name", "skills"])
rows = (df.withColumn("skill", F.explode(F.split("skills", ",")))
          .withColumn("skill", F.trim("skill"))
          .select("name", "skill"))
rows.show()

Expected output: five rows: Ravi with PySpark, SQL and Azure, and Divya with Python and Databricks.

Interview tip: Mention the reverse operation too: F.collect_list() with F.concat_ws(",", ...) turns rows back into one comma-separated string.

Q76. How do you pivot sales data by month?

Answer: Group by the row key, call pivot() on the month column and aggregate the amount. Passing the list of pivot values is faster because Spark does not need an extra job to find distinct values.

How it works:

• groupBy("product").pivot("month", months).sum("amount_inr") makes one column per month.
• Missing combinations become null; use na.fill(0) to show zero.
• For the reverse (columns to rows), use unpivot() (Spark 3.4+) or the SQL UNPIVOT clause.

Example: Monthly sales per product.

from pyspark.sql import functions as F
sales = spark.createDataFrame(
    [("Laptop", "Jan", 50000), ("Laptop", "Feb", 65000), ("Mobile", "Jan", 30000),
     ("Mobile", "Mar", 42000), ("Laptop", "Jan", 10000)], ["product", "month", "amount_inr"])
months = ["Jan", "Feb", "Mar"]
pivoted = sales.groupBy("product").pivot("month", months).sum("amount_inr").na.fill(0)
pivoted.show()

Expected output: Laptop = Jan 60000, Feb 65000, Mar 0; Mobile = Jan 30000, Feb 0, Mar 42000.

Interview tip: Say you pass the month list explicitly to fix the column order and avoid an extra scan; otherwise month names sort alphabetically.

Q77. How do you count null values in every column of a DataFrame?

Answer: Build one aggregate per column with a list comprehension: F.count(F.when(F.col(c).isNull(), 1)). count() ignores nulls, so it counts only the rows where the condition is true. Everything runs in a single pass over the data.

How it works:

• F.when(cond, 1) returns 1 for null rows and null otherwise.
• F.count() counts non-null results, which equals the number of nulls.
• For float columns, also check F.isnan() if NaN values matter.

Example: Customer data with missing values.

from pyspark.sql import functions as F
cust = spark.createDataFrame(
    [(1, "Asha", None), (2, None, "Pune"), (3, None, None)], "id INT, name STRING, city STRING")
null_counts = cust.select([F.count(F.when(F.col(c).isNull(), 1)).alias(c) for c in cust.columns])
null_counts.show()

Expected output: one row with id = 0, name = 2, city = 2.

Interview tip: Avoid a separate filter().count() per column; interviewers point out that it launches one job per column.

Q78. How do you implement SCD Type 2 with a Delta MERGE in PySpark?

Answer: Stage the source so each changed customer appears twice: once with its real key (to close the old row) and once with a null merge key (to insert the new version). One MERGE then expires old rows and inserts new versions atomically.

How it works:

• Target columns: customer_id, city, is_current, start_date, end_date.
• All source rows get merge_key = customer_id, which matches the current row and closes it if the city changed.
• Changed rows are added again with merge_key = null, so they never match and are inserted as new current rows.
• Brand-new customers do not match and are inserted.

Example: updates holds today's customer extract (one row per customer); Ravi's city changes from Hyderabad to Pune.

from pyspark.sql import functions as F
from delta.tables import DeltaTable
tgt = DeltaTable.forName(spark, "main.sales.dim_customer")
cur = tgt.toDF().filter("is_current = true")
changed = (updates.alias("s").join(cur.alias("t"), "customer_id")
           .filter("s.city <> t.city").select("s.*"))
null_key = F.lit(None).cast(updates.schema["customer_id"].dataType)
staged = (updates.withColumn("merge_key", F.col("customer_id"))
          .unionByName(changed.withColumn("merge_key", null_key)))
(tgt.alias("t").merge(staged.alias("s"), "t.customer_id = s.merge_key AND t.is_current = true")
    .whenMatchedUpdate(condition="t.city <> s.city",
                       set={"is_current": "false", "end_date": "current_date()"})
    .whenNotMatchedInsert(values={"customer_id": "s.customer_id", "city": "s.city",
        "is_current": "true", "start_date": "current_date()", "end_date": "null"})
    .execute())

Expected output: Ravi's Hyderabad row gets is_current = false and today's end date, and a new Pune row is inserted with is_current = true.

Interview tip: Mention deduplicating updates first and using null-safe comparison (NOT (t.city <=> s.city)) when tracked columns can be null.

Q79. How do you compare two DataFrames to find missing, new and changed rows?

Answer: Use left_anti joins on the key to find rows missing on either side, and an inner join with a column comparison to find changed rows. exceptAll() is a quick option when you only need "rows that differ" without knowing why.

How it works:

• Missing in new: old.join(new, "id", "left_anti").
• Added in new: new.join(old, "id", "left_anti").
• Changed: join on the key, then compare a hash of the non-key columns.
• F.sha2(F.concat_ws("||", ...), 256) compares many columns at once.

Example: Yesterday's and today's product price lists.

from pyspark.sql import functions as F
old = spark.createDataFrame([(1, "Pen", 10), (2, "Book", 50), (3, "Bag", 500)], ["id", "name", "price"])
new = spark.createDataFrame([(1, "Pen", 10), (2, "Book", 55), (4, "Lamp", 300)], ["id", "name", "price"])
def with_hash(df):
    return df.withColumn("h", F.sha2(F.concat_ws("||", "name", F.col("price").cast("string")), 256))
missing = old.join(new, "id", "left_anti")
added = new.join(old, "id", "left_anti")
changed = (with_hash(old).alias("o").join(with_hash(new).alias("n"), "id")
           .filter("o.h <> n.h").select("id", F.col("o.price").alias("old_price"), F.col("n.price").alias("new_price")))
missing.show(); added.show(); changed.show()

Expected output: missing = id 3 (Bag), added = id 4 (Lamp), changed = id 2 with price 50 to 55.

Interview tip: Note that concat_ws skips nulls, so wrap columns in F.coalesce(col, F.lit("~")) when a null must count as a change.

Q80. How do you find the top 3 products by revenue in each category?

Answer: Aggregate revenue per category and product, rank products within each category with a window ordered by revenue descending, and keep rank 3 or less. Choose row_number() for exactly 3 rows or dense_rank() to include ties.

How it works:

• First groupBy("category", "product") to get revenue.
• Then rank with Window.partitionBy("category").orderBy(F.desc("revenue")).
• Filter rk <= 3.

Example: Sales lines across two categories.

from pyspark.sql import functions as F, Window
sales = spark.createDataFrame(
    [("Mobiles", "A", 900), ("Mobiles", "B", 700), ("Mobiles", "C", 500), ("Mobiles", "D", 300),
     ("Laptops", "X", 1200), ("Laptops", "Y", 800), ("Mobiles", "A", 100)],
    ["category", "product", "amount_inr"])
rev = sales.groupBy("category", "product").agg(F.sum("amount_inr").alias("revenue"))
w = Window.partitionBy("category").orderBy(F.desc("revenue"))
top3 = rev.withColumn("rk", F.row_number().over(w)).filter("rk <= 3")
top3.orderBy("category", "rk").show()

Expected output: Laptops X (1200) and Y (800); Mobiles A (1000), B (700) and C (500).

Interview tip: Never use orderBy().limit(3) here; it returns the top 3 overall, not per category, which is the most common mistake.

Q81. How do you split user clicks into sessions using a 30-minute inactivity gap?

Answer: Use lag() to get the previous event time per user, mark a new session when the gap is more than 30 minutes (or there is no previous event), and take a running sum of that flag to number the sessions. This is called sessionization.

How it works:

• F.unix_timestamp("ts") gives seconds, so the gap is a simple subtraction.
• The first event of each user has a null previous time and always starts a session.
• A running sum() of the 0/1 flag over the same ordered window becomes the session number.

Example: Clickstream from a shopping app.

from pyspark.sql import functions as F, Window
clicks = spark.createDataFrame(
    [("u1", "2026-10-01 10:00:00"), ("u1", "2026-10-01 10:10:00"), ("u1", "2026-10-01 11:00:00"),
     ("u2", "2026-10-01 09:00:00")], ["user_id", "ts"]).withColumn("ts", F.to_timestamp("ts"))
w = Window.partitionBy("user_id").orderBy("ts")
gap = F.unix_timestamp("ts") - F.lag(F.unix_timestamp("ts")).over(w)
flagged = clicks.withColumn("new_session", F.when(gap.isNull() | (gap > 1800), 1).otherwise(0))
sessions = flagged.withColumn("session_no", F.sum("new_session").over(w))
sessions.groupBy("user_id", "session_no").agg(F.count("ts").alias("events")).show()

Expected output: u1 has session 1 with 2 events and session 2 with 1 event; u2 has session 1 with 1 event.

Interview tip: Mention that in Structured Streaming you would use the built-in session_window() function instead of this batch pattern.

Q82. How do you explode an array and then aggregate the results?

Answer: Explode the array so each element becomes a row, then use groupBy() and agg() on the exploded values. This is the standard way to answer questions like "total quantity sold per product" when orders store items as an array.

How it works:

• F.explode("items") gives one row per item and keeps the order columns.
• Struct fields of the exploded item are read with item.sku style references.
• countDistinct on the order id tells you how many orders contained each product.

Example: Orders with an items array.

from pyspark.sql import functions as F
orders = spark.createDataFrame(
    [(1, [("P1", 2), ("P2", 1)]), (2, [("P1", 3)]), (3, [("P3", 5), ("P1", 1)])],
    "order_id INT, items ARRAY<STRUCT<sku: STRING, qty: INT>>")
per_sku = (orders.select("order_id", F.explode("items").alias("item"))
           .groupBy(F.col("item.sku").alias("sku"))
           .agg(F.sum("item.qty").alias("total_qty"), F.countDistinct("order_id").alias("orders")))
per_sku.orderBy("sku").show()

Expected output: P1 = 6 units in 3 orders, P2 = 1 unit in 1 order, P3 = 5 units in 1 order.

Interview tip: Point out that explode multiplies rows, so filter and select only needed columns before exploding large arrays.

Q83. How do you read many files and add the source file name as a column?

Answer: Read the folder or a wildcard path once, then select the hidden _metadata column, for example _metadata.file_path. This works for file sources in recent Apache Spark versions and on Databricks. Prefer it over input_file_name(), which Databricks marks as deprecated and does not support on Unity Catalog.

How it works:

• _metadata is a hidden struct with file_path, file_name, file_size and file_modification_time.
• You must select it explicitly; it does not appear in select("*") alone.
• The file name helps with auditing, debugging bad files and reprocessing one file.

Example: Read all October order files from a Unity Catalog volume.

from pyspark.sql import functions as F
orders = (spark.read.option("header", True)
          .csv("/Volumes/sales/raw/files/orders/2026-10-*.csv")
          .select("*",
                  F.col("_metadata.file_path").alias("source_file"),
                  F.col("_metadata.file_modification_time").alias("file_time")))
orders.groupBy("source_file").count().show(truncate=False)

Expected output: one row per CSV file with its full path and the number of rows it contained.

Interview tip: Mention that Auto Loader pipelines usually store _metadata.file_path in the Bronze table so every row can be traced back to its source file.

Q84. How do you calculate each row's percentage of the total?

Answer: Divide each value by a total computed with a window aggregate, then multiply by 100. Use Window.partitionBy("group") for percent within a group, and a separate total joined back (or an empty window) for percent of the grand total.

How it works:

• F.sum("amount_inr").over(Window.partitionBy("region")) puts the region total on every row without a groupBy.
• An empty Window.partitionBy() moves all rows into one partition, so for big data compute the grand total with agg() and crossJoin() it back instead.
• Round the result for readable reports.

Example: Each city's share of its region's sales.

from pyspark.sql import functions as F, Window
sales = spark.createDataFrame(
    [("South", "Hyderabad", 600), ("South", "Chennai", 400), ("West", "Pune", 300), ("West", "Mumbai", 700)],
    ["region", "city", "amount_inr"])
w = Window.partitionBy("region")
by_region = sales.withColumn("pct_of_region", F.round(F.col("amount_inr") * 100 / F.sum("amount_inr").over(w), 2))
total = sales.agg(F.sum("amount_inr").alias("grand_total"))
result = by_region.crossJoin(total).withColumn("pct_of_all", F.round(F.col("amount_inr") * 100 / F.col("grand_total"), 2))
result.show()

Expected output: Hyderabad 60.0% of South and 30.0% of all; Mumbai 70.0% of West and 35.0% of all.

Interview tip: Say why you avoid the empty window on large data; interviewers like candidates who know where a single-partition bottleneck hides.

Q85. How do you find missing dates in a daily sales table?

Answer: Build the full list of dates between the minimum and maximum date with F.sequence() and F.explode(), then left anti join it with the dates that exist. What remains are the missing days.

How it works:

• F.sequence(start, end) on date columns steps by one day by default.
• explode() turns that array into one row per date.
• A left_anti join keeps calendar dates that have no sales row.
• To check per store, compute the bounds per store and join on store and date.

Example: Store sales with two missing days.

from pyspark.sql import functions as F
sales = spark.createDataFrame(
    [("2026-10-01", 5000), ("2026-10-02", 6000), ("2026-10-05", 4000)], ["sale_date", "amount_inr"]
).withColumn("sale_date", F.to_date("sale_date"))
bounds = sales.agg(F.min("sale_date").alias("start_d"), F.max("sale_date").alias("end_d"))
calendar = bounds.select(F.explode(F.sequence("start_d", "end_d")).alias("sale_date"))
missing = calendar.join(sales.select("sale_date").distinct(), "sale_date", "left_anti")
missing.orderBy("sale_date").show()

Expected output: 2026-10-03 and 2026-10-04.

Interview tip: Mention that in production you would join to a calendar (date dimension) table instead, which also marks holidays, so a Sunday with zero sales is not flagged as a data gap.

8. Databricks platform basics

Q86. What is Databricks, and what is a lakehouse?

Answer: Databricks is a cloud data and AI platform built by the original creators of Apache Spark. It runs on Azure, AWS and Google Cloud. A lakehouse combines the cheap, open storage of a data lake with warehouse features such as ACID transactions, governance and fast SQL, using Delta Lake tables on cloud storage.

How it works:

• Data stays in your cloud storage (for example ADLS Gen2) in open formats such as Delta and Parquet.
• Compute (Spark clusters, serverless compute, SQL warehouses) reads and writes that data.
• Unity Catalog governs tables, files, permissions and lineage in one place.
• The same data serves data engineering, BI, data science and AI.

Example: A retail company lands raw POS and UPI data in Bronze Delta tables, cleans it into Silver, and builds Gold sales tables. Analysts query Gold with Databricks SQL and Power BI, while data scientists train a demand-forecast model on the same Silver tables. There is no separate warehouse copy to keep in sync.

Interview tip: Explain the lakehouse as "one copy of data for BI and AI", and name the three pieces that make it work: Delta Lake, Unity Catalog and Spark/Photon compute.

Q87. What is the difference between Azure Databricks and open-source Apache Spark?

Answer: Apache Spark is the open-source processing engine; Azure Databricks is a managed first-party Azure service that runs an optimised Spark (Databricks Runtime) and adds a full platform around it. You get managed compute, Photon, Delta Lake features, Unity Catalog, notebooks, jobs and security integration without managing servers.

How it works:

Example: A team running Spark on a self-managed Hadoop cluster spends days patching and tuning. After moving to Azure Databricks, they use job compute that starts on demand, reads ADLS Gen2 through Unity Catalog, and stops after the run.

Interview tip: Mention that PySpark code is mostly portable, but platform features like dbutils, Unity Catalog and Photon exist only on Databricks.

Q88. What are the Databricks workspace and notebooks?

Answer: The workspace is the web environment where you organise notebooks, files, Git folders, jobs, pipelines, dashboards and other assets, and manage compute and permissions. Notebooks are interactive documents with cells that run Python, SQL, Scala or R against attached compute.

How it works:

• A notebook has a default language; magic commands such as %sql, %python, %md and %pip switch the language or task for one cell.
• %run ./other_notebook includes another notebook in the same session, so its functions and variables become available.
• display(df) shows rich tables and charts.
• Notebooks support comments, version history, real-time co-editing and scheduling as a job.
• In Unity Catalog workspaces, data lives in catalogs and volumes, not in the workspace folders.

Example: A data engineer writes bronze_orders in Python, adds a %sql cell to check row counts, uses %md cells for documentation, then schedules the notebook as a task in a Lakeflow Jobs job.

Interview tip: Interviewers often ask "%run vs dbutils.notebook.run"; say %run shares the same session and variables, while dbutils.notebook.run starts a separate run and returns only an exit string.

Q89. What is the difference between all-purpose compute, job compute, serverless compute and SQL warehouses?

Answer: All-purpose compute is for interactive notebook work and can be shared; job compute is created for a job run and ends when it finishes; serverless compute is managed by Databricks and starts in seconds; SQL warehouses run SQL and BI queries. Each is billed and tuned differently.

How it works:

Example: Developers build code on a shared all-purpose cluster, the nightly ETL runs on job compute or serverless jobs, and the Power BI dashboards connect to a serverless SQL warehouse.

Interview tip: Never say "run production jobs on all-purpose clusters"; interviewers treat that as a cost red flag.

Q90. What are cluster access modes in Databricks?

Answer: The access mode decides who can use the compute and how data access is isolated. The two modes for Unity Catalog are standard (earlier called shared), where many users share the compute with isolation between them, and dedicated (earlier called single user), assigned to one user or group.

How it works:

• The default "Auto" choice picks standard unless you select an ML runtime, a GPU instance type or an older runtime, in which case dedicated is used.
• Databricks recommends standard access mode unless you need a feature it does not support.

Example: A team of 8 analysts shares one standard cluster, and each sees only the tables their Unity Catalog grants allow. A data scientist training a GPU model uses dedicated compute.

Interview tip: Mention that legacy "No isolation shared" mode does not support Unity Catalog, so it should not be used for governed data.

Q91. How do autoscaling and auto-termination work in Databricks?

Answer: Autoscaling lets Databricks add or remove worker nodes between a minimum and maximum you set, based on the workload. Auto-termination stops a compute resource after it has been idle for the number of minutes you choose, so you do not pay for idle clusters.

How it works:

• Premium workspaces use optimised autoscaling, which can scale up from min to max quickly and scale down even when the cluster is not fully idle, based on shuffle file state.
• Autoscaling is not available for spark-submit tasks.
• Classic autoscaling has limits scaling down Structured Streaming jobs.
• Auto-termination measures time since the last command; a running job counts as activity.
• Compute policies let admins force limits such as maximum workers and auto-termination.

Example: A dev cluster autoscales from 1 to 4 workers and terminates after 30 minutes of inactivity. A morning ad-hoc analysis grows it to 4 workers; by lunch it shrinks back, and in the evening it shuts down on its own.

Interview tip: Mention compute policies: interviewers for senior roles want to hear how you enforce cost controls for the whole team, not just your own cluster.

Q92. How do you enable Photon and check whether a query used it?

Answer: Photon is on by default for SQL warehouses and serverless compute. On classic all-purpose or job compute, you control it with the "Use Photon Acceleration" option in the compute settings. You confirm Photon use in the Spark UI or the query profile.

How it works:

• In the Spark UI SQL/DataFrame tab, Photon operators appear in a different colour from normal Spark operators.
• In the query profile on SQL warehouses and serverless, you can see the share of task time spent in Photon.
• If part of a query uses a feature Photon does not support (such as a Python UDF), that part falls back to standard Spark.

Example: A Gold aggregation job shows 95% of task time in Photon. After a developer adds a Python UDF for GST calculation, the Photon share drops, and runtime increases. Replacing the UDF with F.when() and arithmetic built-ins brings Photon back.

Interview tip: Interviewers like when you connect Photon with "avoid UDFs", because unsupported code is the usual reason Photon does not help.

Q93. What is dbutils, and which utilities do you use most?

Answer: dbutils (Databricks Utilities) is a helper library available in Databricks notebooks for files, parameters, secrets and notebook workflows. The most used modules are fs, widgets, secrets, notebook and jobs.

How it works:

• dbutils.fs: list, copy, move and remove files (ls, cp, mv, rm).
• dbutils.widgets: create input parameters (text, dropdown) and read them with get; get also reads job task parameters.
• dbutils.secrets: read secrets with get(scope, key); values are shown as [REDACTED] in output.
• dbutils.notebook.run(path, timeout_seconds, arguments): run another notebook and get its exit value.
• dbutils.jobs.taskValues: pass small values between tasks in a job.

Example: A parameterised load notebook.

dbutils.widgets.text("run_date", "2026-10-20", "Run date")
run_date = dbutils.widgets.get("run_date")
key = dbutils.secrets.get(scope="kv-sales", key="partner-api-key")
display(dbutils.fs.ls("/Volumes/sales/raw/files/orders/"))
dbutils.notebook.exit(f"loaded {run_date}")

Interview tip: Say you never print or hard-code secrets, and that dbutils exists only on Databricks, so you keep core logic in plain PySpark functions for testing.

Q94. What are secret scopes, and how do you use an Azure Key Vault-backed scope?

Answer: A secret scope is a named collection of secrets that notebooks and jobs read with dbutils.secrets.get(). On Azure there are two types: Azure Key Vault-backed scopes, which read secrets stored in your Key Vault, and Databricks-backed scopes, stored in an encrypted database managed by Databricks.

How it works:

• A Key Vault-backed scope is read-only from Databricks; you add or change secrets in Azure Key Vault.
• You create it from the workspace URL ending in #secrets/createScope (capital S) or with the Databricks CLI, giving the vault DNS name and resource ID.
• The Key Vault must use the "Vault access policy" permission model; Azure RBAC is not supported for this scope type.
• Permissions are set per scope, so anyone with access to a Key Vault-backed scope can read all secrets in that vault.

Example: The SQL Server password lives in Key Vault kv-sales-prod, linked to scope kv-sales.

pwd = dbutils.secrets.get(scope="kv-sales", key="sqlserver-password")
df = (spark.read.format("jdbc")
      .option("url", "jdbc:sqlserver://sales-db.database.windows.net:1433;database=sales")
      .option("dbtable", "dbo.orders").option("user", "etl_user").option("password", pwd)
      .load())

Interview tip: Mention using separate Key Vaults (and scopes) per environment or team, because scope permissions cover every secret in the vault.

Q95. What are Git folders in Databricks?

Answer: Git folders (earlier called Repos) connect a workspace folder to a remote Git repository, so you can clone, branch, commit, pull and push notebooks and code from inside Databricks. They bring normal software practices such as pull requests and code review to notebook development.

How it works:

• Supported providers include GitHub, Azure DevOps, GitLab and Bitbucket.
• Each developer works in their own Git folder and feature branch.
• Changes go through pull requests and are merged in the Git provider.
• Notebooks can be stored as source files, which keeps diffs readable.
• Production jobs should run from a Git reference or a deployed bundle, not from someone's personal folder.

Example: Priya clones the sales-etl repo into her Git folder, creates branch feature/upi-refunds, edits the Silver notebook, commits and pushes. Her teammate reviews the pull request in Azure DevOps, and after merge the CI/CD pipeline deploys to test.

Interview tip: Say Git folders are for development and that deployment across environments is handled by CI/CD with Declarative Automation Bundles (earlier called Databricks Asset Bundles).

Q96. What are Lakeflow Jobs, and how do you set tasks, dependencies, retries and parameters?

Answer: Lakeflow Jobs (earlier called Databricks Workflows) is the built-in orchestrator for running notebooks, Python scripts, SQL, pipelines and other tasks on a schedule or trigger. A job is a graph of tasks with dependencies, retries, parameters and alerts.

How it works:

• Tasks: notebook, Python script, SQL, pipeline, JAR, dbt, "run job" and more; a job can hold up to 1,000 tasks.
• Dependencies: a task runs after the tasks it depends on; "Run if" conditions handle failure paths; If/else and For each tasks add control flow.
• Retries: set the retry count and interval per task, plus timeouts.
• Parameters: job parameters are passed to every task and read in notebooks with dbutils.widgets.get(); dynamic references such as {{job.start_time.iso_date}} fill values at run time.
• Triggers: scheduled, file arrival, table update, continuous or manual.

Example: Job daily_sales has tasks bronze, then silver, then gold and quality_check in parallel. Each task retries twice with a 5-minute gap. A file arrival trigger starts it when the partner drops files, and an email alert fires on failure.

Interview tip: Mention job compute or serverless for tasks and "repair run" to rerun only failed tasks; both show production experience.

Q97. What is Databricks SQL?

Answer: Databricks SQL is the part of Databricks for running SQL analytics and BI on lakehouse data. It includes SQL warehouses (the compute), a SQL editor, saved queries, dashboards and alerts, and it connects to tools like Power BI and Tableau.

How it works:

• SQL warehouses come in serverless, pro and classic types; serverless starts in seconds and scales quickly.
• Photon is the default engine on all SQL warehouse types.
• Queries read Unity Catalog tables, so the same permissions apply in BI tools.
• Alerts run a query on a schedule and notify when a condition is met.

Example: A finance team builds a daily revenue dashboard on main.sales.gold_daily_revenue.

SELECT order_date, region, SUM(amount_inr) AS revenue_inr
FROM main.sales.gold_daily_revenue
WHERE order_date >= current_date() - INTERVAL 30 DAYS
GROUP BY order_date, region
ORDER BY order_date;

An alert emails the team if yesterday's revenue is zero, which usually means a pipeline failed.

Interview tip: Explain when you would pick a SQL warehouse (BI and SQL users) versus job compute (Python ETL), since interviewers check you understand the cost model.

Q98. How do you do CI/CD for Databricks with Declarative Automation Bundles (earlier Databricks Asset Bundles)?

Answer: Declarative Automation Bundles (earlier called Databricks Asset Bundles) let you define jobs, pipelines and their code as YAML plus source files in Git, and deploy them to each environment with the Databricks CLI. A CI/CD pipeline runs databricks bundle validate, deploy and run against dev, test and prod targets.

How it works:

• databricks.yml defines the bundle name, resources (jobs, pipelines) and targets.
• Targets hold environment settings such as workspace host, and mode: development or mode: production.
• databricks bundle init creates a project from a template.
• In Azure DevOps or GitHub Actions, a service principal runs the CLI so deployments do not depend on a person.

Example: A minimal bundle and the commands to deploy it.

bundle:
  name: sales_etl
resources:
  jobs:
    daily_sales:
      name: daily_sales
      tasks:
        - task_key: bronze
          notebook_task:
            notebook_path: ./src/bronze_orders.py
targets:
  dev:
    mode: development
    default: true
  prod:
    mode: production
    workspace:
      host: https://adb-1234567890123456.7.azuredatabricks.net

databricks bundle validate -t prod
databricks bundle deploy -t prod
databricks bundle run -t prod daily_sales

Interview tip: Mention that the same code moves through dev, test and prod with only target settings changing, and that production deploys run as a service principal, not a user.

9. Delta Lake interview questions

Q99. What is Delta Lake, and what is the transaction log?

Answer: Delta Lake is an open-source storage layer that adds ACID transactions, schema control and version history on top of Parquet files. The transaction log is the _delta_log folder inside the table path. It records every change made to the table, in order, and it is the single source of truth for which files belong to the table.

How it works:

• Every write creates a new commit file in _delta_log, named with a version number (for example 00000000000000000005.json).
• Each commit lists actions such as "add file", "remove file", schema change or table property change.
• Delta writes a Parquet checkpoint file from time to time, so readers don't need to replay every JSON file.
• A reader builds the current table state from the latest checkpoint plus the newer commit files.

Example: A table at abfss://lake@stsales.dfs.core.windows.net/silver/orders/ holds many Parquet files and one _delta_log folder. If a write job crashes half way, its commit file never lands, so readers never see its half-written Parquet files.

Interview tip: Say clearly that the data files are plain Parquet and the "magic" is the log, because many candidates describe Delta as a new file format.

Q100. How does Delta Lake give ACID transactions on a data lake?

Answer: Delta Lake gets ACID guarantees by making every write an atomic commit to the transaction log. A change is visible only after its commit file is written, and concurrent writers are controlled with optimistic concurrency.

How it works:

• Atomicity: a commit file either exists or it doesn't. There is no partial write for readers.
• Consistency: schema enforcement and constraints (NOT NULL, CHECK) reject bad data at write time.
• Isolation: readers use a snapshot (one table version), so they never see a write in progress. The default isolation level is WriteSerializable.
• Durability: commits and data files live in cloud storage such as ADLS Gen2 or S3.

Example: A CHECK constraint stops negative order amounts at write time.

ALTER TABLE main.silver.orders ADD CONSTRAINT positive_amount CHECK (amount_inr > 0);
INSERT INTO main.silver.orders VALUES (1001, 'C01', -50.0); -- this insert fails

Interview tip: Interviewers like it when you explain that plain Parquet folders have no atomic commit, so a failed overwrite can leave the folder half empty, which Delta prevents.

Q101. What is time travel in Delta Lake, and how do you restore a table?

Answer: Time travel lets you query an older version of a Delta table by version number or timestamp. RESTORE rolls the table back to an older version by writing a new commit, so the history is kept.

How it works:

• Query with VERSION AS OF, TIMESTAMP AS OF or the @v syntax (for example orders@v12).
• History is kept for delta.logRetentionDuration (default 30 days). Old data files are kept for delta.deletedFileRetentionDuration (default 7 days), and VACUUM removes them after that.
• You can time travel only while both the log entries and the data files still exist.

Example: Someone ran a bad UPDATE at version 42. I check the old data and roll back.

SELECT COUNT(1) FROM main.silver.orders VERSION AS OF 41;
SELECT * FROM main.silver.orders TIMESTAMP AS OF '2026-10-19 08:00:00' LIMIT 10;
RESTORE TABLE main.silver.orders TO VERSION AS OF 41;

Interview tip: Mention that after VACUUM with the default 7-day retention, versions older than 7 days may fail to read, even though DESCRIBE HISTORY still lists them.

Q102. How does MERGE INTO work for upserts in Delta Lake?

Answer: MERGE INTO joins a source dataset to a target Delta table on a key and then updates, deletes or inserts rows in one atomic transaction. It is the standard way to do upserts, CDC apply and SCD loads on Databricks.

How it works:

• WHEN MATCHED clauses update or delete target rows. You can add extra conditions to each clause.
• WHEN NOT MATCHED inserts new rows. WHEN NOT MATCHED BY SOURCE can update or delete target rows that are missing from the source.
• If more than one source row matches the same target row, MERGE fails. So I always deduplicate the source first.

Example: Upsert today's UPI payment updates into the payments table.

MERGE INTO main.silver.upi_payments AS t
USING main.bronze.upi_payments_today AS s
ON t.txn_id = s.txn_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET t.status = s.status, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (txn_id, payer_vpa, amount_inr, status, updated_at)
  VALUES (s.txn_id, s.payer_vpa, s.amount_inr, s.status, s.updated_at);

Interview tip: Always say "I deduplicate the source on the merge key before MERGE", because the multiple-match error is the most common MERGE failure in real projects.

Q103. What do OPTIMIZE and Z-ORDER do?

Answer: OPTIMIZE compacts many small files into fewer large files so reads are faster. ZORDER BY goes further and co-locates related values of chosen columns in the same files, so data skipping can avoid more files.

How it works:

• Delta keeps min/max statistics per file. A filter like customer_id = 'C1001' skips files whose range can't contain that value.
• Z-ordering sorts data along several columns at once, which makes those min/max ranges tighter.
• Z-ORDER is a full rewrite of the files it touches, and it must be re-run as new data arrives.
• For new tables, Databricks now recommends liquid clustering instead of partitioning plus Z-ORDER.

Example: Compact last week's partitions and Z-order by customer.

OPTIMIZE main.silver.orders
WHERE order_date >= '2026-10-13'
ZORDER BY (customer_id);

Interview tip: Don't Z-order on the partition column. It adds nothing, because partition pruning already handles that column.

Q104. What is liquid clustering, and how is it different from partitioning and Z-ORDER?

Answer: Liquid clustering is a Delta Lake data layout feature that replaces both Hive-style partitioning and Z-ORDER. You declare clustering keys with CLUSTER BY, and Databricks clusters data incrementally, so you can change the keys later without rewriting the whole table.

How it works:

• You can define up to four clustering keys. Liquid clustering can't be combined with partitioning or Z-ORDER on the same table.
• OPTIMIZE clusters new data incrementally. OPTIMIZE ... FULL reclusters all existing data, which you run after changing keys.
• CLUSTER BY AUTO lets Databricks pick keys from your query patterns, using predictive optimization on Unity Catalog managed tables.
• Databricks recommends liquid clustering for all new tables.

Example: A 3 TB sales table filtered by date and store.

CREATE TABLE main.gold.sales (
  sale_id BIGINT, store_id STRING, sale_date DATE, amount_inr DECIMAL(12,2)
) CLUSTER BY (sale_date, store_id);

ALTER TABLE main.gold.sales CLUSTER BY (store_id, sale_date);
OPTIMIZE main.gold.sales FULL;

Interview tip: The key point is flexibility: changing a partition column means rewriting the table, while changing clustering keys is one ALTER TABLE.

Q105. What does VACUUM do, and what is the default retention?

Answer: VACUUM permanently deletes data files that the Delta table no longer references and that are older than the retention threshold. The default threshold is 7 days, set by delta.deletedFileRetentionDuration.

How it works:

• UPDATE, DELETE, MERGE and OPTIMIZE leave old files behind for time travel. VACUUM removes them to save storage.
• VACUUM never touches files still used by the current version.
• A safety check blocks retention shorter than 7 days unless you turn off spark.databricks.delta.retentionDurationCheck.enabled. Short retention can break long-running readers and streams.
• DRY RUN lists files without deleting. Newer runtimes also offer VACUUM ... LITE, which uses the log instead of listing the whole directory.
• On Unity Catalog managed tables, predictive optimization can run VACUUM for you.

Example: Check first, then clean, and keep 14 days of history for this table.

VACUUM main.silver.orders DRY RUN;
ALTER TABLE main.silver.orders SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = '14 days');
VACUUM main.silver.orders;

Interview tip: Say that VACUUM limits how far back time travel works, so retention is a business decision, not just a storage setting.

Q106. What is the difference between schema enforcement and schema evolution?

Answer: Schema enforcement means Delta rejects writes whose columns or types don't match the table schema. Schema evolution means you explicitly allow the table schema to change, usually to add new columns, with mergeSchema or automatic evolution settings.

How it works:

• By default, appending a DataFrame with an extra column fails with a schema mismatch error. This protects the table from bad data.
• .option("mergeSchema", "true") adds new columns to the table during the write.
• Changing a column's type or dropping a column usually needs overwriteSchema with an overwrite, or an ALTER TABLE command.
• For MERGE, you can use MERGE WITH SCHEMA EVOLUTION on recent runtimes, or the session setting spark.databricks.delta.schema.autoMerge.enabled.

Example: The source now sends a new coupon_code column.

(new_orders_df.write
    .format("delta")
    .mode("append")
    .option("mergeSchema", "true")
    .saveAsTable("main.silver.orders"))

Interview tip: Say you keep enforcement on by default and allow evolution only in bronze or behind a review, because silent schema change in gold breaks reports.

Q107. What is Change Data Feed (CDF) in Delta Lake?

Answer: Change Data Feed records row-level changes (inserts, updates and deletes) made to a Delta table, so downstream jobs can read only what changed between versions. You turn it on with the table property delta.enableChangeDataFeed = true.

How it works:

• CDF adds three metadata columns when you read changes: _change_type, _commit_version and _commit_timestamp.
• _change_type values are insert, update_preimage, update_postimage and delete.
• Read in batch with table_changes() in SQL or the readChangeFeed option, or read as a stream.
• Changes are available only from the version where CDF was enabled. Very recent runtimes also offer an automatic CDF mode for some Unity Catalog tables with row tracking.

Example: Push only changed customers from silver to gold.

changes = (spark.read
    .option("readChangeFeed", "true")
    .option("startingVersion", 120)
    .table("main.silver.customers")
    .filter("_change_type IN ('insert', 'update_postimage', 'delete')"))

Interview tip: Explain why you drop update_preimage rows when you apply changes downstream, because it shows you understand that each update produces two rows.

Q108. What are deletion vectors?

Answer: Deletion vectors are a Delta feature that marks deleted or changed rows in a small side file instead of rewriting the whole Parquet file. This makes DELETE, UPDATE and MERGE much faster, especially when only a few rows in a large file change.

How it works:

• Readers apply the deletion vector at query time and skip the marked rows.
• The table property is delta.enableDeletionVectors. Workspace settings can enable it on new tables automatically.
• The files are physically rewritten later by OPTIMIZE, auto compaction or REORG TABLE ... APPLY (PURGE).
• Deletion vectors also enable row-level concurrency, which reduces write conflicts.

Example: A GDPR-style request asks to remove one customer's data completely.

DELETE FROM main.silver.customers WHERE customer_id = 'C77821';
REORG TABLE main.silver.customers APPLY (PURGE);
VACUUM main.silver.customers;

Interview tip: Point out that a DELETE with deletion vectors is a soft delete at file level, so for legal deletion you also need REORG PURGE and VACUUM.

Q109. How does Delta Lake handle concurrent writes and conflicts?

Answer: Delta Lake uses optimistic concurrency control. Each writer reads a snapshot, writes new files, and then tries to commit. If another commit changed the same data in the meantime, the second writer gets a conflict exception and must retry.

How it works:

• Blind appends from many writers rarely conflict.
• Two jobs that UPDATE or MERGE into the same files can fail with errors such as ConcurrentAppendException or ConcurrentDeleteReadException.
• Schema or table property changes can fail all concurrent writes.
• Row-level concurrency (with deletion vectors, and liquid clustering or no partitioning) checks conflicts at row level, so fewer jobs fail.

Example: Two MERGE jobs load different regions into one table. I add the region to the MERGE condition so each job reads only its own data.

MERGE INTO main.silver.sales t
USING staged_south s
ON t.sale_id = s.sale_id AND t.region = 'SOUTH'
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Interview tip: Interviewers like to hear that you made conflicting jobs touch disjoint data (by partition or explicit predicate) and added retries, instead of only increasing retries.

Q110. What do DESCRIBE HISTORY and DESCRIBE DETAIL show?

Answer: DESCRIBE HISTORY shows every operation on a Delta table, newest first, with version, timestamp, user, operation and metrics. DESCRIBE DETAIL shows the current physical state: location, format, number of files, size, partition or clustering columns and table properties.

How it works:

• HISTORY answers "who changed what and when". Columns like operationMetrics show rows inserted, updated and deleted.
• DETAIL answers "what does the table look like now". numFiles and sizeInBytes quickly tell you if you have a small files problem.
• History is kept for delta.logRetentionDuration (30 days by default).

Example: Debug why yesterday's row count jumped.

DESCRIBE HISTORY main.silver.orders LIMIT 5;
DESCRIBE DETAIL main.silver.orders;

If numFiles is 40,000 and sizeInBytes is only 20 GB, the average file is tiny, so I plan an OPTIMIZE.

Interview tip: Mention operationMetrics from HISTORY. It is the fastest way to see how many rows a MERGE actually updated without writing any query.

Q111. What is the difference between managed and external tables?

Answer: For a managed table, Databricks (Unity Catalog) controls both the metadata and the data files in managed storage. For an external table, Unity Catalog stores only the metadata, and the data files sit in a path you own, registered through an external location.

How it works:

Example:

CREATE TABLE main.silver.customers (id BIGINT, name STRING);
CREATE TABLE main.raw.legacy_orders
LOCATION 'abfss://lake@stsales.dfs.core.windows.net/legacy/orders';

Interview tip: Databricks recommends managed tables for new work, so say you choose external only when another engine must own or read the files directly.

Q112. Delta Lake vs Parquet: what is the difference?

Answer: Parquet is a columnar file format. Delta Lake is a table format that stores data as Parquet files and adds a transaction log on top, which gives ACID writes, time travel, MERGE, schema enforcement and file-level statistics.

How it works:

Example: Converting an existing Parquet folder to Delta is one command, and it doesn't rewrite the data.

CONVERT TO DELTA parquet.`abfss://lake@stsales.dfs.core.windows.net/raw/orders`;

Interview tip: Don't say Delta is "faster than Parquet" as a blanket claim. Say it reads the same Parquet files but skips more of them, thanks to statistics and layout features.

10. Unity Catalog, Auto Loader, streaming and pipelines

Q113. What is Unity Catalog, and what is the three-level namespace?

Answer: Unity Catalog is Databricks' central governance layer for data and AI assets. It manages access control, auditing, lineage and discovery across all workspaces attached to one metastore. Every object is named with three levels: catalog.schema.table.

How it works:

• Metastore: the top-level container, usually one per region, attached to many workspaces.
• Catalog: the first level, often split by environment or business unit (dev, prod, sales).
• Schema: the second level, often a layer or domain (bronze, silver, gold).
• Objects at the third level: tables, views, volumes, functions and models.
• Permissions are granted once in Unity Catalog and apply in every attached workspace.

Example: The same gold table is reachable from the data engineering and BI workspaces.

USE CATALOG prod;
SELECT region, SUM(amount_inr) AS revenue
FROM prod.gold.daily_sales
GROUP BY region;

Interview tip: Contrast it with the old per-workspace Hive metastore, where every workspace had its own tables and its own permissions. That is the main reason teams migrate.

Q114. What are storage credentials, external locations and volumes in Unity Catalog?

Answer: A storage credential holds the cloud identity (on Azure, usually an access connector with a managed identity) that Unity Catalog uses to reach storage. An external location pairs that credential with a storage path. A volume is a Unity Catalog object for non-tabular files such as CSV, JSON, images or JARs.

How it works:

• Users never see keys. They get permissions such as READ FILES on an external location or volume.
• Managed volumes live in Unity Catalog managed storage. External volumes point to a path under an external location.
• Volume paths look like /Volumes/catalog/schema/volume/folder/file.csv.
• Volumes replace most old DBFS mount use cases.

Example:

CREATE EXTERNAL LOCATION landing_loc
URL 'abfss://landing@stsales.dfs.core.windows.net/'
WITH (STORAGE CREDENTIAL sales_mi_cred);

CREATE EXTERNAL VOLUME main.raw.landing_files
LOCATION 'abfss://landing@stsales.dfs.core.windows.net/files/';

Interview tip: Say that you replaced DBFS mounts with volumes and external locations, because mounts don't follow Unity Catalog permissions.

Q115. How do grants, row filters and column masks work in Unity Catalog?

Answer: Unity Catalog uses SQL GRANT and REVOKE on catalogs, schemas, tables, volumes and functions. Row filters hide rows and column masks hide column values, using SQL functions that run on every query.

How it works:

• To read a table, a user needs USE CATALOG, USE SCHEMA and SELECT. Privileges granted on a catalog or schema apply to the objects inside it.
• A row filter is a SQL UDF that returns BOOLEAN, attached with ALTER TABLE ... SET ROW FILTER.
• A column mask is a SQL UDF that returns the real or masked value, attached with ALTER COLUMN ... SET MASK.
• For many tables, Databricks recommends attribute-based access control (ABAC) policies built on governed tags.

Example: Analysts see only the South region and masked phone numbers.

GRANT SELECT ON TABLE main.gold.customers TO `analysts`;
CREATE FUNCTION main.sec.south_only(region STRING) RETURNS BOOLEAN
RETURN is_account_group_member('admins') OR region = 'SOUTH';
ALTER TABLE main.gold.customers SET ROW FILTER main.sec.south_only ON (region);
CREATE FUNCTION main.sec.mask_phone(p STRING) RETURNS STRING
RETURN CASE WHEN is_account_group_member('pii_readers') THEN p
            ELSE concat('XXXXXX', right(p, 4)) END;
ALTER TABLE main.gold.customers ALTER COLUMN phone SET MASK main.sec.mask_phone;

Interview tip: Grant to groups, never to individual users. Interviewers check this habit.

Q116. How does data lineage work in Unity Catalog?

Answer: Unity Catalog captures lineage automatically at runtime for queries that run on Unity Catalog-enabled compute. It records which tables and columns were read to produce each table, and which notebooks, jobs and dashboards were involved.

How it works:

• Lineage is captured for SQL and DataFrame operations. You don't need extra code.
• You see it as a graph on the table's Lineage tab in Catalog Explorer, down to column level.
• The same data is available in system tables: system.access.table_lineage and system.access.column_lineage.
• A user sees lineage only for objects they have permission on.

Example: Before dropping a column from a silver table, I check which downstream tables use it.

SELECT DISTINCT target_table_full_name, target_column_name
FROM system.access.column_lineage
WHERE source_table_full_name = 'main.silver.orders'
  AND source_column_name = 'discount_inr';

Interview tip: Tell an impact-analysis story (find out who breaks before you change something). It shows you used lineage, not just read about it.

Q117. What is Auto Loader, and how does it handle schema changes?

Answer: Auto Loader is a Databricks Structured Streaming source (cloudFiles) that incrementally loads new files from cloud storage exactly once. It tracks processed files in its checkpoint and can infer and evolve the schema.

How it works:

• cloudFiles.schemaLocation stores the inferred schema and turns on schema evolution.
• For JSON and CSV, columns are inferred as strings unless cloudFiles.inferColumnTypes is true.
• The default evolution mode is addNewColumns: the stream stops with an UnknownFieldException, saves the new schema, and continues after a restart. Other modes are rescue, failOnNewColumns and none.
• Values that don't match the schema go to the _rescued_data column instead of being lost.

Example: Load UPI payment JSON files from ADLS Gen2 into bronze.

df = (spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/Volumes/main/raw/meta/schemas/upi")
    .option("cloudFiles.inferColumnTypes", "true")
    .load("abfss://landing@stsales.dfs.core.windows.net/upi/"))

(df.writeStream
    .option("checkpointLocation", "/Volumes/main/raw/meta/checkpoints/upi")
    .trigger(availableNow=True)
    .toTable("main.bronze.upi_payments"))

Interview tip: Mention that you run Auto Loader as a Lakeflow Job with retries, so the expected restart after a new column happens on its own.

Q118. Directory listing vs file notification mode in Auto Loader: what is the difference?

Answer: In directory listing mode (the default), Auto Loader lists the input folder to find new files. In file notification mode, it uses storage events instead of listing the folder. Databricks now recommends file notification with file events for most workloads.

How it works:

Example: A folder receives lakhs of small IoT files every day, and listing gets slower each week. I enable file events on the external location and add one option.

.option("cloudFiles.useManagedFileEvents", "true")

Interview tip: Say that the choice depends on file volume and folder size, and that file events need Unity Catalog and an external location.

Q119. What is Structured Streaming in Spark?

Answer: Structured Streaming is Spark's streaming engine. It treats a stream as an unbounded table and runs the same DataFrame or SQL logic on new data as it arrives, by default in small micro-batches.

How it works:

• Sources include Kafka, Event Hubs, Auto Loader and Delta tables. Sinks include Delta tables, Kafka and foreachBatch.
• Output modes: append (only new rows), update (changed rows) and complete (the whole result, for small aggregates).
• A checkpoint location stores offsets and state, so the query restarts where it stopped.
• foreachBatch lets you run batch logic, such as MERGE, on each micro-batch.

Example: Read a Delta table as a stream and upsert each micro-batch.

def upsert(batch_df, batch_id):
    batch_df.createOrReplaceTempView("updates")
    batch_df.sparkSession.sql("""
        MERGE INTO main.silver.orders t USING updates s ON t.order_id = s.order_id
        WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *""")

(spark.readStream.table("main.bronze.orders")
    .writeStream.foreachBatch(upsert)
    .option("checkpointLocation", "/Volumes/main/silver/meta/cp/orders")
    .start())

Interview tip: Say that streaming and batch share one API, so the same transformation code can run in both modes.

Q120. What are the trigger types in Structured Streaming?

Answer: A trigger decides when a streaming query processes data. The main options are the default (the next micro-batch starts as soon as the last one ends), processingTime (a fixed interval) and availableNow (process everything available now, then stop).

How it works:

• Default: lowest latency for micro-batch, but the compute runs all the time.
• processingTime="1 minute": a steady cadence with fewer, larger batches.
• availableNow=True: batch-style incremental processing. It splits the backlog into several batches, respects options like maxFilesPerTrigger, and stops when done. It replaces the older once=True, which is deprecated.
• Continuous: experimental, very low latency, limited operations.

Example: A daily job that loads only new files and then lets the job cluster shut down.

(df.writeStream
    .trigger(availableNow=True)
    .option("checkpointLocation", "/Volumes/main/raw/meta/cp/sales")
    .toTable("main.bronze.sales"))

Interview tip: Link triggers to cost. Saying that availableNow on job compute gives streaming guarantees at batch cost is a strong answer.

Q121. What is a checkpoint in Structured Streaming, and how does it give exactly-once results?

Answer: A checkpoint is a folder where a streaming query saves its progress: source offsets, committed batches and state. Exactly-once results come from three things together: a replayable source, the checkpoint, and an idempotent sink such as Delta.

How it works:

• Before a batch runs, Spark writes the planned offsets. After the sink commits, Spark marks the batch as done.
• If the job crashes, it restarts from the last committed batch and replays the same offsets.
• The Delta sink records the batch in its log, so a replayed batch isn't written twice.
• foreachBatch is at-least-once by default. Make it idempotent with MERGE, or with the txnAppId and txnVersion write options.
• Each query needs its own checkpoint. Never share one between two queries.

Example: A foreachBatch append made idempotent with Delta's transaction options.

def write_batch(df, batch_id):
    (df.write.format("delta")
        .option("txnAppId", "orders_to_gold")
        .option("txnVersion", batch_id)
        .mode("append")
        .saveAsTable("main.gold.orders"))

Interview tip: Never say "I delete the checkpoint to fix errors" without explaining the reprocessing and duplicate risk it creates.

Q122. What is a watermark, and how does Spark handle late data?

Answer: A watermark tells Spark how late an event can arrive, by event time, and still be counted. Spark keeps state only within that delay, then drops old state and ignores events that arrive later than the watermark.

How it works:

• withWatermark("event_time", "10 minutes") sets the watermark to the latest event time seen minus 10 minutes.
• Window aggregations in append mode emit a window only after the watermark passes the window end.
• Without a watermark, state for aggregations, deduplication and stream-stream joins can grow forever.
• A longer delay gives more complete counts but more state and later output.

Example: Count UPI transactions per 5-minute window, allowing 10 minutes of lateness.

from pyspark.sql import functions as F

counts = (payments_stream
    .withWatermark("event_time", "10 minutes")
    .groupBy(F.window("event_time", "5 minutes"), "bank_code")
    .agg(F.count("txn_id").alias("txn_count")))

Interview tip: Say how you chose the delay (from the real lateness you measured) and what you do with very late data, such as keeping it in bronze and fixing totals in a batch job.

Q123. What is a stream-static join?

Answer: A stream-static join joins a streaming DataFrame with a static (batch) DataFrame, such as an order stream with a customer dimension. When the static side is a Delta table, each micro-batch joins against the latest version of that table.

How it works:

• The static side needs no watermark, and Spark keeps no join state.
• Inner joins and left outer joins (stream on the left) are supported.
• New dimension rows are picked up in the next micro-batch. Facts already processed are not joined again if the dimension changes later.
• If the dimension is small, Spark broadcasts it.

Example: Enrich streaming orders with customer details.

orders = spark.readStream.table("main.bronze.orders")
customers = spark.read.table("main.silver.customers")

enriched = orders.join(customers, "customer_id", "left")
(enriched.writeStream
    .option("checkpointLocation", "/Volumes/main/silver/meta/cp/enriched")
    .toTable("main.silver.orders_enriched"))

Interview tip: Mention the late-dimension problem: if a customer row arrives after the order, that order keeps nulls, so you need a fix-up job or a stream-stream join.

Q124. What are Lakeflow Spark Declarative Pipelines and expectations?

Answer: Lakeflow Spark Declarative Pipelines (earlier called Delta Live Tables, or DLT) is a Databricks framework where you declare tables and the queries that build them, and Databricks handles ordering, dependencies, retries, checkpoints and compute. Expectations are data quality rules on a table that warn, drop or fail on bad rows.

How it works:

• In Python you write from pyspark import pipelines as dp. Old import dlt code still runs.
• @dp.table creates a streaming table, and @dp.materialized_view creates a materialized view.
• @dp.expect keeps bad rows and records them, @dp.expect_or_drop removes them, and @dp.expect_or_fail stops the update. SQL uses CONSTRAINT ... EXPECT (...) ON VIOLATION ....
• Expectation results appear in the pipeline UI and event log.

Example:

from pyspark import pipelines as dp
from pyspark.sql import functions as F

@dp.table(name="silver_orders")
@dp.expect_or_drop("valid_amount", "amount_inr > 0")
@dp.expect_or_fail("order_id_present", "order_id IS NOT NULL")
def silver_orders():
    return (spark.readStream.table("bronze_orders")
            .withColumn("loaded_at", F.current_timestamp()))

Interview tip: Explain which action you pick for each rule: fail when the business breaks (missing key), drop for bad rows, warn for rules you are still tuning.

Q125. What is the medallion architecture?

Answer: The medallion architecture organises a lakehouse into three layers of rising quality: bronze (raw), silver (cleaned and conformed) and gold (business-ready). Each layer is a set of Delta tables, often one Unity Catalog schema per layer.

How it works:

• Bronze keeps history, so you can rebuild silver and gold without going back to the source system.
• Access is usually tight on bronze and wider on gold.

Example: POS files from 900 stores land in bronze.pos_sales. Silver removes duplicates and casts amounts to DECIMAL. Gold builds gold.daily_store_revenue for a Power BI dashboard.

Interview tip: Don't present medallion as a fixed rule of three tables. Say the layers are quality stages, and some sources need more or fewer hops.

Q126. COPY INTO vs Auto Loader: when do you use each?

Answer: Both load files from cloud storage into Delta tables and skip files that were already loaded. COPY INTO is a SQL command that suits simple scheduled loads of up to thousands of files. Auto Loader is a streaming source that scales to millions of files, supports file notification and handles schema evolution better.

How it works:

Example: A finance team loads monthly GST CSV files from a SQL warehouse.

COPY INTO main.bronze.gst_returns
FROM 'abfss://landing@stfin.dfs.core.windows.net/gst/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

Interview tip: Say Auto Loader is your default for production ingestion, and COPY INTO is fine for small, SQL-only teams.

11. Scenario-based PySpark and Databricks interview questions (by experience)

For 2–3 years experience

Q127. Your Spark job is stuck at 199 of 200 tasks for an hour. What do you do?

Answer: This is almost always data skew: one partition has far more rows than the others, usually because of a hot join or groupBy key. I confirm it in the Spark UI, then fix it with AQE skew join handling, a broadcast join, or salting.

How it works:

• In the Spark UI stage page, I compare the max task duration and shuffle read size with the median. A max far above the median means skew.
• I find the hot key with a quick groupBy(key).count() ordered by count.
• AQE skew join (spark.sql.adaptive.skewJoin.enabled, on by default) splits large partitions in sort-merge joins.
• If AQE can't help (for example a skewed groupBy), I salt the key and aggregate in two steps.

Example: customer_id = 'UNKNOWN' holds 40% of the rows. I salt it.

from pyspark.sql import functions as F

salted = orders.withColumn("salt", (F.rand() * 16).cast("int"))
partial = salted.groupBy("customer_id", "salt").agg(F.sum("amount_inr").alias("amt"))
final = partial.groupBy("customer_id").agg(F.sum("amt").alias("total_inr"))

How I prove it works: I rerun the job and check the stage in the Spark UI. Max task time should now be close to the median, and the total stage time should drop.

Interview tip: Also ask whether the hot key is valid data. Often "UNKNOWN" or null keys should be filtered or handled separately.

Q128. The driver fails with an OutOfMemoryError after someone added collect(). How do you fix it?

Answer: collect() pulls every row to the driver, which has limited memory. I remove it and keep the work distributed: write results to a table, use limit() or take() for samples, or aggregate before bringing data back.

How it works:

• collect(), toPandas() and large broadcast variables all load data into driver memory.
• spark.driver.maxResultSize limits result size. Raising it or the driver size only hides the problem.
• Loops that call collect() to build per-row logic should become joins or window functions.
• If Python really needs rows one by one, toLocalIterator() streams them a partition at a time.

Example: The old code collected 30 million orders to compute totals in Python.

from pyspark.sql import functions as F

totals = (orders.groupBy("state")
          .agg(F.sum("amount_inr").alias("total_inr")))
totals.write.mode("overwrite").saveAsTable("main.gold.state_totals")
display(totals.limit(20))

How I prove it works: The job finishes on the same driver size, and the driver memory chart on the compute metrics page stays flat instead of climbing to the limit.

Interview tip: Say you add a code review rule: no collect() or toPandas() on unbounded data.

Q129. Design a daily incremental load of files from ADLS Gen2 into Delta.

Answer: I use Auto Loader with trigger(availableNow=True) in a scheduled Lakeflow Job. Auto Loader remembers processed files in its checkpoint, so each run loads only new files and then stops.

How it works:

• Source path in ADLS Gen2 is registered as a Unity Catalog external location.
• Schema and checkpoint live in a volume, one folder per source.
• I add audit columns: file path from _metadata.file_path and load time.
• The job runs daily on job compute or serverless with retries enabled.

Example:

from pyspark.sql import functions as F

(spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "csv")
    .option("cloudFiles.schemaLocation", "/Volumes/main/raw/meta/schemas/sales")
    .load("abfss://landing@stsales.dfs.core.windows.net/sales/")
    .withColumn("source_file", F.col("_metadata.file_path"))
    .withColumn("loaded_at", F.current_timestamp())
    .writeStream
    .option("checkpointLocation", "/Volumes/main/raw/meta/cp/sales")
    .trigger(availableNow=True)
    .toTable("main.bronze.sales"))

How I prove it works: I drop three files and run the job, then drop one more and run again. The second run's numInputRows in the query progress covers only the new file, and source_file shows it.

Interview tip: Say you chose availableNow instead of an always-on stream because the business needs data daily, so you pay only for minutes.

Q130. After a job rerun, the silver table has duplicate rows. How do you make the load idempotent?

Answer: The job used append, so a rerun added the same rows again. I change the write to a MERGE on the business key, after deduplicating the source, so running it twice gives the same result as running it once.

How it works:

• Deduplicate the incoming batch with row_number() over the key, keeping the latest record.
• MERGE on the key: update when matched, insert when not matched.
• Clean existing duplicates once with the same window logic and an overwrite.
• For pure append jobs, replaceWhere on the load date also makes reruns safe.

Example:

from pyspark.sql import functions as F, Window

w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())
latest = (bronze_batch.withColumn("rn", F.row_number().over(w))
          .filter("rn = 1").drop("rn"))
latest.createOrReplaceTempView("src")
spark.sql("""MERGE INTO main.silver.orders t USING src s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *""")

How I prove it works: I run the job twice for the same day. COUNT(1) and COUNT(DISTINCT order_id) must be equal, and DESCRIBE HISTORY shows the second MERGE inserted 0 rows.

Interview tip: Use the word "idempotent" and explain it simply: same input, rerun any number of times, same output.

Q131. Queries on a Delta table are slow because it has thousands of small files. What do you do?

Answer: I compact the files with OPTIMIZE and stop new small files at the source with optimized writes and auto compaction. For a Unity Catalog managed table, I also turn on predictive optimization so maintenance runs automatically.

How it works:

• Small files come from frequent streaming micro-batches, too many partitions, or over-partitioning by a high-cardinality column.
• DESCRIBE DETAIL shows numFiles and sizeInBytes, so I can compute the average file size.
• delta.autoOptimize.optimizeWrite and delta.autoOptimize.autoCompact reduce small files on write.
• If the table is partitioned by something like customer_id, I redesign it with liquid clustering.

Example:

DESCRIBE DETAIL main.bronze.clickstream;
ALTER TABLE main.bronze.clickstream SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact' = 'true');
OPTIMIZE main.bronze.clickstream;

How I prove it works: I compare numFiles before and after OPTIMIZE, and I rerun the same slow dashboard query. The query profile shows fewer files read and lower duration.

Interview tip: Fix the cause (writer pattern or partitioning), not just the symptom. Interviewers want both.

Q132. The source team added new fields to the JSON files and the pipeline broke. How do you handle it?

Answer: I use Auto Loader with a schema location so new columns are added automatically after a restart, and the _rescued_data column catches type mismatches. I then decide, per layer, whether the new field should flow on to silver and gold.

How it works:

• Bronze: cloudFiles.schemaEvolutionMode = addNewColumns, with job retries so the stream restarts by itself.
• Type changes (a number arriving as a string) land in _rescued_data instead of failing.
• Silver: I select columns explicitly, so a new bronze column doesn't silently reach gold.
• I set alerts when _rescued_data is not null.

Example:

bronze = (spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/Volumes/main/raw/meta/schemas/orders")
    .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
    .load("abfss://landing@stsales.dfs.core.windows.net/orders/"))

How I prove it works: In test, I drop a file with an extra field coupon_code. The first run fails with UnknownFieldException, the retry succeeds, and the bronze table now has coupon_code with values only for new rows.

Interview tip: Say you also talk to the source team about data contracts. Technical evolution is not a replacement for agreement.

Q133. You need to join a 2 TB orders table with a 50 MB product table. How do you make it fast?

Answer: I broadcast the 50 MB table, so Spark sends a copy to every executor and joins without shuffling the 2 TB side. The default auto-broadcast threshold is 10 MB, so I use a broadcast hint or let AQE convert the join at runtime.

How it works:

• A sort-merge join shuffles both sides by the key, which is costly for 2 TB.
• A broadcast hash join avoids shuffling the big table.
• AQE can switch to broadcast after it sees the real size of a filtered side.
• Broadcasting a table that is too big can cause OOM on driver or executors, so I keep it small and select only needed columns.

Example:

from pyspark.sql import functions as F

products = spark.table("main.silver.products").select("product_id", "category", "brand")
result = spark.table("main.silver.orders").join(F.broadcast(products), "product_id")
result.explain()

How I prove it works: explain() shows BroadcastHashJoin instead of SortMergeJoin, and the Spark UI shows no shuffle exchange for the orders side. The stage time drops sharply.

Interview tip: Mention that you checked the size of the small table after filtering and column pruning, not its size on disk.

Q134. Events reach your streaming job up to 2 hours late. How do you keep counts correct?

Answer: I set a watermark based on how late events really arrive, here a bit over 2 hours, so late events still update their windows. For anything later than that, I keep the raw events in bronze and correct the totals with a batch job.

How it works:

• withWatermark("event_time", "3 hours") keeps window state for 3 hours after the latest event time.
• In append mode, a window is written only after the watermark passes it. In update mode, it is re-emitted as late data arrives.
• Bronze always keeps every event, even ones the stream drops.
• A daily batch job recomputes the last few days from bronze for exact totals.

Example:

from pyspark.sql import functions as F

hourly = (events
    .withWatermark("event_time", "3 hours")
    .groupBy(F.window("event_time", "1 hour"), "app_id")
    .agg(F.count("event_id").alias("events")))

How I prove it works: In test, I replay events with timestamps 90 minutes old and confirm their window count goes up. Then I send events 5 hours old and confirm they are dropped from the stream but present in bronze.

Interview tip: Say the watermark is a trade-off between correctness and state size, and give the number you measured, not a guess.

For 4–6 years experience

Q135. Build an SCD Type 2 customer dimension on Delta Lake.

Answer: I use one MERGE with a staged source. Changed customers appear twice in the source: once to close the current row, and once (with a null merge key) to insert the new version. New customers are simply inserted.

How it works:

• The dimension has is_current, start_date and end_date columns.
• The union creates the extra row for each changed customer.
• WHEN MATCHED closes the old row. WHEN NOT MATCHED inserts new and changed versions.
• I only compare tracked columns (here city) to detect change.

Example:

MERGE INTO main.gold.dim_customer t
USING (
  SELECT s.customer_id AS merge_key, s.* FROM main.silver.customer_updates s
  UNION ALL
  SELECT NULL AS merge_key, s.* FROM main.silver.customer_updates s
  JOIN main.gold.dim_customer d
    ON s.customer_id = d.customer_id AND d.is_current = true
  WHERE s.city <> d.city
) u
ON t.customer_id = u.merge_key AND t.is_current = true
WHEN MATCHED AND t.city <> u.city THEN UPDATE SET is_current = false, end_date = current_date()
WHEN NOT MATCHED THEN INSERT (customer_id, city, start_date, end_date, is_current)
  VALUES (u.customer_id, u.city, current_date(), NULL, true);

How I prove it works: I change one customer's city and rerun. That customer now has two rows, only one with is_current = true, and every customer has exactly one current row.

Interview tip: Mention that Lakeflow pipelines can do this declaratively with AUTO CDC (earlier APPLY CHANGES) and STORED AS SCD TYPE 2.

Q136. Databricks costs went up 40% this quarter. How do you bring them down?

Answer: I first find where the money goes using the billing system tables, then fix the biggest items: jobs on all-purpose compute, idle clusters, oversized clusters and missing auto-termination. Cluster policies stop the problem from coming back.

How it works:

• system.billing.usage shows DBUs by workspace, SKU, job, cluster and custom tags.
• Move scheduled notebooks from all-purpose compute to job compute, which has a lower DBU rate, or to serverless jobs.
• Set auto-termination, right-size with autoscaling, and use spot instances for workers where it's safe.
• Use Photon where it reduces total runtime, and availableNow instead of always-on streams when latency allows.
• Cluster policies enforce limits on node types, max workers and tags.

Example: Find the top 10 jobs by DBUs in the last 30 days.

SELECT usage_metadata.job_id, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
  AND usage_metadata.job_id IS NOT NULL
GROUP BY usage_metadata.job_id
ORDER BY dbus DESC LIMIT 10;

How I prove it works: I track the same query week by week, with tags per team, and show the DBU trend falling for the jobs I changed while their SLAs still pass.

Interview tip: Start with measurement, not guesses. Interviewers dislike "I reduced cluster size" without data.

Q137. How do you migrate Hive metastore tables to Unity Catalog?

Answer: I inventory the Hive metastore, set up catalogs, external locations and grants in Unity Catalog, then upgrade tables in waves. External tables can be upgraded with SYNC. Managed tables in workspace storage need a copy, such as CREATE TABLE ... DEEP CLONE or CTAS.

How it works:

• Inventory tables, views, mounts, jobs and permissions. The Databricks Labs UCX tool helps automate this, though it is not officially supported by Databricks.
• Create storage credentials and external locations to replace mounts.
• Run SYNC ... DRY RUN to check readiness, then SYNC. The Catalog Explorer upgrade wizard runs SYNC for you.
• Update code to three-level names and move jobs to Unity Catalog-enabled compute (standard or dedicated access mode).

Example:

SYNC SCHEMA main.sales FROM hive_metastore.sales DRY RUN;
SYNC SCHEMA main.sales FROM hive_metastore.sales;
CREATE TABLE main.sales.customers DEEP CLONE hive_metastore.sales.customers;

How I prove it works: For each table, I compare row counts and a checksum of key columns between hive_metastore and main, and I run each migrated job in parallel with the old one for a few days before switching.

Interview tip: Say you migrate in waves by domain, not big bang, and you keep the Hive tables read-only until sign-off.

Q138. Analysts must not see customer PII, but data scientists can. How do you set this up?

Answer: I keep one table and use Unity Catalog column masks for PII columns and, where needed, row filters. Group membership decides what each user sees, so there is no duplicate "masked copy" to maintain.

How it works:

• Create account groups such as analysts and pii_readers.
• Write a mask function that checks is_account_group_member() and returns the raw or masked value.
• Attach masks to phone, email and pan_number. For many tables, use ABAC policies with governed tags instead.
• Audit access with system.access.audit.

Example:

CREATE FUNCTION main.sec.mask_email(e STRING) RETURNS STRING
RETURN CASE WHEN is_account_group_member('pii_readers') THEN e
            ELSE concat('***@', split(e, '@')[1]) END;
ALTER TABLE main.silver.customers ALTER COLUMN email SET MASK main.sec.mask_email;
GRANT SELECT ON TABLE main.silver.customers TO `analysts`;

How I prove it works: I run the same SELECT email FROM main.silver.customers LIMIT 5 as a member of each group. Analysts see masked values, and pii_readers see real emails.

Interview tip: Mention that masks also apply in BI tools and notebooks, because enforcement happens in Unity Catalog, not in the report.

Q139. A production job failed at 2 a.m. How do you debug it?

Answer: I start from the Lakeflow Jobs run page to find the failed task and its error, then go to the Spark UI and driver logs for the root cause. After the fix, I use Repair run to rerun only the failed and downstream tasks.

How it works:

• Run page: task status, error message, notebook output and parameters.
• Driver logs: full stack trace. The real cause is often the first "Caused by" line.
• Spark UI: failed stage, executor lost events, spill and skew.
• Compute event log: node lost, spot reclaim, autoscaling or init script failure.
• Common causes: source file missing, schema change, OOM, concurrent write conflict, expired secret.

Example: The error says ConcurrentAppendException. History shows a backfill job wrote to the same table at 2 a.m.

DESCRIBE HISTORY main.silver.orders LIMIT 10;

I move the backfill to another window and add an explicit date predicate to the MERGE.

How I prove it works: The repaired run succeeds, and the next scheduled runs succeed for a week. I also add a failure notification so the team knows within minutes next time.

Interview tip: Walk through your steps in order. Interviewers score the method more than the specific bug.

Q140. How do you orchestrate Databricks with Azure Data Factory?

Answer: ADF handles copying from source systems and the overall schedule, and Databricks handles transformation. ADF calls Databricks with the Azure Databricks Job activity (which runs an existing Lakeflow Job, on serverless) or the older Notebook activity, and passes parameters such as the load date.

How it works:

• The linked service uses managed identity authentication, not a personal access token, where possible.
• Pipeline parameters like load_date are passed to job or notebook parameters and read with dbutils.widgets.get().
• The notebook returns a small JSON result with dbutils.notebook.exit(), which ADF reads from the activity output.
• Many teams now keep the whole flow in Lakeflow Jobs and use ADF only for copy activities from on-premises sources.

Example: Inside the Databricks notebook called by ADF.

import json

dbutils.widgets.text("load_date", "")
load_date = dbutils.widgets.get("load_date")
df = spark.table("main.bronze.sales").filter(f"sale_date = '{load_date}'")
df.write.mode("overwrite").option("replaceWhere", f"sale_date = '{load_date}'").saveAsTable("main.silver.sales")
dbutils.notebook.exit(json.dumps({"rows": df.count()}))

How I prove it works: I trigger the ADF pipeline with a test date and check that the activity output shows the same row count as a COUNT(1) on the silver table for that date.

Interview tip: Explain why you split responsibilities, and say you avoid running heavy logic in ADF Data Flows when Databricks already does it.

Q141. How do you set up CI/CD for Databricks across dev, test and prod?

Answer: I use Declarative Automation Bundles (earlier called Databricks Asset Bundles). The code, jobs and pipelines are defined in YAML in Git, and the Databricks CLI deploys the same bundle to dev, test and prod targets from Azure DevOps or GitHub Actions.

How it works:

• databricks.yml defines resources (jobs, pipelines) and targets for each environment, with different workspace hosts and variables like catalog names.
• Developers work in Git folders and deploy to dev with mode: development, which prefixes resource names.
• The pipeline runs databricks bundle validate, unit tests, then databricks bundle deploy -t test.
• Prod deploys after approval, using a service principal, not a personal user.

Example:

databricks bundle validate -t prod
databricks bundle deploy -t prod
databricks bundle run -t prod daily_sales_job

How I prove it works: After a merge to main, the release pipeline deploys to test, runs the job with test data and checks row counts. Prod gets the same Git commit, and the job settings in the workspace match the YAML.

Interview tip: Mention that catalog names are variables per target, so the same code writes to dev.silver in dev and prod.silver in prod.

Q142. Design a medallion lakehouse that ingests 50 source systems.

Answer: I build a metadata-driven framework: a config table lists each source, its format, path, keys and load type, and one generic ingestion job reads it. Data flows bronze to silver to gold, with Unity Catalog for governance.

How it works:

• Ingestion: Auto Loader for files and Lakeflow Connect or ADF for databases and SaaS apps, landing in bronze with audit columns.
• Silver: generic dedup and MERGE by key from the config table, plus expectations for quality.
• Gold: hand-built facts and dimensions per business domain.
• Catalogs: one per environment; schemas per layer or domain.
• Jobs: Lakeflow Jobs with a for-each task over sources, retries and alerts.

Example: One config row drives a whole source.

source_name | format | landing_path | keys | load_type | active
pos_sales | csv | landing/pos/ | sale_id | merge | true
upi_txn | json | landing/upi/ | txn_id | append | true

How I prove it works: Adding source 51 is one config row and no code change. A daily reconciliation job compares source counts with bronze and silver counts for all sources and posts the result to a dashboard.

Interview tip: Say what you don't make generic: gold logic stays specific to the business, because generic gold layers are hard to understand.

For 8–10+ years / lead and architect

Q143. A streaming job's checkpoint is corrupted and it won't restart. How do you recover?

Answer: I first try to keep the checkpoint by fixing the actual cause, such as a changed query or a deleted source file. If the checkpoint really can't be used, I start a new checkpoint from a known point and make the sink idempotent so the overlap doesn't create duplicates.

How it works:

• Read the error. Many "corrupt checkpoint" cases are really query changes that aren't allowed with an existing checkpoint, such as changed aggregation or state schema.
• For a Delta source, I start a new checkpoint with startingVersion set just before the last committed version I can confirm.
• For Auto Loader, a new checkpoint would reload all files, so I limit it with modifiedAfter or reload into a staging table.
• The sink uses MERGE on a key, so reprocessed rows replace instead of duplicating.

Example:

(spark.readStream
    .option("startingVersion", 5120)
    .table("main.bronze.orders")
    .writeStream.foreachBatch(upsert_to_silver)
    .option("checkpointLocation", "/Volumes/main/silver/meta/cp/orders_v2")
    .start())

How I prove it works: After catch-up, I compare counts and key checksums between bronze and silver for the affected time range, and they match with zero duplicate keys.

Interview tip: Mention prevention: version checkpoint paths, never edit them by hand, and keep the source retention longer than your worst-case recovery time.

Q144. You must reprocess the last 30 days of data without creating duplicates. How?

Answer: I rebuild the 30-day slice and overwrite exactly that slice with replaceWhere, in one atomic Delta transaction. Rows outside the date range are not touched, and a rerun gives the same result.

How it works:

• replaceWhere deletes rows matching the predicate and inserts the new rows in one commit.
• Spark checks that the new rows match the predicate, so a wrong date can't leak in.
• Downstream streams that read this table see the rewrite as changes, so I plan for that (for example with skipChangeCommits on the downstream stream and a separate batch rebuild).
• If the table is large, I run it in weekly chunks.

Example:

start, end = "2026-09-20", "2026-10-20"
fixed = rebuild_orders(spark.table("main.bronze.orders")
                       .filter(f"order_date >= '{start}' AND order_date < '{end}'"))
(fixed.write.format("delta").mode("overwrite")
    .option("replaceWhere", f"order_date >= '{start}' AND order_date < '{end}'")
    .saveAsTable("main.silver.orders"))

How I prove it works: Before and after, I compare counts per day for the 30 days with the source, check distinct keys equal total rows, and confirm that days outside the range have the same counts as before.

Interview tip: Mention that time travel gives you a rollback if the rebuild is wrong, so you run it with confidence.

Q145. A 5 TB table is queried by date and by customer. Partitioning or liquid clustering?

Answer: I use liquid clustering on (order_date, customer_id). Partitioning by date helps date filters but does nothing for customer filters, and partitioning by customer creates millions of tiny files. Liquid clustering serves both filters and lets me change keys later.

How it works:

• Data skipping uses file-level min/max stats on the clustering keys, so both filters skip files.
• Clustering happens incrementally with OPTIMIZE, or automatically through predictive optimization on managed tables.
• Moving an existing partitioned table means creating a clustered table and loading it, or the newer REPLACE PARTITIONED BY WITH CLUSTER BY command on recent runtimes.
• Hive-style partitioning still makes sense only for very large tables with a low-cardinality column that every query filters on.

Example:

CREATE TABLE main.gold.orders_clustered
CLUSTER BY (order_date, customer_id)
AS SELECT * FROM main.gold.orders;

OPTIMIZE main.gold.orders_clustered;

How I prove it works: I run the top 20 real queries from the query history on both tables and compare files read, bytes scanned and duration in the query profile.

Interview tip: Base the key choice on real query history, not guesses, or mention CLUSTER BY AUTO to let Databricks decide.

Q146. When do you choose serverless compute over classic job clusters?

Answer: I choose serverless when fast start-up, no infrastructure management and short or bursty workloads matter most. I choose classic job compute when I need custom Spark configs, init scripts, specific instance types, unsupported languages or libraries, or strict network setups that serverless doesn't cover yet.

How it works:

Example: A team runs 300 small jobs a day that each spend 6 minutes waiting for a cluster and 3 minutes running. Serverless removes the wait. A steady 8-hour nightly ML feature job stays on a tuned classic cluster.

How I prove it works: I run the same job both ways for a week and compare total cost from system.billing.usage and the end-to-end duration from the job run history.

Interview tip: Avoid "serverless is always cheaper". Say you measured it per workload.

Q147. Design a data quality framework for a lakehouse.

Answer: I put quality checks at each layer: schema and file checks in bronze, rule-based expectations in silver, and reconciliation in gold. Results are stored in tables so they can be tracked, alerted on and shown on a dashboard.

How it works:

• Bronze: Auto Loader rescued data, file count and size checks.
• Silver: Lakeflow pipeline expectations (warn, drop, fail) or Delta CHECK constraints, with bad rows sent to a quarantine table.
• Gold: source-to-target counts and sums, freshness checks.
• Monitoring: Databricks data quality monitoring (freshness and completeness anomaly detection, plus data profiling) on Unity Catalog tables, and SQL alerts.
• Rules live in a config table, owned with the data owners.

Example: Quarantine invalid rows instead of losing them.

from pyspark import pipelines as dp

rules = {"valid_amount": "amount_inr > 0", "has_customer": "customer_id IS NOT NULL"}
quarantine_rule = "NOT COALESCE((" + ") AND (".join(rules.values()) + "), false)"

@dp.table(name="silver_orders")
@dp.expect_all_or_drop(rules)
def silver_orders():
    return spark.readStream.table("bronze_orders")

@dp.table(name="quarantine_orders")
def quarantine_orders():
    return spark.readStream.table("bronze_orders").filter(quarantine_rule)

How I prove it works: I load a test file with known bad rows. The pipeline event log shows the exact number dropped per rule, and the quarantine table has the same number of rows.

Interview tip: Say who gets alerted and what they do. A framework without ownership is just logging.

Q148. A multi-hop pipeline must deliver gold data by 7 a.m. every day. How do you guarantee the SLA?

Answer: I work backwards from 7 a.m., set a time budget for each hop, and monitor each one. I design for early failure detection, fast reruns and enough slack to recover once before the deadline.

How it works:

• Build one Lakeflow Job with task dependencies (bronze, silver, gold), task retries and timeouts.
• Use duration threshold alerts so the team hears at 5:30 a.m., not 7:15 a.m.
• Make every task idempotent so a repair run is safe.
• Use table update or file arrival triggers so processing starts as soon as data lands, not at a fixed late time.
• Track run times in the jobs system tables to spot slow growth before it breaks the SLA.

Example: Budget for a 7 a.m. SLA with data landing at 3 a.m.

03:00 data lands -> file arrival trigger starts job
03:00-04:00 bronze (timeout 75 min, 2 retries)
04:00-05:00 silver (timeout 75 min, 2 retries)
05:00-05:30 gold (timeout 45 min, 1 retry)
05:30-07:00 slack for one repair run

How I prove it works: I report SLA hit rate for the last 90 days from job run history, plus the trend of each task's duration.

Interview tip: Mention upstream dependencies. Many SLA misses come from late source data, so you agree on landing times with the source team too.

Q149. Your company asks: Databricks or Microsoft Fabric? How do you decide?

Answer: I decide on workload, team skills and governance, not on brand. Fabric suits Power BI-heavy teams that want one SaaS platform with capacity-based billing. Databricks suits heavy data engineering, streaming, ML and multi-cloud needs, with Unity Catalog governance and fine control over compute.

How it works:

Example: A retail group runs Spark streaming for 2,000 stores and ML forecasting on AWS and Azure, so I pick Databricks. A mid-size company with 50 Power BI reports and SQL-skilled staff fits Fabric. Both use Delta, and they can work together through shortcuts and mirroring.

How I prove it works: I run a two-week proof of concept with three real workloads on both and compare cost, run time, developer effort and governance fit in a scored matrix.

Interview tip: Show you are not biased. A balanced, criteria-based answer scores higher than "Databricks is better".

Q150. How do you plan disaster recovery for a Databricks workspace?

Answer: I keep a secondary workspace in another region and make it rebuildable from code, and I replicate the data separately. Databricks doesn't fail over a workspace automatically, so the plan covers infrastructure, metadata, data and the switch-over runbook.

How it works:

• Infrastructure: Terraform for workspaces, networking, Unity Catalog objects and grants.
• Code and jobs: Git plus bundles, so I can deploy everything to the secondary region quickly.
• Data: replicate critical Delta tables with DEEP CLONE on a schedule (incremental after the first run), or use storage replication with tested consistency.
• Secrets: Key Vault in both regions.
• Define RPO and RTO per data product. Not every table needs the same level.

Example: Nightly incremental clone of gold tables to the DR region.

CREATE OR REPLACE TABLE dr.gold.daily_sales
DEEP CLONE prod.gold.daily_sales;

How I prove it works: Twice a year, I run a DR drill: deploy to the secondary workspace, point a test dashboard at the DR tables and measure the real recovery time against the RTO.

Interview tip: Say that a DR plan that was never tested is not a plan. Interviewers listen for the drill.

12. PySpark vs Databricks vs Azure Data Factory: what interviewers expect

Short answer: PySpark is the Python API for Apache Spark, Databricks is the managed platform that runs Spark with Delta Lake and Unity Catalog, and Azure Data Factory orchestrates and copies data. Interviewers want to hear which tool does which job in a real pipeline.

A common real-world answer: "ADF copies source data into ADLS Gen2, a Databricks job written in PySpark cleans it into Delta tables, and Power BI reads the Gold layer." See our Azure Data Factory interview questions for the orchestration side.

13. How to prepare for a PySpark and Databricks interview in 7 days

Short answer: Practise coding every day, rebuild one Delta pipeline yourself and prepare three scenario stories from your own work.

1. Day 1: Revise basics, lazy evaluation and transformations vs actions (Q1–Q15).
2. Day 2: Practise DataFrame operations, joins and window functions in the free Databricks edition or a local Spark setup (Q16–Q42).
3. Day 3: Learn Spark architecture and read one job in the Spark UI (Q43–Q55).
4. Day 4: Fix a skewed join and a small-files problem on sample data (Q56–Q68).
5. Day 5: Solve all 17 coding questions without looking at the answers (Q69–Q85).
6. Day 6: Build a Bronze to Silver Delta pipeline with Auto Loader and MERGE (Q86–Q126).
7. Day 7: Practise the scenario answers aloud in 2 minutes each (Q127–Q150).

For hands-on practice, pick a few projects from our 90 Azure data engineer projects, and for the full Azure interview mix read our 200 Azure data engineer interview questions.

14. How Brolly Academy helps

• Databricks Course: practical modules on SQL, Spark, Delta Lake, Unity Catalog and data pipelines.
• PySpark Training in Hyderabad: hands-on DataFrames, Spark SQL and performance tuning.
• Azure Data Engineer Training in Hyderabad: ADF, Databricks, PySpark, SQL, Synapse and Fabric in 2–2.5 months, from ₹9,999.
• Mock interviews with PySpark coding rounds and Databricks scenario questions, with feedback on your answers.
• Classroom, online and hybrid batches in English and Telugu, with a free demo.
• Explore all courses at Brolly Academy.

Frequently asked questions

Sources and further reading

• Apache Spark — PySpark documentation
• Apache Spark — Spark SQL performance tuning
• Apache Spark — Cluster mode overview
• Microsoft Learn — Azure Databricks documentation
• Databricks — What is Delta Lake?
• Databricks — What is Unity Catalog?
• Databricks — What is Auto Loader?
• Delta Lake — Delta Lake documentation
• Wikipedia — Apache Spark