Architecture

Conditionals

Building CASE WHEN statements with a simple, intuitive pipeline approach for non-technical users.

Overview

Cortex provides a powerful yet intuitive system for building conditional logic (CASE WHEN statements) that's accessible to non-technical product teams. Instead of nested expression trees, we use a linear pipeline approach that's easy to understand and build UIs for.

Why Pipeline Approach?

Traditional conditional logic systems use complex nested expression trees that look like this:

IF (AND (status = 'active', amount > 100)) THEN 'premium' ELSE 'standard'

This approach is difficult for UI builders and hard for non-technical users to understand.

Instead, Cortex uses a simple pipeline where you:

Reference a column (with optional transforms)
Perform a comparison
Return a value if true
Specify an else value

Pipeline Flow

ColumnField → Transforms → Comparison → Return Value

Core Building Blocks

ColumnField

A reference to a database column with optional transforms applied in order.

from cortex.core.types.semantics.column_field import ColumnField
from cortex.core.semantics.transforms import Transform, TransformFunction

# Simple column reference
ColumnField(column="status", table="users")

# Column with transforms (pipeline)
ColumnField(
    column="status",
    table="users",
    transforms=[
        Transform(function=TransformFunction.COALESCE, params={"fallback": ""}),
        Transform(function=TransformFunction.LOWER)
    ]
)

Key Points:

Always specify table when working with joins
Transforms are applied left-to-right
Each transform receives the output of the previous one

Transform

A single SQL function in the transformation pipeline.

from cortex.core.semantics.transforms import Transform, TransformFunction

# Handle nulls
Transform(function=TransformFunction.COALESCE, params={"fallback": ""})

# Convert to lowercase
Transform(function=TransformFunction.LOWER)

# Round to 2 decimal places
Transform(function=TransformFunction.ROUND, params={"decimals": 2})

WhenClause & Conditions

Define CASE WHEN logic with multiple conditions.

from cortex.core.semantics.conditions import Condition, WhenClause, ComparisonOperator
from cortex.core.types.semantics.column_field import ColumnField

Condition(
    when_clauses=[
        WhenClause(
            field=ColumnField(column="status", table="users"),
            operator=ComparisonOperator.IN,
            compare_values=["active", "pending"],
            then_return="Open"
        )
    ],
    else_return="Closed"
)

Transform Functions Reference

Cortex supports 14 SQL functions that can be chained together:

String Functions

Function	Parameters	Description	Example
COALESCE	`{"fallback": value}`	Handle null values	`COALESCE(name, 'Unknown')`
LOWER	None	Convert to lowercase	`LOWER('Hello')` → `'hello'`
UPPER	None	Convert to uppercase	`UPPER('hello')` → `'HELLO'`
TRIM	None	Remove leading/trailing whitespace	`TRIM(' hello ')` → `'hello'`
CONCAT	`{"values": [col1, col2, ...]}`	Concatenate strings	`CONCAT('John', ' ', 'Doe')` → `'John Doe'`
SUBSTRING	`{"start": n, "length": m}`	Extract substring	`SUBSTRING('Hello', 1, 3)` → `'Hel'`

Math Functions

Function	Parameters	Description	Example
ROUND	`{"decimals": n}`	Round numbers	`ROUND(3.14159, 2)` → `3.14`
ABS	None	Absolute value	`ABS(-5)` → `5`
CEIL	None	Round up	`CEIL(3.2)` → `4`
FLOOR	None	Round down	`FLOOR(3.9)` → `3`

Date Functions

Function	Parameters	Description	Example
EXTRACT	`{"part": "YEAR\|MONTH\|DOW"}`	Extract date parts	`EXTRACT(YEAR FROM date)`
DATE_TRUNC	`{"part": "year\|month\|day"}`	Truncate to precision	`DATE_TRUNC('month', date)`
DATE_PART	`{"part": "year\|month\|day"}`	Extract date part	`DATE_PART('year', date)`

Type Conversion

Function	Parameters	Description	Example
CAST	`{"type": "TEXT\|INTEGER\|FLOAT"}`	Type conversion	`CAST(value AS INTEGER)`

Comparison Operators

Use these operators to compare values in conditions:

Operator	SQL Equivalent	Usage	Example
EQUALS	`=`	Equality	`status = 'active'`
NOT_EQUALS	`!=`	Inequality	`status != 'deleted'`
GREATER_THAN	`>`	Greater than	`amount > 100`
LESS_THAN	`<`	Less than	`amount < 100`
GREATER_EQUAL	`>=`	Greater or equal	`amount >= 100`
LESS_EQUAL	`<=`	Less or equal	`amount <= 100`
IN	`IN`	Membership	`status IN ('a', 'b')`
NOT_IN	`NOT IN`	Not membership	`status NOT IN ('x', 'y')`
LIKE	`LIKE`	Pattern match	`name LIKE '%john%'`
BETWEEN	`BETWEEN`	Range	`amount BETWEEN 100 AND 1000`
IS_NULL	`IS NULL`	Null check	`value IS NULL`
IS_NOT_NULL	`IS NOT NULL`	Not null check	`value IS NOT NULL`

Examples

Attendance Percentage

Calculate attendance percentage using conditional aggregation:

from cortex.core.semantics.measures import SemanticMeasure
from cortex.core.types.semantics.measure import SemanticMeasureType
from cortex.core.semantics.conditions import Condition, WhenClause, ComparisonOperator
from cortex.core.semantics.transforms import Transform, TransformFunction
from cortex.core.types.semantics.column_field import ColumnField

# Build the condition
attendance_condition = Condition(
    when_clauses=[
        WhenClause(
            field=ColumnField(
                column="status",
                table="attendance",
                transforms=[
                    Transform(function=TransformFunction.COALESCE, params={"fallback": ""}),
                    Transform(function=TransformFunction.LOWER)
                ]
            ),
            operator=ComparisonOperator.IN,
            compare_values=["present", "attended", "yes", "y", "p"],
            then_return=1
        )
    ],
    else_return=0
)

# Create the measure
attendance_measure = SemanticMeasure(
    name="Attendance %",
    type=SemanticMeasureType.AVG,
    conditional=True,
    conditions=attendance_condition
)

Generated SQL:

AVG(
  CASE
    WHEN LOWER(COALESCE(attendance.status, '')) IN ('present', 'attended', 'yes', 'y', 'p') THEN 1
    ELSE 0
  END
) AS "Attendance %"

Revenue Bucket Classification

Classify orders into size categories:

from cortex.core.semantics.dimensions import SemanticDimension
from cortex.core.semantics.conditions import Condition, WhenClause, ComparisonOperator
from cortex.core.types.semantics.column_field import ColumnField

revenue_condition = Condition(
    when_clauses=[
        WhenClause(
            field=ColumnField(column="order_amount", table="orders"),
            operator=ComparisonOperator.LESS_THAN,
            compare_values=100,
            then_return="Small"
        ),
        WhenClause(
            field=ColumnField(column="order_amount", table="orders"),
            operator=ComparisonOperator.LESS_THAN,
            compare_values=1000,
            then_return="Medium"
        ),
    ],
    else_return="Large"
)

dimension = SemanticDimension(
    name="Order Size",
    conditional=True,
    conditions=revenue_condition
)

Generated SQL:

CASE
  WHEN orders.order_amount < 100 THEN 'Small'
  WHEN orders.order_amount < 1000 THEN 'Medium'
  ELSE 'Large'
END AS "Order Size"

Weekday vs Weekend Sessions

Extract day of week and classify:

from cortex.core.semantics.measures import SemanticMeasure
from cortex.core.types.semantics.measure import SemanticMeasureType
from cortex.core.semantics.conditions import Condition, WhenClause, ComparisonOperator
from cortex.core.semantics.transforms import Transform, TransformFunction
from cortex.core.types.semantics.column_field import ColumnField

weekday_condition = Condition(
    when_clauses=[
        WhenClause(
            field=ColumnField(
                column="session_date",
                table="sessions",
                transforms=[Transform(function=TransformFunction.EXTRACT, params={"part": "DOW"})]
            ),
            operator=ComparisonOperator.IN,
            compare_values=[1, 2, 3, 4, 5],
            then_return=1
        )
    ],
    else_return=0
)

measure = SemanticMeasure(
    name="Weekday Sessions",
    type=SemanticMeasureType.SUM,
    conditional=True,
    conditions=weekday_condition
)

Generated SQL:

SUM(
  CASE
    WHEN EXTRACT(DOW FROM sessions.session_date) IN (1, 2, 3, 4, 5) THEN 1
    ELSE 0
  END
) AS "Weekday Sessions"

Architecture

Processor System

Cortex uses specialized processors to convert conditional logic to SQL:

TransformProcessor

Converts Transform objects into SQL functions:

from cortex.core.query.engine.processors.transform_processor import TransformProcessor

# Apply a single transform
sql = TransformProcessor.process_transform(
    transform=Transform(function=TransformFunction.LOWER),
    input_sql="users.name",
    dialect="postgres"
)
# Result: "LOWER(users.name)"

ConditionProcessor

Converts Condition objects into complete CASE WHEN SQL:

from cortex.core.query.engine.processors.condition_processor import ConditionProcessor

sql = ConditionProcessor.process_condition(
    condition=my_condition,
    table_alias_map={"users": "u"},
    dialect="postgres"
)
# Result: "CASE WHEN ... THEN ... ELSE ... END"

Integration with Query Generators

When conditional=True and conditions are provided, query generators automatically use ConditionProcessor:

# In base_sql.py _format_measure()
if measure.conditional and measure.conditions:
    condition_sql = ConditionProcessor.process_condition(
        measure.conditions,
        self._table_alias_map,
        self.source_type.value
    )
    agg_sql = f'SUM({condition_sql})'  # or AVG, COUNT, etc.
    return f'{agg_sql} AS "{measure.name}"'
else:
    # Fallback to simple query mode
    ...

Database Dialect Support

All processors support multiple SQL dialects with automatic translation:

PostgreSQL (default)
MySQL
BigQuery
SQLite

Dialect-specific handling ensures functions like EXTRACT, DATE_PART work correctly across all databases.

Using Conditional Logic

In Measures, Dimensions, and Filters

All semantic components support conditional logic through a conditional flag and conditions field:

# In measures.py
class SemanticMeasure(TSModel):
    query: Optional[str] = None          # Simple mode
    conditional: bool = False             # Use conditional logic
    conditions: Optional[Condition] = None # CASE WHEN definition

# In dimensions.py
class SemanticDimension(TSModel):
    query: str                            # Simple mode
    conditional: bool = False
    conditions: Optional[Condition] = None

# In filters.py
class SemanticFilter(TSModel):
    query: str
    conditional: bool = False
    conditions: Optional[Condition] = None

Choosing Between Modes

Use conditional=False (simple query) when:

Working with a single column
No CASE WHEN logic needed
Simple aggregation or grouping

Use conditional=True (conditions) when:

Need multiple WHEN clauses
Require data transformations first
Building complex categorization
Creating conditional aggregations

Best Practices

1. Always Specify Table in ColumnField

When working with joins, always specify the table field:

# Good
ColumnField(column="amount", table="orders")

# Bad (ambiguous with joins)
ColumnField(column="amount")

2. Use Transforms for Data Cleaning

Apply transforms to handle common data quality issues:

# Handle nulls and case-insensitive comparison
ColumnField(
    column="status",
    transforms=[
        Transform(function=TransformFunction.COALESCE, params={"fallback": ""}),
        Transform(function=TransformFunction.LOWER)
    ]
)

3. Keep Comparisons Simple

One comparison per WhenClause for clarity:

# Good: Multiple simple clauses
Condition(
    when_clauses=[
        WhenClause(field=amount, operator=LESS_THAN, compare_values=100, then_return="Small"),
        WhenClause(field=amount, operator=LESS_THAN, compare_values=1000, then_return="Medium"),
    ],
    else_return="Large"
)

# Avoid: Complex nested logic (use multiple measures instead)

4. Test with Your Database

Some databases have different function names. Test your conditions:

# PostgreSQL
EXTRACT(DOW FROM date)

# MySQL
DAYOFWEEK(date)

# Cortex handles this automatically via dialect support

Next Steps

For Practical Usage: See the semantic documentation for Measures, Dimensions, and Filters
For Implementation Details: See cortex/core/query/engine/processors/
For Transform Reference: See cortex/core/semantics/transforms.py

Edit this pageorReport an issue

Storage

Overview of the Cortex storage layer and SQL connectors.

Measures

Quantitative data points for analysis, representing the numerical values you track, calculate, and aggregate.