Path ambiguity

IntroductionCopy Anchor Link

When building datasets with multiple models, you may encounter situations where there are multiple possible join paths between two models. This is called path ambiguity, and understanding how the compiler handles it is crucial for building accurate analytics.

For example, let's say you have an e-commerce dataset with the following models and relationships:

Dataset ecommerce {
  ...
  models: [orders, users, order_items, countries, merchants, products, cities]

  relationships: [
    relationship(orders.user_id > users.id, true),
    relationship(order_items.order_id > orders.id, true),
    relationship(cities.country_code > countries.code, true),
    relationship(users.city_id > cities.id, true),
    relationship(order_items.product_id > products.id, true),
    relationship(products.merchant_id > merchants.id, true),
    relationship(merchants.city_id > cities.id, true)
  ]
}

A dataset like this compiles with a warning about ambiguous paths. The warning is there to raise awareness, not to block you: the dataset still works, and the compiler resolves the ambiguity for you at query time.

This page explains how that resolution works so you can understand which path the compiler chooses and why.

Let's look at a concrete example. Say you want to answer: "What's the total order quantity by country?" You combine a dimension from countries with a measure from order_items:

explore {
  dimensions {
    country_name: countries.name
  }
  measures {
    total_order_quantity: sum(order_items.quantity)
  }
}

Here's where ambiguity comes in. There are two valid ways to join from countries to order_items:

• Path A: countries → cities → users → orders → order_items (orders by customer country)
• Path B: countries → cities → merchants → products → order_items (orders by merchant country)

Each path gives you a different answer with a different business meaning: are you analyzing orders based on where your customers live, or where your merchants operate?

How the compiler resolves ambiguityCopy Anchor Link

When multiple paths exist, the compiler selects the most analytically correct path using a ranking algorithm. You can create datasets with all relationships active, and the compiler will choose a path at query time.

The algorithm works in 4 steps:

Step 1: Path Tier ClassificationCopy Anchor Link

All possible paths are categorized into 4 tiers based on common analytics patterns:

• Tier 1 (Best): Only one-to-many relationships (e.g., dimension → fact, like countries → cities → users → orders)
• Tier 2: Only many-to-one relationships (e.g., fact → dimension, like orders → users → cities → countries)
• Tier 3: Special sequential patterns (e.g., many-to-many using junction tables, like users → orders → order_items → products)
• Tier 4: Mixed patterns not matching the above

Step 2: Assign WeightsCopy Anchor Link

Each relationship in a path has a default weight of 0. Weights increase when you explicitly specify relationships:

• Default relationships (defined in dataset): Weight = 0
• with_relationships() specified: Higher weight than default
• Nested with_relationships(): Inner (nested) relationships get even higher weight

This means explicitly specified paths using with_relationships() take priority over default paths.

Step 3: Rank PathsCopy Anchor Link

Paths are compared in this order:

1. Tier first (Tier 1 > Tier 2 > Tier 3 > Tier 4)
2. Weight second (higher scores preferred)
3. Path length third (shorter paths preferred as they're simpler and more performant)

Step 4: Automatic Selection with TransparencyCopy Anchor Link

• Clear winner: the compiler uses the best-ranked path
• Tie situation: if multiple paths have identical rankings, the compiler picks one, emits a warning that names the chosen path, and suggests resolving the ambiguity manually

ExamplesCopy Anchor Link

Example 1: Clear Winner Based on Tier RankingCopy Anchor Link

Query: "Total products by cities"

Path 1: cities → merchants → products (3 hops)

• Business meaning: Total products produced/sold by merchants in each city
• Pattern: cities → merchants → products
• Tier: Only one-to-many relationships → Tier 1 (Best)
• Length: 3 hops

Path 2: cities → users → orders → order_items → products (5 hops)

• Business meaning: Total products bought by users living in each city
• Pattern: cities → users → orders → order_items (junction) → products
• Tier: Many-to-many using junction table (order_items) → Tier 3
• Length: 5 hops

Result: the compiler selects Path 1Copy Anchor Link

• Tier 1 ranks higher than Tier 3 (tier is the primary ranking criteria)
• Path 1 has a simpler dimension → fact pattern
• Path 2 requires a junction table, making it more complex

Both paths are analytically correct but answer different business questions.

Overriding the automatic selectionCopy Anchor Link

If you need Path 2 (products bought by city residents) instead of Path 1 (products sold by city merchants), you can use with_relationships() to control which path to use. We want the customer path instead of the merchant path, so we disable the direct merchant relationship:

Dataset ecommerce {
  ...
  metric products_bought_by_city_residents {
    label: 'Products Bought by City Residents'
    type: 'number'
    definition: @aql
      products
        | count(products.id)
        | with_relationships(
            //highlight-next-line
            relationship(products.merchant_id > merchants.id, false, 'two_way')
          )
    ;;
  }
}

By disabling the products → merchants relationship, we're telling the compiler: "Don't take the direct path through merchants. Instead, use the longer path through order_items, orders, and users to reach cities."

Example 2: Tie Situation with Same Tier, Weight, and Path LengthCopy Anchor Link

Query: "Total order items by countries"

Path A: countries → cities → users → orders → order_items (5 hops)

• Business meaning: Order items from customers in each country
• Tier: Only one-to-many relationships → Tier 1
• Length: 5 hops

Path B: countries → cities → merchants → products → order_items (5 hops)

• Business meaning: Order items from merchants located in each country
• Tier: Only one-to-many relationships → Tier 1
• Length: 5 hops

Result: Both paths have:

• Same tier (Tier 1 - both are valid dimension-to-fact patterns)
• Same length (5 hops)
• Same weights (both use default relationships)

When faced with a tie, the compiler picks one of the paths and emits a warning suggesting that you explicitly define which path to use.

Overriding the automatic selection:

If you need a different path than the one the compiler selected, you can use with_relationships() to explicitly control which path to use. In this example, we want the merchant path instead of the customer path, so we disable the relationship that leads through customers:

Dataset ecommerce {
  ...
  metric orders_by_merchant_countries {
    label: 'Total Orders by Merchant Countries'
    type: 'number'
    definition: @aql
      order_items
        | count(order_items.id)
        | with_relationships(
            //highlight-next-line
            relationship(order_items.order_id > orders.id, false, 'two_way')
          )
    ;;
  }
}

By disabling the order_items → orders relationship, we're telling the compiler: "Don't go through orders to reach countries. Instead, use the alternative path through products and merchants."

When Automatic Resolution Cannot WorkCopy Anchor Link

While automatic path resolution handles most ambiguous path scenarios, there are specific cases where the algorithm cannot determine the correct path automatically. In these situations, you must manually configure your relationships.

Role-Playing DimensionsCopy Anchor Link

Role-playing dimensions occur when the same dimension table is referenced multiple times from a single fact table, each time with a different meaning. The classic example is using a date dimension for multiple date fields in an order fact: created date, delivered date, cancelled date, etc.

Why automatic resolution fails: When you have multiple relationships between the same two models (e.g., fct_orders → dim_dates via created_at, delivered_at, cancelled_at), there is no clear "winner" based on tier, weight, or path length - all paths have identical characteristics.

Solution: You must disable all relationships except one (the most commonly used), and use with_relationships() to explicitly activate the other relationships when needed.

For detailed implementation steps and an alternative approach using extend(), see Role-Playing Dimensions Pattern.

Manual Control OptionsCopy Anchor Link

While automatic resolution works well for most cases, you have full control to override the behavior:

Option 1: Disable Relationships and Use with_relationships()Copy Anchor Link

Use with_relationships() to explicitly specify which path a metric should use. This is ideal when different metrics need different paths.

Dataset ecommerce {
  ...

  relationships: [
    relationship(orders.user_id > users.id, true),
    relationship(order_items.order_id > orders.id, true),
    relationship(order_items.product_id > products.id, true),
    relationship(users.city_id > cities.id, true),
    relationship(cities.country_code > countries.code, true),
    relationship(products.merchant_id > merchants.id, true),
    //highlight-next-line
    relationship(merchants.city_id > cities.id, false)  // The relationship is currently disabled
  ]

  metric order_values_by_merchant_countries {
    label: 'Total Order Values by Merchant Countries'
    type: 'number'
    definition: @aql orders
      | count(orders.id)
      //highlight-next-line
      | with_relationships(merchants.city_id > cities.id)
    ;;
  }
}

When to use:

• You want to completely prevent a certain path from being used across the entire dataset
• You're working with legacy datasets and want to maintain existing behavior

Trade-offs:

• ❌ Disables the path for ALL queries in the dataset
• ❌ Requires with_relationships() to re-enable for specific metrics

Option 2: Duplicate ModelsCopy Anchor Link

Create separate dimension models for different contexts (e.g., user_cities and merchant_cities):

When to use:

• Clear semantic separation is important for end users
• You want to avoid any ambiguity warnings

Trade-offs:

• ❌ Makes the dataset larger and more complex to maintain
• ❌ Duplicates dimension data

Best PracticesCopy Anchor Link

1. Let the compiler handle it: For most cases, the automatic resolution works correctly.

2. Use with_relationships() for exceptions: When you need a specific path that differs from the automatic selection, explicitly declare it.

3. Read the warnings: If the compiler warns about an ambiguous path, review whether the automatic selection makes sense for your use case. The symptoms are listed under Ambiguous relationships.

4. Document complex paths: Add comments in your AML code explaining why specific paths are chosen for metrics.

5. Test your metrics: Always verify that metrics return expected results, especially in datasets with multiple possible paths.