The Semantic Layer, From ScratchChapter 9

Chapter 9

Federation: where the magic happens

Customers in Aurora, purchases in Redshift, one query. Who knows where the data lives, where SPARQL turns into SQL, and where the two halves finally meet.

Monday, 9:04 a.m. A message from your VP: "Who are our top spenders, and what state are they in? Need it for the 10 o'clock." Easy, you think, and open DBeaver. Then it sinks in. Names and states live in Aurora; purchases live in Redshift; no SQL statement you can type joins across both. You can answer the question, the slow way. By the end of this chapter you'll answer it with one query, and you'll be able to point at the exact place, inside Stardog, where the two databases meet.

9.1The slow way (do this once, so you feel it)

Here's the SQL-only route. First Redshift, which knows spend per customer number:

-- Redshift (dev.c360)
SELECT cid, SUM(price * quantity) AS spend
FROM c360.purchase
GROUP BY cid
ORDER BY spend DESC
LIMIT 3;

Top row: 225, 502529.42. Redshift has no idea who 225 is. So you copy the three cids out of the result grid and paste them into a second query, on Aurora:

-- Aurora (c360.c360). Paste the cids from the Redshift result.
SELECT c.cid, c.full_name, a.state
FROM c360.customer c
JOIN c360.address  a ON a.id = c.location
WHERE c.cid IN (225 /*, second cid, third cid */);

Then you match the two grids by eye. That last step is a join, and you are the join engine. It works for three rows. It doesn't work for "spend per state across all 500 customers", it can't be handed to an AI agent, and the usual ways out (a pipeline, Redshift federated query, a lake) all move data or bolt the two systems together. The rest of the chapter makes that go away.

9.2Who knows that customers are in Aurora and purchases are in Redshift?

Not the ontology, and not either database: the mappings, stored in Stardog as part of each virtual graph's definition. Here are the three layers you've built:

Figure 9.1: Meaning, location, rows. Only the middle layer knows that :purchasedBy comes from Redshift and :name from Aurora.

Each virtual graph is a name + a data source + a mapping, saved on the Stardog server (GET /admin/virtual_graphs lists them). The data source says which database (a JDBC connection, Chapter 7). The mapping's TO block says which ontology terms this graph can produce, and its FROM SQL says from which table. So when a query asks for a property, Stardog looks through the mappings for a TO block that produces it. You have already seen this work: your query for :name and :hasRewards was answered from the customer and rewards blocks of aurora_c360_safe, because those are the only mappings that produce those properties. Once you create redshift_c360 in this chapter, a query for :purchasedBy will be answered from its purchase block in the same way.

9.3The Redshift mapping and the line that matters

Redshift needs its own virtual graph. The full redshift_c360.sms is in the Try box; here's the purchase block, the one that makes federation possible:

MAPPING <urn:c360:redshift:purchase>
FROM SQL {
  SELECT id, pid, "date" AS pdate, "time" AS ptime, quantity, price, cid, card, reward_account,
         CAST("date" AS varchar) || ' ' || "time" || ' - $' || CAST(price AS varchar) AS label
  FROM c360.purchase
}
TO {
  ?iri a :Order ; rdfs:label ?label ;
    :purchasePrice ?p ; :quantity ?q ; :purchaseTime ?ptime ; :purchaseDate ?dt ;
    :purchasedBy ?cust_iri ; :itemPurchased ?product ; :cardUsed ?cc ; :rewardsAccountUsed ?rewardsIri .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:purchase:us:pos{id}") AS ?iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?cust_iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:product:{pid}") AS ?product)
  BIND(TEMPLATE("urn:stardog:demos:c360:cards:{card}") AS ?cc)
  BIND(TEMPLATE("urn:stardog:demos:c360:rewards:{reward_account}") AS ?rewardsIri)
  BIND(xsd:decimal(?price) AS ?p)
  BIND(xsd:integer(?quantity) AS ?q)
  BIND(xsd:date(?pdate) AS ?dt)
}

Chapter 8's tools read all of it: each row is an :Order; four foreign keys become four arrows; the label is concatenated in SQL; three casts pin the datatypes. The quoted "date" and "time" and the xsd:decimal each have a story (Section 9.9). But one line is the whole chapter:

BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?cust_iri)   -- Redshift, purchase block
BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?iri)        -- Aurora, customer block (Chapter 8)

Character for character, the same template. Redshift's purchase.cid = 225 and Aurora's customer.cid = 225 both become the IRI <urn:stardog:demos:c360:customer:225>, and in RDF a node is its IRI (Chapter 3). So Aurora's facts about customer 225 and Redshift's orders "purchased by" customer 225 are facts about one and the same node. In Redshift, purchase.cid is a plain integer column: no foreign key can point from Redshift to a table in Aurora. The cross-engine "join" is the shared IRI template instead.

The same file, redshift_c360.sms, has three more blocks after the purchase block: one each for products, categories and vendors. Here they are in full:

MAPPING <urn:c360:redshift:product>
FROM SQL { SELECT id, name, brand, price, dept, description, '$' || CAST(price AS varchar) AS price_label FROM c360.product }
TO {
  ?iri a :Product ; :inCategory ?cat_iri ; :description ?description ; :msrp ?up ;
    :vendor ?vendor_iri ; :name ?name ; rdfs:label ?name .
  ?up a :UnitPrice ; rdfs:label ?price_label ; :hasCurrency "USD" ; :hasPrice ?pf .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:product:{id}") AS ?iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:category:{dept}") AS ?cat_iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:vendor:{brand}") AS ?vendor_iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:unitprice:{id}") AS ?up)
  BIND(xsd:float(?price) AS ?pf)
}
;
MAPPING <urn:c360:redshift:category>
FROM SQL { SELECT id, dept_name FROM c360.category }
TO { ?iri a :Category ; :name ?dept_name ; rdfs:label ?dept_name . }
WHERE { BIND(TEMPLATE("urn:stardog:demos:c360:category:{id}") AS ?iri) }
;
MAPPING <urn:c360:redshift:vendor>
FROM SQL { SELECT id, vendor_name, industry, homepage FROM c360.vendor }
TO {
  ?iri a :Vendor ; :industry ?ind ; :homepage ?homepage ; :name ?vendor_name ; rdfs:label ?vendor_name .
  ?ind a :Industry ; :name ?industry ; rdfs:label ?industry .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:vendor:{vendor_name}") AS ?iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:industry:{industry}") AS ?ind)
}

They use the same ideas you already know (FROM SQL, TO, TEMPLATE, casts). Three details are new:

Vendors are matched by name, not by an ID. The product table has a brand column holding the vendor's name; the vendor table has a vendor_name column. Both mappings build the vendor's IRI from that name, so they meet on the same node. Product 1, a Ring Light with brand Neewer, gives vendor:Neewer; the vendor row named Neewer gives vendor:Neewer too.
Spaces in names are encoded. An IRI can't contain a space, so Stardog replaces it with %20 (this is called percent-encoding, the same thing a browser does in URLs). The brand Charmin Ultra Soft becomes vendor:Charmin%20Ultra%20Soft in both mappings, so they still match.
A product's list price stays xsd:float. The Ring Light's list price, 59.99, is only ever shown, never added up, so a float's tiny rounding error can't accumulate. Purchase prices, which are added up, use xsd:decimal (you'll see the cents drift otherwise, in the gotchas below).

9.4Where exactly does SPARQL get converted to SQL?

Inside the Stardog server's query planner, before a byte goes to AWS. Studio just sends text over HTTPS, and Aurora and Redshift only run the SQL that comes out at the end. The planner, in order:

Parse. The SPARQL text becomes an algebra tree: triple patterns, joins, a group, an order, a limit.
Route each triple pattern to a mapping. Which mapping blocks have a matching pattern in their TO? ?o :purchasedBy ?c matches only the purchase block of redshift_c360; ?c :name ?name matches the customer block of aurora_c360_safe (layer 2 of Figure 9.1 at work).
Group by source. Patterns answerable by the same virtual graph are bundled, because one SQL statement can answer the bundle. Bundled patterns that share a variable built from matching templates (customer :address ↔ address subject) become a SQL JOIN.
Rewrite each bundle into the source's SQL dialect. Stardog's virtual-graph engine is built on Apache Calcite, an open-source SQL parser and query-algebra framework. Stardog uses it to parse your mapping's FROM SQL, combine it with the query's needs, and print SQL in Postgres or Redshift flavor. You have seen Calcite at work twice already. First, the error described in Section 7.5, Object 'customer' not found within 'c360': Calcite works from its own list of tables, and by default that list covers only the database's public schema. Our tables live in a schema named c360 (inside a database that is also named c360, which makes the message confusing), so until the data source setting sql.schemas=c360 adds that schema to the list, Calcite cannot find customer even though the table exists. Second, the query plan in Section 8.9: lines such as LogicalJoin and JdbcTableScan(table=[[c360, c360, customer]]) (database c360, schema c360, table customer) are Calcite's description of the SQL it built.

Plan the rest. Everything that can run inside one database is packed into one SQL query for that database. Whatever needs results from both databases (the join across them, and the SUM, GROUP BY and ORDER BY over the joined rows) cannot be sent to either one, so Stardog does it itself, on top of the rows that come back. A query plan is drawn as an upside-down tree, and the SQL queries are its leaves, the bottom nodes where data comes in:

                 ORDER BY spend DESC, LIMIT 10     <- Stardog
                        |
                 GROUP BY customer, SUM(price x qty)  <- Stardog
                        |
                 JOIN on the customer IRI          <- Stardog (needs both sides)
                 +------+------+
      SQL to Aurora         SQL to Redshift       <- the leaves: one query per database
   (customer + address)       (purchase)

Step 4's "do as much as possible in the source" is pushdown (introduced in Section 7.3): moving computation to the data is almost always cheaper than moving data to the computation.

For one source, this is what Hibernate does with HQL: the mapping plus a dialect print SQL, with the WHERE in the SQL instead of the JVM. Stardog's mapping is the entity annotation, Calcite is the Dialect, and pushdown is the where clause landing in the database.

SPARQL, and the SQL Stardog sends

GRAPH <virtual://aurora_c360_safe> {
  ?c :name ?name ; :address ?a .
  ?a :state "Texas" .
}

# Stardog (Calcite, Postgres dialect),
# roughly:
# SELECT c.full_name, a.state
# FROM c360.customer c
# JOIN c360.address a ON c.location = a.id
# WHERE a.state = 'Texas'

Java 21 + Hibernate

List<Object[]> rows = em.createQuery("""
        select c.fullName, a.state
        from Customer c join c.address a
        where a.state = :st""", Object[].class)
    .setParameter("st", "Texas")
    .getResultList();

// Hibernate (PostgreSQLDialect), roughly:
// select c1_0.full_name, a1_0.state
// from c360.customer c1_0
// join c360.address a1_0 on a1_0.id = c1_0.location
// where a1_0.state = ?

Where the analogy breaks: a Hibernate persistence unit has one DataSource and one dialect, so HQL cannot join an Aurora Customer to a Redshift Order. Stardog routes each pattern to its own source and dialect, then does the cross-source join itself (the Java version is in Section 9.6).

How Stardog knows which graphs to look in: GRAPH vs virtual transparency

Our queries name their sources with GRAPH <virtual://…> { … }. That's explicit routing: the patterns inside may only match that virtual graph. By default, a query with no GRAPH block reads the local default graph and does not see virtual graphs at all; you'd get zero rows. With the database option virtual.transparency=true, Stardog adds every virtual graph the user may access to the query's dataset, so you can drop the GRAPH blocks and each pattern is matched against whichever graph can answer it. One subtlety from the docs: a GRAPH ?g { … } block still forces all its patterns to match within one graph, so patterns split across Aurora and Redshift return nothing inside a single GRAPH ?g.

We keep explicit GRAPH blocks in this book: they make the routing visible and Chapter 12's security demo easy to reason about, and Chapter 14's agent uses them too.

9.5Sometimes Aurora, sometimes Redshift, sometimes both

Routing is per pattern, so the same endpoint sends SQL to one database, the other, or both, depending on which terms your query uses. Three examples from the blog's sample questions (SQL shown is illustrative; Section 9.8 shows how to see the real text):

Aurora only: customers by state

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?state (COUNT(?c) AS ?n)
WHERE { GRAPH <virtual://aurora_c360_safe> { ?c a :Customer ; :address ?a . ?a :state ?state } }
GROUP BY ?state ORDER BY DESC(?n) LIMIT 5

Every pattern routes to Aurora, so the whole thing, join, group, sort and limit, is pushed down as one statement, roughly SELECT a.state, COUNT(*) FROM customer c JOIN address a ON c.location = a.id GROUP BY a.state ORDER BY 2 DESC LIMIT 5. Redshift stays asleep. Five rows cross the wire (Texas 53, California 51, Florida 48, …; you ran this in Chapter 8).

Redshift only: top products by units sold

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?product (SUM(?qty) AS ?units)
WHERE { GRAPH <virtual://redshift_c360> { ?o :itemPurchased ?p ; :quantity ?qty . ?p :name ?product } }
GROUP BY ?product ORDER BY DESC(?units) LIMIT 10

All patterns route to Redshift. The purchase-to-product hop matches templates (product:{pid} ↔ product:{id}), so it becomes a SQL join, roughly SELECT p.name, SUM(u.quantity) FROM purchase u JOIN product p ON u.pid = p.id GROUP BY p.name ORDER BY 2 DESC LIMIT 10. Aurora stays paused.

Both: top spenders with their state

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?name ?state (SUM(?price * ?qty) AS ?spend)
WHERE {
  GRAPH <virtual://redshift_c360> {
    ?o a :Order ; :purchasedBy ?c ; :purchasePrice ?price ; :quantity ?qty .
  }
  GRAPH <virtual://aurora_c360_safe> {
    ?c :name ?name ; :address ?a .
    ?a :state ?state .
  }
}
GROUP BY ?c ?name ?state
ORDER BY DESC(?spend)
LIMIT 10

Now ?c appears in both blocks. Chapter 4 said "a shared variable is a join"; here the shared variable crosses two databases. Stardog sends one SQL statement to each side:

-- Illustrative: to Redshift. The mapping's SQL becomes a subquery; only needed columns are selected.
SELECT "cid", "price", "quantity"
FROM (SELECT id, pid, "date" AS pdate, "time" AS ptime, quantity, price, cid, card, reward_account,
             CAST("date" AS varchar) || ' ' || "time" || ' - $' || CAST(price AS varchar) AS label
      FROM c360.purchase) AS "t"
WHERE "cid" IS NOT NULL AND "price" IS NOT NULL AND "quantity" IS NOT NULL

-- Illustrative: to Aurora. The join condition location = id was derived from matching templates.
SELECT "t0"."cid", "t0"."full_name", "t1"."state"
FROM (SELECT cid, first_name, last_name, email, phone, location, full_name FROM c360.customer) AS "t0"
JOIN (SELECT id, city, state, zip, ... FROM c360.address) AS "t1" ON "t0"."location" = "t1"."id"

And here's the catch. The SUM is grouped by name and state, from Aurora, over prices from Redshift. Neither database has both, so neither can compute it. That brings us to the heart of the chapter.

9.6Where the data lands, and how the answer is rolled up

No database ever joins Aurora to Redshift. Stardog does, in memory, for the duration of one query. Press Play to follow it.

Press ▶ Play to watch one query travel from Studio to both databases and back, step by step.

Figure 9.2: One federated query, start to finish. Aurora and Redshift never talk to each other; the join is step 10, inside Stardog.

Step 10 answers "without a JOIN across two engines, how does the data come together?"

Where the rows land. Each SQL result streams back over JDBC into Stardog's query engine, where it lives in memory as bindings (variable → value rows, like a result set) for the life of this one query. Nothing is written into kit-c360. (Very large joins may spill to temporary disk; still scratch space, not stored data.)
How keys become the same key. Each Redshift row's cid goes through the purchase mapping's template and binds ?c to customer:225; each Aurora row's goes through the customer mapping's template to the same IRI. Because the templates are identical, Stardog can compare the underlying values rather than long strings, but logically it joins on the IRI.
How the join runs. Typically a hash join: build a hash table from the smaller side (500 customers, keyed by ?c), then stream the 2,500 purchase rows through it. The planner can also choose a bind join: run one side first, then send its keys into the other side's SQL (think WHERE cid IN (225, …)), which wins when one side is tiny, such as "top spenders in Wisconsin". The docs show plans combining virtual graphs under a ServiceJoin operator; the strategy is the optimizer's call, and your plan (Section 9.8) tells you.
Only then, the rollup. Stardog computes SUM(?price * ?qty) per customer (on exact xsd:decimal values), sorts, and keeps ten. An aggregate grouped by Aurora's columns over Redshift's numbers can't be pushed to either source.

Per database, Stardog sends one SQL query; across databases, Stardog joins the results itself.

Example: the Wisconsin query in Chapter 8 became one 3-table SQL JOIN on Aurora; top spenders needs a query to Aurora, one to Redshift, then a join in Stardog.

Figure 9.3: Where the rows physically are, stage by stage, for the top-spenders query.

The trade-offs, honestly

Concern	What it means here
Data volume on the wire	Anything that can't be pushed down gets pulled: here one row per order. 2,500 rows is nothing; 2.5 billion would be a problem.
Pushdown	Single-source work runs in the database; cross-source joins and anything above them run in Stardog. Query shape decides (next section).
Latency	At least the slower source's time plus transfer. Our Aurora adds 15 to 20 seconds after a pause.
Cost	You pay each source's compute per query (Redshift bills a 60-second minimum per wake-up) and Stardog's memory for the join.
Freshness	The upside for all of the above: every run reads live data. No pipeline, no copy.

9.7Helping pushdown: put single-source work inside one source

Why the SUM cannot go to Redshift in the 9.5 query

First, where the rows land. When Redshift and Aurora answer their SQL, the rows travel over the network into Stardog's memory. They live there only while the query runs, for the join and the math; when the answer is sent back, the memory is freed. Nothing is copied into Stardog permanently.

Now the SUM. The 9.5 query ends with SUM(?price * ?qty) and GROUP BY ?c ?name ?state. Look at what each database actually holds for Carolyn:

Redshift `purchase`			Aurora `customer` + `address`
cid	price	qty	cid	name	state
225	10,479.00	21	225	Carolyn Inworth	Wisconsin
225	199.70	10
…	…	…

The query says "group by name and state", but Redshift has no name or state columns; it has never heard of "Carolyn Inworth". The grouping key only exists after each Redshift row has been joined with its Aurora row. So the steps must be: fetch all 2,500 purchase rows, join them to Aurora inside Stardog, and only then sum. The SUM is stuck in Stardog.

The fix below groups by ?c (the cid) instead, inside a subquery that touches only Redshift. Redshift does have cid, so it can do the sum itself (SELECT cid, SUM(price * quantity) FROM purchase GROUP BY cid, giving 225 → 502,529.42), and Stardog attaches the name and state afterwards, one Aurora row per total.

You can restructure the query so most of the work is single-source: sum per customer inside Redshift first, then join the totals to Aurora:

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?name ?state ?spend
WHERE {
  {
    SELECT ?c (SUM(?price * ?qty) AS ?spend)
    WHERE {
      GRAPH <virtual://redshift_c360> {
        ?o :purchasedBy ?c ; :purchasePrice ?price ; :quantity ?qty .
      }
    }
    GROUP BY ?c
  }
  GRAPH <virtual://aurora_c360_safe> {
    ?c :name ?name ; :address ?a .
    ?a :state ?state .
  }
}
ORDER BY DESC(?spend)
LIMIT 10

The subquery touches only Redshift, so Stardog can push the whole GROUP BY into it (roughly SELECT cid, SUM(price * quantity) FROM purchase GROUP BY cid), and Redshift returns about one row per customer instead of one per order. Same answer, far fewer rows. Whether your version pushes this aggregate is what the plan will tell you.

The proof: how many rows reach Stardog

All three versions below return the same top spenders (Carolyn Inworth, Wisconsin, $502,529.42 first). What differs is how many rows travel from the databases into Stardog. Redshift always reads all 2,500 purchase rows internally, because it has to add them up; the question is how much of that leaves Redshift.

Version	Redshift sends Stardog	Aurora sends Stardog	Into Stardog
A. The plain federated query (Figure 9.2)	2,500 purchase rows (cid, price, quantity)	500 customers	~3,000
B. This section: `GROUP BY` inside the subquery	one total per customer (up to 500)	500 customers	~1,000
C. Also `ORDER BY` and `LIMIT 10` inside the subquery	10 totals	10 customers	20

These are the real SQL statements Redshift receives for B and C (from Stardog's query plan):

-- B: the sum is pushed down
SELECT cid, SUM(price * quantity) AS spend
FROM c360.purchase
GROUP BY cid

-- C: the sum, the sort and the limit are all pushed down
SELECT cid, SUM(price * quantity) AS spend
FROM c360.purchase
GROUP BY cid
ORDER BY SUM(price * quantity) DESC
LIMIT 10

Version C only needs one change to the query: move ORDER BY DESC(?spend) LIMIT 10 inside the subquery, after its GROUP BY ?c. The plan then also changes the join: instead of matching against all 500 customers, Stardog takes the 10 customer IDs that came back from Redshift and looks up only those 10 in Aurora (a bind join, shown as ServiceJoin in the plan).

Why Stardog never does C on its own. The top 10 is decided after the join. If a top spender had purchases in Redshift but no customer row in Aurora, the join would drop them and you would get 9 rows instead of 10, with the true 11th spender missing. No foreign key can run between two databases, so Stardog cannot know every Redshift cid exists in Aurora, and it stays safe. Use C when you can vouch for the data, with a safety net:

Check the keys once: count Redshift cids with no matching Aurora customer. If it is 0 and stays 0, C is safe.
Over-fetch: LIMIT 20 inside, LIMIT 10 outside, so a few unmatched customers still leave you 10 complete rows.
Keep gaps visible: wrap the Aurora part in OPTIONAL { … }, so an unmatched top spender appears with an empty name instead of silently disappearing.

Rule of thumb: B by default, because it is always correct. C when the data is large and you have checked the keys. Chapter 16 returns to this kind of silent gap.

9.8Seeing the real plan

Figure 9.2 shows how Stardog works in general; your endpoint can show what it actually does. In Studio's Workspace, the tree icon next to Run shows a query's plan without running it; its dropdown has Run Profiler, which runs the query and shows the plan with timings. Note that Studio adds its own safety limit of 1,000 rows when you don't write one, so you may see FETCH NEXT 1000 ROWS ONLY in the SQL. The CLI equivalent is stardog query explain. In the plan text, look for:

VirtualGraphSql<virtual://…>: one per SQL statement sent to a source. Query= is the exact SQL; Vars= shows columns becoming variables, e.g. ?c <- TEMPLATE(urn:stardog:demos:c360:customer:{cid}). Both leaves binding ?c with the same template is the IRI trick in the machine's own words.
A join above them (for example HashJoin(?c) or a ServiceJoin): the variable is the join key.
[#N]: the optimizer's row estimate for each node.

9.9Why the template is the join key, and the gotchas to watch for

Everything above depends on two independent mapping files agreeing on a string pattern. That contract breaks quietly:

Different templates: no error, no rows. Say Redshift's mapping calls the same person a buyer instead of a customer, building urn:c360:buyer:{cid} where Aurora builds urn:stardog:demos:c360:customer:{cid}. To us it is obviously the same person; to Stardog they are two different names, so they never map onto each other. The most common federation bug.
```
Aurora   customer mapping:  urn:stardog:demos:c360:customer:225
Redshift purchase mapping:  urn:c360:buyer:225          <- different string, different node
```
The top-spenders query joins on ?c. Redshift offers …buyer:225, …buyer:88…; Aurora offers …customer:225, …customer:88…. No value appears on both sides, so the query returns 0 rows: valid, no error, just empty. The SQL version of the same bug is one table storing 225 and the other 'C-225': JOIN ON a.cid = b.cid runs fine and returns nothing. If a federated query comes back empty, ask each graph for its ?c values separately and compare.
Different key formats. 225 vs 0225 vs C-225 give different IRIs. Normalize in the mapping's SQL.
Different meanings. A generic urn:node:{id} for both customers and products would merge unrelated things (the docs' optimization page has an example with actors and movies). That's why every c360 template names its type.

Quote reserved words. purchase has columns called date and time, both SQL reserved words. Redshift tolerates them bare, but Stardog parses the mapping's SQL itself (Calcite, Section 9.4), and the parse fails. Fix: "date" AS pdate, "time" AS ptime.

Pin types with casts. The kit's original mappings were written for CSV, where everything is a string, hence xsd:integer(?quantity) and xsd:date(…). Over SQL the columns are typed already, but explicit casts make each datatype match the ontology rather than whatever the driver reports; mismatched types break comparisons, YEAR() and value joins.

Money is xsd:decimal, never xsd:float. The kit's original mapping casts purchase prices with xsd:float, which puts Carolyn's total at $502,529.44, 2 cents off SQL (the float drift of Chapter 3). With xsd:decimal, matching Redshift's decimal(12,2), it matches SQL to the cent: $502,529.42.

9.10Try it: create `redshift_c360` and run the federated query (real build step 4)

Step 1. Create the Redshift virtual graph. In Studio's Virtual Graphs section, add one as in Section 8.8: name redshift_c360, database kit-c360, data source redshift_c360 (the existing one; the graph and the data source sharing a name is fine), no extra options. Save this complete, verified mapping as redshift_c360.sms, select it under File, untick Auto-generate mapping, and click Create:

Use the whole file, prefix lines included. The mapping must start with the three prefix lines shown at the top. Without prefix : <tag:stardog:api:ecomm:>, Stardog fills : in with the database default (http://api.stardog.com/), so the graph produces http://api.stardog.com/purchasedBy instead of tag:stardog:api:ecomm:purchasedBy. Every query then finds nothing on the Redshift side, and Explain shows Empty. If that happens, delete the virtual graph and create it again from the complete file.

# Virtual graph: redshift_c360 — analytics side. purchasedBy mints the SAME customer IRI as Aurora:
# that template agreement is the cross-warehouse join.
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix : <tag:stardog:api:ecomm:>

MAPPING <urn:c360:redshift:purchase>
FROM SQL {
  SELECT id, pid, "date" AS pdate, "time" AS ptime, quantity, price, cid, card, reward_account,
         CAST("date" AS varchar) || ' ' || "time" || ' - $' || CAST(price AS varchar) AS label
  FROM c360.purchase
}
TO {
  ?iri a :Order ; rdfs:label ?label ;
    :purchasePrice ?p ; :quantity ?q ; :purchaseTime ?ptime ; :purchaseDate ?dt ;
    :purchasedBy ?cust_iri ; :itemPurchased ?product ; :cardUsed ?cc ; :rewardsAccountUsed ?rewardsIri .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:purchase:us:pos{id}") AS ?iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?cust_iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:product:{pid}") AS ?product)
  BIND(TEMPLATE("urn:stardog:demos:c360:cards:{card}") AS ?cc)
  BIND(TEMPLATE("urn:stardog:demos:c360:rewards:{reward_account}") AS ?rewardsIri)
  BIND(xsd:decimal(?price) AS ?p)
  BIND(xsd:integer(?quantity) AS ?q)
  BIND(xsd:date(?pdate) AS ?dt)
}
;
MAPPING <urn:c360:redshift:product>
FROM SQL { SELECT id, name, brand, price, dept, description, '$' || CAST(price AS varchar) AS price_label FROM c360.product }
TO {
  ?iri a :Product ; :inCategory ?cat_iri ; :description ?description ; :msrp ?up ;
    :vendor ?vendor_iri ; :name ?name ; rdfs:label ?name .
  ?up a :UnitPrice ; rdfs:label ?price_label ; :hasCurrency "USD" ; :hasPrice ?pf .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:product:{id}") AS ?iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:category:{dept}") AS ?cat_iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:vendor:{brand}") AS ?vendor_iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:unitprice:{id}") AS ?up)
  BIND(xsd:float(?price) AS ?pf)
}
;
MAPPING <urn:c360:redshift:category>
FROM SQL { SELECT id, dept_name FROM c360.category }
TO { ?iri a :Category ; :name ?dept_name ; rdfs:label ?dept_name . }
WHERE { BIND(TEMPLATE("urn:stardog:demos:c360:category:{id}") AS ?iri) }
;
MAPPING <urn:c360:redshift:vendor>
FROM SQL { SELECT id, vendor_name, industry, homepage FROM c360.vendor }
TO {
  ?iri a :Vendor ; :industry ?ind ; :homepage ?homepage ; :name ?vendor_name ; rdfs:label ?vendor_name .
  ?ind a :Industry ; :name ?industry ; rdfs:label ?industry .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:vendor:{vendor_name}") AS ?iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:industry:{industry}") AS ?ind)
}

API equivalent (reference only): the POST /admin/virtual_graphs call from Chapter 8 with name:"redshift_c360", data_source:"redshift_c360", db:"kit-c360", options:{"mappings.syntax":"SMS2"}.

Step 2. Redshift alone.

PREFIX : <tag:stardog:api:ecomm:>
SELECT (COUNT(?o) AS ?orders)
WHERE { GRAPH <virtual://redshift_c360> { ?o a :Order } }

Expected: 2500. Then run the "Redshift only" products query from 9.5; expect ten product names with unit totals.

Step 3. The federated query. Run the "Both" query from 9.5 (copy it from there). Expected top three of ten rows:

name	state	spend
Carolyn Inworth	Wisconsin	502529.42
Marlane Shadwick	Florida	491217.53
Isaac Popping	Pennsylvania	440274.78

Slow the first time? That's Aurora waking. Run it again.

Step 4. The pushdown-friendly version from 9.7. Same top three?

Step 5. Read the plans. Show the plan for the Step 3 query and for the 9.7 query. Find the two VirtualGraphSql nodes, read the SQL in each Query=, find the join on ?c, and check whether the 9.7 version's Redshift SQL contains GROUP BY. Also show the plan for the Aurora-only query from 9.5: a single VirtualGraphSql, no Redshift. If you can't find the plan view, tell Claude what your Workspace toolbar shows.

Step 6. Cross-check the slow way. In DBeaver, run the two SQL queries from 9.1 with LIMIT 10 on the Redshift one, paste the ten cids into the Aurora query's IN list, and match the grids by eye. You should get the same ten names, states and amounts as Step 3 (the first Redshift row is 225, 502529.42): two queries and a human join, versus one query that never mentions cid.

Cleanup: none. redshift_c360 is part of the build. kit-c360 now has three virtual graphs.

Check yourself

Who knows that customer names are in Aurora and purchases are in Redshift?

The virtual graph definitions stored in Stardog: a data source (which database) plus a mapping (which ontology terms come from which tables). Not the ontology, which only defines meaning, and not the databases.

Where exactly is SPARQL turned into SQL?

In the Stardog server's query planner: parse, route each triple pattern to the mapping blocks whose TO can produce it, bundle per source, and rewrite each bundle into that source's SQL dialect with Apache Calcite, pushing down as much as it can.

A query has no GRAPH blocks and returns zero rows, though the virtual graphs exist. Why?

By default, virtual graphs aren't in a query's dataset. Either name them with GRAPH <virtual://…> or enable the database option virtual.transparency, which adds all accessible virtual graphs.

Which databases receive SQL for (a) customers by state, (b) top products by units, (c) top spenders with state?

(a) Aurora only, fully pushed down. (b) Redshift only, fully pushed down. (c) Both: one statement each; the join, the SUM, the sort and the limit happen in Stardog.

Where do the rows land, and why can't the SUM be pushed to Redshift in the 9.5 query?

They stream into Stardog's memory as bindings for the life of the query; nothing is stored. The SUM is grouped by name and state, which only Aurora has, so it must follow the cross-source join. The 9.7 rewrite groups by ?c inside a Redshift-only subquery, which can be pushed.

What would you see if Redshift's mapping used urn:c360:buyer:{cid}?

No error and zero federated rows: no IRI would appear in both graphs, so the join on ?c matches nothing. Templates are the contract.

Chapter recap

No SQL statement spans Aurora and Redshift; by hand, it takes two queries and a human join.
Three layers: the ontology (meaning), the virtual graph definitions in Stardog (where), the databases (rows).
SPARQL becomes SQL in Stardog's planner: parse, route patterns to mappings, bundle per source, rewrite with Calcite, push down. Use explicit GRAPH blocks or turn on virtual.transparency.
Single-source queries are pushed down whole. Cross-source queries send one SQL per source, join the rows in Stardog's memory on the shared customer IRI (hash or bind join), then aggregate, sort and limit.
The IRI template is the join key: identical templates, identical key formats, type-specific names.
Gotchas: quote "date"/"time"; cast with xsd:integer/xsd:date; money as xsd:decimal (float drifted 2 cents).
Result: Carolyn Inworth, Wisconsin, $502,529.42; Marlane Shadwick, Florida, $491,217.53; Isaac Popping, Pennsylvania, $440,274.78, matching SQL exactly.

Paste it to Claude with what you saw (the counts or results), so we can confirm it matches before the next build step relies on it.