Look up reference data

This example enriches two orders with a region owner. It uses two Postgres ingress endpoints and the bundled lookup transform with primary and reference inputs. A left lookup preserves unmatched primary records using the configured missing-match behavior.

What you need

You need a Semogram account with workspace membership, a project in that workspace and permission to create and run pipelines. Reading or writing also requires access to the selected endpoints and external systems. Creating a pipeline does not grant those permissions.

Use a reachable test PostgreSQL database and its SQL client. You need installation/endpoint management access as well as pipeline read/write/execute access. The connection must be reachable from the execution runtime, not only from your laptop.

Prepare both datasets

CREATE TABLE public.pipeline_demo_orders (
  order_id integer PRIMARY KEY,
  region text NOT NULL,
  total numeric(12,2) NOT NULL
);
INSERT INTO public.pipeline_demo_orders VALUES
  (1, 'North', 120.00),
  (2, 'South', 75.50);

CREATE TABLE public.pipeline_demo_regions (region text PRIMARY KEY, owner_name text NOT NULL);
INSERT INTO public.pipeline_demo_regions VALUES
  ('North', 'Nora'), ('South', 'Sam');

Open workspace Plugins → Explore, find Postgres (@craven/postgres), inspect its installed contract and install the read capability. Fill these connection fields and run its supported connection check:

Open workspace Data Endpoints → New data endpoint. Use name demo_orders, namespace pipelines, direction Ingress, the installed Postgres read capability and Stream (source_stream) as the source contract. In the target fields set Schema to public and Table to pipeline_demo_orders. Save and inspect its schema/sample. demo_orders is a name you choose in Semogram; public.pipeline_demo_orders is the actual database table. Keep its returned endpoint UUID for programmatic examples.

The Postgres installation guide and endpoint guide provide additional detail; the required setup for this fixture is included here.

Create another Ingress endpoint using the same installed Postgres read capability: name demo_regions, namespace pipelines, Stream contract, Schema public, Table pipeline_demo_regions. Verify its two rows. The Semogram name is a label; the schema/table identify the physical reference dataset.

Install and configure lookup

In workspace Plugins → Explore, find Craven Transforms (@craven/transforms), inspect the required operation and install/enable it. Its bundle installation configuration is empty; the operation’s business parameters belong on the transform node. Use the capability installation ID, not the bundle installation ID, for capabilityRefId. The Transforms guide provides optional background.

Select Lookup Transform (lookup). In Studio create two ingress nodes and one transform. Both reads use Full load and parser extraction. Name their output pools orders and regions. Connect orders to transform port primary and regions to port reference; this manifest does not use one generic input for both datasets.

Read demo_orders and demo_regions in full mode. Use the installed lookup capability with orders as primary and regions as reference. Match region to region, use a left join, fill unmatched owners with null, and copy owner_name to region_owner. Show both targets and bindings before saving.

{
  "capabilityRefId": "<LOOKUP_CAPABILITY_UUID>",
  "function": "lookup",
  "input": {
    "primary": "orders",
    "reference": "regions"
  },
  "output": "enriched_orders",
  "params": {
    "primaryKey": "region",
    "referenceKey": "region",
    "joinType": "left",
    "onMissing": "null",
    "fields": [
      {
        "source": "owner_name",
        "target": "region_owner"
      }
    ]
  }
}

Validate, save and run

Validate the draft and resolve errors. Preflight checks configuration only; it does not produce records or run a model. Save version, inspect the active saved graph and Run once. Open the run and compare the named step's output with the expected result below. Keep the run ID and inspect errors/counts, not only the assistant's response. Programmatic launch uses HTTP POST /api/v1/projects/<PROJECT_ID>/pipelines/<PIPELINE_ID>/execute with pipelines:execute and an Idempotency-Key, or MCP pipeline_execute with pipelineId, projectId and idempotencyKey.

If the Studio result only exposes an artifact handle, open the corresponding output/artifact inspection rather than treating that handle as a row preview. To persist rows externally, add an Egress endpoint and test its write semantics; the examples below do not silently create a destination.

Expected result

Test an order with an absent region and a reference dataset with duplicate keys. Verify the installed transform’s behavior rather than assuming every lookup is one-to-one. Inspect explicit lineage back to both inputs. Keep target names and keys distinct from endpoint UUIDs and transform installation IDs.