Delta Lake

Delta Tables in Microsoft Fabric for SQL Server Engineers

What a Delta table really is, how it differs from a SQL Server table, and what to understand about writes, keys, time travel, maintenance and the SQL analytics endpoint before using Fabric Lakehouse tables in production.

Por JaviPublicado 19 min de lecturaPrincipiante

If you come from SQL Server, a Fabric Lakehouse table looks reassuringly familiar. It has columns and types, you can SELECT from it with T-SQL, and the Lakehouse explorer shows it in a neat list next to the others. It is easy to carry over the rest of your instincts too: the table must have indexes somewhere, the primary key must stop duplicates, UPDATE must change a row in place, and the SQL endpoint must accept writes.

None of those is true, and most production problems in a first Fabric project trace back to one of them. (For the same translation one level up, from instance and database to capacity, workspace and Lakehouse, see SQL Server to Microsoft Fabric: What Maps to What?.)

A SQL Server table is owned by a database engine. The engine controls the pages on disk, writes every change to its transaction log, takes locks, enforces constraints and maintains indexes. You never touch the files; you talk to the engine.

A Delta table is a different kind of thing. It is a folder of Parquet data files plus a transaction log (_delta_log) that records which files make up the table at each version. There is no single engine that owns it. Spark, the SQL analytics endpoint, Power BI and other engines all read the same files and the same log, and any engine that follows the Delta protocol can write to them.

That is not “SQL Server on files”. It is an open table format with transactional guarantees, designed for large analytical reads and batch writes. This article builds the mental model you need before you use Lakehouse tables in production: how Delta stores data, what happens on writes, what you do and do not get compared with SQL Server, and what the SQL analytics endpoint can and cannot do.

It assumes you have a Lakehouse and know how to run a notebook. If not, start with Create Your First Fabric Lakehouse and Create Your First Fabric Notebook. For where the Lakehouse fits in the platform, see What Is Microsoft Fabric?.

Fabric behavior in this article was checked against Microsoft Learn in October 2026.

Why Delta feels different to SQL Server engineers

The shift is about who owns the table.

  • SQL Server tableOwned by one engine
    1. SQL Server table
    2. Database engineLocks, constraints, indexes
    3. PagesEngine-managed storage
    4. Transaction logEvery change, for recovery
  • Delta tableFiles plus a log in OneLake
    1. Delta table
    2. Parquet filesImmutable, columnar data
    3. _delta_logOrdered commits of file changes
    4. Table versionsReaders reconstruct the current state
Two mental models of a table. Not a one-to-one mapping. SQL Server tables live inside one engine; a Delta table is files plus a log that several engines read and write.

In SQL Server, the engine is the table’s single source of truth. In Delta, the log is the source of truth, and engines are clients of it. Several consequences follow:

  • Files are immutable. A Parquet file is written once and never modified. Changing data means writing new files and recording in the log that old ones no longer count.
  • Everything happens at file granularity. The unit of storage, of change and of skipping data during a read is a file, not an 8 KB page or a row.
  • Constraints, indexes and statistics are not engine-owned features you can assume. Some exist in other forms, some are informational only, and some do not exist.
  • Different engines see the same data. A table written by a Spark notebook is read by T-SQL through the SQL analytics endpoint and by Power BI in Direct Lake mode, with no copy.

A Delta table is Parquet files plus a transaction log

Look at a Lakehouse table in OneLake and you find something like this:

Tables/sales_orders/
├── _delta_log/
│   ├── 00000000000000000000.json
│   ├── 00000000000000000001.json
│   ├── 00000000000000000002.json
│   └── ...
├── part-00000-3f2a...-c000.snappy.parquet
├── part-00001-91bc...-c000.snappy.parquet
└── part-00002-d07e...-c000.snappy.parquet
  • Parquet is an open, columnar file format. Each file stores a chunk of rows organized by column, compressed and encoded per column, with metadata such as column types and value ranges. Columnar storage is why analytical queries that read a few columns of many rows are efficient. It is also why changing a single row is comparatively expensive.
  • _delta_log contains one JSON file per commit, numbered in order. Each commit records actions: which data files were added, which were removed, schema changes, table properties, and per-file statistics. Periodically, Delta also writes checkpoint files that summarize the log so readers do not have to replay every commit.
  • A version is the state of the table after a given commit. Version 0 is the creation, version 1 the next write, and so on.
  • The current state is reconstructed by reading the latest checkpoint and the commits after it. That produces the list of files that are part of the table right now. A reader then reads only those files. Files that a later commit removed stay on disk, invisible to current readers, until maintenance deletes them.

Commits are atomic: a write either appears completely as a new version or not at all. That is where Delta’s ACID guarantees come from, and it is why a half-finished Spark job does not leave a half-written table.

SQL Server table vs Delta table

Area SQL Server table Delta table in a Fabric Lakehouse
Storage 8 KB pages in database files, managed by the engine Immutable Parquet files in OneLake, plus _delta_log
Transaction log Records every modification for recovery and rollback; truncated by checkpoints or log backups depending on recovery model Records which files make up each table version; also the basis for time travel; retention controlled by table settings
Inserts Rows written into pages; may cause page splits New Parquet files added and committed
Updates Rows modified in place (or moved), logged row by row Affected files rewritten with the new values, or rows marked deleted through deletion vectors where the table uses them; new files committed
Deletes Rows removed from pages, logged Affected files rewritten without the rows, or rows marked through deletion vectors
Indexes Clustered and nonclustered B-tree indexes, columnstore indexes No B-tree indexes. Performance comes from file layout: per-file statistics for data skipping, partitioning, clustering, file sizes, V-Order
Keys and constraints Primary keys, unique and foreign keys, check and default constraints, all enforced Delta can enforce NOT NULL and CHECK constraints for supporting writers. No enforced primary, unique or foreign keys. The SQL analytics endpoint accepts them only as NOT ENFORCED
Schema enforcement Writes must match the table definition Writes must match the table schema unless schema evolution is explicitly enabled
Statistics Histograms and density, created and updated by the engine for the optimizer Per-file statistics (such as min and max values) in the log for data skipping; the SQL analytics endpoint maintains its own statistics automatically
Transactions Multi-statement, multi-table transactions with BEGIN TRAN / COMMIT Each write to a table is one atomic commit. No multi-table transactions across Lakehouse tables in Spark
Time travel Not built in for ordinary tables (temporal tables and snapshots are separate features) Query earlier versions while the files and log entries are retained
Concurrency Locking, plus optional row versioning (RCSI, snapshot isolation) Optimistic concurrency: readers see a consistent version; writers commit if no conflicting commit happened, otherwise they fail and must retry
Maintenance Index rebuilds and reorganizes, statistics updates, integrity checks, log management Compaction (OPTIMIZE), cleanup of unreferenced files (VACUUM), optional V-Order
Query engines SQL Server only Spark, SQL analytics endpoint, Power BI Direct Lake, Dataflows, pipelines and other Delta-compatible engines

What happens when you INSERT

An insert in Delta, whether a Spark INSERT INTO or a DataFrame written in append mode, does three things:

  1. Writes the new rows into one or more new Parquet files. How many depends on the data volume and how Spark partitions the work.
  2. Prepares a commit listing those files as added, with their statistics.
  3. Atomically writes the commit as the next entry in _delta_log. Only then do readers see the new rows.

Existing files are never touched. That makes appends cheap and safe to run alongside readers. It also explains a common problem: many small appends produce many small files, and every reader pays to open them. A pipeline that appends a few hundred rows every five minutes builds up thousands of tiny files within weeks. That is what compaction is for.

What happens when you UPDATE or DELETE

This is where page-oriented intuition misleads most.

In SQL Server, an UPDATE of one row finds the row’s page, changes it, and logs the change. The cost scales roughly with the rows touched.

In Delta, files are immutable, so an update or delete has to:

  1. Find the files that contain matching rows. Per-file statistics and partitioning help skip files that cannot match.
  2. For each affected file, write a new file with the changed rows (or without the deleted rows) plus all the unchanged rows from the same file, which is known as copy-on-write. Alternatively, when the table uses deletion vectors, Delta records which rows in the existing file are no longer valid instead of rewriting it straight away.
  3. Commit: old files removed, new files added.

So the cost scales with the files touched, not the rows. Updating one row in a 200 MB file can mean rewriting most of that file. Updating ten scattered rows can touch ten files. That is the main reason row-by-row update patterns from OLTP habits perform badly on Delta, and why a single set-based MERGE per batch beats thousands of individual statements.

Deletion vectors reduce the write cost of updates and deletes, at the price of extra work for readers until the table is compacted. They are an available Delta feature in current Fabric Spark runtimes and enabled by default for new tables in Runtime 2.0. Whether a given table uses them depends on how it was created.

Append vs overwrite vs MERGE

Most Delta writes in a data platform are one of three patterns:

Pattern What it does Use it for Watch out for
Append Adds new files; nothing existing changes Immutable events, Bronze landing tables, logs Rerunning the same batch duplicates it
Overwrite (whole table or a slice with replaceWhere) Replaces all files, or the files matching a predicate, in one commit Small tables, full rebuilds, recomputing a date range Overwriting the whole table when only a slice changed
MERGE Matches source rows to target rows by key; updates, inserts or deletes Current-state tables (upserts), applying change batches Duplicate keys in the source fail the MERGE; cost grows with the files touched

The examples below use the sales_orders table from the End-to-End Sales Analytics lab dataset, keyed on sales_order_id, with customer_id, order_timestamp, status and grand_total. These are the same names used in Incremental Loads in Microsoft Fabric.

PySpark
from delta.tables import DeltaTable
from pyspark.sql import functions as F

# new_orders and changed_orders are DataFrames with the sales_orders columns

# 1. Append: add new, immutable rows
new_orders.write.format("delta").mode("append").saveAsTable("sales_orders")

# 2. Overwrite a slice: recompute one month and replace only those rows
(changed_orders
    .write.format("delta")
    .mode("overwrite")
    .option("replaceWhere",
            "order_timestamp >= '2026-07-01' AND order_timestamp < '2026-08-01'")
    .saveAsTable("sales_orders"))

# 3. MERGE: upsert by business key
target = DeltaTable.forName(spark, "sales_orders")
(target.alias("t")
    .merge(changed_orders.alias("s"), "t.sales_order_id = s.sales_order_id")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute())
PySpark23 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

Each is a single commit, so each is atomic. The choice between them is about correctness on rerun and cost, not syntax. A MERGE against a table with billions of rows deserves its own design; Delta Lake MERGE performance patterns covers it. For the full set of read, write and MERGE patterns, keep the Delta read and write cheat sheet nearby.

Create and query a Delta table in Fabric

In a notebook attached to your Lakehouse, with the lab’s sales_orders.csv uploaded to Files/raw/:

PySpark
from pyspark.sql import functions as F

orders = (
    spark.read.option("header", True).csv("Files/raw/sales_orders.csv")
    .select(
        "sales_order_id",
        "customer_id",
        F.to_timestamp("order_timestamp").alias("order_timestamp"),
        "channel",
        "status",
        F.col("grand_total").cast("decimal(18,2)").alias("grand_total"),
    )
)

orders.write.format("delta").mode("overwrite").saveAsTable("sales_orders")
PySpark15 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

Delta is the default table format in Fabric Lakehouses, so saveAsTable creates a managed Delta table under Tables/. The Lakehouse explorer discovers it automatically.

Query it with Spark SQL in the notebook:

%%sql
SELECT CAST(order_timestamp AS DATE) AS order_date,
       COUNT(*)                      AS orders,
       SUM(grand_total)              AS revenue
FROM sales_orders
GROUP BY CAST(order_timestamp AS DATE)
ORDER BY order_date;
Spark SQL7 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

And with T-SQL through the Lakehouse’s SQL analytics endpoint, from the Fabric query editor or SSMS:

Consulta SQL: Create and query a Delta table in FabricT-SQL (Fabric Warehouse)
SELECT TOP (10) sales_order_id, customer_id, status, grand_total
FROM dbo.sales_orders
ORDER BY grand_total DESC;
T-SQL (Fabric Warehouse)3 líneasVista de solo lectura. No está conectada a ninguna base de datos.

Same files, two engines, no copy. To see the physical side, DESCRIBE DETAIL sales_orders in Spark SQL reports the table’s location, file count and size.

Create Your First Fabric Notebook walks through notebook basics, and the Microsoft Fabric notebook tutorial takes it to production patterns. For how Parquet and Delta compare with CSV and JSON, see the file formats cheat sheet.

A note on table types. Tables created with saveAsTable in the Lakehouse are managed, and their files live under Tables/. Delta tables created with Spark at another location, such as under Files/, are external. They are not visible to the SQL analytics endpoint. To expose Delta data stored elsewhere, create a shortcut in the Tables section instead.

Schema enforcement and evolution

Schema enforcement means a write must match the table’s schema. If changed_orders has a column the table does not, or a column with an incompatible type, the write fails instead of silently corrupting the table. This is the same protective instinct as a SQL Server table rejecting an INSERT with the wrong columns, and it is on by default.

Schema evolution means deliberately allowing the schema to change as part of a write. The simplest form is adding new columns on append:

PySpark
(orders_with_new_column
    .write.format("delta")
    .mode("append")
    .option("mergeSchema", "true")
    .saveAsTable("sales_orders"))
PySpark5 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

Treat schema evolution as a design decision, not a convenience flag to add whenever a write fails. A column renamed upstream should not quietly become a new column with nulls in every historical row. Renames, type changes and column drops raise further questions, including whether every engine reading the table supports the table features they need. They belong in your pipeline’s change process.

Versions and time travel

Every commit creates a version, and the log keeps the history. In a Fabric notebook:

%%sql
DESCRIBE HISTORY sales_orders;
Spark SQL2 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

returns one row per version: its number, timestamp, operation (WRITE, MERGE, OPTIMIZE…), operation parameters and metrics such as files and rows written.

You can query an earlier version, which Delta calls time travel:

%%sql
SELECT COUNT(*) FROM sales_orders VERSION AS OF 3;

SELECT COUNT(*) FROM sales_orders TIMESTAMP AS OF '2026-10-23T08:00:00';
Spark SQL4 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

In PySpark, the equivalent is the versionAsOf or timestampAsOf read option. Time-travel reads do not change the table. To make an earlier version current again, Delta has a separate RESTORE command, which creates a new commit.

Time travel only works while the old files and log entries still exist. VACUUM deletes data files that are no longer referenced and older than the retention threshold, and log retention removes old commit entries. History is a rolling window, not an archive.

On the T-SQL side, the SQL analytics endpoint has its own time-travel syntax (OPTION (FOR TIMESTAMP AS OF ...)). For Lakehouse tables, at the time of writing, it was in preview and only available on endpoints using the new metadata sync. Use Spark for Delta time travel unless you have confirmed the endpoint feature in your environment.

OPTIMIZE, VACUUM and V-Order

Delta tables need maintenance, but it is different from index maintenance. Three concepts are enough to start:

  • OPTIMIZE compacts many small files into fewer, larger ones (bin-compaction), and can reorganize data within files. Readers then open fewer files and skip more data.

    %%sql
    OPTIMIZE sales_orders;
    Spark SQL2 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.
  • VACUUM permanently deletes data files that the current table version no longer references and that are older than the retention threshold. In Fabric, the default retention threshold is seven days, and portal and API maintenance requests reject shorter intervals unless you explicitly disable the retention check. Shorter retention saves storage but shortens time travel and can break long-running readers that still need older files.

    %%sql
    VACUUM sales_orders;  -- default retention
    Spark SQL2 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.
  • V-Order is a Fabric-specific, write-time optimization of Parquet file layout (sorting, encoding and compression) that can speed up reads across Fabric engines. The files remain standard Parquet. It costs write time, and Microsoft quotes around 15% on average. It is disabled by default in newly created workspaces; workspaces created before April 2025 may still have it on. You can enable it per session, per table or per write, or apply it during OPTIMIZE.

You can run maintenance from the Lakehouse explorer (the table’s Maintenance action), from notebooks, or on a schedule with the pipeline Lakehouse Maintenance activity, which was in preview at the time of writing. When and how often to run it, and how it interacts with Direct Lake and the SQL endpoint, is covered in Delta Table Maintenance in Microsoft Fabric. For now, the point is that no maintenance is a decision with consequences: small files accumulate and storage grows.

What the SQL analytics endpoint sees

Every Lakehouse automatically gets a SQL analytics endpoint. For SQL Server engineers it is the most familiar surface in Fabric, and the one with the most dangerous assumptions.

What it is:

  • A read-only T-SQL surface over the Lakehouse’s Delta tables, running on the same engine as Fabric Data Warehouse.
  • It discovers Delta tables in the Tables section automatically, including shortcuts to Delta tables elsewhere, and keeps its metadata in sync as tables change. Sync is automatic but not instantaneous, and it can be refreshed manually.

What SQL users can do:

  • Run SELECT queries over any Lakehouse Delta table, including joins across shortcut tables.
  • Create views, functions and stored procedures that persist in the endpoint.
  • Apply object-level and row-level security with SQL permissions. These rules only apply to access through the endpoint, not through Spark or OneLake.
  • Add PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, but only as NOT ENFORCED metadata (see below).

What they cannot do:

  • INSERT, UPDATE, DELETE or MERGE into Lakehouse tables. The endpoint is read-only over Delta tables. Changes go through Spark (notebooks, Spark job definitions) or other Delta writers such as pipelines and Dataflows Gen2.
  • See Delta tables created outside Tables/, or external tables created by Spark at another location. Some Spark data types may not map to T-SQL types and can be missing from the endpoint’s view of a table.

If your team wants to write with T-SQL (INSERT, UPDATE, MERGE, stored-procedure ETL), that is what the Fabric Warehouse is for. The Warehouse also stores its tables as Delta in OneLake, so Spark can read them. The endpoint is for reading Lakehouse data with SQL, not for maintaining it.

SQL Server indexes vs Delta optimization

There is no CREATE INDEX for a Delta table, and no direct equivalent. The question SQL Server indexes answer (“how do I avoid reading rows I don’t need?”) still applies, but Delta answers it with file layout:

  • Data skipping. The log stores per-file statistics such as minimum and maximum values. A query filtering on order_timestamp can skip files whose range does not overlap the filter. This works well when data with similar values is stored together, and poorly when every file contains every value.
  • Partitioning. A table partitioned by a column stores each value’s rows in separate folders, so filters on that column skip whole folders. It suits low-cardinality columns with large partitions, such as a date at the right grain. Partitioning a modest table by a high-cardinality column creates thousands of tiny partitions and makes things worse. Do not partition just because the SQL Server table was partitioned.
  • File sizes and compaction. Fewer, appropriately sized files mean less overhead per query. OPTIMIZE restores this after many small writes.
  • Data organization within files. Z-ordering and liquid clustering co-locate related values so skipping works better. Liquid clustering support depends on the Fabric runtime; current runtimes support it, with read-only support in older ones.
  • V-Order. Speeds up reads of the files themselves, especially for the SQL engine and Direct Lake.

A clustered index orders rows within a structure the engine maintains on every write. Delta layout is created at write time and improved by maintenance, and it degrades between maintenance runs. That difference is why table maintenance is part of the design, not an afterthought.

Primary keys, uniqueness and foreign keys

This deserves its own section because the consequences are silent.

A Delta table does not enforce primary keys, unique keys or foreign keys. The data looks tabular, the column is called sales_order_id, and you may even have declared a primary key in the SQL endpoint, but nothing stops a pipeline from writing the same order twice.

Three different things are easy to confuse:

  • Enforced constraints. Delta can enforce NOT NULL and CHECK constraints on writes from engines that support them, such as Spark. Adding a CHECK constraint changes the table’s protocol requirements, so confirm that every engine writing to the table supports it.
  • Informational constraints. The SQL analytics endpoint and the Warehouse accept PRIMARY KEY and UNIQUE only as NONCLUSTERED ... NOT ENFORCED, and FOREIGN KEY only as NOT ENFORCED. They describe the model for tools and the optimizer; they do not check data. If you declare a key that is not actually unique, queries can return incorrect results, and that is your responsibility. On the endpoint, a foreign key also blocks later schema changes to the table.
  • Logical keys maintained by the pipeline. In practice, uniqueness in a Lakehouse is something your pipeline guarantees: deduplicate before writing, use MERGE on the business key, and test for duplicates.
Consulta SQL: Primary keys, uniqueness and foreign keysT-SQL (Fabric Warehouse)
-- Informational only: documents the key, does not enforce it (SQL analytics endpoint)
ALTER TABLE dbo.sales_orders
ADD CONSTRAINT pk_sales_orders PRIMARY KEY NONCLUSTERED (sales_order_id) NOT ENFORCED;

-- The check that actually protects you: run it in validation
SELECT sales_order_id, COUNT(*) AS copies
FROM dbo.sales_orders
GROUP BY sales_order_id
HAVING COUNT(*) > 1;
T-SQL (Fabric Warehouse)9 líneasVista de solo lectura. No está conectada a ninguna base de datos.

Concurrency

SQL Server’s default mental model is locking: writers take locks, readers may wait or read row versions if RCSI or snapshot isolation is enabled, and the engine arbitrates everything as it happens.

Delta uses optimistic concurrency:

  • Readers always read a specific table version, so they see a consistent snapshot and never block writers.
  • Writers do their work without locking the table: they write new files, then try to commit the next version. If another writer committed in the meantime, Delta checks whether the two changes conflict, for example whether both touched the same files. If they don’t, the commit succeeds. If they do, one writer fails with a concurrent-modification error and has to retry.

The practical result: concurrent appends usually coexist, but two jobs updating or merging into overlapping parts of the same table at the same time can fail. In production, design so that one process owns writes to a given table or partition, rather than relying on retries to sort it out. Two scheduled runs of the same load overlapping is a common way to hit this.

Common SQL Server assumptions that cause problems

  • Expecting indexes. There are none to create. Slow filters are fixed with layout, partitioning, compaction and better predicates.
  • Assuming primary keys enforce uniqueness. They are informational. Duplicates come in through reruns and appends, and nothing raises an error.
  • Tiny partitions. Partitioning by a high-cardinality column, or by day on a table that gets a few thousand rows a day, produces thousands of small files.
  • Too many small files. Frequent small appends without compaction slow every reader, from Spark to Direct Lake.
  • Row-by-row UPDATE patterns. Looping over rows with individual statements rewrites files over and over. Use set-based MERGE per batch.
  • Treating MERGE like OLTP DML. A MERGE is a batch operation that rewrites files. It belongs at batch frequency, not on every application event.
  • Assuming every SQL surface is writable. The SQL analytics endpoint is read-only over Lakehouse tables. Writes go through Spark or the Warehouse.
  • Expecting multi-table transactions. Each table commit is atomic on its own. A load that updates three tables needs idempotent steps and a recovery plan, not BEGIN TRAN.
  • Unnecessary full reloads. Overwriting a large table every hour to pick up a few changes rewrites every file each time. Incremental loads scale with the change, not the table.

When Delta is a good fit

Delta tables in a Lakehouse are the natural choice for:

  • Analytical data engineering: large reads, batch writes, transformations in Spark.
  • Lakehouse architectures where raw files and curated tables live together in OneLake.
  • Medallion layers: Bronze append tables, Silver current-state tables, Gold serving tables. Medallion Architecture Explained covers the pattern, and Bronze, Silver, Gold in Microsoft Fabric the implementation.
  • Large append and update workloads, especially when processed incrementally with MERGE.
  • Spark and SQL consumption of the same data: engineers in notebooks, analysts through the SQL endpoint, reports through Direct Lake.
  • Declarative transformations with Materialized Lake Views, which build Delta tables from Spark SQL definitions.

When Fabric Warehouse may be a better fit

The Warehouse also stores Delta tables in OneLake, but you work with it through T-SQL, with full DML and multi-table transactions. It is often the better home when:

  • The team is T-SQL first and wants to develop, test and deploy in SQL.
  • The workload is relational warehouse serving: star schemas, dimensional models, stored-procedure ETL.
  • Development is SQL-centric: INSERT, UPDATE, MERGE and transactions spanning several tables.
  • BI warehouse workloads are served mainly to SQL clients and semantic models.

Most platforms end up using both. Microsoft Fabric Lakehouse vs Warehouse walks through the decision, and Create Your First Fabric Warehouse with T-SQL, the next step in the Fabric path, builds a Warehouse and reads this sales_orders table from it.

Production checklist

  • Write pattern chosen per table: append, overwrite (whole or replaceWhere) or MERGE, and safe to rerun.
  • Uniqueness owned by the pipeline: deduplication before writes, MERGE on the business key, duplicate checks in validation.
  • Informational keys declared only where true, and never relied on for enforcement.
  • Partitioning justified by size and query patterns, not copied from SQL Server.
  • Small files under control: compaction scheduled for tables with frequent writes.
  • VACUUM retention set deliberately, balancing storage against time travel and recovery needs.
  • V-Order decision made for read-heavy tables, knowing the workspace default.
  • One writer per table or partition at a time; no overlapping runs of the same load.
  • Schema changes handled deliberately, not by blanket mergeSchema.
  • Readers checked: tables live under Tables/, and their columns map to T-SQL types where SQL users need them.
  • The right SQL surface used: SQL analytics endpoint for reading, Warehouse when T-SQL writes are required.

What you learned

  • A Delta table is Parquet files plus a transaction log; the log defines which files make up each version, and engines reconstruct the current state from it.
  • Files are immutable, so inserts add files and updates or deletes rewrite or mark files. Cost scales with files touched, not rows.
  • There are no B-tree indexes and no enforced primary or foreign keys. Performance comes from file layout and maintenance; uniqueness comes from your pipeline.
  • Time travel is available while old files and log entries are retained, and VACUUM bounds it.
  • OPTIMIZE compacts files, VACUUM cleans up unreferenced files, and V-Order is an optional, Fabric-specific read optimization that is off by default in new workspaces.
  • The SQL analytics endpoint reads Lakehouse tables with T-SQL but does not write them. For T-SQL writes, use the Warehouse.
  • Delta concurrency is optimistic: readers never block, and conflicting writers fail and retry.

Etiquetas

  • Delta Lake
  • Microsoft Fabric
  • Fabric Lakehouse
  • SQL Server
  • SQL Endpoint
  • Mantenimiento de tablas