The Semantic Layer, From ScratchChapter 1

Chapter 1

One question, five systems

Why a simple business question is hard to answer, and what a semantic layer does about it.

On Monday morning someone in marketing sends you a message: "Who are our top spenders in Wisconsin? We want to invite them to a loyalty event." It's a one-line question, and every engineer knows it's not a one-line answer. The customers live in one system, the orders in another, the product catalog in a third, the loyalty accounts in a fourth, and the definition of "top spender" in somebody's spreadsheet. This chapter is about that gap, and about the idea this book builds: a layer that closes it by describing what the data means rather than copying it somewhere new.

1.1The question and the five systems

In a typical company, answering "top spenders in Wisconsin" needs:

The customer database, usually an operational Postgres or MySQL behind the app or CRM: names, emails, and which address each customer has.
The data warehouse, where orders land for analytics: every purchase, with a customer id, a date, a quantity and a price.
The product catalog, if the question grows to "top spenders on sports gear in Wisconsin".
The loyalty or rewards system, because marketing will immediately ask "and which of them already have a rewards account?"
The definitions: whatever tool holds the agreed meaning of "top spender". Total spend or order count? Lifetime or this year? Usually a BI model, a dbt file, or a spreadsheet, in several slightly different versions.

5. Finally, what does "top spender" even mean? Somebody's spreadsheet knows. In our lab, a Stardog rule will (Chapter 10).

Figure 1.1: One question, five systems. In our lab, two AWS databases stand in for the first four, and the fifth, the definitions, moves into Stardog.

Our lab is a small, honest version of this: two real AWS databases play the first four roles, and the fifth, the definitions, moves into the semantic layer as rules (Chapter 10).

1.2The usual answers, and what they cost

You know the standard ways to answer a cross-system question. They all work, and each has a cost that grows with the number of questions and systems.

Approach	How it works	What it costs
ETL into one warehouse	Copy customers and addresses from Aurora into Redshift every night, then JOIN.	A pipeline to build, schedule, monitor and fix. Data is as stale as the last run. Personal data lives in two places, with two sets of grants.
Federated SQL	A query engine (Trino, Athena federated queries, Redshift's federated query to Postgres) reads both sources in one SQL statement.	Solves the plumbing, not the meaning. The author still needs every table and join key, and "top spender" is still defined inside each query.
A BI semantic model	LookML, dbt metrics or a Power BI model defines "revenue" once for that tool.	Good within one tool, but tied to it (and usually one warehouse), and awkward for other programs or an AI agent to use.

Notice the pattern. Getting rows from two places is solvable in several ways. The hard, recurring problem is meaning: which table holds customers, how orders connect to customers, what "top spender" means, who may see a Social Security number. Every approach above re-solves that in each pipeline, query or dashboard.

1.3What a semantic layer is, and what it isn't

A knowledge graph is a way of representing information as a network of things (customers, orders, products) connected by named relationships (an order was purchased by a customer), together with a machine-readable description of what those things and relationships are. A semantic layer is a job, not a technology: a layer between people or programs and the physical data, where they ask questions in business terms and the layer works out which data answers them. A knowledge graph is one way to do that job (there are others; see "Other ways to build a semantic layer" below). This book builds the semantic layer using a knowledge graph.

Here the semantic layer is Stardog, with five parts, each of which you will build.

Part	What it does	Nearest SQL idea
Ontology	The business vocabulary: kinds of things (Customer, Order) and how they relate	A logical data model, but one the database can read and reason with
Mappings	How each concept corresponds to tables and columns in Aurora and Redshift	The SELECT behind a view
Rules	Derived facts: "a customer with a large order is a big spender"	A view or a computed column, shared by every query
Security	Who may see which parts of the data	GRANTs and row/column security, in one place for all sources
Query engine	Takes one query, splits it by source, runs SQL, joins the results	A federated query planner

Just as important is what it isn't:

It is not a new database you ETL into. Stardog can store data (Chapter 7), but in our design it stores none of the customer or order rows. They stay in Aurora and Redshift and are read at query time through virtual graphs, views defined over another database.
It is not magic. You write the ontology and the mappings, and the answers are exactly as good as those.
It is not a warehouse replacement. Heavy aggregation over billions of rows still belongs in Redshift. The layer pushes as much work as it can down to the databases (Chapter 9) and stitches the results together.

The closest familiar Java stack is a JDBC DataSource plus Hibernate, if Hibernate could span many databases and every application shared the domain model:

Stardog semantic layer

ontology      :Customer, :Order,
              :purchasedBy          # domain model
mappings      c360.customer -> :Customer
              c360.purchase -> :Order
data sources  aurora_c360, redshift_c360
query         SPARQL over :Customer, :Order

Java

@Entity class Customer { … }   // domain model
@Entity class Order { … }
@Table(name = "customer")       // ORM mapping
DataSource aurora;              // JDBC
session.createQuery(
  "from Order o where o.customer = :c")

Where the analogy breaks: a Hibernate session talks to one database and loads objects into memory. Stardog answers one query from several databases, returns rows rather than managed objects, and its domain model is itself data (triples) you can query and reason over, shared by every app, report and agent rather than compiled into each service.

Other ways to build a semantic layer

A knowledge graph isn't the only way to build a semantic layer, and the relationship runs one way only: some semantic layers aren't knowledge graphs, and some knowledge graphs aren't semantic layers (Google's Knowledge Graph powers search results; others detect fraud or drive recommendations). There are six families in use today, and big companies often combine two of them.

Approach	Examples	How it works	Good at	Weaker at
Metrics layer ("headless BI")	dbt Semantic Layer (MetricFlow), Cube, AtScale, Looker's LookML, Snowflake semantic views, Databricks metric views	YAML or code defines entities, dimensions and measures ("revenue = sum(price × qty)") over a warehouse, then generates SQL	One consistent definition of every metric across dashboards; the most common choice today	Usually one warehouse; few relationships; no reasoning
BI-tool semantic model	Power BI semantic models (DAX), Tableau data models, SAP BusinessObjects "universes" (the original semantic layer, from the 1990s)	A business model built inside the reporting tool	Fast for analysts	Locked into that tool; other apps and agents can't reuse it
Data virtualization with business views	Denodo, Dremio, Trino/Starburst with views	One SQL query across engines, with views renamed into business terms	Cross-engine, no ETL	The "meaning" is only view and column names; no shared identity or rules
Knowledge graph (this book)	Stardog, Ontotext GraphDB, Amazon Neptune (RDF); Neo4j (property graph)	An ontology plus mappings or loaded data, queried with SPARQL (or Cypher)	Relationships, one identity for the same customer across sources, rules and reasoning, federation (Stardog)	Steeper learning curve; slower than a warehouse at heavy aggregation
Operational ontology platform	Palantir Foundry Ontology	Business "object types", links and actions on top of datasets	Covers both reading and doing (workflows)	Proprietary; the data typically lives inside the platform
API or service layer	GraphQL (Apollo federation), domain microservices, even a JPA domain model	Business entities exposed as types or endpoints over many sources	Great for applications	Hand-coded; ad-hoc analytical questions are hard

Which one when. One warehouse and mostly KPIs and dashboards: a metrics layer, the cheapest and most mainstream option. Many systems, lots of relationships, the same customer scattered across them, and business rules like "big spender" that should be defined once: a knowledge graph. That is the customer-360 situation in this book, and the reason the AWS blog chose Stardog. For AI agents, every family is now adding an "AI-ready" semantic model (Snowflake Cortex Analyst's semantic model files, Databricks Genie, dbt's MCP server). A knowledge graph's edge there is that the ontology and its relationships give the model more meaning to work with.

"Semantic layer" is an interface; a knowledge graph is one implementation of it.

The job

interface SemanticLayer {
  // ask in business terms,
  // get answers from the physical data
  Result ask(String businessQuestion);
}

Implementations

class KnowledgeGraphLayer implements SemanticLayer {}  // this book
class MetricsLayer        implements SemanticLayer {}  // dbt, Cube
class VirtualViewsLayer   implements SemanticLayer {}  // Denodo
class BiToolModel         implements SemanticLayer {}  // Power BI

Where the analogy breaks: real products don't share one clean interface. Each implementation covers a different part of the job (metrics, federation, relationships, reasoning), which is why companies often run two side by side, e.g. a metrics layer for dashboards and a knowledge graph for cross-system meaning and agents.

A semantic layer is a job, not a technology: an ETL-free business view over many databases, where the ontology supplies the meaning and the mappings supply the connections.

Example: This book builds it with a knowledge graph on Stardog; metrics layers, BI models and data virtualization are other ways.

1.4The three layers from the blog

The AWS blog describes an agentic system in three layers, separating three jobs that often get blurred together.

Figure 1.2: The blog's three layers, with the blog's choices and ours. The data stays below, untouched.

The model layer is the language model: good at language, bad at knowing your data. Given only table names, it will cheerfully invent columns and guess join keys. The meaning layer fixes that: it gives the model a small, precise vocabulary ("an Order is purchased by a Customer; a Customer has address an Address") and a single query interface to everything. The model writes a query in business terms; the semantic layer turns it into correct SQL on the right systems. The word for this is grounding: the answer is tied to real data through a well-defined interface rather than to the model's guesses. The runtime layer runs the agent: holding the conversation, calling tools, keeping credentials safe.

Most of this book is about the middle layer. The model and runtime are interchangeable, which is why swapping Bedrock for OpenAI costs us nothing in the chapters that matter.

1.5Our use case: customer 360, split across two engines

"Customer 360" means a complete view of each customer: who they are, where they live, what they bought, how they pay, whether they're in the rewards program. Our data is a Stardog training dataset (the c360 knowledge kit), split the way it might be in a real company: operational data in Aurora, transactional history in the warehouse.

Engine	Database.schema	Table	Rows	Key columns
Aurora PostgreSQL	`c360.c360`	`customer`	500	`cid` (PK), `location` → address.id
		`address`	500	`id` (a UUID string), city, state, zip
		`credit_card`	500	`id`, `cid`, card_num, card_type
		`rewards_account`	500	`id`, `cid`, account_id, create_date
Redshift Serverless	`dev.c360`	`purchase`	2,500	`id`, `pid`, `cid`, date, time, quantity, price
		`product`	580	`id`, name, brand, price, dept
		`category`	31	`id`, dept_name, parent
		`vendor`	418	`id`, vendor_name, industry

Figure 1.3: The split. The only thing connecting the two engines is a value: the customer id cid appears in both. Chapter 9 turns that shared value into a shared name.

Aurora's customer.cid is a primary key; Redshift's purchase.cid is a plain integer column with the same values. There can be no foreign key across engines. Carolyn Inworth is cid 225 in Aurora, and her purchases carry cid 225 in Redshift. Hold on to that: Chapters 3 and 9 turn that shared value into a shared name, the whole trick behind joining the two databases without ETL.

Competency questions

Before designing any vocabulary, the blog recommends writing down competency questions: a short list of real questions the ontology must be "competent" to answer. They keep the model small and honest; anything nobody asks about stays out. Ours come from the blog, plus the one we started with:

#	Question	Needs
1	List ten customers and their state.	Aurora only
2	Top ten products by units sold.	Redshift only
3	Top ten customer spenders, with their state.	Both (federated)
4	How many big spenders per state?	Both, plus a rule (reasoning)
5	Who are our top spenders in Wisconsin?	Both, plus a filter

These five come back in Chapter 14 as the test for the finished agent. You could answer the first two in SQL today, the third only with extra plumbing. The fourth needs a shared definition of "big spender" that doesn't exist yet.

1.6The questions this book answers

Competency questions are what the system must answer. Your own questions as a skeptical database engineer (how the agent knows the ontology, who knows which data lives where, where SPARQL becomes SQL, how two engines' results get joined) are listed with their chapters in Section P.2. The short version: the mappings know where data lives, and Stardog's query engine splits the query, runs SQL on each source and joins the results itself, storing nothing (Chapter 9).

The chapters follow the build order: foundations (ch2–4: triples, names, SPARQL), the model (ch5–6: the ontology), the data (ch7–9: virtual graphs, mappings, federation), meaning and control (ch10–12: rules, SHACL, security) and the agent (ch13–14).

1.7The terms from this chapter

Term	What it is	Why it exists	Tiny example
Knowledge graph	Data stored as things connected by named relationships, plus a description of what they mean.	Meaning travels with the data, not in people's heads.	Order 0e7e… purchased by customer 225.
Semantic layer	A knowledge graph used as a layer between consumers and physical databases.	Ask in business terms once, instead of re-encoding meaning in every query.	Ask for "Customers in Wisconsin", not `customer JOIN address`.
Virtual graph	A view-like graph whose facts are produced from SQL at query time.	Query data where it lives, with no ETL.	`aurora_c360_safe` over Aurora's tables.
Grounding	Tying an LLM's answer to real data through a well-defined interface.	Models invent columns and joins when they guess.	The agent must answer from `run_sparql` results.
Competency question	A real question the finished system must answer.	Keeps the model small and gives you an acceptance test.	"How many big spenders per state?"

No building yet: a two-minute, read-only look at the baseline.

1. In DBeaver, on the Aurora connection, run the question "how many customers per state?" the SQL way:

SELECT a.state, COUNT(*) AS customers
FROM c360.customer c
JOIN c360.address  a ON a.id = c.location
GROUP BY a.state
ORDER BY customers DESC
LIMIT 5;

Expect: Texas 53, California 51, Florida 48, District of Columbia 28, New York 25. (If Aurora was paused, the first run takes 15–20 seconds.) Remember these numbers: in Chapter 8 you will get the same answer from Stardog.

2. In DBeaver, on the Redshift connection, count the orders:

SELECT COUNT(*) FROM c360.purchase;

Expect: 2500. Now try to write, in one SQL statement, the sum of purchases per customer with the customer's state. You can't: the state is in the other database.

3. In Studio, open the Workspace, choose the database kit-c360 from the database menu, and run this. It counts every triple in the database, in every graph:

SELECT (COUNT(*) AS ?n)
WHERE { { ?s ?p ?o } UNION { GRAPH ?g { ?s ?p ?o } } }

Expect: one row, n = 0. The database is empty, by design. The syntax comes in Chapter 4.

Check yourself

What's the difference between the "plumbing" problem and the "meaning" problem?

Plumbing is getting rows from several systems into one place (ETL, federated SQL). Meaning is knowing which tables represent a customer or an order, how they connect, and what terms like "top spender" mean. Most tools solve plumbing; the semantic layer is mainly about meaning, and brings its own plumbing (virtual graphs).

Does Stardog store copies of the customers and orders in our design?

No. The rows stay in Aurora and Redshift. Stardog holds the ontology, mappings, rules and security, and reads the data at query time through virtual graphs.

Which of the blog's three layers does this book mostly teach, and which two do we swap?

The meaning layer (Stardog). We swap the model (Claude on Bedrock becomes an OpenAI or OpenRouter model) and the runtime (AgentCore becomes Python on your laptop).

What connects Carolyn's Aurora row to her Redshift purchases?

Only a value: cid = 225 appears in both, with no foreign key and no shared engine. Chapters 3 and 9 turn it into a shared name, which makes the federated join work.

What is a competency question, and why write them first?

A real question the finished system must answer. Writing three to five first keeps the ontology small: you model what the questions need and nothing else. They also become the agent's acceptance test.

Chapter recap

A simple business question usually touches several systems plus a set of informal definitions.
ETL, federated SQL and BI semantic models solve the plumbing but re-solve the meaning in every pipeline, query or dashboard.
A semantic layer is a job: a business-terms layer over physical data. We build it with a knowledge graph (ontology, mappings, rules, security and a query engine); metrics layers, BI models and data virtualization are other ways. It is not a database you copy into.
The blog's three layers are model, meaning and runtime; we keep Stardog for meaning and swap the other two for OpenAI/OpenRouter and plain Python.
Our data: customers, addresses, cards and rewards in Aurora; purchases, products, categories and vendors in Redshift; tied together only by cid.
Five competency questions define what the system must answer.