The ML.FEATURES_AT_TIME function

This document describes theML.FEATURES_AT_TIME function, which lets you usea point-in-time cutoff for all entities when retrieving features, becausefeatures can have time dependencies if they include time-sensitive data. Toavoiddata 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 whenretrieving features. Use theML.ENTITY_FEATURES_AT_TIME functionto 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 thetable.
• DATASET: the BigQuery dataset thatcontains the table.

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

  • entity_id: aSTRING column that contains the ID of the entityrelated to the features.
  • One or more feature columns.
  • feature_timestamp: aTIMESTAMP column that identifies when thefeature data was last updated.

  The column names are case-insensitive. For example, you can use a columnnamedEntity_ID instead ofentity_id.

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

• QUERY_STATEMENT: aSTRING value that specifies aGoogleSQL query that returns the feature data.This query must return the same columns as theTABLE_NAME argument. SeeGoogleSQL query syntaxfor the supported SQL syntax of theQUERY_STATEMENT clause.

• TIME: aTIMESTAMP value that specifies the point intime to use as a cutoff for feature data. Only rows where the value in thefeature_timestamp column is equal to or earlier than theTIME value arereturned. Defaults to the value of theCURRENT_TIMESTAMP function.

• NUM_ROWS: anINT64 value that specifies the numberof rows to return for each entity ID. Defaults to1.

• IGNORE_FEATURE_NULLS: aBOOL value that indicateswhether to replace aNULL value in a feature column with the feature columnvalue 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*FROMML.FEATURES_AT_TIME(TABLEmydataset.feature_table,time=>'2022-06-11 10:00:00+00',num_rows=>1,ignore_feature_nulls=>TRUE);

  Results in the following output, where thef2 value from therow for entity ID 2 that is timestamped'2022-06-10 12:00:00+00' issubstituted for theNULL 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 noearlier row for that entity ID, aNULL value is returned.

  Defaults toFALSE.

Output

TheML.FEATURES_AT_TIME function returns the input table rows that meet thepoint-in-time cutoff criteria, with thefeature_timestamp column showing thetimestamp that was input in thetime argument.

Examples

Example 1

This example shows a how to retrain a model using only features that werecreated or updated before2023-01-01 12:00:00+00:

CREATEORREPLACE`mydataset.mymodel`OPTIONS(WARM_START=TRUE)ASSELECT*EXCEPT(feature_timestamp,entity_id)FROMML.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 featuresthat were created or updated before2023-01-01 12:00:00+00:

SELECT*FROMML.PREDICT(MODEL`mydataset.mymodel`,(SELECT*EXCEPT(feature_timestamp,entity_id)FROMML.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 thefunction:

WITHfeature_tableAS(SELECT*FROMUNNEST(ARRAY<STRUCT<entity_idSTRING,f_1FLOAT64,f_2FLOAT64,feature_timestampTIMESTAMP>>[('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*FROMML.FEATURES_AT_TIME(TABLEfeature_table,time=>'2022-06-12 10:00:00+00',num_rows=>1,ignore_feature_nulls=>TRUE);