RAI Python to SQL Comparison#

This guide maps common SQL operations to their RAI equivalents, helping users with a SQL background transition smoothly to using RAI.

Table of Contents#

Core Concepts
Key Differences
Models
Types
Objects and Properties
Relationships
Rules and Queries
Missing Values
Stored Procedures
Resources

Core Concepts#

The following table maps the core RAI concepts discussed in the Core Concepts guide to their SQL equivalents:

RAI Concept	SQL Equivalent	Explanation
Model	Schema	A model in RAI serves as a logical container that groups related types and rules, similar to how a Snowflake schema organizes related tables and views. Models define the overall structure and relationships of data entities within the system.
Type	Entity Table / View	A type in RAI is analogous to a table or view in SQL. It represents a collection of objects (instances). Unlike SQL tables, types in RAI are schemaless, allowing dynamic addition of properties without predefined columns.
Object	Row	An object in RAI corresponds to a row in a SQL table. It is an instance of a type and represents a single record within that type. Unlike SQL rows, which belong to a single table, an object in RAI can be associated with multiple types.
Property	Column / Many-to-Many Relationship Table	A property in RAI is similar to a column in a SQL table, representing an attribute of an object. Properties can be single-valued (analogous to a single column) or multi-valued (similar to a join table used for many-to-many relationships in SQL). Unlike SQL columns, properties in RAI do not have fixed data types and can hold different types of values for different objects.
Rule	Constraint / Trigger / Stored Procedure	Rules in RAI define logical relationships, data transformations, and business logic between objects and their properties. They encompass functionalities similar to constraints, triggers, and stored procedures in SQL: constraints enforce data integrity, triggers automate actions in response to data changes, and stored procedures encapsulate complex operations. RAI rules integrate these functionalities in a declarative manner to manage and manipulate data within the model.
Query	SQL Query	A query in RAI retrieves information from the model based on the defined rules and types.

Key Differences#

While RAI and SQL share some similarities, there are several key differences that you should be aware of when transitioning from SQL to RAI.

Schemaless Types vs. Fixed Schemas#

RAI	SQL
Schemaless Types: Types in RAI are schemaless, allowing you to add properties dynamically to objects without needing to alter a predefined schema. This flexibility means that different objects of the same type can have different sets of properties.	Fixed Schemas: Tables in SQL have fixed schemas that must be defined upfront. Altering the schema, such as adding or removing columns, requires executing `ALTER TABLE` statements. All rows within a table share the same set of columns.

Flexible Data Types vs. Fixed Data Types#

RAI	SQL
Flexible Data Types: Properties in RAI do not have fixed data types. The same property can hold different types of values for different objects, providing greater flexibility in how data is stored and used.	Fixed Data Types: Each column in SQL has a predefined data type, and all values within that column must conform to this type. This ensures data consistency but limits flexibility.

Multi-Valued Properties vs. Many-to-Many Relationship Tables#

RAI	SQL
Multi-Valued Properties: RAI supports both single-valued and multi-valued properties. Multi-valued properties can directly reference multiple related values for a single object.	Many-To-Many Relationship Tables: SQL requires separate tables and foreign keys to represent many-to-many relationships, as columns are inherently single-valued.

Multiple Type Membership vs. Single Table Membership#

RAI	SQL
Multiple Type Membership: An object in RAI can be an instance of multiple types simultaneously, allowing for flexible and rich data modeling.	Single Table Membership: In SQL, a row belongs to one table. While views can present data differently, the underlying row exists in only one table, limiting the ability to represent multiple roles or classifications for a single record directly.

Immutable Data vs. Mutable Data with Direct Updates#

RAI	SQL
Immutable Data: In RAI, data is treated as immutable within the context of a query. Instead of updating objects directly, add new rules or edit existing ones to produce new states.	Mutable Data with Direct Updates: SQL allows data to be updated in place using `UPDATE` statements. Transactions can include a mix of inserts, updates, and deletes, enabling direct modifications to existing data.

Presence-Based Values vs. Null Values#

RAI	SQL
Presence-Based Values: RAI does not have a `NULL` value. Instead, property values are either present or absent. Methods like `.not_found()` and `.or_()` are used to handle missing values, providing alternatives to `NULL` checks.	Null Values: SQL uses `NULL` to represent missing or unknown values. Operators like `IS NULL` and functions like `COALESCE()` are used to handle `NULL` values in queries and data manipulations.

Set-Based Aggregations vs. Row-Based Aggregations#

RAI	SQL
Set-Based Aggregations: RAI’s aggregate functions operate on sets of values assigned to properties. By default, aggregations consider distinct property values, but additional parameters may be passed to control grouping and aggregation behavior, allowing both set-based and row-based aggregations similar to SQL.	Row-Based Aggregations: SQL uses `GROUP BY` clauses to group rows, and aggregate functions operate over each row within these groups. Aggregations are performed on column values across all rows in each group, including duplicates unless `DISTINCT` is specified.

Models#

RAI models are logical containers that group related types and rules together. To create a model, import the relationalai package and create a Model object:

RAI

#import relationalai as rai

# Additional recommended imports:
from relationalai.std import alias  # For customizing result column names
from relationalai.std import aggregates  # For functions like count, sum, min, max, etc.

model = rai.Model("MyModel")

SQL

#CREATE SCHEMA IF NOT EXISTS MyModel;
USE SCHEMA MyModel;

IMPORTANT

Models connect to your Snowflake account using your raiconfig.toml profile or connection details provided to the Model constructor. See Cloud Notebooks and Local Installation for details.

See What is a Model? for more information about models.

Types#

Use the model.Type() constructor to define a new type in RAI:

RAI

## Create a new type named 'Person'. Person is an instance of the Type class.
Person = model.Type("Person")
# Optionally, declare properties used by the Person type.
Person.id.declare()

SQL

#CREATE TABLE IF NOT EXISTS Person (
    id INT
);

Notes:

Types are schemaless and do not require explicit column definitions. However, if you prefer a structured approach, you may use:
- Property.declare() to explicitly declare single-valued properties.
- Property.has_many() to declare multi-valued properties.
Properties do not have a fixed data type, unlike columns in SQL tables. The same property may reference an integer for one object and to a float for another object.

Objects and Properties#

Define Objects from Rows in a Snowflake Table#

Use the model.Type() constructor with the source argument to define a type based on a Snowflake table:

## NOTE: `<db>`, `<schema>`, and `<table>` are placeholders for your database,
# schema, and table names, respectively.
Person = model.Type("Person", source="<db>.<schema>.<table>")

# If the table has a column that you intend to use as a multi-valued property,
# you must declare it as multi-valued using Property.has_many(). Otherwise, all
# columns are interpreted as single-valued properties. For example, your source
# data may have an 'email' column that you may want to use as a multi-valued
# property in your model to store multiple email addresses for the same person.
Person.email.has_many()

See Defining Objects from Rows in Snowflake Tables for details.

Define Objects in a Rule#

Use the Type.add() method to define new objects in a rule:

RAI

#with model.rule():
    Person.add(id=1)

SQL

#INSERT INTO Person (id) VALUES (1);

Properties defined in Type.add() serve as the object’s primary key. See Defining Objects in Rules for details.

Set a Single-Valued Property#

Use the Instance.set() method to set the value of a single-valued property for an object:

RAI

#with model.rule():
    person = Person.add(id=1)  # Person.add() returns an Instance object
    person.set(name="Bob", birthday="1994-03-14")

SQL

#-- Dynamically add the 'name' and 'birthday' columns to the 'Person' table.
ALTER TABLE Person
ADD COLUMN IF NOT EXISTS name VARCHAR(255),
ADD COLUMN IF NOT EXISTS birthday DATE;

-- Insert the new record with the 'birthday' property
INSERT INTO Person (id, name, birthday) VALUES (1, 'Bob', '1994-03-14');

See Single-Valued Properties for more information.

Set a Multi-Valued Property#

Use the InstanceProperty.extend() method to add values to a multi-valued property:

RAI

#with model.rule():
    person = Person.add(id=1)
    person.set(name="Bob", birthday="1994-03-14")
    # person.emails returns an InstanceProperty object. If the emails property
    # has never been set before, it is created by the .extend() method.
    person.emails.extend(["bob@example.com", "bob@company.com"])

SQL

#-- Multi-valued properties would be modeled using separate tables with a
-- foreign key to the main table.
CREATE TABLE IF NOT EXISTS PersonEmails (
    person_id INT,
    email VARCHAR(255),
    FOREIGN KEY (person_id) REFERENCES Person(id)
);

-- Insert the email addresses for the person with id=1
INSERT INTO PersonEmails (person_id, email) VALUES
(1, 'bob@example.com'),
(1, 'bob@company.com');

See Multi-Valued Properties for details.

Relationships#

Relationships are properties that link objects to other objects in the model. They may be defined based on foreign keys in the source data or created explicitly in rules.

Define a Relationship From a Foreign Key in Source Data#

Use the Type.define() method to define relationships based on foreign keys in your source data:

RAI

## Define types sourced from Snowflake tables
Employee = model.Type("Employee", source="<db>.<schema>.employees")
Department = model.Type("Department", source="<db>.<schema>.departments")

# Define the 'department' property on Employee, linking to Department via
# Employee.department_id -> Department.id
Employee.define(department=(Department, "department_id", "id"))

SQL

#CREATE TABLE Employee (
    id INT,
    name TEXT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

CREATE TABLE Department (
    id INT,
    name TEXT
);

By default, define() creates a single-valued property. To define a multi-valued property, use the Property.has_many() method:

RAI

#Employee = model.Type("Employee", source="<db>.<schema>.employees")
Department = model.Type("Department", source="<db>.<schema>.departments")

# Define a multi-valued relationship between Employee and Department
Employee.define(departments=(Department, "department_id", "id"))
Employee.departments.has_many()

SQL

#CREATE TABLE Employee (
    id INT,
    name TEXT
);

CREATE TABLE Department (
    id INT,
    name TEXT
);

-- Join table for the many-to-many relationship
CREATE TABLE EmployeeDepartments (
    employee_id INT,
    department_id INT,
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

Notes:

In the single-valued property case, each Employee object is associated with one Department object via the department_id foreign key in the employees table.
In the multi-valued property case, where an employee can belong to multiple departments, a join table EmployeeDepartment represents the many-to-many relationship.

Define a Relationship in a Rule#

You can define relationships between objects directly in rules by setting properties that link objects:

RAI

#with model.rule():
    # Create new Employee and Department objects
    employee = Employee.add(id=1, name="Alice")
    department = Department.add(id=10, name="Engineering")

    # Set the 'department' property of the employee to the department object
    employee.set(department=department)

SQL

#-- Insert department
ALTER TABLE Department
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT;
INSERT INTO Department (id, name) VALUES (10, 'Engineering');

-- Insert employee with department_id
ALTER TABLE Employee
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT,
ADD COLUMN IF NOT EXISTS department_id INT;
INSERT INTO Employee (id, name, department_id) VALUES (1, 'Alice', 10);

If an employee can belong to multiple departments, you can define a multi-valued relationship:

RAI

## Declare 'departments' as a multi-valued property
Employee.departments.has_many()

with model.rule():
    # Create Employee and Department objects
    employee = Employee.add(id=1, name="Bob")
    dept_engineering = Department.add(id=10, name="Engineering")
    dept_research = Department.add(id=20, name="Research")

    # Assign multiple departments to the employee
    employee.departments.extend([dept_engineering, dept_research])

SQL

#-- Insert departments
ALTER TABLE Department
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT;
INSERT INTO Department (id, name) VALUES (10, 'Engineering'), (20, 'Research');

-- Insert employee
ALTER TABLE Employee
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT;
INSERT INTO Employee (id, name) VALUES (1, 'Bob');

-- Insert into join table to represent the multi-valued relationship
CREATE TABLE IF NOT EXISTS EmployeeDepartments (
    employee_id INT,
    department_id INT,
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (department_id) REFERENCES Department(id)
);
INSERT INTO EmployeeDepartments (employee_id, department_id) VALUES
(1, 10),  -- Bob is in Engineering
(1, 20);  -- Bob is in Research

Notes:

In the single-valued relationship, the department property links an employee to one department.
In the multi-valued relationship, the departments property allows an employee to be associated with multiple departments.
The Employee.departments.has_many() declares departments as a multi-valued property.
The InstanceProperty.extend() method is used to add multiple departments to an employee.

You can perform joins by navigating relationships between objects using the dot (.) operator:

RAI

#with model.query() as select:
    employee = Employee()
    # employee.department.name accesses the name property of the related Department object.
    employee.department.name == "Sales"
    response = select(employee.name)

SQL

#SELECT e.name
FROM Employee e
JOIN Department d ON e.department_id = d.id
WHERE d.name = 'Sales';

For a multi-valued relationship, you can access related objects similarly:

RAI

#with model.query() as select:
    employee = Employee()
    employee.departments.name == "Engineering"  # Multi-valued property
    response = select(employee.name)

SQL

#SELECT e.name
FROM Employee e
JOIN EmployeeDepartment ed ON e.id = ed.employee_id
JOIN Department d ON ed.department_id = d.id
WHERE d.name = 'Engineering';

Rules and Queries#

Rules define objects, set properties, and establish relationships between objects. Queries retrieve information from the model based on the rules you’ve defined.

A Simple RAI Model and Query#

Both rules and queries are written using RAI’s Python query-builder syntax:

RAI

#import relationalai as rai

# 1. Create a model.
model = rai.Model("MyModel")

# 2. Define types.
Person = model.Type("Person")
Adult = model.Type("Adult")

# 3. Define a rule to create a new Person object.
with model.rule():
    person = Person.add(id=1)
    person.set(name="Alice", age=30)

# 4. Define a rule that assigns Person objects to the Adult type if their age
# property is greater than or equal to 18.
with model.rule():
    person = Person()
    person.age >= 18
    person.set(Adult)

# 5. Get the names and ages of all adults.
with model.query() as select:
    adult = Adult()
    response = select(adult.name, adult.age)

SQL

#-- 1. Create a schema and switch to it.
CREATE SCHEMA IF NOT EXISTS MyModel;
USE SCHEMA MyModel;

-- 2. Create tables for Person and Adult types.
CREATE TABLE IF NOT EXISTS Person;
CREATE TABLE IF NOT EXISTS Adult;

-- 3. Add columns to the Person table and insert a record.
ALTER TABLE Person
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name VARCHAR,
ADD COLUMN IF NOT EXISTS age INT;
INSERT INTO Person (id, name, age) VALUES (1, 'Alice', 30);

-- 4. Insert records into the Adult table corresponding to Person records
-- with age >= 18.
ALTER TABLE Adult
ADD COLUMN IF NOT EXISTS id INT;
INSERT INTO Adult (id) SELECT id FROM Person WHERE age >= 18;

-- 5. Get the names and ages of all adults.
SELECT name, age
FROM Person p, Adult a
WHERE p.id = a.id;

Notes:

Rules and queries are defined using the model.rule() and model.query() context managers, respectively.
Unlike SQL, which executes each command immediately, in RAI, rules are not executed until a query is run.
Rules are not executed in any particular order, and can be thought of as a set of constraints that are applied to the data when a query is executed.

See Executing Queries and Capturing Knowledge in Rules for more information.

Change Result Column Names#

Use the alias() function to customize the names of columns in the query results:

RAI

#from relationalai.std import alias

with model.query() as select:
    person = Person()
    response = select(alias(person.name, "full_name"))

SQL

#SELECT name AS full_name FROM Person;

Change the Result Format#

By default, RAI queries return a pandas DataFrame. Set format="snowpark" in the query to return a Snowpark DataFrame instead:

#with model.query(format="snowpark") as select:
    person = Person()
    response = select(person.name)

Sort Results (ORDER BY)#

To sort objects or values within a query, use the rank_asc() and rank_desc() functions to compute the rank of each object and include the rank as the first column in the query results:

RAI

#from relationalai.std import aggregates

with model.query() as select:
    person = Person()
    rank = aggregates.rank_asc(person.age)
    rank <= 10  # Filter results where rank is at most 10
    response = select(rank, person.name, person.age)

SQL

#WITH ranked_persons AS (
    SELECT
        RANK() OVER (ORDER BY age ASC) AS rank,
        name,
        age
    FROM Person
)
SELECT rank, name, age
FROM ranked_persons
WHERE rank <= 10;

Both .rank_asc() and .rank_desc() work similarly to SQL’s RANK() function. The computed rank can be assigned to a variable and used in additional filters or aggregations.

As an alternative, you can also sort results after the query is executed using the pandas .sort_values() method or the Snowpark .sort() method, depending on the format of the query results:

RAI

## pandas format
with model.query() as select:
    person = Person()
    response = select(person.name, person.age)

response.results.sort_values(by='age', inplace=True)

# Snowpark format
with model.query(format="snowpark") as select:
    person = Person()
    response = select(person.name, person.age)

response.results.sort('age')

SQL

#SELECT name, age FROM Person ORDER BY age;

TIP

In general, it is more efficient to sort results using pandas and Snowpark DataFrame methods after the query is executed. Consider using the rank_asc() and rank_desc() functions only when you need to use the rank in subsequent operations.

Limit Results (LIMIT)#

Use the top() function to limit the number of results returned by a query:

RAI

#from relationalai.std import aggregates

with model.query() as select:
    person = Person()
    # Limit the results to 10 people.
    aggregates.top(10, person)
    response = select(person.name, person.age)

SQL

#SELECT name, age FROM Person LIMIT 10;

top() can be used to both rank results in descending order and limit the number of results:

RAI

#with model.query() as select:
    person = Person()
    # Rank results in descending order by age and limit to the top 10 ages.
    rank = aggregates.top(10, person.age)
    response = select(rank, person.name, person.age)

SQL

#WITH ranked_persons AS (
    SELECT
        RANK() OVER (ORDER BY age DESC) AS rank,
        name,
        age
    FROM Person
)
SELECT rank, name, age
FROM ranked_persons
WHERE rank <= 10;

Use bottom() to rank results in ascending order and limit the number of results:

RAI

#with model.query() as select:
    person = Person()
    # Rank results in ascending order by age and limit to the 10 smallest ages.
    rank = aggregates.bottom(10, person.age)
    response = select(rank, person.name, person.age)

SQL

#WITH ranked_persons AS (
    SELECT
        RANK() OVER (ORDER BY age ASC) AS rank,
        name,
        age
    FROM Person
)
SELECT rank, name, age
FROM ranked_persons
WHERE rank <= 10;

Filter Data (WHERE)#

Conditions in rules and queries are added line-by-line and implicitly combined using AND:

RAI

#from relationalai.std.dates import date

with model.query() as select:
    employee = Employee()
    # Filter employees who started after January 1, 2021
    employee.start_date > date(2021, 1, 1)
    # Filter employees in the Engineering or Research departments
    employee.department.in_([Department(name="Engineering"), Department(name="Research")])
    response = select(employee.id, employee.name)

SQL

#SELECT e.id, e.name
FROM Employee e
JOIN Department d ON e.department_id = d.id
WHERE e.start_date > '2021-01-01'
  AND d.name IN ('Engineering', 'Research');

Filter expressions are created using the following operators:

Operator	Description
`==`	Equal to
`!=`	Not equal to
`>`	Greater than
`>=`	Greater than or equal to
`<`	Less than
`<=`	Less than or equal to
`.in_()`	In a list of values

See Filtering Objects by Property Value for more details.

Python’s logical operators, like and, or, and not, expect boolean operands. However, RAI filter expressions, like employee.start_date > date(2021, 1, 1), do not return boolean values, since their truth value is only determined once the query or rule is evaluated. For this reason, you cannot use Python’s logical operators to combine filter expressions in RAI.

To express or in RAI, use the model.match(multiple=True) context manager:

RAI

#with model.query() as select:
    employee = Employee()
    # Filter employees who started before January 1, 2020 or after January 1, 2021
    with model.match(multiple=True) as matched:
        with employee.start_date < date(2020, 1, 1):
            matched.add(employee)
        with employee.start_date > date(2021, 1, 1):
            matched.add(employee)
    response = select(matched.id, matched.name)

SQL

#-- model.match(multiple=True) is equivalent to using UNION ALL in SQL to combine
-- the results of multiple queries.
SELECT id, name
FROM Employee
WHERE start_date < '2020-01-01'
UNION ALL
SELECT id, name
FROM Employee
WHERE start_date > '2021-01-01';

-- Logically, this is equivalent to using OR.
SELECT id, name
FROM Employee
WHERE start_date < '2020-01-01' OR start_date > '2021-01-01';

See Conditional Logic for more information on model.match().

Check for Existence (EXISTS)#

You can check for the existence or non-existence of certain conditions or related objects in RAI using model.found() and model.not_found(), similar to SQL’s EXISTS and NOT EXISTS clauses:

RAI

## Get the names of employees who belong to the Research department, and the
# names of other departments they belong to.
with model.query() as select:
    employee = Employee()
    # Check if the employee is in the Research department
    with model.found():
        employee.departments.name == "Research"
    # Retrieve other departments the employee belongs to
    other_department = employee.departments
    other_department.name != "Research"
    response = select(employee.name, other_department.name)

SQL

#SELECT e.name, d_other.name
FROM Employee e
JOIN EmployeeDepartments ed1 ON e.id = ed1.employee_id
JOIN Department d1 ON ed1.department_id = d1.id
JOIN EmployeeDepartments ed2 ON e.id = ed2.employee_id
JOIN Department d_other ON ed2.department_id = d_other.id
WHERE EXISTS (
    SELECT 1
    FROM EmployeeDepartments ed
    JOIN Department d ON ed.department_id = d.id
    WHERE ed.employee_id = e.id AND d.name = 'Research'
)
AND d_other.name != 'Research';

RAI

## Get the names of employees who do NOT belong to the Research department, and
# the names of departments they belong to.
with model.query() as select:
    employee = Employee()
    # Check if the employee is NOT in the Research department
    with model.not_found():
        employee.departments.name == "Research"
    # Retrieve departments the employee belongs to
    department = employee.departments
    response = select(employee.name, department.name)

SQL

#SELECT e.name, d.name
FROM Employee e
JOIN EmployeeDepartments ed ON e.id = ed.employee_id
JOIN Department d ON ed.department_id = d.id
WHERE NOT EXISTS (
    SELECT 1
    FROM EmployeeDepartments ed2
    JOIN Department d2 ON ed2.department_id = d2.id
    WHERE ed2.employee_id = e.id AND d2.name = 'Research'
);

Aggregate and Group Data (GROUP BY)#

Use the functions in the aggregates module to perform aggregations in queries. Unlike SQL, where aggregate function operate over each row in a column, RAI aggregate functions operate on the set of values assigned to a property. If multiple objects have the same value property, it only contributes once to the aggregation.

RAI

#from relationalai.std import aggregates, alias

with model.query() as select:
    sale = Sale()
    # WARNING: The following may not work as you expect in RAI. This sums all
    # *distinct* amounts across all sales, rather than all amounts.
    total_sales = aggregates.sum(sale.amount)
    response = select(alias(total_sales, "total_sales"))

SQL

#SELECT SUM(DISTINCT amount) AS total_sales
FROM Sale;

RAI

## To sum all amounts, including duplicates, include the sale variable in the sum function:
with model.query() as select:
    sale = Sale()
    # Sum the amounts for each sale object.
    total_sales = aggregates.sum(sale, sale.amount)
    response = select(alias(total_sales, "total_sales"))

SQL

#SELECT SUM(amount) AS total_sales
FROM Sale;

Passing the sale object as the first argument to aggregates.sum() allows RAI to aggregate over (sale, sale.amount) pairs instead of just the sale.amount values. When multiple arguments are provided to an aggregate function like .sum(), the aggregation is performed on the last argument.

Use the per parameter to group results in an aggregation:

RAI

#with model.query() as select:
    salesperson = Salesperson()
    sale = salesperson.sales
    total = aggregates.sum(sale, sale.amount, per=[salesperson])
    response = select(salesperson.name, total)

SQL

#SELECT s.name, SUM(sa.amount) AS total
FROM Salesperson s
JOIN Sales sa ON sa.salesperson_id = s.id
GROUP BY s.id, s.name;

Additional filters on results of aggregations can be included in the query:

RAI

#with model.query() as select:
    salesperson = Salesperson()
    sale = salesperson.sales
    count = aggregates.count(sale, per=[salesperson])
    count > 10  # Filter salespeople with more than 10 sales
    response = select(salesperson.name, alias(count, "count"))

SQL

#SELECT s.name, COUNT(*) AS count
FROM Salesperson s
JOIN Sales sa ON sa.salesperson_id = s.id
GROUP BY s.id, s.name
HAVING COUNT(*) > 10;

Filters are applied in the order they are written in the query and only affect aggregations that follow them:

RAI

#from relationalai.std import aggregates

with model.query() as select:
    sale = Sale()
    # First aggregation: Compute total sales amount across all sales.
    total_sales = aggregates.sum(sale, sale.amount)
    # Apply a filter after the first aggregation.
    sale.amount > 1000
    # Second aggregation: Count the number of large sales.
    count_large_sales = aggregates.count(sale)
    response = select(
        alias(total_sales, "total_sales"),
        alias(count_large_sales, "count_large_sales"),
    )

SQL

#-- Compute total sales amount across all sales
WITH total_sales AS (
    SELECT SUM(amount) AS total_sales
    FROM Sale
),
-- Filter sales with amount > 1000 and count them
count_large_sales AS (
    SELECT COUNT(*) AS count_large_sales
    FROM Sale
    WHERE amount > 1000
)
-- Select both results
SELECT total_sales.total_sales, count_large_sales.count_large_sales
FROM total_sales, count_large_sales;

See Using Aggregate Functions for more details.

Conditional Logic (CASE-WHEN)#

You can implement conditional logic in RAI using the model.match() and model.case() context managers, similar to SQL’s CASE statement:

RAI

#with model.query() as select:
    student = Student()
    with model.match() as letter_grade:  # letter_grade is a variable
        with student.grade >= 90:
            letter_grade.add("A")  # Set the value of letter_grade to "A"
        with student.grade >= 80:
            letter_grade.add("B")
        with student.grade >= 70:
            letter_grade.add("C")
        with student.grade >= 60:
            letter_grade.add("D")
        # Default case if none of the above conditions are met.
        with model.case():
            letter_grade.add("F")
    response = select(student.name, alias(letter_grade, "letter_grade"))

SQL

#SELECT
    name,
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM
    Student;

For each object in a rule or query, model.match() applies the first condition that evaluates to True and ignores the rest. You can use model.match(multiple=True) to apply multiple conditions. In a query, this works like a UNION ALL in SQL:

RAI

#with model.query() as select:
    person = Person()
    # Assign status based on age group. multiple=True allows multiple matches,
    # so people who are over 60 will have status set to both "Senior" and "Adult".
    with model.match(multiple=True) as status:
        with model.case():
            person.age >= 60
            status.add("Senior")
        with model.case():
            person.age >= 18
            status.add("Adult")
        with model.case():
            person.age < 18
            status.add("Minor")
    response = select(person.name, status)

SQL

#SELECT name, 'Senior' AS status
FROM Person
WHERE age >= 60
UNION ALL
SELECT name, 'Adult' AS status
FROM Person
WHERE age >= 18
UNION ALL
SELECT name, 'Minor' AS status
FROM Person
WHERE age < 18;

In a rule, model.match() can be used to conditionally set types and properties of objects:

RAI

#with model.rule():
    person = Person()
    with model.match():
        # If the person's age is at least 18, assign them to the Adult type.
        with person.age >= 18:
            person.set(Adult)
        # Otherwise, assign them to the Minor type.
        with model.case():
            person.set(Minor)

SQL

#-- Insert persons aged 18 or older into the Adult table
INSERT INTO Adult (person_id)
SELECT id
FROM Person
WHERE age >= 18;

-- Insert persons not in the Adult table into the Minor table
INSERT INTO Minor (person_id)
SELECT p.id
FROM Person p
WHERE NOT EXISTS (
    SELECT 1
    FROM Adult a
    WHERE a.person_id = p.id
);

RAI

## Using multiple=True
with model.rule():
    person = Person()
    with model.match(multiple=True):
        with person.age >= 60:
            person.set(Senior)
        with person.age >= 18:
            person.set(Adult)
        with person.age < 18:
            person.set(Minor)

SQL

#-- Insert 'Senior' classifications
INSERT INTO Senior (person_id)
SELECT id
FROM Person
WHERE age >= 60;

-- Insert 'Adult' classifications
INSERT INTO Adult (person_id)
SELECT id
FROM Person
WHERE age >= 18;

-- Insert 'Minor' classifications
INSERT INTO Minor (person_id)
SELECT id
FROM Person
WHERE age < 18;

See Expressing if Using model.case() and Expressing if-else Using model.match() for more examples of conditional logic in RAI.

Missing Values#

RAI does not have a NULL value. Instead, property values are either present or missing. You can check for missing values and provide default values in RAI, similar to SQL’s IS NULL, IS NOT NULL, and COALESCE.

INFO

Properties that are not set may appear as NULL or NaN in query results. See Dealing With Null Values for more details.

Check for Value (IS NOT NULL)#

Use the InstanceProperty.has_value() method to check that a property is not missing:

RAI

#with model.query() as select:
    person = Person()
    person.age.has_value()
    response = select(person.name)

SQL

#SELECT name FROM Person WHERE age IS NOT NULL;

Check for Missing Value (IS NULL)#

Use model.not_found() with InstanceProperty.has_value() to check for missing values:

RAI

#with model.query() as select:
    person = Person()
    with model.not_found():
        person.age.has_value()
    response = select(person.name)

SQL

#SELECT name FROM Person WHERE age IS NULL;

Provide a Default Value (COALESCE)#

Use the InstanceProperty.or_() method to provide a default value for a property when it is missing:

RAI

#with model.query() as select:
    person = Person()
    response = select(person.name, person.age.or_(0))

SQL

#SELECT name, COALESCE(age, 0) AS age FROM Person;

Stored Procedures#

You can export Python functions as SQL stored procedures using the @model.export() decorator.

RAI

#from typing import Tuple

@model.export("<db>.<schema>")
def get_friends_of_person(id: int) -> Tuple[str]:
    person = Person(id=id)
    friend = person.friends
    return friend.name

SQL

#-- NOTE: Replace <db> and <schema> with the names of the database and schema where
-- the stored procedure will be created. The stored procedure name will be the same
-- as the function name.
CREATE FUNCTION get_friends_of_person(id INT)
RETURNS TABLE(name VARCHAR)
AS
BEGIN
    -- Function logic here
END;

See Exporting SQL Stored Procedures for more details.

Resources#

RAI Documentation
Examples
- Example Notebooks

RAI Python to SQL Comparison#

Table of Contents#

Core Concepts#

Key Differences#

Schemaless Types vs. Fixed Schemas#

Flexible Data Types vs. Fixed Data Types#

Multi-Valued Properties vs. Many-to-Many Relationship Tables#

Multiple Type Membership vs. Single Table Membership#

Immutable Data vs. Mutable Data with Direct Updates#

Presence-Based Values vs. Null Values#

Set-Based Aggregations vs. Row-Based Aggregations#

Models#

Types#

Objects and Properties#

Define Objects from Rows in a Snowflake Table#

Define Objects in a Rule#

Set a Single-Valued Property#

Set a Multi-Valued Property#

Relationships#

Define a Relationship From a Foreign Key in Source Data#

Define a Relationship in a Rule#

Access Related Objects#

Rules and Queries#

A Simple RAI Model and Query#

Change Result Column Names#

Change the Result Format#

Sort Results (ORDER BY)#

Limit Results (LIMIT)#

Filter Data (WHERE)#

Check for Existence (EXISTS)#

Aggregate and Group Data (GROUP BY)#

Conditional Logic (CASE-WHEN)#

Missing Values#

Check for Value (IS NOT NULL)#

Check for Missing Value (IS NULL)#

Provide a Default Value (COALESCE)#

Stored Procedures#

Resources#