Build a knowledge graph with AI Pipelines in Azure HorizonDB

AI Pipelines support both structured and unstructured graph construction. This guide uses nested JSON so you can focus on the graph sink without requiring a model endpoint. Each source row represents a department and contains an employees array. One AI Pipeline creates department and employee nodes, connects them with WORKS_IN relationships, processes later source rows incrementally, and supports replay without duplicate graph objects.

After the runnable walkthrough, you adapt the pattern for unstructured documents by adding a chunking and AI extraction stage that creates the same graph-ready JSON shape.

You complete these tasks:

  • Enable the required extensions.
  • Create a nested JSONB source table.
  • Register a graph ontology.
  • Map one JSON document to many graph nodes and relationships.
  • Run and monitor the pipeline.
  • Validate idempotency and incremental processing.
  • Understand when to add optional text-identity deduplication.
  • Query and visualize the graph.
  • Review cleanup steps and preview limitations.

Prerequisites

Before you begin, ensure you have:

  • An Azure HorizonDB cluster and database.
  • An administrator who can configure cluster parameters and install extensions.
  • A database role without the PostgreSQL SUPERUSER attribute that can create the guide's tables and run the pipeline.
  • A firewall rule or private network path that allows your client to connect.
  • psql, Azure Data Studio, or the PostgreSQL extension for Visual Studio Code.
  • The age and azure_ai extensions available on the cluster.
  • azure_ai 2.5.6 or later for the unstructured extraction adaptation, which uses ai.extract_step(). For earlier versions, follow the database upgrade instructions.
  • age configured in shared_preload_libraries.
  • Access to pg_durable for background, crash-recoverable execution. It's optional for the structured walkthrough and required for the unstructured extraction adaptation.

The runnable department example doesn't call an AI model because the source already contains structured JSON. The unstructured adaptation later in this article requires a registered chat or generation model.

Enable the extensions

In the Azure portal, create or update a parameter group:

  1. Add age and azure_ai to azure.extensions.
  2. Add age to shared_preload_libraries.
  3. Add pg_durable to both azure.extensions and shared_preload_libraries if you want durable background execution and your cluster supports it.
  4. Connect the parameter group to the cluster.
  5. Wait for the cluster restart to complete.

Connect to the test database through the read/write endpoint as an administrator, then run:

CREATE EXTENSION IF NOT EXISTS age CASCADE;
CREATE EXTENSION IF NOT EXISTS azure_ai CASCADE;

SET search_path = public, ag_catalog;

If your cluster supports durable execution and you enabled pg_durable in shared_preload_libraries, you can also run:

CREATE EXTENSION IF NOT EXISTS pg_durable;

Confirm the extensions:

SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('age', 'azure_ai', 'pg_durable')
ORDER BY extname;

You don't need to run LOAD 'age' on Azure HorizonDB when the cluster preloads AGE through shared_preload_libraries.

Run the remaining steps as a database role without the PostgreSQL SUPERUSER attribute. The role needs access to the source table, pipeline functions, and graph labels. ai.run() rejects superuser callers.

Verify the current role:

SELECT current_user, rolsuper
FROM pg_roles
WHERE rolname = current_user;

Continue when rolsuper is false. If it is true, switch to a nonsuperuser role with the required database permissions.

Create the source table

Create one JSONB document per department. Include a monotonic updated_at column for incremental processing.

DROP TABLE IF EXISTS department_documents;

CREATE TABLE department_documents (
    source_id integer PRIMARY KEY,
    updated_at timestamptz NOT NULL,
    document jsonb NOT NULL
);

INSERT INTO department_documents (source_id, updated_at, document)
VALUES
(
    1,
    '2026-10-13 15:00:00+00',
    '{
      "department_id": 1,
      "department_name": "Platform",
      "employees": [
        {"employee_id": 10, "employee_name": "Ava"},
        {"employee_id": 11, "employee_name": "Ben"}
      ]
    }'::jsonb
),
(
    2,
    '2026-10-13 15:01:00+00',
    '{
      "department_id": 2,
      "department_name": "Data",
      "employees": [
        {"employee_id": 12, "employee_name": "Cara"},
        {"employee_id": 13, "employee_name": "Dan"}
      ]
    }'::jsonb
),
(
    3,
    '2026-10-13 15:02:00+00',
    '{
      "department_id": 3,
      "department_name": "Security",
      "employees": [
        {"employee_id": 14, "employee_name": "Eve"}
      ]
    }'::jsonb
);

Inspect the source:

SELECT
    source_id,
    updated_at,
    document->>'department_name' AS department,
    jsonb_array_length(document->'employees') AS employee_count
FROM department_documents
ORDER BY source_id;

Register the graph ontology

The ontology defines valid node labels, identity properties, node properties, and edge endpoints.

SELECT ai.create_graph(
    name => 'department_graph',
    ontology => '{
      "nodes": [
        {
          "label": "Department",
          "identity": "id",
          "properties": ["id", "name", "disp_label"]
        },
        {
          "label": "Employee",
          "identity": "id",
          "properties": ["id", "name", "disp_label"]
        }
      ],
      "edges": [
        {
          "label": "WORKS_IN",
          "from": "Employee",
          "to": "Department"
        }
      ]
    }'::jsonb
);

Review the registered ontology:

SELECT name, ontology, created_by, created_at
FROM ai.graphs
WHERE name = 'department_graph';

Create the graph pipeline

The pipeline reads the document JSONB column. The top-level mappings create one department for each source row. The each block iterates through the employees array.

SELECT ai.create_pipeline(
    name => 'department_documents_to_graph',
    source => ai.table_source(
        table_name => 'department_documents',
        incremental_column => 'updated_at'
    ),
    -- For a top-level text identity, use:
    -- steps => ARRAY[ai.deduplicate(input => 'department_name')],
    steps => ARRAY[]::jsonb[], -- Numeric identities don't need deduplication.
    sink => ai.graph_sink(
        graph_name => 'department_graph',
        mappings => '{
          "nodes": [
            {
              "label": "Department",
              "property": "id",
              "from": "$.department_id"
            },
            {
              "label": "Department",
              "property": "name",
              "from": "$.department_name"
            },
            {
              "label": "Department",
              "property": "disp_label",
              "from": "$.department_name"
            }
          ],
          "each": [
            {
              "path": "employees",
              "nodes": [
                {
                  "label": "Employee",
                  "property": "id",
                  "from": "@.employee_id"
                },
                {
                  "label": "Employee",
                  "property": "name",
                  "from": "@.employee_name"
                },
                {
                  "label": "Employee",
                  "property": "disp_label",
                  "from": "@.employee_name"
                }
              ],
              "edges": [
                {
                  "edge_label": "WORKS_IN",
                  "from_key": "@.employee_id",
                  "to_key": "$.department_id"
                }
              ]
            }
          ]
        }'::jsonb,
        source_column => 'document'
    ),
    trigger => 'manual'
);

Inspect the compiled pipeline:

SELECT ai.explain('department_documents_to_graph');

Run the pipeline

Use synchronous execution for this guide so each validation query runs after the graph write completes:

SET azure_ai.graph_sink_durable = off;
SELECT ai.run('department_documents_to_graph');

Check the pipeline status:

SELECT *
FROM ai.status('department_documents_to_graph');

Inspect run history:

SELECT *
FROM ai.result('department_documents_to_graph');

The most recent run should report completed before you validate the graph.

In an application workflow, keep azure_ai.graph_sink_durable at its default value of on when pg_durable is available and the graph pipeline has no transform steps. In that mode, ai.run() can return an instance identifier before the background worker completes.

Validate the graph

Count nodes by label:

SELECT *
FROM ag_catalog.cypher(
    'department_graph',
    $$
      MATCH (node)
      RETURN label(node) AS label, count(*) AS node_count
    $$
) AS (
    label ag_catalog.agtype,
    node_count ag_catalog.agtype
)
ORDER BY label;

Expected counts:

Label Count
Department 3
Employee 5

Count relationships:

SELECT *
FROM ag_catalog.cypher(
    'department_graph',
    $$
      MATCH ()-[relationship]->()
      RETURN type(relationship) AS relationship_type,
             count(*) AS relationship_count
    $$
) AS (
    relationship_type ag_catalog.agtype,
    relationship_count ag_catalog.agtype
);

The graph contains five WORKS_IN relationships.

Find employees in the Platform department:

SELECT *
FROM ag_catalog.cypher(
    'department_graph',
    $$
      MATCH (employee:Employee)-[:WORKS_IN]->(department:Department {name: 'Platform'})
      RETURN employee.name
    $$
) AS (
    employee_name ag_catalog.agtype
)
ORDER BY employee_name;

The query returns Ava and Ben.

Verify idempotency

Advance every source watermark without changing the documents. Keep these watermarks earlier than the new document in the next section:

UPDATE department_documents AS target
SET updated_at = source.updated_at
FROM (
    VALUES
        (1, '2026-10-13 15:05:00+00'::timestamptz),
        (2, '2026-10-13 15:06:00+00'::timestamptz),
        (3, '2026-10-13 15:07:00+00'::timestamptz)
) AS source(source_id, updated_at)
WHERE target.source_id = source.source_id;

Run the pipeline to replay all three unchanged documents:

SELECT ai.run('department_documents_to_graph');

After the run finishes, verify that the graph still contains three departments and five employees:

SELECT *
FROM ag_catalog.cypher(
    'department_graph',
    $$
      MATCH (node)
      RETURN label(node) AS label, count(*) AS node_count
    $$
) AS (
    label ag_catalog.agtype,
    node_count ag_catalog.agtype
)
ORDER BY label;

Verify that replay didn't duplicate relationships:

SELECT *
FROM ag_catalog.cypher(
    'department_graph',
    $$
      MATCH ()-[relationship:WORKS_IN]->()
      RETURN count(relationship) AS relationship_count
    $$
) AS (
    relationship_count ag_catalog.agtype
);

The relationship count remains five. Identity-based upserts keep both node and relationship counts stable.

Process a new document incrementally

Insert a new department document with a later watermark:

INSERT INTO department_documents (source_id, updated_at, document)
VALUES
(
    4,
    '2026-10-13 15:10:00+00',
    '{
      "department_id": 4,
      "department_name": "Reliability",
      "employees": [
        {"employee_id": 15, "employee_name": "Fatima"},
        {"employee_id": 16, "employee_name": "Mateo"}
      ]
    }'::jsonb
);

Run the pipeline:

SELECT ai.run('department_documents_to_graph');

After completion, verify the updated counts:

SELECT *
FROM ag_catalog.cypher(
    'department_graph',
    $$
      MATCH (node)
      RETURN label(node) AS label, count(*) AS node_count
    $$
) AS (
    label ag_catalog.agtype,
    node_count ag_catalog.agtype
)
ORDER BY label;

The graph now contains four departments and seven employees.

Understand deduplication behavior

This guide uses numeric identities, which avoid ambiguity. The graph sink compares identity values exactly.

Use ai.deduplicate() when a graph uses a text value as a node identity and your source can contain case or surrounding-whitespace variants. The step must run in a pipeline with a graph sink. It rewrites the selected column before the graph mappings run.

If your source table exposes its text identity as a top-level column, add the step to the original pipeline's steps array:

steps => ARRAY[
    ai.deduplicate(input => 'department_name')
],

Then map that same department_name column to the identity property in ai.graph_sink(). Don't add the step to this guide's runnable pipeline because its Department and Employee identities are numeric IDs inside the document JSONB column.

For this preview, ai.deduplicate():

  • Ignores uppercase and lowercase differences.
  • Trims whitespace at the start and end of the value.

It doesn't perform semantic entity resolution. For example, it doesn't infer that Customer Success and Customer Support refer to the same department.

Don't use display names as identities when your source provides a stable business key.

Adapt the workflow for unstructured documents

Before you continue, enable, preload, and install pg_durable by following the earlier extension setup. AI transform steps such as ai.extract_step() require durable execution even when the graph sink itself runs synchronously.

For unstructured documents, add an extraction stage before the graph sink:

Document table
     |
     v
Chunk and AI extraction pipeline
     |
     v
JSONB graph fragment table
     |
     v
Graph sink pipeline from this guide
     |
     v
Apache AGE graph

Register an extraction model

Use AI Model Management or register a Microsoft Foundry model through the model registry. Confirm the model with a one-shot AI Functions call before you build the extraction pipeline.

The following extraction pipeline uses the model alias default-chat. If you register the model under another alias, replace default-chat with that alias.

Define the graph fragment contract

Use durable source keys in every extracted fragment. Don't use the doc_id from ai.chunk_step() as provenance because it's a batch-local row number and can repeat across incremental runs.

The graph sink in this guide expects this shape:

{
  "department_id": 1,
  "department_name": "Platform",
  "employees": [
    {
      "employee_id": 10,
      "employee_name": "Ava"
    }
  ],
  "_source": {
    "source_id": 9001,
    "source_revision": 1,
    "chunk_index": 0
  }
}

For another domain, replace the department and employee fields with ontology-aligned arrays. For example:

  • Incident reports can extract services, teams, dependencies, and failure relationships.
  • Contracts can extract parties, obligations, clauses, and references.
  • Research papers can extract authors, methods, datasets, and citations.
  • Support tickets can extract products, symptoms, fixes, and affected components.

Prepare source-linked chunks

Create chunks before the extraction pipeline so each chunk retains the source document identity and revision. The identity column provides a monotonic watermark for incremental pipeline runs when writes follow the serialized commit contract described after the table.

CREATE TABLE department_document_chunks (
    source_id        bigint NOT NULL,
    source_revision  bigint NOT NULL,
    chunk_index      integer NOT NULL,
    chunk_text       text NOT NULL,
    relationship_count integer NOT NULL CHECK (relationship_count = 1),
    chunk_watermark  bigint GENERATED ALWAYS AS IDENTITY,
    PRIMARY KEY (source_id, source_revision, chunk_index),
    UNIQUE (chunk_watermark)
);

Write source-linked chunks from your application or another trusted preparation process. Treat a revision as immutable. Insert a new source_revision when the source document changes. Use one writer for this staging table, commit each batch before the writer starts the next batch, and don't overlap writer transactions. Identity values reflect allocation order, not commit order, so concurrent writers can otherwise commit a higher watermark before a lower one. If you need multiple producers, serialize their writes through an ingestion service or queue that assigns the watermark in commit order.

The trusted preparation process must verify that each emitted chunk contains exactly one department-to-employee relationship and set relationship_count to 1. If source text describes multiple employees, split it into multiple chunks with the same source identity and revision but different chunk indexes. The check constraint rejects chunks that declare any other relationship count.

This sample adds one relationship chunk:

INSERT INTO department_document_chunks (
    source_id,
    source_revision,
    chunk_index,
    chunk_text,
    relationship_count
)
VALUES (
    9001,
    1,
    0,
    'Department 5 is Customer Reliability. Employee 30 is Diego and works in department 5.',
    1
);

Create the extraction staging pipeline

Create the extraction sink with the durable source keys. The pipeline reads chunk_text directly instead of calling ai.chunk_step(), so the keys and watermark stay in the output row.

CREATE TABLE department_extraction_output (
    source_id        bigint NOT NULL,
    source_revision  bigint NOT NULL,
    chunk_index      integer NOT NULL,
    chunk_text       text NOT NULL,
    relationship_count integer NOT NULL CHECK (relationship_count = 1),
    chunk_watermark  bigint NOT NULL,
    metadata         jsonb,
    PRIMARY KEY (source_id, source_revision, chunk_index)
);

SELECT ai.create_pipeline(
    name => 'extract_department_graph_fragments',
    source => ai.table_source(
        table_name => 'department_document_chunks',
        incremental_column => 'chunk_watermark'
    ),
    steps => ARRAY[
        ai.extract_step(
            input => 'chunk_text',
            data => ARRAY[
                'department_id: number - stable department identifier',
                'department_name: string - department name',
                'employee_id: number - stable employee identifier',
                'employee_name: string - employee name'
            ],
            model => 'default-chat'
        )
    ],
    sink => ai.table_sink(
        table_name => 'department_extraction_output',
        on_conflict => ARRAY[
            'source_id',
            'source_revision',
            'chunk_index'
        ],
        on_conflict_action =>
            'DO UPDATE SET chunk_text = EXCLUDED.chunk_text, relationship_count = EXCLUDED.relationship_count, chunk_watermark = EXCLUDED.chunk_watermark, metadata = EXCLUDED.metadata'
    ),
    trigger => 'manual'
);

SELECT ai.run('extract_department_graph_fragments');
SELECT * FROM ai.status('extract_department_graph_fragments');

Wait for the extraction run to finish before you prepare graph fragments. The extracted fields are in metadata; they aren't separate sink columns.

Normalize the graph fragments

Create a separate graph staging table. Its identity column is the monotonic watermark for the graph pipeline when writes use the same serialized commit contract. The _source object keeps evidence traceable without participating in node or edge identity.

CREATE TABLE department_graph_fragments (
    source_id        bigint NOT NULL,
    source_revision  bigint NOT NULL,
    chunk_index      integer NOT NULL,
    graph_watermark  bigint GENERATED ALWAYS AS IDENTITY,
    document         jsonb NOT NULL,
    PRIMARY KEY (source_id, source_revision, chunk_index),
    UNIQUE (graph_watermark)
);

WITH validated AS (
    SELECT
        *,
        CASE
            WHEN jsonb_typeof(metadata->'department_id') = 'number'
            THEN (metadata->>'department_id')::numeric
        END AS department_id_numeric,
        CASE
            WHEN jsonb_typeof(metadata->'employee_id') = 'number'
            THEN (metadata->>'employee_id')::numeric
        END AS employee_id_numeric
    FROM department_extraction_output
    WHERE metadata ?& ARRAY[
        'department_id',
        'department_name',
        'employee_id',
        'employee_name'
    ]
      AND nullif(btrim(metadata->>'department_name'), '') IS NOT NULL
      AND nullif(btrim(metadata->>'employee_name'), '') IS NOT NULL
)
INSERT INTO department_graph_fragments (
    source_id,
    source_revision,
    chunk_index,
    document
)
SELECT
    source_id,
    source_revision,
    chunk_index,
    jsonb_build_object(
        'department_id', department_id_numeric::bigint,
        'department_name', metadata->>'department_name',
        'employees', jsonb_build_array(
            jsonb_build_object(
                'employee_id', employee_id_numeric::bigint,
                'employee_name', metadata->>'employee_name'
            )
        ),
        '_source', jsonb_build_object(
            'source_id', source_id,
            'source_revision', source_revision,
            'chunk_index', chunk_index
        )
    )
FROM validated
WHERE department_id_numeric BETWEEN 0 AND 9223372036854775807
  AND department_id_numeric = trunc(department_id_numeric)
  AND employee_id_numeric BETWEEN 0 AND 9223372036854775807
  AND employee_id_numeric = trunc(employee_id_numeric)
ON CONFLICT (source_id, source_revision, chunk_index) DO NOTHING;

Use one writer for department_graph_fragments, commit each batch before the writer starts the next batch, and don't overlap writer transactions. Use a commit-ordering ingestion layer if multiple producers must write fragments.

Rows with missing, empty, nonnumeric, nonintegral, or out-of-range required identities stay in department_extraction_output for review. Integral numeric representations such as 5, 5.0, and 5.00 pass validation. To correct a source document or extraction, insert a new source_revision; don't overwrite a fragment that the graph pipeline might already process.

Create the graph staging pipeline

Create a second pipeline that reads only validated graph fragments. This stage uses the knowledge graph pipeline to resolve ontology identities and write the nodes and WORKS_IN edges to Apache AGE.

SELECT ai.create_pipeline(
    name => 'extracted_fragments_to_department_graph',
    source => ai.table_source(
        table_name => 'department_graph_fragments',
        incremental_column => 'graph_watermark'
    ),
    steps => ARRAY[]::jsonb[],
    sink => ai.graph_sink(
        graph_name => 'department_graph',
        mappings => '{
          "nodes": [
            {
              "label": "Department",
              "property": "id",
              "from": "$.department_id"
            },
            {
              "label": "Department",
              "property": "name",
              "from": "$.department_name"
            },
            {
              "label": "Department",
              "property": "disp_label",
              "from": "$.department_name"
            }
          ],
          "each": [
            {
              "path": "employees",
              "nodes": [
                {
                  "label": "Employee",
                  "property": "id",
                  "from": "@.employee_id"
                },
                {
                  "label": "Employee",
                  "property": "name",
                  "from": "@.employee_name"
                },
                {
                  "label": "Employee",
                  "property": "disp_label",
                  "from": "@.employee_name"
                }
              ],
              "edges": [
                {
                  "edge_label": "WORKS_IN",
                  "from_key": "@.employee_id",
                  "to_key": "$.department_id"
                }
              ]
            }
          ]
        }'::jsonb,
        source_column => 'document'
    ),
    trigger => 'manual'
);

The two-stage pattern isolates model variability from deterministic graph writes. You can validate or approve graph fragments before the knowledge graph pipeline loads them.

Validate extracted graph fragments

Before loading the graph, check:

  • Required entity identities are present and scalar values.
  • Relationship endpoints refer to emitted or existing nodes.
  • Labels and properties match the ontology.
  • Arrays use the shape expected by each each mapping.
  • Each fragment represents one department-to-employee relationship, and its employees array contains exactly one object.
  • The extraction preserves a source identifier and evidence reference.
  • Low-confidence or high-impact relationships follow your review policy.

After the fragments pass these checks and any required approval, load them into the graph:

SELECT ai.run('extracted_fragments_to_department_graph');
SELECT * FROM ai.status('extracted_fragments_to_department_graph');

Visualize the graph in Visual Studio Code

Connect the PostgreSQL extension for Visual Studio Code to the Azure HorizonDB read/write endpoint.

Run a query that returns complete node and relationship objects:

SELECT *
FROM ag_catalog.cypher(
    'department_graph',
    $$
      MATCH (employee:Employee)-[relationship:WORKS_IN]->(department:Department)
      RETURN employee, relationship, department
    $$
) AS (
    employee ag_catalog.agtype,
    relationship ag_catalog.agtype,
    department ag_catalog.agtype
);

The Apache AGE Graph Visualizer renders employees and departments as nodes connected by WORKS_IN edges. The disp_label properties you set earlier provide readable labels.

To inspect the pipeline:

  1. In Object Explorer, open the database context menu.
  2. Select Pipelines and Workflows.
  3. Open the AI Pipelines tab.
  4. Select department_documents_to_graph.
  5. Review the run state and the details available in your installed extension version.

Known limitations

Keep these preview behaviors in mind:

  • Deleting a source row doesn't delete its graph nodes or relationships.
  • Removing an employee from an existing JSON array doesn't retract the existing WORKS_IN relationship.
  • An updated source row runs incrementally only when updated_at advances.
  • Backdated or out-of-order updated_at values can fall below the saved checkpoint.
  • The pipeline supports one level of each.
  • The mapping doesn't support conditional filters or expressions.
  • Null or missing identities skip the related graph emission.
  • Missing edge endpoints fail the run and roll back the transaction.
  • Pipelines and direct writers that target the same graph must run serially.
  • A mapping change doesn't automatically reprocess source rows below the current checkpoint.
  • The role that calls ai.run() can't have the PostgreSQL SUPERUSER attribute.
  • Unstructured input requires a chunking and extraction stage that produces graph-ready JSON before the graph sink runs.
  • AI extraction can produce incomplete or incorrect graph fragments. Validate output before loading sensitive or high-impact graphs.

Clean up resources

Cleaning up requires a role that can drop the AGE graph and delete its registry row. Ask your database administrator to run:

SELECT ai.drop_pipeline('department_documents_to_graph');

SELECT ag_catalog.drop_graph('department_graph', true)
WHERE EXISTS (
    SELECT 1
    FROM ag_catalog.ag_graph
    WHERE name = 'department_graph'
);

DELETE FROM ai.graphs
WHERE name = 'department_graph';

DROP TABLE department_documents;

If you ran the unstructured-data adaptation, drop its pipelines before its staging tables:

SELECT ai.drop_pipeline('extracted_fragments_to_department_graph');
SELECT ai.drop_pipeline('extract_department_graph_fragments');

DROP TABLE department_graph_fragments;
DROP TABLE department_extraction_output;
DROP TABLE department_document_chunks;

The preview doesn't provide ai.drop_graph(), so cleaning up removes the AGE graph and its registry row separately.