LLMs modeling

EXTERNAL_SQL(
  'SELECT customer_email, customer_country, CAST(customer_date as DATE) as customer_created_at FROM customers', -- Note the optional casting and alias to match the mapped feature
  ON `SELF.customer_id = %CUSTOMER_ID`, -- Note the binding of the primary key column to the INPUT feature `CUSTOMER_ID` representing the entity `CUSTOMERS`
  AS ROW(customer_email VARCHAR, customer_country VARCHAR#COUNTRIES, customer_created_at DATE)
),

-- Define entities (always generic)
CUSTOMERS := ENTITY(),
PRODUCTS := ENTITY(),

-- Define inputs (always generic)
CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),
PRODUCT_ID := INPUT(BIGINT#PRODUCTS),

-- External data (generic, no filters)
CUSTOMER_DETAILS := EXTERNAL_SQL(
  'SELECT customer_id, name, email FROM customers',
  AS ROW(customer_id BIGINT, name VARCHAR, email VARCHAR)
),
CUSTOMER_NAME := CUSTOMER_DETAILS[name],

-- Grouped relationships (generic)
CUSTOMER_ORDER_IDS := EXTERNAL_SQL(
  `SELECT customer_id, ARRAY_AGG(order_id) as order_ids
   FROM orders GROUP BY customer_id`,
  AS ROW(customer_id BIGINT, order_ids ARRAY(BIGINT#ORDERS))
)[order_ids],

-- At ORDER level
ORDER_DATE := EXTERNAL_SQL(...)[order_date],
IS_JULY_2017 := ORDER_DATE >= '2017-07-01' AND ORDER_DATE < '2017-08-01',

-- At PRODUCT level
PRODUCT_NAME := EXTERNAL_SQL(...)[product_name],
IS_REFERENCE_PRODUCT := PRODUCT_NAME = 'Target Product',

-- ORDER_ITEMS is already ARRAY(ROW(product_id, quantity))
-- Access field directly:
PRODUCT_IDS := ORDER_ITEMS[product_id],

-- If nested deeper:
-- ORDERS has array, each order has ORDER_ITEMS which has array
ALL_PRODUCT_IDS := CUSTOMER_ORDERS[ORDER_ITEMS][product_id],

-- CUSTOMER has array of order_ids called CUSTOMER_ORDER_IDS of type ARRAY(BIGINT#ORDERS)
-- Enrich with order-level features (automatic join on order_id)
CUSTOMER_ORDERS_ENRICHED := EXTEND(
  ZIP(CUSTOMER_ORDER_IDS as order_id)  -- Transform array into array of rows with an order_id field
  WITH ORDER_DATE, ORDER_AMOUNT, PRODUCT_IDS  -- Features at ORDER level
  VIA order_id BOUND TO ORDER_ID   -- Join condition (optional as order_id is already annotated with ORDER_ID)
),

-- If you already have an array of rows, no ROW() needed:
-- ORDER_ITEMS is ARRAY(ROW(product_id, quantity))
ORDER_ITEMS_ENRICHED := EXTEND(
  ORDER_ITEMS
  WITH PRODUCT_NAME, PRODUCT_PRICE  -- Features at PRODUCT level
  VIA product_id BOUND TO PRODUCT_ID
),

-- Filter array
JULY_ORDERS := CUSTOMER_ORDERS_ENRICHED.TRANSFORM(
  SELECT * WHERE ORDER_DATE BETWEEN '2017-07-01' AND '2017-07-31'
),

-- Aggregate on all rows
TOTAL_SPENT := CUSTOMER_ORDERS_ENRICHED.TRANSFORM(
  SELECT SUM(ORDER_AMOUNT)
),

-- Check existence of a product in the customer's order items
HAS_BOUGHT_REFERENCE := CUSTOMER_ITEMS_ENRICHED.TRANSFORM(
  SELECT BOOL_OR(CONTAINS(PRODUCT_IDS, PRODUCT_ID))
),

-- Aggregate by any field or derived field
TOTAL_SPENT_BY_MONTH := CUSTOMER_ORDERS_ENRICHED.TRANSFORM(
  WITH ORDER_MONTH := EXTRACT(MONTH FROM ORDER_DATE)
  SELECT
    ORDER_MONTH,
    -- Note the GROUP BY as part of the feature definition (contrarily to SQL where the GROUP BY is part of the query)
    COUNT(1) GROUP BY ORDER_MONTH AS total_orders_by_month,
    SUM(ORDER_AMOUNT) GROUP BY ORDER_MONTH AS total_spent_by_month
  ORDER BY ORDER_MONTH ASC
),

-- Get distinct values
UNIQUE_PRODUCT_IDS := CUSTOMER_ITEMS_ENRICHED.TRANSFORM(
  SELECT DISTINCT order_customer_id
),

-- WRONG
JULY_ORDERS := EXTERNAL_SQL(
  'SELECT * FROM orders WHERE order_date >= ''2017-07-01''',
  ...
)

-- RIGHT
ALL_ORDERS := EXTERNAL_SQL('SELECT * FROM orders', ...),
JULY_ORDERS := ALL_ORDERS.TRANSFORM(SELECT * WHERE ORDER_DATE >= '2017-07-01'),

-- WRONG: ORDER_ITEMS is already ARRAY(ROW(...))
EXTEND(
  ROW(product_id := ORDER_ITEMS)  -- Unnecessary ROW()
  WITH PRODUCT_NAME
  VIA product_id BOUND TO PRODUCT_ID
)

-- RIGHT: Converting scalar array to array of rows
EXTEND(
  ROW(order_id := CUSTOMER_ORDER_IDS)  -- CUSTOMER_ORDER_IDS is ARRAY(BIGINT)
  WITH ORDER_DATE
  VIA order_id BOUND TO ORDER_ID
)

-- RIGHT: Already array of rows
EXTEND(
  ORDER_ITEMS  -- ORDER_ITEMS is ARRAY(ROW(product_id, quantity))
  WITH PRODUCT_NAME
  VIA product_id BOUND TO PRODUCT_ID
)

-- If you need to count something at PRODUCT level based on CUSTOMER qualification:
-- Don't create intermediate flattening/unnesting steps

-- Get customers per product, enrich with customer features, aggregate
PRODUCT_CUSTOMER_IDS := EXTERNAL_SQL(...)  -- Array of customer_ids
CUSTOMERS_ENRICHED := EXTEND(ROW(...) WITH HAS_QUALIFICATION),
COUNT := CUSTOMERS_ENRICHED.TRANSFORM(SELECT COUNT(*) WHERE HAS_QUALIFICATION),

WITH
  -- Entity definitions
  -- Input definitions
  -- External data (generic, reusable)
  -- Filter features
  -- Derived features (composition, enrichment, transformation)
SELECT
  PRIMARY_KEY := BIND_VALUES(...) or BIND_SQL(...),
  feature1,
  feature2...

WITH
  -- Same as feature engineering
SELECT
  PRIMARY_KEY := BIND_VALUES(...) or BIND_SQL(...),
  feature1,
  feature2
WHERE condition
ORDER BY feature1, feature2...
OFFSET n
LIMIT n;