> ## Documentation Index
> Fetch the complete documentation index at: https://oxy.tech/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Tables and storage

> How Airhouse stores your tables with DuckLake: Parquet files, snapshots, transactions, and schema changes.

Airhouse stores your tables with [DuckLake](https://ducklake.select), an open lakehouse format. DuckLake splits every table into two parts:

* **Data**: Parquet files in S3-compatible object storage.
* **Metadata**: the catalog of schemas, tables, columns, files, and snapshots, stored in Postgres.

Each Airhouse database has its own catalog and its own storage location, so data in one database is never visible from another.

## Create and load tables

Create tables in the `lake` catalog. The default schema is `main`, and admins can create more schemas:

```sql theme={null}
CREATE TABLE lake.main.orders (
  order_id    BIGINT,
  customer_id BIGINT,
  status      TEXT,
  total       DECIMAL(12, 2),
  ordered_at  TIMESTAMP
);

INSERT INTO lake.main.orders VALUES
  (1, 100, 'paid',    120.00, '2026-03-01 10:00'),
  (2, 101, 'pending',  35.50, '2026-03-01 11:30');
```

Create a table from a query:

```sql theme={null}
CREATE TABLE lake.main.paid_orders AS
SELECT * FROM lake.main.orders WHERE status = 'paid';
```

For bulk loads from your machine, use `COPY ... FROM STDIN` (psql's `\copy`). See [Connect with psql](../connect/psql#load-data-from-a-local-file).

<Tip>
  Every committed write creates new Parquet files and a new snapshot. Load data in batches (multi-row `INSERT`, `INSERT ... SELECT`, or `COPY`) instead of many single-row inserts.
</Tip>

## Update and delete

`UPDATE` and `DELETE` work as in Postgres:

```sql theme={null}
UPDATE lake.main.orders SET status = 'paid' WHERE order_id = 2;
DELETE FROM lake.main.orders WHERE status = 'cancelled';
```

## Transactions

DuckLake gives you ACID transactions. Each statement runs in its own transaction unless you group statements explicitly:

```sql theme={null}
BEGIN;
INSERT INTO lake.main.orders VALUES (3, 102, 'paid', 80.00, '2026-03-02 09:15');
UPDATE lake.main.orders SET status = 'refunded' WHERE order_id = 1;
COMMIT;
```

Other connections see your changes only after `COMMIT`. Use `ROLLBACK` to discard them.

Writes to the same database are committed one at a time, while reads never wait for writes. Writes to different databases don't affect each other.

## Schema changes

Adding, renaming, and dropping columns only changes catalog metadata. Existing Parquet files aren't rewritten, so these changes are instant even on large tables:

```sql theme={null}
ALTER TABLE lake.main.orders ADD COLUMN channel TEXT;
ALTER TABLE lake.main.orders RENAME COLUMN channel TO sales_channel;
ALTER TABLE lake.main.orders DROP COLUMN sales_channel;
```

## Snapshots and time travel

Every committed change creates a new snapshot of the catalog. List the snapshots of your database:

```sql theme={null}
SELECT * FROM lake.snapshots();
```

Query a table as it was at an earlier snapshot or point in time:

```sql theme={null}
-- As of snapshot 3
SELECT * FROM lake.main.orders AT (VERSION => 3);

-- As of one hour ago
SELECT * FROM lake.main.orders AT (TIMESTAMP => now() - INTERVAL 1 HOUR);
```

Time travel is useful for auditing changes, debugging a pipeline, or recovering rows deleted by mistake:

```sql theme={null}
INSERT INTO lake.main.orders
SELECT * FROM lake.main.orders AT (VERSION => 3)
WHERE order_id = 2;
```

## Views

Admins can create views to share reusable query logic:

```sql theme={null}
CREATE VIEW lake.main.revenue_by_day AS
SELECT date_trunc('day', ordered_at) AS day, sum(total) AS revenue
FROM lake.main.orders
WHERE status = 'paid'
GROUP BY ALL;
```

## Where your data lives

When you run Airhouse yourself, you choose the object storage bucket (see [Configuration](../reference/configuration)). Each database's Parquet files are stored under its own prefix in that bucket, and its catalog lives in Postgres. Because the data is standard Parquet and the catalog is standard DuckLake, your data stays in open formats.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.