Sooner or later every Looker project hits the same request: "can we put Salesforce pipeline next to warehouse revenue on one tile?" The data lives in two Explores, sometimes in two different databases, and there is no single SQL query that can reach both.
Looker gives you several answers — Merged Results, a cross-database join in the warehouse, or simply modeling the relationship in LookML. They are not interchangeable. This tutorial explains what each one does at execution time, shows the failure modes that generate wrong numbers, and ends with a decision path you can hand to your analysts.
1. What Merged Results actually does
Merged Results is a post-query feature. You build a primary query in one Explore, add one or more secondary queries from other Explores, and map fields between them. Looker then:
- Runs each query independently against its own connection.
- Pulls all result sets back into the Looker application.
- Performs a left join in memory, keyed on the fields you mapped.
Three consequences fall straight out of that:
- Row limits apply per query. Each sub-query is capped (500 rows by default in the UI, up to the row limit you set). If the secondary query truncates, the merge silently produces nulls for the missing keys. This is the single most common source of "the merged tile disagrees with the source tile".
- Aggregation happens before the merge. Every sub-query is already grouped. You cannot re-aggregate across the join the way SQL would; a sum of a merged measure is a sum of pre-aggregated values.
- No database-side filtering between queries. A filter on the primary query does not constrain the secondary query unless you apply it there too.
Building one correctly
- Start from the Explore whose grain you want to keep: the primary query defines the row set.
- Add the secondary query and check the merge keys Looker proposes. It guesses by field name and label; verify each mapping by hand.
- Make the merge key unique in the secondary query. If
order_idappears twice on the secondary side, you get duplicated primary rows — the classic in-memory fan-out. - Raise row limits on both queries, then sanity-check the row counts against the standalone queries.
- Use table calculations for cross-source math (
${orders.total_revenue} / ${salesforce.pipeline_amount}); you cannot write a LookML measure across merged results.
When Merged Results is the right call
It is genuinely good for: small, bounded result sets; ad-hoc exploration; one-off executive tiles; sources that will never live in the same warehouse (a SaaS API, a spreadsheet upload). It is a reporting convenience, not a modeling layer.
2. Cross-database joins in the warehouse
If both sources are reachable from one engine, join them before Looker sees them. Modern warehouses make this easier than it used to be:
- BigQuery: federated queries with
EXTERNAL_QUERY()to Cloud SQL/Spanner, BigLake and object tables for lake data, cross-project joins by fully qualified name, and BigQuery Omni for other clouds. - Snowflake: cross-database joins inside an account are free-form; across accounts use secure data sharing or the Marketplace listing as a read-only database.
- Databricks: Lakehouse Federation (
CREATE FOREIGN CATALOG) exposes external systems as catalogs you can join to Delta tables.
Two Looker-specific notes:
- A Looker connection is scoped to one database/catalog context, so the join has to be legal for the user that connection authenticates as. Grant the connection's service account read access to both sides, including the federation objects.
- Federated scans are frequently the slowest part of the query and often are not cached by the accelerator layer (BI Engine will not accelerate an
EXTERNAL_QUERYleg). Materialize instead of federating on every dashboard load:
view: pipeline_with_revenue {
derived_table: {
sql:
SELECT
w.account_id,
w.order_date,
w.revenue,
c.stage,
c.pipeline_amount
FROM `analytics.fact_revenue` w
LEFT JOIN EXTERNAL_QUERY(
"us.crm-connection",
"SELECT account_id, stage, pipeline_amount FROM opportunities"
) c
ON c.account_id = w.account_id ;;
datagroup_trigger: daily_etl
partition_keys: ["order_date"]
}
dimension: account_id { primary_key: yes; type: string; sql: ${TABLE}.account_id ;; }
dimension_group: order { type: time; timeframes: [date, week, month]; sql: ${TABLE}.order_date ;; }
measure: revenue { type: sum; sql: ${TABLE}.revenue ;; }
measure: pipeline_amount { type: sum; sql: ${TABLE}.pipeline_amount ;; }
}
The PDT rebuilds on the datagroup, and every dashboard query then hits one local table.
3. The option people forget: model it
Before reaching for either of the above, ask whether the two Explores could simply be one Explore. If the data is already in the same warehouse, a modeled join is faster, cacheable, reusable, testable in CI, and does not depend on an analyst remembering to map keys correctly.
explore: orders {
join: accounts {
type: left_outer
relationship: many_to_one
sql_on: ${orders.account_id} = ${accounts.id} ;;
}
join: crm_opportunities {
type: left_outer
relationship: one_to_many
sql_on: ${accounts.id} = ${crm_opportunities.account_id} ;;
}
}
With a one_to_many join declared honestly, Looker applies symmetric aggregates and your orders.total_revenue stays correct despite the fan-out — something an in-memory merge will never do for you. If the grains genuinely do not reconcile (daily budget vs. transaction-level actuals), model the bridge explicitly with a date-spine or a union-style derived table rather than merging at report time.
4. Failure modes to check for
| Symptom | Likely cause | Fix |
|---|---|---|
| Merged tile totals lower than source tile | Secondary query hit its row limit | Raise limits; aggregate the secondary query to a coarser grain |
| Rows duplicated after merge | Merge key not unique on the secondary side | Pre-aggregate the secondary query to one row per key |
| Nulls for recent dates only | Filters applied only to the primary query | Apply the equivalent filter to every sub-query |
| Dashboard filter does nothing | Filter not mapped to all sub-queries | Re-map the dashboard filter to each merged query |
| Merged tile slow and un-cacheable | Merge runs per render, in-app | Move the join into a PDT or a modeled Explore |
| Access filters not applied to the second source | Secondary Explore lacks equivalent access_filter | Mirror row-level security in both models |
That last row matters most. Row-level security is enforced per Explore. If one of the two Explores is unrestricted, merging can leak data a user should not see. Audit both sides before you publish anything cross-source to a broad audience.
5. A decision path
- Same warehouse, related entities? → Model the join in LookML. Default answer.
- Same warehouse, mismatched grain? → Derived table (or NDT with
explore_source) that reconciles the grain, then join. - Different systems, but federation available? → Cross-database join materialized into a PDT on a datagroup.
- Different systems, no federation, small results, ad-hoc? → Merged Results, with row limits raised and merge keys verified.
- Different systems, large volumes, business-critical? → Fix it in the pipeline. Land both sources in the warehouse; no reporting-layer trick replaces ingestion.
6. Governance checklist before you ship a cross-source tile
- Merge keys documented in the tile description.
- Row limits explicit and comfortably above actual row counts.
- Both sub-queries carry the same time filter and the same access filters.
- A LookML data test covering the modeled equivalent, if one exists.
- An owner and a review date — merged tiles rot quietly when either source changes.
Where Vistelio comes in
Most of the "our numbers do not match" tickets we get called into trace back to a cross-source shortcut that was never meant to become a production dashboard. Our Looker consultants untangle those: we work out which combinations belong in the semantic layer, which belong in the warehouse, and which should never have been joined at all — then implement the model, the tests, and the CI that keeps it honest.
If you are combining Looker data across systems and are not confident the totals are right, get in touch or take a look at our Looker health check and technical audit.