Keys and Relationships

Parable exposes logical keys and relationships through Flight SQL metadata and the semantic catalog. Use them to choose joins and understand the grain of a table.

Discover keys

Flight SQL operation	What it returns
`GetPrimaryKeys`	The ordered logical primary-key columns for a table.
`GetImportedKeys`	Foreign keys from a table to referenced tables.
`GetExportedKeys`	Foreign keys in other tables that reference a table.
`GetCrossReference`	Relationships between one parent and one foreign table.

The semantic catalog exposes the same model as primaryKey and importedForeignKeys on each table.

These keys are descriptive metadata. Delta tables and DataFusion do not enforce uniqueness, referential integrity, cascades, or other database constraint actions.

Preserve table grain

A main Provider table and its child tables have different keys. For example, an issue can have many labels, so the parent issue ID repeats in providers.github.issues__labels. Use the complete logical key returned by the catalog instead of deduplicating a child table on its parent ID. Provider Plugins define the business key and ordering behavior used to project source records. Consumers inspect that metadata; they do not redefine it in a query.

Join a child table to its parent

The catalog relates each child table to its parent: the child’s parent_pk column references the parent’s key. parent_pk is text, so plain equality with the parent’s key works only for a string key. Compare parent_pk with the parent key in the text form Parable stores, as the _with_parent and _nested convenience views do:

Parent key type	Compare `parent_pk` with
String	The key itself
Integer, number, or boolean	`CAST(key AS VARCHAR)`
Date	`CAST(CAST(key AS BIGINT) AS VARCHAR)`, the days since 1970-01-01
Time	`CAST(CAST(key AS BIGINT) AS VARCHAR)`, the microseconds since midnight
Timestamp	`CAST(CAST(key AS BIGINT) AS VARCHAR)`, the microseconds since the Unix epoch

SELECT c.*
FROM providers.claude.usage_report AS p
JOIN providers.claude.usage_report__results AS c
  ON CAST(CAST(p.starting_at AS BIGINT) AS VARCHAR) = c.parent_pk

A floating-point (number) key of magnitude 1e16 or more does not round-trip. parent_pk holds it with a plus sign in the exponent (1e+16), and CAST(key AS VARCHAR) prints 1e16, so the children of that parent do not join, by hand or through the views. Smaller floating-point keys and integer keys round-trip.

Prefer semantic joins

Use a relationship from the catalog when one exists; a child table’s relationship to its parent compares keys as above. Otherwise, choose columns that represent the same semantic value or domain key:

Join Contact.Email values across Provider and Workspace tables.
Join Provider-native IDs only inside the Provider domain that owns them.
Use providers.identity.account_associations when accounts for the same person do not share an email. Its email column is Contact.Email, so a Provider or Workspace email joins to it directly.
Join work items on a real shared domain key, such as a branch name, rather than similarly named IDs.

SELECT
  member.email,
  member.name,
  directory.orgunitpath
FROM workspace.users AS member
JOIN providers.google.users AS directory
  ON directory.primaryemail = member.email

See the Provider Pool for identity tables and cross-Provider examples.

Admin

​Discover keys

​Preserve table grain

​Join a child table to its parent

​Prefer semantic joins

Discover keys

Preserve table grain

Join a child table to its parent

Prefer semantic joins