Salesforce Docs Portal

Defining a Property Graph

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

A property graph tells Data 360 SQL how your Data 360 objects connect.

  • Customers, orders, and products become vertices.
  • "This customer placed this order" and "this order includes this product" become edges.
  • Selected object fields become properties that you can filter or return.

You must define the graph in the SQL statement that queries it. Graph definitions can't be persisted and referenced in other SQL statements.

WITH <graph_name> AS PROPERTY GRAPH (
    VERTEX TABLES ( <vertex_table>[, ...] )
    [ EDGE TABLES ( <edge_table>[, ...] ) ]
)
<select_using_graph_table>

EDGE TABLES is a synonym for RELATIONSHIP TABLES.

What Each Clause Means

Use these clauses to turn Data 360 objects into graph elements.

Vertex Table

<table> [AS <alias>] KEY ( <column>[, ...] )
    [ LABEL <label> [ <properties> ] ] ...
  • <table> is the Data 360 object, such as ssot__Individual__dlm. It can also be a CTE or a parenthesized subquery when you need a filtered subset of records.
  • AS <alias> gives the element a new name. The REFERENCES clause in an edge table definition uses the alias, not the original table name.
  • KEY identifies each record. Use the object's ID field. A key can list more than one field when one field isn't unique. The listed fields must exist on the source and be distinct.
  • LABEL is the name you use later in MATCH, such as individual or product. More than one table can use the same label. Repeat LABEL on the same table to give that table more than one label. Each LABEL on a table can list its own properties. If you omit LABEL, Data 360 SQL uses the table alias, or name if no alias is given.

Edge Table

<table> [AS <alias>] KEY ( <column>[, ...] )
    SOURCE KEY ( <column>[, ...] ) REFERENCES <vertex_table> [ ( <key_column>[, ...] ) ]
    DESTINATION KEY ( <column>[, ...] ) REFERENCES <vertex_table> [ ( <key_column>[, ...] ) ]
    [ LABEL <label> [ <properties> ] ] ...
  • SOURCE KEY is the field that points to the starting vertex. In the commerce graph, ssot__SoldToCustomerId__c points to the customer, and ssot__SalesOrderId__c points to the order.
  • DESTINATION KEY is the field that points to the ending vertex. ssot__Id__c on the Sales Order points to that order vertex, and ssot__ProductId__c points to the product.
  • The number of source or destination fields must match the referenced vertex key, and the types must be comparable. The optional column list after REFERENCES maps those fields to vertex key fields by position. The listed fields must be key columns of that vertex. If you omit the list, Data 360 SQL uses the vertex key in declared order.

If an edge's source or destination is NULL or doesn't match a vertex, Data 360 SQL leaves that edge out of the results. That isn't an error.

Properties

PROPERTIES ( <expression> [AS <property>][, ...] )
PROPERTIES ARE ALL COLUMNS
PROPERTIES ALL COLUMNS EXCEPT ( <column>[, ...] )
NO PROPERTIES

Start with an explicit list of the fields you need, and give them short names such as name or quantity. Later queries use those property names, not the original ssot__... field names.

When you want every field, use PROPERTIES ARE ALL COLUMNS. Use EXCEPT to hide fields you don't want as properties, or NO PROPERTIES when the table is only a connection. If you expose a field without an alias, MATCH and COLUMNS use the original field name, such as i.ssot__FirstName__c.

Example: An Ecommerce Graph

This graph answers "which products did this customer order?"

WITH pg AS PROPERTY GRAPH (
    VERTEX TABLES (
        ssot__Individual__dlm AS individuals KEY (ssot__Id__c)
            LABEL individual PROPERTIES (
                ssot__Id__c AS individualId,
                ssot__FirstName__c AS name
            ),
        ssot__SalesOrder__dlm AS orders KEY (ssot__Id__c)
            LABEL salesOrder PROPERTIES (
                ssot__Id__c AS orderId,
                ssot__Name__c AS name
            ),
        ssot__GoodsProduct__dlm AS products KEY (ssot__Id__c)
            LABEL product PROPERTIES (
                ssot__Id__c AS productId,
                ssot__Name__c AS name
            )
    )
    EDGE TABLES (
        ssot__SalesOrder__dlm AS customerOrders KEY (ssot__Id__c)
            SOURCE KEY (ssot__SoldToCustomerId__c) REFERENCES individuals
            DESTINATION KEY (ssot__Id__c) REFERENCES orders
            LABEL customerOrder PROPERTIES (
                ssot__CreatedDate__c AS createdDate
            ),
        ssot__SalesOrderProduct__dlm AS orderProducts KEY (ssot__Id__c)
            SOURCE KEY (ssot__SalesOrderId__c) REFERENCES orders
            DESTINATION KEY (ssot__ProductId__c) REFERENCES products
            LABEL orderProduct PROPERTIES (
                ssot__CreatedDate__c AS createdDate,
                ssot__OrderedQuantity__c AS quantity
            )
    )
)
SELECT * FROM GRAPH_TABLE(pg
    MATCH (i:individual)-[:customerOrder]->(:salesOrder)
        -[line:orderProduct]->(product:product)
    COLUMNS (i.name AS individualName, product.name AS productName,
        line.createdDate, line.quantity)
) AS g;

Read the MATCH clause as a walk: start at a customer, follow the order they placed, then follow the line item to the product.

Expose Every Field (or Hide Some)

Use PROPERTIES ARE ALL COLUMNS when you want every field. Use EXCEPT to hide IDs. Use NO PROPERTIES when the table is only a connection. Fields exposed without aliases keep their original names, such as i.ssot__FirstName__c.

WITH pg AS PROPERTY GRAPH (
    VERTEX TABLES (
        ssot__Individual__dlm AS individuals KEY (ssot__Id__c)
            LABEL individual PROPERTIES ARE ALL COLUMNS,
        ssot__SalesOrder__dlm AS orders KEY (ssot__Id__c)
            LABEL salesOrder PROPERTIES ALL COLUMNS EXCEPT (ssot__Id__c),
        ssot__GoodsProduct__dlm AS products KEY (ssot__Id__c)
            LABEL product PROPERTIES ALL COLUMNS EXCEPT (ssot__Id__c)
    )
    EDGE TABLES (
        ssot__SalesOrder__dlm AS customerOrders KEY (ssot__Id__c)
            SOURCE KEY (ssot__SoldToCustomerId__c) REFERENCES individuals
            DESTINATION KEY (ssot__Id__c) REFERENCES orders
            LABEL customerOrder NO PROPERTIES,
        ssot__SalesOrderProduct__dlm AS orderProducts KEY (ssot__Id__c)
            SOURCE KEY (ssot__SalesOrderId__c) REFERENCES orders
            DESTINATION KEY (ssot__ProductId__c) REFERENCES products
            LABEL orderProduct PROPERTIES (ssot__OrderedQuantity__c AS quantity)
    )
)
SELECT * FROM GRAPH_TABLE(pg
    MATCH (i:individual)-[:customerOrder]->(:salesOrder)
        -[line:orderProduct]->(product:product)
    COLUMNS (i.ssot__FirstName__c AS individualName,
        product.ssot__Name__c AS productName, line.quantity)
) AS g;

Related Documentation