How `primary_key` and `unique_keys` Enforce Data Integrity in Apache Ossie Datasets

The primary_key and unique_keys fields in Ossie datasets define composite and candidate key constraints that propagate across converters to guarantee row-level uniqueness in downstream warehouses and semantic layers.

Apache Ossie is an open-source semantic modeling framework that treats datasets as logical business entities. The primary_key and unique_keys fields in the OSIDataset model are optional but critical for enforcing data integrity and determining relationship cardinality when converting between semantic layer formats. These constraints ensure that downstream systems can recreate the same uniqueness guarantees defined in the source OSSIE specification.

Schema Definition and Data Model

The OSSIE specification formally defines these fields in the canonical YAML schema. According to the source code in [core-spec/spec.yaml](https://github.com/apache/ossie/blob/main/core-spec/spec.yaml#L101-L124) (lines 101-124), primary_key is an ordered list of column names representing the preferred unique identifier, while unique_keys accepts zero or more additional key definitions for tables with multiple candidate keys.

Python Model Representation

In the Python implementation at [python/src/ossie/models.py](https://github.com/apache/ossie/blob/main/python/src/ossie/models.py#L151-L160) (lines 151-160), the OSIDataset Pydantic model exposes these fields as optional typed lists:

class OSIDataset(BaseModel):
    name: str
    source: str
    primary_key: Optional[list[str]] = None          # Preferred unique identifier

    unique_keys: Optional[list[list[str]]] = None    # Additional candidate keys

    ...

The primary_key field accepts a list[str] for single or composite keys, while unique_keys uses list[list[str]] to support multiple independent uniqueness constraints.

How Converters Propagate Key Constraints

OSSIE converters translate these logical constraints into vendor-specific formats. Each converter handles primary and unique keys according to the target platform's capabilities.

Wisdom Converter

The OSIToWisdomConverter in [converters/wisdom/src/ossie_wisdom/osi_to_wisdom.py](https://github.com/apache/ossie/blob/main/converters/wisdom/src/ossie_wisdom/osi_to_wisdom.py#L252-L253) (lines 252-253) maps primary_key to a primaryKey object and unique_keys to uniqueKeys in the exported JSON. This ensures the Wisdom domain can recreate identical uniqueness constraints when importing the semantic model.

OrionBelt Converter

The OSIToOBMLConverter in [converters/orionbelt/src/ossie_orionbelt/osi_to_obml.py](https://github.com/apache/ossie/blob/main/converters/orionbelt/src/ossie_orionbelt/osi_to_obml.py#L62-L78) (lines 62-78) propagates primary key information as a boolean flag on individual columns. It also preserves unique keys in a vendor-specific extension field obml_unique_keys to prevent data loss during round-trip conversions:

from ossie_orionbelt import OSIToOBMLConverter

converter = OSIToOBMLConverter()
obml = converter.convert(doc)

# Column flag indicates primary key membership

obml["datasets"]["orders"]["columns"]["order_id"]["primaryKey"]  # => True

Snowflake Converter

In [converters/snowflake/src/ossie_snowflake/converter.py](https://github.com/apache/ossie/blob/main/converters/snowflake/src/ossie_snowflake/converter.py#L199-L202) (lines 199-202), the SnowflakeConverter transforms primary_key into a Snowflake-style constraint object and unique_keys into an array of constraint objects:

{
  "primary_key": {"columns": ["order_id"]},
  "unique_keys": [{"columns": ["customer_id"]}, {"columns": ["order_id", "line_number"]}]
}

These structures enable Snowflake schema generation to create native primary key and unique key constraints in the target warehouse.

Polaris YAML Generator

The OsiYamlGenerator in [converters/polaris/src/main/java/org/apache/ossie/converter/polaris/OsiYamlGenerator.java](https://github.com/apache/ossie/blob/main/converters/polaris/src/main/java/org/apache/ossie/converter/polaris/OsiYamlGenerator.java#L85-L90) (lines 85-90) renders keys using the canonical OSSIE YAML format, ensuring compatibility with other OSSIE tools in the ecosystem.

Data Integrity and Relationship Cardinality

These key definitions serve two critical functions for data integrity across the OSSIE ecosystem:

• Uniqueness guarantees: The primary_key and each entry in unique_keys assert that no two rows can share the same combination of values, preventing duplicate records in downstream systems.

• Relationship cardinality: When defining relationships between datasets, the to_columns array must reference columns that are part of a primary_key or unique_keys definition in the target dataset. This determines whether the relationship is many-to-one, one-to-one, or many-to-many according to the OSSIE relationship specification.

Practical Implementation Examples

Defining Keys in Python

Create a dataset with a composite primary key and additional unique constraints using the OSIDataset model:

from ossie import OSIDataset

orders = OSIDataset(
    name="orders",
    source="salesdb.public.orders",
    primary_key=["order_id"],                     # Single-column primary key

    unique_keys=[["customer_id"], ["order_id", "line_number"]],  # Two candidate keys

)

Exporting to Wisdom

Convert the dataset to Wisdom format to verify key propagation:

from ossie_wisdom import OSIToWisdomConverter
from ossie import OSIDocument, OSISemanticModel

doc = OSIDocument(
    semantic_model=[
        OSISemanticModel(
            name="sales",
            datasets=[orders],
        )
    ]
)

converter = OSIToWisdomConverter()
result = converter.convert(doc)
wisdom_sheet = result.output["tables"][0]["zsheet_json"]
print(wisdom_sheet["primaryKey"])

# Output: {'columns': ['order_id']}

Generating Snowflake Schemas

Generate Snowflake-compatible YAML with native constraint definitions:

from ossie_snowflake import SnowflakeConverter

converter = SnowflakeConverter()
yaml_out = converter.convert(doc)
print(yaml_out["datasets"]["orders"]["primary_key"])

# Output: {"columns": ["order_id"]}

Summary

• primary_key defines the preferred unique identifier as a list of one or more column names, stored in python/src/ossie/models.py as Optional[list[str]].

• unique_keys captures additional candidate keys as a list of column lists, enabling support for tables with multiple natural keys.

• Converters in Wisdom, OrionBelt, and Snowflake preserve these constraints through native format mappings, ensuring data integrity persists across vendor boundaries.

• Relationship cardinality rules depend on these key definitions to validate that foreign key references target unique rows in the destination dataset.

Frequently Asked Questions

Can a dataset have multiple primary keys in Ossie?

No. A dataset can have only one primary_key field, though it can be a composite key containing multiple columns (e.g., ["region_id", "order_number"]). However, you can define multiple additional unique constraints through the unique_keys field to capture alternative candidate keys.

What happens if I define a primary key on a column that doesn't exist?

The OSSIE Python model validates the dataset structure but does not inherently validate column existence against the source table. However, specific converters may raise warnings or errors during conversion. For example, the OrionBelt converter issues a warning when primary_key references unknown columns, as implemented in osi_to_obml.py (lines 62-78).

How do unique keys affect relationship definitions in Ossie?

When creating relationships between datasets, the to_columns array must reference columns that are part of either the primary_key or one of the unique_keys definitions in the target dataset. This requirement ensures referential integrity by guaranteeing that the referenced columns uniquely identify rows, which determines whether the relationship is many-to-one, one-to-one, or many-to-many.

Are unique keys preserved when converting to formats that don't support them natively?

Yes. Converters like the OrionBelt implementation store unique_keys in vendor-specific extension fields (e.g., obml_unique_keys) even when the target format lacks first-class support for multiple unique constraints. This ensures the metadata persists for round-trip conversions back to OSSIE format.

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