Scheduling Examples¶
This section shows how to schedule exported SQL using Databricks notebooks.
Prerequisites¶
Before creating the scheduled notebook, you need to export the SQL from your FeatureByte deployment. See SQL Export for detailed instructions:
- Go to your deployment in the FeatureByte UI
- Click the SQL tab
- Click Generate SQL to create the SQL template
- Copy the generated SQL code
Creating a Databricks Notebook¶
Here's a simple SQL notebook that runs your exported SQL daily. This will replace the feature table content on each run:
-- Databricks notebook source
%sql
CREATE OR REPLACE TABLE daily_customer_features
USING DELTA
AS
WITH DEPLOYMENT_REQUEST_TABLE AS (
SELECT
REQ."SK_ID_CURR",
CURRENT_TIMESTAMP() AS POINT_IN_TIME
FROM (
SELECT DISTINCT
"SK_ID_CURR" AS "SK_ID_CURR"
FROM (
SELECT
"SK_ID_CURR" AS "SK_ID_CURR",
"ClientID" AS "ClientID",
"CONTRACT_TYPE" AS "CONTRACT_TYPE",
"AMT_INCOME_TOTAL" AS "AMT_INCOME_TOTAL",
"AMT_CREDIT" AS "AMT_CREDIT",
"AMT_ANNUITY" AS "AMT_ANNUITY",
"AMT_GOODS_PRICE" AS "AMT_GOODS_PRICE",
"TYPE_SUITE" AS "TYPE_SUITE",
"REGION_POPULATION_RELATIVE" AS "REGION_POPULATION_RELATIVE",
"application_time" AS "application_time",
"DAYS_EMPLOYED" AS "DAYS_EMPLOYED",
"DAYS_REGISTRATION" AS "DAYS_REGISTRATION",
"DAYS_ID_PUBLISH" AS "DAYS_ID_PUBLISH",
"OWN_CAR_AGE" AS "OWN_CAR_AGE",
"FLAG_MOBIL" AS "FLAG_MOBIL",
"FLAG_EMP_PHONE" AS "FLAG_EMP_PHONE",
"FLAG_WORK_PHONE" AS "FLAG_WORK_PHONE",
"FLAG_CONT_MOBILE" AS "FLAG_CONT_MOBILE",
"FLAG_PHONE" AS "FLAG_PHONE",
"FLAG_EMAIL" AS "FLAG_EMAIL",
"REGION_RATING_CLIENT" AS "REGION_RATING_CLIENT",
"REGION_RATING_CLIENT_W_CITY" AS "REGION_RATING_CLIENT_W_CITY",
"WEEKDAY_APPR_PROCESS_START" AS "WEEKDAY_APPR_PROCESS_START",
"HOUR_APPR_PROCESS_START" AS "HOUR_APPR_PROCESS_START",
"REG_REGION_NOT_LIVE_REGION" AS "REG_REGION_NOT_LIVE_REGION",
"REG_REGION_NOT_WORK_REGION" AS "REG_REGION_NOT_WORK_REGION",
"LIVE_REGION_NOT_WORK_REGION" AS "LIVE_REGION_NOT_WORK_REGION",
"REG_CITY_NOT_LIVE_CITY" AS "REG_CITY_NOT_LIVE_CITY",
"REG_CITY_NOT_WORK_CITY" AS "REG_CITY_NOT_WORK_CITY",
"LIVE_CITY_NOT_WORK_CITY" AS "LIVE_CITY_NOT_WORK_CITY",
"EXT_SOURCE_1" AS "EXT_SOURCE_1",
"EXT_SOURCE_2" AS "EXT_SOURCE_2",
"EXT_SOURCE_3" AS "EXT_SOURCE_3",
"APARTMENTS_AVG" AS "APARTMENTS_AVG",
"BASEMENTAREA_AVG" AS "BASEMENTAREA_AVG",
"YEARS_BEGINEXPLUATATION_AVG" AS "YEARS_BEGINEXPLUATATION_AVG",
"YEARS_BUILD_AVG" AS "YEARS_BUILD_AVG",
"COMMONAREA_AVG" AS "COMMONAREA_AVG",
"ELEVATORS_AVG" AS "ELEVATORS_AVG",
"ENTRANCES_AVG" AS "ENTRANCES_AVG",
"FLOORSMAX_AVG" AS "FLOORSMAX_AVG",
"FLOORSMIN_AVG" AS "FLOORSMIN_AVG",
"LANDAREA_AVG" AS "LANDAREA_AVG",
"LIVINGAPARTMENTS_AVG" AS "LIVINGAPARTMENTS_AVG",
"LIVINGAREA_AVG" AS "LIVINGAREA_AVG",
"NONLIVINGAPARTMENTS_AVG" AS "NONLIVINGAPARTMENTS_AVG",
"NONLIVINGAREA_AVG" AS "NONLIVINGAREA_AVG",
"APARTMENTS_MODE" AS "APARTMENTS_MODE",
"BASEMENTAREA_MODE" AS "BASEMENTAREA_MODE",
"YEARS_BEGINEXPLUATATION_MODE" AS "YEARS_BEGINEXPLUATATION_MODE",
"YEARS_BUILD_MODE" AS "YEARS_BUILD_MODE",
"COMMONAREA_MODE" AS "COMMONAREA_MODE",
"ELEVATORS_MODE" AS "ELEVATORS_MODE",
"ENTRANCES_MODE" AS "ENTRANCES_MODE",
"FLOORSMAX_MODE" AS "FLOORSMAX_MODE",
"FLOORSMIN_MODE" AS "FLOORSMIN_MODE",
"LANDAREA_MODE" AS "LANDAREA_MODE",
"LIVINGAPARTMENTS_MODE" AS "LIVINGAPARTMENTS_MODE",
"LIVINGAREA_MODE" AS "LIVINGAREA_MODE",
"NONLIVINGAPARTMENTS_MODE" AS "NONLIVINGAPARTMENTS_MODE",
"NONLIVINGAREA_MODE" AS "NONLIVINGAREA_MODE",
"APARTMENTS_MEDI" AS "APARTMENTS_MEDI",
"BASEMENTAREA_MEDI" AS "BASEMENTAREA_MEDI",
"YEARS_BEGINEXPLUATATION_MEDI" AS "YEARS_BEGINEXPLUATATION_MEDI",
"YEARS_BUILD_MEDI" AS "YEARS_BUILD_MEDI",
"COMMONAREA_MEDI" AS "COMMONAREA_MEDI",
"ELEVATORS_MEDI" AS "ELEVATORS_MEDI",
"ENTRANCES_MEDI" AS "ENTRANCES_MEDI",
"FLOORSMAX_MEDI" AS "FLOORSMAX_MEDI",
"FLOORSMIN_MEDI" AS "FLOORSMIN_MEDI",
"LANDAREA_MEDI" AS "LANDAREA_MEDI",
"LIVINGAPARTMENTS_MEDI" AS "LIVINGAPARTMENTS_MEDI",
"LIVINGAREA_MEDI" AS "LIVINGAREA_MEDI",
"NONLIVINGAPARTMENTS_MEDI" AS "NONLIVINGAPARTMENTS_MEDI",
"NONLIVINGAREA_MEDI" AS "NONLIVINGAREA_MEDI",
"FONDKAPREMONT_MODE" AS "FONDKAPREMONT_MODE",
"HOUSETYPE_MODE" AS "HOUSETYPE_MODE",
"TOTALAREA_MODE" AS "TOTALAREA_MODE",
"WALLSMATERIAL_MODE" AS "WALLSMATERIAL_MODE",
"EMERGENCYSTATE_MODE" AS "EMERGENCYSTATE_MODE",
"OBS_30_CNT_SOCIAL_CIRCLE" AS "OBS_30_CNT_SOCIAL_CIRCLE",
"DEF_30_CNT_SOCIAL_CIRCLE" AS "DEF_30_CNT_SOCIAL_CIRCLE",
"OBS_60_CNT_SOCIAL_CIRCLE" AS "OBS_60_CNT_SOCIAL_CIRCLE",
"DEF_60_CNT_SOCIAL_CIRCLE" AS "DEF_60_CNT_SOCIAL_CIRCLE",
"DAYS_LAST_PHONE_CHANGE" AS "DAYS_LAST_PHONE_CHANGE",
"FLAG_DOCUMENT_2" AS "FLAG_DOCUMENT_2",
"FLAG_DOCUMENT_3" AS "FLAG_DOCUMENT_3",
"FLAG_DOCUMENT_4" AS "FLAG_DOCUMENT_4",
"FLAG_DOCUMENT_5" AS "FLAG_DOCUMENT_5",
"FLAG_DOCUMENT_6" AS "FLAG_DOCUMENT_6",
"FLAG_DOCUMENT_7" AS "FLAG_DOCUMENT_7",
"FLAG_DOCUMENT_8" AS "FLAG_DOCUMENT_8",
"FLAG_DOCUMENT_9" AS "FLAG_DOCUMENT_9",
"FLAG_DOCUMENT_10" AS "FLAG_DOCUMENT_10",
"FLAG_DOCUMENT_11" AS "FLAG_DOCUMENT_11",
"FLAG_DOCUMENT_12" AS "FLAG_DOCUMENT_12",
"FLAG_DOCUMENT_13" AS "FLAG_DOCUMENT_13",
"FLAG_DOCUMENT_14" AS "FLAG_DOCUMENT_14",
"FLAG_DOCUMENT_15" AS "FLAG_DOCUMENT_15",
"FLAG_DOCUMENT_16" AS "FLAG_DOCUMENT_16",
"FLAG_DOCUMENT_17" AS "FLAG_DOCUMENT_17",
"FLAG_DOCUMENT_18" AS "FLAG_DOCUMENT_18",
"FLAG_DOCUMENT_19" AS "FLAG_DOCUMENT_19",
"FLAG_DOCUMENT_20" AS "FLAG_DOCUMENT_20",
"FLAG_DOCUMENT_21" AS "FLAG_DOCUMENT_21",
"AMT_REQ_CREDIT_BUREAU_HOUR" AS "AMT_REQ_CREDIT_BUREAU_HOUR",
"AMT_REQ_CREDIT_BUREAU_DAY" AS "AMT_REQ_CREDIT_BUREAU_DAY",
"AMT_REQ_CREDIT_BUREAU_WEEK" AS "AMT_REQ_CREDIT_BUREAU_WEEK",
"AMT_REQ_CREDIT_BUREAU_MON" AS "AMT_REQ_CREDIT_BUREAU_MON",
"AMT_REQ_CREDIT_BUREAU_QRT" AS "AMT_REQ_CREDIT_BUREAU_QRT",
"AMT_REQ_CREDIT_BUREAU_YEAR" AS "AMT_REQ_CREDIT_BUREAU_YEAR",
(
"AMT_GOODS_PRICE" / NULLIF("AMT_CREDIT", 0)
) AS "AMT_GOODS_PRICE To AMT_CREDIT"
FROM "DEMO_DATASETS"."CREDIT_DEFAULT"."NEW_APPLICATION"
)
WHERE
NOT "SK_ID_CURR" IS NULL
) AS REQ
), _FB_AGGREGATED AS (
SELECT
REQ."SK_ID_CURR",
REQ."POINT_IN_TIME",
"T0"."_fb_internal_SK_ID_CURR_lookup_AMT_GOODS_PRICE To AMT_CREDIT_assign_1" AS "_fb_internal_SK_ID_CURR_lookup_AMT_GOODS_PRICE To AMT_CREDIT_assign_1"
FROM DEPLOYMENT_REQUEST_TABLE AS REQ
LEFT JOIN (
SELECT
"SK_ID_CURR",
ANY_VALUE("_fb_internal_SK_ID_CURR_lookup_AMT_GOODS_PRICE To AMT_CREDIT_assign_1") AS "_fb_internal_SK_ID_CURR_lookup_AMT_GOODS_PRICE To AMT_CREDIT_assign_1"
FROM (
SELECT
"SK_ID_CURR" AS "SK_ID_CURR",
"AMT_GOODS_PRICE To AMT_CREDIT" AS "_fb_internal_SK_ID_CURR_lookup_AMT_GOODS_PRICE To AMT_CREDIT_assign_1"
FROM (
SELECT
"SK_ID_CURR" AS "SK_ID_CURR",
"ClientID" AS "ClientID",
"CONTRACT_TYPE" AS "CONTRACT_TYPE",
"AMT_INCOME_TOTAL" AS "AMT_INCOME_TOTAL",
"AMT_CREDIT" AS "AMT_CREDIT",
"AMT_ANNUITY" AS "AMT_ANNUITY",
"AMT_GOODS_PRICE" AS "AMT_GOODS_PRICE",
"TYPE_SUITE" AS "TYPE_SUITE",
"REGION_POPULATION_RELATIVE" AS "REGION_POPULATION_RELATIVE",
"application_time" AS "application_time",
"DAYS_EMPLOYED" AS "DAYS_EMPLOYED",
"DAYS_REGISTRATION" AS "DAYS_REGISTRATION",
"DAYS_ID_PUBLISH" AS "DAYS_ID_PUBLISH",
"OWN_CAR_AGE" AS "OWN_CAR_AGE",
"FLAG_MOBIL" AS "FLAG_MOBIL",
"FLAG_EMP_PHONE" AS "FLAG_EMP_PHONE",
"FLAG_WORK_PHONE" AS "FLAG_WORK_PHONE",
"FLAG_CONT_MOBILE" AS "FLAG_CONT_MOBILE",
"FLAG_PHONE" AS "FLAG_PHONE",
"FLAG_EMAIL" AS "FLAG_EMAIL",
"REGION_RATING_CLIENT" AS "REGION_RATING_CLIENT",
"REGION_RATING_CLIENT_W_CITY" AS "REGION_RATING_CLIENT_W_CITY",
"WEEKDAY_APPR_PROCESS_START" AS "WEEKDAY_APPR_PROCESS_START",
"HOUR_APPR_PROCESS_START" AS "HOUR_APPR_PROCESS_START",
"REG_REGION_NOT_LIVE_REGION" AS "REG_REGION_NOT_LIVE_REGION",
"REG_REGION_NOT_WORK_REGION" AS "REG_REGION_NOT_WORK_REGION",
"LIVE_REGION_NOT_WORK_REGION" AS "LIVE_REGION_NOT_WORK_REGION",
"REG_CITY_NOT_LIVE_CITY" AS "REG_CITY_NOT_LIVE_CITY",
"REG_CITY_NOT_WORK_CITY" AS "REG_CITY_NOT_WORK_CITY",
"LIVE_CITY_NOT_WORK_CITY" AS "LIVE_CITY_NOT_WORK_CITY",
"EXT_SOURCE_1" AS "EXT_SOURCE_1",
"EXT_SOURCE_2" AS "EXT_SOURCE_2",
"EXT_SOURCE_3" AS "EXT_SOURCE_3",
"APARTMENTS_AVG" AS "APARTMENTS_AVG",
"BASEMENTAREA_AVG" AS "BASEMENTAREA_AVG",
"YEARS_BEGINEXPLUATATION_AVG" AS "YEARS_BEGINEXPLUATATION_AVG",
"YEARS_BUILD_AVG" AS "YEARS_BUILD_AVG",
"COMMONAREA_AVG" AS "COMMONAREA_AVG",
"ELEVATORS_AVG" AS "ELEVATORS_AVG",
"ENTRANCES_AVG" AS "ENTRANCES_AVG",
"FLOORSMAX_AVG" AS "FLOORSMAX_AVG",
"FLOORSMIN_AVG" AS "FLOORSMIN_AVG",
"LANDAREA_AVG" AS "LANDAREA_AVG",
"LIVINGAPARTMENTS_AVG" AS "LIVINGAPARTMENTS_AVG",
"LIVINGAREA_AVG" AS "LIVINGAREA_AVG",
"NONLIVINGAPARTMENTS_AVG" AS "NONLIVINGAPARTMENTS_AVG",
"NONLIVINGAREA_AVG" AS "NONLIVINGAREA_AVG",
"APARTMENTS_MODE" AS "APARTMENTS_MODE",
"BASEMENTAREA_MODE" AS "BASEMENTAREA_MODE",
"YEARS_BEGINEXPLUATATION_MODE" AS "YEARS_BEGINEXPLUATATION_MODE",
"YEARS_BUILD_MODE" AS "YEARS_BUILD_MODE",
"COMMONAREA_MODE" AS "COMMONAREA_MODE",
"ELEVATORS_MODE" AS "ELEVATORS_MODE",
"ENTRANCES_MODE" AS "ENTRANCES_MODE",
"FLOORSMAX_MODE" AS "FLOORSMAX_MODE",
"FLOORSMIN_MODE" AS "FLOORSMIN_MODE",
"LANDAREA_MODE" AS "LANDAREA_MODE",
"LIVINGAPARTMENTS_MODE" AS "LIVINGAPARTMENTS_MODE",
"LIVINGAREA_MODE" AS "LIVINGAREA_MODE",
"NONLIVINGAPARTMENTS_MODE" AS "NONLIVINGAPARTMENTS_MODE",
"NONLIVINGAREA_MODE" AS "NONLIVINGAREA_MODE",
"APARTMENTS_MEDI" AS "APARTMENTS_MEDI",
"BASEMENTAREA_MEDI" AS "BASEMENTAREA_MEDI",
"YEARS_BEGINEXPLUATATION_MEDI" AS "YEARS_BEGINEXPLUATATION_MEDI",
"YEARS_BUILD_MEDI" AS "YEARS_BUILD_MEDI",
"COMMONAREA_MEDI" AS "COMMONAREA_MEDI",
"ELEVATORS_MEDI" AS "ELEVATORS_MEDI",
"ENTRANCES_MEDI" AS "ENTRANCES_MEDI",
"FLOORSMAX_MEDI" AS "FLOORSMAX_MEDI",
"FLOORSMIN_MEDI" AS "FLOORSMIN_MEDI",
"LANDAREA_MEDI" AS "LANDAREA_MEDI",
"LIVINGAPARTMENTS_MEDI" AS "LIVINGAPARTMENTS_MEDI",
"LIVINGAREA_MEDI" AS "LIVINGAREA_MEDI",
"NONLIVINGAPARTMENTS_MEDI" AS "NONLIVINGAPARTMENTS_MEDI",
"NONLIVINGAREA_MEDI" AS "NONLIVINGAREA_MEDI",
"FONDKAPREMONT_MODE" AS "FONDKAPREMONT_MODE",
"HOUSETYPE_MODE" AS "HOUSETYPE_MODE",
"TOTALAREA_MODE" AS "TOTALAREA_MODE",
"WALLSMATERIAL_MODE" AS "WALLSMATERIAL_MODE",
"EMERGENCYSTATE_MODE" AS "EMERGENCYSTATE_MODE",
"OBS_30_CNT_SOCIAL_CIRCLE" AS "OBS_30_CNT_SOCIAL_CIRCLE",
"DEF_30_CNT_SOCIAL_CIRCLE" AS "DEF_30_CNT_SOCIAL_CIRCLE",
"OBS_60_CNT_SOCIAL_CIRCLE" AS "OBS_60_CNT_SOCIAL_CIRCLE",
"DEF_60_CNT_SOCIAL_CIRCLE" AS "DEF_60_CNT_SOCIAL_CIRCLE",
"DAYS_LAST_PHONE_CHANGE" AS "DAYS_LAST_PHONE_CHANGE",
"FLAG_DOCUMENT_2" AS "FLAG_DOCUMENT_2",
"FLAG_DOCUMENT_3" AS "FLAG_DOCUMENT_3",
"FLAG_DOCUMENT_4" AS "FLAG_DOCUMENT_4",
"FLAG_DOCUMENT_5" AS "FLAG_DOCUMENT_5",
"FLAG_DOCUMENT_6" AS "FLAG_DOCUMENT_6",
"FLAG_DOCUMENT_7" AS "FLAG_DOCUMENT_7",
"FLAG_DOCUMENT_8" AS "FLAG_DOCUMENT_8",
"FLAG_DOCUMENT_9" AS "FLAG_DOCUMENT_9",
"FLAG_DOCUMENT_10" AS "FLAG_DOCUMENT_10",
"FLAG_DOCUMENT_11" AS "FLAG_DOCUMENT_11",
"FLAG_DOCUMENT_12" AS "FLAG_DOCUMENT_12",
"FLAG_DOCUMENT_13" AS "FLAG_DOCUMENT_13",
"FLAG_DOCUMENT_14" AS "FLAG_DOCUMENT_14",
"FLAG_DOCUMENT_15" AS "FLAG_DOCUMENT_15",
"FLAG_DOCUMENT_16" AS "FLAG_DOCUMENT_16",
"FLAG_DOCUMENT_17" AS "FLAG_DOCUMENT_17",
"FLAG_DOCUMENT_18" AS "FLAG_DOCUMENT_18",
"FLAG_DOCUMENT_19" AS "FLAG_DOCUMENT_19",
"FLAG_DOCUMENT_20" AS "FLAG_DOCUMENT_20",
"FLAG_DOCUMENT_21" AS "FLAG_DOCUMENT_21",
"AMT_REQ_CREDIT_BUREAU_HOUR" AS "AMT_REQ_CREDIT_BUREAU_HOUR",
"AMT_REQ_CREDIT_BUREAU_DAY" AS "AMT_REQ_CREDIT_BUREAU_DAY",
"AMT_REQ_CREDIT_BUREAU_WEEK" AS "AMT_REQ_CREDIT_BUREAU_WEEK",
"AMT_REQ_CREDIT_BUREAU_MON" AS "AMT_REQ_CREDIT_BUREAU_MON",
"AMT_REQ_CREDIT_BUREAU_QRT" AS "AMT_REQ_CREDIT_BUREAU_QRT",
"AMT_REQ_CREDIT_BUREAU_YEAR" AS "AMT_REQ_CREDIT_BUREAU_YEAR",
(
"AMT_GOODS_PRICE" / NULLIF("AMT_CREDIT", 0)
) AS "AMT_GOODS_PRICE To AMT_CREDIT"
FROM "DEMO_DATASETS"."CREDIT_DEFAULT"."NEW_APPLICATION"
)
)
GROUP BY
"SK_ID_CURR"
) AS T0
ON REQ."SK_ID_CURR" = T0."SK_ID_CURR"
)
SELECT
AGG."SK_ID_CURR",
CAST("_fb_internal_SK_ID_CURR_lookup_AMT_GOODS_PRICE To AMT_CREDIT_assign_1" AS DOUBLE) AS "NEW_APPLICATION_AMT_GOODS_PRICE_To_AMT_CREDIT",
CURRENT_TIMESTAMP() AS "POINT_IN_TIME"
FROM _FB_AGGREGATED AS AGG
Scheduling the Notebook¶
To schedule this notebook in Databricks:
- Save the notebook to your Databricks workspace
- Go to Jobs -> Create Job
- Select your notebook as the task
- Set schedule (e.g., "Every 1 day")
- Configure cluster settings as needed
Next Steps¶
See the Complete Workflow for an end-to-end example including model training and batch scoring.