Skip to main content

Semantic Layer

The OSO semantic layer allows us to provide a detailed interface to the OSO data model that encapsulates the contextual meaning of tables, columns or other abstractions in the data warehouse. The hope is that this interface will allow new users an easy way to interrogate the data while also providing for convenience tools when creating new models for seasoned users.

Querying against the semantic layer​

Overview​

Before we get into a simple query, here's the overview of the most likely models you'd be querying against:

  • artifacts - This is a Semantic Model representing out artifacts_v1 table. This includes everything from repositories, packages, blockchain addresses, and similar.
  • projects - This is a Semantic Model representing our projects_v1 table. This includes all of the projects gathered in both oss-directory and projects defined from op-atlas. Projects are comprised of artifacts. Often projects are simply the github organization for a set of repositories. However, they can also represent other groupings of artifacts.
  • collections - This is a Semantic Model representing our collections_v1 table. A collection is a grouping of projects. This grouping can be completely arbitrary.
  • int_events__github - This is a Semantic Model representing our int_events__github table. Despite this being an intermediate table, it contains useful information that is likely useful for querying for github related events.
  • int_events__blockchain - This is a Semantic Model representing our int_events__blockchain table. This table contains events related to blockchain transactions and other blockchain related events.
  • int_events__4337 - This is a Semantic Model representing our int_events__4337 table. This table contains events related to ERC-4337 transactions and other ERC-4337 related events.
  • metrics - This is a Semantic Model representing our metrics_v0 table. This table is not actually the metrics themselves, but rather a directory of the available metrics.
  • timeseries_metrics_by_* - These are a set of tables that actually represent the metrics for artifacts, projects, and collections. These tables are named based on the model they are associated with. For instance, the timeseries_metrics_by_project_v1 table contains metrics for projects. These are the actual values where metrics is the directory of available metric types stored in these tables.
note

The list above is not exhaustive. For more information on the available models, you can always refer to the live documentation inside the semantic layer. This can be accessed by using the describe method on the semantic object in the pyoso client:

from pyoso import Client

oso = Client()

print(oso.semantic.describe())

A basic query​

Let's consider a basic query that only seeks to find all artifacts within a collection. If you only cared about the artifact name and collection name attributes this semantic query would look like this:

from pyoso import Client

oso = Client()

query = oso.semantic.select(
    "collection.name"
    "artifact.name",
)

This query simply selects the name attribute from both the collection model and the artifact model.

To render this semantic query to sql you can do:

print(query.sql())

By default this will use the trino SQL dialect and the printed query will look something like this:

SELECT
  collection_db6d9b45.collection_name AS collection_name,
  artifact_8e5b948a.artifact_name AS artifact_name
FROM oso.artifacts_v1 AS artifact_8e5b948a
LEFT JOIN oso.artifacts_by_project_v1 AS artifacts_by_project_v1_4f760b72
  ON artifact_8e5b948a.artifact_id = artifacts_by_project_v1_4f760b72.artifact_id
LEFT JOIN oso.projects_v1 AS project_46f86faa
  ON artifacts_by_project_v1_4f760b72.project_id = project_46f86faa.project_id
LEFT JOIN oso.projects_by_collection_v1 AS projects_by_collection_v1_483e7c1c
  ON project_46f86faa.project_id = projects_by_collection_v1_483e7c1c.project_id
LEFT JOIN oso.collections_v1 AS collection_db6d9b45
  ON projects_by_collection_v1_483e7c1c.collection_id = collection_db6d9b45.collection_id
GROUP BY
  1,
  2

As evident in the generated sql, the semantic query tool automatically decides the proper join path and automatically groups the collection name and artifact name.

note

For this specific example, a custom written query could produce one less join, but due to the way the registry has is modeled this result is currently as intended.

To execute this generated sql query and return the associated dataframe one can simply do:

df = query.as_pandas()

Querying with a relationship​

Relaltionships are a key part of the semantic layer. They allow us to define how models relate to each other. Unlike, SQL you don't explicity define join paths. The semantic layer resolves these paths for you. For dealing with relationships we have special semantics when a relationship attribute is included in the query.

Selecting just a relationship attribute​

The project model has a relationship to the collection via it's collection relationship attribute.

query = oso.semantic.select(
  "project.by_collection",
)

This would produce the following SQL:

SELECT
  projects_by_collection_v1_8247793e.collection_id AS project_collection
FROM oso.projects_v1 AS project_46f86faa
LEFT JOIN oso.projects_by_collection_v1 AS projects_by_collection_v1_8247793e
  ON project_46f86faa.project_id = projects_by_collection_v1_8247793e.project_id
GROUP BY
  1

When a relationship attribute is selected only the key used to reference the foreign model is returned. In the case of project.collection, the collection is referenced by it's collection id in a foreign key relationship.

We can also include the relationship attribute to provide a filtering context without having an explicit filter. For instance if we wanted to only get the project names that exist in any collection, we could do this:

query = oso.semantic.select(
    "project.name",
    "collection",
)

As not all artifacts are part of a project, this will only return the projects that have a relationship to a collection. This would produce the following SQL:

SELECT
  project_46f86faa.project_name AS project_name,
  project_46f86faa.collection_id AS project_collection
FROM oso.projects_v1 AS project_46f86faa
WHERE
  project_46f86faa.collection_id IS NOT NULL
GROUP BY
  1,
  2

Handling ambiguous joins​

The previous examples only involved a fairly simple join relationships. Artifacts are related to Collections via their relationship to Projects and some intermediate tables in between. However, in the OSO data model we also have a concept of Events. There are multiple event tables but let's consider the events from github. In the current semantic layer the model responsible for the github events is github_event. All of the event models have a generic interface that involves the following relationships:

  • from - The artifact that initiated an event
  • to - The artifact that received an event

If we instead wanted to make the following semantic query:

query = oso.semantic.select(
    "int_events__github.time",
    "collection.name",
    "artifact.name",
)

If you try to render this sql:

query.sql()

This would result in a ModelHasAmbiguousJoinPath exception. This "ambiguous" join is because there are two possible paths to join github_event to both the artifact model the collection model. In such a case, we need to give the semantic querying mechanism an explicit path for which to join against the github_event table. To do this, we use a special arrow operator -> that will define the path we want to relate through for the ambiguous join. For instance, if what we cared about are the event times and the associated artifacts and collections that received an event we'd do this:

query = oso.semantic.select(
    "int_events__github.time",
    "int_events__github.to->collection.name",
    "int_events__github.from->artifact.name",
)

This would then produce the following SQL:

SELECT
  collection_2083abff.collection_name AS int_events__github_to__collection_name,
  artifact_95a01095.artifact_name AS int_events__github_from__artifact_name,
  int_events__github_420c9a8e.time AS int_events__github_time
FROM oso.int_events__github AS int_events__github_420c9a8e
LEFT JOIN oso.artifacts_v1 AS artifact_1b71f23f
  ON int_events__github_420c9a8e.to_artifact_id = artifact_1b71f23f.artifact_id
LEFT JOIN oso.artifacts_by_project_v1 AS artifacts_by_project_v1_5ee26df1
  ON artifact_1b71f23f.artifact_id = artifacts_by_project_v1_5ee26df1.artifact_id
LEFT JOIN oso.projects_v1 AS project_e17705b6
  ON artifacts_by_project_v1_5ee26df1.project_id = project_e17705b6.project_id
LEFT JOIN oso.projects_by_collection_v1 AS projects_by_collection_v1_8247793e
  ON project_e17705b6.project_id = projects_by_collection_v1_8247793e.project_id
LEFT JOIN oso.collections_v1 AS collection_2083abff
  ON projects_by_collection_v1_8247793e.collection_id = collection_2083abff.collection_id
LEFT JOIN oso.artifacts_v1 AS artifact_95a01095
  ON github_event_420c9a8e.from_artifact_id = artifact_95a01095.artifact_id
GROUP BY
  1,
  2,
  3

Filtering​

Filtering is a key part of any query. The semantic layer provides a way to filter on the attributes of the models. For instance, if we wanted to filter the previous query to only include artifacts that are part of a specific namespace, we could do this:

query = oso.semantic.select(
    "int_events__github.time",
    "int_events__github.to->collection.name",
    "int_events__github.from->artifact.name",
).where(
    "int_events__github.to->artifact.namespace = 'oso'",
)

This would produce the following SQL:

SELECT
  collection_2083abff.collection_name AS int_events__github_to__collection_name,
  artifact_95a01095.artifact_name AS int_events__github_from__artifact_name,
  int_events__github_420c9a8e.time AS int_events__github_time
FROM oso.int_events__github AS int_events__github_420c9a8e
LEFT JOIN oso.artifacts_v1 AS artifact_1b71f23f
  ON int_events__github_420c9a8e.to_artifact_id = artifact_1b71f23f.artifact_id
LEFT JOIN oso.artifacts_by_project_v1 AS artifacts_by_project_v1_5ee26df1
  ON artifact_1b71f23f.artifact_id = artifacts_by_project_v1_5ee26df1.artifact_id
LEFT JOIN oso.projects_v1 AS project_e17705b6
  ON artifacts_by_project_v1_5ee26df1.project_id = project_e17705b6.project_id
LEFT JOIN oso.projects_by_collection_v1 AS projects_by_collection_v1_8247793e
  ON project_e17705b6.project_id = projects_by_collection_v1_8247793e.project_id
LEFT JOIN oso.collections_v1 AS collection_2083abff
  ON projects_by_collection_v1_8247793e.collection_id = collection_2083abff.collection_id
LEFT JOIN oso.artifacts_v1 AS artifact_95a01095
  ON github_event_420c9a8e.from_artifact_id = artifact_95a01095.artifact_id
WHERE
  artifact_1b71f23f.artifact_namespace = 'oso'
GROUP BY
  1,
  2,
  3

Additionally, you can also filter on a model's measures. Let's get all the artifacts with 1000 or less events:

query = oso.semantic.select(
    "int_events__github.time",
    "int_events__github.to->collection.name",
    "int_events__github.to->artifact.name",
).where(
    "github_event.count <= 1000",
)

This would produce the following SQL:

SELECT
  collection_2083abff.collection_name AS int_events__github_to__collection_name,
  artifact_95a01095.artifact_name AS int_events__github_from__artifact_name,
  int_events__github_420c9a8e.time AS int_events__github_time
FROM oso.int_events__github AS int_events__github_420c9a8e
LEFT JOIN oso.artifacts_v1 AS artifact_1b71f23f
  ON int_events__github_420c9a8e.to_artifact_id = artifact_1b71f23f.artifact_id
LEFT JOIN oso.artifacts_by_project_v1 AS artifacts_by_project_v1_5ee26df1
  ON artifact_1b71f23f.artifact_id = artifacts_by_project_v1_5ee26df1.artifact_id
LEFT JOIN oso.projects_v1 AS project_e17705b6
  ON artifacts_by_project_v1_5ee26df1.project_id = project_e17705b6.project_id
LEFT JOIN oso.projects_by_collection_v1 AS projects_by_collection_v1_8247793e
  ON project_e17705b6.project_id = projects_by_collection_v1_8247793e.project_id
LEFT JOIN oso.collections_v1 AS collection_2083abff
  ON projects_by_collection_v1_8247793e.collection_id = collection_2083abff.collection_id
LEFT JOIN oso.artifacts_v1 AS artifact_95a01095
  ON github_event_420c9a8e.from_artifact_id = artifact_95a01095.artifact_id
GROUP BY
  1,
  2,
  3
HAVING
  COUNT(github_event_420c9a8e.event_id) <= 1000

Chaining and reusing queries​

The semantic layer also provides a way to chain multiple queries together. In the same way that one may define CTEs in SQL, a named query allows us to reference a query as if it were just another model in the semantic layer. This can only happen within a single query builder context, so you must use the same oso.semantic instance to chain queries together that you wish to reuse. In order to support this, you must name a query using the with_select method. The first argument to this method is the name of the query. Then in subsequent queries you can reference that query as if it were just another model in the semantic layer.

Let's find all the projects that published at least one new release in the last 6 months and also had a user operation in ERC-4337.

note

This is not yet implemented and this design may need to change.

github_release_metrics = oso.semantic.select(
    "timeseries_metrics_by_project.sum as metric_sum",
    "timeseries_metrics_by_project.project as project"
).where(
    "metrics.name LIKE 'GITHUB_releases_monthly'"
)

# Get the received grants by selecting `projects.*`
received_grants = oso.semantic.cte(
  "github_release_metrics", github_release_metrics
).select(
    "projects"
).where(
    "github_release_metrics.metric.sum > 0"
)

paymaster_projects = oso.semantic.select(
    "int_events__4337.to",
).where(
    "int_events__4337.event_type LIKE 'CONTRACT_INVOCATION_VIA_PAYMASTER'"
)

filtered_projects = oso.semantic.cte(
    "paymaster_projects",
    paymaster_projects
).cte(
   "received_grants",
    received_grants
).select(
    "paymaster_projects.to",
    "received_grants.projects",
)

final = oso.semantic.cte(
  "filtered_projects",
  filtered_projects
).select(
    "filtered_projects.name",
).where(
    "filtered_projects.project_name IS NOT NULL",
)

This query will produce the following SQL similar to the following:

-- This query is not exactly correct we need to render this once this part of the code is implemented
with github_release_metrics as (
  SELECT
    SUM(timeseries_metrics_by_project_v1_4f760b72.amount) AS metric_sum,
    timeseries_metrics_by_project_v1_4f760b72.project_id AS project
  FROM oso.timeseries_metrics_by_project_v1 AS timeseries_metrics_by_project_v1_4f760b72
  LEFT JOIN oso.metrics_v0 AS metrics_v0_5ee26df1
    ON timeseries_metrics_by_project_v1_4f760b72.metric_id = metrics_v0_5ee26df1.metric_id
  WHERE
    metrics_v0_5ee26df1.name LIKE 'GITHUB_releases_monthly'
  GROUP BY
    2
),
received_grants as (
  SELECT
    project_46f86faa.project_id AS project_id
  FROM github_release_metrics as github_release_metrics_13371337
  LEFT JOIN oso.projects_v1 AS project_46f86faa
    ON github_release_metrics_13371337.project = project_46f86faa.project_id
  WHERE
    github_release_metrics_13371337.metric_sum > 0
  GROUP BY
    1
),
paymaster_projects as (
  SELECT
    4337_events_420c9a8e.to_artifact_id AS to_artifact_id
  FROM oso.int_events__4337 AS 4337_events_420c9a8e
  WHERE
    4337_events_420c9a8e.event_type LIKE 'CONTRACT_INVOCATION_VIA_PAYMASTER'
  GROUP BY
    1
),
filtered_projects as (
  SELECT
    project_46f86faa.*,
    paymaster_projects_8247793e.to_artifact_id AS to_artifact_id,
    received_grants_5ee26df1.project_id AS project_id
  FROM oso.projects_v1 AS project_46f86faa
  LEFT JOIN paymaster_projects AS paymaster_projects_8247793e
    ON project_46f86faa.project_id = paymaster_projects_8247793e.to_artifact_id
  LEFT JOIN received_grants AS received_grants_5ee26df1
    ON project_46f86faa.project_id = received_grants_5ee26df1.project_id
  WHERE
    project_46f86faa.project_name IS NOT NULL
  GROUP BY
    1, 2, 3, 4, 5, 6, 7, 8
)
SELECT
  filtered_projects_5ee26df1.project_name AS filtered_projects_name
FROM filtered_projects AS filtered_projects_5ee26df1
GROUP BY
  1

Star querying​

The semantic layer doesn't support star querying in the same way that SQL does. In order to get all of the fields from a model you instead simply reference the model itself. For instance, if you wanted to get all of the fields from the artifact model, you would do this:

query = oso.semantic.select(
    "artifact"
)

This is more useful when combined with a filter. For instance, if you wanted to get all of the fields from the artifact model where the artifact is part of a specific collection, you would do this:

query = oso.semantic.select(
    "artifact",
).where(
    "collection.name = 'my_collection'",
)

Querying a relationship attribute​

While not always useful, it is possible to simply query a relationship attribute. While not always useful on it's own, this can be important to use when reusing queries as the semantic layer will automatically.

query = registry.select(
    "project",
    ["by_collection"],
)
print(query.sql(pretty=True))

The output of this query will look like this:

SELECT
    artifact.artifact_id as artifact__by_project
FROM iceberg.oso.artifacts_v1 as artifact

The importance of this query is that assuming that not all projects have artifacts, this query will return all projects that actually have artifacts.

When using reusing a query this ensures that the Relationship is maintained and can be used by downstream queries.

Like this:

source_more_than_100_artifacts = registry.select(
  "artifact.source as source",
  "artifact.by_project as by_project"
).where(
    "artifact.count > 100"
)

registry.cte(
    "source_more_than_100_artifacts",
    source_more_than_100_artifacts
).select(
    "source_more_than_100_artifacts.source",
    "project.name",
)

This contrived example will produce a result that returns all projects related to artifact source that have more than 100 artifacts.

Anatomy of the semantic layer​

The semantic layer provides an interface that takes a lot of inspiration from the wonderful work at cube.dev. Many of the same abstractions are used to provide a similar vocabulary for those familiar with that tool. These abstractions and components are as such:

  • Model
    • A model is a given object abstraction in for a given data type in the OSO data warehouse. At this time, it is expected that there's a single canonical table to represent a given Model. Models, like any object can relate to each other. For instance, the OSO data model has the concept of an Artifact which is part of zero or more Projects, and Projects which are part of zero or more Collections. Each of those entities are related to their associated Models.
  • Dimension
    • A dimension is a non-aggregated attribute of a model. This could be something like an Artifact name, a Project namespace, a Collection description, an Event type, or Event time.
  • Measure
    • A measure is an aggregated attribute of a model that is usually used to summarize values related to a model. This could be something like, the count of Artfiacts or sum of the amount dimension of Events.
  • Relationship
    • A relationship defines a relationship is a special attribute that defines the relationship between one model and another. In sql, this is modelled as some kind of foreign key.
  • Interface
    • Much like an interface in an object oriented language, an interface is a generic interface that can be applied to a given model. This allows us to provide
  • Registry
    • A registry is the directory of all the models and their relationships to each other. Without the registry it's impossible to create a valid sql query.

Defining the semantic layer​

The semantic layer definitions are all JSON serializable Python objects. These all represent objects detailed in the Anatomy of the Semantic Layer section above. This section will walk through the definition of the artifact, project, and collection models. We will start with a very basic definition of the artifact model and expand it to include more attributes and relationships.

OSO artifacts, projects and collections​

Before we get to defining a semantic layer for these three entities, we need to understand the meaning these entities hold in the context of OSO. Inside the OSO data warehouse, artifacts, projects, and collections are generic entities to understand the events that occur in the universe of open source software. These entities roughly map to certain classes of real world or digital entities.

  • Artifact entities
    • An artifact is generally an real or digital object that we consider the smallest atom that can send or receive events. This could be an NPM package, a Github repository, an Optimism contract, an Ethereum address, or any other entity that can be interacted with that is generally indivisible.
  • Project entities
    • A project is a collection of artifacts that are related to each other in a some way. Usually, this project relates to some real world phyiscal or logical grouping of entities in the real world. In general, but not always, a project "owns" a set of artifacts rather than simply being composed of them. Examples of a project include an organization, a company, a team or even a single person.
  • Collection entities
    • A collection is an arbitrary grouping of projects. The relationship between collections and projects is not necessarily one of ownership. For instance, a collection could be a set of projects that are related to a specific topic, or perhaps they're a set of projects involved in the same event. A collection could also be a set of projects that simply relate to another project. We see collections as a flexible way to group related projects but each collection may have a different purpose.

An artifact model with dimensions​

The model is the core abstraction of the semantic layer. It represents an object in the OSO data warehouse that corresponds to a specific table. This model then has a set of attributes that are one of Dimension, Measure, or Relationship. Defining a model is done by creating a Model object with the appropriate attributes.

To start, let's define a very basic model for the artifact table. This definition will be incomplete but it will give us a starting point to understand how to define a model.

from oso_semantic import Model, Dimension

artifact_model = Model(
    name="artifact",
    table="iceberg.oso.artifacts_v1",
    dimensions=[
        Dimension(
            name="id",
            description="Unique identifier for the artifact",
            column_name="artifact_id",
        ),
        Dimension(
            name="name",
            description="Name of the artifact",
            column_name="artifact_name",
        ),
        Dimension(
            name="namespace",
            description="Namespace of the artifact",
            column_name="artifact_namespace"
        ),
        Dimension(
            name="source",
            description="Source of the artifact",
            column_name="artifact_source"
        ),
        Dimension(
            name="url",
            description="URL of the artifact",
            column_name="artifact_url"
        ),
    ]
)

At its most basic, a model must have a name, a table, and one or more attributes. Here we've defined some basic dimensions for the artifact model. Let's query this model to see what it looks like when translated to SQL. For this we must first register this model to a registry and then create a query against it.

To create the registry:

from oso_semantic import Registry

registry = Registry()
registry.register(artifact_model)

To query, we use the select method on the registry, which creates a QueryBuilder object that allows us to build a query against the registered models.

from oso_semantic import QueryBuilder

query: QueryBuilder = registry.select(
    "artifacts.id",
    "artifacts.name"
)

print(query.sql(pretty=True)) # Pretty print the SQL query

When querying, the select method simply takes a list of attributes that we want to query. These attributes are specified in the format <model_name>.<attribute_name>. In this case, we are querying the id and name attributes of the artifact model. In the final line, we call query.sql(pretty=True) and print the generated sql query to the console. This will output as follows:

note

The sql generated by the semantic layer will actually be a bit more verbose. This example is simplified to show the basic structure of the query.

SELECT
    artifact.artifact_id as artifact__id,
    artifact.artifact_name as artifact__name
FROM iceberg.oso.artifacts_v1 as artifact
GROUP BY 1, 2

From this query you will notice that the tool is translating dimension names to actual columns names in the underlying table. The artifact.artifact_id is the column name in the iceberg.oso.artifacts_v1 table that corresponds to the id dimension of the artifact model.

Dimensions can also be filtered on. Let's filter only for artifacts that are from the GITHUB source. We can do this by adding a filter to the query:

query_with_filter: QueryBuilder = registry.select(
    "artifact.id",
    "artifact.name",
).where(
    "artifact.source = 'GITHUB'"
)
print(query_with_filter.sql(pretty=True))

The where method allows us to add a filter to the query using sql expressions. The expressions look very similar to standard sql but instead of columns being referenced, we use the model's attributes for filtering. The output of this query will look like this:

SELECT
    artifact.artifact_id as artifact__id,
    artifact.artifact_name as artifact__name
FROM iceberg.oso.artifacts_v1 as artifact
WHERE artifact.artifact_source = 'GITHUB'
GROUP BY 1, 2

Adding measures to the artifact model​

Dimensions are useful for querying specific attributes of a model, but usually we also want to perform some kind of aggregation on the data. This is where measures come in. Measures are attributes that represent some kind of aggregation of the data in the model. For instance, we might want to know the count of artifacts or the distinct count of artifacts.

Here's an updated definition of the artifact model that includes measures and a more complete set of dimensions:

from oso_semantic import Model, Dimension, Measure

artifact_model = Model(
    name="artifact",
    table="iceberg.oso.artifacts_v1",
    primary_key="artifact_id",
    dimensions=[
        Dimension(
            name="id",
            description="Unique identifier for the artifact",
            column_name="artifact_id",
        ),
        Dimension(
            name="name",
            description="Name of the artifact",
            column_name="artifact_name",
        ),
        Dimension(
            name="namespace",
            description="Namespace of the artifact",
            column_name="artifact_namespace"
        ),
        Dimension(
            name="source",
            description="Source of the artifact",
            column_name="artifact_source"
        ),
        Dimension(
            name="url",
            description="URL of the artifact",
            column_name="artifact_url"
        ),
    ],
    measures=[
        Measure(
            name="count",
            description="Count of artifacts",
            query="count(self.id)",
        )
    ],
)

Now we have a more complete model that also includes the measure count of artifacts. You will notice, that unlike a dimension, a measure does not have a column_name attribute. Instead, it has a query attribute that defines how the measure is calculated. In this case, the count measure is defined as count(self.id). Measures may only reference attributes of models. Related models can also be referenced (covered later) but to prevent any ambiguity, the semantic layer provides a special self keyword that resolves to the current model instance.

If we query this model again, we can see how the measures are included in the SQL query:

# Recreate the registry with the updated model
registry = Registry()
registry.register(artifact_model)

# Query the model with the measure.
# This query will select the count of artifacts in a given source
count_query: QueryBuilder = registry.select(
    "artifact.source",
    "artifact.count"
)

print(count_query.sql(pretty=True))

The output of this query will look like this:

SELECT
    artifact.artifact_source as artifact__source,
    count(artifact.artifact_id) as artifact__count
FROM iceberg.oso.artifacts_v1 as artifact
GROUP BY 1

Much like, dimensions, measures can also be filtered on. For instance, if we wanted to only return artifact sources that have more than 100 artifacts, we could do the following:

count_query_with_filter: QueryBuilder = registry.select(
    "artifact.source",
    "artifact.count"
).where(
    "artifact.count > 100"
)
print(count_query_with_filter.sql(pretty=True))

The generated SQL query will look like this:

SELECT
    artifact.artifact_source as artifact__source,
    count(artifact.artifact_id) as artifact__count
FROM iceberg.oso.artifacts_v1 as artifact
GROUP BY 1
HAVING count(artifact.artifact_id) > 100

Adding relationships to the artifact model​

Relationships are used to define how models relate to each other. This allows the semantic layer to provide automatic joins between models when querying. In order for this to work, we need to define the relationships between models and classify them as either one_to_one, one_to_many or many_to_one. Many to many relationships are not explicitly modeled in the semantic layer but can be represented by a join table that is defined as a model.

note

One potentially unintuitive aspect of the semantic layer is that relationships are defined in a single direction and this is strictly enforced by the model registry. This allows us to treat the entire semantic layer as a directed acyclic graph. References within a model can happen in any direction but we force the definition to define a direction of the relationship. This is currently a limitation of the semantic layer and could change in the future.

Let's add a model to represent the project entity called project and add the relationships between the artifact and project models. Despite roughly having a constraint that an artifact may only belong to a single project, the data model is flexible enough to allow an artifact to be related to many projects. For this reason, we model the artifact and project relationship as a many to many relationship. This means we will also model the join table as a model in the semantic layer.

from oso_semantic import Model, Dimension, Measure, Relationship

# Let's redefine the registry and include the models without using
# intermediate variables
registry = Registry()


registry.register(Model(
    name="projects",
    description="A project is a collection of artifacts",
    primary_key="project_id",
    dimensions=[
        Dimension(
            name="project_id",
            description="Unique identifier for the project",
            column_name="project_id",
        ),
        Dimension(
            name="project_name",
            description="Name of the project",
            column_name="project_name",
        ),
        Dimension(
            name="project_description",
            description="Description of the project",
            column_name="project_description",
        ),
    ],
))

register.register(Model(
    name="artifacts_by_project",
    table="iceberg.oso.artifacts_by_project_v1",
    primary_key="artifact_id",
    description="Join table between artifacts and projects",
    dimensions=[
        Dimension(
            name="artifact_id",
            description="Unique identifier for the artifact",
            column_name="artifact_id",
        ),
        Dimension(
            name="project_id",
            description="Unique identifier for the project",
            column_name="project_id",
        ),
    ],
    relationships=[
        Relationship(
            name="project",
            ref_model="project",
            type="many_to_one",
            source_foreign_key="project_id",
            ref_key="project_id",
        )
    ]
))

registry.register(Model(
    name="artifacts",
    table="iceberg.oso.artifacts_v1",
    primary_key="artifact_id",
    dimensions=[
        Dimension(
            name="artifact_id",
            description="Unique identifier for the artifact",
            column_name="artifact_id",
        ),
        Dimension(
            name="artifact_name",
            description="Name of the artifact",
            column_name="artifact_name",
        ),
        Dimension(
            name="artifact_namespace",
            description="Namespace of the artifact",
            column_name="artifact_namespace"
        ),
        Dimension(
            name="artifact_source",
            description="Source of the artifact",
            column_name="artifact_source"
        ),
        Dimension(
            name="artifact_url",
            description="URL of the artifact",
            column_name="artifact_url"
        ),
    ],
    measures=[
        Measure(
            name="count",
            description="Count of artifacts",
            query="count(self.id)",
        ),
    ],
    relationships=[
        Relationship(
            name="by_project",
            description="Relationship to the artifacts_by_project model",
            ref_model="artifacts_by_project",
            type="many_to_one",
            source_foreign_key="artifact_id",
            ref_key="artifact_id",
        )
    ]
))

In this we have now defined the relationships between the artifacts and the projects by mapping how the reference in one model translates to the referenced model. The relationships themselves are not defined using model objects explicity but rather reference models by names. This allows a more flexible way to define each of the models. Once you start querying the models, however, the registry is treated as immutable and the relationships are validated to ensure that the model and any relationships are valid and do not have cycles.

Now if we query for both artifacts model attributes and projects model attributes, the semantic layer will automatically join the two models together:

query = registry.select(
    "artifacts.name",
    "projects.name",
)
print(query.sql(pretty=True))

The output of this query will look like this:

SELECT
    artifacts_12345678.artifact_name as artifact__name,
    projects_12345678.project_name as project__name
FROM iceberg.oso.artifacts_v1 as artifacts_12345678
LEFT JOIN iceberg.oso.artifacts_by_project_v1 as artifacts_by_project_1b23c4d
    ON artifacts_12345678.artifact_id = artifacts_by_project_1b23c4d.artifact_id
LEFT JOIN iceberg.oso.projects_v1 as projects_12345678
    ON artifacts_by_project_1b23c4d.project_id = projects_12345678.project_id
GROUP BY 1, 2