Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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
SUPERUSERattribute 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
ageandazure_aiextensions available on the cluster. azure_ai2.5.6 or later for the unstructured extraction adaptation, which usesai.extract_step(). For earlier versions, follow the database upgrade instructions.ageconfigured inshared_preload_libraries.- Access to
pg_durablefor 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:
- Add
ageandazure_aitoazure.extensions. - Add
agetoshared_preload_libraries. - Add
pg_durableto bothazure.extensionsandshared_preload_librariesif you want durable background execution and your cluster supports it. - Connect the parameter group to the cluster.
- 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
eachmapping. - Each fragment represents one department-to-employee relationship, and its
employeesarray 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:
- In Object Explorer, open the database context menu.
- Select Pipelines and Workflows.
- Open the AI Pipelines tab.
- Select
department_documents_to_graph. - 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_INrelationship. - An updated source row runs incrementally only when
updated_atadvances. - Backdated or out-of-order
updated_atvalues 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 PostgreSQLSUPERUSERattribute. - 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.