Datajunction Semantic Model

DataJunction Semantic Modeling

Use this skill when designing how something gets expressed as DJ nodes — independent of whether you'll write the result as YAML files in a repo or POST it to the DJ API.

For DJ vocabulary (what node types exist, how dimension links work mechanically), see the datajunction skill. This skill assumes that vocabulary and focuses on the modeling decisions.

General principles

Reuse over reinvent

Before authoring a new node, check whether an authoritative one already exists. Use the datajunction-query skill's MCP tools (search_nodes, get_node_details) to find candidates. Every additional transform / dim / metric fragments the catalog; every reuse strengthens it.

When you can't find a fit:

• Build on the closest existing parent rather than creating a sibling source/transform that duplicates upstream logic.
• Refuse to build on non-git-backed namespaces. Nodes without a source repo can change or disappear without review — depending on them silently couples your work to someone's UI session. Stick to git-backed namespaces (see datajunction-api's "Checking if a Namespace Is Repo-Backed" section for how to verify).

Every node has owners

Not just metrics — every DJ node should declare owners. The reasons apply uniformly across all node types:

• No one to contact when the node breaks or produces unexpected values
• Undefined responsibility for keeping definitions accurate
• Difficult to coordinate changes or deprecations
• Team knowledge scattered across individuals

Best practices:

• ✅ Prefer team emails over individuals — teams outlast individual contributors
• ✅ Use Google groups or distribution lists for teams
• ✅ Include both data team AND business stakeholder teams where applicable
• ❌ Never leave owners field empty or omit it

✅ GOOD — team ownership:
  data-platform-team@company.com
  finance-analytics@company.com

⚠️ ACCEPTABLE — individual ownership (less sustainable):
  alice@company.com

❌ BAD — no owners (governance nightmare!)

This applies to sources, transforms, dimensions, metrics, and cubes alike.

Naming conventions

Use fully qualified names with namespace:

namespace.node_name

Examples:

• ✅ finance.total_revenue
• ✅ common.dimensions.users
• ✅ clean.user_events
• ❌ revenue (missing namespace)

Names should be readable and business-meaningful — what a stakeholder would call this thing, not the column transformations behind it. total_revenue over sum_amount_usd. avg_session_duration_secs over avg_session_dur.

Namespace organization

Namespaces are organized by business area:

Common conventions:

• common.dimensions.* — shared dimensions (users, dates, regions)
• finance.* — financial metrics & facts
• growth.* — user engagement & activation
• product.* — product usage & features
• source.* — raw source tables

Model named, reusable entities — not anonymous SQL

A general spirit that runs through every layer:

• Don't bury an aggregate inside a derived metric's query when it should be its own named base metric (see the Metrics section below for the reusability rule).
• Don't bury a join condition inside a query when it should be a dimension link (see "Joins → dimension links" below).

The semantic layer has two goals working together:

• Express the business in business concepts. Stakeholders should see signups, revenue, active customers — not a chain of tables and SQL transformations. Every node should have a name and meaning a non-engineer would recognize.
• Make every meaningful thing findable, queryable, and reusable. Anonymous SQL — inline aggregates, hardcoded joins — works against both goals: it hides business concepts behind technical detail and forecloses reuse.

The dimensional model

DJ uses normalized star schema modeling. The decisions you make here shape everything downstream.

Facts vs dimensions vs transforms

When to author a transform vs use a source directly:

• Use the source directly when its row shape is the semantic entity you want to aggregate.
• Author a transform when the source needs cleaning (status IN ('complete', 'completed') → 'completed'), joining, or filtering before it represents the entity meaningfully.
• The transform is where you attach dimension links — that determines what slices are available to every metric built on it.

Grain is the most important decision in a fact

What does one row in your fact transform represent?

• "One row per order" → grain is order_id
• "One row per order per snapshot day" → grain is (order_id, snapshot_date)
• "One row per customer per month" → grain is (customer_id, month)

Grains don't mix in one fact. If you find yourself with two different grains in one transform, you have two facts trying to live in one node — split them.

Joins → dimension links, not baked-in JOINs

The DJ idiom: every JOIN that connects a fact to a dimension belongs in a dimension_links: declaration on the fact's transform — not inside a metric's query.

Why: dimension links make the join optional. Consumers slice by that dim only when they ask for it. A hardcoded JOIN in a metric forces every query that touches the fact to pay the join cost even when nobody's slicing by that dim. It also fragments behavior — metrics on the same fact would each carry their own copy of the same JOIN, with the inevitable drift.

Same applies to dim-to-dim joins (e.g., users → countries → regions) — express them as dimension links on the dimension node, creating a dimensional graph DJ can traverse automatically.

See the datajunction skill's "Dimension Links" section for the mechanics; this is about when to use them — which is essentially always.

Metrics

A metric is one aggregation expression over a single source, transform, or dimension node.

Metric query constraints

Metrics cannot contain WHERE clauses

• Use CASE WHEN for conditional aggregation instead

-- ❌ NOT ALLOWED — WHERE clause in metric
SELECT SUM(amount_usd)
FROM finance.transactions
WHERE status = 'completed'

-- ✅ CORRECT — use CASE WHEN
SELECT SUM(
  CASE WHEN status = 'completed' THEN amount_usd ELSE 0 END
) FROM finance.transactions

A WHERE permanently constrains scope; CASE WHEN lets the metric coexist with a broader-population sibling on the same fact. Reflect the scope in the metric name (active_signups, not signups).

Metrics select a single expression from a single node

• Cannot join multiple nodes in a metric query
• Define joins via dimension links on the source/transform node instead

Metrics can reference other metrics (composition)

Build derived metrics by referencing other metrics in the query. Excellent for ratios, rates, and complex calculations.

-- Create base metrics first
SELECT COUNT(*) FROM finance.transactions              -- metric: finance.transaction_count
SELECT SUM(amount_usd) FROM finance.transactions       -- metric: finance.total_revenue

-- Then compose them into a derived metric
SELECT finance.total_revenue / finance.transaction_count  -- metric: finance.avg_transaction_value
-- DJ handles divide-by-zero automatically; NULLIF() is optional safety

The reusability rule: every aggregate is its own named metric

This is the highest-leverage discipline in DJ metric modeling. Apply it even when "the user only cares about the final ratio."

❌ Less reusable — anonymous SQL blob:

SELECT SUM(amount_usd)
       / NULLIF(SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END), 0)
FROM finance.transactions

✅ Reusable — three named, discoverable, composable metrics:

-- metric: total_revenue
SELECT SUM(amount_usd) FROM finance.transactions

-- metric: num_completed_orders
SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)
FROM finance.transactions

-- metric: avg_order_value (derived)
SELECT total_revenue / NULLIF(num_completed_orders, 0)

Why: the bottom shape gives three named metrics anyone can find, query, and reuse. The top shape gives one — the base aggregates are anonymous SQL blobs nobody else can reference. Different teams asking "what's our total revenue?" can't find it; they'd have to know to read inside avg_order_value's query.