Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 36 ยท Delta Lake

Delta Lake Basics: Creating and Changing Tables

Learn Delta Lake, the default table format on Databricks: create managed tables, insert, update and delete rows, see table details and understand why it beats plain Parquet.

Plain files in cloud storage (Parquet, CSV) are cheap and open, but they are not tables. You cannot safely update one row, two jobs writing at once can corrupt the folder, and a failed job can leave half its output behind. Delta Lake fixes this by adding a transaction log next to the data files. It is the default table format in Databricks and the foundation of the lakehouse (see The lakehouse architecture).

A Delta table is simply a folder with Parquet data files plus a _delta_log folder that records every change. Because it is an open format, other engines can read it too.

Creating Delta tables

In Databricks every managed table is Delta unless you say otherwise. Read-only Databricks SQL:

SQL
CREATE TABLE IF NOT EXISTS main.sales.orders (
  order_id   BIGINT,
  customer   STRING,
  amount     DOUBLE,
  order_date DATE
);

INSERT INTO main.sales.orders VALUES
  (1, 'ana',  120.0, '2024-05-01'),
  (2, 'ben',   35.5, '2024-05-02'),
  (3, 'cara',  80.0, '2024-05-02');

From a DataFrame:

Python
(df.write
   .mode("overwrite")          # or "append"
   .saveAsTable("main.sales.orders"))

Or create a table from a query (CTAS):

SQL
CREATE OR REPLACE TABLE main.sales.big_orders AS
SELECT * FROM main.sales.orders WHERE amount > 100;

Managed tables are stored in the catalog's storage and removed (including data) when dropped. External tables point at a location you own; dropping them removes only the metadata.

Changing data

This is where Delta shines. Plain Parquet cannot do any of these in place:

SQL
UPDATE main.sales.orders SET amount = amount * 1.1 WHERE customer = 'ben';

DELETE FROM main.sales.orders WHERE order_date < '2024-01-01';

INSERT INTO main.sales.orders VALUES (4, 'dan', 15.0, '2024-05-03');

Behind the scenes Delta never edits a Parquet file. It writes new files containing the changed rows and records in the log that the old files are no longer part of the table. (With deletion vectors enabled, small changes can instead mark rows as deleted without rewriting a whole file.)

Inspecting a table

SQL
DESCRIBE DETAIL main.sales.orders;     -- format, location, number of files, size
DESCRIBE HISTORY main.sales.orders;    -- every change, who and when
DESCRIBE EXTENDED main.sales.orders;   -- columns plus properties
SHOW TBLPROPERTIES main.sales.orders;

DESCRIBE HISTORY is a window into the transaction log; the next lesson, The Delta transaction log, explains it.

Constraints and defaults

Delta enforces rules at write time:

SQL
ALTER TABLE main.sales.orders ALTER COLUMN order_id SET NOT NULL;
ALTER TABLE main.sales.orders ADD CONSTRAINT positive_amount CHECK (amount >= 0);

An insert that breaks a CHECK or NOT NULL constraint fails and changes nothing, which protects downstream tables from bad data.

Why Delta beats plain Parquet

Need Parquet folder Delta table
Atomic writes No (partial output possible) Yes
Update/delete rows No Yes
Concurrent writers Unsafe Safe (optimistic concurrency)
Schema enforcement No Yes
History and time travel No Yes
Streaming and batch on one table Awkward Yes

Stand-in: a tiny "table with a log"

The core trick of Delta can be shown in a few lines. The stand-in below stores immutable "files" (lists of rows) and a log of which files are currently part of the table. An UPDATE does not edit a file; it writes a new one and records a swap. This is a simplified model, not the real Delta protocol.

Python Stand-in: immutable files plus a log

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second stand-in demonstrates a constraint: a write that violates a rule is rejected as a whole, leaving the table unchanged (atomicity).

Python Stand-in: a CHECK constraint rejects the whole write

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Practical advice

  • Prefer saveAsTable and SQL CREATE TABLE over writing raw paths, so Unity Catalog can govern the table.
  • Use append for new data and MERGE for upserts (see MERGE and upserts); use overwrite with care, because it replaces the table's contents.
  • Avoid rewriting entire tables daily when only a day of data changes.
  • Add NOT NULL and CHECK constraints on keys and amounts.

Common mistakes

  • Treating a Delta folder as plain Parquet and deleting files by hand, which breaks the table.
  • Using mode("overwrite") expecting an append.
  • Skipping constraints and finding negative amounts in reports months later.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a Delta table consist of?

    The data is stored as Parquet files, and the _delta_log folder records every change.
  2. 2. Does UPDATE on a Delta table edit the Parquet file in place?

    Files are immutable. Changes create new files, and the log says which files are current.
  3. 3. What happens when an INSERT violates a CHECK constraint?

    Delta writes are atomic, so the whole statement fails.
  4. 4. What happens to data when a managed table is dropped?

    For managed tables Databricks owns the storage, so dropping removes the data (after a retention period). External tables keep their data.

Summary

  • Delta Lake adds a transaction log to Parquet files, giving ACID tables on cheap storage.
  • Create managed tables with SQL or saveAsTable; UPDATE, DELETE and INSERT work like a database.
  • Constraints enforce data quality at write time.
  • Changes create new immutable files; the log decides which files are current.

Next: The Delta Transaction Log