Databricks senior interview questions
Unity Catalog grants, MERGE file rewrites, OPTIMIZE, Photon fallbacks.
Question 1 of 117
Security asks: can PII leak through the control plane?
Answer it out loud, then reveal. Play steps through like the simulators.
All questions on this page
Indexed as FAQ. Open any item if you prefer a list to Play.
Showing 117 of 117 questions.
seniorSecurity asks: can PII leak through the control plane?
Databricks architecture · tap to open the answer
Say this first: Not as table files. Risk is notebooks, logs, collect(), and who can run compute that reads the lake.
Why: Files stay in the data plane. Notebook outputs, driver pulls, and mis-shared workspaces can still leak samples. Private link, cluster policies, and no collect() on PII are the controls. Unity Catalog governs names and grants; it is not a second copy of the files.
Senior depth: Commands down, status up, files never leave the data plane. Circle the driver in the customer VPC.
Common mistake: Answering only 'data never leaves our VPC' and stopping.
Follow-up they will ask: What would you put on the architecture diagram in an interview?
advancedWith serverless compute enabled, where does the Spark driver actually run, and what does that change for a security review?
Databricks architecture · tap to open the answer
Say this first: In a Databricks-managed account rather than your VPC, so the controls shift from your network rules to serverless connectivity settings and Unity Catalog credentials.
Remember these
- Serverless compute runs in the Databricks account, not your VPC
- Storage access goes through Unity Catalog credentials, not instance profiles
- Your VPC security groups no longer govern that traffic
- Network connectivity configuration plus private endpoints are the replacement
Why: Classic compute puts the driver and executors on VMs in your subnets, reaching storage with an instance profile or managed identity you attached. Serverless warehouses, jobs, and notebooks run that same Spark work on Databricks-managed compute, and it reaches your storage through Unity Catalog storage credentials rather than cluster-attached cloud identities. So the questionnaire line 'all compute runs in our VPC' stops being true the moment serverless is switched on, while 'table files stay in our bucket' remains true. The controls that replace your security groups are the serverless network connectivity configuration, private endpoints from serverless to your storage, and the fact that data access is only possible through Unity Catalog. Init scripts and instance profiles are not the extension points they were, which is itself part of the isolation story.
-- Which warehouses are serverless?
SELECT name, warehouse_type FROM system.compute.warehousesSenior depth: Serverless moves the execution boundary, not the data boundary. I would keep two documented models side by side — classic compute governed by our VPC, serverless governed by the connectivity configuration and Unity Catalog — and say which products use which. Then I would make the choice enforceable rather than cultural: restrict who can create serverless resources, require private connectivity to storage, and remove direct path access so Unity Catalog is the only route to the data on either model.
Common mistake: Repeating 'our compute never leaves our VPC' in a review after enabling serverless warehouses.
Follow-up they will ask: Which identity reads storage when a serverless warehouse queries a managed table?
advancedA workspace admin cannot create the metastore or add a new user and insists they already have admin rights. Explain what is happening.
Databricks architecture · tap to open the answer
Say this first: Metastores, identities, and credential configurations are account-level objects; workspace admin is a different scope from account admin.
Remember these
- Account console: metastore, identities, workspaces, credentials
- Workspace admin: compute, policies, jobs, secrets, object ACLs
- One metastore per region, assigned to workspaces afterwards
- Identity federation pushes account groups down into workspaces
Why: Databricks splits administration in two. The account console owns the Unity Catalog metastore, workspace provisioning, users, groups, service principals, and the network and credential configurations. Workspace admin owns compute, cluster policies, jobs, secrets, and workspace object ACLs. A metastore is created once per region in the account console and then assigned to workspaces, so no amount of workspace entitlement produces that button. With identity federation, groups are managed at the account level and assigned down into workspaces, which is why 'add this person' is also an account-level action. The fix is to involve an account admin or get that role, not to grant more workspace permissions.
Senior depth: The practical consequence is that the account is the real security boundary and the workspace is an operational one. I keep account admin small and audited, manage groups once at the account level through SCIM so a leaver is removed everywhere at once, and never let a team solve a permission problem by creating a new workspace. Anything account-level — metastore assignment, credentials, network config — goes through Terraform so the configuration is reviewable instead of clicked.
Common mistake: Hunting for a Create Metastore action inside Catalog Explorer in the workspace.
Follow-up they will ask: Where would you manage group membership when ten workspaces share one metastore?
seniorWhy would you run three workspaces instead of one workspace with three catalogs?
Databricks architecture · tap to open the answer
Say this first: A workspace is the blast radius for compute, secrets, and schedules; a catalog is the governance boundary for data — you split workspaces to isolate people and compute, not tables.
Remember these
- Catalogs separate data; workspaces separate compute, secrets, and admins
- One metastore can be shared by many workspaces in a region
- Catalog bindings stop a dev workspace writing a prod catalog
- More workspaces means more configuration to keep identical in code
Why: One metastore can serve many workspaces in a region, so separating data does not require separating workspaces: catalogs, grants, and catalog-to-workspace bindings already do that. What a separate workspace buys you is a separate set of cluster policies, secret scopes, job schedules, network configuration, and admins — the right boundary between production and development, or between a regulated business unit and everyone else. Bindings then make the isolation mutual: a prod catalog bound to prod workspaces cannot be written from a dev notebook even if someone over-grants. The cost is configuration drift, because every workspace now needs the same policies and permissions, which is why multi-workspace setups live or die on Terraform and Databricks Asset Bundles. Splitting per team without that discipline is how organisations end up with ten unmanaged workspaces and a legacy hive metastore in each.
Senior depth: I decide it by asking what I am trying to contain. If the answer is 'who can read this table', that is a catalog and a grant. If the answer is 'a dev cannot attach to compute that holds prod credentials, and a dev job cannot run on the prod schedule', that is a workspace. Then I bind prod catalogs to prod workspaces so the two boundaries reinforce each other, and define every workspace from the same Terraform module so the fourth workspace is not a snowflake.
Common mistake: Creating a workspace per team for data isolation that catalogs and grants already provide.
Follow-up they will ask: What makes a prod and dev split worth a second workspace rather than a second catalog?
architectCompliance requires that no cluster has a public IP and that traffic to object storage does not cross the internet. What are you configuring?
Databricks architecture · tap to open the answer
Say this first: Secure cluster connectivity for no public IPs, a customer-managed VPC with private subnets, private or gateway endpoints to storage, and private link to the control plane.
Remember these
- Secure cluster connectivity: no public IPs, outbound tunnel only
- Customer-managed VPC gives you subnets, routes, and security groups
- Private or gateway endpoints keep storage traffic off the internet
- Serverless is governed separately, not by your VPC rules
Why: With secure cluster connectivity the cluster VMs get no public IP address and no inbound ports; instead each instance opens an outbound tunnel to the control plane relay, so the control plane never initiates a connection into your network. Bringing your own VPC lets you own the subnets, route tables, and security groups the compute runs in. Storage traffic is kept off the internet with a gateway or private endpoint to the object store so it stays on the cloud backbone. Private link covers the other two hops: users to the workspace on the front end, and compute to the control plane on the back end. Serverless needs its own answer, because none of your VPC controls apply to it — that is a network connectivity configuration plus private connectivity from serverless to your storage. Customer-managed keys cover the encryption question that usually arrives in the same conversation.
Senior depth: I present it as four hops and ask which of them compliance cares about: user to workspace, control plane to compute, compute to storage, and compute to the internet. Secure cluster connectivity and private link close the first two, endpoints and the credential model close the third, and an egress policy closes the fourth. Then I state the serverless exception out loud rather than letting someone discover it in an audit, because the whole value of this diagram is that it is the answer we can defend in writing.
Common mistake: Assuming private link to the control plane also covers serverless compute automatically.
Follow-up they will ask: Which part of this design does nothing for a serverless SQL warehouse?
seniorReaders see partial data during a write. What's broken?
Delta Lake · tap to open the answer
Say this first: They are not reading through the Delta log — or the writer isn't Delta.
Why: Spark parquet. format on a Delta path, a broken symlink, or copying files in S3. DESCRIBE HISTORY should exist. If not, it's not a Delta table.
Senior depth: VACUUM deletes unreferenced files. Time travel older than retention dies. Never VACUUM 0 hours in prod.
Common mistake: Restarting the cluster to 'clear the partial write'.
Follow-up they will ask: When do you VACUUM and what's the footgun?
advancedA DELETE on a billion-row table finished in seconds and barely rewrote any files. What happened, and what does it mean for readers?
Delta Lake · tap to open the answer
Say this first: Deletion vectors recorded the removed rows as per-file bitmaps instead of a copy-on-write rewrite, and readers apply the vectors during the scan.
Remember these
- Deletes become per-file bitmaps instead of file rewrites
- Readers merge the vectors in, so scans do extra work
- OPTIMIZE materialises vectors back into clean files
- Enabling the feature raises the protocol version external readers must support
Why: With deletion vectors enabled, DELETE, UPDATE, and MERGE can mark rows as removed in a small side file rather than rewriting every Parquet file that contained a matched row, turning a large copy-on-write rewrite into a metadata-sized write. The cost moves to the read path: scans must merge the vectors in, so a table that accumulates many vectors reads more slowly until OPTIMIZE materialises the changes into clean files. Enabling the feature raises the table's reader and writer protocol requirements, so an external engine that does not understand deletion vectors can no longer read the table correctly — that is the compatibility decision you are making. DESCRIBE DETAIL shows the enabled table features, and OPTIMIZE is the maintenance that purges accumulated vectors.
ALTER TABLE ops.silver.orders
SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');Senior depth: Deletion vectors move cost from write time to read time, which is the right trade for update-heavy tables and the wrong one for a table that is written rarely and scanned constantly. So I enable them where MERGE rewrite amplification is the bottleneck, keep OPTIMIZE on a schedule so the read-side cost does not accumulate, and check the consumer list before flipping any protocol-raising feature. The question I always ask first is who reads this table outside Databricks, because protocol upgrades are not reversible in practice.
Common mistake: Enabling table features on a table that an older external reader still consumes, then being surprised by a protocol error.
Follow-up they will ask: Why do you still schedule OPTIMIZE on a table that uses deletion vectors?
seniorA downstream team wants row-level changes from a silver table and proposes diffing two snapshots. What do you offer instead?
Delta Lake · tap to open the answer
Say this first: Change Data Feed: enable delta.enableChangeDataFeed and read table_changes(), which returns inserted, updated, and deleted rows per version.
Remember these
- table_changes() returns _change_type, _commit_version, _commit_timestamp
- CDF distinguishes updates from delete-plus-insert; a diff cannot
- History before the enable commit is not available
- Change data lives in storage, so VACUUM and retention apply to it
Why: With CDF enabled, Delta records the change type alongside each commit, so table_changes('ops.silver.orders', 120) returns rows carrying _change_type, _commit_version, and _commit_timestamp. That is exactly the input a downstream MERGE wants: only the deltas, with deletes represented explicitly and updates visible as before and after images. A snapshot diff cannot match that — it costs a full scan of both versions, it cannot distinguish an update from a delete-plus-insert, and it silently collapses a row that changed twice inside the window. The constraints to state: CDF only covers commits from the version where you enabled it, and the change data is stored in the table's storage, so VACUUM and retention apply to it the same way they apply to time travel. Downstream consumers therefore need to keep up, or you need retention long enough to cover their lag.
ALTER TABLE ops.silver.orders
SET TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true');
SELECT * FROM table_changes('ops.silver.orders', 120, 125);Senior depth: CDF turns a pull-the-whole-table integration into an incremental contract, which is the architectural win: the downstream job's cost becomes proportional to change volume instead of table size. The discipline it demands is that consumers cannot fall behind retention, so I treat the consumer lag as a monitored SLA and size retention above it with margin. If a consumer genuinely needs an arbitrary historical window, that is a deep clone or a replay from bronze, not CDF.
Common mistake: Enabling CDF and expecting changes from before the enable commit to be readable.
Follow-up they will ask: What happens to change data after VACUUM runs?
architectA team wants to keep writing plain Parquet and add Delta later. Make the argument with mechanisms, not slogans.
Delta Lake · tap to open the answer
Say this first: Without the log there is no atomic commit, no schema enforcement, no isolated reader snapshot, and no file statistics — so every consumer re-implements correctness.
Remember these
- Parquet's state is a directory listing; Delta's state is a committed file list
- No log means no atomic commit, no conflict detection, no snapshot isolation
- Per-file min/max statistics in the commit are what enable data skipping
- CONVERT TO DELTA adds a log over existing Parquet without rewriting it
Why: A Parquet directory's state is whatever the object store happens to list, so a reader can see a half-finished job, a failed job leaves orphan files nobody will remove, and two writers to the same prefix have no conflict detection at all. The _delta_log makes the file list itself the transaction: readers resolve a version and see a consistent snapshot, writers commit atomically after a conflict check, and each commit carries per-file min and max statistics that drive data skipping. That same metadata is the foundation for MERGE, time travel, Change Data Feed, and OPTIMIZE, so 'add it later' means not having any of them while the dataset and the number of consumers grow. The migration cost argument is also weaker than people think: CONVERT TO DELTA writes a log over existing Parquet in place, without rewriting data. The honest framing is that Delta is not a competing file format — Delta data files are Parquet, plus a log that makes the directory a table.
CONVERT TO DELTA parquet.`s3://lake/orders`;
DESCRIBE HISTORY delta.`s3://lake/orders`;Senior depth: I make it a cost-of-correctness argument rather than a feature list. Every guarantee the log provides is one somebody has to reimplement in every reader and writer: partial-write visibility, retry semantics, schema drift, orphan cleanup, and statistics for pruning. That work is unpaid, inconsistent across teams, and only discovered during an incident. Since CONVERT TO DELTA is an in-place metadata operation, the real decision is not whether to pay the migration cost but how many ungoverned consumers you want to untangle before you do.
Common mistake: Framing Delta and Parquet as rival file formats when Delta's data files are Parquet.
Follow-up they will ask: What breaks if someone drops files into that directory after the conversion without going through Delta?
advancedA Trino cluster outside Databricks needs to read your UniForm tables. Walk me through how it finds the table and how it gets permission to read the files.
UniForm and Iceberg: one copy, two formats · tap to open the answer
Say this first: Unity Catalog exposes an Iceberg REST catalog endpoint; Trino loads the table through it and UC vends short-lived storage credentials scoped to that table's files.
Remember these
- UC acts as an Iceberg REST catalog for external engines
- Credential vending returns short-lived, table-scoped storage credentials
- Grants in UC remain the access decision, not a shared storage key
- USE CATALOG and USE SCHEMA are required alongside SELECT
Why: Trino is configured with the Iceberg REST catalog connector pointed at the Unity Catalog endpoint, authenticating as a principal UC knows — typically a service principal with a token. When Trino asks for a table, UC checks SELECT on that table and returns the current Iceberg metadata location. For the data files, UC performs credential vending: it hands back a temporary, scoped cloud credential instead of you pasting a long-lived storage key into the Trino config. That is the governance win — access is still decided by UC grants, and revoking SELECT stops the next request rather than requiring a key rotation. The two things that break this in practice are a principal with no USE CATALOG and USE SCHEMA, and network rules on the bucket that the vended credential cannot overcome.
-- Trino catalog properties
connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=https://<workspace>/api/2.1/unity-catalog/iceberg
iceberg.rest-catalog.security=OAUTH2Senior depth: The point of the REST catalog plus credential vending is that there is exactly one place where access is decided, and it is not the engine's config file. I would stand that up with a dedicated service principal per external engine, grants only on the specific schemas being published, and no direct path access to the bucket for those principals. Then I would watch system.access.audit for reads coming through the Iceberg endpoint, because the failure mode that actually bites is an engine team quietly falling back to a storage key when a grant is missing, and now your audit trail has a hole in it.
Common mistake: Giving the external engine a long-lived storage account key and calling it integration.
Follow-up they will ask: What happens to an in-flight Trino scan when you revoke SELECT from that principal?
advancedALTER TABLE ... SET TBLPROPERTIES to enable UniForm fails on an older table. What prerequisites are you checking, and what does enabling it change permanently?
UniForm and Iceberg: one copy, two formats · tap to open the answer
Say this first: UniForm requires column mapping and a minimum Delta protocol version, and enabling those upgrades the table protocol in a way you cannot cleanly undo.
Remember these
- Column mapping in name mode is required so Iceberg can use field IDs
- Enabling it raises reader and writer protocol versions permanently
- Older Delta clients can lose access after the protocol upgrade
- Some Delta features are restricted on a UniForm-enabled table
Why: Iceberg identifies columns by field ID, so the Delta table has to use column mapping in name mode before Iceberg metadata can describe it. Turning column mapping on raises the table's reader and writer protocol versions, and a protocol upgrade is recorded in the Delta log as a permanent property of the table. The consequence is that any older client — an external Spark pinned to a stale delta-core, a partner job on an old runtime — can stop being able to read or write the table at all. UniForm also restricts some Delta features on the table, because an Iceberg reader has to be able to interpret the same files. So the pre-flight check is: what else reads this table, on what runtime, and is there a feature on it that UniForm will not allow.
ALTER TABLE sales.orders SET TBLPROPERTIES(
'delta.columnMapping.mode' = 'name',
'delta.enableIcebergCompatV2' = 'true',
'delta.universalFormat.enabledFormats' = 'iceberg'
)Senior depth: I would treat it as a protocol migration, not a property change. First inventory the readers per table from lineage and audit, then enable UniForm on one low-traffic table and have every consumer re-run against it. Roll out in waves by consumer group rather than by schema, because the blast radius follows who reads the table, not where it lives. And I would say out loud in the design review that the protocol upgrade is one-way: the fallback plan is a fresh CTAS into a new table, not an ALTER that puts it back.
Common mistake: Treating the ALTER as a reversible toggle and running it across production tables in one script.
Follow-up they will ask: How would you roll this out across a hundred tables without surprising a downstream reader?
seniorThe Flink team wants to write into a UniForm table through the Iceberg catalog so both sides can append. What do you tell them?
UniForm and Iceberg: one copy, two formats · tap to open the answer
Say this first: Iceberg access to a UniForm table is read-only; Delta remains the single writer, so Flink writes to its own table and you ingest from it.
Remember these
- The Delta log is authoritative; Iceberg metadata is derived from it
- Iceberg clients read UniForm tables; they do not write them
- Two writers against two metadata layers would diverge silently
- Give each writer its own table and merge downstream
Why: UniForm publishes Iceberg metadata derived from the Delta log. The Delta log is the source of truth, and the Iceberg snapshot is a projection of it generated afterwards. An Iceberg writer committing its own snapshot would have no way to make the Delta log agree, so the two metadata layers would diverge and one set of readers would see files the other does not. That is why the Iceberg surface is for readers. The workable pattern is to give Flink its own table — its own Iceberg table, or a Delta table it writes through a supported path — and then merge that into the shared table from Databricks, so there is exactly one writer per table and the lineage shows it.
Senior depth: Single-writer-per-table is the rule I would not bend, because divergence between two metadata layers over shared files is the kind of corruption nobody detects for weeks. Practically: Flink writes its own bronze table, Databricks runs a streaming MERGE into the shared silver table, and the shared table is the one with UniForm on it. You lose a few seconds of latency and gain a lineage graph where every table has one owner. If the Flink team pushes back on the hop, I would rather spend the argument there than on reconciling two snapshot histories after the fact.
Common mistake: Promising full bidirectional interoperability because 'it is the same Parquet underneath'.
Follow-up they will ask: How would you design the handoff so the Flink stream still lands within a minute?
seniorA partner already publishes Iceberg tables in their own catalog and wants you to query them. Is that UniForm?
UniForm and Iceberg: one copy, two formats · tap to open the answer
Say this first: No — that is the opposite direction: a foreign Iceberg table or catalog federation, where Unity Catalog reads someone else's Iceberg metadata.
Remember these
- UniForm is outbound: your Delta tables readable as Iceberg
- Foreign Iceberg tables are inbound: you read their catalog
- The partner's catalog stays authoritative; you get read access
- Repeated hot joins may still justify a local materialization
Why: UniForm is for data you own and write as Delta that you want external engines to read as Iceberg. Reading a partner's Iceberg table is the inbound case: you register their catalog as a connection in Unity Catalog and the tables appear as foreign tables you can query with normal SQL. The data stays in their storage and their catalog remains authoritative, so you get reads, not ownership. The practical constraints are the ones you would expect from a federated read — write support is limited, performance depends on their file layout, and you are now coupled to their refresh cadence and their credentials. If you need to join it hot against your own gold tables repeatedly, materializing a local copy on a schedule is often the honest answer.
Senior depth: The distinction matters because the direction decides who is on the hook for an incident. With UniForm I own the write path, the layout, and the freshness SLA. With a foreign catalog the partner owns all three and I own only the complaint. So I would federate for discovery and ad hoc analysis, and materialize anything that sits under a dashboard SLA — with the materialization job visible in lineage so nobody mistakes the local copy for the live source.
Common mistake: Using 'UniForm' as a blanket word for any Delta-Iceberg interoperability, in either direction.
Follow-up they will ask: Where would governance live if half your gold layer is foreign tables?
architectYou are designing a lakehouse where Databricks owns ETL but Snowflake, Trino, and a Flink app all read the gold layer. How do you avoid three copies of the data?
UniForm and Iceberg: one copy, two formats · tap to open the answer
Say this first: One Delta gold layer with UniForm enabled, published through Unity Catalog's Iceberg REST catalog, with per-engine service principals and a documented freshness lag.
Remember these
- One write path in Delta; Iceberg metadata published for readers
- UC Iceberg REST catalog plus credential vending for every external engine
- Publish a narrow, contracted set of gold tables, not the whole catalog
- State the Iceberg freshness lag in the contract, do not leave it implied
Why: The physical design is one set of Parquet files written by Databricks, with Delta as the write format and Iceberg metadata published alongside. Unity Catalog is the single catalog: external engines come through the Iceberg REST endpoint with credential vending rather than storage keys, so grants, lineage, and audit cover every reader. The organizational design matters as much as the technical one: declare which tables are published, hold them to a schema contract, and state the Iceberg lag in the contract so nobody builds a reconciliation job on an assumption of exact parity. Publish a narrow set of gold tables rather than the whole catalog, because every published table is an interface you now have to keep stable. The copies you still accept are deliberate ones — a reader that needs sub-second latency or a format you do not publish.
GRANT USE CATALOG ON CATALOG prod TO `trino-reader`;
GRANT USE SCHEMA ON SCHEMA prod.gold TO `trino-reader`;
GRANT SELECT ON TABLE prod.gold.orders TO `trino-reader`Senior depth: The architecture question is not 'can these engines read Delta' — UniForm answers that. It is 'how many interfaces am I willing to support'. So I would publish a small, versioned gold surface, make UC the only access path, and refuse requests to expose silver. Then I would instrument it: audit reads per principal, watch which published tables nobody has queried in ninety days, and retire them. The failure mode of multi-engine lakehouses is not storage cost, it is a hundred implicit contracts you cannot change because you do not know who depends on them.
Common mistake: Enabling UniForm catalog-wide and calling it a data product strategy, with no contract or ownership.
Follow-up they will ask: Which engine would you still let keep its own copy, and why?
seniorRolling back a table worked on Tuesday; on Wednesday the same trick threw a file-not-found. What changed in between?
Time travel: reading the commit log backwards · tap to open the answer
Say this first: VACUUM physically deleted the data files those older versions point at, so the commits still exist but the Parquet does not.
Why: VACUUM removes files that the current version no longer references once they are older than delta.deletedFileRetentionDuration, and those unreferenced files are exactly what time travel reads. Commit history can outlive the data because delta.logRetentionDuration is a separate knob, so DESCRIBE HISTORY may still list a version you can no longer query.
Senior depth: Two independent dials. delta.deletedFileRetentionDuration decides how long VACUUM leaves unreferenced data files alone — restorability actually depends on that one. delta.logRetentionDuration decides how long the commit metadata survives. Set the file dial from the recovery requirement, accept that you pay storage for every superseded file for that long, and stop calling it a backup: a dropped table or a deleted storage prefix takes the log with it.
Common mistake: Saying 'time travel goes back 30 days' as if it were a fixed guarantee instead of a function of retention and VACUUM.
Follow-up they will ask: Which property do you raise if legal wants 60 days of restorability, and what does it cost?
architectA junior ran VACUUM sales RETAIN 0 HOURS to reclaim storage while the hourly MERGE was running. What is the blast radius?
Time travel: reading the commit log backwards · tap to open the answer
Say this first: In-flight readers and the concurrent writer can fail on missing files, and every earlier version becomes unreadable — that retention floor exists to protect concurrent operations.
Why: Delta refuses a retention below the configured threshold unless someone sets spark.databricks.delta.retentionDurationCheck.enabled to false, precisely because a query that resolved its file list minutes ago may still be reading files VACUUM just deleted. Once it lands, only the current version is guaranteed readable, so RESTORE and VERSION AS OF for anything earlier are gone, along with change-feed and streaming replays that needed those files.
Senior depth: The retention check is a guardrail, not a nuisance — anyone disabling it should be able to name the longest-running reader on that table. I would VACUUM on a schedule with retention at or above the longest read plus the recovery window, keep it out of the write window on hot tables, and get real durability from something VACUUM cannot touch: cloud storage versioning and periodic DEEP CLONE snapshots to a separate location.
Common mistake: Disabling the retention check as a habit because the error message was in the way.
Follow-up they will ask: How do you get the storage savings safely instead?
advancedA structured streaming job reads this table and you need to RESTORE it. What happens to the stream?
Time travel: reading the commit log backwards · tap to open the answer
Say this first: RESTORE commits a non-append data change, so the Delta streaming source fails unless you restart it with skipChangeCommits or ignoreChanges and an explicit starting version.
Remember these
- RESTORE removes and re-adds files, which is not an append
- skipChangeCommits ignores the commit; ignoreChanges re-emits rewritten files
- Either option requires an idempotent sink, normally a MERGE on a key
- Stop the stream, restore, then restart with an explicit startingVersion
Why: A Delta streaming source expects commits to add files it can deliver incrementally; a RESTORE commit removes and re-adds files, which is a data change the source cannot express as new rows. By default it errors out rather than silently emitting something wrong. skipChangeCommits ignores such commits entirely, and ignoreChanges lets rewritten files through as if they were new data — both change your semantics, so the sink must be idempotent, which in practice means a MERGE on a business key rather than an append. The safe operational sequence is to stop the stream, perform the restore, choose the restart position with startingVersion, and verify the sink deduplicates. Doing the restore first and discovering the stream's behaviour afterwards is how a rollback turns into a duplicate-row incident.
spark.readStream.format("delta")
.option("skipChangeCommits", "true")
.option("startingVersion", 941)
.table("ops.silver.orders")Senior depth: I plan a restore on a streamed table as a coordinated operation, not a SQL statement. Stop the consumers, restore, pick the restart version deliberately, and confirm each sink converges on re-delivery before resuming. The underlying requirement is idempotent sinks, because every incremental option here eventually re-delivers something — so I would rather pay for MERGE semantics downstream than rely on exactly-once behaviour that a single rollback breaks.
Common mistake: Restoring a source table mid-day and assuming downstream streams simply carry on.
Follow-up they will ask: What makes the sink safe to re-emit rows into?
advancedLegal wants a point-in-time copy of a 40 TB gold table kept for a year. Time travel or something else?
Time travel: reading the commit log backwards · tap to open the answer
Say this first: CLONE — shallow for a cheap short-lived snapshot, deep when the copy has to survive VACUUM and a possible DROP on the source.
Remember these
- Shallow clone references the source's files and dies when VACUUM removes them
- Deep clone copies files and survives source retention and DROP
- CLONE accepts VERSION AS OF or TIMESTAMP AS OF to pin the snapshot
- Deep clone can be re-run incrementally for recurring archives
Why: A shallow clone copies only metadata and points at the source table's existing data files, so it is near-instant and costs almost no storage — and it stops working the moment VACUUM on the source removes files the clone still references. A deep clone copies the data files into its own table and location, so it is independent of the source's retention, its VACUUM schedule, and a DROP. That independence is exactly what a one-year legal hold requires, which is why this is the moment to say out loud that time travel is not a backup. You can pin the snapshot precisely with VERSION AS OF or TIMESTAMP AS OF in the clone statement, and a deep clone can be re-run incrementally, copying only what changed, which makes it usable as a recurring archive rather than a one-off. The price is a second copy of the data and a lifecycle of its own.
CREATE TABLE finance.archive.orders_2026q3
DEEP CLONE finance.gold.orders VERSION AS OF 940;Senior depth: The distinction I insist on is retention versus durability. Time travel and shallow clones are retention features inside one table's storage, governed by VACUUM, and they vanish with the table. Deep clones, cross-account copies, and storage replication are durability, because they survive the failure modes that take the original with it. Legal holds and audit requirements are durability questions, so they get deep clones in a separate location with their own lifecycle policy, and I write the recovery horizon down rather than inferring it from a table property.
Common mistake: Taking a shallow clone for an archive and losing it the first time the source is vacuumed.
Follow-up they will ask: What is a shallow clone genuinely good for, then?
seniorPeople keep saying 'we have time travel, we don't need backups'. Dismantle it.
Time travel: reading the commit log backwards · tap to open the answer
Say this first: Time travel lives inside the same table in the same storage under the same credentials, so a DROP, a deleted container, or a region loss takes the history with it.
Remember these
- History is stored in the same prefix under the same credentials as the table
- DROP, VACUUM, and storage loss each end time travel
- Protection is bounded by a retention window you pay storage for
- Durability means a copy the same failure cannot reach
Why: The commit log and the superseded data files are objects in the same prefix as the live table, so every failure that affects the table affects its history identically. It protects you from one thing — a bad write — and only inside a retention window you are paying storage for. Drop a managed table and the log goes with it; run VACUUM and earlier versions stop being readable; lose the storage account and there was never a second copy. Real durability needs something the same failure cannot reach: deep clones in a different location or account, cloud storage versioning or cross-region replication, and the ability to replay from bronze. The framing that lands in an interview is that time travel is undo and a backup is a second copy, and the two are not substitutes.
Senior depth: I ask the question that settles it: name the failure you are protecting against, then say which mechanism covers it. Bad write inside the window is time travel. Bad write outside the window is a bronze replay. Dropped table is UNDROP if you are fast, otherwise a deep clone. Lost storage account or region is replication, and nothing in Delta helps. Once that table of failures and mechanisms exists, nobody argues that one feature covers all four, and the VACUUM schedule becomes a recovery decision rather than a storage-cleanup chore.
Common mistake: Quoting a retention number as an RPO without checking when VACUUM actually runs.
Follow-up they will ask: What is your RPO for a dropped managed table right now?
architectSet the retention policy for a 500-table lakehouse. What do you actually decide, and what does each choice cost?
Time travel: reading the commit log backwards · tap to open the answer
Say this first: Set delta.deletedFileRetentionDuration per tier from the recovery requirement, keep delta.logRetentionDuration at least as long, and make VACUUM a scheduled, audited job.
Remember these
- Restorability depends on file retention plus the VACUUM schedule
- Log retention only governs whether a version can still be named
- Default deleted-file retention is 7 days; raise it deliberately per tier
- Retention must exceed the longest-running reader on the table
Why: Restorability is a function of the data-file retention — which defaults to 7 days — and of when VACUUM last ran; the log retention only governs whether the commit metadata still exists to name a version, which is why shortening one without the other leaves you with a window narrower than you think. So the decision is per tier rather than global: gold tables with an audit obligation get a long file retention plus deep-clone snapshots, while high-churn bronze and streaming tables get a short one because every superseded file is billed storage until VACUUM removes it. VACUUM then has to actually run on a schedule, because an unrun VACUUM makes the retention setting theoretical and lets storage grow without bound. Keep the retention above your longest-running reader so VACUUM can never delete files out from under an executing query, and treat disabling the retention-duration check as an incident rather than a workaround. The deliverable is a written recovery horizon per tier, not a number someone remembers.
ALTER TABLE finance.gold.orders SET TBLPROPERTIES (
'delta.deletedFileRetentionDuration' = 'interval 30 days',
'delta.logRetentionDuration' = 'interval 30 days');Senior depth: I turn it into three tiers with explicit numbers and an owner, because a per-table conversation across 500 tables never finishes. Tier one is audited gold: long file retention plus periodic deep clones to a separate location. Tier two is operational silver and gold: retention sized to cover a weekend plus a replay window. Tier three is high-churn bronze and streaming sinks: short retention, because replay comes from the source rather than from versions. Then VACUUM runs as a governed job with its retention derived from the tier, and I audit for tables whose properties do not match their tier, so the policy is enforced rather than documented.
Common mistake: Raising retention uniformly across every table and discovering the storage bill on the high-churn streaming ones.
Follow-up they will ask: How do you tell whether VACUUM has ever run on a given table?
seniorHow do you share a gold table with another cloud account without copying files?
Unity Catalog · tap to open the answer
Say this first: Delta Sharing / UC shares — grants on a share, not a second lake.
Why: Recipient gets access through the sharing protocol. Your storage stays. Don't clone PII into their bucket 'to make BI easy'. Lineage should show the share.
Senior depth: The files. UC stores metadata and grants; the bytes stay in the owner’s storage unless you explicitly copy.
Common mistake: COPY INTO their account as the default share mechanism.
Follow-up they will ask: What must still live in the data plane?
advancedAn engineer left the company and now a nightly job fails on a table that nobody is able to grant access on. What is going on?
Unity Catalog · tap to open the answer
Say this first: That user's identity owned the table, and only the owner or an admin can GRANT, so ownership has to be reassigned — usually to a group.
Remember these
- Ownership, not ALL PRIVILEGES, confers the right to grant
- A metastore admin or parent owner can reassign ownership
- ALTER ... OWNER TO a group removes the single point of failure
- SHOW GRANTS shows privileges; Catalog Explorer shows the owner
Why: Every Unity Catalog securable has an owner, and ownership is not a privilege you can hand out piecemeal — it is what confers the right to GRANT, ALTER, and DROP that object. When the owner is a departed individual the object is effectively orphaned: colleagues with ALL PRIVILEGES still cannot delegate access. A metastore admin, or the owner of the parent catalog or schema, fixes it with ALTER TABLE ... OWNER TO, and Catalog Explorer shows the owner per object next to the grants. The durable answer is that production objects should be owned by a group or a service principal from the start so no leaver creates this situation. SHOW GRANTS lists the privileges, which is a separate question from who owns the thing.
ALTER SCHEMA finance.gold OWNER TO `data-platform-owners`;
SHOW GRANTS ON TABLE finance.gold.orders;Senior depth: I treat ownership as an operational risk register rather than a detail. Any object with a human owner in production is a future incident, so I audit owners across the metastore and reassign anything production-critical to a group or service principal. The groups come from the account level through identity federation, so a leaver loses access everywhere at once without orphaning the object. Then onboarding and offboarding stop touching Unity Catalog grants at all, which is the only version of this that survives staff turnover.
Common mistake: Granting themselves ALL PRIVILEGES and expecting that to include the ability to grant onward.
Follow-up they will ask: Why isn't ALL PRIVILEGES the same thing as ownership?
advancedAn auditor asks which dashboards consume PII from one specific silver table. How do you answer in ten minutes?
Unity Catalog · tap to open the answer
Say this first: Unity Catalog lineage for downstream tables, notebooks, and dashboards, plus the audit log for who actually read it.
Remember these
- Lineage is captured from queries on Unity Catalog-enabled compute
- Column-level lineage traces a PII column into specific gold columns
- system.access tables make lineage and audit queryable instead of clickable
- Direct path reads and non-UC compute are invisible to lineage
Why: Unity Catalog captures lineage automatically from queries run on Unity Catalog-enabled compute, so Catalog Explorer's Lineage tab gives upstream and downstream tables and the notebooks, jobs, and dashboards attached to them. Column-level lineage narrows it further, showing which gold column was derived from the PII column rather than just which table touched it. The same information is queryable in system tables, so for an audit you filter instead of clicking through a graph. Read access itself lands in the audit log, which is how you answer the second half of the question — which principals selected that table in a given window. The caveat you must state: work done outside Unity Catalog-enabled compute, or by reading the storage path directly, never appears in lineage, which is exactly why path bypass is a governance problem rather than a convenience.
SELECT target_table_full_name, entity_type
FROM system.access.table_lineage
WHERE source_table_full_name = 'finance.silver.customers'Senior depth: Lineage answers 'what is connected'; audit answers 'who actually looked'. An auditor almost always wants both, and the gap between them is the ungoverned path. So the real deliverable is not the lineage screenshot, it is closing the bypass: remove cluster-attached credentials, front every path with an external location, and require Unity Catalog access modes in policy. After that I tag PII columns so the next question is a single filter over system tables rather than a week of archaeology.
Common mistake: Promising complete lineage without mentioning that direct path reads leave no trace in it.
Follow-up they will ask: What would you tag so the next audit is a query rather than an investigation?
seniorLegal wants analysts to see only their own region's order rows and to see email addresses masked. Implement it without forty views.
Unity Catalog · tap to open the answer
Say this first: Attach a row filter and a column mask to the table with ALTER TABLE ... SET ROW FILTER and ALTER COLUMN ... SET MASK, so one table enforces it for every reader.
Remember these
- Row filters and column masks are SQL UDFs attached to the table
- The policy applies to every client, not just the view you handed out
- is_account_group_member() makes behaviour depend on the caller
- Owners and metastore admins need to be in the policy or they are the leak
Why: You write a SQL UDF that returns a boolean for the row filter and another that returns the masked value for the column, then attach them to the table itself. Every query through Unity Catalog applies them, whether it arrives from a notebook, a SQL warehouse, or a BI tool, because the policy lives on the securable rather than on a view someone has to be pointed at. The functions normally branch on the caller using is_account_group_member() or current_user(), so behaviour depends on identity instead of on which object was granted. This beats per-team views on two counts: one object to audit, and no way to query around the policy by reaching the base table. Make sure the policy covers privileged identities too, since the table owner and metastore admins are the obvious remaining leak.
ALTER TABLE finance.gold.orders
SET ROW FILTER finance.gold.region_filter ON (region);
ALTER TABLE finance.gold.orders
ALTER COLUMN email SET MASK finance.gold.mask_email;Senior depth: The design principle is that the policy belongs on the data, not on the access path, because access paths multiply and nobody re-audits them. Filters and masks give me one object with one rule and a lineage graph that still works, instead of a view sprawl where the base table grant is the real permission model. I test it by querying as each group, including the owner, and I keep the functions in version control with the table definition so a policy change is a reviewed diff rather than an ALTER someone ran once.
Common mistake: Building one view per region and trusting that nobody is ever granted the base table.
Follow-up they will ask: Who can still see the raw column, and how would you prove it?
architectDesign the catalog layout for four business domains, three environments, and a shared conformed layer.
Unity Catalog · tap to open the answer
Say this first: One metastore per region, a catalog per domain and environment, medallion layers as schemas, and catalog bindings so prod catalogs are not writable from dev workspaces.
Remember these
- Catalog per domain and environment, not one catalog with name prefixes
- Medallion layers as schemas so a layer can be granted at once
- Workspace bindings stop dev compute writing prod catalogs
- Per-catalog managed storage keeps domains' files separated
Why: The catalog is the natural unit of isolation because grants, managed storage locations, and workspace bindings all attach there, so sales_prod, sales_dev, finance_prod beats a single catalog separated by naming conventions. Layers become schemas, which keeps the three-level name readable and lets you grant a whole layer at once — GRANT SELECT ON SCHEMA sales_prod.gold. Binding prod catalogs to prod workspaces means a dev notebook cannot write production data even if someone over-grants, so the compute boundary and the data boundary reinforce each other. The shared conformed layer gets its own catalog owned by the platform group with read grants outward, and anything crossing a metastore or an organisation goes through Delta Sharing rather than a copy. Give each catalog its own managed storage location so one domain's files are not interleaved with another's container, and define the whole thing in Terraform so the fifth domain is not a hand-built exception.
GRANT USE CATALOG ON CATALOG sales_prod TO `analysts`;
GRANT SELECT ON SCHEMA sales_prod.gold TO `analysts`;Senior depth: I design it so that the common mistakes become impossible rather than discouraged. Environment separation at the catalog level plus workspace binding means a dev job physically cannot write prod. Layers as schemas means a grant is a sentence rather than a list of tables. Platform-owned conformed data means there is one place for shared keys instead of four domains cross-joining each other's silver. The thing I insist on is that this is code: catalogs, grants, bindings, and storage locations in Terraform, so the layout is reviewable and reproducible when the fifth domain arrives.
Common mistake: Putting everything in main and separating environments by table-name prefixes.
Follow-up they will ask: Where does the hive_metastore migration fit into that plan?
advancedAfter adding masks to twelve columns, dashboard latency roughly tripled. Explain the mechanism and how you would confirm it.
Row filters and column masks · tap to open the answer
Say this first: A mask is an expression evaluated per row per masked column, so twelve masks on a wide scan add twelve function calls to every row, visible in the query profile.
Remember these
- Each mask becomes a per-row expression on its column
- Twelve masks on a wide scan means twelve evaluations per row
- Query profile shows the cost in the project operator, not the scan
- Narrowing SELECT removes masks on columns nobody displays
Why: Unity Catalog rewrites the plan so each masked column becomes a function call over the underlying value. That is cheap for one simple CASE expression and expensive when you multiply it by twelve columns and hundreds of millions of rows, especially if a mask does string manipulation or calls something that cannot be evaluated natively. In the query profile you see time concentrated in the project operator rather than in the scan, and the row counts tell you the multiplier. The first fix is usually the query, not the policy: dashboards that select every column pay for masks on columns nobody displays, so narrowing the projection removes most of the cost. Then simplify the mask bodies themselves, keeping them to native SQL expressions so they can be evaluated efficiently rather than row-by-row in a slower path.
Senior depth: I treat masking as something with a measurable unit cost rather than a free governance checkbox, so I benchmark before and after on the real dashboard query, not on a SELECT of one column. Most of the time the answer is not to weaken the policy — it is that the dashboard was selecting forty columns to display six. Beyond that I keep mask bodies to plain SQL expressions, avoid lookups inside a mask, and for the genuinely hot path consider serving an aggregate where the sensitive column never appears. The governance conversation I want to avoid is one where performance pressure leads to dropping masks instead of fixing the query.
Common mistake: Blaming the warehouse size when the plan shows the added cost sitting in per-row expression evaluation.
Follow-up they will ask: Which of those twelve columns would you argue does not need a mask at all?
advancedThe nightly ETL service principal started writing an empty gold table the day a row filter went live on its source. Why?
Row filters and column masks · tap to open the answer
Say this first: The filter applies to the service principal too, and it is not in any of the groups the function checks, so every row evaluates to false.
Remember these
- Filters evaluate against the querying identity, including service principals
- An unmatched principal sees zero rows, with no error raised
- Downstream tables go silently empty rather than failing loudly
- Add an explicit pipeline-group branch and test as the service principal
Why: Row filters are evaluated against the querying identity, and a job running as a service principal is just another identity. If the function is written as is_account_group_member('emea_analysts') OR is_account_group_member('global_analysts') and the service principal belongs to neither, the predicate is false for every row and the read returns nothing. Nothing fails, which is the dangerous part: the MERGE runs, matches nothing, and the downstream table is empty or frozen. The fix is to decide deliberately whether pipelines are in scope for the policy. Usually they are not — the ETL needs the full grain and the restriction belongs on the serving tables — so you add a branch for a pipeline group, and you verify by running the read as the service principal rather than as yourself.
CREATE OR REPLACE FUNCTION sec.region_filter(region STRING) RETURN
is_account_group_member('etl_service')
OR (is_account_group_member('emea_analysts') AND region = 'EMEA')Senior depth: The pattern I insist on is that every policy function has an explicit branch for automated principals, written first, so a pipeline never falls through to a false predicate by accident. Alongside that, policy rollouts get a row-count check on the downstream tables for the first few runs, because a silent zero is worse than a failure — a failure pages someone. Longer term I would rather not apply row filters to the raw layer at all: pipelines read the full grain, and the restrictions live on the serving tables humans actually query.
Common mistake: Testing the filter only as a human analyst and never as the identity the pipelines run under.
Follow-up they will ask: How would you catch a silently empty load before the dashboard does?
seniorYou need the same PII masking on four thousand tables across twenty schemas. Attaching functions table by table is not viable. What do you propose?
Row filters and column masks · tap to open the answer
Say this first: Tag the sensitive columns and apply attribute-based policies against the tags, so the rule is defined once and follows the tag rather than the table name.
Remember these
- Per-table ALTER does not scale and silently misses new tables
- Tag the columns and write the policy against the tag
- New tables are protected as soon as their columns are tagged
- Audit tag coverage, because tagging becomes the real control
Why: Per-object ALTER statements do not scale because every new table is a new chance to forget, and nobody can answer 'is this rule applied everywhere' without a script. The attribute-based approach inverts it: columns carry governed tags such as a pii classification, and the policy says that columns with that tag are masked for anyone outside the compliance group. New tables inherit protection the moment their columns are tagged, and tagging can be driven by classification scanning rather than by hand. You still need the tagging to be right, which makes it the thing to audit — the information schema exposes tag assignments so you can report untagged columns whose names look sensitive. The per-table ALTER then stays for genuine exceptions rather than being the mechanism.
ALTER TABLE hr.people ALTER COLUMN national_id
SET TAGS ('data_classification' = 'pii_high');
SELECT * FROM system.information_schema.column_tags
WHERE tag_name = 'data_classification'Senior depth: At that scale the control is the tagging pipeline, not the policy syntax, so that is where I would put the engineering. Classification on ingest, a periodic scan that flags columns matching sensitive patterns without the tag, and a report of untagged candidates that goes to the data owner rather than to the platform team. The policy itself becomes a small number of rules that almost never change. The failure mode to design against is a confident dashboard saying one hundred percent of tagged columns are masked, while the real exposure is the columns nobody tagged.
Common mistake: Scripting four thousand ALTER statements and treating the one-time run as the end of the problem.
Follow-up they will ask: How would you find a sensitive column that nobody tagged?
seniorAn auditor asks you to prove that an analyst cannot see other regions' rows through any tool, including a BI client over JDBC. How do you answer?
Row filters and column masks · tap to open the answer
Say this first: Because enforcement is in Unity Catalog at the table, not in the client or in a view, the same filter applies to JDBC, ODBC, notebooks, and downstream views alike.
Remember these
- Enforcement lives in Unity Catalog, so every UC client inherits it
- The filter is applied in the plan before the user's own predicates
- Demonstrate by running as the analyst across each access path
- Direct storage path access bypasses it, so remove those grants
Why: The argument is about where the rule lives. A BI tool connecting over JDBC to a SQL warehouse still goes through Unity Catalog, and the row filter is injected into the logical plan before the query's own predicates are applied, so there is no ordering trick that reveals filtered rows. The same holds for a notebook on a UC-enabled cluster and for a view defined on top of the table, since the view reads the base table through the same path. To demonstrate it rather than assert it, run the same query as the analyst's identity across each access path and show identical row counts, then show the grants and the function definition. Add system.access.audit evidence that queries from that principal are recorded with the table accessed. The remaining hole to be honest about is anyone with direct storage path access to the underlying files, which is why those grants should not exist.
Senior depth: I answer audits with the mechanism and the exception list, because a claim with no exceptions is never believed and usually wrong. The mechanism is UC-level enforcement, which covers every client that goes through the catalog. The exceptions are direct storage access, non-UC legacy compute, and anyone who can edit the policy function — so the audit deliverable includes who holds those, and ideally that it is nobody outside a break-glass group. That framing turns the conversation from 'is it secure' into 'here are the three paths we control', which is the only version of the answer that survives scrutiny.
Common mistake: Claiming enforcement is universal while a legacy cluster or a storage credential still allows raw path reads.
Follow-up they will ask: Who in your workspace can still read the underlying files directly?
architectDesign the access model for a multi-tenant lakehouse where each customer's analysts may only see their own rows, in a single shared table.
Row filters and column masks · tap to open the answer
Say this first: One row filter driven by a governed mapping from principal to tenant, applied at the table, with pipeline identities explicitly scoped and the mapping itself audited.
Remember these
- One shared table with a row filter beats a table per tenant
- Resolve tenant from governed group membership, not hard-coded names
- Give pipeline identities an explicit branch and review it hardest
- Audit the mapping and the reads, because the mapping is the real control
Why: A table per tenant does not scale past a few dozen and makes every schema change a migration, so a shared table with a row filter is the right shape. The filter should not hard-code tenant names: it resolves the caller's tenant from a governed mapping — group membership per tenant, or a lookup the function consults — so onboarding a customer is a membership change rather than a code change. Pipeline and service identities get an explicit branch so the ETL reads the full grain, and that branch is the thing you review most carefully because it is the bypass. Sensitive columns get masks driven by tags rather than per-table ALTER, so the policy survives new tables. Finally, make the mapping auditable: who is in which tenant group, who changed it, and a periodic report from system.access.audit that no principal ever read rows outside its tenant.
CREATE FUNCTION sec.tenant_filter(tenant_id STRING) RETURN
is_account_group_member('platform_etl')
OR is_account_group_member(concat('tenant_', tenant_id));
ALTER TABLE app.events SET ROW FILTER sec.tenant_filter ON (tenant_id)Senior depth: The architecture is only as good as the weakest write to the mapping, so I design around that: tenant groups provisioned from the identity provider rather than created by hand, change review on the policy function, and a very small set of principals who can ALTER the table's filter. I would also insist on a detective control alongside the preventive one — a scheduled check that joins audit reads against the tenant mapping and alerts on anything inconsistent — because in multi-tenant systems the cost of a single wrong group membership is a disclosure incident, not a slow dashboard.
Common mistake: Building one view or one table per tenant, then discovering every schema change is a hundred migrations.
Follow-up they will ask: What is the single change that would expose one tenant's rows to another?
advancedYour cloud egress bill jumped after onboarding one recipient. Nothing about the table changed. Explain it.
Delta Sharing and Clean Rooms · tap to open the answer
Say this first: The recipient reads directly from your object storage, so their scans are your egress — and a cross-region or cross-cloud recipient pulling full tables pays that at your expense.
Remember these
- Recipient reads are egress from your bucket, billed to you
- Cross-region and cross-cloud recipients make that expensive
- Full-history rescans are the usual multiplier
- Fix by sharing a prunable or pre-aggregated table, or by incremental reads
Why: The no-copy property means the bytes move from your bucket to their compute, and your cloud provider charges you for that egress. A recipient in the same region and cloud is cheap; one in another region or another cloud is not, and a recipient that re-reads the full history on every run multiplies it. The usual cause is their job doing a full scan because they have no predicate on the partition or clustering columns, or because their orchestration refreshes from scratch nightly instead of reading the increment. Find it by correlating storage egress metrics with the recipient's read pattern in the audit records, then look at what their query is actually scanning. The fixes are cooperative: share a table laid out so their filters prune, share an aggregate instead of raw rows, or ask them to read incrementally through change data feed.
Senior depth: I price sharing as a product, because the cost is real and it lands on the provider by default. That means deciding deliberately what gets shared — a narrow aggregate rather than raw silver, laid out so the recipient's likely filters prune — and writing an expected read volume into the agreement. Then I monitor egress per recipient and treat a step change as a conversation, not a surprise. The alternative, where any recipient can rescan your full history nightly for free, is a cost line nobody owns until it is large enough to be an escalation.
Common mistake: Assuming no-copy means no cost, and forgetting that the provider's storage account is paying the egress.
Follow-up they will ask: How would you charge that egress back to the recipient's contract?
advancedA recipient wants to process only what changed since their last run rather than rescanning. What do you have to enable, and what does it cost you?
Delta Sharing and Clean Rooms · tap to open the answer
Say this first: Add the table to the share WITH HISTORY and enable change data feed, which lets the recipient read versions or changes — at the cost of retaining history you would otherwise vacuum.
Remember these
- ADD TABLE ... WITH HISTORY exposes previous versions to the recipient
- Change data feed lets them consume row-level changes incrementally
- Your VACUUM retention must cover their read window
- History sharing also lets them see pre-correction values
Why: By default a shared table gives the recipient the current snapshot. Sharing WITH HISTORY exposes previous versions, which is what lets their client read a range rather than the whole table, and with change data feed enabled on the table they can consume inserts, updates, and deletes directly. The cost is retention: the files backing those versions must still exist, so your VACUUM retention has to cover the window the recipient relies on, and that is extra storage. It also means the recipient can time travel across that window, which is a disclosure decision — they can see what a row looked like before you corrected it. Set the retention window deliberately and state it in the agreement, rather than discovering the mismatch when their incremental read fails because the files were vacuumed.
ALTER TABLE prod.gold.daily_revenue SET TBLPROPERTIES('delta.enableChangeDataFeed' = 'true');
ALTER SHARE partner_metrics ADD TABLE prod.gold.daily_revenue WITH HISTORYSenior depth: History sharing turns an internal implementation detail — how long I keep old files — into a contractual obligation, and that is the part teams miss. So I set the table's deleted-file retention explicitly to cover the recipient's agreed window plus a margin, document it, and monitor that the recipient's last-read version stays inside it. I also think about disclosure before enabling it: exposing history means exposing restatements, and there are tables where the previous value of a row is more sensitive than the current one. That decision belongs with the data owner, not with whoever ran the ALTER SHARE.
Common mistake: Enabling history sharing without raising retention, so the recipient's incremental reads break after a vacuum.
Follow-up they will ask: What retention window would you negotiate, and what does it cost in storage?
seniorCompliance asks for a monthly report of exactly which tables each recipient read and when. Where does that come from?
Delta Sharing and Clean Rooms · tap to open the answer
Say this first: The system.access audit tables record Delta Sharing activity on the provider side, including the recipient, the share, and the object accessed.
Remember these
- Provider-side system.access tables record sharing reads per recipient
- Open sharing shows the recipient, not the individual on their side
- Audit schemas must be enabled per metastore and do not backfill
- Persist the monthly report so it outlives audit retention
Why: Because every read goes through the sharing server for permission and URL issuance, the provider sees each access and it is recorded in the audit tables under the system.access schema. You can group by recipient and shared object to produce exactly the report compliance wants, and you can spot patterns like a recipient pulling the whole table nightly. The limits are worth stating: the audit records recipient-level activity, so with open sharing you see the recipient, not which human on their side ran the query, whereas Databricks-to-Databricks sharing carries more identity detail. Audit tables also need their schema enabled on the metastore and have no backfill, so if this is a contractual requirement, enable it before the first contract rather than after the first audit. Build the report as a scheduled query over the audit tables and keep its output, so you are not dependent on audit retention.
SELECT request_params.recipient_name, request_params.share, COUNT(*) AS reads
FROM system.access.audit
WHERE service_name = 'unityCatalog' AND action_name ILIKE '%deltaSharing%'
AND event_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALLSenior depth: I set this up before signing rather than after, because audit tables do not backfill and 'we will produce it when asked' quietly means 'we cannot produce last quarter'. Practically: enable the audit schema on the metastore, run a scheduled job that snapshots recipient activity into a retained reporting table, and reconcile it monthly against the active contracts. If a partner needs per-user attribution, that is an argument for Databricks-to-Databricks sharing or for per-team recipients rather than one shared credential, and it is cheaper to make that a contract term than a retrofit.
Common mistake: Promising per-end-user attribution for an open-sharing recipient, where you only see the recipient identity.
Follow-up they will ask: How would you get per-user attribution for a partner who insists on it?
seniorA partner wants to measure audience overlap with you, but neither side will hand over customer identifiers. Is Delta Sharing the answer?
Delta Sharing and Clean Rooms · tap to open the answer
Say this first: No — that is a clean room: approved code runs over both parties' inputs and only the agreed aggregate output comes back, with neither side reading the other's rows.
Remember these
- Sharing grants read access; a clean room grants only approved computation
- Both parties contribute inputs and approve the code that runs
- Output is limited to the agreed aggregate, not raw rows
- Aggregation floors are what stop re-identification of small cohorts
Why: Delta Sharing gives the recipient read access to rows you chose to share, which is exactly what both parties are refusing here. A clean room instead creates a collaboration environment where each side contributes tables, and only notebooks or queries both sides approve may run against them. The output is restricted to what was agreed — typically aggregates above a minimum count — so neither party can read the other's raw identifiers. The real design work is in the rules rather than the plumbing: what join key is permitted, what aggregation floor prevents re-identification of a small cohort, and who can approve a new piece of code. Treat it as a joint governance agreement with compute attached, not as a fancier share.
Senior depth: The question I push on is what the output is allowed to be, because that is where the privacy actually lives — the environment just enforces it. Hashed identifiers are not anonymisation; they are pseudonymisation, and with a shared salt they match perfectly, which is the whole point and also the whole risk. So I would insist on an aggregation floor, a fixed set of approved analyses rather than open query access, and a review step for any new code. That is slower than a share, and it is slower on purpose, because the thing being protected is the one asset neither company can get back after a mistake.
Common mistake: Proposing a share of hashed identifiers and calling it privacy-preserving, when a hashed join key is still a join key.
Follow-up they will ask: What minimum cohort size would you require on the output, and why?
architectYou are standing up data sharing as a product for forty external partners. What is the design, and what do you refuse to do?
Delta Sharing and Clean Rooms · tap to open the answer
Say this first: A narrow versioned set of shared gold tables, one recipient per partner with expiry, D2D where possible, and per-recipient audit and egress monitoring — and no sharing of silver or raw.
Remember these
- One share per data product, one recipient per partner, with expiry set
- Prefer D2D for identity-aware audit; rotate open-sharing credentials
- Never share silver or raw — every shared table becomes a frozen interface
- Monitor egress and reads per recipient, and retire unused shares quarterly
Why: The technical pattern is straightforward: a dedicated publishing schema of contracted gold tables, one share per product and one recipient per partner so revocation and audit are per-partner, with recipient expiration set and credentials rotated for open sharing. The decisions that matter are editorial. You refuse to share silver or raw, because every shared table is an interface whose schema you can no longer change freely, and raw grain is where accidental disclosure lives. You version the shared tables so a breaking change is a new table rather than a surprise, and you state freshness and history retention in the contract because both are now obligations. Operationally you need per-recipient egress and read monitoring, since the provider pays for recipient scans, and a quarterly review that retires shares nobody has read. Without that review, you accumulate forty permanent interfaces and lose the ability to evolve your gold layer.
ALTER RECIPIENT acme SET PROPERTIES ('expiration_time' = '2027-06-30T00:00:00Z');
SELECT * FROM system.information_schema.shares;
SHOW GRANTS ON SHARE partner_metricsSenior depth: The architectural cost of sharing is not storage or compute, it is schema freedom, and that bill arrives about a year in. So the design I defend is a small published surface with explicit versions and a deprecation process agreed up front: a new version is a new table, the old one has an announced end date, and recipient read activity tells you who still depends on it. I would also keep the publishing schema physically separate from the internal gold layer, even when the content overlaps, so internal evolution is never blocked by an external contract.
Common mistake: Sharing whatever each partner asks for, then being unable to change a gold column because forty contracts depend on it.
Follow-up they will ask: How would you deprecate a shared table that three partners still read?
advancedA SQL warehouse doubled in cost this week and nobody changed its size. Which system table tells you the query responsible?
System tables: who spent that? · tap to open the answer
Say this first: system.query.history has the statement text, duration, and bytes scanned, so you can rank the warehouse's queries by what they actually cost to run.
Remember these
- system.query.history stores statement text, user, duration, and bytes
- Rank one warehouse's statements to find the one that grew
- The query profile then explains why that statement is expensive
- Filter on the time column or the history table itself is a large scan
Why: Warehouse size explains the rate, not the cause. system.query.history records each statement against a SQL warehouse: the text, the user, the warehouse id, total_duration_ms, and the bytes read. Sorting a single warehouse by duration or bytes over the week usually surfaces one dashboard query or a SELECT * that started scanning far more after a table grew. Cross-check the query profile for that statement to see whether it was a cache miss, a join explosion, or a full scan. Billing usage confirms the warehouse's DBU total; query history tells you which statement to fix. Filter on the start time or you will scan the whole history table.
SELECT user_name, total_duration_ms, read_bytes, statement_text
FROM system.query.history
WHERE compute.warehouse_id = :id
AND start_time >= current_timestamp() - INTERVAL 7 DAYS
ORDER BY total_duration_ms DESC
LIMIT 20Senior depth: Unchanged SQL gets more expensive when the table underneath grows, loses clustering, or stops hitting the result cache because a non-deterministic function was added. Bytes scanned in query history is the column that distinguishes those from a warehouse that was simply busy.
Common mistake: Resizing the warehouse because the bill went up, without looking at which query changed.
Follow-up they will ask: The top query's text has not changed. What else could have made it slower?
advancedSomeone asks which downstream jobs break if you drop a column from a silver table. Which tables answer that?
System tables: who spent that? · tap to open the answer
Say this first: system.access.table_lineage and system.access.column_lineage record reads and writes observed between tables, so you can walk what depends on that column.
Remember these
- Lineage is recorded from observed reads, not from declared intent
- column_lineage names the source and target columns of each read
- A column with no recent downstream lineage is the safer drop
- Jobs that have not run, or that read from outside UC, are invisible
Why: Unity Catalog records lineage as queries actually run. table_lineage connects a source table to a target table for each observed read, with the workspace, the user, and the time. column_lineage goes one level deeper and names the source and target columns, which is what you need for a drop-column question. A column with no downstream lineage for the last 90 days is a much safer drop than one feeding three gold tables. The limit is that lineage only exists for workloads that ran through Unity Catalog after the schema was enabled, so a job that has not run recently, or that reads the table from outside Databricks, will not appear.
Senior depth: Lineage follows Spark and SQL plans that Unity Catalog can see. A collect() into pandas, a read through a raw cloud path, or a job that last ran before lineage was enabled all fall outside it, so an empty result is 'not observed', not 'unused'.
Common mistake: Treating an empty lineage result as proof that nothing depends on the column.
Follow-up they will ask: A notebook reads the column into a pandas DataFrame and writes a CSV. Does lineage catch it?
seniorYour cost query over system.billing.usage takes four minutes and scans tens of gigabytes. The table is not that conceptually big. What is going on?
System tables: who spent that? · tap to open the answer
Say this first: System tables grow without your layout choices, and a query that does not filter the usage date scans the full retained history.
Remember these
- You do not control layout or retention of system tables
- Filter on usage_date or the query reads the full retained history
- audit and query.history grow faster than billing usage
- Recent hours can be missing because ingestion lags
Why: You do not OPTIMIZE system tables and you do not choose their clustering. They are retained for a long window and appended continuously, so an unfiltered scan reads every workspace and every day the metastore has kept. usage_date and usage_start_time are the columns that prune. The same applies to system.access.audit and system.query.history, which get large faster than billing does. There is also ingestion lag: the last hour or two may not be there yet, so a query for 'right now' both scans a lot and still misses the newest rows. Materialise a daily aggregate of your own, filtered and tagged, if this query runs on a schedule.
SELECT usage_date, sku_name, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - 14
GROUP BY usage_date, sku_nameSenior depth: Incrementally merge the last few usage_date partitions into your own gold table. That table you can cluster, tag, and retain on your own terms, and the system table goes back to being a source rather than a dashboard.
Common mistake: Writing SELECT * FROM system.billing.usage and blaming Databricks for the scan.
Follow-up they will ask: How would you keep a daily cost mart fresh without re-scanning history?
seniorSecurity wants to know who granted a contractor access to a PII table last Tuesday. Which table, and what is the catch?
System tables: who spent that? · tap to open the answer
Say this first: system.access.audit records the grant action, the principal who made it, and the object — if the audit schema was enabled before Tuesday.
Remember these
- system.access.audit records who did what, including grants
- The schema has to have been enabled before the event
- Filter on event date and action or the scan is huge
- Recipient reads of a share are audited here as well
Why: system.access.audit is the account's audit log as a table: logins, permission changes, share access, and job actions, with the user, the request, and the target. A GRANT shows up as an action name on the table's securable, with the identity that performed it. The catch is the same as billing: the schema must have been enabled at the time, and audit rows arrive with a delay. It is also a wide, chatty table, so filter on event_date and the action name before you scan it. For shared data, recipient reads are audited too, which is the record a file drop never gives you.
Senior depth: The identity on the audit row is the answer. A human email means someone clicked. A service principal application id means a pipeline did it, which is what you want in production and what you should alert on when it appears somewhere unexpected.
Common mistake: Expecting the audit table to reconstruct grants made before the schema was enabled.
Follow-up they will ask: How do you tell a grant made in the UI from one made by a CI service principal?
architectYou are rolling Databricks out to twelve business units. What has to be true on day one so the bill is attributable in a year?
System tables: who spent that? · tap to open the answer
Say this first: A required team tag enforced by cluster policy and budget policy, every system schema enabled, and a owned cost mart — because none of this can be reconstructed later.
Remember these
- Policies must reject compute that has no team tag
- System schemas start collecting only once enabled, with no backfill
- Aggregate into a table you own so retention is your decision
- A tag taxonomy with one owner is what makes the numbers mean anything
Why: Attribution is a decision you make before the spend, not a query you write after it. Cluster policies and budget policies that reject untagged compute close the UI path. Asset bundles set the tag in code so jobs cannot drift. Enabling billing, access, query, and lakeflow schemas on each metastore starts the clock, and waiting a quarter means that quarter is unauditable. A small daily job that aggregates usage by tag into a table you own survives retention limits and gives finance a stable contract. The organisational part is harder than the SQL: someone has to own the tag taxonomy, and a tag that means three different things across units is as useless as a null.
Senior depth: Put the allowed values in the cluster policy itself so an unknown team name fails creation. A wiki list and a policy that only checks the tag is non-empty will be full of typos within a month, and typos split one team's spend across spellings.
Common mistake: Leaving tagging as a convention in a wiki and planning to clean it up at year end.
Follow-up they will ask: Who is allowed to create a new tag value, and where is that list enforced?
seniorHow do you handle a late-arriving correction in medallion?
Medallion Architecture · tap to open the answer
Say this first: Land it in bronze, merge into silver on the business key, rebuild gold from silver — never patch gold by hand.
Why: If you edit gold, you can't reconstruct yesterday. MERGE in silver with event time. Gold is a projection.
Senior depth: Drop silver+gold, rebuild from bronze, numbers match. If they don't, gold had hidden logic.
Common mistake: UPDATE gold.daily_revenue in a notebook because finance asked.
Follow-up they will ask: What's the replay test?
advancedRerunning yesterday's job doubled the gold numbers. Make the pipeline idempotent, layer by layer.
Medallion Architecture · tap to open the answer
Say this first: Bronze keyed on the source file, silver MERGE on the business key, gold rewritten with a bounded overwrite of the window rather than appended.
Remember these
- Bronze idempotence comes from the source file identity
- Silver idempotence comes from MERGE on the business key
- Gold idempotence comes from replaceWhere or INSERT OVERWRITE, never append
- Pass the window as a parameter instead of calling current_date() inside the job
Why: Idempotence means rerunning a window converges on the same state instead of adding to it, and each layer needs a different mechanism to get there. Bronze: ingest by file with Auto Loader's checkpoint, or a MERGE keyed on _metadata.file_path, so the same file cannot land twice. Silver: MERGE on the business key with an event-time or version tiebreaker, so a replayed row updates rather than inserts. Gold: never append — use INSERT OVERWRITE or a replaceWhere overwrite scoped to exactly the window being recomputed, so a rerun replaces the slice it owns and touches nothing else. The last piece is where the window comes from: pass it in as a job parameter, because a notebook calling current_date() recomputes the wrong day the moment a retry crosses midnight. Each layer being individually idempotent is what makes the whole pipeline safe to rerun.
df.write.format("delta").mode("overwrite")
.option("replaceWhere", "event_date = '2026-10-23'")
.saveAsTable("ops.gold.daily_revenue")Senior depth: I treat 'can I rerun this window safely' as an acceptance criterion for every pipeline, because retries and backfills are not exceptional events. That means each layer's write is a converging operation and the window is an explicit input, which also makes the pipeline testable: run the same window twice in staging and assert the row counts are identical. The failure I guard against hardest is gold appends, because they are the one that produces plausible-looking wrong numbers instead of an error.
Common mistake: Making silver idempotent and leaving gold as an append, so every backfill doubles the metric.
Follow-up they will ask: What breaks if the job derives its own date from now()?
advancedThe source added a field and renamed another. How does that ripple through bronze, silver, and gold, and where should the job fail?
Medallion Architecture · tap to open the answer
Say this first: Bronze absorbs it with schema evolution and rescued data, silver fails or quarantines because it holds a contract, and gold should never see it.
Remember these
- Bronze evolves: add new columns, rescue unexpected ones
- Silver enforces: fail or quarantine instead of defaulting to null
- Gold is a projection, so a held line upstream leaves it untouched
- Stale numbers are a better failure than confident zeros
Why: Bronze's job is to lose nothing, so evolution belongs there: a new column appends to the schema and unexpected fields land in a rescued-data column rather than being silently dropped. Silver is where the contract lives, and the rename means the column silver reads is now null — a correctness bug that must fail loudly or route the rows to a quarantine table with a reason, not pass through with defaults. If silver holds the line, gold is a projection of silver and is unaffected, so dashboards show yesterday's numbers rather than a confident zero, which is the far better failure. The observable surfaces are a row-count or null-rate expectation on the silver load, and DESCRIBE HISTORY on bronze showing the schema-change commit next to the upstream release date. The anti-pattern to name is coalescing the missing column to a default so the pipeline stays green while the metric quietly goes to zero.
spark.readStream.format("cloudFiles")
.option("cloudFiles.format", "json")
.option("cloudFiles.schemaEvolutionMode", "rescue")
.load("abfss://lake@acct.dfs.core.windows.net/raw/orders")Senior depth: I design each layer's reaction to drift explicitly, because schema change is routine and the only question is who absorbs it. Bronze is permissive and lossless, silver is strict and loud, gold is derived and therefore immune. Then the pipeline needs one thing more to be operable: an expectation on the silver load — null rate on contract columns, row count against the previous run — so the alert arrives before a consumer notices. A green pipeline producing zeros is strictly worse than a red one, and that is the principle I design the failure modes around.
Common mistake: Coalescing the renamed column to a default so the pipeline stays green and the metric silently drops to zero.
Follow-up they will ask: What would you alert on so this is caught on the first run rather than in a dashboard?
seniorA team proposes a fourth layer called platinum for ML features. Yes or no, and why?
Medallion Architecture · tap to open the answer
Say this first: Only if it has a named consumer and a genuinely different contract; otherwise it is a gold table and the extra layer is one more copy to keep in sync.
Remember these
- A layer needs a different contract, not just another transformation
- ML features do have one: point-in-time correctness and a serving path
- A layer created because gold got messy is a modelling problem in disguise
- Every extra layer is another copy to backfill when logic changes
Why: Layers are justified by a change in contract — fidelity in bronze, correctness in silver, consumer grain in gold — not by the number of transformations between them. ML features often do have a distinct contract: point-in-time correctness so training sees no future information, a serving path with latency requirements, and a schema owned by the model rather than by a dashboard. That is a legitimate reason to model them separately, usually as feature tables in their own schema with their own lineage. What does not justify a layer is that gold became messy, because that is a modelling problem and a fourth copy makes it worse by adding another thing to backfill whenever the logic changes. The three questions I ask of any proposed layer are who owns it, what its SLA is, and what breaks if we delete it.
Senior depth: I let the contract decide, and I make the proposer state it. If they can describe a guarantee gold does not provide — no leakage from the future, a sub-second serving read, a schema versioned with the model — then it is a real layer and I would rather it be explicit than smuggled into gold. If they cannot, the honest answer is that gold needs restructuring and a new name will not fix it. Layer count is not architecture; the number of contracts you can actually enforce is.
Common mistake: Adding a layer per team so each new consumer creates another copy of the same numbers.
Follow-up they will ask: What point-in-time requirement makes a feature table different from a gold aggregate?
architectMap medallion onto Unity Catalog and compute for a platform with twelve pipelines. What do you standardise?
Medallion Architecture · tap to open the answer
Say this first: Layers as schemas inside a domain catalog, write access per layer limited to the owning service principal, and different retention, quality, and compute per layer.
Remember these
- Layers as schemas so the layer is grantable and visible in lineage
- MODIFY on silver and gold only for the pipeline's service principal
- Humans read gold; nobody reads bronze directly
- Masks and filters at silver and gold; bronze is locked down and expired
Why: Make the layer part of the name — sales_prod.bronze.orders_raw, sales_prod.silver.orders, sales_prod.gold.daily_revenue — so a grant can be made on a whole layer and the lineage graph reads like the architecture diagram. Only the pipeline's service principal gets MODIFY on silver and gold; humans get SELECT on gold and nothing on bronze, which eliminates the whole class of incident where someone patched a gold table by hand because finance asked. Retention and PII treatment differ by layer, so masks and row filters attach at silver and gold while bronze stays locked down and expires on a schedule. Compute differs too: job or serverless compute for the pipelines, SQL warehouses for gold consumption, and nobody scheduling production work on all-purpose. The last piece is reconciliation — a row-count or revenue check between silver and gold per run — because three named layers are only a contract if something verifies it.
GRANT SELECT ON SCHEMA sales_prod.gold TO `analysts`;
GRANT MODIFY ON SCHEMA sales_prod.silver TO `sp-sales-pipeline`;Senior depth: The architecture is only real if the permission model enforces it, so I map each layer to a securable and each pipeline to a service principal, then grant narrowly. Once humans have no MODIFY on gold, the hand-patched-metric incident cannot happen; once bronze is read only by the silver pipeline, nobody builds a dashboard on raw JSON. Compute follows the same logic: pipelines on job or serverless compute, consumption on warehouses, all-purpose for humans only. Then I add the reconciliation check per run, because a contract nobody verifies drifts within a quarter.
Common mistake: Implementing layers as folder prefixes in one schema, so grants and lineage cannot tell them apart.
Follow-up they will ask: Who should be allowed to write to bronze, and with what identity?
seniorConcurrent MERGEs fail with ConcurrentAppendException. What's the design change?
Delta MERGE · tap to open the answer
Say this first: Isolate partitions / rows so commits don't conflict, or serialize writers.
Why: Two jobs rewriting the same files. Partition the table by the merge boundary (date). One writer per partition. Retry is OK; 50 retries is a design bug.
Senior depth: When you replace a whole partition from a correct snapshot — cheaper than matching every key.
Common mistake: Turning off optimistic concurrency.
Follow-up they will ask: When is a replaceWhere overwrite better than MERGE?
advancedYour MERGE joins on t.id = s.id and rewrites about 2 TB a night. A colleague adds AND t.event_date >= current_date() - INTERVAL 3 DAYS to the ON clause and it drops to 60 GB. Explain the mechanism — and the risk.
Delta MERGE · tap to open the answer
Say this first: The extra predicate lets Delta refute most target files from their min/max statistics before the join, so far fewer files are read and rewritten.
Remember these
- Phase one of MERGE prunes candidate files from log statistics
- An equality on a high-cardinality key prunes nothing
- A range predicate on the clustering key is refutable, so files drop out
- Too narrow a window turns late updates into duplicate inserts
Why: MERGE runs in two phases: find the target files that could contain a matching row, then rewrite those files with the merged result. The first phase pushes the ON clause down against the per-file statistics in the transaction log. An equality on a high-cardinality id proves nothing — any file could hold that id — so every file becomes a candidate. A range predicate on the partition or clustering column is refutable, so files whose event_date range falls outside the window are eliminated without being opened, and the rewrite shrinks with them. You can watch the effect in numTargetFilesRemoved in the commit metrics and in the scan's file-pruning counters in the query profile. The risk is correctness: a change that arrives for a row older than three days now matches nothing, so WHEN NOT MATCHED either skips it or inserts a duplicate key. Only narrow the ON clause to a window you can prove bounds late arrivals, and keep a wider periodic reconciliation.
MERGE INTO ops.silver.orders t
USING changes s
ON t.id = s.id
AND t.event_date >= current_date() - INTERVAL 3 DAYS
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *Senior depth: The ON clause is the only part of a MERGE that can eliminate files, so anything you want pruning on belongs there. I would cluster or partition on the window column, keep the window at the upstream's measured p99 lateness plus margin, and add a weekly full-key reconciliation MERGE so a straggler cannot live as a duplicate forever. The number I would bring to the review is numTargetFilesRemoved per run before and after, because that is what the DBUs track.
Common mistake: Putting the date filter in a WHEN MATCHED AND ... clause instead of the ON clause, which filters rows after the files were already read.
Follow-up they will ask: How would you prove three days is enough for this source?
advancedDesign SCD Type 2 on a Delta dimension using MERGE. Why can't a single MERGE statement do it?
Delta MERGE · tap to open the answer
Say this first: A matched row can only take one action per MERGE, and SCD2 needs two — close the current version and insert the new one — so you stage both or write twice.
Remember these
- One action per matched row is the constraint that forces the pattern
- Stage the changed key twice: real key expires, null key inserts
- Compare a row hash so unchanged keys cause no rewrite
- Two separate statements mean two commits and a gap on failure
Why: SCD Type 2 keeps history by expiring the current row and appending a new one, which is an UPDATE and an INSERT against the same business key. MERGE allows one action per matched target row, so the classic shape stages a source where a changed key appears twice: once with the real key so it matches and expires the current row, and once with a null merge key so it falls to NOT MATCHED and inserts the new version. Change detection is a row hash over the tracked attributes, so unchanged rows take no action and you do not churn files. The alternative is two statements in one job — a MERGE that sets is_current = false and valid_to, then an INSERT of the new versions — which is easier to read but is two commits, so a failure between them leaves a key with no current row. Declarative pipelines offer APPLY CHANGES ... STORED AS SCD TYPE 2 if you would rather not own this at all.
MERGE INTO dim_customer t
USING staged s ON t.customer_id = s.merge_key AND t.is_current
WHEN MATCHED AND t.row_hash <> s.row_hash THEN
UPDATE SET t.is_current = false, t.valid_to = s.effective_ts
WHEN NOT MATCHED THEN INSERT *Senior depth: The interesting part is not the SQL, it is the invariant: exactly one current row per key, no gaps and no overlaps in validity. I would assert that invariant as a scheduled check rather than trusting the merge, because the failure mode is silent fan-out in every downstream join. If the team does not have an appetite to own that, APPLY CHANGES with STORED AS SCD TYPE 2 maintains the start and end columns for you and handles out-of-order rows by the sequence column, which is a fair trade of control for correctness.
Common mistake: Matching on the business key without AND t.is_current, so the MERGE tries to modify every historical version of that key and errors on multiple matches.
Follow-up they will ask: Where does the clustering key go on an SCD2 dimension that is queried both by key and by validity date?
seniorA nightly sync uses WHEN NOT MATCHED BY SOURCE THEN DELETE against a vendor's full export. This morning 60% of the table is gone. What happened and what do you change?
Delta MERGE · tap to open the answer
Say this first: The export was partial, and NOT MATCHED BY SOURCE means 'delete every target row the source did not send' — the MERGE did exactly what it was told, atomically.
Remember these
- NOT MATCHED BY SOURCE makes source completeness a correctness dependency
- The condition on that clause may only reference target columns
- RESTORE TABLE recovers it while the files are inside retention
- Gate the job on a source row-count delta before the MERGE runs
Why: NOT MATCHED BY SOURCE turns a MERGE into a full-snapshot reconciliation, so the source's completeness becomes a hard correctness dependency. A truncated file, a partial upload still in flight, or a vendor filtering their export to one region all look identical to the statement: those keys are absent, so they are deleted, in one commit that readers see all at once. Recovery is the easy part — DESCRIBE HISTORY gives you the version before the MERGE and RESTORE TABLE puts it back, since the data files are still within the deleted-file retention window. The fix is a precondition, not a better MERGE: count the source and compare it against the previous run's count, and abort the job when it moves by more than a tolerance. After that, scope the delete clause with a target-side condition such as last_seen older than a week so a one-night gap cannot clear live rows, or switch to a soft delete flag and let a separate job physically remove rows later.
WHEN NOT MATCHED BY SOURCE
AND t.last_seen < current_date() - INTERVAL 7 DAYS
THEN DELETESenior depth: Any statement that can delete rows because data is absent needs a completeness check in front of it. I would land the export in bronze first, assert its row count and file manifest against the prior run, and only then run the reconciliation MERGE from bronze — which also gives you the raw file to argue with the vendor about. Then make the delete recoverable by default: soft-delete with a flag, physically purge on a lagging schedule, and keep deletedFileRetentionDuration long enough that RESTORE is a real option rather than a hope.
Common mistake: Treating the blast radius as a Delta problem and asking for backups, when the missing control is a row-count guard on the incoming export.
Follow-up they will ask: How would you detect this within minutes instead of at the next morning's standup?
seniorOPTIMIZE ran 3 hours and the next query was unchanged. Why?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Filters don't match clustered columns, or the reader bypassed Delta, or stats weren't used.
Why: Check PartitionFilters / data skipping in the scan. If you Z-ORDER columns you never filter, you paid a rewrite for nothing. Photon/warehouse still needs a selective predicate.
Senior depth: Better reads vs extra DBUs and longer MERGE (more rewrite). Measure scan bytes before/after, not just 'we optimized'.
Common mistake: Running OPTIMIZE daily on a table nobody filters.
Follow-up they will ask: What's the write-amplification tradeoff?
advancedWhat decides the file size OPTIMIZE aims for, and when would you deliberately ask for smaller files?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Databricks tunes the bin-packing target automatically; you can pin it with delta.targetFileSize, and smaller files are the right call on a MERGE-heavy table.
Remember these
- delta.targetFileSize overrides the auto-tuned bin-packing target
- delta.tuneFileSizesForRewrites favours sizes that keep MERGE cheap
- Copy-on-write makes a large file a large rewrite for one row
- Append-heavy scan tables want the opposite of upsert-heavy tables
Why: OPTIMIZE bin-packs small files toward a target size rather than producing one giant file, and on Databricks that target is chosen for you based on the table's size unless you override it. delta.targetFileSize pins it explicitly, and delta.tuneFileSizesForRewrites tells Delta to treat the table as rewrite-heavy and favour sizes that keep MERGE cheap. The tension is between read and write: large files mean fewer tasks and less listing for a full scan, but copy-on-write means changing one row rewrites a whole file, so a 1 GB file is 1 GB of rewrite for a single update. On a silver table that takes hourly upserts, smaller files cut the rewrite amplification you measured in numTargetFilesRemoved, at the cost of more tasks per scan. On an append-only gold table that BI scans end to end, go the other way. Decide from the dominant access pattern and then verify with the MERGE commit metrics and the scan's task count, not from a number someone quoted in a blog post.
ALTER TABLE ops.silver.orders SET TBLPROPERTIES (
'delta.targetFileSize' = '128mb',
'delta.tuneFileSizesForRewrites' = 'true'
)Senior depth: File size is a read/write trade, so I pick it from whichever side dominates the bill. I would measure both ends before touching it: bytes scanned and task count per typical query, and numTargetFilesRemoved per MERGE. If the table is both heavily scanned and heavily merged, that is usually a modelling signal — the mutable grain and the analytical grain want to be different tables — rather than a file-size tuning problem.
Common mistake: Pushing the target file size up to speed up scans on a table whose real cost is an hourly MERGE, which then rewrites more bytes per run.
Follow-up they will ask: Which metric would you watch for a week after changing it?
advancedYou enabled deletion vectors. DELETEs and MERGEs got much cheaper, then the morning dashboards got slower. Explain both halves.
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Deletion vectors make deletes merge-on-read — the write only marks rows, and readers pay to apply those marks until a rewrite materializes them.
Remember these
- Vectors mark deleted row positions instead of rewriting files
- Readers apply the vectors, so the cost shifts to every scan
- OPTIMIZE materializes vectors during compaction
- REORG TABLE ... APPLY (PURGE) is what makes bytes actually go away
Why: With delta.enableDeletionVectors set, a DELETE, UPDATE or MERGE can record which row positions are no longer valid in a side file instead of rewriting the Parquet, which is why the write collapses from minutes to seconds. The cost moves to the read path: every scan must apply the vectors, and a file whose rows are 90% deleted is still read in full because the bytes are still there. As vectors accumulate over days, the effective rows per byte scanned drops and dashboards slow down. OPTIMIZE materializes them as part of compaction, so a maintenance schedule is no longer optional on a table with deletion vectors. When you need the deleted bytes physically gone — a GDPR erasure, for instance — REORG TABLE ... APPLY (PURGE) forces the rewrite so a later VACUUM can remove the old files. Note the reader requirement too: deletion vectors are a table feature, so clients too old to understand it cannot read the table correctly.
ALTER TABLE ops.silver.orders
SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');
REORG TABLE ops.silver.orders APPLY (PURGE);
VACUUM ops.silver.orders;Senior depth: Deletion vectors move cost from writers to readers, which is usually the right direction for a table with frequent small deletes and a maintenance window. The two things I insist on before enabling them are a real OPTIMIZE schedule, because unmaterialized vectors degrade every reader, and a documented purge path for erasure requests, because 'deleted' now means two different things depending on whether you are a query or a regulator. I would also check that every consumer of the table — external engines especially — supports the table feature before turning it on.
Common mistake: Telling an auditor the rows are deleted after a DELETE with deletion vectors on, when the bytes are still in the Parquet until a purge and a vacuum.
Follow-up they will ask: How would you tell from the table that vectors have been accumulating?
seniorPredictive optimization is enabled on your Unity Catalog managed tables. Do you delete the scheduled OPTIMIZE jobs?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Only for the managed tables it covers — external and legacy-metastore tables still need your job, and SLA-critical tables still need deterministic timing.
Remember these
- Coverage is Unity Catalog managed tables, not external or legacy ones
- DESCRIBE HISTORY proves whether maintenance actually ran
- Keep explicit OPTIMIZE where timing is part of an SLA
- Maintenance cost moves to its own usage line, it does not vanish
Why: Predictive optimization runs maintenance operations such as OPTIMIZE and VACUUM on Unity Catalog managed tables, choosing which tables and when from observed read and write patterns, and it bills as its own usage line rather than on your job cluster. Its coverage is the first thing to check: external tables and anything still in the legacy metastore are outside it, so deleting those jobs silently stops maintenance. The second is timing. The service decides when work is worthwhile; if a table must be compacted in the ten minutes between the nightly load and the first dashboard refresh, keep an explicit OPTIMIZE in that job so the guarantee is yours. Verify rather than assume: DESCRIBE HISTORY shows OPTIMIZE and VACUUM commits whether a human or the service made them, so you can confirm coverage per table and then retire the redundant jobs. Watch the billing line too, because maintenance you used to see on an ETL cluster now appears somewhere else.
Senior depth: I treat it as a default with exceptions. Managed tables with no hard timing requirement go to the service and their jobs get deleted after I can see service-made commits in their history. Tables with a post-load latency commitment keep an explicit OPTIMIZE in the pipeline, because an SLA I cannot schedule is not an SLA. Then I would run a periodic audit over table histories to catch the tables that fell through the gap — external paths, legacy metastore, anything created outside the standard pattern — since that gap is silent by construction.
Common mistake: Switching it on, deleting every maintenance job, and discovering months later that the external tables were never covered.
Follow-up they will ask: How would you audit, across 400 tables, which ones have had no maintenance commit in a month?
seniorSomeone partitioned by customer_id and the table is now millions of sub-megabyte files. Walk me through the fix and why clustering prevents a repeat.
Liquid Clustering: change your mind later · tap to open the answer
Say this first: High-cardinality partition columns create a directory per value so every write drops tiny files; recreate the table with CLUSTER BY (customer_id, event_date).
Why: Hive partitioning makes the physical layout a function of cardinality, so a million customers means a million directories and a scan that launches a task per tiny file. Liquid clustering decouples key choice from directory structure: files are written at a normal target size and the keys only decide which rows sit together, with file-level min/max statistics doing the skipping.
Senior depth: Partitioning is a physical commitment; clustering is a layout the engine maintains for you. I would migrate with a CTAS into a CLUSTER BY table, cut readers over, then verify in the query profile — files pruned versus files read, and bytes scanned before and after. The real win is not that clustering is magic, it is that re-keying later costs an ALTER instead of a full rewrite, so the choice stops being permanent.
Common mistake: Adding more OPTIMIZE runs while keeping the partition column, so compaction fights the partition boundaries forever.
Follow-up they will ask: How do you prove skipping actually improved after the migration?
architectYou own 400 tables and nobody can tell you which columns each one is filtered on. Do you just set CLUSTER BY AUTO everywhere?
Liquid Clustering: change your mind later · tap to open the answer
Say this first: It is the right default for the long tail with unknown or drifting access patterns; keep explicit keys where the predicate is known and under an SLA.
Why: CLUSTER BY AUTO lets Databricks choose and adjust clustering keys from observed query patterns and maintain them through predictive optimization, so you are not hand-auditing hundreds of tables. Explicit CLUSTER BY (cols) still wins where the access path is known and critical — a merge key you rely on for MERGE pruning — because you do not want keys drifting under a latency commitment.
Senior depth: Treat it as a portfolio: AUTO for the tables nobody can characterize, explicit keys for the handful with a contract — merge keys and headline dashboard filters. Then measure instead of assuming: sample query profiles for files-pruned ratios per table, and watch the optimization DBUs, because incremental clustering costs scale with churn rather than table size. That is the improvement over Z-ORDER's full rewrite, not a free lunch.
Common mistake: Turning AUTO on and assuming layout work is now free, with no maintenance running and no check that queries improved.
Follow-up they will ask: What would make you override AUTO with explicit keys on a specific table?
advancedA liquid-clustered table has no partition directories. The physical plan shows no PartitionFilters at all. So what is pruning the files?
Liquid Clustering: change your mind later · tap to open the answer
Say this first: The same per-file min/max statistics in the Delta log that power data skipping — clustering just makes those ranges tight enough to be useful.
Remember these
- Clustered tables prune on file statistics, not on directory paths
- PartitionFilters is absent by design, not because pruning failed
- The log marks clustered files so OPTIMIZE can work incrementally
- Judge it on files pruned vs files read in the query profile
Why: Hive partitioning prunes by path: the planner reads the directory names, so the plan shows PartitionFilters and the elimination happens before any file metadata is consulted. A clustered table has a flat file layout, so there is nothing to prune by path and that line disappears from the plan, which is why people conclude pruning is gone. What actually happens is that writers and OPTIMIZE place rows with nearby clustering-key values into the same file, so each file's recorded min/max for those columns is narrow, and the scan refutes files from the log without opening them. The log also tracks which files have already been clustered, which is what lets a later OPTIMIZE do incremental work rather than re-reading the table. The evidence is in the query profile, not the plan text: files pruned against files read, and bytes scanned for the same query before and after clustering.
DESCRIBE DETAIL ops.silver.orders; -- clusteringColumns, numFiles
-- then compare files pruned vs files read in the query profileSenior depth: Partitioning gives you coarse, guaranteed elimination on an exact column match; clustering gives you statistical elimination that degrades gracefully across several keys and reorganizes incrementally. The practical consequence is that clustering rarely matches a well-chosen partition column on that column's own filter, but it does not collapse on the second filter column or on high cardinality, which is where partitioning fails badly. I argue it from measured bytes scanned per query class plus maintenance DBUs, because the headline comparison on a single predicate usually flatters partitioning and ignores everything it costs.
Common mistake: Reading explain output, seeing no PartitionFilters, and concluding the migration from partitioning broke pruning.
Follow-up they will ask: What kind of predicate still gets you nothing from either layout?
advancedWhen would you run OPTIMIZE ... FULL on a clustered table, and what does it cost you?
Liquid Clustering: change your mind later · tap to open the answer
Say this first: When history must be laid out under keys it was never written with — and it costs the full rewrite that incremental clustering exists to avoid.
Remember these
- Plain OPTIMIZE skips files the log already marks as clustered
- That is why re-keying leaves history in the old layout
- OPTIMIZE ... FULL reclusters every record, ignoring those marks
- Cost is a full rewrite plus a pile of files for VACUUM
Why: A plain OPTIMIZE on a clustered table only processes files the log has not already marked as clustered, so its cost tracks the day's change rather than the table's size. That is exactly why the table does not reorganize itself after ALTER TABLE ... CLUSTER BY: the existing files are already marked clustered, just under the old keys. OPTIMIZE ... FULL ignores those marks and reclusters every record, which is the right call in two situations — right after changing keys when historical queries must prune immediately, and after enabling clustering on a table whose data predates it. The cost is a complete rewrite of the dataset: the same bill a nightly ZORDER was running up, plus a large number of superseded files that VACUUM will have to clean. So treat it as a one-off migration step: confirm the new keys are the ones queries really filter on, run it off-peak, and go back to plain incremental OPTIMIZE afterwards.
ALTER TABLE ops.silver.orders CLUSTER BY (region, event_date);
OPTIMIZE ops.silver.orders FULL;Senior depth: FULL is a migration tool, not a maintenance mode. The decision is whether historical queries matter enough to pay a full rewrite now, and on most time-series tables they do not: the old layout ages out of the hot range on its own while new data lands under the new keys. I would re-key, let plain OPTIMIZE run, and measure pruning separately for the recent and historical date ranges — if the historical range is genuinely in the critical path, that is when FULL earns its bill, once.
Common mistake: Running OPTIMIZE ... FULL on a schedule, which turns incremental clustering back into the nightly full rewrite it replaced.
Follow-up they will ask: If history is cold and the hot range is the last 30 days, do you still need FULL?
seniorA team wants to cluster by their MERGE key to make the nightly upsert cheaper. Does clustering help writes, or only reads?
Liquid Clustering: change your mind later · tap to open the answer
Say this first: It helps the write, because the expensive part of a MERGE is finding and rewriting candidate target files — and that is a read.
Remember these
- MERGE's file-finding phase prunes on the same statistics reads use
- Clustering the merge key cuts numTargetFilesRemoved per run
- High-cardinality keys cluster fine; they partition terribly
- A non-selective source cancels the benefit regardless of layout
Why: MERGE first identifies which target files could contain a matching row, then rewrites those files. That identification pushes the ON clause against the per-file statistics, so if the merge key is a clustering key, the matched rows live in a small number of files and the rewrite shrinks with them. This is the main lever on an hourly upsert, and it is visible in numTargetFilesRemoved in the commit metrics. Clustering is also the right tool here precisely because a merge key is usually high cardinality, which is where Hive partitioning fails — a directory per key gives you millions of tiny files, while clustering writes normal-sized files and only decides which rows share them. Two caveats. The source has to be selective too: merging a full snapshot against a clustered table still touches everything. And a second clustering key such as an event date only helps if the MERGE's ON clause mentions it, so the key choice and the statement have to be designed together.
ALTER TABLE ops.silver.orders CLUSTER BY (order_id, event_date);
OPTIMIZE ops.silver.orders;
-- then check numTargetFilesRemoved on the next MERGE commitSenior depth: Layout is one decision serving two workloads, so I would price both. Clustering on the merge key plus the window column makes the nightly MERGE prune and gives the dashboard's date filter something to work with, which is usually the best compromise. If the two access patterns genuinely conflict, the honest answer is two tables — a mutable serving table clustered for upserts and an append-only analytical table built from it — rather than a layout that is mediocre for both.
Common mistake: Clustering by the merge key while leaving the ON clause as a bare equality on a non-clustered column, so nothing prunes.
Follow-up they will ask: The table is also scanned end to end by BI every morning. Does that change your keys?
architectYou own a 60 TB table partitioned by event_date that performs acceptably today. Make the case for or against migrating it to liquid clustering, and say how you would do it.
Liquid Clustering: change your mind later · tap to open the answer
Say this first: Migrating is a full rewrite plus a reader cutover because there is no in-place conversion, so it only pays when the partition scheme is already costing you.
Remember these
- No in-place conversion: migration is CTAS or clone plus rewrite
- CLUSTER BY and PARTITIONED BY cannot coexist on one table
- Migrate for measured pain: ZORDER cost, tiny files, drifted predicates
- replaceWhere writers and path-reading tools break on the cutover
Why: A table is either partitioned or clustered — the two cannot be combined, and you cannot turn an existing partitioned table into a clustered one with an ALTER. The migration is a CREATE TABLE ... CLUSTER BY ... AS SELECT, or a clone followed by a rewrite, then repointing every reader and writer. So the argument has to come from pain you can measure: a second filter column forcing a nightly ZORDER whose cost scales with table size, a partition column with the wrong cardinality producing tiny files or enormous skewed partitions, or a query mix that has drifted away from event_date and cannot be fixed without recreating the table. If the dominant predicate is genuinely event_date and DESCRIBE DETAIL shows healthy file sizes, there is nothing to buy. Also count what breaks: writers using replaceWhere on partition predicates, external tools that read partition paths directly, and anything that assumes the directory layout. The real purchase is optionality — after migrating, the next layout change is an ALTER instead of another migration.
CREATE TABLE ops.silver.orders_clustered
CLUSTER BY (country, event_date)
AS SELECT * FROM ops.silver.orders;
-- cut readers over, then swap namesSenior depth: I would not migrate a healthy partitioned table on principle — the rewrite and the cutover are real risk for no measured gain. What I would do is make clustering the default for new tables and keep a short list of migration candidates ranked by what the current layout costs: nightly ZORDER DBUs, average file size from DESCRIBE DETAIL, and bytes scanned per query class. For the ones that qualify, I would run the CTAS into a new name, dual-write or backfill the gap, verify pruning and row counts on the new table, then swap names in one step so the rollback is a swap back.
Common mistake: Adding CLUSTER BY to the existing DDL in the repository and assuming the next deploy converts the table.
Follow-up they will ask: How would you cut readers over on a table that is written to every hour?
advancedPredictive optimization has been on for a month and a key dashboard table is still slow. The team says the feature is not working. What is your diagnosis?
Predictive Optimization: stop cron-ing OPTIMIZE · tap to open the answer
Say this first: Maintenance keeps the layout you chose healthy; it does not choose better clustering keys, so a table clustered on the wrong column stays slow no matter how well it is compacted.
Remember these
- Layout decay and a wrong key both look like 'slow table'
- Healthy file sizes with poor pruning points at the clustering keys
- ALTER TABLE ... CLUSTER BY changes keys; it does not rewrite history
- CLUSTER BY AUTO lets Databricks pick keys from observed queries
Why: There are two different problems that both present as a slow table. One is layout decay — too many small files, data no longer sorted by the keys you picked — and that is what predictive optimization fixes by scheduling incremental OPTIMIZE where it pays. The other is a wrong key: the table is clustered or Z-ordered on a column nobody filters on, so even a perfectly compacted table has to read most of its files. Look at the query profile for the ratio of files pruned to files read; if pruning is poor while file sizes are healthy, the keys are the problem. The fix is ALTER TABLE ... CLUSTER BY on the columns query history shows people actually filtering on, or CLUSTER BY AUTO so Databricks adjusts the keys from observed patterns. Predictive optimization then schedules the incremental clustering work for the new keys.
ALTER TABLE prod.gold.orders CLUSTER BY (order_date, region);
-- or let Databricks choose and adjust the keys
ALTER TABLE prod.gold.orders CLUSTER BY AUTOSenior depth: I separate the two conversations deliberately, because they have different owners. Layout maintenance is a platform concern and should be automated out of every team's backlog — that is what predictive optimization buys. Key selection is a data modelling decision tied to how the table is actually queried, and it needs query history as evidence, not intuition. For a headline dashboard table I would set explicit keys and hold them to a measured pruning ratio; for the long tail nobody can characterise, AUTO plus predictive optimization is the right default. The failure I want to avoid is a team concluding automation is broken when it did exactly what it promised.
Common mistake: Expecting automated maintenance to compensate for a partition or clustering column nobody queries on.
Follow-up they will ask: How would you decide between explicit keys and CLUSTER BY AUTO for that table?
advancedAn auditor asks why one table was optimized a dozen times last week while another was never touched. How does the scheduler decide?
Predictive Optimization: stop cron-ing OPTIMIZE · tap to open the answer
Say this first: It uses Unity Catalog's change and access statistics to estimate the benefit of maintenance against its cost, so churned-and-queried tables win and cold tables are left alone.
Remember these
- Decisions come from UC write-churn and read-access statistics
- High churn plus high read volume means high expected payoff
- Cold tables are intentionally skipped to avoid wasted DBUs
- The operations history table is the audit record of what ran
Why: Unity Catalog already knows how much data was written to each table and how often it is read, because every commit and every query goes through it. Predictive optimization uses those signals to rank candidate operations: a table taking constant small writes and serving many queries has a high expected payoff from compaction, while a table written once and never read has none. That is the whole point — a cron loop would have spent the same DBUs on both. The evidence for the auditor is system.storage.predictive_optimization_operations_history, which shows the operations and metrics per table, cross-referenced with query history to show the read volume that justified them. The honest caveat is that the decision is heuristic and tuned by Databricks, so you influence it by what you write and query, not by a knob.
Senior depth: The thing to be clear about is that this is an optimizer, not a scheduler, and you cannot give it a deadline. So for anything with a hard timing requirement — a table that must be compacted before a market-open dashboard — I keep an explicit OPTIMIZE in the job that produces it and let predictive optimization handle everything else. That hybrid is not a failure of the feature; it is recognising that an expected-value scheduler and an SLA are different contracts. I would document which tables are in the explicit set and review it quarterly, because that list quietly grows.
Common mistake: Assuming there is a per-table schedule or threshold you can configure directly.
Follow-up they will ask: If a table must be compacted before a specific hour each day, what would you do instead?
seniorLegal requires that deleted customer records are physically removed from storage within seven days. Can you point at predictive optimization and call that done?
Predictive Optimization: stop cron-ing OPTIMIZE · tap to open the answer
Say this first: No — VACUUM only removes files past the table's retention window, so the deletion deadline is set by delta.deletedFileRetentionDuration, not by the fact that VACUUM is automated.
Remember these
- VACUUM only removes files older than the table's retention window
- delta.deletedFileRetentionDuration is the knob that sets the deadline
- Shortening retention kills time travel and RESTORE beyond that window
- With deletion vectors, rows persist in files until those files are rewritten
Why: Deleting a row in Delta writes a new version that no longer references the old file; the file itself stays until VACUUM removes it, and VACUUM will not remove files newer than the retention period. The default retention is measured in days and is a table property, so if it is longer than your legal deadline, automated VACUUM still will not delete in time. You set delta.deletedFileRetentionDuration on the tables under the requirement, and you accept that time travel and restore beyond that window stop working for them. Deletion vectors add a wrinkle worth saying out loud: a deleted row can remain inside a data file until the file is rewritten, so the record is logically gone but physically present until maintenance rewrites it. The compliance answer is a per-table retention setting plus evidence from the operations history that VACUUM ran, not 'the platform handles it'.
ALTER TABLE prod.silver.customers SET TBLPROPERTIES(
'delta.deletedFileRetentionDuration' = 'interval 3 days'
)Senior depth: Compliance questions need a mechanism and a receipt, and automation only gives you the mechanism. I would define a 'right to erasure' table class with a short deletedFileRetentionDuration, confirm those tables are UC managed so predictive optimization actually covers them, and build a report from the operations history showing the last VACUUM per table in that class. I would also flag the time-travel tradeoff in writing to whoever owns incident recovery, because the day someone needs to restore a bad load on one of those tables, the history they expected will not be there.
Common mistake: Treating automated VACUUM as a GDPR control without ever checking the retention property.
Follow-up they will ask: How would you produce evidence for the auditor that a specific record's files are gone?
seniorWalk me through decommissioning the existing maintenance jobs after you turn predictive optimization on. What would you not delete on day one?
Predictive Optimization: stop cron-ing OPTIMIZE · tap to open the answer
Say this first: Run both for a few weeks, then retire maintenance only for UC managed tables that show up in the operations history, keeping explicit jobs for external tables and deadline-bound tables.
Remember these
- Overlap both approaches before deleting anything
- Retire per-table, using the operations history as the coverage proof
- External, hive_metastore, and deadline-bound tables keep explicit jobs
- Expect the bill to move lines, not disappear
Why: Enable it at the catalog level and leave the existing job running, because deleting the safety net before you have evidence is how you discover coverage gaps during an incident. After a couple of weeks, pull the set of tables that appear in system.storage.predictive_optimization_operations_history and intersect it with the table list your job iterates over. Anything in both can come out of the job. What stays is the external and hive_metastore tables, which were never covered, and any table with a hard timing requirement that an expected-value scheduler will not honour. Then shrink the job to that residual list and watch the bill shift from your ETL cluster's DBUs to the serverless maintenance line. Record the before-and-after so the cost conversation later is about a shift, not a new expense.
Senior depth: The risk in this migration is not that the new thing fails, it is that the old thing was quietly doing something nobody documented — a VACUUM with a non-default retention, a REORG, a stats refresh. So before touching the job I read what it actually executes per table, not what the README says. Then I retire in waves with the operations history as evidence, and I leave a weekly check that diffs the UC managed-table list against the tables maintenance touched in the last thirty days. That diff is the drift detector, and it is cheaper than finding out from a slow dashboard.
Common mistake: Switching the feature on and deleting the maintenance job in the same change, then finding the gaps in production.
Follow-up they will ask: How would you detect a table that silently fell out of coverage later?
architectYou own a metastore with forty catalogs and a dozen teams. Where do you enable predictive optimization, and how do you keep the cost attributable?
Predictive Optimization: stop cron-ing OPTIMIZE · tap to open the answer
Say this first: Enable at account level so new catalogs inherit it, override downward only where you have a reason, and attribute the serverless maintenance spend per catalog from the operations history.
Remember these
- Enable at account level so new catalogs inherit coverage by default
- Override downward for sandbox catalogs and external-table catalogs
- Roll operations history up to an ownership mapping for chargeback
- Maintenance spend scales with write churn, so set that expectation early
Why: Inheritance is the whole design lever: enabling at the account means every catalog a team creates next quarter is covered without a ticket, which is the opposite of the per-table cron world. You then override at catalog or schema level for the exceptions — a sandbox catalog of throwaway tables where maintenance is wasted spend, or a catalog of external tables where it does nothing anyway. For attribution, the operations history carries the catalog and table, so you can roll maintenance DBUs up to whatever ownership mapping you keep, rather than leaving it as one unattributable platform line. The governance work that actually pays is pushing teams toward managed tables, since that is what decides coverage. And set the expectation early that maintenance spend scales with write churn, so a team that triples its ingest will see its maintenance line triple too.
SELECT catalog_name, SUM(usage_quantity) AS maintenance_dbus
FROM system.storage.predictive_optimization_operations_history
WHERE start_time >= date_trunc('MONTH', current_date())
GROUP BY catalog_name ORDER BY maintenance_dbus DESCSenior depth: I treat it as a default-on platform policy with documented exceptions, because opt-in governance decays the moment the person who filed the tickets changes teams. The architecture work is less about the toggle and more about making managed tables the default landing place, since external tables are the real coverage hole. Then I make the spend visible per catalog monthly, so a team that sees its maintenance line jump asks the right question — what changed in our write pattern — rather than asking the platform team to turn it off.
Common mistake: Enabling per schema by request, which recreates the coverage-gap problem the feature was meant to remove.
Follow-up they will ask: Which catalog would you deliberately leave disabled, and how would you justify it?
seniorAuto Loader missed a day of files. How do you debug without reprocessing the lake?
Auto Loader · tap to open the answer
Say this first: Checkpoint, cloud event backlog, and whether files arrived in a different prefix.
Why: Don't reset the checkpoint as step 1 — you'll duplicate bronze. Compare source listing vs bronze _metadata.file_path. Replay a prefix with a bounded backfill stream.
Senior depth: Idempotent bronze (path as key) or a separate backfill table you merge once.
Common mistake: rm checkpoint and 'just rerun'.
Follow-up they will ask: How do you backfill without doubles?
advancedWhat is the rescued data column, and what kind of row ends up in it?
Auto Loader · tap to open the answer
Say this first: It is a JSON string column holding whatever did not fit the schema — unknown columns, values that failed the column's type, and case-mismatched names — instead of failing the batch.
Remember these
- Holds unknown columns, type mismatches, and case-mismatched names
- Carries the source file path so you can trace the bad file
- Named via cloudFiles.rescuedDataColumn, _rescued_data by default
- Nothing alerts on it: a type change looks like silent nulls
Why: Auto Loader will not throw away data it cannot place, so when a value does not parse as the column's type, or a field is not in the schema, or a name differs only by case, the raw content goes into the rescued data column as JSON along with the source file path. The column name defaults to _rescued_data and is configurable with cloudFiles.rescuedDataColumn. That design is why ingestion is robust and also why a field can vanish from silver with no error anywhere: an upstream type change from integer to a string with a currency symbol moves every value into the rescued column and leaves the real column null. Nothing alerts on that by default. So treat the rescued column as a monitored metric — count non-null rows per batch, alert on a threshold, and inspect the JSON to see which field is drifting. On a regulated table a growing rescued count should break a build, not sit in a dashboard.
SELECT count(*) AS rescued, min(_metadata.file_path) AS example
FROM ops.bronze.orders_raw
WHERE _rescued_data IS NOT NULLSenior depth: The rescued column is the ingest layer's error channel, so it needs the same treatment as a dead-letter queue: a count per batch, a threshold alert, and an owner. I would expose it as a data-quality expectation on the bronze table so a rising rate fails or flags the pipeline update rather than living in a chart. The failure mode worth naming in an interview is that nothing ever errors — downstream aggregates just get quieter — which is exactly the class of bug that survives a quarter.
Common mistake: Dropping the rescued column in the bronze-to-silver transformation because it is 'just noise', which deletes the only evidence of schema drift.
Follow-up they will ask: How would you turn the rescued column into something on-call actually sees?
advancedYour Auto Loader stream has been running for two years over a landing zone that is never cleaned up. What degrades, and which option addresses it?
Auto Loader · tap to open the answer
Say this first: Both the per-trigger discovery and the checkpoint's file registry grow with the number of files ever seen; cloudFiles.maxFileAge bounds the registry, and notifications bound the discovery.
Remember these
- The checkpoint's RocksDB file registry grows with files ever seen
- Listing mode cost tracks total objects, not new ones
- cloudFiles.maxFileAge bounds state but allows reprocessing of very late files
- cloudFiles.cleanSource can archive or delete files after ingest
Why: Auto Loader keeps the set of already-ingested file paths in a RocksDB-backed store inside the checkpoint, and that state grows with every file ever processed. In directory listing mode the stream also has to discover files in a prefix that now holds millions of objects, so each trigger pays a listing cost that has nothing to do with how much new data arrived. cloudFiles.maxFileAge puts an upper bound on how long a path is retained in the registry, which keeps state bounded — at the cost of a real tradeoff, because a file that arrives later than that window is no longer known to have been seen and can be reprocessed. The complementary fixes are structural: file notification mode makes discovery proportional to new files instead of total files, a date-based prefix layout keeps any listing small, and cloudFiles.cleanSource can move or delete source files after ingestion so the landing zone stops growing at all. Set maxFileAge from the measured worst-case lateness of the source, not from a round number.
.option('cloudFiles.useNotifications', 'true')
.option('cloudFiles.maxFileAge', '90 days')
.option('cloudFiles.cleanSource', 'MOVE')
.option('cloudFiles.cleanSource.moveDestination', archive_path)Senior depth: State growth and discovery cost are two problems people merge into one. Discovery is fixed structurally — notifications plus a date-partitioned prefix — and that is the change I make first because it is the one with no correctness implications. maxFileAge is the one with teeth: it trades bounded state for a reprocessing window, so I would set it from the observed p99 lateness with headroom, and make bronze idempotent on the source file path so a late reprocess converges instead of duplicating.
Common mistake: Setting maxFileAge low to shrink the checkpoint and reintroducing duplicates for any source that occasionally delivers a week late.
Follow-up they will ask: How would you measure the real worst-case lateness of this source?
seniorAn upstream team overwrites yesterday's file in place with a corrected version — same path, same name. What does Auto Loader ingest?
Auto Loader · tap to open the answer
Say this first: Nothing. The registry keys on file path, so a rewritten file at an already-processed path is treated as seen and its new content never lands.
Remember these
- Dedup is by file path, so a rewritten path looks already-ingested
- The correction is lost silently, with no error anywhere
- cloudFiles.allowOverwrites reprocesses changed files and duplicates rows
- Immutable file names are the only fix that does not need dedup downstream
Why: Auto Loader's exactly-once guarantee comes from remembering which paths it has committed, and that model assumes the landing zone holds immutable objects. Overwrite a path it has already processed and the corrected content is simply never read — no error, no warning, and bronze keeps the wrong version indefinitely. cloudFiles.allowOverwrites makes the stream reprocess files whose content has changed, which fixes the missed correction and immediately creates the next problem: the corrected rows are appended on top of the originals, so bronze now has both versions. That is only safe if the downstream path deduplicates, which in practice means silver MERGEs on a business key rather than appending. The durable answer is to make the contract immutable — new file names for corrections, ideally with a version or timestamp in the name — because every alternative here is a workaround for a mutable landing zone.
.option('cloudFiles.allowOverwrites', 'true')
-- bronze now holds both versions; silver must MERGE on the business keySenior depth: This is an interface problem dressed as a configuration problem. The ingestion contract should say files are immutable and corrections arrive as new objects, because that makes replay, audit and lineage all work by construction. If I cannot change the upstream, I enable allowOverwrites and pair it with a silver MERGE keyed on the business key and sequenced by the file's modification time, so a reprocessed file converges instead of duplicating — and I would say out loud that bronze is then no longer a faithful append-only record of what arrived.
Common mistake: Enabling allowOverwrites as the fix and shipping it with an append-only bronze-to-silver step, which doubles the corrected rows.
Follow-up they will ask: How would you detect, today, whether any file in the landing zone has been overwritten since ingestion?
seniorPhoton on, runtime unchanged. How do you investigate?
Photon · tap to open the answer
Say this first: The bottleneck isn't a Photon-eligible operator — tiny files, skew, or a UDF/scan of the whole lake.
Why: Check Photon usage % in the SQL profile. If it's high and you're still slow, it's I/O or shuffle skew. If it's ~0%, find the fallback expression. Don't buy bigger Photon SKUs first.
Senior depth: Photon accelerates a good plan. It does not fix a bad model, tiny files, or collect().
Common mistake: Upgrading DBU SKU without a profile.
Follow-up they will ask: What's the interview closer?
advancedWhich parts of a query does Photon actually accelerate, and name things that keep it out.
Photon · tap to open the answer
Say this first: Columnar scans of Parquet and Delta, projections, filters, hash aggregates, hash joins, sorts and writes — Python and pandas UDFs, RDD code, and some expressions and types fall back.
Remember these
- Native: columnar scans, filters, projections, hash aggregates, hash joins, writes
- Not native: Python and pandas UDFs, RDD code, some expressions and types
- A SQL UDF is inlined, so its expressions can stay in Photon
- Non-SQL Python work gets nothing from Photon at any price
Why: Photon reimplements physical operators as vectorized native code working on columnar batches, which is why its strengths are the operators where per-row JVM overhead dominated: reading columnar files, evaluating filters and projections, hash aggregation and hash joins. Outside that set, execution stays on the JVM path. Python and pandas UDFs are the clearest exclusion, since their semantics require a Python worker; RDD code is not part of the SQL engine at all; and individual expressions or data types that are not implemented natively will fall back even inside an otherwise supported operator. A SQL UDF behaves differently because it is inlined into the plan, so the expressions it contains can stay native — which makes rewriting a hot Python UDF as SQL a real engineering lever, not a cosmetic one. Knowing this boundary is mostly about not over-promising: a pipeline whose runtime is arbitrary Python will see almost nothing, and no DBU SKU changes that.
Senior depth: I frame Photon as plan coverage: it accelerates the parts of a query it implements, and the question is always what fraction of the runtime those parts hold. That makes the triage mechanical — sample query profiles, measure time in Photon nodes against total, and rank pipelines by the non-Photon time you could recover. It also sets expectations honestly with whoever is paying: Photon makes a good plan over Delta cheaper, and it does nothing for a data model problem, a tiny-file problem, or a pipeline of Python UDFs.
Common mistake: Promising a blanket multiplier for every workload, then explaining a flat result on a UDF-heavy pipeline after the invoice arrives.
Follow-up they will ask: How would you triage 30 pipelines to find the ones Photon will actually help?
advancedThere is one Python UDF halfway through an otherwise Photon plan. Does the whole query fall back, and what does the transition cost?
Photon · tap to open the answer
Say this first: No — Photon is per-operator, so the scan and aggregate around it can stay native, but each boundary converts columnar batches to rows and back.
Remember these
- Photon eligibility is per operator, not per query
- Each boundary converts columnar batches to rows and back
- Python UDFs add worker serialization on top of the transition
- Many scattered transitions can be slower than no Photon at all
Why: Photon decides operator by operator, so a single unsupported node does not disqualify the query; the scan beneath the UDF and the aggregate above it can both run natively. The cost is the boundary. Photon works on columnar batches and the JVM path works on rows, so crossing between them requires a conversion in each direction, and a Python UDF adds serialization to and from a Python worker on top of that. One such transition in a large scan-heavy query is usually worth paying because the scan is where the time was. Several transitions scattered through the plan can make the query slower than running it entirely on the JVM, which is the counter-intuitive result worth being able to explain. The remedies are ordered by effect: replace the UDF with built-in expressions so there is no boundary, push it to the very edge of the plan so only a small result set crosses, or precompute the column in silver so the interactive query never sees it.
-- replace the UDF with built-ins so no boundary exists
SELECT id, upper(trim(country)) AS country_norm
FROM ops.silver.ordersSenior depth: Transitions are the part people miss: the mental model is not Photon or JVM, it is a plan with boundaries that each cost a conversion. So when a UDF is unavoidable, I care about its position and its cardinality — applied to a small aggregated result at the top of the plan it is nearly free, applied per row on a billion-row scan it dominates and drags the conversion cost along with it. Precomputing the derived column once in silver is usually the cheapest answer, because it moves the cost out of every interactive query and into one batch job.
Common mistake: Assuming one UDF makes the whole query JVM-bound, so nobody bothers to look at where the time is actually spent.
Follow-up they will ask: The UDF cannot be rewritten in SQL. Where in the plan do you put it?
architectA platform lead wants Photon enabled on every cluster in the account. What is your pushback, and what would you do instead?
Photon · tap to open the answer
Say this first: A blanket mandate raises the DBU rate on the whole fleet, including the clusters doing Python and ML work where Photon has almost nothing to run.
Remember these
- The premium applies to uptime, not to accelerated plan fractions
- Python, ML and RDD fleets pay the rate and gain almost nothing
- Encode the decision in cluster policies per workload type
- Low coverage plus high premium identifies a rewrite backlog
Why: Photon's rate premium applies to cluster uptime, not to the portion of the plan it accelerated, so enabling it on a cluster whose time goes to arbitrary Python, model training or RDD code is a straight cost increase with no corresponding speedup. The honest counter-proposal is segmentation: enable it where the work is SQL and DataFrame operations over Delta, which is most ETL and all BI, and leave the Python and ML fleet alone. Encode that in cluster policies by workload type so the default is right without anyone having to remember, rather than issuing an account-wide setting. Then measure instead of asserting: sample query profiles for the share of time in Photon nodes per workload, and compare DBUs per workload before and after from the billing usage tables. The pipelines that show high premium and low coverage are not an argument against Photon — they are a rewrite backlog, because UDF-heavy code is where the recoverable time is largest.
Senior depth: I would reframe it from a toggle to a portfolio decision with two measurements per workload: share of runtime in Photon nodes, and DBUs consumed. SQL and ETL over Delta gets Photon by policy default. Python, ML and RDD workloads get the standard runtime until something changes. And the UDF-heavy pipelines in the middle become a ranked backlog, because rewriting a hot Python UDF as built-in expressions pays twice — the operator becomes native and the plan loses its conversion boundaries.
Common mistake: Arguing the mandate on principle rather than bringing per-workload coverage and DBU numbers, which is the only thing that moves the decision.
Follow-up they will ask: What evidence would you collect before the next platform review?
seniorHow do you read a Databricks bill that exploded with 'no new jobs'?
Compute types · tap to open the answer
Say this first: Look at all-purpose uptime, DBU SKU (Photon), and warehouses left in running.
Why: System tables / account console: idle clusters, SQL warehouses without auto-stop, Photon on workloads that don't benefit. Spark UI will look fine.
Senior depth: Auto-terminate all-purpose, job clusters only for Workflows, warehouses auto-stop, Photon where the plan is native.
Common mistake: Tuning spark.sql.shuffle.partitions to cut DBUs.
Follow-up they will ask: What policy would you add first?
advancedOn a shared cluster a user's RDD code and a direct path read both fail, but the same notebook works on their personal cluster. What does access mode have to do with it?
Compute types · tap to open the answer
Say this first: Standard (shared) access mode enforces Unity Catalog isolation between users, so ungovernable operations like arbitrary RDD and JVM access are blocked by design.
Remember these
- Standard access mode sandboxes user code so reads stay attributable
- Dedicated access mode binds compute to one principal and allows the full API
- No-isolation shared cannot attribute reads, so Unity Catalog access is off
- A mode error looks like a permission error but no grant will fix it
Why: Unity Catalog has to attribute every read to a principal, which is impossible if user code can reach the JVM, the filesystem, or RDDs directly, so a multi-user cluster runs that code in an isolated sandbox and permits only the governed surface: Spark SQL, the DataFrame API, Unity Catalog tables, and volumes. Dedicated (single-user) access mode binds the cluster to one principal, so the full API surface is available and is still attributable — that is the mode for ML libraries and Scala jars that need it. The legacy no-isolation mode has no per-user attribution at all, which is why it cannot be used for Unity Catalog table access. This is the single most common cause of 'it works for me but not on the shared cluster', and re-granting privileges never fixes it because the privilege was never the problem.
Senior depth: I treat access mode as part of the governance design rather than a cluster checkbox. Standard is the default for anything multi-user, because that is the only mode where one analyst cannot read another's credentials or escape to the filesystem. Dedicated exists for the workloads that genuinely need the full API, and I give those a narrow service principal rather than a human owner. Then I pin the mode in cluster policies, because if people can create no-isolation compute the entire audit story has a hole in it.
Common mistake: Granting more privileges to chase an access-mode restriction.
Follow-up they will ask: Which access mode would you require for production jobs that must show up in lineage?
advancedTwelve small jobs each spend three minutes provisioning VMs, and serverless is not available to you. What do you do?
Compute types · tap to open the answer
Say this first: Back them with an instance pool holding idle instances, so cluster creation takes pre-warmed machines instead of asking the cloud provider.
Remember these
- Pools hold idle VMs so cluster creation skips cloud provisioning
- Preloading the runtime version removes image download from startup
- Idle pool instances cost cloud money but not DBUs
- Multiple instance types in a pool reduce capacity failures
Why: An instance pool keeps a set of idle VMs held in your account; a cluster that references the pool takes its driver and workers from those, which removes cloud provisioning from the start path and leaves only runtime initialisation. Preloading the Databricks Runtime version on the pool removes the image download too, so starts drop to a fraction of what they were. The cost model matters in the interview: Databricks does not charge DBUs for idle pool instances, but your cloud provider still charges for the VMs, so pool size and idle timeout are a direct trade of money for latency. Pools also soften capacity errors, because you can list several instance types for the pool to draw from. The job run timeline is the proof — the pending segment in front of the first Spark stage should nearly vanish.
Senior depth: The question behind the question is what you are willing to pay for latency. A pool converts a per-run wait into a continuous VM cost, so I size it from the concurrency of the schedule, not from the biggest job, and set a real idle timeout so the pool deflates overnight. I measure it the same way either side: pending seconds per run from the job timeline against VM hours from the cloud bill. If serverless becomes available for job compute, it answers the same problem without me running a pool at all, which is usually the better end state.
Common mistake: Keeping a warm all-purpose cluster instead, which bills both DBUs and VMs for every idle hour.
Follow-up they will ask: Who pays for an idle pool instance, and how do you size the pool?
architectYou own the platform and every team sizes its own compute. Write the guardrail.
Compute types · tap to open the answer
Say this first: Cluster policies: fix the access mode, cap workers and node types, force auto-termination, pin the runtime, and require tags so spend is attributable.
Remember these
- Policies can fix, default, or range-restrict any compute setting
- Auto-termination and max workers are the two rules that cut the bill
- Required custom tags make DBUs attributable per team in system tables
- Separate policies for all-purpose and jobs compute
Why: A cluster policy is a set of rules applied at compute creation that can fix a value, supply a default, or restrict a range, and policies are defined per compute type so all-purpose and jobs get different ones. The rules that pay for themselves immediately are autotermination_minutes, a maximum worker count, an allowed node-type list, the access mode, and custom tags. Tags are the one people skip and the one that ends the argument, because tagged DBUs land in system.billing.usage and you can show a team its own spend instead of debating the workspace total. Pinning the runtime version keeps jobs off an unsupported DBR and keeps behaviour comparable across teams. The last decision is political rather than technical: whether anyone is allowed to schedule production work on all-purpose compute, and the answer should be no.
SELECT custom_tags.team, sum(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 7 DAYS
GROUP BY 1
ORDER BY dbus DESCSenior depth: Guardrails only work if the feedback loop is short, so I pair policies with attribution. Policies stop the obviously wrong compute from existing — no untagged clusters, no missing auto-termination, no production on all-purpose — and tagged usage in system tables gives every team a weekly number they own. I would rather enforce four rules everyone understands than twenty that teams route around by asking for exceptions. Then the only remaining conversation is sizing, which is the one teams are actually qualified to have.
Common mistake: Writing a policy that caps worker count but leaves auto-termination as an optional field.
Follow-up they will ask: What would you do about teams that already have oversized clusters created before the policy?
advancedA serverless job needs to call a REST API that only accepts traffic from your VPC. On classic it worked because the cluster was already inside. What now?
Serverless compute: where the VMs went · tap to open the answer
Say this first: Serverless egress leaves a Databricks-managed network, so you configure network connectivity and private endpoints rather than relying on being inside the VPC.
Remember these
- Serverless egress does not originate from your VPC subnets
- A network connectivity configuration plus private endpoints is the replacement
- Firewall rules written against subnet CIDRs stop matching
- The symptom is a connection timeout, which reads like an app bug
Why: The implicit assumption in the classic setup was that the executor's source address was one your firewall already trusted. Serverless compute runs outside your VPC, so that assumption disappears and the connection is refused or times out with nothing useful in the Spark logs. The replacement is explicit: a network connectivity configuration attached to the workspace, with private endpoints to the resources serverless needs to reach, and firewall rules written against that path instead of against your subnet CIDRs. For public endpoints you may also need to allow the documented egress addresses on the other side. The reason this surprises teams is that the failure looks like an application bug — a timeout in a notebook cell — rather than the networking change it actually is.
Senior depth: I treat 'can this workload reach its dependencies' as a first-class migration criterion, right next to cost. Before moving anything I list the outbound dependencies per job — storage, metastores, SaaS APIs, on-prem databases over a gateway — and mark which ones are reachable only because of VPC placement. Those jobs need a connectivity configuration and private endpoints designed first, or they stay on classic. Doing it the other way round means discovering network requirements one production failure at a time, which is both slower and much more visible.
Common mistake: Debugging the HTTP client for a day before checking where the request is now coming from.
Follow-up they will ask: What would you check first to prove the failure is network rather than code?
advancedA team migrated a job to serverless and the bill went up, not down. Walk me through diagnosing that instead of just reverting.
Serverless compute: where the VMs went · tap to open the answer
Say this first: Compare DBUs per run rather than per hour: if the job already saturated its cluster with little idle, you paid the premium rate for the same work.
Remember these
- Normalise by cost per run, not cost per day
- A saturated classic cluster had little idle to recover
- Faster runs often lead to more frequent runs, which is a real cost change
- Easier scale-out can raise concurrency and therefore spend
Why: Pull system.billing.usage filtered to the job for a window before and after the change and normalise by run count, so you are comparing cost per execution rather than cost per day. Then put the job run timeline next to it: if classic runs show the cluster busy end to end with a short start-up tail, there was never much idle to recover and the higher rate goes straight through to the bill. The other common cause is that serverless removed the startup wait, so the job now finishes faster and somebody increased its frequency, which is a real behavioural change hiding inside a cost regression. Check whether concurrency changed too, since serverless scales out more readily and a job that used to queue may now run several tasks in parallel at full rate. Decide from that evidence: revert, resize, or keep it because the latency improvement is worth the money.
SELECT usage_metadata.job_id, COUNT(DISTINCT usage_metadata.job_run_id) AS runs,
SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_metadata.job_id IS NOT NULL
AND usage_start_time >= current_date() - INTERVAL 14 DAYS
GROUP BY ALLSenior depth: Cost regressions after a compute change are usually a behaviour change wearing a cost change's clothes. So the first thing I establish is whether the unit of work is the same — same run count, same data volume, same concurrency — before I touch the compute decision. If the work really is identical and serverless is more expensive, that is a correct result for a saturated workload and I move it back without embarrassment. If the work grew because the platform got faster, that is a product decision, and I would make it explicitly rather than let it land as an unexplained invoice line.
Common mistake: Reverting on the daily total without checking whether run count or concurrency changed.
Follow-up they will ask: If the latency win is genuinely worth it, how would you say so in cost terms?
seniorYou have thirty jobs and twelve SQL warehouses. How do you decide which ones move to serverless first?
Serverless compute: where the VMs went · tap to open the answer
Say this first: Rank by idle fraction and startup overhead: spiky warehouses and short frequent jobs move first, saturated long-running ETL moves last or never.
Remember these
- Idle fraction and startup-to-runtime ratio rank the candidates
- Feasibility blockers are init scripts, instance types, and network placement
- Migrate in waves and measure cost per run on each one
- Some saturated long-running workloads should stay on classic
Why: Build the list from data rather than opinion. For warehouses, compare billed uptime against actual query time to get an idle fraction; anything mostly idle is a strong candidate because serverless deletes exactly that cost. For jobs, compare cluster startup time against total run duration in the run timeline; a five-minute job paying four minutes of boot is wasting most of its wall clock. Then subtract the blockers — init scripts, specific instance types, instance profiles, VPC-only network dependencies — because those decide feasibility regardless of the economics. Move in that order, measure cost per run before and after on each wave, and keep the classic definition until the new one has survived a full cycle. The jobs left at the bottom of the list are the ones where classic is genuinely the right answer.
Senior depth: I frame it as a portfolio with evidence per workload, because 'serverless everywhere' and 'serverless nowhere' are both decisions made without data. The ordering that works is cheapest proof first: the idle warehouses, where the saving is obvious and the blast radius is a dashboard. That buys credibility for the harder conversations about network-coupled jobs. I also write down the criteria that would move something back — cost per run above a threshold, a dependency that needs root — so the migration has an exit condition instead of becoming a one-way mandate.
Common mistake: Running a workspace-wide migration as a single change because serverless is the strategic default.
Follow-up they will ask: What would make you move a job back after it migrated cleanly?
seniorSomeone says auto-stop does not matter any more because the warehouse is serverless. Are they right?
Serverless compute: where the VMs went · tap to open the answer
Say this first: No — a serverless warehouse still bills while it is running, and the idle timeout is what ends the meter when queries stop arriving.
Remember these
- Serverless bills while running; the idle timeout ends the meter
- Fast startup means you can set a much shorter timeout than on classic
- A frequently polling dashboard keeps the warehouse alive all day
- Check billed uptime against actual query time, not just the setting
Why: Serverless removes provisioning time, not the meter. A serverless SQL warehouse stays up for its configured idle period after the last query so the next one is instant, and those minutes are billed at the serverless rate. Set the timeout too high and a dashboard that polls every few minutes keeps the warehouse alive all day, which is the classic idle bill with a faster start. Because startup is seconds rather than minutes, you can afford a much shorter timeout than you would on classic — the cost of a cold start is now small. The thing to watch for is an auto-refreshing dashboard or a monitoring query on a short interval, which quietly converts an on-demand warehouse into an always-on one.
SELECT warehouse_id, COUNT(*) AS queries, MAX(end_time) AS last_query
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL 1 DAYS
GROUP BY warehouse_idSenior depth: The setting that was correct on classic is usually wrong on serverless, and nobody revisits it during migration because the warehouse still works. I would shorten the timeout aggressively, then measure: billed uptime versus query time per warehouse, and p50 query latency to confirm cold starts are not hurting anyone. The failure I actually look for is the automated poller — a dashboard refresh or a synthetic health check on a tight interval — because that single consumer can make every timeout setting irrelevant and it never shows up in a conversation about user behaviour.
Common mistake: Leaving a long idle timeout from the classic configuration because startup used to be expensive.
Follow-up they will ask: How short would you set it, and how would you tell if you went too far?
architectYou are setting serverless policy for the whole account. What do you standardise, and what do you deliberately leave to teams?
Serverless compute: where the VMs went · tap to open the answer
Say this first: Standardise network connectivity, identity, budget policies and tagging centrally; leave warehouse sizing and per-job compute choice to the teams who see the latency.
Remember these
- Centralise network connectivity, identity, budget policies, and tagging
- Serverless SKUs are shared, so tags are the only attribution key
- Leave sizing and compute choice to the team that feels the latency
- Review spend against workload growth per team, not in aggregate
Why: The things that must be central are the ones where a wrong local decision is invisible to the person making it: the network connectivity configuration and private endpoints, which identities may create serverless resources, and the budget policies and tags that make serverless spend attributable. Serverless usage lands on shared SKUs, so without a tagging standard you get a large unattributable line and no way to have a cost conversation with anyone. What should stay local is sizing and workload placement, because the team running the job is the one feeling the latency and the one who can tell whether the premium is buying anything. Give them the cost-per-run data and let them choose. Then review quarterly: which teams' serverless spend grew, and did their workload grow with it.
SELECT custom_tags['team'] AS team, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE sku_name ILIKE '%SERVERLESS%'
AND usage_start_time >= date_trunc('MONTH', current_date())
GROUP BY ALL ORDER BY dbus DESCSenior depth: The architectural risk with serverless is not technical, it is that the friction which used to constrain spend is gone. On classic, creating a big cluster was visible and slow; on serverless anyone can summon capacity in seconds. So the controls have to move from scarcity to accountability: tags and budget policies attached at creation, a small set of blessed configurations, and monthly spend per team that someone actually reads. I would rather have fewer technical restrictions and a working attribution chain than a thicket of policies that teams route around.
Common mistake: Enabling serverless broadly without budget policies or tags, then being unable to tell any team what they spent.
Follow-up they will ask: What does an untagged serverless charge tell you about your governance?
seniorFinance says the SQL warehouse bill tripled but query volume is flat. Where do you look?
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: At uptime, not queries: auto-stop disabled, min clusters pinned above 1, a warehouse left running, or someone changed the size or type.
Why: Warehouses bill DBUs for the time they are up, so an idle warehouse with no auto-stop bills all night for zero queries. Chart warehouse DBUs from system.billing.usage next to query counts per warehouse, then check the warehouse settings for auto-stop, min clusters, size, and a Classic-to-Pro or Serverless switch.
Senior depth: Uptime × size × SKU is the bill; query count is not in that formula. Flat query volume with rising DBU hours means idle time, so the fix is auto-stop plus min clusters back to 1 — not a smaller size that makes every real query slower. Serverless changes the tradeoff because startup is seconds, so aggressive auto-stop stops being painful.
Common mistake: Opening the query profile of the slowest statement — the queries did not change, the running hours did.
Follow-up they will ask: What auto-stop value would you set for a 9-to-6 BI workload, and what breaks if it is too aggressive?
architectThe same dashboard query took 3 seconds yesterday afternoon and 90 seconds after the overnight load. The SQL is identical. Explain it.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: The result cache was invalidated by the new table version and the warehouse's local disk cache is cold for the new files, so the first run pays a full read.
Why: Databricks serves byte-identical queries from a result cache, and that cache is invalidated when the underlying Delta table commits a new version. Below it, each warehouse cluster keeps a local SSD disk cache of Parquet it has already read, which is also empty after auto-stop, a restart, or a rewrite by OPTIMIZE — and if the load produced many small files, that cold read is expensive on top.
Senior depth: Three caches with different lifetimes: the result cache (per identical query, killed by a new table version), the disk cache on the cluster's local SSDs (killed by restart or auto-stop), and Spark's in-memory cache that BI never touches. I would warm it deliberately with a small scheduled job that runs the top dashboard queries right after the load commits, and keep gold compacted so the cold read is cheap when it does happen.
Common mistake: Blaming Photon or asking for a bigger warehouse when the first query after every load is simply uncached.
Follow-up they will ask: How would you make 9am fast without keeping the warehouse hot all night?
advancedA single dashboard query is slow. Walk me through the query profile, in order.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: Queue versus execution time first, then files pruned versus files read, then the operator holding the wall clock, then spill and the task-duration tail.
Remember these
- Queue time means concurrency; no plan change will help it
- Files pruned versus files read is the file-layout verdict
- Cache bytes versus cloud-storage bytes tells you the cache was cold
- Estimated versus actual rows exposes bad statistics before you blame size
Why: Query History gives you the statement and its total time; the profile gives a tree of operators with time, rows, and bytes each. Start by splitting queue time from execution time, because queue time is a concurrency problem and nothing in the plan will fix it. Next read the scan node: files pruned against files read tells you whether partitioning or clustering did any work, and bytes from cache against bytes from cloud storage tells you whether the disk cache was cold. Then find the operator that owns the wall clock — usually a shuffle, a join, or a point where the estimated row count is wildly below actual, which means statistics misled the optimizer. Spill bytes and a long tail in task duration point at skew or an undersized warehouse. Resizing is defensible only after those four reads, and it is the last move, not the first.
Senior depth: The profile is a decision tree, and each branch has a different owner. Queue time goes to the warehouse configuration, poor pruning goes to the table owner for clustering or partitioning, a bad join order goes to statistics and the model, and spill with a skewed task tail goes to the data distribution. I make people name which branch they are on before they file a request, because 'the warehouse is too small' is the one conclusion the profile almost never supports on its own.
Common mistake: Opening the profile, seeing a large bytes-read number, and resizing the warehouse.
Follow-up they will ask: Which number tells you the file layout is the problem rather than the warehouse size?
advancedTwo analysts run identical SQL on the same warehouse; one gets a result in 200ms and the other waits 30 seconds. Explain it.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: The warehouse had scaled out and their queries landed on different clusters — one had the Parquet in its local disk cache, the newly added one did not.
Remember these
- Each cluster behind a warehouse has its own local disk cache
- Result cache can skip compute entirely; a miss falls to per-cluster cache
- A newly added cluster starts with a cold cache by definition
- OPTIMIZE invalidates cached files by rewriting them
Why: Scale-out puts several identical clusters behind one endpoint. A byte-identical query against unchanged tables can return from the result cache with no compute at all, but once you miss that, the read happens on whichever cluster admitted the query, and only that cluster's local SSD disk cache is warm. A cluster that just started to absorb concurrency has an empty disk cache, so it reads from S3 or ADLS and pays full I/O. The profile shows it plainly as bytes read from cache versus bytes read from cloud storage. The same cold-read effect appears after auto stop, after a restart, and after OPTIMIZE rewrites the files the cache was holding, which is why the first query after a maintenance job is reliably the slow one.
Senior depth: I describe it as three caches with three lifetimes: the result cache keyed on identical text and invalidated by a new table version, the per-cluster disk cache killed by restart or scale-out, and Spark's in-memory cache that BI never touches. Once that is clear, the fix is deliberate warming rather than more hardware — a small scheduled job that runs the top dashboard queries after the load commits, and compacted gold tables so the cold read is cheap when it happens. Variance between users is a symptom of scale-out working, not of something broken.
Common mistake: Concluding the slow user has a network or driver problem when the two queries ran on different clusters.
Follow-up they will ask: What does OPTIMIZE do to a warm disk cache, and when does that matter?
seniorDashboards are fine except for twenty minutes every hour, when a dbt run blocks everything. Design the fix.
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: Isolate the workloads on separate warehouses so ETL-shaped queries cannot occupy the admission slots that interactive BI needs.
Remember these
- Admission slots are per cluster; a long query holds one the whole time
- One query cannot be spread across the clusters behind a warehouse
- Query History sorted by duration shows the blocker in one screen
- Separate warehouses also split the bill by workload
Why: A warehouse admits a bounded number of concurrent queries per cluster and queues the rest, and a long transforming statement holds its slot for its entire runtime. Sort Query History by duration for that warehouse and the picture is unambiguous: a handful of dbt statements running for minutes with dozens of two-second dashboard queries queued behind them. Raising max clusters does not solve it the way people hope, because one query runs on one cluster — it just adds capacity next to the blockage. Two warehouses is the real fix: one sized for the heavy serial transforms, one scaled out for concurrency, which also makes spend attributable per workload in system.billing.usage. If the dbt models are large MERGE-shaped writes, the better answer is that they do not belong on a warehouse at all and should run on job compute.
Senior depth: I separate workloads by shape, not by team. Interactive BI wants many short queries and therefore scale-out and a small size; transforms want one query to go fast and therefore size and no competition. Mixing them means the loudest workload sets everyone's latency. Once they are split I add a statement timeout on the BI warehouse so one runaway ad-hoc query cannot recreate the problem, and I watch queue time per warehouse as the signal rather than waiting for someone to complain.
Common mistake: Raising max clusters and expecting the heavy query to spread across them.
Follow-up they will ask: Which part of a dbt project should not be on a warehouse at all?
architectYou are standardising warehouses for a 300-person analytics org. What do you fix in the standard, and what do you leave to teams?
SQL Warehouses: compute shaped for BI · tap to open the answer
Say this first: Fix type, auto stop, tags, and default catalog; leave size and max clusters to the team that owns the workload and its bill.
Remember these
- Fix auto stop, type, tags, and default catalog in the standard
- One warehouse per BI domain, not per person and not per org
- Leave size and cluster count with the owner who pays for it
- Alert on DBU hours versus query counts to catch idle drift
Why: Standardise the settings people get wrong silently: Serverless wherever residency allows so a short auto stop is painless, a short auto stop itself, custom tags so each warehouse's DBUs land attributable in system.billing.usage, and a default catalog so nobody accidentally queries hive_metastore. Set the granularity too — one warehouse per BI domain rather than per person, because per-person warehouses multiply idle time and per-org warehouses destroy isolation. Leave sizing with the owner, because only they know the query shape, but hand them the rule that queue time means more clusters and long execution on large scans means a bigger size. Then monitor the thing that drifts: DBU hours per warehouse against query counts, so an idle warehouse is visible within a day rather than at month end. The governance point is that a warehouse is a permanently visible cost surface, so it needs a named owner the same way a gold table does.
SELECT usage_metadata.warehouse_id, sum(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 7 DAYS
GROUP BY 1
ORDER BY dbus DESCSenior depth: I split the decision into safety and performance. Safety settings are platform-owned and non-negotiable, because the failure mode is a silent bill or an accidental hive_metastore query. Performance settings are team-owned, because the failure mode is their own dashboard being slow and they get the feedback immediately. Then I give every owner the same two numbers each week — DBU hours and query count for their warehouse — so sizing conversations are about evidence instead of entitlement.
Common mistake: One giant always-on warehouse for the whole org because it looked cheaper than several small ones.
Follow-up they will ask: How would you handle the one team whose workload genuinely needs a 4X-Large?
advancedHow do you actually verify whether a given materialized view refresh was incremental, rather than trusting that it was?
Streaming tables and materialized views · tap to open the answer
Say this first: Read the refresh history and the backing pipeline's event log, which record the refresh type and the rows processed for each run.
Remember these
- Every MV is backed by a declarative pipeline with an event log
- Refresh history shows per-run outcome, duration, and row metrics
- Full source row counts on every run indicate full recompute
- Alert on refresh duration jumps rather than waiting for the bill
Why: Every materialized view on Databricks is backed by a declarative pipeline, even when you created it with plain SQL in DBSQL. That pipeline has an event log, and the refresh history for the view shows each run with its outcome and metrics. What you are looking for is whether the run processed only changed rows or re-read the full source, and the input row counts make that obvious even before you read the label. Pair it with DESCRIBE HISTORY on the materialized view's own table to see the write pattern — a full overwrite each time is a strong signal. Make this a monitored metric rather than an investigation you do after a bill arrives: alert when a view's refresh duration or input row count jumps.
Senior depth: The dangerous property of materialized views is that the failure mode is silent and expensive rather than loud and broken. So I treat refresh type as an observable the same way I treat job duration: pull refresh history and event log metrics into a monitoring table, and alert on a step change in duration or input rows per view. I would also gate definition changes in review, because an innocuous-looking edit is the most common trigger. The point is to find out from a dashboard on Tuesday, not from finance at month end.
Common mistake: Inferring the refresh type from how long it felt, instead of reading the event log.
Follow-up they will ask: What alert would you set so this is caught in a day rather than a month?
advancedA materialized view is refreshed every five minutes but the source table only lands new data twice a day. How would you fix the schedule?
Streaming tables and materialized views · tap to open the answer
Say this first: Drive refresh from the data arriving rather than from a clock — schedule it after the load, or let the pipeline that produces the source trigger it.
Remember these
- A clock schedule on an event-driven source wastes most of its runs
- SCHEDULE sets a cron; REFRESH after the load ties freshness to the data
- Putting the MV in the same pipeline as its sources handles ordering
- Verify in refresh history that refresh happens after the load, not before
Why: A five-minute schedule on a twice-daily source runs hundreds of refreshes that find nothing to do, and even a cheap incremental refresh is not free once you multiply it. ALTER MATERIALIZED VIEW ... SCHEDULE lets you set a cron that matches reality, which for a twice-daily load is two refreshes a day plus perhaps a safety run. Better still, have the job that loads the source call REFRESH MATERIALIZED VIEW as its last task, so freshness is tied to the load rather than to a guess about when the load finishes. If the view sits inside a declarative pipeline with its sources, the engine handles the ordering for you. Then check the refresh history for a while to confirm you are not refreshing before the load lands, which is the failure mode of a badly guessed cron.
ALTER MATERIALIZED VIEW gold.daily_revenue
ADD SCHEDULE CRON '0 0 2,14 * * ?';
-- or from the loading job's final task
REFRESH MATERIALIZED VIEW gold.daily_revenueSenior depth: Freshness should be expressed as a requirement and then implemented, rather than approximated with a tight cron. I ask what staleness the consumer can tolerate, then pick the cheapest mechanism that meets it: pipeline ordering if the MV and its source live together, an explicit REFRESH as the last task of the load if they do not, and a cron only when neither is available. The anti-pattern I push back on hardest is a short schedule used as insurance against not knowing when data arrives, because it pays continuously for a question you could answer once.
Common mistake: Setting an aggressive schedule to make the view 'fresh' without checking how often the source actually changes.
Follow-up they will ask: How would you handle a source that lands unpredictably rather than on a schedule?
seniorAn analyst created a materialized view in DBSQL and is surprised to find a pipeline in the workspace that they did not create. Explain it.
Streaming tables and materialized views · tap to open the answer
Say this first: Materialized views and streaming tables are implemented on Declarative Pipelines, so creating one in DBSQL creates the pipeline that refreshes it.
Remember these
- MVs and streaming tables are implemented on Declarative Pipelines
- That is why they have a pipeline, an event log, and refresh history
- Refresh runs with the owner's identity on serverless compute
- An individually owned production MV breaks when that person leaves
Why: The same engine that runs Lakeflow Declarative Pipelines backs materialized views and streaming tables, including the ones created with plain SQL from a SQL editor. That is why each one has a pipeline, an event log, and a refresh history rather than just being a table. It also explains several things that otherwise look odd: refreshes run on serverless compute, the failures show up in a pipeline event log rather than in query history, and the object has an owner identity whose permissions the refresh runs with. The operational consequence is ownership — a materialized view created by an individual refreshes as that individual, and when they leave, the refresh breaks. Production views should be created and owned by a service principal, which usually means defining them in a pipeline or a bundle rather than ad hoc in the editor.
Senior depth: The thing I care about here is that an object created in two clicks carries the same production obligations as a pipeline someone deployed deliberately. So the rule I set is that anything a dashboard depends on is defined in a bundle, owned by a service principal, and deployed through CI, while the SQL editor is for exploration. That is not bureaucracy for its own sake — the recurring incident is a gold-layer materialized view that stops refreshing because its creator's account was deactivated, and nobody notices until the numbers stop moving.
Common mistake: Treating a DBSQL materialized view as a lightweight object with no infrastructure or owner behind it.
Follow-up they will ask: Who should own the materialized views in your gold schema?
seniorBronze ingest from a cloud path, and a gold aggregate over it. Which is the streaming table and which is the materialized view, and why not use one for both?
Streaming tables and materialized views · tap to open the answer
Say this first: Streaming table for the append-only ingest with its checkpoint, materialized view for the aggregate — they have different update semantics, not just different names.
Remember these
- Streaming tables process each source record once, tracked by a checkpoint
- Materialized views keep a derived result correct as inputs change
- Late-arriving records restate an aggregate, which is MV semantics
- Using a streaming table for an aggregate pushes restatement onto you
Why: A streaming table processes each source record once, tracked by a checkpoint, which is exactly right for files landing in storage: new files are picked up, old ones are not re-read, and the table grows by appending. A materialized view defines a result over its sources and keeps that result correct, which may mean updating or replacing rows when the inputs change — that is what an aggregate needs, since one late record changes an existing group's total. Using a streaming table for the aggregate forces you to handle restatement yourself with stateful aggregation and watermarks. Using a materialized view for ingest means re-evaluating a growing source instead of consuming only new files. Match the object to the semantics: append-once means streaming table, derived result that can change means materialized view.
CREATE STREAMING TABLE bronze.events AS
SELECT * FROM STREAM read_files('s3://landing/events/', format => 'json');
CREATE MATERIALIZED VIEW gold.events_daily AS
SELECT date(ts) AS d, count(*) AS n FROM bronze.events GROUP BY 1Senior depth: I decide this on update semantics rather than on freshness, because freshness is tunable and semantics are not. Append-once ingest and restatable aggregates are genuinely different problems, and the moment you pick the wrong object you inherit a pile of hand-written state management — watermarks, late-data policies, dedup keys — that the other object would have handled. Where it gets interesting is a gold table that must be both low-latency and restatable; there I would be explicit about the staleness the business accepts rather than pretending one object gives you both for free.
Common mistake: Choosing between them on expected latency alone, without considering whether results need to be restated.
Follow-up they will ask: What breaks first if you try to maintain that aggregate as a streaming table?
architectTwo hundred dashboards hit the same handful of silver tables and the warehouse bill is dominated by repeated aggregation. How do you design the serving layer?
Streaming tables and materialized views · tap to open the answer
Say this first: Build a small set of owned materialized views at the grains the dashboards actually use, refreshed on data arrival, and point every dashboard at those rather than at silver.
Remember these
- Derive the grains to materialize from query history, not from requests
- Keep definitions pure so refresh stays incremental
- Define them in bundles owned by service principals, not in the editor
- Retire views whose refresh cost exceeds the query cost they avoid
Why: Start from query history: group the warehouse's expensive queries by shape and find the handful of grains that account for most of the aggregation cost. Materialize those, deliberately and with owners, so one refresh replaces thousands of ad hoc recomputations. Keep the definitions free of time-dependent expressions so refresh stays incremental, and drive refresh from the loading pipelines rather than from clocks. The governance half matters as much: define the materialized views in asset bundles owned by service principals, so they are reviewed, deployed, and attributable, and so a departing employee does not take a gold table's refresh with them. Then watch two numbers — refresh cost per view, and warehouse cost avoided — and retire views whose refresh costs more than the queries they replaced, because a stale unused materialized view is pure spend.
SELECT statement_text, COUNT(*) AS runs, SUM(total_duration_ms)/1000 AS secs
FROM system.query.history
WHERE start_time >= current_date() - INTERVAL 7 DAYS
GROUP BY ALL ORDER BY secs DESC LIMIT 20Senior depth: The trap in a serving layer is that materialized views are easy to create and nobody ever deletes one. So I design it as a small, owned, versioned set — a dozen grains, not two hundred — and make adding one a review conversation about whether an existing grain covers it. Then I keep a standing report of refresh cost per view against the read traffic it serves, and I retire the bottom of that list every quarter. Without that loop you end up paying continuously to keep results fresh for dashboards that were decommissioned a year ago.
Common mistake: Creating a materialized view per dashboard on request, ending with hundreds of overlapping refreshes nobody owns.
Follow-up they will ask: How would you find the materialized views that are no longer worth refreshing?
seniorDLT pipeline is green but gold is wrong. Where do you look?
Delta Live Tables (Lakeflow) · tap to open the answer
Say this first: Expectations that drop rows, a wrong grain in silver, or a live table reading a stale source snapshot.
Why: Event log + UC lineage. Don't start in Spark UI Stages — DLT may have many datasets. Compare bronze counts vs gold. Check if development vs production mode used different data.
Senior depth: Full refresh on a sampled bronze, expect metrics, then prod. Never full-refresh prod as a test.
Common mistake: Scaling the DLT cluster first.
Follow-up they will ask: How do you test a DLT graph locally-ish?
advancedIt is 3am, the pipeline is running, and one table has not updated in two hours. Which surface do you query first, and what do you look for?
Delta Live Tables (Lakeflow) · tap to open the answer
Say this first: The pipeline event log — it records per-flow progress, expectation results, and update state as queryable rows, which driver logs do not.
Remember these
- event_log() exposes pipeline events as queryable rows
- flow_progress carries per-flow row counts and backlog metrics
- data_quality rows are the only record of expectation results
- Cluster events reveal time spent waiting rather than processing
Why: Every pipeline writes an event log you can query with the event_log table-valued function, or read as a published table if the pipeline is configured that way. Each row has a timestamp, an event_type and a details struct you extract JSON fields from. For a stalled table, filter on flow_progress and read the metrics for that flow: whether batches are completing, how many rows went in, and whether a backlog is growing, which separates 'stuck' from 'slow' immediately. Then check data_quality events for that flow, because an expectation failing on every row looks like no progress downstream. update_progress tells you the update's state transitions, and cluster-related events tell you whether the pipeline spent those two hours waiting for capacity rather than processing. The reason to start here rather than in Spark stages is that a pipeline has many datasets and the event log is the only place that attributes work and quality metrics to a specific flow.
SELECT timestamp, event_type, details:flow_progress.metrics
FROM event_log(TABLE(ops.pipelines.silver_orders))
WHERE event_type = 'flow_progress'
ORDER BY timestamp DESC LIMIT 50Senior depth: The event log is the pipeline's observability contract, so I would not wait for an incident to discover it. I persist it, build the three queries on-call needs — per-flow freshness, expectation drop rates, and update outcomes — and alert off those rather than off job success, because a declarative pipeline is perfectly capable of succeeding while dropping a fifth of the rows. Resizing compute is the last move, after the log has shown that the flow is making progress and still falling behind.
Common mistake: Going straight to the driver log or resizing the pipeline cluster before establishing whether any flow made progress at all.
Follow-up they will ask: The flow is progressing but the backlog grows every batch. What is your next decision?
advancedA teammate clicks 'Full refresh all' on a production pipeline to clear a bad row. What does that do, and when is it unrecoverable?
Delta Live Tables (Lakeflow) · tap to open the answer
Say this first: It truncates the targets and reprocesses from the sources, discarding streaming state — unrecoverable when the source no longer holds the history.
Remember these
- Full refresh truncates targets and discards streaming state
- Safe for materialized views whose sources still exist
- Fatal for streaming tables whose source aged out of retention
- pipelines.reset.allowed = false protects non-replayable tables
Why: A normal update processes new data; a full refresh throws away the target's contents and the streaming state and rebuilds from the sources. That is fine for a materialized view over an intact silver table, because its sources are still there. It is catastrophic for a streaming table whose source is a landing zone under a lifecycle policy or a message topic past its retention window: the data the table was built from no longer exists, so the rebuild produces whatever is left and the rest is simply gone. The control is the pipelines.reset.allowed table property — set it to false on tables whose sources are not replayable, and a full refresh skips them instead of truncating them. Operationally, refresh a single table rather than all of them, and do it in a development copy first. The deeper point is that a full refresh is only safe when there is a replayable copy of the raw data, which is the actual job of a bronze layer.
@dlt.table(table_properties={'pipelines.reset.allowed': 'false'})
def bronze_orders():
return spark.readStream.format('cloudFiles').load(landing)Senior depth: Full refresh is a destructive operation with a reassuring name, so I would make it structurally hard to get wrong rather than relying on care. Bronze tables fed from a landing zone or a topic get pipelines.reset.allowed set to false, which also documents the intent in the code. Silver and gold stay resettable precisely because they can be rebuilt from bronze, and that replay path is worth exercising on a schedule — if you have never rebuilt gold from bronze, you do not actually know that a full refresh is recoverable.
Common mistake: Treating full refresh as a harmless 'rerun' button because it fixed a development pipeline once.
Follow-up they will ask: Which tables in a medallion pipeline would you mark as not resettable, and why only those?
seniorYou have a CDC feed with inserts, updates and deletes that arrive out of order. How do you land it in a declarative pipeline without hand-writing MERGE?
Delta Live Tables (Lakeflow) · tap to open the answer
Say this first: APPLY CHANGES INTO with KEYS, SEQUENCE BY and APPLY AS DELETE WHEN, stored as SCD Type 1 or Type 2 depending on whether you need history.
Remember these
- SEQUENCE BY is what makes out-of-order changes safe
- APPLY AS DELETE WHEN turns tombstone rows into real deletes
- SCD TYPE 2 maintains start and end columns for you
- You trade merge-predicate tuning for correctness and idempotency
Why: APPLY CHANGES INTO takes a streaming source of change rows and maintains the target for you: KEYS names the identity, SEQUENCE BY names the column that orders changes so an older row arriving late cannot overwrite a newer one, and APPLY AS DELETE WHEN identifies tombstones so a delete flag becomes an actual removal. STORED AS SCD TYPE 1 keeps only current state; TYPE 2 keeps history with start and end columns the service maintains. The target has to be a streaming table, and the service owns the merge logic, idempotency across retries, and out-of-order handling — which is precisely the set of things hand-written MERGE jobs get wrong. What you give up is control: you cannot tune the merge predicate to prune files the way you would in a hand-written statement, so layout work on the target matters more. The honest comparison in an interview is correctness guarantees against tuning freedom, and for most CDC feeds the guarantees are worth more.
APPLY CHANGES INTO LIVE.silver_orders
FROM STREAM(LIVE.bronze_orders)
KEYS (order_id)
APPLY AS DELETE WHEN op = 'D'
SEQUENCE BY seq
STORED AS SCD TYPE 2Senior depth: The whole correctness of CDC rests on the sequence column, so that is the first thing I interrogate — it has to come from the source's commit order, like a log sequence number or a transaction timestamp, not from when your pipeline happened to read the file. If the source cannot provide one, I would not pretend: either get the upstream to emit it, or accept that the target is eventually-consistent-at-batch-granularity and document that. Where it exists, APPLY CHANGES is the better default than hand-written MERGE, because out-of-order handling and retry idempotency are exactly the parts a human implementation gets wrong six months after the author moves team.
Common mistake: Omitting SEQUENCE BY or pointing it at the ingestion timestamp instead of the source's commit order, so a replayed batch overwrites newer state.
Follow-up they will ask: Your feed has no reliable sequence column. What do you do?
seniorThe nightly job runs on the team's all-purpose cluster because it is already warm. Make the cost argument.
Workflows: a DAG of tasks, not one notebook · tap to open the answer
Say this first: All-purpose DBUs are a more expensive SKU than jobs compute, and you also pay for the cluster's idle hours; a job cluster exists for the run and terminates.
Why: In Databricks pricing, Jobs Compute is a cheaper DBU rate than All-Purpose Compute for the same instance type, and a job cluster starts per run and shuts down at the end so uptime matches work. Shared interactive clusters also leak state — installed libraries, cached data, someone else's runaway cell — into production runs, which is how a green pipeline fails only on Mondays.
Senior depth: There are two separate costs: the DBU rate and uptime you did not need. I would move scheduled work to job clusters or serverless, add a cluster policy so nobody can schedule production onto all-purpose, and keep interactive clusters for humans. If per-run startup latency is the genuine objection, serverless job compute answers it without the idle bill — the warm shared cluster answers it by paying for the cluster all night.
Common mistake: Arguing only about startup convenience and never comparing the DBU rate or the idle hours on the bill.
Follow-up they will ask: When is serverless job compute the better answer than a job cluster?
architectTwo runs of the same hourly job overlapped and the target table got duplicate rows. What is wrong beyond 'the job was slow'?
Workflows: a DAG of tasks, not one notebook · tap to open the answer
Say this first: Concurrent runs were allowed against a non-idempotent write, so two runs processed overlapping input and both appended.
Why: A job's max concurrent runs setting decides whether a scheduled run starts while the previous one is still going; at 1 the new run is skipped or queued instead of racing. The durable fix is making the write idempotent — MERGE on a business key, or a replaceWhere overwrite bounded to the run's window — so an overlap or a retry converges instead of duplicating.
Senior depth: Overlap is the symptom; the non-idempotent append is the bug. I would set max concurrent runs to 1 with a timeout and an alert so a slow run becomes visible rather than doubled, pass the window explicitly through job parameters and task values instead of each task calling now(), and make the sink a MERGE or a bounded replaceWhere so re-running a window is a no-op. Then define the job in Databricks Asset Bundles so concurrency, retries, and schedule are code, not something each workspace gets clicked differently.
Common mistake: Just lengthening the schedule interval, which hides the race until one run is slow again.
Follow-up they will ask: How would you pass the window boundaries between tasks so each task knows exactly what it processes?
advancedYou want a task that posts to Slack only when something upstream failed. Yours never fires. Why?
Workflows: a DAG of tasks, not one notebook · tap to open the answer
Say this first: The default run_if is ALL_SUCCESS, so when its parent fails the notifier is marked upstream-failed and skipped along with everything else downstream.
Remember these
- ALL_SUCCESS is the default, so dependents of a failed task are skipped
- AT_LEAST_ONE_FAILED fires a handler only when a parent failed
- ALL_DONE fires cleanup regardless of parent outcome
- Plain alerting belongs in job notifications, not in a task
Why: A dependent task's run_if condition decides when it is eligible to start given its parents' outcomes, and the default requires every parent to have succeeded. That is correct for a gold task — you do not want it running on stale silver — and wrong for a failure handler, which by definition only matters when a parent failed. AT_LEAST_ONE_FAILED fires the task when at least one parent failed; ALL_DONE fires it once the parents have finished regardless of outcome, which is the right choice for cleanup that must happen either way. Worth separating from this: plain alerting does not need a task at all, because jobs have built-in notifications and webhook destinations for failure and timeout. Use a run_if task when the response is an action — rolling back a staged table, releasing a lock, writing an incident record — rather than a message. One caveat to say out loud: a task that runs on failure must not assume its parent's outputs exist.
- task_key: rollback_staging
depends_on:
- task_key: gold_publish
run_if: AT_LEAST_ONE_FAILED
notebook_task:
notebook_path: /Repos/etl/rollbackSenior depth: run_if is how you express compensation in the DAG rather than in a try/except somebody forgets to maintain. I keep notification in the job's built-in alerting, because a task that posts to Slack can itself fail, and reserve run_if tasks for state-changing recovery — unlocking, rolling back a staging table, recording the incident. The design rule is that a handler must be safe with partial or missing upstream output, since that is the exact situation it runs in.
Common mistake: Wiring the notifier as a dependent of the task it watches and leaving run_if at its default, so the alert is skipped precisely when it is needed.
Follow-up they will ask: How does a repair run interact with a task whose run_if is AT_LEAST_ONE_FAILED?
advancedA task appends to a gold table and occasionally fails partway through. Someone set max_retries to 3. What did they just build?
Workflows: a DAG of tasks, not one notebook · tap to open the answer
Say this first: An automatic duplicator — a retry re-runs the whole task, so a partially completed append plus a retry writes some rows twice.
Remember these
- A retry re-runs the whole task, not the part that did not finish
- Atomicity is per write; a task of several writes is not atomic
- MERGE on a key or replaceWhere on a window both converge on rerun
- txnAppId plus txnVersion let Delta drop a duplicate append
Why: Delta gives each individual write atomicity, but a task is usually several writes, and a retry restarts the task from the beginning with no knowledge of what the previous attempt committed. So a failure after the second of three appends, followed by a retry, double-writes the first two. Retries are a reliability feature that assumes idempotency; adding them to a non-idempotent task converts an occasional failure into permanent silent duplication, which is harder to find than the original error. Fix the write first. A MERGE on the business key converges no matter how many times it runs. A bounded overwrite with replaceWhere scoped to the run's window replaces that window's rows instead of adding to them. For append-shaped writes, Delta's txnAppId and txnVersion write options let the table recognise and ignore a duplicate commit from the same logical transaction. Once the write is idempotent, retries do what people expected them to do all along.
(df.write.format('delta').mode('append')
.option('txnAppId', 'nightly_etl')
.option('txnVersion', run_id)
.saveAsTable('ops.gold.metrics'))Senior depth: Retries are a statement that the task is idempotent, so the review question for any max_retries change is 'what happens if this runs twice'. I would make the sink converge first — MERGE on a key, or replaceWhere bounded by a window passed in as a parameter so the retry processes the identical slice — and only then turn retries on. For multi-table tasks the honest options are to split them so each task owns one convergent write, or to accept a reconciliation check as the safety net and say so out loud.
Common mistake: Adding retries to make a flaky task green, and finding the duplicates weeks later in a reconciliation.
Follow-up they will ask: Which of those three idempotency techniques fits a task that writes to four tables?
seniorJobs are configured by hand in three workspaces and staging no longer matches production. How do you fix that?
Workflows: a DAG of tasks, not one notebook · tap to open the answer
Say this first: Define the jobs as code in a Databricks Asset Bundle with per-target overrides, and deploy them instead of clicking them.
Remember these
- databricks.yml declares resources plus per-environment targets
- Schedule, concurrency, retries and cluster spec stop drifting
- Each developer gets an isolated deployed copy under a dev target
- Config changes become reviewable diffs instead of UI edits
Why: A bundle is a databricks.yml that declares resources — jobs, pipelines, clusters — plus targets such as dev, staging and prod that override whatever differs between environments. You validate it, deploy it with the CLI against a target, and the workspace's job definition becomes an artifact of a reviewed commit rather than a record of who last edited it in the UI. The drift this eliminates is the dangerous kind: schedule, max concurrent runs, retry counts, cluster spec, notebook path and parameters are precisely the settings that get adjusted during an incident and never copied back. It also gives each developer their own deployed copy under a dev target, which is a better isolation story than sharing one job. The migration is not a rewrite — you can start from an existing job's definition and bring it under the bundle. The point to make in an interview is that a retry policy or a concurrency limit then becomes a pull request, with a diff and a reviewer, instead of a checkbox nobody can audit.
resources:
jobs:
nightly_etl:
max_concurrent_runs: 1
schedule:
quartz_cron_expression: '0 0 2 * * ?'
timezone_id: UTCSenior depth: The failure mode is not YAML preference, it is that nobody can say what production is currently configured to do. Bundles fix that by making the deployed definition derive from a commit, so an incident edit becomes visible as drift rather than becoming the new truth. I would pair it with a rule that UI edits in production are allowed during an incident and must be followed by a PR that reconciles them, and I would deploy from CI so the workspace state and the repository cannot disagree for long.
Common mistake: Keeping the production job in the UI as the source of truth and using the bundle only for dev, which guarantees the two diverge again.
Follow-up they will ask: Someone fixes production by editing the job in the UI during an incident. What is your process for that?
architectOne job has grown to 120 tasks owned by five teams. What is your restructuring, and what do you lose?
Workflows: a DAG of tasks, not one notebook · tap to open the answer
Say this first: Split along ownership and SLA boundaries into separate jobs, wire them with a run-job task or table-arrival triggers, and accept that you lose the single timeline and one-click repair.
Remember these
- Split on ownership, SLA and schedule, not on task count
- A run-job task makes a cross-job dependency explicit
- Table-arrival triggers decouple producers from consumers
- You lose one timeline, one repair, and shared job-cluster economics
Why: A 120-task job has one owner for alerting, one schedule, one concurrency limit and one repair surface, which is the wrong shape when five teams have different SLAs and on-call rotations. The split follows ownership: each team gets a job for its own tasks, with its own retries, notifications and permissions, and the cross-team dependency is expressed explicitly with a run-job task so the parent waits for the child, or with a table-arrival trigger so the downstream job fires when its input is actually ready. What you give up is real. The single run timeline becomes several, so an end-to-end view needs stitching together. A repair no longer covers the whole graph — you repair each job, in order. And tasks that used to share one job cluster now live in different jobs, so you either accept more provisioning or move to serverless. The rule I would state is to keep tasks in one job when they share compute and fail as a unit, and split when they have different owners, different SLAs, or different schedules.
- task_key: run_gold_job
run_job_task:
job_id: 418273
depends_on:
- task_key: silver_publishSenior depth: The boundary I draw is the on-call boundary: whoever gets paged for a task should own the job it lives in. Cross-job dependencies then get expressed through the data rather than through orchestration wherever possible — a consumer triggered by its input table being updated is more robust than a chain of run-job tasks, because the producer can be rescheduled without touching the consumer. The cost I would flag up front is observability: end-to-end latency now has to be measured from table freshness rather than read off one run timeline, so that measurement has to be built before the split, not after.
Common mistake: Splitting by technical layer so every job spans all five teams, which multiplies the coordination instead of reducing it.
Follow-up they will ask: How would you keep an end-to-end SLA visible once the pipeline spans four jobs?
advancedThe pipeline updates tables, but finance wants it to run only after their warehouse export lands, and then to kick off a report. Where does that orchestration live?
Lakeflow: ingest, transform, orchestrate · tap to open the answer
Say this first: In a Lakeflow Job. The pipeline is one task in the job, with the export task before it and the report task after it.
Remember these
- A pipeline is one task inside a job, not a replacement for the job
- depends_on orders the export, the pipeline, and the report
- A failed pipeline task skips everything downstream of it
- Repair run reruns the failed branch, not the tasks that succeeded
Why: A declarative pipeline is the right tool for a graph of tables. It is the wrong tool for 'wait for an external system, then run this, then send that'. A job is a DAG of tasks with depends_on, and a pipeline can be one of those tasks. The export task runs first; the pipeline task depends on it and runs the whole table graph as a unit; the report task depends on the pipeline. If the pipeline fails, downstream tasks are skipped, and a repair run reruns the failed task and anything that was skipped — the export does not have to be fetched again. Putting the export and the email inside the pipeline couples operational chores to the table graph.
Senior depth: Repair marks the export task as already succeeded and starts from the failed pipeline task, then runs the report if the pipeline succeeds. Re-running the export would duplicate data unless that task is idempotent, which is why repair exists.
Common mistake: Encoding the whole company workflow as tables inside one pipeline so there is only one thing to schedule.
Follow-up they will ask: The export task succeeded and the pipeline failed. What exactly does repair run execute?
advancedYou created a materialized view from a SQL warehouse and now there is a pipeline in the workspace you never made. Where did it come from?
Lakeflow: ingest, transform, orchestrate · tap to open the answer
Say this first: Streaming tables and materialized views are implemented on the declarative pipeline engine, so creating one from SQL creates a pipeline to run it.
Remember these
- A DBSQL materialized view is backed by a declarative pipeline
- The pipeline is created for you and shows up in the workspace
- Its event log records incremental versus full refresh
- Deleting the pipeline deletes the refresh, not just a UI entry
Why: CREATE MATERIALIZED VIEW in DBSQL does not store a passive object. It declares a query whose result must be refreshed, and the runtime for that declaration is a Lakeflow Declarative Pipeline, created for you and visible in the pipelines list. That is why the object has an event log, a refresh history, and a schedule, and why serverless shows up on the bill. The same engine was open-sourced into Apache Spark, which is why the syntax is not a proprietary side path. If you delete the pipeline, you delete the thing that refreshes the view. Look at the pipeline's event log when a refresh was slow — it records whether the refresh was incremental or a full recompute.
Senior depth: The event log and the refresh history name the refresh type. A COMPLETE refresh on a view you expected to be incremental is the signal to look at the SQL — usually a non-deterministic function — rather than at the warehouse size.
Common mistake: Deleting the unfamiliar pipeline as clutter and then wondering why the view went stale.
Follow-up they will ask: Where in that pipeline do you see whether yesterday's refresh recomputed the whole table?
seniorGive the rule you actually use for Connect versus Auto Loader versus a pipeline versus a job.
Lakeflow: ingest, transform, orchestrate · tap to open the answer
Say this first: Connect for a SaaS or database source, Auto Loader for files in storage, a declarative pipeline for the table graph, and a job for anything that is not itself a table.
Remember these
- Connect speaks the source system's API and CDC protocol
- Auto Loader speaks files landing in object storage
- A pipeline owns the table-to-table graph and its expectations
- A job owns steps that are not table declarations
Why: Connect owns the protocol of an external system, so use it when the source is Salesforce, SQL Server, or something with its own incremental API. Auto Loader owns files appearing in a bucket, which Connect is not for. Once data is in the lakehouse, a declarative pipeline is where bronze, silver, and gold should live, because the graph, the expectations, and the incremental state are the product. A job is the outer frame: cross-system steps, notifications, conditional branches, and tasks that update something other than a table. The failure mode is using a job of notebooks for the table graph, where order is hand-maintained and a new table means editing the DAG, or using a pipeline for the email step, where a table declaration is the wrong shape.
Senior depth: A job task. The moment the work is 'call this system, handle its errors, write a result', it is orchestration, and burying it in a pipeline hides it from the depends_on graph everyone else reasons about.
Common mistake: Picking whichever of the four the team already knows and stretching it over the other three jobs.
Follow-up they will ask: A notebook that calls an external API and writes a table — pipeline task or job task?
seniorAn expectation is set to drop rows that fail, and a downstream aggregate quietly shrunk by 12%. How do you find that?
Lakeflow: ingest, transform, orchestrate · tap to open the answer
Say this first: The pipeline event log records how many rows each expectation dropped or failed, which is the number the aggregate will not show you.
Remember these
- DROP ROW keeps the pipeline green while deleting data
- The event log has per-expectation pass and fail counts
- Alert on the drop count, not only on pipeline status
- Drop is a business decision and needs an owner
Why: ON VIOLATION DROP ROW keeps the pipeline green while removing data, so the job status is the wrong signal. The event log for the update records each expectation's pass and fail counts. A sudden jump in dropped rows on the silver table is the 12%, and it will not appear as a failure anywhere in the job run. Alert on the drop count, not only on pipeline failure. If the rule should have stopped the line, the expectation belongs on fail, not drop — drop is for rows you have decided are expendable, and that decision needs an owner who looks at the count.
CONSTRAINT valid_id EXPECT (order_id IS NOT NULL)
ON VIOLATION DROP ROWSenior depth: The update fails, downstream tables in the pipeline do not refresh, and the bad rows are listed as the cause. You trade a quiet 12% loss for a loud stop, which is correct when the rows are not expendable.
Common mistake: Treating a green pipeline run as evidence that every source row landed.
Follow-up they will ask: What changes in the event log if you switch that expectation from drop to fail?
architectWhat do you actually gain from these three being one product family, and what do you still have to assign to a team?
Lakeflow: ingest, transform, orchestrate · tap to open the answer
Say this first: One lineage and one event trail from the source system to the gold table. You still need an owner for the connector credentials, the expectations, and the job.
Remember these
- Lineage can run from the source system to the gold table in one view
- Connector credentials are a platform concern, not a wizard default
- Expectations encode domain policy and need a domain owner
- The job should run as a service principal, not an employee
Why: Separate products meant three UIs, three permission models, and a lineage story that stopped at each boundary. With Connect landing into a pipeline that a job triggers, a column can be traced from the source object to the gold table in one place, and a failure can be read in one event log. That does not remove ownership. Connect credentials are a production secret and belong to a platform owner, not to whoever first clicked through the wizard. Expectations encode data-quality policy and belong to the domain team that knows which rows are expendable. The job's run-as identity should be a service principal so a person leaving does not take the pipeline with them. Unification removes the seams. It does not decide who is on call.
Senior depth: The connector's auth is tied to their identity, the job runs as them, and both stop. Recreate the connection under a service principal and set the job's run-as before that week, not during it.
Common mistake: Treating the unification as an org design and leaving every pipeline owned by whoever created it.
Follow-up they will ask: What breaks the week after the person who clicked through the Connect wizard leaves?
advancedYou fix a failing job in the prod UI, save it, and the next morning your fix is gone. What happened?
Asset Bundles: your workspace in git · tap to open the answer
Say this first: The job is bundle-managed, and the next deploy overwrites the workspace with whatever is in git.
Remember these
- Deploy makes the workspace match git, not the other way around
- A UI edit to a bundle-managed job is overwritten on the next deploy
- A job removed from YAML is deleted from the workspace
- An emergency UI fix has to be copied back into the repo immediately
Why: Deploy treats the YAML as the desired state. A change made in the UI is not in git, so the following deploy writes the git version back over it. That includes the schedule, the cluster, the task list, and parameters. The bundle can also remove resources that disappeared from YAML, so a job deleted from the file disappears from the workspace on the next prod deploy. The rule is that a bundle-managed job is edited in the repo and only in the repo. An emergency UI edit has to be copied back into YAML in the same change, or it will not survive the night. This is the trade you accepted to get a reviewable definition.
Senior depth: Bundle-deployed resources carry deployment metadata, and the bundle's own state records what it owns. If the job is in the bundle's resource list, assume the next deploy will win. Check the job's source before an incident edit, not after.
Common mistake: Editing the production job in the UI and considering the incident finished.
Follow-up they will ask: How can you tell whether a given job is bundle-managed before you edit it?
advancedProduction jobs run as the engineer who created them. Why is that a bigger problem than it sounds, and what replaces it?
Asset Bundles: your workspace in git · tap to open the answer
Say this first: When that person leaves, their identity is disabled and every job running as them stops. Production should run as a service principal.
Remember these
- Run-as is the identity whose permissions the job executes with
- A human run-as dies when that account is disabled
- A service principal gets only the grants that job needs
- The bundle sets run_as on the prod target so it is not per-job folklore
Why: The run-as identity is whose permissions the job uses. If it is a human, the job can read whatever they can read, which is usually too much, and it dies when their account is deactivated, which is a strange way to discover a bus factor. A service principal is an application identity with exactly the grants the job needs: USE CATALOG, USE SCHEMA, SELECT, and MODIFY on the tables it writes. The bundle sets run_as on the prod target so it cannot be forgotten per job. CI also deploys as a service principal, separate from the one that runs the jobs if you want a tighter audit trail. Personal access tokens on a laptop are neither of these and should not appear in the pipeline.
targets:
prod:
run_as:
service_principal_name: 00000000-0000-0000-0000-000000000000Senior depth: Prefer two. The deployer can change the workspace definition; the runtime principal can only touch data. One identity that can do both is a larger blast radius and a muddier audit log.
Common mistake: Using a shared human admin account as run-as so that 'someone always has access'.
Follow-up they will ask: Should the principal that deploys be the same one the jobs run as?
seniorTwo engineers deploy the same bundle to the shared dev workspace and each one's jobs disappear. What setting prevents that?
Asset Bundles: your workspace in git · tap to open the answer
Say this first: mode: development prefixes every resource with the deploying user and pauses schedules, so the two deploys do not own the same objects.
Remember these
- development mode prefixes resources with the deploying user
- It pauses schedules so dev deployments do not run overnight
- Two engineers can deploy the same bundle without colliding
- Prod must not use development mode
Why: Without development mode, both engineers deploy a job named daily_revenue to the same workspace, and the second deploy updates or replaces the first engineer's job. With the mode set, each gets [user] daily_revenue, schedules are paused so dev does not fire overnight, and the deployment is marked as development. Prod must not use this mode, or production jobs ship with a username prefix and no schedule. The prefix also makes cleanup honest: databricks bundle destroy -t dev removes that user's deployment rather than the team's shared jobs. A shared dev workspace without this mode is how one engineer's experiment becomes another engineer's missing job.
Senior depth: Destroy removes the resources tracked by that bundle deployment in that target. It does not remove tables the job created, and it does not remove jobs that were never part of the bundle. In dev that is one user's prefixed resources. Pointed at prod, it removes production.
Common mistake: Turning development mode on for the prod target because it seemed safer.
Follow-up they will ask: What does bundle destroy remove, and what does it leave alone?
seniorA deploy to prod failed halfway. Some jobs updated, one did not, and the pipeline is red. What do you do, and what do you not do?
Asset Bundles: your workspace in git · tap to open the answer
Say this first: Fix the YAML and rerun deploy. Deploy is desired-state, so a second successful run converges. Do not patch the remainder by hand in the UI.
Remember these
- A failed deploy can leave some resources updated and some not
- A corrected second deploy converges to the YAML
- A UI patch during the incident will be overwritten
- validate against the prod target before rerunning
Why: A failed deploy can leave the workspace partially updated, because resources are applied in order and a later one failed validation or permissions. The recovery is to correct the cause — usually a permission the service principal lacks, or a variable prod does not define — and deploy again. The second deploy updates what is left and leaves what already matches untouched. Hand-editing the failed job in the UI forks it from git, and the next green deploy will overwrite that edit or fight it. Read the deploy log for the specific resource that failed rather than rerunning blindly, and prefer validate against the prod target locally before pushing the fix.
Senior depth: On the service principal, and in code if you manage grants with the bundle or Terraform. Granting it to your own user fixes your laptop deploy and leaves CI broken.
Common mistake: Finishing the failed deploy by clicking the remaining changes into the workspace.
Follow-up they will ask: The failure was a missing permission. Where do you grant it so the next deploy works?
architectHow do you decide what belongs in the bundle and what stays managed elsewhere?
Asset Bundles: your workspace in git · tap to open the answer
Say this first: The bundle owns everything whose drift would change production behaviour: jobs, pipelines, and their permissions. Tables of data do not belong in a deploy that can destroy them.
Remember these
- Jobs, pipelines, schedules, and runtime grants belong in the bundle
- Tables of data do not belong to a tool with destroy semantics
- Schemas are a judgement call because a destroy could drop them
- CI should be able to redeploy orchestration without being able to drop data
Why: Jobs, pipelines, job clusters, schedules, and the grants the runtime principal needs are definition, and definition belongs in reviewable YAML. A catalog full of terabytes does not: deploy and destroy semantics that are correct for a job are dangerous for a table, and data lifecycle is a different change process. Schemas are a judgement call — a bundle-managed schema is convenient and also something a bad destroy could drop, so many teams create schemas out of band and only reference them. The split to write down is: the bundle is allowed to create and delete the orchestration, and it is not allowed to be the thing that drops prod.gold. That boundary is what lets you give CI an aggressive deploy role without giving it the keys to the data.
Senior depth: If the answer is 'drop the gold catalog', the boundary is wrong. Move data lifecycle out of the bundle until the worst case is 'prod jobs disappear and can be redeployed from git'.
Common mistake: Managing production tables in the same bundle that manages jobs, because it is convenient to have one tool.
Follow-up they will ask: What is the worst thing a mistyped bundle destroy -t prod could do in your setup?
advancedWhat is actually different between ai_query and a Python UDF that calls the same endpoint?
AI functions and Vector Search in SQL · tap to open the answer
Say this first: The engine batches and distributes ai_query, and the endpoint it calls is a governed Unity Catalog object. A UDF is opaque per-row code running in a Python worker.
Remember these
- ai_query is batched and planned; a Python UDF is opaque per-row code
- The endpoint is a Unity Catalog object with grants and lineage
- AI Gateway adds rate limits, logging, and usage tracking
- Batching improves throughput and does not change the per-row price
Why: A Python UDF sends rows to a Python process, serializes each one, and runs your loop, and the optimizer cannot see inside it. ai_query is planned by the engine: it batches many rows into one request, runs those batches across the cluster, and retries against the serving endpoint. The endpoint is a Unity Catalog object, so grants decide who may call it, lineage records that the table was produced from it, and audit records the calls. AI Gateway can add rate limits, payload logging, and usage tracking in front of that endpoint. None of that exists for a URL hardcoded inside a UDF. The result is still one inference per row — batching changes throughput, not the bill's slope.
Senior depth: No. Batching amortises request overhead, so a million rows finish sooner and waste fewer tokens on repeated prompts, but you still pay for every row's input and output tokens. The way to lower the bill is to call the model on fewer rows.
Common mistake: Wrapping the endpoint in a UDF for flexibility and losing batching, grants, and lineage.
Follow-up they will ask: Does batching mean a million rows costs the same as a thousand?
advancedThe same ai_query call can hit a pay-per-token foundation model, a provisioned throughput endpoint, or a custom model you trained. What changes in the SQL, and what changes in the bill?
AI functions and Vector Search in SQL · tap to open the answer
Say this first: The SQL does not change — you name an endpoint. The bill changes from a per-token price to capacity you pay for whether or not queries arrive.
Remember these
- The SQL names an endpoint; it does not name a billing model
- Pay-per-token is cheap when idle and expensive on a large backfill
- Provisioned throughput is reserved capacity you pay for either way
- A custom served model is the same SQL with your own latency and scale policy
Why: ai_query takes an endpoint name, and any of those three can sit behind one. A foundation-model endpoint bills per token, so idle costs nothing and a big backfill costs a lot, all at once. Provisioned throughput reserves capacity, so latency is predictable and you pay for the reservation even on a quiet day. A custom model on Model Serving is your artifact, your latency, and your scale-to-zero or always-on choice. Picking provisioned throughput for a nightly batch that runs for twenty minutes means paying for capacity the other twenty-three hours and forty minutes. Picking pay-per-token for an interactive assistant means latency and rate limits you do not control. The SQL is the wrong place to encode that choice; the endpoint configuration is the right one.
Senior depth: Provisioned throughput or a warm custom endpoint, because a person is waiting and a cold pay-per-token call adds latency and can be rate-limited mid-session. The nightly batch stays on pay-per-token unless its volume justifies a reservation.
Common mistake: Provisioning throughput because it sounds faster, for a workload that runs once a night.
Follow-up they will ask: Which of the three would you put behind an interactive assistant, and why?
seniorYou built a vector index yourself in a separate database and a second job embeds new rows into it. What does Vector Search with Delta Sync remove from that design?
AI functions and Vector Search in SQL · tap to open the answer
Say this first: The sync job. Delta Sync tracks the source Delta table through change data feed and updates the index as rows are inserted, changed, and deleted.
Remember these
- Delta Sync updates the index from the source table's change data feed
- Inserts, updates, and deletes propagate without a job you maintain
- Queries combine vector similarity with keyword filters
- The index is a Unity Catalog object with its own grants
Why: A self-managed index is correct until the table changes, and then freshness is a pipeline you operate: detect changes, embed them, upsert them, and delete the ones that disappeared. In Delta Sync mode the index is defined on a Delta table column, Databricks can compute the embeddings, and change data feed drives incremental updates. Querying the index is a mix of vector similarity and keyword filters, so you can restrict by tenant or date as well as by meaning. The index is a Unity Catalog object, so grants decide who can query it. You can still supply your own vectors if you have an embedding model you do not want replaced. What you give up is control of the index internals.
CREATE VECTOR SEARCH INDEX support.ticket_index
ON support.tickets (ticket_id)
WITH (embedding_column => comment, sync_mode => 'CONTINUOUS')Senior depth: The change is captured by change data feed, re-embedded, and upserted into the index. Until that sync completes, queries see the previous vector. Sync lag is the failure mode to monitor, not a missing cron.
Common mistake: Keeping a separate embedding job 'so we control it' and then debugging staleness every week.
Follow-up they will ask: A row is updated in the source table. What has to happen before a query sees the new text?
seniorA materialized view calls ai_query and its refresh is fully recomputing every hour, and the labels change slightly each time. What two properties of the function cause that?
AI functions and Vector Search in SQL · tap to open the answer
Say this first: The output is non-deterministic, so incremental refresh cannot reuse prior results, and every refresh calls the model again for every row.
Remember these
- Non-deterministic expressions block incremental refresh
- Each full refresh calls the model for every row again
- Labels drift between refreshes even when the source did not
- Store inference results as data and aggregate those
Why: An incremental refresh reuses rows whose inputs did not change. A non-deterministic expression has no stable output for a given input, so the planner cannot prove that yesterday's label is still valid and falls back to recomputing the view. Recomputing calls the endpoint for the whole table, every hour, which is the bill. The labels also drift: the same ticket can classify differently on Tuesday, so downstream aggregates move without any source data moving. Keep model inference in a table you append to, with the label stored as data, and let the materialized view aggregate that table. Inference becomes a deliberate write instead of a side effect of refresh.
Senior depth: Incrementally re-infer only the changed keys, using the source table's change data feed, and merge those labels. The view then refreshes over a deterministic table and can be incremental again.
Common mistake: Putting ai_query inside a materialized view because the syntax allows it.
Follow-up they will ask: How do you keep the stored labels from going stale when the source row changes?
architectWho should be allowed to send production table data to a foundation-model endpoint, and what controls exist beyond a wiki page?
AI functions and Vector Search in SQL · tap to open the answer
Say this first: The endpoint and the index are Unity Catalog objects, so grants decide who calls them, and AI Gateway records and rate-limits what was sent.
Remember these
- A serving endpoint is a Unity Catalog object with grants
- Lineage shows which tables were produced by which endpoint
- AI Gateway rate-limits calls and can log payloads
- Mask sensitive columns before they are interpolated into a prompt
Why: A URL in a notebook plus a personal token is invisible: anyone who can run the notebook can send any column anywhere, and you will not know. An endpoint registered in Unity Catalog is grantable, so only a specific group can invoke it, and lineage shows which tables were produced using it. AI Gateway in front of the endpoint adds rate limits, usage accounting, and payload logging, which is the record you need when someone asks what customer text left the account. Combine that with column masks so the PII columns are masked before they ever reach the prompt. The remaining decision is contractual: which provider, which region, and whether prompts are retained, and that one is not a Unity Catalog setting.
Senior depth: The model sees whatever the caller sees. If the caller is subject to the mask, the prompt contains the masked value. If the view or job runs as an owner who is exempt from the mask, the model receives the raw value, which is usually not what anyone intended.
Common mistake: Relying on a guideline that says not to paste customer data into a model.
Follow-up they will ask: A masked column is used inside ai_query. Who sees the unmasked value — the model, or only the function?
Practice by topic
- Databricks Architecture
- Compute types
- SQL Warehouses
- Unity Catalog
- Delta Lake
- Time Travel & VACUUM
- Medallion Architecture
- Delta MERGE
- OPTIMIZE & Clustering
- Liquid Clustering
- Auto Loader
- Delta Live Tables
- Workflows
- Photon
- UniForm and Iceberg
- Row filters and column masks
- Delta Sharing and Clean Rooms
- System tables
- Predictive Optimization
- Serverless compute
- Streaming tables and materialized views
- Lakeflow
- Asset Bundles and CI/CD
- AI functions and Vector Search
