Best Practices for Organizing and Querying Large Scientific Datasets with sdata

Use sdata's hierarchical Data containers with rich metadata and scalable back-ends like SQLite or Parquet to enable efficient SQL-based filtering and chunked processing of datasets that exceed available memory.

sdata is a Python library that implements an open, self-describing data format for scientific workloads by wrapping pandas DataFrames with rich metadata and a hierarchical object model. According to the lepy/sdata source code, it supports multiple storage back-ends—including SQLite, HDF5, Parquet, and MinIO—and uses deterministic UUID-based identifiers to enable reproducible data management across complex experiment trees.

Understanding the Core Architecture

The sdata library is built around four primary components that enable scalable scientific data management.

The Data Class

The Data class in sdata/data.py serves as the central container for tabular data, metadata, and hierarchical relationships. It handles UUID/SUUID generation, automatic column-metadata extraction, and I/O operations to various formats. The class provides methods like to_folder(), to_sqlite(), and refactor() that streamline data persistence and normalization.

Metadata Management

The Metadata class in sdata/metadata.py implements a lightweight key/value store with typed attributes. It stores user-defined attributes including physical units, descriptions, and data types alongside internal !sdata_* system attributes. This ensures that column metadata travels with the data file, eliminating external lookup tables.

Scalable Storage Back-ends

sdata delegates persistence to specialized I/O modules that optimize for different access patterns:

• sdata/iolib/json1sqlitestore.py – SQLite with JSON1 extensions for SQL-based metadata and table queries
• sdata/iolib/hdf.py – HDF5 for high-performance scientific I/O
• sdata/iolib/minio.py – Cloud object storage integration for distributed datasets
• sdata/iolib/hdf.py – Parquet support for columnar analytics

Hierarchical Identification

The SUUID (Structured UUID) system embeds class, name, and parent hierarchy into globally unique identifiers. Implemented in Data.gen_suuid(), this enables reproducible linking between objects and easy navigation of complex experiment trees without relying on file system paths.

Organizing Large Scientific Datasets

Effective organization requires leveraging sdata's hierarchical model and metadata capabilities to create self-describing, portable archives.

Structure Data Hierarchically

Organize related measurements under a top-level project container using parent-child relationships. In sdata/data.py, the Data class maintains a group attribute that contains child Data objects, forming a tree structure that persists automatically when exporting.

import pandas as pd
import sdata

# Create child datasets

temp_df = pd.DataFrame({"Temp [K]": [295, 300, 305]})
stress_df = pd.DataFrame({"Stress [MPa]": [120, 130, 125]})

temperature = sdata.Data(name="temperature", table=temp_df)
stress = sdata.Data(name="stress", table=stress_df)

# Assemble hierarchical project

project = sdata.Data(name="tensile_test", project="material_X")
project.add_data(temperature)
project.add_data(stress)

# Persist entire hierarchy to folder or SQLite

project.to_folder("experiment_folder", dtype="csv")
project.to_sqlite("tensile_test.db")

Normalize Column Metadata with Refactor

Raw data often contains ambiguous column names like "Force [N]" that combine physical quantity and unit. The Data.refactor() method in sdata/data.py parses these strings to separate the column name from its unit, generating proper metadata attributes automatically.

raw_data = sdata.Data(name="raw_measurements", table=raw_df)
raw_data.refactor(fix_columns=True, add_table_metadata=True)

This creates attributes like !sdata_column_0 with distinct fields for value, unit, and label, improving query readability and downstream plotting automation.

Define Rich Metadata Up-front

Attach physical meaning and ontology during data creation to ensure self-describing files. Store units, measurement conditions, and provenance in the metadata store:

temperature.metadata.add("!sdata_column_0", 
                        value="Temperature", 
                        unit="K", 
                        label="Temp [K]")
temperature.metadata.add("experiment_id", 
                        value="EXP-2024-001", 
                        dtype="str")

Select Scalable Back-ends

For datasets exceeding 1 GB, avoid in-memory CSV storage. The JSON1SQLiteStore class in sdata/iolib/json1sqlitestore.py provides efficient on-disk random access and SQL querying capabilities without loading full tables into memory.

Querying Strategies for Scalable Analysis

sdata does not impose a custom query language; it delegates to pandas or the underlying storage engine. Choose the strategy based on data size and access patterns.

In-Memory Pandas Queries

For moderate datasets that fit in RAM, use pandas boolean indexing or the .query() method on the table attribute:

df = temperature.table
high_temp = df.query("Temperature > 300")

This approach is fastest for interactive notebook analysis but requires full data materialization.

SQL-Based Filtering via SQLite

For out-of-core datasets, use the SQLite back-end to push filters to the storage layer. The json1sqlitestore.py module exposes a SQL connection that supports standard queries without loading entire tables:

import pandas as pd
import sqlite3

conn = sqlite3.connect("tensile_test.db")
sql = "SELECT * FROM data WHERE \"Stress\" > 125"
high_stress = pd.read_sql_query(sql, conn)
conn.close()

Chunked Processing for Very Large Tables

When processing datasets that exceed memory, use chunksize to iterate through batches. This pattern avoids memory errors while enabling aggregation operations:

conn = sqlite3.connect("tensile_test.db")
sql = "SELECT * FROM data"

for chunk in pd.read_sql_query(sql, conn, chunksize=50_000):
    # Process each batch independently

    print("Chunk mean stress:", chunk["Stress"].mean())
conn.close()

Columnar Access with Parquet

For column-oriented analytics pipelines compatible with Spark or Arrow, export to Parquet format. This enables projection pushdown—reading only necessary columns without full table materialization:

project.to_parquet("experiment.parquet")

# Later access

df = pd.read_parquet("experiment.parquet", 
                     columns=["Temperature", "Stress"])

Complete Workflow Examples

Building and Exporting a Hierarchical Dataset

This example demonstrates the full pipeline: creating child objects, normalizing metadata, and persisting to SQLite:

import pandas as pd
import sdata

# Simulated measurements

temp_df = pd.DataFrame({"Temp [K]": [295, 300, 305, 310]})
stress_df = pd.DataFrame({"Stress [MPa]": [120, 130, 125, 135]})

# Build Data objects with automatic metadata extraction

temp = sdata.Data(name="temperature", table=temp_df)
stress = sdata.Data(name="stress", table=stress_df)
temp.refactor()
stress.refactor()

# Create project hierarchy

project = sdata.Data(name="tensile_test", project="material_X")
project.add_data(temp)
project.add_data(stress)

# Export to SQLite for scalable querying

project.to_sqlite("tensile_test.db")

Storing Derived Results with Version Control

Maintain provenance by adding processed results back into the hierarchy. Use rename() with random=False to preserve deterministic identifiers:

# Derive new quantity

temp.table["Temp_C"] = temp.table["Temp"] - 273.15
temp.metadata.add("!sdata_column_1", 
                 value="Temp_C", 
                 unit="°C", 
                 label="Temp [°C]")

# Update project with derived data

project.add_data(temp)
project.to_sqlite("tensile_test_v2.db")

The UUID remains stable, enabling reproducible pipelines that link raw and processed data via SUUID references.

Summary

• Encapsulate logical tables in Data objects with rich metadata and organize them hierarchically using add_data() to create portable, self-describing archives.
• Normalize column metadata using Data.refactor() in sdata/data.py to separate physical units from column names automatically.
• Persist large datasets using scalable back-ends like JSON1SQLiteStore (sdata/iolib/json1sqlitestore.py) or Parquet rather than CSV to enable efficient random access.
• Query at the storage layer using SQL or chunked pandas iterators (chunksize parameter) to process out-of-core data without memory errors.
• Track provenance via UUID/SUUID identifiers and store derived results back into the hierarchy to maintain reproducible experiment trees.

Frequently Asked Questions

How does sdata handle datasets larger than available RAM?

sdata delegates to storage-optimized back-ends that support out-of-core processing. The JSON1SQLiteStore class in sdata/iolib/json1sqlitestore.py enables SQL-based filtering directly on disk, while the to_parquet() method supports columnar formats that allow projection pushdown. For iterative processing, use pandas.read_sql_query() with the chunksize parameter to stream batches of rows without loading the full table into memory.

What is the difference between UUID and SUUID in sdata?

The UUID provides a random unique identifier for each Data object, while the SUUID (Structured UUID) embeds the object's class name, instance name, and parent hierarchy into a deterministic string. According to the implementation in sdata/data.py, the gen_suuid() method creates reproducible identifiers that remain consistent across sessions, enabling reliable linking between related datasets in complex experiment trees.

Can sdata integrate with cloud storage solutions?

Yes. The sdata/iolib/minio.py module provides native integration with MinIO and S3-compatible object storage, allowing hierarchical datasets to persist directly to cloud buckets. This enables distributed scientific workflows where data resides on remote storage but maintains the same hierarchical structure and metadata as local SQLite or HDF5 files.

How does the refactor method improve data organization?

The refactor() method in sdata/data.py parses column names that combine physical quantities with units—such as "Force [N]"—and splits them into separate metadata fields. It automatically creates !sdata_column_* attributes containing the parsed name, unit, and label, ensuring that downstream tools can render plots with proper axes labels without external lookup tables. This normalization also standardizes column names for more reliable SQL querying.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}