Ontology, Knowledge Graphs & the Semantic Layer on Snowflake — The Implementation在 Snowflake 上实现本体论、知识图谱与语义层——实施篇

Snowflake WikiSnowflake 维基

Follow关注

15 min read15 分钟阅读

·

Sep 14, 20262026年9月14日

34

Listen

Share

More

Building the five-layer intelligence stack with working code!用可运行的代码构建五层智能栈！

Here Ontology, Knowledge Graphs & the Semantic Layer on Snowflake — The Concepts, we covered what ontology, knowledge graphs, and the semantic layer are, how they differ, and how they compose into a five-layer architecture on Snowflake. Now we build them.在《在 Snowflake 上实现本体论、知识图谱与语义层——概念篇》中，我们介绍了什么是本体论、知识图谱和语义层，它们有何区别，以及它们如何构成 Snowflake 上的五层架构。现在，我们要开始构建它们了。

Everything below runs on Snowflake. No external graph databases, no third-party semantic tools, no separate infrastructure. We use a movies and entertainment domain as the running example, but the patterns apply to any domain — sports, healthcare, supply chain, financial services, or education.以下所有内容均在 Snowflake 上运行。无需外部图数据库，无需第三方语义工具，也无需单独的基础设施。我们以电影和娱乐领域作为运行示例，但这些模式适用于任何领域——体育、医疗保健、供应链、金融服务或教育。

Setup: Database and Schema Structure设置：数据库与模式结构

We organize the stack into schemas that mirror the five layers:我们将该栈组织为映射五层架构的模式（Schema）：

-- Database
CREATE DATABASE IF NOT EXISTS MOVIES_ONTOLOGY;

-- Schemas by purpose
CREATE SCHEMA IF NOT EXISTS MOVIES_ONTOLOGY.STORAGE;     -- Layer 1: Nodes and Edges
CREATE SCHEMA IF NOT EXISTS MOVIES_ONTOLOGY.ONTOLOGY;    -- Layer 2: Ontology metadata
CREATE SCHEMA IF NOT EXISTS MOVIES_ONTOLOGY.VIEWS;       -- Layer 3: Auto-generated views
CREATE SCHEMA IF NOT EXISTS MOVIES_ONTOLOGY.SEMANTIC;    -- Layer 4: Semantic models
CREATE SCHEMA IF NOT EXISTS MOVIES_ONTOLOGY.AGENT;       -- Layer 5: Cortex Agent and UDFs

Layer 1: Physical Storage — Two Tables第 1 层：物理存储——两张表

The foundation is radically simple. Everything in your entertainment domain is either a node or an edge.基础非常简单。娱乐领域中的一切要么是节点，要么是边。

Node Table节点表

Every entity instance — regardless of type — lives here:每个实体实例——无论类型如何——都存储于此：

CREATE OR REPLACE TABLE MOVIES_ONTOLOGY.STORAGE.KG_NODES (
    NODE_ID        VARCHAR(100)    NOT NULL,
    NODE_TYPE      VARCHAR(50)     NOT NULL,   -- Maps to ontology class: 'Actor', 'Movie', 'Genre'
    PROPERTIES     VARIANT         NOT NULL,   -- All attributes as structured JSON
    SOURCE_SYSTEM  VARCHAR(100),               -- Where this entity originated
    VALID_FROM     TIMESTAMP_NTZ   DEFAULT CURRENT_TIMESTAMP(),
    VALID_TO       TIMESTAMP_NTZ               -- NULL = currently active
);

Edge Table边表

Every relationship between entities lives here:实体间的每一种关系都存储于此：

CREATE OR REPLACE TABLE MOVIES_ONTOLOGY.STORAGE.KG_EDGES (
    EDGE_ID         VARCHAR(200)    NOT NULL,
    SOURCE_NODE_ID  VARCHAR(100)    NOT NULL,   -- The "from" entity
    TARGET_NODE_ID  VARCHAR(100)    NOT NULL,   -- The "to" entity
    EDGE_TYPE       VARCHAR(50)     NOT NULL,   -- Maps to ontology relation: 'PERFORMED_IN', 'DIRECTED'
    PROPERTIES      VARIANT,                    -- Edge-specific attributes (role, billing order, etc.)
    VALID_FROM      TIMESTAMP_NTZ   DEFAULT CURRENT_TIMESTAMP(),
    VALID_TO        TIMESTAMP_NTZ               -- NULL = currently active
);

Two tables. That is the entire physical layer. An Actor is a node. A Movie is a node. “Performed in” is an edge. An Award is a node. “Won” is an edge. The schema never changes as the domain grows.两张表，这就是整个物理层。演员是一个节点。电影是一个节点。“出演”是一条边。奖项是一个节点。“获得”是一条边。随着领域的发展，模式无需任何更改。

Seed Data: Nodes种子数据：节点

Nodes use PARSE_JSON to store flexible properties. Here's the pattern for each entity type:节点使用 PARSE_JSON 来存储灵活的属性。以下是每种实体类型的模式：

-- Actors
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_NODES (NODE_ID, NODE_TYPE, PROPERTIES, SOURCE_SYSTEM)
SELECT 'ACTOR_1001', 'Actor', PARSE_JSON('{
    "name": "Tom Hanks",
    "birth_year": 1956,
    "nationality": "American",
    "gender": "Male"
}'), 'IMDB'
UNION ALL SELECT 'ACTOR_1002', 'Actor', PARSE_JSON('{
    "name": "Leonardo DiCaprio",
    "birth_year": 1974,
    "nationality": "American",
    "gender": "Male"
}'), 'IMDB';
-- ... more actors


-- Movies
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_NODES (NODE_ID, NODE_TYPE, PROPERTIES, SOURCE_SYSTEM)
SELECT 'MOVIE_2001', 'Movie', PARSE_JSON('{
    "title": "Forrest Gump",
    "release_year": 1994,
    "budget": 55000000,
    "domestic_gross": 330252182,
    "international_gross": 347693217,
    "runtime_minutes": 142,
    "rating": 8.8,
    "language": "English"
}'), 'BOX_OFFICE_MOJO'
UNION ALL SELECT 'MOVIE_2002', 'Movie', PARSE_JSON('{
    "title": "The Godfather",
    "release_year": 1972,
    "budget": 6000000,
    "domestic_gross": 134966411,
    "international_gross": 110000000,
    "runtime_minutes": 175,
    "rating": 9.2,
    "language": "English"
}'), 'BOX_OFFICE_MOJO';
-- ... more movies

The same pattern applies for Directors, Producers, Genres, Studios, and Awards.同样的模式也适用于导演、制片人、类型、工作室和奖项。

Seed Data: Edges种子数据：边

Edges capture relationships with their own properties. The VARIANT column adapts to each relationship type without schema changes.边通过其自身的属性捕获关系。VARIANT 列可适应每种关系类型，无需更改模式。

-- Actor performances with role details (Actor -> Movie)
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_EDGES (EDGE_ID, SOURCE_NODE_ID, TARGET_NODE_ID, EDGE_TYPE, PROPERTIES)
VALUES
-- Tom Hanks
('E_ACTOR1001_MOVIE2001', 'ACTOR_1001', 'MOVIE_2001', 'PERFORMED_IN', PARSE_JSON('{"role": "Forrest Gump", "billing_order": 1, "screen_time_minutes": 130}')),
('E_ACTOR1001_MOVIE2011', 'ACTOR_1001', 'MOVIE_2011', 'PERFORMED_IN', PARSE_JSON('{"role": "Chuck Noland", "billing_order": 1, "screen_time_minutes": 120}')),
-- Leonardo DiCaprio
('E_ACTOR1002_MOVIE2003', 'ACTOR_1002', 'MOVIE_2003', 'PERFORMED_IN', PARSE_JSON('{"role": "Dom Cobb", "billing_order": 1, "screen_time_minutes": 125}'));
-- ... more performance edges

-- Director credits (Director -> Movie)
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_EDGES (EDGE_ID, SOURCE_NODE_ID, TARGET_NODE_ID, EDGE_TYPE, PROPERTIES)
VALUES
('E_DIR301_MOVIE2001', 'DIRECTOR_301', 'MOVIE_2001', 'DIRECTED', PARSE_JSON('{"credit": "Director"}')),
('E_DIR301_MOVIE2011', 'DIRECTOR_301', 'MOVIE_2011', 'DIRECTED', PARSE_JSON('{"credit": "Director"}'));
-- ... more directed edges

-- Movie Studios (Studio -> Movie)
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_EDGES (EDGE_ID, SOURCE_NODE_ID, TARGET_NODE_ID, EDGE_TYPE, PROPERTIES)
VALUES
('E_STUDIO401_MOVIE2001', 'STUDIO_401', 'MOVIE_2001', 'PRODUCED', PARSE_JSON('{"distribution": "Worldwide"}')),
('E_STUDIO401_MOVIE2002', 'STUDIO_401', 'MOVIE_2002', 'PRODUCED', PARSE_JSON('{"distribution": "Worldwide"}'));
-- ... more studio edges

-- Movie Genres (Movie -> Genre)
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_EDGES (EDGE_ID, SOURCE_NODE_ID, TARGET_NODE_ID, EDGE_TYPE, PROPERTIES)
VALUES
('E_MOVIE2001_GENRE101', 'MOVIE_2001', 'GENRE_101', 'BELONGS_TO', PARSE_JSON('{"primary": true}')),
('E_MOVIE2001_GENRE107', 'MOVIE_2001', 'GENRE_107', 'BELONGS_TO', PARSE_JSON('{"primary": false}'));
-- ... more genre edges

-- Award wins or Nominations (Movie -> Award)
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_EDGES (EDGE_ID, SOURCE_NODE_ID, TARGET_NODE_ID, EDGE_TYPE, PROPERTIES)
VALUES
('E_MOVIE2001_AWARD601', 'MOVIE_2001', 'AWARD_601', 'WON', PARSE_JSON('{"recipient": "Film"}')),
('E_ACTOR1001_AWARD604', 'ACTOR_1001', 'AWARD_604', 'WON', PARSE_JSON('{"recipient": "Tom Hanks", "for_movie": "MOVIE_2001"}')),
('E_MOVIE2002_AWARD602', 'MOVIE_2002', 'AWARD_602', 'WON', PARSE_JSON('{"recipient": "Film"}')),
('E_MOVIE2003_AWARD603', 'MOVIE_2003', 'AWARD_603', 'NOMINATED_FOR', PARSE_JSON('{"recipient": "Film", "result": "Did not win"}'));
-- ... more award edges

-- Movie Producers (Producer -> Movie)
INSERT INTO MOVIES_ONTOLOGY.STORAGE.KG_EDGES (EDGE_ID, SOURCE_NODE_ID, TARGET_NODE_ID, EDGE_TYPE, PROPERTIES)
VALUES
('E_PROD501_MOVIE2006', 'PRODUCER_501', 'MOVIE_2006', 'PRODUCED', PARSE_JSON('{"credit": "Executive Producer"}')),
('E_PROD502_MOVIE2003', 'PRODUCER_502', 'MOVIE_2003', 'PRODUCED', PARSE_JSON('{"credit": "Producer"}'));
-- ... more award edges

Layer 2: Ontology Metadata — The Configuration Layer第 2 层：本体元数据——配置层

The ontology does not store data. It stores meaning. Three metadata tables declare what classes exist, how they can relate, and what rules constrain them.本体论不存储数据，它存储含义。三张元数据表声明了存在哪些类、它们如何关联以及约束它们的规则。

Ontology Classes本体类

CREATE OR REPLACE TABLE MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_CLASS (
    CLASS_NAME      VARCHAR(50)     NOT NULL,
    PARENT_CLASS    VARCHAR(50),               -- NULL = root class
    DESCRIPTION     VARCHAR(500),
    PROPERTIES_SCHEMA VARIANT,                 -- Expected properties for this class
    CREATED_AT      TIMESTAMP_NTZ   DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_CLASS VALUES
('Person',   NULL,     'Root class for all individuals in the entertainment industry',
    PARSE_JSON('{"name": "STRING (required)", "birth_year": "INTEGER", "nationality": "STRING", "gender": "STRING"}'),
    CURRENT_TIMESTAMP()),
('Actor',    'Person', 'A person who performs roles in movies',
    PARSE_JSON('{"name": "STRING (required)", "birth_year": "INTEGER", "nationality": "STRING", "gender": "STRING"}'),
    CURRENT_TIMESTAMP()),
('Director', 'Person', 'A person who directs movies',
    PARSE_JSON('{"name": "STRING (required)", "birth_year": "INTEGER", "nationality": "STRING", "known_for": "STRING"}'),
    CURRENT_TIMESTAMP()),
('Producer', 'Person', 'A person who produces or finances movies',
    PARSE_JSON('{"name": "STRING (required)", "birth_year": "INTEGER", "nationality": "STRING", "company": "STRING"}'),
    CURRENT_TIMESTAMP()),
('Movie',    NULL,     'A motion picture -- the central entity of the domain',
    PARSE_JSON('{"title": "STRING (required)", "release_year": "INTEGER (required)", "budget": "FLOAT", "domestic_gross": "FLOAT", "international_gross": "FLOAT", "runtime_minutes": "INTEGER", "rating": "FLOAT", "language": "STRING"}'),
    CURRENT_TIMESTAMP()),
('Genre',    NULL,     'A classification category for movies',
    PARSE_JSON('{"name": "STRING (required)", "description": "STRING"}'),
    CURRENT_TIMESTAMP()),
('Award',    NULL,     'A recognition given at a ceremony for achievement in film',
    PARSE_JSON('{"name": "STRING (required)", "ceremony": "STRING (required)", "ceremony_year": "INTEGER (required)", "category_type": "STRING"}'),
    CURRENT_TIMESTAMP()),
('Studio',   NULL,     'A production company that finances and distributes movies',
    PARSE_JSON('{"name": "STRING (required)", "founded_year": "INTEGER", "country": "STRING", "parent_company": "STRING"}'),
    CURRENT_TIMESTAMP());

This gives us a class hierarchy: Actor, Director, and Producer all inherit from Person. This means a query for “all Persons connected to Movie X” will automatically include actors, directors, and producers — without you having to write UNION ALL logic.这为我们提供了类层次结构：演员、导演和制片人都继承自“人”。这意味着查询“与电影 X 相关的所有人”时，会自动包含演员、导演和制片人，而无需编写 UNION ALL 逻辑。

Ontology Relationships本体关系

CREATE OR REPLACE TABLE MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_RELATION (
    RELATION_NAME       VARCHAR(50)     NOT NULL,
    SOURCE_CLASS        VARCHAR(50)     NOT NULL,
    TARGET_CLASS        VARCHAR(50)     NOT NULL,
    CARDINALITY         VARCHAR(20)     NOT NULL,   -- 'ONE_TO_MANY', 'MANY_TO_MANY', etc.
    DESCRIPTION         VARCHAR(500),
    PROPERTIES_SCHEMA   VARIANT,
    CREATED_AT          TIMESTAMP_NTZ   DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_RELATION VALUES
('PERFORMED_IN',  'Actor',    'Movie', 'MANY_TO_MANY', 'An actor performs a role in a movie',
    PARSE_JSON('{"role": "STRING", "billing_order": "INTEGER", "screen_time_minutes": "INTEGER"}'),
    CURRENT_TIMESTAMP()),
('DIRECTED',      'Director', 'Movie', 'ONE_TO_MANY',  'A director helms a movie',
    PARSE_JSON('{"credit": "STRING"}'),
    CURRENT_TIMESTAMP()),
('PRODUCED',      'Studio',   'Movie', 'ONE_TO_MANY',  'A studio produces and distributes a movie',
    PARSE_JSON('{"distribution": "STRING"}'),
    CURRENT_TIMESTAMP()),
('PRODUCED',      'Producer', 'Movie', 'MANY_TO_MANY', 'A producer finances or oversees a movie',
    PARSE_JSON('{"credit": "STRING"}'),
    CURRENT_TIMESTAMP()),
('BELONGS_TO',    'Movie',    'Genre', 'MANY_TO_MANY', 'A movie belongs to one or more genres',
    PARSE_JSON('{"primary": "BOOLEAN"}'),
    CURRENT_TIMESTAMP()),
('WON',           'Movie',    'Award', 'MANY_TO_MANY', 'A movie or person won an award',
    PARSE_JSON('{"recipient": "STRING"}'),
    CURRENT_TIMESTAMP()),
('WON',           'Actor',    'Award', 'MANY_TO_MANY', 'An actor won an individual award',
    PARSE_JSON('{"recipient": "STRING", "for_movie": "STRING"}'),
    CURRENT_TIMESTAMP()),
('WON',           'Director', 'Award', 'MANY_TO_MANY', 'A director won an individual award',
    PARSE_JSON('{"recipient": "STRING", "for_movie": "STRING"}'),
    CURRENT_TIMESTAMP()),
('NOMINATED_FOR', 'Movie',    'Award', 'MANY_TO_MANY', 'A movie was nominated for an award',
    PARSE_JSON('{"recipient": "STRING", "result": "STRING"}'),
    CURRENT_TIMESTAMP());

Ontology Rules本体规则

CREATE OR REPLACE TABLE MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_RULES (
    RULE_ID         VARCHAR(50)     NOT NULL,
    RULE_TYPE       VARCHAR(20)     NOT NULL,   -- 'CONSTRAINT', 'VALIDATION', 'DERIVED'
    TARGET_CLASS    VARCHAR(50)     NOT NULL,
    RULE_EXPRESSION VARCHAR(1000)   NOT NULL,
    SEVERITY        VARCHAR(20)     DEFAULT 'ERROR',  -- 'ERROR', 'WARNING', 'INFO'
    DESCRIPTION     VARCHAR(500),
    CREATED_AT      TIMESTAMP_NTZ   DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_RULES VALUES
('RULE_001', 'CONSTRAINT',  'Movie',
    'PROPERTIES:rating >= 0 AND PROPERTIES:rating <= 10',
    'ERROR', 'Movie rating must be between 0 and 10', CURRENT_TIMESTAMP()),
('RULE_002', 'CONSTRAINT',  'Movie',
    'PROPERTIES:budget > 0',
    'ERROR', 'Movie budget must be positive', CURRENT_TIMESTAMP()),
('RULE_003', 'VALIDATION',  'Movie',
    'PROPERTIES:runtime_minutes > 0 AND PROPERTIES:runtime_minutes < 600',
    'WARNING', 'Movie runtime should be between 1 and 600 minutes', CURRENT_TIMESTAMP()),
('RULE_004', 'CONSTRAINT',  'Award',
    'PROPERTIES:ceremony_year >= 1929',
    'ERROR', 'Academy Awards began in 1929; ceremony year cannot be earlier', CURRENT_TIMESTAMP()),
('RULE_005', 'DERIVED',     'Movie',
    'total_box_office = PROPERTIES:domestic_gross + PROPERTIES:international_gross',
    'INFO', 'Total box office is the sum of domestic and international gross', CURRENT_TIMESTAMP());

The ontology is data, not code. Adding a new entity type (say, “Cinematographer”) means inserting a row into KG_ONTOLOGY_CLASS, not altering a table.本体论是数据，而非代码。添加新的实体类型（例如“摄影师”）意味着向 KG_ONTOLOGY_CLASS 插入一行数据，而不是更改表结构。

Layer 3: Auto-Generated Views — The Compiler第 3 层：自动生成视图——编译器

The compiler reads ontology metadata and generates views automatically. When you add a new class or change properties, you re-run the compiler — no manual DDL.编译器读取本体元数据并自动生成视图。当您添加新类或更改属性时，只需重新运行编译器，无需手动执行 DDL。

CREATE OR REPLACE PROCEDURE MOVIES_ONTOLOGY.VIEWS.GENERATE_ONTOLOGY_VIEWS()
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
    -- Step 1: Create a unified view for each root class and its subtypes
    -- For the Person hierarchy: Actor, Director, Producer
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_PERSON AS
    SELECT
        n.NODE_ID,
        n.NODE_TYPE,
        n.PROPERTIES:name::VARCHAR AS NAME,
        n.PROPERTIES:birth_year::INTEGER AS BIRTH_YEAR,
        n.PROPERTIES:nationality::VARCHAR AS NATIONALITY,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM,
        n.VALID_TO
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE IN ('Actor', 'Director', 'Producer')
      AND n.VALID_TO IS NULL;

-- Individual class views
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_ACTOR AS
    SELECT
        n.NODE_ID,
        n.PROPERTIES:name::VARCHAR AS NAME,
        n.PROPERTIES:birth_year::INTEGER AS BIRTH_YEAR,
        n.PROPERTIES:nationality::VARCHAR AS NATIONALITY,
        n.PROPERTIES:gender::VARCHAR AS GENDER,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE = 'Actor' AND n.VALID_TO IS NULL;
    
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_DIRECTOR AS
    SELECT
        n.NODE_ID,
        n.PROPERTIES:name::VARCHAR AS NAME,
        n.PROPERTIES:birth_year::INTEGER AS BIRTH_YEAR,
        n.PROPERTIES:nationality::VARCHAR AS NATIONALITY,
        n.PROPERTIES:known_for::VARCHAR AS KNOWN_FOR,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE = 'Director' AND n.VALID_TO IS NULL;
    
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_PRODUCER AS
    SELECT
        n.NODE_ID,
        n.PROPERTIES:name::VARCHAR AS NAME,
        n.PROPERTIES:birth_year::INTEGER AS BIRTH_YEAR,
        n.PROPERTIES:nationality::VARCHAR AS NATIONALITY,
        n.PROPERTIES:company::VARCHAR AS COMPANY,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE = 'Producer' AND n.VALID_TO IS NULL;
    
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_MOVIE AS
    SELECT
        n.NODE_ID,
        n.PROPERTIES:title::VARCHAR AS TITLE,
        n.PROPERTIES:release_year::INTEGER AS RELEASE_YEAR,
        n.PROPERTIES:budget::FLOAT AS BUDGET,
        n.PROPERTIES:domestic_gross::FLOAT AS DOMESTIC_GROSS,
        n.PROPERTIES:international_gross::FLOAT AS INTERNATIONAL_GROSS,
        (n.PROPERTIES:domestic_gross::FLOAT + n.PROPERTIES:international_gross::FLOAT) AS TOTAL_BOX_OFFICE,
        n.PROPERTIES:runtime_minutes::INTEGER AS RUNTIME_MINUTES,
        n.PROPERTIES:rating::FLOAT AS RATING,
        n.PROPERTIES:language::VARCHAR AS LANGUAGE,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE = 'Movie' AND n.VALID_TO IS NULL;
    
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_GENRE AS
    SELECT
        n.NODE_ID,
        n.PROPERTIES:name::VARCHAR AS NAME,
        n.PROPERTIES:description::VARCHAR AS DESCRIPTION,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE = 'Genre' AND n.VALID_TO IS NULL;
    
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_AWARD AS
    SELECT
        n.NODE_ID,
        n.PROPERTIES:name::VARCHAR AS AWARD_NAME,
        n.PROPERTIES:ceremony::VARCHAR AS CEREMONY,
        n.PROPERTIES:ceremony_year::INTEGER AS CEREMONY_YEAR,
        n.PROPERTIES:category_type::VARCHAR AS CATEGORY_TYPE,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE = 'Award' AND n.VALID_TO IS NULL;
    
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_STUDIO AS
    SELECT
        n.NODE_ID,
        n.PROPERTIES:name::VARCHAR AS NAME,
        n.PROPERTIES:founded_year::INTEGER AS FOUNDED_YEAR,
        n.PROPERTIES:country::VARCHAR AS COUNTRY,
        n.PROPERTIES:parent_company::VARCHAR AS PARENT_COMPANY,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.NODE_TYPE = 'Studio' AND n.VALID_TO IS NULL;
    
-- Step 2: Create a unified entity view (all node types)
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_ENTITY AS
    SELECT
        n.NODE_ID,
        n.NODE_TYPE,
        COALESCE(n.PROPERTIES:name::VARCHAR, n.PROPERTIES:title::VARCHAR) AS DISPLAY_NAME,
        n.PROPERTIES,
        n.SOURCE_SYSTEM,
        n.VALID_FROM,
        n.VALID_TO
    FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    WHERE n.VALID_TO IS NULL;
    
-- Step 3: Create a unified relationship view
    CREATE OR REPLACE VIEW MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_RELATIONSHIPS AS
    SELECT
        e.EDGE_ID,
        e.EDGE_TYPE,
        e.SOURCE_NODE_ID,
        src.NODE_TYPE AS SOURCE_TYPE,
        COALESCE(src.PROPERTIES:name::VARCHAR, src.PROPERTIES:title::VARCHAR) AS SOURCE_NAME,
        e.TARGET_NODE_ID,
        tgt.NODE_TYPE AS TARGET_TYPE,
        COALESCE(tgt.PROPERTIES:name::VARCHAR, tgt.PROPERTIES:title::VARCHAR) AS TARGET_NAME,
        e.PROPERTIES AS EDGE_PROPERTIES,
        e.VALID_FROM
    FROM MOVIES_ONTOLOGY.STORAGE.KG_EDGES e
    JOIN MOVIES_ONTOLOGY.STORAGE.KG_NODES src ON e.SOURCE_NODE_ID = src.NODE_ID AND src.VALID_TO IS NULL
    JOIN MOVIES_ONTOLOGY.STORAGE.KG_NODES tgt ON e.TARGET_NODE_ID = tgt.NODE_ID AND tgt.VALID_TO IS NULL
    WHERE e.VALID_TO IS NULL;
    RETURN 'Ontology views generated successfully: ONTOLOGY_PERSON, ONTOLOGY_ACTOR, ONTOLOGY_DIRECTOR, ONTOLOGY_PRODUCER, ONTOLOGY_MOVIE, ONTOLOGY_GENRE, ONTOLOGY_AWARD, ONTOLOGY_STUDIO, ONTOLOGY_ENTITY, ONTOLOGY_RELATIONSHIPS';
END;

Run the compiler:运行编译器：

CALL MOVIES_ONTOLOGY.VIEWS.GENERATE_ONTOLOGY_VIEWS();

Now we can query with clean SQL:现在我们可以使用简洁的 SQL 进行查询：

-- "Who are all the Persons in our knowledge graph?"
SELECT NODE_TYPE, NAME, BIRTH_YEAR, NATIONALITY
FROM MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_PERSON
ORDER BY NAME;

This returns Actors, Directors, and Producers in a single result set. If tomorrow we add a “Screenwriter” subclass of Person, we rerun the compiler, and this query automatically includes them. No downstream changes required.这会在单个结果集中返回演员、导演和制片人。如果明天我们添加“人”的子类“编剧”，我们只需重新运行编译器，该查询就会自动包含他们。无需进行任何下游更改。

The Six Degrees Query六度分隔查询

One of the most powerful patterns in a knowledge graph is path finding. Here’s a recursive CTE that builds paths by hopping between actors and movies, tracking visited nodes to avoid cycles. The ARRAY and OBJECT_CONSTRUCT functions give us a readable path output.知识图谱中最强大的模式之一是路径查找。这是一个递归 CTE，通过在演员和电影之间跳转来构建路径，同时跟踪已访问的节点以避免循环。ARRAY 和 OBJECT_CONSTRUCT 函数为我们提供了可读的路径输出。

-- Six Degrees of Separation: Find the path between Tom Hanks and Al Pacino
WITH RECURSIVE actor_paths AS (
    -- Start: all movies Tom Hanks appeared in
    SELECT
        e.TARGET_NODE_ID AS CURRENT_NODE,
        n.PROPERTIES:title::VARCHAR AS CURRENT_NAME,
        'Movie' AS CURRENT_TYPE,
        ARRAY_CONSTRUCT(
            OBJECT_CONSTRUCT('id', 'ACTOR_1001', 'name', 'Tom Hanks', 'type', 'Actor'),
            OBJECT_CONSTRUCT('id', e.TARGET_NODE_ID, 'name', n.PROPERTIES:title::VARCHAR, 'type', 'Movie')
        ) AS PATH,
        1 AS HOPS
    FROM MOVIES_ONTOLOGY.STORAGE.KG_EDGES e
    JOIN MOVIES_ONTOLOGY.STORAGE.KG_NODES n ON e.TARGET_NODE_ID = n.NODE_ID
    WHERE e.SOURCE_NODE_ID = 'ACTOR_1001'
      AND e.EDGE_TYPE = 'PERFORMED_IN'
      AND e.VALID_TO IS NULL
      AND n.VALID_TO IS NULL

UNION ALL

    -- Expand: from movies find other actors, from actors find other movies
    SELECT
        e.TARGET_NODE_ID AS CURRENT_NODE,
        COALESCE(n.PROPERTIES:name::VARCHAR, n.PROPERTIES:title::VARCHAR) AS CURRENT_NAME,
        n.NODE_TYPE AS CURRENT_TYPE,
        ARRAY_APPEND(ap.PATH, OBJECT_CONSTRUCT('id', e.TARGET_NODE_ID, 'name',
            COALESCE(n.PROPERTIES:name::VARCHAR, n.PROPERTIES:title::VARCHAR), 'type', n.NODE_TYPE)),
        ap.HOPS + 1
    FROM actor_paths ap
    JOIN MOVIES_ONTOLOGY.STORAGE.KG_EDGES e
        ON ap.CURRENT_NODE = e.SOURCE_NODE_ID
        AND e.VALID_TO IS NULL
        AND e.EDGE_TYPE = 'PERFORMED_IN'
    JOIN MOVIES_ONTOLOGY.STORAGE.KG_NODES n
        ON e.TARGET_NODE_ID = n.NODE_ID
        AND n.VALID_TO IS NULL
    WHERE ap.HOPS < 6
      AND NOT ARRAY_CONTAINS(e.TARGET_NODE_ID::VARIANT, ap.PATH)
)
SELECT PATH, HOPS
FROM actor_paths
WHERE CURRENT_NODE = 'ACTOR_1005'  -- Al Pacino
ORDER BY HOPS
LIMIT 5;

This recursive CTE walks the graph hop by hop — from Tom Hanks to his movies, from those movies to their co-stars, from those co-stars to their other movies — until it reaches Al Pacino. No N-way joins. No hardcoded table relationships. Just traversal.这个递归 CTE 逐跳遍历图——从汤姆·汉克斯到他的电影，从这些电影到它们的联合主演，从这些联合主演到他们的其他电影——直到到达阿尔·帕西诺。没有 N 路连接，没有硬编码的表关系，只有遍历。

Layer 4: Semantic Models — Three Interfaces第 4 层：语义模型——三个接口

Model 1: Knowledge Graph Semantic View模型 1：知识图谱语义视图

The KG semantic view provides the operational analytics layer — metrics, dimensions, verified queries for natural language access.KG 语义视图提供了操作分析层——指标、维度以及用于自然语言访问的已验证查询。

# MOVIES_ONTOLOGY.SEMANTIC.MOVIES_KG_SEMANTIC_VIEW

name: movies_knowledge_graph
description: >
  Operational analytics over the movies knowledge graph.
  Covers box office performance, ratings, award recognition, genre analysis,
  and studio comparisons. Optimized for concrete, metric-driven questions.

custom_instructions: >
  When the user says "blockbuster", filter where total_box_office > 200000000.
  When the user says "classic", filter where release_year < 1990.
  When the user says "modern", filter where release_year >= 2000.
  Box office figures are in USD. Budget is in USD.
  Total box office = domestic_gross + international_gross.
  ROI = (total_box_office - budget) / budget.

tables:
  - name: movies
    base_table:
      database: MOVIES_ONTOLOGY
      schema: VIEWS
      table: ONTOLOGY_MOVIE
    dimensions:
      - name: movie_id
        expr: NODE_ID
        data_type: VARCHAR
      - name: title
        expr: TITLE
        data_type: VARCHAR
      - name: release_year
        expr: RELEASE_YEAR
        data_type: NUMBER
      - name: release_decade
        expr: "FLOOR(RELEASE_YEAR / 10) * 10"
        data_type: NUMBER
      - name: rating_band
        expr: "CASE WHEN RATING >= 8 THEN 'Excellent' WHEN RATING >= 6 THEN 'Good' WHEN RATING >= 4 THEN 'Average' ELSE 'Poor' END"
        data_type: VARCHAR
    facts:
      - name: budget
        expr: BUDGET
        data_type: NUMBER
      - name: total_box_office
        expr: TOTAL_BOX_OFFICE
        data_type: NUMBER
      - name: rating
        expr: RATING
        data_type: NUMBER
    metrics:
      - name: total_box_office_revenue
        expr: SUM(TOTAL_BOX_OFFICE)
        data_type: NUMBER
      - name: average_rating
        expr: AVG(RATING)
        data_type: NUMBER
      - name: movie_count
        expr: COUNT(*)
        data_type: NUMBER
      - name: avg_roi
        expr: "AVG((TOTAL_BOX_OFFICE - BUDGET) / NULLIF(BUDGET, 0))"
        data_type: NUMBER

  - name: cast_performances
    base_table:
      database: MOVIES_ONTOLOGY
      schema: VIEWS
      table: ONTOLOGY_RELATIONSHIPS
    filter: "EDGE_TYPE = 'PERFORMED_IN'"
    dimensions:
      - name: actor_name
        expr: SOURCE_NAME
        data_type: VARCHAR
      - name: movie_title
        expr: TARGET_NAME
        data_type: VARCHAR
      - name: role_name
        expr: "EDGE_PROPERTIES:role::VARCHAR"
        data_type: VARCHAR

  # ... genres, studios, awards tables follow the same pattern

verified_queries:
  - name: average_rating_by_genre
    question: "What is the average rating by genre?"
    verified_query: >
      SELECT g.NAME AS GENRE, AVG(m.RATING) AS AVG_RATING, COUNT(*) AS MOVIE_COUNT
      FROM MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_MOVIE m
      JOIN MOVIES_ONTOLOGY.STORAGE.KG_EDGES e ON m.NODE_ID = e.SOURCE_NODE_ID AND e.EDGE_TYPE = 'BELONGS_TO' AND e.VALID_TO IS NULL
      JOIN MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_GENRE g ON e.TARGET_NODE_ID = g.NODE_ID
      GROUP BY g.NAME ORDER BY AVG_RATING DESC

  - name: top_grossing_movies
    question: "What are the top 10 highest grossing movies?"
    verified_query: >
      SELECT TITLE, RELEASE_YEAR, TOTAL_BOX_OFFICE, BUDGET, RATING
      FROM MOVIES_ONTOLOGY.VIEWS.ONTOLOGY_MOVIE
      ORDER BY TOTAL_BOX_OFFICE DESC LIMIT 10

Model 2: Ontology Model — Entity Traversal UDF模型 2：本体模型——实体遍历 UDF

The ontology model answers abstract, polymorphic questions by traversing the knowledge graph with awareness of the class hierarchy.本体模型通过在了解类层次结构的情况下遍历知识图谱，来回答抽象的多态问题。

CREATE OR REPLACE FUNCTION MOVIES_ONTOLOGY.AGENT.GET_NODE_NEIGHBORHOOD(
    P_NODE_ID VARCHAR,
    P_MAX_HOPS INTEGER DEFAULT 2
)
RETURNS TABLE (
    HOP_LEVEL INTEGER,
    NODE_ID VARCHAR,
    NODE_TYPE VARCHAR,
    DISPLAY_NAME VARCHAR,
    EDGE_TYPE VARCHAR,
    DIRECTION VARCHAR,
    PATH VARCHAR
)
LANGUAGE SQL
AS
$$
    WITH RECURSIVE neighborhood AS (
        -- Anchor: the starting node itself
        SELECT
            0 AS HOP_LEVEL,
            n.NODE_ID,
            n.NODE_TYPE,
            COALESCE(n.PROPERTIES:name::VARCHAR, n.PROPERTIES:title::VARCHAR) AS DISPLAY_NAME,
            'ORIGIN' AS EDGE_TYPE,
            'SELF' AS DIRECTION,
            n.NODE_ID AS PATH
        FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
        WHERE n.NODE_ID = P_NODE_ID AND n.VALID_TO IS NULL

UNION ALL
        -- Outgoing edges (this entity -> related entity)
        SELECT
            nb.HOP_LEVEL + 1,
            tgt.NODE_ID,
            tgt.NODE_TYPE,
            COALESCE(tgt.PROPERTIES:name::VARCHAR, tgt.PROPERTIES:title::VARCHAR),
            e.EDGE_TYPE,
            'OUTGOING',
            nb.PATH || ' -> ' || tgt.NODE_ID
        FROM neighborhood nb
        JOIN MOVIES_ONTOLOGY.STORAGE.KG_EDGES e
            ON nb.NODE_ID = e.SOURCE_NODE_ID AND e.VALID_TO IS NULL
        JOIN MOVIES_ONTOLOGY.STORAGE.KG_NODES tgt
            ON e.TARGET_NODE_ID = tgt.NODE_ID AND tgt.VALID_TO IS NULL
        WHERE nb.HOP_LEVEL < P_MAX_HOPS
          AND NOT CONTAINS(nb.PATH, tgt.NODE_ID)
        UNION ALL
        -- Incoming edges (related entity -> this entity)
        SELECT
            nb.HOP_LEVEL + 1,
            src.NODE_ID,
            src.NODE_TYPE,
            COALESCE(src.PROPERTIES:name::VARCHAR, src.PROPERTIES:title::VARCHAR),
            e.EDGE_TYPE,
            'INCOMING',
            nb.PATH || ' <- ' || src.NODE_ID
        FROM neighborhood nb
        JOIN MOVIES_ONTOLOGY.STORAGE.KG_EDGES e
            ON nb.NODE_ID = e.TARGET_NODE_ID AND e.VALID_TO IS NULL
        JOIN MOVIES_ONTOLOGY.STORAGE.KG_NODES src
            ON e.SOURCE_NODE_ID = src.NODE_ID AND src.VALID_TO IS NULL
        WHERE nb.HOP_LEVEL < P_MAX_HOPS
          AND NOT CONTAINS(nb.PATH, src.NODE_ID)
    )
    SELECT * FROM neighborhood ORDER BY HOP_LEVEL, NODE_TYPE, DISPLAY_NAME
$$;

Model 3: Governance Model — System Introspection UDF模型 3：治理模型——系统内省 UDF

The governance model answers meta-questions: what entity types exist, what relationships are defined, what rules constrain the system.治理模型回答元问题：存在哪些实体类型，定义了哪些关系，以及哪些规则约束着系统。

CREATE OR REPLACE FUNCTION MOVIES_ONTOLOGY.AGENT.EXPLORE_KNOWLEDGE_GRAPH(
    P_QUERY_TYPE VARCHAR DEFAULT 'SUMMARY'
)
RETURNS TABLE (
    CATEGORY VARCHAR,
    ITEM_NAME VARCHAR,
    DETAIL VARCHAR,
    ITEM_COUNT INTEGER
)
LANGUAGE SQL
AS
$$
    SELECT * FROM (
        -- Entity class summary
        SELECT
            'ENTITY_CLASS' AS CATEGORY,
            c.CLASS_NAME AS ITEM_NAME,
            c.DESCRIPTION AS DETAIL,
            (SELECT COUNT(*) FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES n
             WHERE n.NODE_TYPE = c.CLASS_NAME AND n.VALID_TO IS NULL) AS ITEM_COUNT
        FROM MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_CLASS c
        WHERE P_QUERY_TYPE IN ('SUMMARY', 'CLASSES')
        UNION ALL
        -- Relationship summary
        SELECT
            'RELATIONSHIP' AS CATEGORY,
            r.RELATION_NAME AS ITEM_NAME,
            r.SOURCE_CLASS || ' -> ' || r.TARGET_CLASS || ' (' || r.CARDINALITY || ')' AS DETAIL,
            (SELECT COUNT(*) FROM MOVIES_ONTOLOGY.STORAGE.KG_EDGES e
             WHERE e.EDGE_TYPE = r.RELATION_NAME AND e.VALID_TO IS NULL) AS ITEM_COUNT
        FROM MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_RELATION r
        WHERE P_QUERY_TYPE IN ('SUMMARY', 'RELATIONSHIPS')
        UNION ALL
        -- Rules summary
        SELECT
            'RULE' AS CATEGORY,
            ru.RULE_ID AS ITEM_NAME,
            ru.TARGET_CLASS || ': ' || ru.DESCRIPTION AS DETAIL,
            NULL AS ITEM_COUNT
        FROM MOVIES_ONTOLOGY.ONTOLOGY.KG_ONTOLOGY_RULES ru
        WHERE P_QUERY_TYPE IN ('SUMMARY', 'RULES')
        UNION ALL
        -- Overall statistics
        SELECT
            'STATISTICS' AS CATEGORY,
            stat_name AS ITEM_NAME,
            NULL AS DETAIL,
            stat_value AS ITEM_COUNT
        FROM (
            SELECT 'Total Nodes' AS stat_name,
                   COUNT(*) AS stat_value
            FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES WHERE VALID_TO IS NULL
            UNION ALL
            SELECT 'Total Edges',
                   COUNT(*)
            FROM MOVIES_ONTOLOGY.STORAGE.KG_EDGES WHERE VALID_TO IS NULL
            UNION ALL
            SELECT 'Entity Types',
                   COUNT(DISTINCT NODE_TYPE)
            FROM MOVIES_ONTOLOGY.STORAGE.KG_NODES WHERE VALID_TO IS NULL
            UNION ALL
            SELECT 'Relationship Types',
                   COUNT(DISTINCT EDGE_TYPE)
            FROM MOVIES_ONTOLOGY.STORAGE.KG_EDGES WHERE VALID_TO IS NULL
        )
        WHERE P_QUERY_TYPE IN ('SUMMARY', 'STATISTICS')
    )
$$;

Layer 5: Cortex Agent — The Conductor第 5 层：Cortex 代理——指挥官

The agent orchestrates all three models. It determines which tool should answer each question and combines results into coherent responses.代理协调所有三个模型。它确定应该使用哪个工具来回答每个问题，并将结果组合成连贯的响应。

CREATE OR REPLACE CORTEX AGENT MOVIES_ONTOLOGY.AGENT.MOVIES_INTELLIGENCE_AGENT
COMMENT = 'Movies knowledge graph intelligence agent -- routes questions to KG, Ontology, or Governance models'
TOOLS = (
    -- Tool 1: KG Semantic View for operational analytics
    MOVIES_ONTOLOGY.SEMANTIC.MOVIES_KG_SEMANTIC_VIEW,
    -- Tool 2: Ontology traversal for relationship exploration
    MOVIES_ONTOLOGY.AGENT.GET_NODE_NEIGHBORHOOD,
    -- Tool 3: Governance introspection for system metadata
    MOVIES_ONTOLOGY.AGENT.EXPLORE_KNOWLEDGE_GRAPH
)
INSTRUCTIONS = $$
You are a Movies Intelligence Agent. You answer questions about movies, actors, directors, studios, genres, and awards using a knowledge graph built on Snowflake.

You have three specialized tools:
1. **MOVIES_KG_SEMANTIC_VIEW** -- Use for concrete, metric-driven questions:
   - Box office revenue, ratings, budgets
   - Genre comparisons, studio performance
   - Trend analysis by decade or year
   - "What is the average rating by genre?"
   - "Top 10 highest grossing movies?"
2. **GET_NODE_NEIGHBORHOOD** -- Use for relationship and connection questions:
   - "Who is connected to Tom Hanks?"
   - "What movies has Christopher Nolan directed?"
   - "Which actors and directors are linked to this movie?"
   - Pass the NODE_ID (e.g., 'ACTOR_1001' for Tom Hanks) and max hops
3. **EXPLORE_KNOWLEDGE_GRAPH** -- Use for system/governance questions:
   - "What entity types exist?"
   - "How many nodes and edges are in the graph?"
   - "What relationships are defined?"
   - Pass query_type: 'SUMMARY', 'CLASSES', 'RELATIONSHIPS', 'RULES', or 'STATISTICS'
Key NODE_IDs for reference:
- Tom Hanks: ACTOR_1001
- Leonardo DiCaprio: ACTOR_1002
- Meryl Streep: ACTOR_1003
- Christopher Nolan: DIRECTOR_303
- Steven Spielberg: DIRECTOR_302
- Quentin Tarantino: DIRECTOR_304
When the user asks about connections or relationships, use GET_NODE_NEIGHBORHOOD.
When the user asks about numbers, rankings, or comparisons, use the semantic view.
When the user asks about the system itself, use EXPLORE_KNOWLEDGE_GRAPH.
You can combine multiple tools for complex questions.
$$;

Example Questions and Routing示例问题与路由

Press enter or click to view image in full size按回车键或点击以查看完整尺寸图片

Maintenance: Keeping the Graph Fresh维护：保持图谱的实时性

The knowledge graph is a derived structure. As source data changes, it needs to stay current:知识图谱是一个派生结构。随着源数据的变化，它需要保持更新：

CREATE OR REPLACE TASK MOVIES_ONTOLOGY.STORAGE.REFRESH_KNOWLEDGE_GRAPH
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = 'USING CRON 0 6 * * * UTC'
AS
BEGIN
    -- Expire nodes that no longer exist in source
    UPDATE MOVIES_ONTOLOGY.STORAGE.KG_NODES n
    SET VALID_TO = CURRENT_TIMESTAMP()
    WHERE NODE_TYPE = 'Movie'
      AND VALID_TO IS NULL
      AND NOT EXISTS (
          SELECT 1 FROM SOURCE_DB.SOURCE_SCHEMA.MOVIES_RAW mr
          WHERE n.NODE_ID = 'MOVIE_' || mr.MOVIE_ID
      );

-- Upsert new or changed movie nodes
    MERGE INTO MOVIES_ONTOLOGY.STORAGE.KG_NODES tgt
    USING (
        SELECT
            'MOVIE_' || MOVIE_ID AS NODE_ID,
            'Movie' AS NODE_TYPE,
            OBJECT_CONSTRUCT(
                'title', TITLE,
                'release_year', RELEASE_YEAR,
                'budget', BUDGET,
                'domestic_gross', DOMESTIC_GROSS,
                'international_gross', INTERNATIONAL_GROSS,
                'runtime_minutes', RUNTIME_MINUTES,
                'rating', RATING,
                'language', LANGUAGE
            ) AS PROPERTIES,
            'SOURCE_DB' AS SOURCE_SYSTEM
        FROM SOURCE_DB.SOURCE_SCHEMA.MOVIES_RAW
    ) src
    ON tgt.NODE_ID = src.NODE_ID AND tgt.VALID_TO IS NULL
    WHEN MATCHED AND tgt.PROPERTIES != src.PROPERTIES THEN
        UPDATE SET PROPERTIES = src.PROPERTIES, VALID_FROM = CURRENT_TIMESTAMP()
    WHEN NOT MATCHED THEN
        INSERT (NODE_ID, NODE_TYPE, PROPERTIES, SOURCE_SYSTEM)
        VALUES (src.NODE_ID, src.NODE_TYPE, src.PROPERTIES, src.SOURCE_SYSTEM);
    -- Regenerate ontology views after data refresh
    CALL MOVIES_ONTOLOGY.VIEWS.GENERATE_ONTOLOGY_VIEWS();
END;

ALTER TASK MOVIES_ONTOLOGY.STORAGE.REFRESH_KNOWLEDGE_GRAPH RESUME;

The Complete Architecture完整的架构

Here is what we built, mapped to the five layers:以下是我们构建的内容，对应五层架构：

Press enter or click to view image in full size

A data platform without meaning is just expensive storage.没有含义的数据平台只是昂贵的存储空间。

The ontology gives it vocabulary — a shared language for what is allowed to exist. The knowledge graph gives it structure — a connected map of what actually exists. The semantic layer gives it fluency — the ability to translate human questions into precise answers. The agent gives it intelligence — orchestrating all three at runtime.本体论赋予了它词汇表——一种关于允许存在什么的共享语言。知识图谱赋予了它结构——一张关于实际存在什么的连接地图。语义层赋予了它流畅性——将人类问题转化为精确答案的能力。代理赋予了它智能——在运行时协调所有这三者。

Facts are stored once. Meanings are defined as configuration. Intelligence is orchestrated dynamically. Everything runs on Snowflake.事实存储一次。含义定义为配置。智能动态协调。一切都在 Snowflake 上运行。

Start with what your domain means. Build the connections. The SQL will follow.从您的领域含义开始。建立连接。SQL 自然会随之而来。

If you’re building ontology-driven architectures or knowledge graphs on Snowflake, I’d love to hear about your approach. Drop a comment or connect.如果您正在 Snowflake 上构建本体驱动的架构或知识图谱，我很乐意了解您的方法。欢迎评论或联系我。

AI Disclosure: Selected images and visual representations were created using AI image generation models (LLMs and generative AI tools) to enhance the explanation. All technical content has been reviewed and validated by the author.AI 披露：部分图片和视觉表现形式是使用 AI 图像生成模型（LLM 和生成式 AI 工具）创建的，旨在增强解释效果。所有技术内容均已由作者审阅和验证。