Derived Properties: Let Data Compute Itself

“

Series: S4 Ontology Modeling · Article 7 | Level: Intermediate | Reading Time: 18 min

#TL;DR

Derived Properties are the Ontology version of "computed columns" — define a computation rule once, auto-recompute when data changes, and consumers always get the latest value, ending the nightmare of "writing the same calculation logic in 10 places."
9 Reducers (SUM / AVG / COUNT / MIN / MAX / FIRST / LAST / CONCAT / CUSTOM) cover every computation scenario from simple aggregation to custom functions, each with clear semantics and performance characteristics.
Three evaluation modes (Realtime / Deferred / SQL-Backed) allow choosing the optimal strategy based on data volume and latency requirements.

#1. Why Derived Properties Are Needed

#1.1 Pain Points of the Traditional Approach

Code

Scenario: Order "total amount"

Traditional approach (calculation logic everywhere):

Frontend:
  const total = order.items.reduce((sum, item) =>
    sum + item.price * item.quantity, 0)  // Logic #1

Backend API:
  double total = lineItems.stream()
    .mapToDouble(item -> item.getPrice() * item.getQuantity())
    .sum();  // Logic #2

Reporting system:
  SELECT SUM(price * quantity) FROM line_items
  WHERE order_id = ?;  // Logic #3

Data pipeline:
  df['total'] = df['price'] * df['quantity']
  df.groupby('order_id')['total'].sum()  // Logic #4

Problems:
├── Same calculation written 4 times in 4 places
├── One place changes (e.g., adds discount), other 3 are forgotten
├── Different consumers see different "total amounts" → data inconsistency
├── New team members don't know which is the "correct" calculation
└── No audit trail (who defined this calculation? when was it changed?)

#1.2 The coomia-dip Solution

Code

coomia-dip approach (Derived Property):

Define once:
┌──────────────────────────────────────────┐
│  ObjectType: Order                       │
│                                          │
│  properties:                             │
│  ├── orderId: STRING (primary key)       │
│  ├── status: ENUM                        │
│  └── totalAmount: DERIVED                │
│       ├── reducer: SUM                   │
│       ├── source: Order_contains_LineItem │
│       ├── field: lineTotal               │
│       └── where: status != 'CANCELLED'   │
└──────────────────────────────────────────┘

All consumers:
  Frontend: order.totalAmount  // Direct read
  Backend:  order.totalAmount  // Direct read
  Reports:  order.totalAmount  // Direct read
  Pipeline: order.totalAmount  // Direct read

  → Same value, same calculation logic, always consistent ✅

#2. Derived Property Data Model

#2.1 Core Definition

Python

from ontology_sdk import OntologyClient

client = OntologyClient(base_url="http://localhost:8080")

# Create ObjectType with derived properties
client.schema.create_object_type({
    "name": "Order",
    "primaryKey": "orderId",
    "properties": {
        "orderId": {"type": "STRING", "required": True},
        "customerId": {"type": "STRING", "required": True},
        "status": {"type": "ENUM", "enumValues": ["DRAFT", "CONFIRMED", "SHIPPED", "DELIVERED", "CANCELLED"]},

        # Derived property: order total amount
        "totalAmount": {
            "type": "DOUBLE",
            "derived": True,
            "derivation": {
                "reducer": "SUM",
                "sourceRelation": "Order_contains_LineItem",
                "sourceProperty": "lineTotal",
                "filter": {"status": {"neq": "CANCELLED"}},
            },
        },

        # Derived property: line item count
        "itemCount": {
            "type": "INTEGER",
            "derived": True,
            "derivation": {
                "reducer": "COUNT",
                "sourceRelation": "Order_contains_LineItem",
                "filter": {"status": {"neq": "CANCELLED"}},
            },
        },

        # Derived property: average unit price
        "avgItemPrice": {
            "type": "DOUBLE",
            "derived": True,
            "derivation": {
                "reducer": "AVG",
                "sourceRelation": "Order_contains_LineItem",
                "sourceProperty": "unitPrice",
            },
        },

        # Derived property: highest-priced item
        "maxPriceItem": {
            "type": "STRING",
            "derived": True,
            "derivation": {
                "reducer": "MAX",
                "sourceRelation": "Order_contains_LineItem",
                "sourceProperty": "unitPrice",
                "returnProperty": "productName",  # Return product name of max price
            },
        },
    },
})

#2.2 Protobuf Schema

PROTOBUF

message DerivedPropertyDef {
    ReducerType reducer = 1;
    string source_relation = 2;
    string source_property = 3;
    string return_property = 4;
    FilterExpression filter = 5;
    EvaluationMode evaluation_mode = 6;
    string custom_expression = 7;
    string sql_query = 8;
    CacheConfig cache = 9;
}

enum ReducerType {
    SUM = 0;
    AVG = 1;
    COUNT = 2;
    MIN = 3;
    MAX = 4;
    FIRST = 5;
    LAST = 6;
    CONCAT = 7;
    CUSTOM = 8;
}

enum EvaluationMode {
    REALTIME = 0;    // Compute on every read
    DEFERRED = 1;    // Async recompute after changes
    SQL_BACKED = 2;  // Delegate to database
}

#3. 9 Reducers Explained

#3.1 SUM — Summation

Python

# Order total = sum of all line item amounts
"totalAmount": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "SUM",
        "sourceRelation": "Order_contains_LineItem",
        "sourceProperty": "lineTotal",  # lineTotal = unitPrice * quantity
    },
}

# Example data:
# LineItem-1: lineTotal = 100.00
# LineItem-2: lineTotal = 250.00
# LineItem-3: lineTotal = 75.50
# → totalAmount = 425.50

#3.2 AVG — Average

Python

# Customer average order amount
"avgOrderAmount": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "AVG",
        "sourceRelation": "Customer_hasOrder_Order",
        "sourceProperty": "totalAmount",
        "filter": {"status": {"in": ["DELIVERED", "SHIPPED"]}},
    },
}

#3.3 COUNT — Count

Python

# Department employee count
"employeeCount": {
    "type": "INTEGER",
    "derived": True,
    "derivation": {
        "reducer": "COUNT",
        "sourceRelation": "Department_hasEmployee_Employee",
        "filter": {"status": {"eq": "ACTIVE"}},
    },
}

#3.4 MIN — Minimum

Python

# Lowest price in product line
"lowestPrice": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "MIN",
        "sourceRelation": "ProductLine_contains_Product",
        "sourceProperty": "price",
    },
}

#3.5 MAX — Maximum

Python

# Most recent equipment alert time
"lastAlertTime": {
    "type": "TIMESTAMP",
    "derived": True,
    "derivation": {
        "reducer": "MAX",
        "sourceRelation": "Equipment_hasAlert_Alert",
        "sourceProperty": "createdAt",
    },
}

#3.6 FIRST — First Value

Python

# Customer's first order date
"firstOrderDate": {
    "type": "DATE",
    "derived": True,
    "derivation": {
        "reducer": "FIRST",
        "sourceRelation": "Customer_hasOrder_Order",
        "sourceProperty": "orderDate",
        "orderBy": "orderDate",
        "orderDirection": "ASC",
    },
}

#3.7 LAST — Last Value

Python

# Customer's most recent activity
"lastActivity": {
    "type": "STRING",
    "derived": True,
    "derivation": {
        "reducer": "LAST",
        "sourceRelation": "Customer_hasActivity_Activity",
        "sourceProperty": "description",
        "orderBy": "timestamp",
        "orderDirection": "ASC",
    },
}

#3.8 CONCAT — Concatenation

Python

# All product tags (comma-separated)
"tagList": {
    "type": "STRING",
    "derived": True,
    "derivation": {
        "reducer": "CONCAT",
        "sourceRelation": "Product_hasTag_Tag",
        "sourceProperty": "name",
        "separator": ", ",
        "orderBy": "name",
        "maxLength": 500,
    },
}
# → "AI, Machine Learning, NLP, Python"

#3.9 CUSTOM — Custom Expression

Python

# Custom calculation: weighted average rating
"weightedRating": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": """
            sum(review.rating * review.weight) / sum(review.weight)
        """,
        "sourceRelation": "Product_hasReview_Review",
        "variables": {
            "review.rating": "rating",
            "review.weight": "helpfulVotes",
        },
    },
}

# Custom calculation: health score (based on multiple sensor readings)
"healthScore": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": """
            let temp_score = if(temperature > 80, 0, if(temperature > 60, 50, 100));
            let vibration_score = if(vibration > 10, 0, if(vibration > 5, 50, 100));
            let pressure_score = if(pressure < 2 || pressure > 8, 0, 100);
            (temp_score + vibration_score + pressure_score) / 3
        """,
        "sourceRelation": "Equipment_hasReading_SensorReading",
        "variables": {
            "temperature": "readings.temperature",
            "vibration": "readings.vibration",
            "pressure": "readings.pressure",
        },
    },
}

#4. Three Evaluation Modes

#4.1 REALTIME — Realtime Evaluation

Code

Characteristics:
├── Computed on every read request
├── Data is always fresh
├── Computation cost borne at read time
└── Suitable when related data is small (< 1000 records)

┌──────┐    Read request    ┌──────────────┐    Query related     ┌──────┐
│Client│──────────────────►│ Runtime      │──────────────────────►│  DB  │
│      │◄──────────────────│              │◄──────────────────────│      │
│      │  Computed result  │  (realtime)  │    Raw data          │      │
└──────┘                   └──────────────┘                      └──────┘

Python

# Configure realtime evaluation
"employeeCount": {
    "type": "INTEGER",
    "derived": True,
    "derivation": {
        "reducer": "COUNT",
        "sourceRelation": "Department_hasEmployee_Employee",
        "evaluationMode": "REALTIME",
    },
}

#4.2 DEFERRED — Deferred Evaluation (Recommended)

Code

Characteristics:
├── Async recomputation after source data changes
├── Results cached, reads return cached value directly
├── Triggered on write, no extra cost on read
├── Brief inconsistency window (typically < 5 seconds)
└── Suitable for most business scenarios

Write flow:
┌──────┐   Write LineItem   ┌──────┐   Publish change event   ┌───────────┐
│Client│───────────────────►│  DB  │──────────────────────────►│ Event Bus │
└──────┘                    └──────┘                           └─────┬─────┘
                                                                     │
                                                                     ▼
                                                              ┌───────────┐
                                                              │ Recompute │
                                                              │  Worker   │
                                                              │           │
                                                              │ SUM(...)  │
                                                              └─────┬─────┘
                                                                    │
                                                                    ▼ Update cache
                                                              ┌───────────┐
                                                              │   Cache   │
                                                              └───────────┘

Read flow:
┌──────┐   Read totalAmount   ┌───────────┐
│Client│─────────────────────►│   Cache   │──► Return cached value directly
└──────┘                      └───────────┘

Python

# Configure deferred evaluation (default mode)
"totalAmount": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "SUM",
        "sourceRelation": "Order_contains_LineItem",
        "sourceProperty": "lineTotal",
        "evaluationMode": "DEFERRED",
        "cache": {
            "ttlSeconds": 300,           # Cache TTL 5 minutes
            "invalidateOnChange": True,  # Invalidate immediately on source change
        },
    },
}

#4.3 SQL_BACKED — SQL-Delegated Evaluation

Code

Characteristics:
├── Computation logic pushed down to the database
├── Leverages database computation power (for complex aggregations)
├── Suitable for large data volumes and complex computations
├── Depends on database performance
└── Supports window functions, subqueries, and advanced SQL features

┌──────┐   Read request   ┌──────────────┐   Execute SQL   ┌──────┐
│Client│─────────────────►│   Runtime    │────────────────►│ Doris│
│      │◄─────────────────│              │◄────────────────│      │
│      │  Computed result │ (SQL Proxy)  │  Query result   │      │
└──────┘                  └──────────────┘                 └──────┘

Python

# Configure SQL-delegated evaluation
"rollingAvg30d": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "evaluationMode": "SQL_BACKED",
        "sqlQuery": """
            SELECT AVG(daily_amount) as value
            FROM (
                SELECT DATE(order_date) as dt, SUM(total_amount) as daily_amount
                FROM orders
                WHERE customer_id = :objectId
                  AND order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                GROUP BY DATE(order_date)
            ) daily_sums
        """,
    },
}

# Window function example
"salesRank": {
    "type": "INTEGER",
    "derived": True,
    "derivation": {
        "evaluationMode": "SQL_BACKED",
        "sqlQuery": """
            SELECT rank_value as value FROM (
                SELECT product_id,
                       RANK() OVER (
                           PARTITION BY category
                           ORDER BY total_sales DESC
                       ) as rank_value
                FROM product_sales_summary
            ) ranked
            WHERE product_id = :objectId
        """,
    },
}

#5. Evaluation Mode Selection Guide

Code

┌──────────────┬──────────────┬──────────────┬──────────────┐
│   Feature     │   REALTIME   │   DEFERRED   │  SQL_BACKED  │
├──────────────┼──────────────┼──────────────┼──────────────┤
│ Data freshness│ 100% realtime│ Delay < 5s   │ Depends on   │
│              │              │              │ SQL          │
│ Read perf    │ Slow (needs  │ Fast (cache) │ Depends on   │
│              │ computation) │              │ SQL          │
│ Write impact │ None         │ Triggers     │ None         │
│              │              │ recompute    │              │
│ Data volume  │ < 1K related │ < 100K       │ Unlimited    │
│ Complexity   │ Simple agg   │ Simple agg   │ Arbitrarily  │
│              │              │              │ complex      │
│ Use case     │ RT dashboards│ Most business│ Complex      │
│              │              │ scenarios    │ analytics    │
│ Cache cost   │ None         │ Yes          │ Optional     │
└──────────────┴──────────────┴──────────────┴──────────────┘

Decision tree:
  Data volume > 100K?
  ├── Yes → SQL_BACKED
  └── No → Need 100% realtime?
            ├── Yes → REALTIME
            └── No → DEFERRED (recommended)

#6. Expression Evaluation Engine

Python

# Derived properties support rich expression syntax

# Math operations
"profit": {
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": "self.revenue - self.cost",  # Reference own properties
    },
}

# Conditional expressions
"riskLevel": {
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": """
            if(self.creditScore >= 750, 'LOW',
               if(self.creditScore >= 600, 'MEDIUM',
                  'HIGH'))
        """,
    },
}

# Date calculations
"daysSinceLastOrder": {
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": "dateDiff(now(), self.lastOrderDate, 'DAYS')",
    },
}

# String operations
"fullAddress": {
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": """
            concat(self.homeAddress.province, ' ',
                   self.homeAddress.city, ' ',
                   self.homeAddress.street)
        """,
    },
}

# Cross-relation reference
"managerName": {
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": "traverse(self, 'Employee_reportsTo_Employee').name",
    },
}

#7. Derived Property Update Mechanisms

#7.1 Trigger-Based Updates

Code

Change propagation path:

LineItem.quantity changes
    │
    ▼
LineItem.lineTotal recomputed (lineTotal = unitPrice * quantity)
    │
    ▼
Order.totalAmount recomputed (SUM of lineTotal)
    │
    ▼
Order.avgItemPrice recomputed (AVG of unitPrice)
    │
    ▼
Customer.totalSpent recomputed (SUM of Order.totalAmount)
    │
    ▼
Customer.loyaltyTier recomputed (tier based on totalSpent)

This is "cascade recomputation" — covered in detail in the next article (S4-08)

#7.2 Batch Recomputation

Python

# Manually trigger batch recomputation (used after data migration or repair)
result = client.schema.recompute_derived_property(
    object_type="Order",
    property_name="totalAmount",
    filter={"status": {"in": ["CONFIRMED", "SHIPPED"]}},
    batch_size=1000,
    concurrency=5,
)

print(f"Recomputation complete: {result.total_objects} objects")
print(f"Duration: {result.duration_seconds} seconds")
print(f"Succeeded: {result.success_count}")
print(f"Failed: {result.failure_count}")

#8. Derived Properties and Indexes

Python

# Derived properties can also be indexed (for filtering and sorting)
client.schema.create_index({
    "objectType": "Order",
    "properties": ["totalAmount"],
    "type": "BTREE",
    "name": "idx_order_total_amount",
})

# Query based on derived properties
orders = client.objects.search("Order", {
    "filters": {
        "totalAmount": {"gte": 10000},  # High-value orders
    },
    "orderBy": "totalAmount",
    "orderDirection": "DESC",
    "maxResults": 20,
})

#9. Error Handling

Python

# Handling strategies for derived property computation failures

"riskyDerived": {
    "type": "DOUBLE",
    "derived": True,
    "derivation": {
        "reducer": "CUSTOM",
        "expression": "self.revenue / self.unitsSold",  # Possible division by zero
        "errorHandling": {
            "onDivisionByZero": "RETURN_NULL",  # Return null on division by zero
            "onNullInput": "RETURN_NULL",        # Return null when input is null
            "onOverflow": "RETURN_MAX",           # Return max value on overflow
            "onError": "RETURN_DEFAULT",          # Return default value on other errors
            "defaultValue": 0,
        },
    },
}

#10. Real-World Case: Customer 360 View

Python

# Complete derived property set for a Customer

client.schema.create_object_type({
    "name": "Customer",
    "primaryKey": "customerId",
    "properties": {
        "customerId": {"type": "STRING", "required": True},
        "name": {"type": "STRING", "required": True},
        "email": {"type": "STRING"},
        "registeredAt": {"type": "TIMESTAMP"},

        # 1. Order statistics
        "totalOrders": {
            "type": "INTEGER",
            "derived": True,
            "derivation": {"reducer": "COUNT", "sourceRelation": "Customer_hasOrder_Order"},
        },
        "totalSpent": {
            "type": "DOUBLE",
            "derived": True,
            "derivation": {"reducer": "SUM", "sourceRelation": "Customer_hasOrder_Order", "sourceProperty": "totalAmount"},
        },
        "avgOrderAmount": {
            "type": "DOUBLE",
            "derived": True,
            "derivation": {"reducer": "AVG", "sourceRelation": "Customer_hasOrder_Order", "sourceProperty": "totalAmount"},
        },

        # 2. Time dimensions
        "firstOrderDate": {
            "type": "DATE",
            "derived": True,
            "derivation": {"reducer": "MIN", "sourceRelation": "Customer_hasOrder_Order", "sourceProperty": "orderDate"},
        },
        "lastOrderDate": {
            "type": "DATE",
            "derived": True,
            "derivation": {"reducer": "MAX", "sourceRelation": "Customer_hasOrder_Order", "sourceProperty": "orderDate"},
        },
        "daysSinceLastOrder": {
            "type": "INTEGER",
            "derived": True,
            "derivation": {"reducer": "CUSTOM", "expression": "dateDiff(now(), self.lastOrderDate, 'DAYS')"},
        },

        # 3. Customer segmentation
        "loyaltyTier": {
            "type": "STRING",
            "derived": True,
            "derivation": {
                "reducer": "CUSTOM",
                "expression": """
                    if(self.totalSpent >= 100000, 'PLATINUM',
                       if(self.totalSpent >= 50000, 'GOLD',
                          if(self.totalSpent >= 10000, 'SILVER',
                             'BRONZE')))
                """,
            },
        },

        # 4. Activity score
        "activityScore": {
            "type": "DOUBLE",
            "derived": True,
            "derivation": {
                "reducer": "CUSTOM",
                "expression": """
                    let recency = min(self.daysSinceLastOrder / 90.0, 1.0);
                    let frequency = min(self.totalOrders / 50.0, 1.0);
                    let monetary = min(self.totalSpent / 100000.0, 1.0);
                    (1 - recency) * 0.3 + frequency * 0.3 + monetary * 0.4
                """,
            },
        },

        # 5. Product preference
        "topCategory": {
            "type": "STRING",
            "derived": True,
            "derivation": {
                "evaluationMode": "SQL_BACKED",
                "sqlQuery": """
                    SELECT category as value
                    FROM order_line_items oli
                    JOIN orders o ON oli.order_id = o.order_id
                    JOIN products p ON oli.product_id = p.product_id
                    WHERE o.customer_id = :objectId
                    GROUP BY category
                    ORDER BY COUNT(*) DESC
                    LIMIT 1
                """,
            },
        },
    },
})

# Use derived properties for customer analysis
vip_customers = client.objects.search("Customer", {
    "filters": {
        "loyaltyTier": {"in": ["PLATINUM", "GOLD"]},
        "activityScore": {"gte": 0.7},
        "daysSinceLastOrder": {"lte": 30},
    },
    "orderBy": "totalSpent",
    "orderDirection": "DESC",
})

for c in vip_customers:
    print(f"Customer: {c.name}")
    print(f"  Tier: {c.loyaltyTier}")
    print(f"  Total spent: ${c.totalSpent:,.2f}")
    print(f"  Activity score: {c.activityScore:.2f}")
    print(f"  Last order: {c.daysSinceLastOrder} days ago")
    print(f"  Top category: {c.topCategory}")

#11. Performance Optimization

Code

Derived property performance optimization strategies:

┌─────────────────────────────────────────────────┐
│          Performance Optimization Matrix         │
├────────────────┬────────────────────────────────┤
│  Strategy       │  Use Case                      │
├────────────────┼────────────────────────────────┤
│  Deferred+cache │  Most scenarios (recommended)  │
│  Incremental    │  SUM/COUNT/AVG can update      │
│  computation    │  incrementally                 │
│  SQL pushdown   │  Complex aggregations, large   │
│                 │  data volumes                  │
│  Pre-materialize│  Read-heavy derived properties │
│  Batch recompute│  Data repair, initialization   │
│  Parallel       │  Multiple independent derived  │
│  recompute      │  properties                    │
└────────────────┴────────────────────────────────┘

Incremental computation example (SUM):
  Old value: totalAmount = 1000
  New LineItem.lineTotal = 200 added
  New value: totalAmount = 1000 + 200 = 1200

  No need to re-traverse all LineItems!
  Time complexity drops from O(N) to O(1)

#Key Takeaways

Derived properties eliminate calculation logic duplication — define once, and all consumers (frontend, backend, reports, pipelines) see the same result, with data consistency guaranteed at the Schema level.
9 Reducers cover every scenario: SUM/AVG/COUNT/MIN/MAX for basic aggregation, FIRST/LAST for ordered value selection, CONCAT for text joining, CUSTOM for arbitrarily complex expressions.
Choosing between three evaluation modes: REALTIME guarantees freshness (small data), DEFERRED balances freshness and performance (recommended), SQL_BACKED handles complex computation (large data).
The expression engine supports rich syntax — math operations, conditionals, date calculations, string operations, and cross-relation references, sufficient for the vast majority of business computations.
Derived properties can be indexed — computed results can be filtered and sorted, participating in queries just like regular properties.

#Next Article

The next article, S4-08 Derived Property DAG, will dive into the internals of "cascade computation" — how topological sort ensures correct computation order when one property change triggers multi-level derived property recomputation, how cycle detection works, and how to batch-recompute millions of objects.

#ontology #derived-property #reducer #expression-eval #sql-backed #computed-column #data-consistency