Merge Statements

Merge statements unify concepts from different sources, allowing them to be queried together. This is useful when the same logical concept exists in multiple namespaces.

A merge defines a relationship for subsequent queries in the model. For an ad hoc relationship in one select, use a query-scoped join. Use union(...) to stack independent query results into shared columns.

Syntax

syntax-example

merge_statement: "merge" WILDCARD_IDENTIFIER "into" SHORTHAND_MODIFIER? WILDCARD_IDENTIFIER
                 | "merge" ("subset" | "union" | "equal") WILDCARD_IDENTIFIER "=" WILDCARD_IDENTIFIER

Basic Merge

Merge one concept into another when they represent the same complete domain:

import sales.customer as sales_customer;
import support.customer as support_customer;
merge sales_customer.id into support_customer.id;

After merging, queries can join data across both sources using the merged concept.

Wildcard Merge

Use wildcards to merge multiple concepts at once:

merge sales.* into main.*;

This merges all matching concepts from the sales namespace into the main namespace.

Partial Modifier

Use ~ to mark the merge as partial (the merged concept has a subset of values of the full concept).

Tips

This is very common for blending into a date dimension, for example - you may not have an order on every day.

merge sales.customer.id into ~main.customer.id;

Explicit Domain Relations

The relation spelling makes the coverage claim explicit:

Declaration	Relationship
merge equal a = b;	Same complete domain; equivalent to merge a into b;
merge subset a = b;	a is a subset of b; equivalent to merge a into ~b;
merge union a = b;	Either side may have values absent from the other; preserve both populations

For independent customer populations with possible overlap:

import sales.customer as sales;
import support.customer as support;
merge union sales.id = support.id;
select sales.id, support.id;

These relations persist for later queries in the model. Use union join for a relationship limited to one query, and union(...) when you want to stack result rows. merge union establishes a domain relationship, rather than appending two tables' rows.

Example: Connect Orders to Customers

Declare order customer IDs as a subset of the customer model's complete IDs. This reusable relationship lets later queries include customers with no orders:

import orders as orders;
import customers as customers;
merge orders.customer_id into ~customers.id;
select
    customers.id,
    customers.name,
    coalesce(sum(orders.total), 0) as total_spend,
order by total_spend desc;