Lesson 4 of 6
Create a Delta table from CSV data
Use Spark SQL to create an external Delta table from the CSV file and inspect its transaction log.
7 minDatabricks
After this lesson, you will be able to create an external Delta table from a CSV file in cloud storage, query it with Spark SQL, and inspect the Delta transaction log to understand how changes are recorded.
What is a Delta table?
A Delta table is a table stored in the Delta Lake format. Delta Lake is an open-source storage layer that brings reliability and performance to data lakes. It stores data as Parquet files, a columnar format that is efficient for analytics, and adds a transaction log that records every change to the table. This log lets you time travel to previous versions, audit changes, and handle concurrent writes safely.
In Databricks, you can create Delta tables using SQL or Python. In this lesson, you will use Spark SQL, the SQL engine built into Databricks, to create an external Delta table from the CSV file you used in the earlier lesson 'Run your first query in a notebook'.
An external table is a table whose data lives in a location you specify in cloud storage, such as an S3 bucket or Azure Blob Storage container. The table definition in the metastore points to that location, but the data is not managed by the metastore. If you drop an external table, the data files remain in cloud storage. This is useful when you want to share data with other systems or manage the lifecycle of the data separately from the table metadata.
Create an external Delta table from CSV
You will use the CSV file people.csv that you created or used in the earlier lesson. The file has three columns: name, age, and city. The path to the file in cloud storage might look like s3://my-bucket/data/people.csv on AWS or abfss://[email protected]/data/people.csv on Azure. Replace <path-to-csv> with the actual path to your file.
To create a Delta table, you use the CREATE TABLE statement with the USING DELTA clause and the LOCATION clause pointing to a directory where the Delta table will be stored. The directory should be empty or not exist yet; Databricks will create it and write the Delta files there.
Run the following SQL in a notebook cell attached to your cluster:
CREATE TABLE people_delta
USING DELTA
LOCATION '<path-to-delta-table>'
AS SELECT * FROM csv.`<path-to-csv>`Replace <path-to-delta-table> with a path in your cloud storage where you want the Delta table to live, for example s3://my-bucket/delta/people or abfss://[email protected]/delta/people. This path should be different from the CSV file path.
This statement reads the CSV file, infers the schema, and writes the data as a Delta table in the specified location. It also registers the table in the metastore with the name people_delta, so you can query it by name in the current database.
After the command completes, you can query the Delta table just like any other table:
SELECT * FROM people_deltaYou should see the same rows as in the CSV file: Alice, Bob, and Carol with their ages and cities.
Inspect the Delta transaction log
When you create a Delta table, Delta Lake writes a transaction log in a subdirectory called _delta_log inside the table location. This log is a series of JSON files that record every operation on the table, such as adding files, removing files, and updating metadata. Each operation is an atomic commit, and the log allows readers to see a consistent snapshot of the table at any point in time.
To inspect the transaction log, you can use the DESCRIBE HISTORY command in Spark SQL. This command shows the history of operations on the table, including the version, timestamp, operation, and other details.
Run:
DESCRIBE HISTORY people_deltaYou will see at least one row, corresponding to the initial write operation. The version column starts at 0 and increments with each change. The operation column shows the type of operation, such as WRITE or CREATE TABLE AS SELECT. The operationParameters column contains details like the source path and mode.
You can also see the physical files in the table location. In your cloud storage console or using Databricks utilities, list the contents of the table directory. You will see a _delta_log folder and one or more Parquet files (with .snappy.parquet extension) containing the data. The _delta_log folder contains JSON files like 00000000000000000000.json that describe the first commit.
To list files using Databricks, you can use the dbutils.fs.ls command in a Python cell. For example:
dbutils.fs.ls('<path-to-delta-table>')Replace <path-to-delta-table> with the same path you used in the CREATE TABLE statement. The output shows the files and directories in that location.
Understand the transaction log entries
The transaction log is the key to Delta Lake's reliability. Each entry contains actions, such as add for adding a file, remove for removing a file, and metaData for schema changes. When you create a table from a CSV, the first commit typically includes a metaData action with the schema and a set of add actions for each Parquet file written.
You can view the content of a log file by reading it as JSON. For example, in a Python cell:
log_path = '<path-to-delta-table>/_delta_log/00000000000000000000.json'
df = spark.read.json(log_path)
display(df)Replace <path-to-delta-table> with your table path. The display function shows the DataFrame in a table format. You will see columns like add, remove, and metaData, with nested structures. The add column contains information about the data files, such as their paths and sizes.
This inspection helps you understand how Delta Lake tracks changes. In later lessons, you will see how new operations add new log entries and how you can use the log for time travel.
Summary
You have created an external Delta table from a CSV file using Spark SQL and inspected its transaction log. You learned that Delta Lake stores data as Parquet files and maintains a transaction log in the _delta_log directory. The DESCRIBE HISTORY command shows the table's version history, and the log files contain actions that describe every change.
Key takeaways
- A Delta table is a Parquet-based table with a transaction log that records every change.
- Use
CREATE TABLE ... USING DELTA LOCATION ... AS SELECTto create an external Delta table from a CSV file. - The transaction log is stored in the
_delta_logsubdirectory and contains JSON files with actions likeaddandmetaData. DESCRIBE HISTORYshows the operations performed on a Delta table, starting with version 0.
Try it
Create a new Delta table from a different CSV file in your cloud storage. The CSV should have at least four columns and five rows. Use a CREATE TABLE statement with USING DELTA and a new location. After creating the table, run DESCRIBE HISTORY on it and verify that the first operation is a write with version 0. Then list the files in the table location using dbutils.fs.ls and confirm that a _delta_log folder and Parquet files exist. Finally, query the table to ensure the data matches the CSV.
Official documentation
Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.