UseCases/AnomalyDetection_OutstandingAmount_FS/AnomalyDetection_OutstandingAmount_FeatureStore.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "510d7ad9-c472-45ed-8bd3-509865ff039d",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial;color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Anomaly Detection in Banking - Finding Outstanding Amounts using Feature Store\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0eefa90c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In banking, tracking customer balances is vital, with anomaly detection crucial for spotting errors or fraud. This method targets anomalies in monthly balances.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Time and Value are critical in today’s business, with ML model development and deployment increasingly challenging as prediction becomes central to competitiveness.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Successful AI/ML implementations face three main challenges:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>The Data Problem:</b> Quality data and feature engineering consume 80% of the implementation time. Even when different use cases share the same source data and features, organizations often handle data preparation separately.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>The Scale Problem:</b> Real-world use cases often require multiple models. In production, these models require fresh features engineered in the same way as during training. Ensuring the auditability of these features behind model decisions is crucial.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>The Deployment Problem:</b> Transitioning prototypes to production, especially operationalizing data prep pipelines, is often problematic.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Addressing these challenges requires strategic planning, skilled talent, and integration with existing systems. Oraganizations with a history in Data Management recognize the benefits of reusable Data Products, making Enterprise Feature Stores a valuable investment.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A Feature Store is a curated repository of pre-calculated features, simplifying the journey from data to actionable insights. An Enterprise Feature Store extends across domains/teams, incorporating a Governance Framework for predictable feature delivery. </p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>While most features are reusable, some need model-specific calculations before integration into a unified dataset.</b></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The key difference between Feature Store (FS) and Enterprise Feature Store (EFS) is the scope across multiple domains/teams along with the Governance Framework (that gives an assurance that features are delivered under predictable SLAs and it also defines the operating model how the EFS is used across different teams/domains and how features lifecycle is managed). Although most Features are considered as re-usable, there is still some minor part of Features that must be calculated as model-specific (e.g., scaled variables, principal components, etc.) and then combined with the rest of the pre-calculated Features into a single data set (ADS). The figure below describes this co-existence of model-specific ADS(es) and model-independent EFS.</p>\n",
    "\n",
    "<img src='images/EFS.png'>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>This use case provides insight into real customer needs for Feature Store functionalities, derived from a large French bank's ongoing project.</i></b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Rapid model creation and deployment through enterprise feature reuse.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Flexible creation and usage of new features without extensive engineering support.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Consistent definitions ensure accuracy and quick deployment.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Collaboration and sharing of features among teams.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Maintained feature lifecycle for compliance and auditability.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are several reasons why EFS naturally fits to Teradata Vantage:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Utilizes Teradata Vantage with its powerful Analytical Library and SQL Engine.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Primary Index enables efficient single-row access for online feature use.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Single platform for both online and offline feature stores.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Macros reduce parsing overhead from API access.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>R and Python code execution within SQL Engine.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Bi-temporal querying capability.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Scalable MPP power for feature computation.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Industry-specific Logical Data Model as a feature source.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Connectivity to Object Storage via NOS for feature data sourcing.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Query Grid facilitates access to multiple data sources.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Close-to-real-time feature delivery using Query Services and Teradata Intelligent Memory.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Workload management prioritizes tasks effectively.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The unique massively-parallel architecture of Teradata Vantage allows users to prepare data, train, evaluate, and deploy models at unprecedented scale.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Methodology</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we have used a methodology which involves analyzing a time series of data, where each data point represents the outstanding amount at the end of each month. To detect anomalies, we use the following steps:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Model the Distribution:</b> We assume that the historical data of monthly balances follow a normal distribution. This distribution is characterized by two parameters: the mean (μ) and the standard deviation (σ). These are the features of the Entity </li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Compute the Z-Score:</b> For the most recent monthly balance (the latest data point in the time series), we compute its Z-score. The Z-score is a statistical measure that describes a value's relationship to the mean of a group of values. It is calculated using the formula: </li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><div><code>Z = (X - μ) / σ</code></div></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>where X is the value in question, μ is the mean, and σ is the standard deviation.</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Threshold for Anomaly Detection:</b> We set a threshold for the Z-score. If the absolute value of the Z-score for the latest monthly balance exceeds this threshold, it is flagged as an anomaly.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>It's important to note that the computation of the Z-score and the anomaly flag is dependent on the values of the mean and standard deviation. These dependent features are not computed at the same time as the static features but are derived later, once the latest outstanding amount (the new data point) becomes available.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Feature Engineering</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Feature engineering is a crucial step in the entity-feature paradigm, as it involves creating and transforming features to better represent the underlying problem for predictive modeling. In our case, the feature engineering process is twofold, each with its specific inputs and outputs. Below are the processes that are a part of this feature engineering</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process 1:</b> Computing Mean and Standard Deviation</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process 2:</b> Computing Z-Score and Anomaly Flag</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Roll Out:</b> Feature Engineering rollout\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Addressing Circular Dependency</li>\n",
    "    <li>Roll out after adjusting circular dependency</li></ul>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Validation:</b> Feature Store Validation</li>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "071e8d21-976d-4628-8f3f-0e944b7e743e",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58929a2d-3502-4c82-a173-415aa42db3e7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47cbbc2c-850f-4b12-9a3a-801f071dadd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# # '%%capture' suppresses the display of installation steps of the following packages\n",
    "!pip install tdfs4ds==0.2.2.5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83c6c29a-c898-4fa0-8b83-b28ad2b716e2",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af76543c-a082-4800-adff-8b786b898491",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "warnings.simplefilter(action='ignore', category=RuntimeWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "import json\n",
    "import getpass\n",
    "import pandas as pd\n",
    "import datetime\n",
    "from teradataml import *\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "display.max_rows = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "909f2124-4408-4e9c-8aa7-5c319fb2ce0f",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe183a24-71b3-4310-b9a2-25bac5cb43ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86852069-bccd-4853-a551-7aa306abcb4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=AnomalyDetection_OutstandingAmount_EFS.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd2caf56-913b-4fbb-ad9b-6c5a25dd5006",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>   \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83c82fde-c387-4f2a-babf-fe5d4c52b1c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_AnomalyDetection_EFS_cloud');\"\n",
    " # Takes about 20 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_AnomalyDetection_EFS_local');\"\n",
    " # Takes about 30 secs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d50d524d-bd0e-4c1e-998a-306b7407171e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a70f9a79-61a1-4037-8b9b-40365620e356",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeda84b9-ef5c-4d64-9cbb-fef647d433ab",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze the raw data set</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by creating a teradataml dataframe. A \"Virtual DataFrame\" that points directly to the dataset in Vantage.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f70b8b9e-f9d2-4127-a370-90d503a7ac01",
   "metadata": {},
   "outputs": [],
   "source": [
    "source_data = DataFrame(in_schema(\"DEMO_AnomalyDetection_EFS\",\"OUTSTANDING_AMOUNTS\"))\n",
    "source_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1d9f400-e445-43e6-87b5-ac344bca23cd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create the list of dates that are existing in the source data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5e1fdcc-0ad5-4710-9d42-35529f268fdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_Balance_Dates = sorted([str(d[0]) for d in source_data.groupby('Balance_Date').count().get('Balance_Date').get_values()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c90061b-d658-4cf3-82e4-13fa6c1216c3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Setup the feature store</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will start by setting up the database used for the creation of feature store</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "889b0b82-ccf2-4be6-a1a1-ecb1927991cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "Param = {\n",
    "    'database': 'demo_user',\n",
    "    'temp_database_name': 'demo_user'\n",
    "}    \n",
    "print(Param)\n",
    "import tdfs4ds\n",
    "tdfs4ds.setup(database = Param['database'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a2b952a-a196-40fb-849f-253577ba5807",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.feature_store.entity_management import remove_entity\n",
    "remove_entity(entity='Account_ID',data_domain='OUTSTANDING_AMOUNTS')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4863f245-8c0a-442b-8c14-b902cfcd6cc5",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4.1 Managing the time</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Build a TimeManager</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TimeManager function from the utils package of the tdfs4ds library initializes the TimeManager with a table name, schema name, and optionally a data type. If the table doesn't exist, it creates one with a BUSINESS_DATE column of the specified data type.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The various functions available in the TimeManager are: </p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>display:</code> Displays the table.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>update:</code> Updates the BUSINESS_DATE.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>get_date_in_the_past:</code> Retrieves the earliest date and time value from the table </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cd08149-1d60-4292-9e46-9183ff156521",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.utils.time_management import TimeManager\n",
    "business_time = TimeManager(table_name = 'BUSINESS_DATE', schema_name = 'demo_user')\n",
    "business_time.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4a76a89-e95d-4f17-a990-c20e16d82bee",
   "metadata": {},
   "outputs": [],
   "source": [
    "business_time.update(new_time=list_Balance_Dates[9])\n",
    "business_time.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c00dc5ee-3b99-4df4-a564-6e7de899854f",
   "metadata": {},
   "outputs": [],
   "source": [
    "business_time.get_date_in_the_past()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14ff7010-430a-489f-9bb7-24b3a2c48ab8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>FEATURE_STORE_TIME is used to define a specific timestamp for querying the feature data, enabling temporal feature querying. If FEATURE_STORE_TIME is None, the query defaults to the current valid time. Here we are setting the time to the past date as we are using the past data for our feature store. The FEATURE_STORE_TIME will be used in the roll out process. In the roll out process the FEATURE_STORE_TIME is set based on the list of dates on which the roll out function iterates.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f09ddae3-8ea0-448b-bc14-d061528c3fff",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdfs4ds.FEATURE_STORE_TIME = business_time.get_date_in_the_past()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bf6dd3c-38be-481d-a8f5-8e76babe277d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use the TimeManager to filter out the source data</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we will select dates only for the past i.e all the data where the Balance Date is less than or equal to Business Date</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1056b79-2818-415f-96c9-7617801d97e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_business_date = DataFrame('BUSINESS_DATE')\n",
    "df_business_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0906777-7e62-42c8-91ab-772494f1adac",
   "metadata": {},
   "outputs": [],
   "source": [
    "input_source = source_data.merge(right=df_business_date, how='inner', on=[source_data.Balance_Date<=df_business_date.BUSINESS_DATE])\n",
    "input_source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31c4491b-7bdf-413f-8d10-19f66da5fe3e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "input_source.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb184a72-cac1-4371-be52-e66fefdbb158",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Feature Engineering - Initialization</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35c2a9da-6f86-4cd2-988e-3dfadf09f74d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1 Process 0 : Count the number of Monthly Balance per Account</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a6961e9-2513-4a9d-ad9f-019e0e1e6259",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this step we will do manual engineering using teradataml</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d086ef8-a2f3-4929-b9c9-10fdebbe6c2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nb_points_dev = input_source.groupby('Account_ID').count()\n",
    "df_nb_points_dev"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19030a4d-049c-4e1c-83b4-0de377524018",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will save this process as a permanent view using the crystallize_view function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> the crystallize_view function crystallizes a TeradataML (tdml) DataFrame into a view with specified name and schema. This function takes a TeradataML DataFrame (tddf), generates a dependency graph for its SQL representation, and replaces or creates sub-views with new names in the database. It then constructs the final view by replacing old names with new ones in the SQL representation and executes the query to create the main view. The result is returned as a TeradataML DataFrame representing the created view.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e647886-b408-4fa0-a8bb-9a1871399ce3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.utils.lineage import crystallize_view\n",
    "df_nb_points = crystallize_view(df_nb_points_dev,view_name='PROCESS_0',schema_name='demo_user')\n",
    "df_nb_points"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33b021c2-6740-4809-b536-e0345ebd3b74",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Register and Upload Features in feature store </b></p> \n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The upload_features register the feature engineering process implemented with the teradata dataframe in the process catalog (and attribute a process id), registers the entity and the features in the feature catalog, performs the feature engineering process at the FEATURE_STORE_TIME and ingests the calculated features in the feature store data model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Required Parameters</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>df (tdml.DataFrame):</code> The input DataFrame containing the feature data.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>entity_id (list/dict/other):</code> A representation of the entity ID. If a dictionary, the keys are used as entity IDs. If a list or another data type, it is used directly as the entity ID.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>feature_names (list):</code> A list of column name corresponding to the features you want to ingest in the feature store. \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>**kwargs:</code> Additional keyword arguments that can be passed to the function. The metadata argument is used to document the features.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The function returns a teradataml dataframe that retrieves the calculated features read from the feature store data model, providing fast access to calculated features.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>First we define the variables to be used</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>entity_id:</code>dict or compatible type. The entity identifier. If not a dictionary, it will be converted using get_column_types.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>feature_names:</code> list. The list of feature names to be uploaded.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>metadata:</code> dict, optional. Additional metadata to associate with the upload. Defaults to an empty dictionary.</li></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a3c4c4d-3a04-41d5-92bb-8971ae0ca2f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the entity ids in the view columns (+ data types) \n",
    "entity_id       = ['Account_ID']\n",
    "# Specify the columns that contains the actual features\n",
    "feature_names   = ['COUNT_MONTHLY_BALANCE']\n",
    "# attach informative metadata to document your process\n",
    "metadata        = {'project' : 'test'}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3818e65-c9b0-46e0-8cd2-2719aeaa1d23",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>DATA_DOMAIN is the data domain under which the table and view will be categorized. If not specified, the function uses a default value which is the default database when specified in the context and when the tdfs4ds is imported after the connection.. Here we set the value of the DATA_DOMAIN to OUTSTANDING_AMOUNTS.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa165a12-99a8-44e1-a3b9-918d6b4133bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdfs4ds.DATA_DOMAIN = 'OUTSTANDING_AMOUNTS'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d324d59e-4399-4280-9ee7-89d09ad8e170",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds import upload_features\n",
    "dataset = upload_features(\n",
    "    df=df_nb_points,\n",
    "    entity_id=entity_id,\n",
    "    feature_names=feature_names,\n",
    "    metadata = metadata\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4530ad3-a8a3-43ac-8def-889a18e3e889",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d014f05-cfc0-42c8-ab7e-93f0019f9ae0",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.2 Process 1 : Computing Mean and Standard Deviation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Input:</b> Historical data of monthly outstanding amounts.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process:</b> This first process involves calculating two key statistical measures from the historical monthly balances: the mean (μ) and the standard deviation (σ). </p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The **mean** provides an average value of the historical data, serving as a central reference point.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The **standard deviation** measures the variation or dispersion of the balances from the mean, indicating the typical range of fluctuation.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Output:</b>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Mean (μ) of historical balances</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Standard Deviation (σ) of historical balances</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>These outputs serve as static features that describe the general behavior of the account's monthly balances over time.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the selected features and build a dataset with these features.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The get_feature_versions function will retrieve feature versions for specified features associated with certain entities from a given data domain. This function allows fetching either all versions or just the latest versions of the features. The feature version is the process_id that computed these features.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32189aaf-69be-4cca-9c28-f4a59375dc4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.feature_store.feature_query_retrieval import get_available_features,get_feature_versions\n",
    "selected_features = get_feature_versions(entity_name= entity_id, features=feature_names, domain = 'OUTSTANDING_AMOUNTS')\n",
    "selected_features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43e8fff7-157d-4a00-b1ce-f8d2e7ffa33b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Build Dataset with selected features</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The build_dataset function creates a dataset view from the feature store. This dataset is a snapshot of the values of the selected features at the FEATURE_STORE_TIME. If we want the current version, i.e. the latest, we should set FEATURE_STORE_TIME to None before calling build_dataset.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Required Parameters</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>entity_id:</code> is the list of column names defining the entity.</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>selected_features:</code> is the dictionary with feature names as key and feature version (meaning the process id that calculates these features) are values.\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><code>view_name:</code> is the name of the dataset view we want. </li>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32fa7fa4-8992-44ab-a133-60febd7ffcbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds import build_dataset\n",
    "nb_points_efs = build_dataset(entity_id=entity_id,selected_features=selected_features,view_name='nb_points_efs', time_manager=business_time)\n",
    "nb_points_efs = DataFrame(in_schema(\"demo_user\",\"nb_points_efs\"))\n",
    "nb_points_efs = nb_points_efs[nb_points_efs.COUNT_MONTHLY_BALANCE > 9]\n",
    "nb_points_efs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b8a20d8-a851-45d4-9c1c-5bfee430bdbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import func\n",
    "source_efs = input_source.merge(right=nb_points_efs, how='inner', on = 'Account_ID', lsuffix = 't1', rsuffix = 't2')\n",
    "source_efs = source_efs.assign(drop_columns=True,\n",
    "                               Account_ID = source_efs.Account_ID_t1,\n",
    "                               Balance_Date = source_efs.Balance_Date,\n",
    "                               Monthly_Balance = source_efs.Monthly_Balance)\n",
    "\n",
    "source_efs = source_efs.select(['Account_ID','Monthly_Balance'])\n",
    "source_efs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc2fc890-c3f1-49a2-a4bf-5f94992d43c2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we will calculate the mean and standard deviation for the monthly balance for each account</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8979b1d8-c056-414d-aac4-a5c10330ef75",
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_std_dev = source_efs.groupby('Account_ID').assign(std_monthly_balance = func.stddev_samp(source_efs.Monthly_Balance.expression)\n",
    "                                                      ,mean_monthly_balance = func.avg(source_efs.Monthly_Balance.expression))\n",
    "mean_std_dev"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d53d8de4-a490-48d0-93c1-5f49f914554f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will save the output of this process as a permanent view using the crystallize_view function.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd3190bf-6c1c-4a7d-9826-cea302341d67",
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_std = crystallize_view(mean_std_dev,view_name='mean_std', schema_name='demo_user')\n",
    "mean_std"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1871409-b126-422a-9184-e4741730d005",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will register these newly created features in the feature store and upload.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42acd2b1-b989-4da1-9fec-c10a740433af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the entity ids in the view columns (+ data types) \n",
    "entity_id       = ['ACCOUNT_ID']\n",
    "# Specify the columns that contains the actual features\n",
    "feature_names   = ['MEAN_MONTHLY_BALANCE','STD_MONTHLY_BALANCE']\n",
    "# attach informative metadata to document your process\n",
    "metadata        = {'project' : 'test'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06c409ea-5bdf-413f-b43a-f3a7b5f93394",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dataset = upload_features(\n",
    "    df=mean_std,\n",
    "    entity_id=entity_id,\n",
    "    feature_names=feature_names,\n",
    "    metadata = metadata\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4932a8dd-7c43-4916-ae45-e6492962d572",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.3 Process 2 : Computing Z-score and anomaly score</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Input:</b> Latest outstanding amount and the outputs from Process 1 (mean and standard deviation).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process:</b> Once the latest monthly balance is available, this process computes two additional features:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The **Z-Score**, calculated as \\( Z = (X - μ) / σ \\), where \\(X\\) is the latest outstanding amount. It indicates how many standard deviations the latest balance deviates from the mean.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The **Anomaly Flag**, a binary indicator (1 or 0), is derived from the Z-score. If the absolute value of the Z-score exceeds a predefined threshold, it indicates an anomaly (flagged as 1); otherwise, it is considered normal (flagged as 0).</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Output:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Z-Score of the latest outstanding amount</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Anomaly Flag indicating the presence of an anomaly</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>These dynamic, dependent features are crucial for identifying significant deviations in the latest outstanding amount, enabling timely anomaly detection.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will fetch the uploaded features and than build the dataset using these features</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8087d12b-e277-4d58-8f17-b71652f2e9a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_features = get_feature_versions(entity_name= entity_id, features=feature_names, domain = 'OUTSTANDING_AMOUNTS')\n",
    "selected_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9a1b8d7-f3dd-4eee-a9d7-8adbe67a9b4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_std_efs = build_dataset(entity_id=entity_id,selected_features=selected_features,view_name='mean_std_efs', time_manager=business_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5faee8ce-3c16-4067-8331-8c7c63a121cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_std_efs = DataFrame(in_schema('demo_user','mean_std_efs'))\n",
    "mean_std_efs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "754d9f57-d157-48b9-85c0-f626a2d1ab24",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the dataset where the balance date is equal to the BUSINESS_DATE and use that dataset to calculate the Z_SCORE and the ANOMALY based on the Z_SCORE</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e144a8b-c281-4c56-bb8a-2cbde856bd13",
   "metadata": {},
   "outputs": [],
   "source": [
    "source_data = DataFrame(in_schema(\"DEMO_AnomalyDetection_EFS\",\"OUTSTANDING_AMOUNTS\"))\n",
    "df_business_date = DataFrame(in_schema(\"demo_user\",\"BUSINESS_DATE\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1abda37e-0c20-4651-8b69-5c0da4b8405b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "input_zscore_source = source_data.merge(right=df_business_date, how='inner', on=[source_data.Balance_Date==df_business_date.BUSINESS_DATE])\n",
    "input_zscore_source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b52b097-bf18-4c11-9322-232e24d9fc2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# input_zscore_source = source_data[source_data.Balance_Date == business_date]\n",
    "source_zscore = input_zscore_source.merge(right=mean_std_efs, how='inner', on = [\"ACCOUNT_ID = Account_ID\"], lsuffix = 't1', rsuffix = 't2')\n",
    "source_zscore = source_zscore.assign(drop_columns=True,\n",
    "                               Account_ID = source_zscore.ACCOUNT_ID_t1,\n",
    "                               Z_SCORE = (source_zscore.Monthly_Balance-source_zscore.MEAN_MONTHLY_BALANCE)\n",
    "                                                       /source_zscore.STD_MONTHLY_BALANCE.nullifzero())\n",
    "anomaly_dev = source_zscore.assign(ANOMALY_FLAG = case([(source_zscore.Z_SCORE.abs() > 3, 1)], else_ = 0) )\n",
    "anomaly_dev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17d6053c-4681-42dd-9263-d225a66f8896",
   "metadata": {},
   "outputs": [],
   "source": [
    "anomaly_dev.groupby('ANOMALY_FLAG').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0361714b-fe2c-481b-a633-8423e1d54168",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will save the process as permanent view</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a32711c0-6a48-411d-a51a-1a06d88f443d",
   "metadata": {},
   "outputs": [],
   "source": [
    "anomaly = crystallize_view(anomaly_dev,view_name='anomaly', schema_name='demo_user')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c594f3f-ab7c-4a6a-aaf8-5f99b5502bd2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will register in the feature store</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cfb5a75-ef98-467a-82f9-f6e9133a74f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "anomaly= DataFrame(in_schema('demo_user','anomaly'))\n",
    "anomaly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72422bf3-047d-4406-8eda-a98a6860286c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the entity ids in the view columns (+ data types) \n",
    "entity_id       = ['ACCOUNT_ID']\n",
    "# Specify the columns that contains the actual features\n",
    "feature_names   = ['Z_SCORE','ANOMALY_FLAG']\n",
    "# attach informative metadata to document your process\n",
    "metadata        = {'project' : 'test'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc579629-e68a-47f3-93c6-60eb5922f84f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dataset = upload_features(\n",
    "    df=anomaly,\n",
    "    entity_id=entity_id,\n",
    "    feature_names=feature_names,\n",
    "    metadata = metadata\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "612e96d5-5fec-4182-abdf-42cd99debf11",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Feature Engineering - Roll out</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will do a refinement of the Feature Engineering Process. In the context of anomaly detection for outstanding amounts in the banking sector, a refinement is needed in the feature engineering process to address a potential circular dependency. The key modification is to exclude data points identified as anomalies in past computations when calculating the static features (mean and standard deviation).</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01a5aa6f-5fc5-4822-8b8c-59f0091912c8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 Addressing Circular Dependency</b></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The original methodology has a circular dependency:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process 1</b> computes the mean and standard deviation, which are used as inputs for Process 2.</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process 2</b> computes the Z-score and anomaly flag for the latest data point.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>However, if anomalies identified by **Process 2** are included in the dataset for **Process 1**, it can skew these static values and affect future anomaly detection.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caf28cb7-0ee8-4bb2-8367-bbbea57e127e",
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_features = get_feature_versions(entity_name= entity_id, features=['ANOMALY_FLAG'], domain = 'OUTSTANDING_AMOUNTS')\n",
    "selected_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6b16b02-e8ec-427a-9363-8863709af381",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.feature_store.feature_query_retrieval import list_features\n",
    "list_features()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c8d97e9-1106-46d7-a306-50e6aea6a00f",
   "metadata": {},
   "outputs": [],
   "source": [
    "source_data = DataFrame(in_schema(\"DEMO_AnomalyDetection_EFS\",\"OUTSTANDING_AMOUNTS\"))\n",
    "df_business_date = DataFrame(in_schema(\"demo_user\",\"BUSINESS_DATE\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f577638-69e0-42e5-b4ff-164a92fde07a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "input_source_past = source_data.merge(right=df_business_date, how='inner', on=[source_data.Balance_Date<df_business_date.BUSINESS_DATE])\n",
    "input_source_past"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2a736b8-5c1d-49b4-8066-e7683fcf179d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds import build_dataset_time_series\n",
    "mean_df = build_dataset_time_series(input_source_past, 'Balance_Date', entity_id, selected_features, \n",
    "                                    time_manager=business_time)\n",
    "mean_df = mean_df.assign(mon_balance = case([(mean_df.ANOMALY_FLAG == 1, None)], else_ = mean_df.Monthly_Balance))\n",
    "mean_std_dev = mean_df.groupby('Account_ID').assign(std_monthly_balance = \n",
    "                                                    func.stddev_samp(mean_df.mon_balance.expression)\n",
    "                                                      ,mean_monthly_balance = \n",
    "                                                    func.avg(mean_df.mon_balance.expression))\n",
    "\n",
    "mean_std_dev"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "739b7c2c-25be-4c2e-b6bb-35c000325f3b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will save the modified view as a permanent view</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f62522c-88a2-4a20-ac5e-71228cdfc69b",
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_std = crystallize_view(mean_std_dev,view_name='mean_std', schema_name=Param['database'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91c6c87f-451b-4686-940d-56832da26e63",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.2 The roll out after adjusting circular dependency</b></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To resolve this, we adjust the methodology as follows:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Revised Process 1: Excluding Anomalies in Static Feature Computation</b></p>\n",
    "\n",
    " <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Input:</b> Historical data of monthly outstanding amounts, excluding those identified as anomalies.</li>\n",
    "\n",
    "  <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process:</b> This revised process calculates the mean and standard deviation of the historical monthly balances, but with a critical adjustment – it excludes any data points previously flagged as anomalies. By doing so, it ensures that the static features represent the 'normal' behavior of the account's monthly balances.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Output:</b></p> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Adjusted Mean (μ) of historical balances (excluding anomalies)</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Adjusted Standard Deviation (σ) of historical balances (excluding anomalies)</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Continuation with Process 2: As Previously Defined</b></p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Input:</b> Latest outstanding amount and the outputs from the revised Process 1.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Process and Output:</b> Remain the same as previously defined. The Z-score and anomaly flag are computed using the adjusted mean and standard deviation.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Implications of the Refinement</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This refinement has significant implications:</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>It ensures that the static features (mean and standard deviation) are not influenced by anomalous data, which could otherwise distort the understanding of 'normal' behavior.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>It adds a layer of historical awareness to the model, as the anomaly detection for a current data point depends on the refined understanding of past data.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>It resolves the circular dependency, making the feature engineering process more robust and the anomaly detection more accurate.</li>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0108225-c935-42ec-8359-aa0573b51c27",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>First we get the list of processes to run sequentially</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6232065-c21d-4794-8781-477772d5514c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.process_store.process_query_administration import list_processes\n",
    "list_process_ = list_processes()\n",
    "list_process_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c3d5b52-f708-458f-b161-898b8b7252c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.process_store.process_query_administration import get_process_id\n",
    "db = Param['database']\n",
    "process_0_id = get_process_id(f'\"{db}\".\"PROCESS_0\"')\n",
    "process_1_id = get_process_id(f'\"{db}\".\"mean_std\"')\n",
    "process_2_id = get_process_id(f'\"{db}\".\"anomaly\"')\n",
    "print('process_0_id:',process_0_id)\n",
    "print('process_1_id:',process_1_id)\n",
    "print('process_2_id:',process_2_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8aac24bc-4876-4e93-92dd-01e3a2442351",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will now execute the roll out process. This process executes a series of processes for each date in a given list, managing the time and logging settings. It iterates over a list of dates, updating a TimeManager object with each date, and then executes a list of processes for that date. It also manages the synchronization of time for a feature store and disables display logs during its execution. It will also set global variables DISPLAY_LOGS and FEATURE_STORE_TIME. It catches and prints exceptions along with the date on which they occurred.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4466df9e-1b74-4ac5-bfbf-0f0884d24ea8",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>**Note: The roll out process will take approximately 10-15 minutes as it runs the process for 10 different dates</i></b></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed49d9fd-9883-4d93-babd-502413488b77",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds import roll_out\n",
    "roll_out(\n",
    "    date_list = list_Balance_Dates[10::],\n",
    "    process_list = [process_0_id, process_1_id,process_2_id],\n",
    "    time_manager = business_time\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49eb294b-3144-4f3c-a253-9dec16a16c4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "business_time.get_date_in_the_past()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "209d9364-33a5-4edb-a2ef-4d132898acc7",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Validation of feature Store</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The previous approach starts looking complex with a use case pretty simple. Moreover, we see that this pattern is use case or pipeline dependent, leading to a complex implementation because specific. The enterprise feature store approach aims to reduce the complexity of the implementation by splitting the pipeline in simple processes.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We see that there are only two operations to consider:</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>the process to feature store operation</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>the feature store to feature set operation, with time travel enabled</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>what remains specific to the use case is the building of the dataset, which requires the join between feature set and sources coming from the production area. Beneath the surface, we also guess the problematic of the cold start.</p>\n",
    "\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.1 How many anomalies have been spotted?</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2142c11-e352-477b-bda5-4eb4822a6f93",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tdfs4ds.utils.time_management import TimeManager\n",
    "business_time = TimeManager(table_name = 'BUSINESS_DATE', schema_name = Param['database'])\n",
    "df_business_date =  DataFrame('BUSINESS_DATE')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cca7f0d7-b4e8-4aa6-9631-b7e0f243d723",
   "metadata": {},
   "outputs": [],
   "source": [
    "source_data = DataFrame(in_schema(\"DEMO_AnomalyDetection_EFS\",\"OUTSTANDING_AMOUNTS\"))\n",
    "\n",
    "input_source = source_data.merge(right=df_business_date, how='inner', on=[source_data.Balance_Date<=df_business_date.BUSINESS_DATE])\n",
    "input_source"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "337cedee-65fb-4d78-b9f6-b760903a841c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the build_dataset_time_series function which constructs a time series dataset based on the specified features and entity_id from the provided dataframe.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "140cf560-702e-4417-bf0d-29ac052da99d",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdfs4ds.SCHEMA = Param['database']\n",
    "tdfs4ds.DATA_DOMAIN = 'OUTSTANDING_AMOUNTS'\n",
    "from tdfs4ds.feature_store.feature_query_retrieval import get_feature_versions\n",
    "from tdfs4ds import build_dataset_time_series\n",
    "\n",
    "entity_id = ['Account_Id']\n",
    "features  = ['MEAN_MONTHLY_BALANCE','STD_MONTHLY_BALANCE','Z_SCORE','ANOMALY_FLAG']\n",
    "selected_features = get_feature_versions(entity_name=entity_id , features=features, domain = 'OUTSTANDING_AMOUNTS')\n",
    "#print(selected_features)\n",
    "df = build_dataset_time_series(input_source, 'Balance_Date', entity_id, selected_features, time_manager=business_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df1b63d8-27c1-4809-9636-ba198b71de99",
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88026f0f-878a-4e6f-8869-7895c63b7f48",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('ANOMALY_FLAG').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5125e721-b972-484e-94a8-5ead99b2f87b",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.2 What are the accounts with the most of anomalies?</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d725270d-5beb-479b-a5c8-4da6de7cb98d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.ANOMALY_FLAG == 1].groupby('Account_ID').count().sort('count_ANOMALY_FLAG', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d87df57c-f513-4803-af54-2dcd142827cd",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.3 We will take a closer look on the most suspicious account.</b></p>\n",
    "\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'><b>**Note: Please replace the account number in the cell below with the one which we would like to analyze</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "628118b6-6b8b-4bc1-a5fb-e027ce8fa9be",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "account_id = 370417"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "061ec123-9685-4391-bb6f-b98a4c33a283",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the required data is available to us in teradataml dataframe. Let's visualize this data to better understand the anomaly in each account. Vantage's Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantage and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6dfa5479-bfbe-4010-8a1d-927e09e26ee5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from plotanomalyfunction import plot_account_data\n",
    "plot_account_data(df,account_id )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42bb39aa-ecb3-49ea-a2fb-b4fb874cc9f8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above graph we can see that the green dots which are in between the dotted lines which are calculated by subtracting and adding 3 times the standard deviation of Monthly balance from the mean monthly balance. The red dots that lie outside these range are considered as anomalies.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e2c846d-4c85-45e0-b778-c7c319f6875b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, having an enterprise-curated set of Features, not only provides a decrease in the time to create and deploy models but also provides consistency in the definitions of the data. So, if a company makes its strategic decisions based on the insights it receives from the Data Warehouse and acts based on those insights. Ideally, the actions are data-driven, to affect an outcome. If the same data definitions are used to affect the outcome, as is used to gain the initial insights, then there is more likely to be aligned to the strategy, and the outcome will have a positive impact on the business.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65186aff-c6cf-433f-915d-a2de80b81564",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables and Views</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "992237c4-7281-4182-8621-5d42531d5163",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['FS_PROCESS_CATALOG','FS_FEATURE_CATALOG','FS_T_OUTSTANDING_AMOUNTS_Account_ID_VARCHAR_LATIN',\n",
    "          'tdfs__fgjnojnsmdoignmosnig','FS_T_OUTSTANDING_AMOUNTS_Account_ID_VARCHAR_UNICODE','temp',\n",
    "          'BUSINESS_DATE','FS_T_OUTSTANDING_AMOUNTS_Account_ID_FLOAT','FS_T_OUTSTANDING_AMOUNTS_Account_ID_BIGINT']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f939717d-4d5f-4d36-a073-193fd4404757",
   "metadata": {},
   "outputs": [],
   "source": [
    "views = ['anomaly_sub_1','anomaly','mean_std_sub_4','mean_std_efs','MEAN_STD_SUB_1','mean_std_sub_3','MEAN_STD_SUB_0',\n",
    "         'mean_std','PROCESS_0','MEAN_STD_SUB_2','FS_V_OUTSTANDING_AMOUNTS_Account_ID_VARCHAR_UNICODE',\n",
    "         'FS_V_OUTSTANDING_AMOUNTS_Account_ID_VARCHAR_LATIN','ml__aggregate_count__1711528657804036',\n",
    "         'FS_V_OUTSTANDING_AMOUNTS_Account_ID_BIGINT','PROCESS_0_sub_1','PROCESS_0_SUB_0',\n",
    "         'FS_V_OUTSTANDING_AMOUNTS_Account_ID_FLOAT','nb_points_efs']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for viewname in views:\n",
    "    try:\n",
    "        db_drop_view(view_name=viewname)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f3993ff-059e-4473-807c-12294424728c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e9e6cc1-9544-4839-b75d-4960801dd808",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_AnomalyDetection_EFS');\" \n",
    "#Takes 40 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c4fe20e-6cb6-472e-8cfc-59f9e0d9279a",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70018e32-6313-4b90-b5d1-e2c634500e7b",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Check more details regarding the tdfs4ds package functions <a href='https://pypi.org/project/tdfs4ds/'>here.</a></p>\n",
    "\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Filters:</b>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Finance</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Machine Learning</li> \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Anomaly Detection</li></p>\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Related Resources:</b>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Blogs/Hyper-scale-time-series-forecasting-done-right'>Hyper-scale time series forecasting done right</a> </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Blogs/NPS-is-a-metric-not-the-goal'>In the fight to improve customer experience, NPS is a metric, not the goal</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/Blogs/Customer-360-Analytics-What-Lies-Ahead'>Customer 360 Analytics, What Lies Ahead?</a></li>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69b7f69e-e6a3-4ae6-af78-e1b77208f309",
   "metadata": {
    "tags": []
   },
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2024. All Rights Reserved.\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}