Apache Iceberg Creating Tables & Writing Data: Examples & Syntax

The tables

Same two tables as everywhere else. In this module you'll build a new Iceberg table from them and load it a few different ways.

customerscustomer_id, name, country, tier, signup_date

ordersorder_id, customer_id, product, category, amount, qty, status, order_date

1.0

CREATE TABLE … USING iceberg

The one word that makes a table an Iceberg table is USING iceberg. Everything else is ordinary SQL — column names, types, and an optional PARTITIONED BY and TBLPROPERTIES.

Syntax

CREATE TABLE [catalog.]db.name (
    col1 type [NOT NULL] [COMMENT '...'],
    col2 type, ...
)
USING iceberg
[PARTITIONED BY (col | transform(col))]
[TBLPROPERTIES ('key'='value', ...)];

5 ways to use this

Empty table with an explicit schema
As a copy of a query (CTAS)
Load rows with INSERT INTO
Replace data with INSERT OVERWRITE
From a DataFrame with writeTo

Here we declare a db.sales Iceberg table with a fixed schema. The runnable cell shows the same thing in the in-browser engine (minus the Iceberg-only clauses, which SQLite doesn't parse) so you can see it succeed — edit a type and run again:

Spark SQL

CREATE TABLE db.sales (
    order_id   BIGINT,
    product    STRING,
    category   STRING,
    amount     DOUBLE,
    order_date DATE
)
USING iceberg
PARTITIONED BY (category)
TBLPROPERTIES ('format-version'='2');

SELECT * FROM db.sales;   -- empty, ready for writes

Run it live in the browser — edit the SQL and press Run

SQL

⌘/Ctrl + Enter

format-version'='2' opts into row-level deletes (needed for efficient MERGE/UPDATE/DELETE); v1 is append-only. The PARTITIONED BY (category) is a logical declaration — Iceberg lays the files out and prunes on it automatically, with no category= folder you have to filter on. We go deep on that in Module 5.

1.1

CREATE TABLE AS SELECT (CTAS)

The fastest way to get a populated table: derive it straight from a query. CTAS creates the table, infers the schema from the query, and writes the rows — one atomic commit.

Spark SQL

CREATE TABLE db.sales
USING iceberg
PARTITIONED BY (category)
AS SELECT order_id, product, category, amount, order_date
   FROM orders;

SELECT * FROM db.sales ORDER BY order_id;

Run it live in the browser — edit the SQL and press Run

SQL

⌘/Ctrl + Enter

There's also REPLACE TABLE … AS SELECT (atomically swap the whole table's data while keeping its history) and CREATE OR REPLACE TABLE (create it, or replace it if it exists). CTAS is the go-to for building a curated table from raw input.

1.2

INSERT INTO — appending rows

Once the table exists, INSERT INTO appends rows. Every insert is a new snapshot (that's what powers time travel) — the old data is untouched, new files are added, and the catalog pointer moves forward atomically.

Spark SQL

INSERT INTO db.sales VALUES
  (101, 'Standing Desk', 'Home',        249.0, DATE '2025-07-10'),
  (102, 'Monitor Arm',   'Electronics',  59.0, DATE '2025-07-11');

-- you can also insert the result of a query:
INSERT INTO db.sales
SELECT order_id, product, category, amount, order_date
FROM orders WHERE status = 'delivered';

SELECT * FROM db.sales ORDER BY order_id;

Run it live in the browser — edit the SQL and press Run

SQL

⌘/Ctrl + Enter

Firing lots of tiny INSERTs is the classic way to create the small-files problem — thousands of little Parquet files that slow every read. Batch your writes, and run compaction periodically. More in Module 10.

1.3

INSERT OVERWRITE — replacing data

INSERT OVERWRITE replaces data rather than appending it. With Iceberg's default dynamic overwrite mode, only the partitions present in your new data are replaced — the rest of the table is left alone. This is the standard idempotent-batch pattern: re-run yesterday's job and only yesterday's partition changes.

Spark SQL

-- Replace only the 'Home' partition; 'Electronics' etc. are untouched
INSERT OVERWRITE db.sales
SELECT order_id, product, category, amount, order_date
FROM orders
WHERE category = 'Home';

Spark SQL

SELECT category, count(*) AS files_rows FROM db.sales GROUP BY category;

After a dynamic INSERT OVERWRITE of category='Home' — only that partition was rewritten

category	rows
Electronics	3
Home	2
Stationery	1

baked output — Iceberg-only surface (snapshot / metadata state), not runnable in the browser engine

Set spark.sql.sources.partitionOverwriteMode=dynamic for the behaviour above (Iceberg's recommended default). In static mode, INSERT OVERWRITE would replace the entire table — a common, painful surprise.

1.4

Writing from a DataFrame — writeTo

From code (Scala or PySpark), the modern DataFrameWriterV2 API mirrors the SQL exactly. writeTo("db.sales") then a verb: create, append, replace, or overwritePartitions.

Spark SQL

// Scala — create the table from a DataFrame
df.writeTo("db.sales")
  .tableProperty("format-version", "2")
  .partitionedBy($"category")
  .create()

// append more rows later
moreDf.writeTo("db.sales").append()

// dynamic partition overwrite (same as INSERT OVERWRITE)
reprocessed.writeTo("db.sales").overwritePartitions()

Prefer writeTo(...) over the older df.write.format("iceberg").save(path) — the V2 API is catalog-aware, so it commits through the catalog (atomic, discoverable by name) instead of writing to a bare path. Avoid .mode("overwrite") on the old API unless you truly mean 'replace the whole table.'

1.5

Mission: build the curated table

DevData Engineer · Lakehouse teamnow

We're seeding the new db.sales Iceberg table for the Home category dashboard. From orders, give me order_id, product, and amount for every order where category = 'Home', ordered by order_id. That's the exact query I'll wrap in a CTAS. 🙏

🎯 Your mission From orders, return order_id, product, amount for rows where category = 'Home', ordered by order_id.

SQL

⌘/Ctrl + Enter

Filter with WHERE category = 'Home'.
Add ORDER BY order_id so the rows come back in a stable order — this is the query you'd drop into CREATE TABLE db.sales USING iceberg AS ….

Frequently asked questions

How do I create an Iceberg table in Spark SQL?

Use CREATE TABLE with the USING iceberg clause, for example: CREATE TABLE db.sales (order_id BIGINT, product STRING, amount DOUBLE) USING iceberg PARTITIONED BY (category) TBLPROPERTIES ('format-version'='2'). You can also create and populate in one step with CREATE TABLE db.sales USING iceberg AS SELECT … (CTAS), which infers the schema from the query.

What is the difference between INSERT INTO and INSERT OVERWRITE in Iceberg?

INSERT INTO appends rows as a new snapshot, leaving all existing data in place. INSERT OVERWRITE replaces data: with Iceberg's recommended dynamic partition overwrite mode it replaces only the partitions present in the new data, which makes re-running a batch job idempotent. In static mode INSERT OVERWRITE replaces the entire table, so set spark.sql.sources.partitionOverwriteMode=dynamic to avoid surprises.

What is CTAS in Iceberg?

CTAS is CREATE TABLE AS SELECT — it creates a new Iceberg table, infers its schema from a query, and writes the query's rows, all as one atomic commit. It's the fastest way to build a curated table from raw input: CREATE TABLE db.sales USING iceberg PARTITIONED BY (category) AS SELECT order_id, product, category, amount FROM orders.

How do I write a Spark DataFrame to an Iceberg table?

Use the DataFrameWriterV2 API: df.writeTo("db.sales").create() to create, .append() to add rows, and .overwritePartitions() for a dynamic partition overwrite. Prefer writeTo over the older df.write.format("iceberg").save(path) because it is catalog-aware and commits atomically through the catalog by table name.

Creating Tables & Writing Data

The tables

CREATE TABLE … USING iceberg

CREATE TABLE AS SELECT (CTAS)

INSERT INTO — appending rows

INSERT OVERWRITE — replacing data

Writing from a DataFrame — writeTo

Mission: build the curated table

Frequently asked questions

Ready for the full data stack?

Comments