The Semantic Layer, From ScratchChapter 7

Chapter 7

Where the data lives: materialized vs virtual

Two ways to give your ontology real data, why we leave ours in Aurora and Redshift, and the plumbing that lets Stardog reach them.

Try a quick experiment in your head. Open Studio, pick kit-c360, and ask for Carolyn Inworth: SELECT * { ?c :name "Carolyn Inworth" }. You'd get nothing. After two chapters of modeling, your database knows exactly what a :Customer is, but not a single actual customer. You built the table definitions, and the rows are sitting in Aurora and Redshift. This chapter is about how Stardog gets at them, and the choice you make here (copy them in, or reach out to them) shapes cost, freshness, security and speed for the rest of the book.

7.1The schema is loaded; the rows are not

Recall Chapter 5's split. The T-Box is the vocabulary (161 triples in <urn:stardog:training-c360:1.0:model>). The A-Box is facts about individual things: "customer 225 is named Carolyn Inworth". In Postgres the DDL and the rows live in one engine; in Stardog the A-Box can live somewhere else entirely. Ours does:

Engine	Database / schema	Tables (rows)
Aurora PostgreSQL 16 (Serverless v2)	`c360` / `c360`	customer (500), address (500), credit_card (500), rewards_account (500)
Redshift Serverless	`dev` / `c360`	purchase (2,500), product (580), category (31), vendor (418)

7.2Option one: materialize (copy the triples in)

A materialized graph is instance data that has been converted to triples and stored inside Stardog's own storage. Your INSERT DATA triples about Carolyn in Chapters 2 to 4 were a tiny version. At scale you'd bulk-load: export tables to CSV or Turtle, or run a mapping once with the admin command virtual import, which writes the resulting triples into a database. The original c360 knowledge kit works this way: its loader turns US_Customers.csv, Purchases.csv and friends into triples stored in kit-c360, and queries never leave Stardog.

7.3Option two: virtualize (leave the data where it is)

A virtual graph is a named graph whose triples are never stored in Stardog. Instead, Stardog holds three things: a connection to the source database, a mapping that says how rows turn into triples (Chapter 8), and a name such as virtual://aurora_c360_safe. When a query touches that graph, Stardog translates that part into SQL, sends it to the source, and turns the returned rows into triples on the fly. Only query results cross the wire.

The technique that makes this workable is pushdown: instead of dragging whole tables into Stardog and filtering there, Stardog pushes as much work as it can (filters, joins within one source, aggregates, limits) into the SQL it sends, because the database has the indexes (the Stardog docs call it moving the compute to the data). For example: ask for customers in Texas and Aurora receives … WHERE state = 'Texas', returning 53 rows instead of 500. Chapter 9 takes pushdown apart on a real federated query.

Materialized versus virtual is CREATE MATERIALIZED VIEW versus CREATE VIEW.

Stardog

# Materialized: triples copied into Stardog
# (INSERT DATA, bulk load, virtual import).
# Fast, and as fresh as the last load.

# Virtual: only a mapping and a name.
GRAPH <virtual://aurora_c360_safe> {
  ?c :address ?a .
  ?a :state "Texas" .
}
# becomes SQL: ... WHERE state = 'Texas'

SQL (PostgreSQL)

-- Materialized: rows copied once,
-- as fresh as the last refresh
CREATE MATERIALIZED VIEW customer_state_copy AS
SELECT c.cid, c.first_name, a.state
FROM c360.customer c
JOIN c360.address a ON a.id = c.location;
REFRESH MATERIALIZED VIEW customer_state_copy;

-- Virtual: stores only the query;
-- every read runs it fresh
CREATE VIEW customer_state AS
SELECT c.cid, c.first_name, a.state
FROM c360.customer c
JOIN c360.address a ON a.id = c.location;

SELECT * FROM customer_state
WHERE state = 'Texas';   -- 53 rows

Where the analogy breaks: a SQL view lives inside one database and speaks in its table and column names. A virtual graph speaks in the ontology's words (:state, :Customer), and one SPARQL query can read virtual graphs on two different databases at once, with Stardog doing the join (Chapter 9). No SQL view can reach across Aurora and Redshift like that.

Materialized is a copy; virtual is a view that runs fresh SQL every time.

Example: like CREATE MATERIALIZED VIEW vs CREATE VIEW.

Figure 7.1: Two ways to bind an A-Box. Left: copy the triples into Stardog. Right: keep the rows in the source and translate each query into SQL.

7.4Choosing between them

Neither option is "the right one". The trade-offs are the ones between a federated query engine and a warehouse.

Concern	Materialized	Virtual
Freshness	As old as the last load. Needs a refresh pipeline.	Live. A row inserted a second ago shows up in the next query.
Query speed	Fastest. Everything is in Stardog's indexes.	Bounded by the sources plus network. Our Aurora cold start adds 15 to 20 seconds after a pause.
Load on the sources	Only during the load.	Every query hits your production databases.
Storage and cost	You pay to store a second copy (and the Free tier has limits).	No copy. You pay the sources' compute per query (Redshift's 60-second minimum, for example).
Governance	Sensitive data now lives in a second system needing its own controls, retention and audit.	Data stays in the system of record. Stardog controls who can query which graph (Chapter 12); the source keeps its own grants.
Availability	Queries work even if a source is down.	If Redshift is paused or unreachable, queries that touch it fail or wait.
Changing the mapping	Re-run the load to see the change.	Edit the mapping; the next query uses it.

For this book we go virtual, for three reasons that match the AWS blog's pitch: we want live answers ("no ETL" is the point of the demo); SSNs and card numbers should stay in Aurora, not be copied into another store; and a semantic layer over existing systems is the architecture the book teaches, which materializing would quietly turn back into a warehouse project.

Real deployments often mix the two: virtualize big, sensitive or fast-changing tables; materialize small, stable reference data. (Stardog also has virtual-graph caching as a middle ground; check the docs for your tier.)

7.5Data sources: the connection half of a virtual graph

Stardog splits a virtual graph into two separately managed objects. The first is the data source: a named, reusable connection to an external database, plus a cached copy of its metadata (schemas, tables, columns, types, keys, row-count estimates). The other half, the mapping, is Chapter 8. The split lets one connection serve many graphs: in Chapter 8 you'll build two virtual graphs over the one data source aurora_c360, with the password stored in one place.

The connection itself is JDBC (Java Database Connectivity): the standard Java API for SQL databases, with a vendor-supplied driver per database. DBeaver uses it too, which is why the settings will look familiar.

A Stardog data source is a javax.sql.DataSource: in Spring terms, the one DataSource bean that several repositories (here, virtual graphs) are wired to.

Stardog data source options

# data source: aurora_c360
jdbc.url=jdbc:postgresql://<your-aurora-cluster>…
         :5432/c360?sslmode=require
jdbc.driver=org.postgresql.Driver
jdbc.username=(masked)
jdbc.password=(masked)
sql.schemas=c360

Java 21 + HikariCP

var cfg = new HikariConfig();
cfg.setJdbcUrl(
    "jdbc:postgresql://…:5432/c360?sslmode=require");
cfg.setDriverClassName("org.postgresql.Driver");
cfg.setUsername(System.getenv("PGUSER"));
cfg.setPassword(System.getenv("PGPASSWORD"));
cfg.setSchema("c360");

DataSource aurora = new HikariDataSource(cfg);
// one DataSource, many users of it

Where the analogy breaks: setSchema only sets the default schema of each connection. sql.schemas also decides which tables Stardog reads into its cached metadata, which it validates mappings against, much like Hibernate's hbm2ddl.auto=validate at boot, and which likewise misses later DDL changes (the schema-drift trap below).

Your two data sources were created during the AWS setup; you won't change them. Their options, as Stardog reports them:

Option	`aurora_c360` (PostgreSQL)	`redshift_c360` (Redshift)
`jdbc.url`	`jdbc:postgresql://<your-aurora-cluster>.us-west-2.rds.amazonaws.com:5432/c360?sslmode=require`	`jdbc:redshift://<your-workgroup>.us-west-2.redshift-serverless.amazonaws.com:5439/dev`
`jdbc.driver`	`org.postgresql.Driver`	`com.amazon.redshift.jdbc42.Driver`
`jdbc.username` / `jdbc.password`	(masked)	(masked)
`sql.schemas`	`c360`	`c360`
`initialSize`	`0`	`0`
`minIdle` / `maxIdle`	`0` / `0`	`0` / `0`
`testOnBorrow`	`true`	`true`
`timeBetweenEvictionRunsMillis`	`0`	`0`

URL, driver, username, password: the same four things DBeaver asks for. Stardog Cloud ships both drivers. sslmode=require forces TLS over the public internet.
sql.schemas=c360: by default a data source only sees the user's default schema (public); this adds c360, where our tables live. See the Gotcha.
The pool settings: Section 7.6, because they're about money.

7.6The connection pool, and why `minIdle=0` saves money

A connection pool keeps database connections open between queries so the next query doesn't pay for a new TCP connection, a TLS handshake and a login. Stardog's JDBC data sources use a standard Java pool (the Tomcat JDBC pool), and its defaults suit an always-on database: open 3 connections up front (initialSize=3), keep idle ones, and validate them periodically in the background.

Ours sleeps on purpose. Aurora Serverless v2 is configured with a minimum of 0 ACUs and auto-pause after 5 minutes of inactivity; while paused, compute costs $0. But Aurora only pauses when there are no open user connections, so a pool holding three idle connections means it bills $0.12 per ACU-hour around the clock. Nothing breaks; the bill just never drops to zero.

Every Java service on a serverless database has the same leak: HikariCP's default keeps minimumIdle equal to maximumPoolSize (10), so ten idle connections stay open forever.

Stardog pool options

initialSize=0
minIdle=0
maxIdle=0
testOnBorrow=true
timeBetweenEvictionRunsMillis=0

Java 21 + HikariCP

// tuned so Aurora can auto-pause
cfg.setMinimumIdle(0);          // ~ minIdle=0: no warm spares
cfg.setIdleTimeout(10_000);     // retire idle ones after 10 s
cfg.setMaximumPoolSize(5);
cfg.setInitializationFailTimeout(-1);
                                // no connection at startup
                                // (~ initialSize=0)
// Hikari validates a connection on borrow
// if it sat idle (~ testOnBorrow=true)

Where the analogy breaks: Stardog uses the Tomcat JDBC pool, whose property names are exactly the ones on the left; HikariCP has no maxIdle=0 "close on return" mode, so with a short idleTimeout an idle connection lingers up to about 40 seconds (the housekeeper runs every 30). That is still well inside Aurora's 5-minute pause window.

So both data sources use the settings the Stardog docs list for disabling pooling:

Setting	Value	Effect
`initialSize`	0	Don't open connections when the data source loads.
`minIdle`	0	Never keep a minimum number of idle connections around.
`maxIdle`	0	When a query finishes, close its connection instead of parking it.
`testOnBorrow`	true	Check a connection is alive before using it (useful when the database may have just woken up).
`timeBetweenEvictionRunsMillis`	0	Turn off the background thread that would otherwise touch idle connections.

The price: every query opens a fresh connection (a few hundred milliseconds), and the first query after a pause waits 15 to 20 seconds while Aurora resumes. A fine trade for a sandbox; in production you'd keep a pool and a nonzero minimum capacity.

Redshift Serverless bills differently: $0.375 per RPU-hour with a 60-second minimum per wake-up, $0 when idle. With pooling off it runs only when a query needs it, and our cap of 1 RPU-hour per day (about 15 minutes at 4 RPUs) is plenty for the book.

7.7The network path: Stardog Cloud's egress IP and your security group

Stardog Cloud runs in Stardog's AWS account, not yours. Its SQL reaches Aurora over the public internet, made safe enough for a sandbox by two things.

First, Stardog Cloud sends all virtual-graph traffic from a single, published egress IP per region: for us-west-2, where our endpoint lives, 44.238.183.206 (DNS name produswest2-vg-egress.stardog.cloud).

Second, the Aurora cluster and the Redshift workgroup sit behind a security group (AWS's stateful firewall) that allows inbound traffic on 5432 (Postgres) and 5439 (Redshift) from exactly two /32 addresses: Stardog's egress IP and your laptop's IP (so DBeaver works). Everything else is dropped. Both databases need a public IP (two of the $0.005/hour IPv4 charges in the cost sheet) so Stardog Cloud can reach them.

Figure 7.2: Who can reach the databases. Only two addresses get through the security group: Stardog Cloud's published egress IP and your laptop.

Know the limit: every Stardog Cloud tenant in us-west-2 shares that egress IP, so the allowlist means "Stardog Cloud", not "your endpoint"; the password and TLS do the real work. Production setups use private networking, such as the blog's self-managed Stardog on EKS inside your VPC.

7.8Try it: look, don't touch

This part is read-only: look at the two data sources in Studio and the rows in DBeaver, so you know exactly what Chapter 8 turns into triples.

Step 1. Inspect the data sources in Studio. Open Studio and connect to your endpoint as stardog_admin. In the left rail, open Data (the Studio section for data sources). You should see two entries: aurora_c360 and redshift_c360. Click each one and compare its settings with the table in 7.5: the JDBC URL, the driver, sql.schemas = c360, and the pool settings.

Do not edit or save anything. The password field is shown blank or masked, so saving could overwrite the real password. Just close the panel. If the Data section is laid out differently in your Studio version, tell Claude what you see.

Step 2 (optional). Confirm the same thing over the HTTP API. If you like terminals, this read-only call lists the data sources. Set STARDOG_PASSWORD to your stardog_admin password first; don't paste it into the command.

curl -s -u "stardog_admin:$STARDOG_PASSWORD" \
  https://<your-endpoint>.stardog.cloud:5820/admin/data_sources/list

Expected: two entries, data-source://aurora_c360 with type POSTGRESQL and data-source://redshift_c360 with type REDSHIFT, both "available":true. Swap the path for /admin/virtual_graphs and you'll get {"virtual_graphs":[]}: none exist yet. You create them in Chapters 8 and 9.

Step 3. See Carolyn as SQL. In DBeaver, open your Aurora connection and run:

-- Aurora (Postgres). The first query after a pause can take 15-20 s.
SELECT c.cid, c.full_name, c.email, c.phone, c.location,
       a.street_number || ' ' || a.street_name || ' ' || a.street_suffix AS street,
       a.city, a.state, a.zip
FROM c360.customer c
JOIN c360.address  a ON a.id = c.location
WHERE c.cid = 225;

Expected: one row. cid 225, Carolyn Inworth, cinworth68@g.co, 704-619-5453, location b80ea2ce-2361-408a-b443-721594cbbb35, 78945 Thompson Plaza, Milwaukee, Wisconsin, 53285.

Step 4. See her orders. Switch to your Redshift connection and run:

-- Redshift. Note the double quotes: date and time are reserved words.
SELECT id, pid, "date", "time", quantity, price, price * quantity AS line_total
FROM c360.purchase
WHERE cid = 225
ORDER BY "date";

Expected: a list of Carolyn's orders. Notice what is not here: her name. Redshift knows her only as 225; Aurora knows her name but not her orders. That is the problem Chapter 9 solves.

Cleanup: none. Nothing was written.

Check yourself

In one sentence each, what is a materialized graph and what is a virtual graph?

A materialized graph is triples copied into Stardog's own storage (like loading a warehouse). A virtual graph's triples are produced on demand: Stardog translates the query into SQL, runs it on the source, and turns the rows into triples.

Name two reasons this book virtualizes the c360 data instead of loading it.

Any two of: answers must be live (no ETL, which is the point of the demo); sensitive columns such as SSNs should stay in the system of record rather than be copied; the book teaches a semantic layer over existing systems; changing a mapping takes effect immediately without a reload.

What is the difference between a data source and a virtual graph?

A data source is the connection (JDBC URL, driver, credentials, options such as sql.schemas and pool settings) plus cached metadata. A virtual graph is a name plus a mapping that uses a data source. One shared data source can back several virtual graphs, as aurora_c360 will back both aurora_c360_safe and aurora_c360_pii.

Why would Stardog's default connection pool keep Aurora from ever auto-pausing, and which settings prevent that?

The default pool opens connections up front and keeps idle ones open, and Aurora only pauses with no connections, so it would bill around the clock. initialSize=0, minIdle=0, maxIdle=0, timeBetweenEvictionRunsMillis=0 (plus testOnBorrow=true) close connections after each query and stop background activity.

A mapping fails with "Object 'customer' not found within 'c360'", yet DBeaver shows c360.customer. What do you check?

The data source's sql.schemas option. Without c360 listed, Stardog's metadata only covers the default schema (public). If the option is set and a recently added table is still missing, refresh the data source's metadata (schema drift).

Why does the security group allow 44.238.183.206, and what does that allowlist not protect against?

That's Stardog Cloud's published virtual-graph egress IP for us-west-2, so every SQL query from Stardog arrives from it. Every Stardog Cloud tenant in the region shares it, so the allowlist doesn't distinguish your endpoint from anyone else's; credentials and TLS do the real work. Private networking (for example Stardog on EKS in your VPC) removes the public exposure.

Chapter recap

The T-Box is loaded; the A-Box (the actual customers and orders) lives in Aurora and Redshift.
Materialized = copy triples into Stardog (ETL): fast, but stale between loads and a second copy to govern.
Virtual = leave rows in place; Stardog translates each query into SQL and pushes down what it can: live, no copy, but bounded by the sources.
A data source is a shared, named JDBC connection plus cached metadata. Ours: aurora_c360 and redshift_c360, both with sql.schemas=c360.
Pooling is disabled (initialSize/minIdle/maxIdle=0, testOnBorrow=true, timeBetweenEvictionRunsMillis=0) so Aurora can pause and Redshift isn't woken needlessly. The cost: a fresh connection per query and a 15 to 20 second cold start.
Stardog Cloud reaches your databases from one egress IP per region (44.238.183.206 in us-west-2); the security group allows only that and your laptop.