Setup

Creating a lookup metric

Add the metric

Navigate to Metric in the top navigation.
Click the Add button (⊕).
Enter a Metric Name.
Select Subcategory: Lookup from the dropdown.
Enter a Purpose description.
Click Create.

Open the configuration view

Click the metric name to open its detail page. The lookup metric configuration view exposes:

Asset — the table containing the column(s) to validate
Metadata tabs — Description, Purpose, Rationale
Lookup Metric Configuration — lookup source, composite key mapping, comparison logic, and matching rules
Threshold
Semantic context and Owners

Set the asset and metadata

Asset: Select the table or view containing the column(s) to validate. Use the AI-assisted asset selection button to let Prizm suggest an asset based on the metric name and purpose.The Downstream Impact counter shows how many other assets depend on this asset.Fill in the metadata tabs as needed:

Tab	Content
Description	What this metric measures. Prizm AI can suggest a description based on the metric name.
Purpose	Business justification — why referential validity matters for this column.
Rationale	The reasoning behind the chosen reference source and matching logic.

Select and configure the lookup source

Choose the reference to validate the target column values against. Select a source type from the toolbar, then follow the configuration steps for that type.

Source Type	Description
Table	A connected asset from any source — same platform or cross-platform
API	A REST or GraphQL endpoint returning the valid reference values
Reference File	An uploaded CSV, JSON, or Parquet file used as a static reference
Query	A SQL query that dynamically returns the valid reference values on each run

Table
API
Reference File
Query

Validate against a connected reference table. Use when the valid values live in a table already connected to Prizm — e.g., a products dimension table, a customers master, or any lookup table in your warehouse.

Select Table as the lookup source type.
Select connector — choose the data source connection where the reference table lives. This can be a different platform from the source asset.
Select reference table — choose the table or view containing the valid values.
Select reference column — choose the column that holds the valid entries (e.g., product_id).
Prizm previews a sample of the reference values to confirm the selection is correct.

The reference table does not need to be on the same data platform as the source asset. Cross-source lookups (e.g., validating a Databricks column against a Snowflake dimension table) are fully supported.

Validate against a live API endpoint. Use when the authoritative reference is served by a REST or GraphQL API — e.g., a CRM, an ERP, or an internal microservice.

Select API as the lookup source type.
API Endpoint — enter the full URL (e.g., https://api.example.com/valid-products).
Method — select the HTTP method: GET (most common), POST, or PUT.
Request Parameters (optional) — click + Add parameter to add query string or body parameters needed by the endpoint.
Response Mapping — map the field in the API response that contains the valid values to the source column being validated:
Source Column API Response Field
customer_id id
country_code country
Prizm calls the API and previews the returned values before saving.

Source Column	API Response Field
`customer_id`	`id`
`country_code`	`country`

Validate against a dynamically-computed reference set. Use when the valid values need to be freshly computed on each run — e.g., active product codes, current account IDs, or any reference that changes frequently.

Select Query as the lookup source type.
Select connector — choose the data source connection to execute the query against.

Write a SQL SELECT query that returns the column(s) of valid values:

-- Example: active product IDs from the products table
SELECT product_id
FROM products
WHERE status = 'active'

Select the reference column — choose which column in the query result contains the valid values.
Click Validate to run the query and preview the returned reference set before saving.

The query re-executes on each metric run, keeping the reference current without manual file uploads or table refreshes.

Configure the composite key mapping

Map one or more source columns to their corresponding reference columns. Prizm uses these mappings as the join key to determine whether each source value has a valid match in the reference.

Source Column	Reference Column
`orders.product_id`	`products.product_id`
`orders.country_code`	`iso_codes.code`

Click + Add key to add additional columns for a composite lookup key.

Advanced matching options

Expand Advanced Matching to control how values are compared:

Option	Description
Case-insensitive	Treat `"US"` and `"us"` as matching
Trim whitespace	Remove leading and trailing spaces before comparing
Normalize values (AI-assisted)	Use Prizm AI to resolve common variations — e.g., abbreviations, alternate formats, or regional spelling differences

Prizm AI can also auto-suggest the most likely key mapping based on column names and data types.

Set the comparison logic

Choose what outcomes count as failures and how the metric score is computed.

Match type

Option	Description
Exact match	Source value must exactly match a reference value (after any normalization)
Partial match	Configurable tolerance — values within an acceptable deviation pass

On mismatch — what to flag

Option	Description
Missing in reference	Source value has no matching entry in the reference
Missing in source	Reference value has no corresponding entry in the source (orphaned reference)
Value mismatch	The composite key matches but one or more non-key columns differ

Metric output (alert basis)

Output	Description
Match rate % (Default)	Percentage of source values with a valid reference match
Mismatch count	Absolute number of unmatched records
Missing count	Absolute number of records missing in reference or source
Drift vs baseline	Change in match rate relative to the historical baseline

Rule options

Option	Description
Valid	Count only matched records as valid
Scoring	Include this metric in the asset quality score
Weightage%	Relative weight of this metric in the overall score (default 100)
Pass Criteria	Match rate percentage above which the metric passes (default `>= 95`)
Export	Include results in data exports

An inline preview shows a sample of matched and unmatched records before saving.

Set the execution trigger

Choose when the lookup validation runs:

Trigger	Description
Event-based	Runs automatically when the source asset is updated — aligned with the asset’s operational job cycle
Scheduled	Runs on a fixed interval (e.g., every 6 hours, daily) independent of the asset job

Set the threshold

Choose how Prizm determines when to raise an alert:Dynamic Threshold — Prizm learns statistical baselines from historical match rate values and alerts when the rate deviates by a configurable number of standard deviations:

Priority	Deviation range	Default
Low	0σ – 1σ	Off — within expected variation
Medium	1σ – 2σ	Off — monitor for drift
High	2σ – 3σ	On — investigation recommended
Critical	≥ 3σ	On — immediate action required

Deterministic Threshold — Set an explicit match rate floor to trigger alerts (e.g., alert when match rate < 98%).Additional alert conditions:

Condition	Description
Alert on new unseen lookup values	Fires when source values appear that have never matched any reference entry
Alert on sudden spike in mismatches	Fires when mismatch count increases sharply within a single run

Set semantic context and owners

Scroll down past the Threshold section to complete:

Semantic context: Assign Domain, Application, Product, Tag, and Dimension to make the metric discoverable and contextually relevant.
Owners: Set Business owner, Technical owner, and Steward.
Audience: Restrict who can view this metric’s results.

Click Save Changes when done.

Get Started

Core Concepts

Platform

Architecture

Data Sources

Integrations

Security & Access Control

Deployment

Help

Settings

MCP

Release Notes

API Reference