Understanding Primary and Unique Keys in Ossie Datasets: A Complete Guide

In Apache Ossie, the primary_key property defines the main row identifier as an array of column names, while unique_keys specifies additional uniqueness constraints as an array of column arrays.

In the OSSIE data model, a dataset represents a logical table that can function as either a fact or dimension table within the apache/ossie repository. These two optional properties determine how rows are uniquely identified and referenced in relationships throughout your semantic model.

Defining Primary and Unique Keys in OSSIE Datasets

According to the Core Metadata Specification in core-spec/spec.md, OSSIE datasets support two distinct key definitions that control entity identity and uniqueness constraints.

Primary Key

The primary_key property accepts an array of column names that uniquely identify a row within the dataset. This can be a single column for simple identifiers or a composite list of multiple columns for complex relationships. OSSIE treats the primary key as the preferred unique identifier and uses it as the reference target for relationships on the "one" side of joins.

Unique Keys

The unique_keys property accepts an array of arrays, where each inner array represents a distinct uniqueness constraint. These alternate keys provide additional uniqueness guarantees beyond the primary identifier but are not used as the main reference for entity relationships. Each entry can be simple (single-column) or composite, allowing multiple independent uniqueness constraints per dataset.

These definitions are formally specified in the dataset schema within core-spec/spec.md at lines 100-108.

How OSSIE Processes Keys Internally

When converting OSSIE models to vendor-specific formats, the converters promote these key definitions to platform-native metadata structures.

Vendor Format Conversion

In the OrionBelt converter located at converters/orionbelt/src/ossie_orionbelt/osi_to_obml.py (lines 65-99), OSSIE maps the dataset properties to vendor-specific fields. The converter transforms primary_key into obml_primary_key and unique_keys into obml_unique_keys, ensuring that Snowflake and other supported warehouses maintain the original constraints.

Python Runtime Model

The OSSIE Python SDK exposes these fields through the Dataset class in python/src/ossie/models.py (lines 115-119). The type signatures reveal the structural differences:

• primary_key: Optional[list[str]] — A single list of column names
• unique_keys: Optional[list[list[str]]] — A list containing multiple lists of column names

Practical Examples: Defining Keys in YAML

OSSIE dataset definitions use YAML configuration files to declare keys declaratively.

Simple Primary Key with Alternate Unique Key

datasets:
  - name: customers
    source: sales.public.customers
    primary_key: [customer_id]
    unique_keys:
      - [email]
    description: Customer dimension
    fields: []

This configuration establishes customer_id as the primary identifier while enforcing uniqueness on the email column as an alternate key.

Composite Primary Keys

For fact tables requiring multi-column identifiers, specify multiple columns in the primary key array:

datasets:
  - name: store_sales
    source: tpcds.public.store_sales
    primary_key: [ss_item_sk, ss_ticket_number]
    unique_keys:
      - [ss_item_sk, ss_ticket_number]
      - [order_number]
    description: Fact table of store sales
    fields: []

Note that the composite key [ss_item_sk, ss_ticket_number] appears in both primary_key and unique_keys, which OSSIE allows for explicit constraint declaration.

Accessing Keys in Python

When working with the OSSIE Python runtime, you can inspect key definitions programmatically:

from ossie.models import Dataset

# Assume `model` is an OSSIE SemanticModel already loaded

sales = model.datasets["store_sales"]
print("Primary key:", sales.primary_key)      # ['ss_item_sk', 'ss_ticket_number']

print("Unique keys:", sales.unique_keys)      # [['ss_item_sk', 'ss_ticket_number'], ['order_number']]

This programmatic access enables validation tools and conversion scripts to verify key constraints before deployment.

Summary

• Primary keys in OSSIE are arrays of column names that serve as the main row identifier and relationship target, defined in the primary_key field.
• Unique keys provide additional uniqueness constraints as an array of column arrays via the unique_keys field, supporting alternate natural keys.
• The Core Metadata Specification at core-spec/spec.md formally defines these properties, while python/src/ossie/models.py implements them as Optional[list[str]] and Optional[list[list[str]]] respectively.
• Converters such as the OrionBelt implementation map these keys to vendor-specific metadata like obml_primary_key and obml_unique_keys for warehouse compatibility.

Frequently Asked Questions

What is the difference between primary_key and unique_keys in OSSIE?

The primary_key property designates the main identifier used for entity relationships and row uniqueness, while unique_keys defines additional alternate keys that enforce uniqueness but do not serve as the primary reference. According to the OSSIE specification, primary_key is a single array of columns, whereas unique_keys is an array containing multiple column arrays.

Can a dataset have multiple unique keys in OSSIE?

Yes, OSSIE datasets support multiple unique constraints through the unique_keys property. Each entry in the unique_keys array represents a distinct uniqueness constraint and can be either a single-column or composite key. This allows datasets to enforce business rules such as unique email addresses or order numbers alongside the primary identifier.

How are primary keys handled when converting to Snowflake or OrionBelt formats?

When converting to vendor formats, the OrionBelt converter at converters/orionbelt/src/ossie_orionbelt/osi_to_obml.py promotes primary_key to obml_primary_key and unique_keys to obml_unique_keys. This transformation occurs during the conversion process (lines 65-99), ensuring that downstream data warehouses maintain the original uniqueness constraints defined in the OSSIE semantic model.

Is it mandatory to define a primary key for every OSSIE dataset?

No, both primary_key and unique_keys are optional properties in the OSSIE dataset schema. However, defining a primary key is recommended for dimension tables and fact tables that participate in relationships, as OSSIE uses this property to establish the "one" side of relationships and ensure proper entity identity across the semantic model.

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