Making Databricks Delta Tables Graph Friendly¶

How to structure your Delta tables to enable graph queries with gsql2rsql.

Why Delta Lake for Graphs?¶

Delta Lake can store terabytes of graph data efficiently while still enabling fast queries:

• ACID transactions
• Time travel for auditing and debugging
• Efficient storage
• Data skipping to reduce I/O

Instead of maintaining a separate graph database, you can query your existing Delta tables as a graph.

The Triple Store Pattern¶

One approach I find convenient is the Triple Store pattern: one table for nodes, one table for edges.

┌─────────────────┐         ┌─────────────────┐
│   nodes table   │         │   edges table   │
├─────────────────┤         ├─────────────────┤
│ node_id (PK)    │◄────────│ src (FK)        │
│ node_type       │         │ dst (FK)        │
│ ...properties   │         │ relationship_type│
└─────────────────┘         │ ...properties   │
                            └─────────────────┘

This is not the only approach, and may not be optimal for your use case. But it's flexible and works well with gsql2rsql's GraphContext API.

Nodes Table¶

CREATE TABLE catalog.schema.nodes (
    node_id STRING NOT NULL,
    node_type STRING NOT NULL,  -- 'Person', 'Company', etc.
    name STRING,
    -- ... other properties
)
USING DELTA;

Edges Table¶

CREATE TABLE catalog.schema.edges (
    src STRING NOT NULL,
    dst STRING NOT NULL,
    relationship_type STRING NOT NULL,  -- 'KNOWS', 'WORKS_AT', etc.
    -- ... edge properties
)
USING DELTA;

Alternative: Separate Tables¶

If you already have separate tables per entity type, gsql2rsql supports that too via the low-level API:

people (id, name, email)
companies (id, name, industry)
employment (person_id, company_id, role)

See Low-Level API for how to configure this.

Databricks Optimizations¶

Liquid Clustering vs Partitioning with Z-ORDER¶

Which Columns to Optimize?¶

For graph queries, the most important columns are:

Secondary columns (less impact):

Liquid Clustering¶

CREATE TABLE catalog.schema.edges (
    src STRING,
    dst STRING,
    relationship_type STRING
)
USING DELTA
CLUSTER BY (src, dst);

Pros: - Automatic optimization (no manual OPTIMIZE runs) - Adapts to changing data patterns

Cons: - Performance can be unpredictable - Less control over clustering behavior - May not work well with high-cardinality columns

Partitioning + Z-ORDER¶

-- Create with partitioning (if you have clear access patterns)
CREATE TABLE catalog.schema.edges (...)
USING DELTA
PARTITIONED BY (relationship_type);

-- Then Z-ORDER on join columns
OPTIMIZE catalog.schema.edges
ZORDER BY (src, dst);

Pros: - More predictable behavior - Fine-grained control - Well-understood optimization

Cons: - Requires periodic OPTIMIZE runs - Partitioning can create small file problems

When to Use What?¶

Data Skipping¶

Delta Lake automatically tracks min/max statistics for data skipping. To maximize effectiveness:

1. Z-Order on join columns (src, dst, node_id) - this is the most important
2. Keep column cardinality reasonable - very high cardinality reduces skipping effectiveness

Bloom Filters¶

HELP WANTED!

Data Skew¶

Graph data is often highly skewed - some nodes have many more connections than others (e.g., celebrity accounts, hub nodes). Also, some relationship types are far more frequent than others

Why Skew Matters¶

Detecting Skew¶

Mitigating Skew¶

HELP WANTED

Column Naming¶

gsql2rsql uses sensible defaults. Override them if your tables use different names:

graph = GraphContext(
    nodes_table="my_nodes",
    edges_table="my_edges",

    # Custom column names
    node_id_col="id",              # default: "node_id"
    node_type_col="type",          # default: "node_type"
    edge_src_col="source",         # default: "src"
    edge_dst_col="target",         # default: "dst"
    edge_type_col="rel_type",      # default: "relationship_type"
)

What's Missing¶

Next Steps¶

• User Guide - Start using gsql2rsql
• Examples - Real-world query examples