The ML.FEATURES_AT_TIME function

This document describes the ML.FEATURES_AT_TIME function, which lets you use a point-in-time cutoff for all entities when retrieving features, because features can have time dependencies if they include time-sensitive data. To avoid data leakage , use point-in-time features when training models and running inference.

Use this function to use the same point-in-time cutoff for all entities when retrieving features. Use the ML.ENTITY_FEATURES_AT_TIME function to retrieve features from multiple points in time for multiple entities.

Syntax

ML.FEATURES_AT_TIME(
   { TABLE ` PROJECT_ID 
. DATASET 
. TABLE_NAME 
` | ( QUERY_STATEMENT 
) }
   [, TIME 
=> TIMESTAMP][, NUM_ROWS 
=> INT64][, IGNORE_FEATURE_NULLS 
=> BOOL])

Arguments

ML.FEATURES_AT_TIME takes the following arguments:

• PROJECT_ID : the project that contains the table.
• DATASET : the BigQuery dataset that contains the table.

• TABLE_NAME : is the name of the BigQuery table that contains the feature data. The feature table must contain the following columns:

  • entity_id : a STRING column that contains the ID of the entity related to the features.
  • One or more feature columns.
  • feature_timestamp : a TIMESTAMP column that identifies when the feature data was last updated.

  The column names are case-insensitive. For example, you can use a column named Entity_ID instead of entity_id .

  The feature table must be in wide format, with one column for each feature.

• QUERY_STATEMENT : a STRING value that specifies a GoogleSQL query that returns the feature data. This query must return the same columns as the TABLE_NAME argument. See GoogleSQL query syntax for the supported SQL syntax of the QUERY_STATEMENT clause.

• TIME : a TIMESTAMP value that specifies the point in time to use as a cutoff for feature data. Only rows where the value in the feature_timestamp column is equal to or earlier than the TIME value are returned. Defaults to the value of the CURRENT_TIMESTAMP function .

• NUM_ROWS : an INT64 value that specifies the number of rows to return for each entity ID. Defaults to 1 .

• IGNORE_FEATURE_NULLS : a BOOL value that indicates whether to replace a NULL value in a feature column with the feature column value from the row for the same entity that immediately precedes it in time. For example, for the following feature table:

  +-----------+------+------+--------------------------+
  | entity_id | f1   | f2   | feature_timestamp        |
  +-----------+------+------+--------------------------+
  | '2'       | 5.0  | 8.0  | '2022-06-10 09:00:00+00' |
  +-----------+------+------+--------------------------+
  | '2'       | 2.0  | 4.0  | '2022-06-10 12:00:00+00' |
  +-----------+------+------+--------------------------+
  | '2'       | 7.0  | NULL | '2022-06-11 10:00:00+00' |
  +-----------+------+------+--------------------------+

  Running this query:

   SELECT 
    
   * 
   FROM 
    
   ML 
   . 
   FEATURES_AT_TIME 
   ( 
    
   TABLE 
    
   mydataset 
   . 
   feature_table 
   , 
    
   time 
    
   => 
    
   '2022-06-11 10:00:00+00' 
   , 
    
   num_rows 
    
   => 
    
   1 
   , 
    
   ignore_feature_nulls 
    
   => 
    
   TRUE 
   ); 
  

  Results in the following output, where the f2 value from the row for entity ID 2 that is timestamped '2022-06-10 12:00:00+00' is substituted for the NULL value in the row timestamped '2022-06-11 10:00:00+00' :

  +-----------+------+------+--------------------------+
  | entity_id | f1   | f2   | feature_timestamp        |
  +-----------+------+------+--------------------------+
  | '2'       | 7.0  | 4.0  | '2022-06-11 10:00:00+00' |
  +-----------+------+------+--------------------------+

  If there is no available replacement value, for example, where there is no earlier row for that entity ID, a NULL value is returned.

  Defaults to FALSE .

Output

The ML.FEATURES_AT_TIME function returns the input table rows that meet the point-in-time cutoff criteria, with the feature_timestamp column showing the timestamp that was input in the time argument.

Examples

Example 1

This example shows a how to retrain a model using only features that were created or updated before 2023-01-01 12:00:00+00 :

 CREATE 
  
 OR 
  
 REPLACE 
  
 ` 
 mydataset 
 . 
 mymodel 
 ` 
  
 OPTIONS 
  
 ( 
 WARM_START 
  
 = 
  
 TRUE 
 ) 
 AS 
 SELECT 
  
 * 
  
 EXCEPT 
  
 ( 
 feature_timestamp 
 , 
  
 entity_id 
 ) 
 FROM 
  
 ML 
 . 
 FEATURES_AT_TIME 
 ( 
  
 TABLE 
  
 ` 
 mydataset 
 . 
 feature_table 
 ` 
 , 
  
 time 
  
 => 
  
 '2023-01-01 12:00:00+00' 
 , 
  
 num_rows 
  
 => 
  
 1 
 , 
  
 ignore_feature_nulls 
  
 => 
  
 TRUE 
 ); 

Example 2

This example shows how to get predictions from a model based on features that were created or updated before 2023-01-01 12:00:00+00 :

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 PREDICT 
 ( 
  
 MODEL 
  
 ` 
 mydataset 
 . 
 mymodel 
 ` 
 , 
  
 ( 
  
 SELECT 
  
 * 
  
 EXCEPT 
  
 ( 
 feature_timestamp 
 , 
  
 entity_id 
 ) 
  
 FROM 
  
 ML 
 . 
 FEATURES_AT_TIME 
 ( 
  
 TABLE 
  
 ` 
 mydataset 
 . 
 feature_table 
 ` 
 , 
  
 time 
  
 => 
  
 '2023-01-01 12:00:00+00' 
 , 
  
 num_rows 
  
 => 
  
 1 
 , 
  
 ignore_feature_nulls 
  
 => 
  
 TRUE 
 ) 
  
 ) 
  
 ); 

Example 3

This is a contrived example that you can use to see the output of the function:

 WITH 
  
 feature_table 
  
 AS 
  
 ( 
  
 SELECT 
  
 * 
  
 FROM 
  
 UNNEST 
 ( 
  
 ARRAY<STRUCT<entity_id 
  
 STRING 
 , 
  
 f_1 
  
 FLOAT64 
 , 
  
 f_2 
  
 FLOAT64 
 , 
  
 feature_timestamp 
  
 TIMESTAMP 
 >> 
 [ 
  
 ( 
 'id1' 
 , 
  
 1 
 . 
 0 
 , 
  
 1 
 . 
 0 
 , 
  
 TIMESTAMP 
  
 '2022-06-10 12:00:00+00' 
 ), 
  
 ( 
 'id2' 
 , 
  
 12 
 . 
 0 
 , 
  
 24 
 . 
 0 
 , 
  
 TIMESTAMP 
  
 '2022-06-11 12:00:00+00' 
 ), 
  
 ( 
 'id1' 
 , 
  
 11 
 . 
 0 
 , 
  
 NULL 
 , 
  
 TIMESTAMP 
  
 '2022-06-11 12:00:00+00' 
 ), 
  
 ( 
 'id1' 
 , 
  
 6 
 . 
 0 
 , 
  
 12 
 . 
 0 
 , 
  
 TIMESTAMP 
  
 '2022-06-11 10:00:00+00' 
 ), 
  
 ( 
 'id2' 
 , 
  
 2 
 . 
 0 
 , 
  
 4 
 . 
 0 
 , 
  
 TIMESTAMP 
  
 '2022-06-10 12:00:00+00' 
 ), 
  
 ( 
 'id2' 
 , 
  
 7 
 . 
 0 
 , 
  
 NULL 
 , 
  
 TIMESTAMP 
  
 '2022-06-11 10:00:00+00' 
 )]) 
  
 ) 
 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 FEATURES_AT_TIME 
 ( 
  
 TABLE 
  
 feature_table 
 , 
  
 time 
  
 => 
  
 '2022-06-12 10:00:00+00' 
 , 
  
 num_rows 
  
 => 
  
 1 
 , 
  
 ignore_feature_nulls 
  
 => 
  
 TRUE 
 );