FeatureQL for the Impatient

This guide helps SQL practitioners understand FeatureQL by comparing it with familiar SQL concepts.

Core concepts

Features are functions

In SQL, we create one table from other tables. In FeatureQL, we define features as formulas based on other features.

Unlike tables, when we persist features with CREATE FEATURES AS, no data is evaluated. Only their definition is stored in the feature registry, similar to a view or function in SQL.

Think of features as database functions with automatic parameter detection. When we reference other features, they automatically become inputs to our formula.

Features are pure functions: their output depends only on their inputs. The exception is EXTERNAL functions, which fetch external data.

We can evaluate features by binding values to their inputs using the BIND functions.

Simple example step by step

Let's create a customer segment.

First, the minimal setup:

Note: We define in the WITH section the features that we do not want to return in the result set.

Now let's create some mockup external data:

We can now map features to existing table columns by using the EXTERNAL_SQL function:

Then finally add our business logic:

Feature organization

Catalog vs local definitions

Catalog features (persisted globally):

• Defined with the CREATE FEATURES AS statement
• Available everywhere in the organization
• Namespaced (e.g., fm.finance.revenue, fm.marketing.campaign_roi)
• Have granular permissions

Local features (in WITH or SELECT clause):

• Defined in our query
• Override catalog features if same name
• Useful for prototyping

In practice, core features like CUSTOMER_ID, CUSTOMER_LAST_LOGIN_DATE have been created in the catalog by the data team with this:

So end-users can just write:

Entities and INPUT relationship

ENTITY() creates a semantic label for a data domain. INPUT() parameters can reference entities by type annotation:

CUSTOMERS := ENTITY(),
CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),  -- CUSTOMER_ID references the CUSTOMERS entity as a BIGINT key

This clarify the nature of features, prevents mixing incompatible features (e.g., accidentally joining customer features with product IDs) and allow automated binding with ADD_FIELDS().

Working with features

Type system

• Automatic type inference: Types are inferred for pure transformations
• Explicit types at boundaries: Only INPUT() and EXTERNAL_XXX() need explicit type declarations
• No coercion: Cast explicitly when needed with ::TYPE or CAST(x AS TYPE)
• Advanced types: Use ARRAY, ROW and ARRAY(ROW(...)) all the time

Metaprogramming

@echo and @literal compute values at compilation time.

This is useful for computed literal parameters without having to use external ways of generating text such as jinja templates.

QUERY := @literal(`SELECT * FROM dim_customers WHERE date = '` || CURRENT_DATE() || `'`),

Hybrid queries

Mix SQL and FeatureQL to integrate with existing SQL based clients.

Array of rows operations

FeatureQL promotes ECM (Entity Centric Modeling) that stores facts as arrays or arrays of rows within each dimension table (also known as One Big Table modeling).

FeatureQL has then strong support for ARRAY and ARRAY of ROWs that are the core data structures of ECM.

More details on Arrays of rows patterns .

Operations on fields

Note: Operations on fields are similar to operations on ROW except for ZIP and TRANSFORM.

Operations on rows

Note: Operations on rows are similar to operations on ARRAY.

Transform operations

You can apply a FeatureQL query to any array of rows as if it was a table (equivalent to a correlated subquery in SQL) using the TRANSFORM function.

This query can:

• SELECT: generate new fields from existing fields
• WHERE: filter pre or post aggregation
• GROUP BY: aggregate metrics on one or more fields
• ORDER BY: sort by any post-aggregation field
• LIMIT/OFFSET: limit the post-aggregation result

The query must be self-contained (no external data references) and use only pure transformations. If you need external data, add it to your array first with ARRAY_MERGE() or ADD_FIELDS() before applying TRANSFORM(). You can also chain multiple TRANSFORM functions to apply transformations sequentially.

SQL equivalent comparison

Main patterns

Key differences from SQL

1. Primary key thinking: INPUT() represents your row identifier (like a primary key)
2. No implicit behavior: No NULL coalescing, no type coercion
3. Dependency tracking: Reference a feature = automatic dependency
4. Nested data model: Arrays of rows instead of normalized tables
5. Functional purity: Features are formulas, not stored data

Advanced patterns

Historical data and time travel

• SCD_AT_TIME(): Get the state of a slowly changing dimension (SCD type 2) at a certain point in time
• ACCUMULATION_AT_TIME(): Get the state of an accumulation of facts at a certain point in time

Testing and reusability

• VARIANT(): Create feature variations (e.g., test different thresholds, swap sources, etc.)
• MACRO(): Define reusable functions in FeatureQL
• UDF(): Define reusable functions in the language of the backend database

Developer tools

SHOW FEATURES;                    -- Browse catalog
SHOW CREATE FEATURES <name>;      -- See definition
SHOW FUNCTION <function>;         -- Get function docs
SHOW DOCS;                        -- Browse documentation