Create, append, and query your first table¶

This tutorial creates a timestamp-indexed table, appends one Parquet file that contains two symbols, and queries both identities through one logical table.

Before you start, install and verify the Python package.

Run the example¶

Run this example from an empty working directory. It creates a table at ./my_table and prints four rows.

from __future__ import annotations

from pathlib import Path

import pyarrow as pa
import pyarrow.parquet as pq

import timeseries_table_format as ttf


def _write_tiny_prices_parquet(path: Path) -> None:
    table = pa.table(
        {
            "ts": pa.array(
                [0, 0, 3_600 * 1_000_000, 3_600 * 1_000_000],
                type=pa.timestamp("us"),
            ),
            "exchange_id": pa.array([1, 2, 1, 2], type=pa.int32()),
            "symbol": pa.array(["NVDA", "AAPL", "NVDA", "AAPL"], type=pa.string()),
            "close": pa.array([10.0, 20.0, 11.0, 21.0], type=pa.float64()),
        }
    )
    pq.write_table(table, str(path))


def run(*, table_root: Path) -> pa.Table:
    table_root.mkdir(parents=True, exist_ok=True)

    tbl = ttf.TimeSeriesTable.create(
        table_root=str(table_root),
        index_column="ts",
        index_type="timestamp",
        index_granularity="1h",
        entity_columns=["exchange_id", "symbol"],
        timezone=None,
    )

    seg_path = table_root / "incoming" / "prices.parquet"
    seg_path.parent.mkdir(parents=True, exist_ok=True)
    _write_tiny_prices_parquet(seg_path)

    parquet_file = pq.ParquetFile(seg_path)
    tbl.append(
        pa.RecordBatchReader.from_batches(
            parquet_file.schema_arrow,
            parquet_file.iter_batches(),
        )
    )

    sess = ttf.Session()
    sess.register_tstable("prices", str(table_root))
    before = sess.sql(
        """
        select ts, exchange_id, symbol, close
        from prices
        order by exchange_id, symbol, ts
        """
    )

    report = tbl.optimize()
    assert report.source_segments_replaced == 1
    assert report.replacement_segments_written == 2
    assert report.distinct_identities_materialized == 2
    assert report.rows_read == report.rows_written == 4
    assert not report.no_op

    repeated = tbl.optimize()
    assert repeated.no_op
    assert repeated.starting_version == repeated.committed_version
    assert repeated.committed_version == report.committed_version

    sess = ttf.Session()
    sess.register_tstable("prices", str(table_root))
    after = sess.sql(
        """
        select ts, exchange_id, symbol, close
        from prices
        order by exchange_id, symbol, ts
        """
    )
    assert after.equals(before)
    return after


def main() -> None:
    out = run(table_root=Path("./my_table"))
    print(out)


if __name__ == "__main__":
    main()

1. Create the table¶

TimeSeriesTable.create(...) initializes the table root and its metadata:

• index_column="ts" selects the ascending timestamp column.
• index_granularity="1h" divides the timestamp domain into one-hour logical intervals.
• entity_columns=["exchange_id", "symbol"] tracks coverage independently for each composite identity.

This means NVDA and AAPL can use the same hour without conflicting. Each complete identity may have at most one row in that hour. The entity columns are an ordered identity definition, not instructions to create one table per entity. exchange_id is an Arrow Int32 column, so the example also shows a numeric identity component.

2. Append a Parquet segment¶

The example opens the Parquet file lazily as a RecordBatchReader and passes it to append(...). Append consumes the batches incrementally and writes one table-owned segment. The source contains both NVDA and AAPL rows in the same hourly intervals; a segment does not need to contain only one identity. The Parquet ts column must be an Arrow timestamp because that is the index type stored in the table metadata.

Append returns an AppendReport containing the committed version, generated segment path, row and row-group counts, file size, and effective writer settings.

If another file uses an existing interval for NVDA, the append raises IndexIntervalOverlapError. AAPL may independently use that same interval. Two NVDA rows in the same incoming interval raise DuplicateIndexIntervalError.

3. Optimize the entity layout¶

optimize() explicitly rewrites each mixed source segment into one replacement segment per complete identity. In this example, the source segment becomes one NVDA segment and one AAPL segment inside the same logical table.

Optimization is optional. It preserves the logical rows, schema, and per-entity coverage. The returned OptimizeReport records the versions, source and replacement segment counts, distinct identities, row counts, and whether the operation was a no-op.

Calling optimize() again in the example returns a successful no-op. Its starting and committed versions are equal, so it does not create another table version. Replaced source files may remain on disk until a future vacuum operation removes unreferenced files.

This operation is specific to entity layout. It does not combine small files or accept a target file size.

4. Query with SQL¶

Session provides the DataFusion SQL engine. The example registers the table as prices before and after optimization. It verifies that both queries return the same rows, then returns the optimized result as a pyarrow.Table.

Both entity columns remain normal SQL columns. You can use WHERE exchange_id = 1 AND symbol = 'NVDA' to select one identity or group by both columns to calculate independent aggregates.

You now have a locally queryable table root that can accept more non-overlapping Parquet segments.

Next, learn how to append files incrementally.