In this post we are going to cover the Fabric lakehouse, Delta tables and the SQL analytics endpoint: Tables vs Files, why everything is Delta, why the endpoint misses a new column or rejects an UPDATE, and how to keep tables fast with V-Order, optimize write, OPTIMIZE and VACUUM. These are 7 real Microsoft Fabric interview questions, asked the way they come up on a real project, and the last one is a scenario where you lay out sales data in bronze, silver and gold.
The lakehouse is where most Fabric data engineering happens. Spark writes Delta tables into it, and the SQL analytics endpoint and Power BI read them. Interviewers ask about it because the everyday problems live here: files that don't show up as tables, a schema change the endpoint can't see yet, and slow reports on a table with thousands of small files.
Part 3 of the Real Time Microsoft Fabric Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Question 1: A new engineer drops CSV files into a lakehouse and asks why they don't show up as tables. What is the difference between Tables and Files?
- Question 2: Your manager asks why everything in Fabric is Delta. What does Delta Lake give you that plain Parquet doesn't?
- Question 3: The SQL analytics endpoint doesn't show the new column your notebook just added. Why, and what do you do?
- Question 4: An analyst tries to UPDATE a table through the SQL analytics endpoint and gets an error. Why, and where should the change happen?
- Question 5: Power BI reports on a lakehouse table are slow. What are V-Order and optimize write, and when do you turn them on?
- Question 6: A table has fifty thousand small files and queries crawl. How do OPTIMIZE and VACUUM fix it, and what is the risk?
- Scenario 7: You need to organize sales data from raw files to reporting tables. How do you lay out bronze, silver and gold in Fabric?
The short answer
A lakehouse has a managed Tables area of Delta tables and an unmanaged Files area for raw files. The SQL analytics endpoint is read-only T-SQL over those Delta tables, kept current by a metadata sync. Spark owns the writes, and you keep tables fast with V-Order, optimize write, OPTIMIZE and VACUUM.
Keep that in mind. Every question below is one piece of it.
Question 1: A new engineer drops CSV files into a lakehouse and asks why they don't show up as tables. What is the difference between Tables and Files?
A lakehouse has two areas, and they behave differently.
- Tables is the managed area: Delta tables, registered in the metastore.
- Files is the unmanaged area: a landing zone for raw CSV, JSON, Parquet or images.
So CSV files under Files are just files. Only Delta tables show up as tables, and only Delta tables appear in the SQL analytics endpoint. You can't query those CSV files there.
The fix is one cell in a notebook. Let's read the CSV files with Spark and save them as a table:
# read the raw CSV files from the Files area
df = (spark.read.option("header", "true")
.csv("Files/landing/sales/*.csv"))
# Spark default format in Fabric is Delta
df.write.mode("overwrite").saveAsTable("sales")In Fabric, Spark's default format is Delta, so saveAsTable writes a Delta table. It lands under Tables and is registered automatically, and the SQL analytics endpoint can see it.
I keep raw files in Files as an untouched landing zone, and everything the business queries is a Delta table under Tables. The mistake I see is pointing reports at raw files and wondering why the endpoint can't see them.
Question 2: Your manager asks why everything in Fabric is Delta. What does Delta Lake give you that plain Parquet doesn't?
A Delta table is Parquet files plus a transaction log. The Parquet files hold the data, in columnar format. The log records every commit, in order, so every engine knows exactly which files make up the current version.
That log is what plain Parquet doesn't have. Let's see what it gives you:
- ACID transactions with optimistic concurrency, so a failed write never leaves half a table.
- MERGE, UPDATE and DELETE, to change rows in place.
- Time travel: see the history, query an older version, or restore one.
- One copy, every engine: Spark, the SQL analytics endpoint and Power BI Direct Lake all read the same copy in OneLake.
Here is time travel in Spark SQL. First the history, then an older version, then a restore:
DESCRIBE HISTORY sales;
SELECT * FROM sales VERSION AS OF 3;
RESTORE TABLE sales TO VERSION AS OF 3;That last point is also why Fabric standardized on Delta. No engine needs its own copy.
New tables get deletion vectors by default and a newer Delta protocol. Newer Delta features there are experimental and Spark only, and protocol upgrades can't be reversed. So don't turn on features that other engines can't read.
Question 3: The SQL analytics endpoint doesn't show the new column your notebook just added. Why, and what do you do?
The endpoint doesn't read the Delta log on every query. A background metadata sync copies the table schema into the endpoint. Your notebook changed the Delta table, and the endpoint simply hasn't caught up yet.
Say the notebook ran this:
-- notebook cell, Spark SQL
ALTER TABLE sales ADD COLUMNS (discount DECIMAL(10,2));The Delta table has the new schema in its log, but the endpoint still shows the old one. Here is why it can lag:
- Sync lag: usually under a minute, but it can be minutes.
- Sync paused: the sync stops after 15 minutes without query activity.
- Many lakehouses: there is one sync process per workspace, so many lakehouses in one workspace means more lag.
The fix is to refresh the metadata. Use the refresh button on the endpoint, the REST API to refresh SQL analytics endpoint metadata, or in a pipeline, the refresh SQL endpoint activity, which is in preview. I refresh right after the load, so reports never see a stale schema.
If the column still doesn't appear, check its type. Delta types the endpoint doesn't support, like timestamp without time zone, are dropped as columns.
Spark views are never visible to the endpoint.
Question 4: An analyst tries to UPDATE a table through the SQL analytics endpoint and gets an error. Why, and where should the change happen?
The SQL analytics endpoint is read-only over the lakehouse tables. It runs on the same engine as the Fabric warehouse, but the tables belong to the lakehouse, and Spark owns the writes.
So this fails on the endpoint:
-- SQL analytics endpoint, T-SQL
UPDATE dbo.sales
SET status = 'Closed'
WHERE order_id = 1001;Insert, update, delete and create table all fail there. What you can create on the endpoint is views, procedures, functions and schemas. That is useful for analysts: a reporting view, or a procedure that reads data, without touching the tables.
The change itself happens in Spark. Let's run the same update in a notebook with Spark SQL:
-- notebook cell, Spark SQL on the Delta table
UPDATE sales
SET status = 'Closed'
WHERE order_id = 1001;This works. Delta writes a new version of the table, and the endpoint picks it up after the metadata sync. A merge in PySpark works the same way.
If the team really needs to write with T-SQL, that is a warehouse:
| SQL analytics endpoint | Warehouse | |
|---|---|---|
| TABLES | read-only, written by Spark | full T-SQL DML |
| YOU CREATE | views, procedures, functions | tables, views, procedures |
| CHANGE DATA | in a notebook or pipeline | T-SQL, including MERGE |
Read in the endpoint, and change data in the pipeline, never by hand.
Question 5: Power BI reports on a lakehouse table are slow. What are V-Order and optimize write, and when do you turn them on?
V-Order is a write-time layout for Parquet: it sorts, distributes row groups, encodes and compresses. The files are still open Parquet. Optimize write does a shuffle before the write, so you get fewer, larger files.
| V-Order | Optimize write | |
|---|---|---|
| WHAT | sorted, encoded, compressed Parquet | fewer, larger files |
| COST | about 15 percent more write time | a shuffle before the write |
| HELPS | Power BI Direct Lake reads | small inserts, MERGE, partitions |
Both cost something. V-Order adds about 15 percent write time on average, and optimize write adds a shuffle. That is why newer workspaces have both off by default, with the writeHeavy resource profile.
V-Order mainly helps Power BI Direct Lake. For the SQL analytics endpoint, it generally isn't required. So for gold tables that serve reports, turn them on. You have two ways: the readHeavyForPBI profile for the whole session, or table properties on just that table.
# whole session: read-heavy profile for Power BI
spark.conf.set("spark.fabric.resourceProfile",
"readHeavyForPBI")
# or just one gold table
spark.sql("""ALTER TABLE fact_sales SET TBLPROPERTIES (
'delta.parquet.vorder.enabled' = 'true',
'delta.autoOptimize.optimizeWrite' = 'true')""")Existing files don't change by themselves, so after turning V-Order on, run OPTIMIZE with V-Order once.
A session setting of true wins, even when the table property says false. In a real project, I leave bronze on the defaults and turn both on for gold.
Question 6: A table has fifty thousand small files and queries crawl. How do OPTIMIZE and VACUUM fix it, and what is the risk?
First, where small files come from: frequent micro-batch appends, partitioning on a column with too many values, and merges, which rewrite files. Every query then opens thousands of files, and that hurts Spark, the endpoint sync and Direct Lake.
Let's fix it in two steps.
OPTIMIZE compacts small files into larger ones, about 1 GB by default, and with the VORDER keyword it applies V-Order too. But the old small files stay, because older table versions still point to them.
VACUUM deletes unreferenced files older than the retention period, 7 days by default. It saves storage, but it doesn't speed up queries, and it is Spark only.
-- compact small files, apply V-Order
OPTIMIZE sales VORDER;
-- delete unreferenced files older than 7 days
VACUUM sales RETAIN 168 HOURS;
-- prevent it: auto compaction after small writes
ALTER TABLE sales SET TBLPROPERTIES
('delta.autoOptimize.autoCompact' = 'true');Run both in a notebook, or right-click the table in the portal and choose Maintenance.
Now the risk, and it is VACUUM:
- Time travel only works within retention. Once files are gone, you can't time travel or restore past that window.
- Below 7 days fails by default, unless you turn off the safety check.
- Spark only, not the SQL endpoint.
Also schedule OPTIMIZE away from merges, because they can conflict.
Use optimize write and auto compaction on ingestion, and liquid clustering instead of over-partitioning.
Scenario 7: You need to organize sales data from raw files to reporting tables. How do you lay out bronze, silver and gold in Fabric?
Medallion is Microsoft's recommended design for Fabric. I put each layer in its own lakehouse, and preferably its own workspace, so access is governed per layer.
Let's go through the layers in order.
- Bronze keeps the raw sales files in their original format, append only. Small files are fine here. If the files already sit in ADLS or S3, a shortcut is better than a copy.
- Silver is clean Delta: typed, deduplicated and merged by key. Microsoft recommends liquid clustering over partitioning for silver and gold. Notebooks do the merges, and a pipeline runs them in order.
- Gold holds the reporting tables, facts and dimensions, with large files and V-Order, because a Direct Lake semantic model reads them.
| Bronze | Silver | Gold | |
|---|---|---|---|
| FORMAT | original files or shortcut | Delta, typed, deduplicated | Delta, reporting tables |
| FILES | small is fine | medium | large, V-Order |
| LAYOUT | append only | liquid clustering | liquid clustering |
One thing an older answer gets wrong: default semantic models are retired, so you build the Direct Lake model on gold yourself.
For simple silver and gold logic, they let you declare it in Spark SQL, and Fabric works out the refresh. They need a schema-enabled lakehouse.
Key points to remember
- Tables = managed Delta; Files = raw landing zone
- Delta = Parquet + transaction log: ACID, MERGE, time travel
- SQL endpoint: read-only, metadata sync, refresh after loads
- V-Order and optimize write on for gold tables behind Direct Lake
- OPTIMIZE compacts; VACUUM cleans up, but limits time travel
Part 4: Spark, Notebooks and Environments
Part of the Real Time Microsoft Fabric Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Microsoft Learn page for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.