crop_Growth_delta_archi.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "78d1ae17-9d03-4284-91ca-611684c62a43",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "I. Create a directory in storage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b34e31c4-6211-4989-b7c1-d08391583111",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Out[44]: True"
     ]
    }
   ],
   "source": [
    "dbutils.fs.mkdirs(\"dbfs:/FileStore/agri_data_archi\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "0a0272c8-7c52-4d4e-97e7-69b12c033a27",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "II. Read incoming file as dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "bfe26151-1217-469b-ad08-75f11960dc41",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.types import StructType, StructField, StringType, DoubleType, IntegerType, TimestampType\n",
    "\n",
    "\n",
    "schema = StructType([\n",
    "    StructField(\"GENOTYPES\", StringType(), True),\n",
    "    StructField(\"plant_height_30DAT_CM\", DoubleType(), True),\n",
    "    StructField(\"plant_height_60DAT_CM\", DoubleType(), True),\n",
    "    StructField(\"plant_height_90DAT_CM\", DoubleType(), True),\n",
    "    StructField(\"days_for_flower_initiation_DAYS\", IntegerType(), True),\n",
    "    StructField(\"no_primery_branches_Num\", DoubleType(), True),\n",
    "    StructField(\"fruit_length_cm\", DoubleType(), True),\n",
    "    StructField(\"fruit_circumference_mm\", DoubleType(), True),\n",
    "    StructField(\"avg_fruit_weight_gram\", DoubleType(), True),\n",
    "    StructField(\"no_fruits_plant\", DoubleType(), True),\n",
    "    StructField(\"fruit_TSS_Brix\", DoubleType(), True),\n",
    "    StructField(\"fruit_yield_plant_Kg\", DoubleType(), True),\n",
    "    StructField(\"yield_status\", StringType(), True),\n",
    "])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "7ab485ac-3c5a-4432-83a6-5cde4f368fa9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "yield_df = spark.read.option(\"mode\",\"failFast\").schema(schema).csv(\"dbfs:/FileStore/agridata/growth_and_growth_attribute.csv\", header = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "62ad9154-73e5-4b8f-bdb5-5454aeccc787",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "III. create and use database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "collapsed": true,
     "inputWidgets": {},
     "nuid": "96cc10ab-58fb-44bd-b2c8-ea77cf931dff",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "create database delta_growth_archi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2704fedb-3f50-44ac-ab3b-335e8ef1ef30",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Out[47]: DataFrame[]"
     ]
    }
   ],
   "source": [
    "spark.sql(\"use delta_growth_archi\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "ad0dd018-5bac-450b-8cd2-57fcffebf191",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Create tables for three tier architechture zones as bronze 1st, silver 2nd, and gold as 3rd. (specify location otherwise it will be stored in hive metastore)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a8c953c3-ad77-40d3-ac3f-d9119241640b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "%sql\n",
    "CREATE TABLE delta_growth_archi.plant_yield_data_bronze (\n",
    "    GENOTYPES STRING,\n",
    "    plant_height_30DAT_CM DOUBLE,\n",
    "    plant_height_60DAT_CM DOUBLE,\n",
    "    plant_height_90DAT_CM DOUBLE,\n",
    "    days_for_flower_initiation_DAYS INT,\n",
    "    no_primery_branches_Num DOUBLE,\n",
    "    fruit_length_cm DOUBLE,\n",
    "    fruit_circumference_mm DOUBLE,\n",
    "    avg_fruit_weight_gram DOUBLE,\n",
    "    no_fruits_plant DOUBLE,\n",
    "    fruit_TSS_Brix DOUBLE,\n",
    "    fruit_yield_plant_Kg DOUBLE,\n",
    "    yield_status STRING,\n",
    "    filename STRING,\n",
    "    created_on TIMESTAMP\n",
    ")\n",
    "USING DELTA\n",
    "LOCATION \"dbfs:/FileStore/agri_data_archi/growth_bronze.delta\"\n",
    "PARTITIONED BY (yield_status)\n",
    "TBLPROPERTIES (delta.enableChangeDataFeed = true);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e4e554a4-a42e-4a87-b3f0-0da1ce8778a5",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "CREATE TABLE delta_growth_archi.plant_yield_data_silver (\n",
    "    GENOTYPES STRING,\n",
    "    plant_height_30DAT_CM DOUBLE,\n",
    "    plant_height_60DAT_CM DOUBLE,\n",
    "    plant_height_90DAT_CM DOUBLE,\n",
    "    fruit_length_cm DOUBLE,\n",
    "    fruit_circumference_mm DOUBLE,\n",
    "    avg_fruit_weight_gram DOUBLE,\n",
    "    fruit_yield_plant_Kg DOUBLE,\n",
    "    yield_status STRING,\n",
    "    created_on TIMESTAMP,\n",
    "    modified_on TIMESTAMP\n",
    ")\n",
    "\n",
    "USING DELTA\n",
    "LOCATION \"dbfs:/FileStore/agri_data_archi/growth_silver.delta\"\n",
    "PARTITIONED BY (yield_status)\n",
    "TBLPROPERTIES (delta.enableChangeDataFeed = true);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "8141f451-5480-45f8-8359-2b49ae51b505",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "CREATE TABLE delta_growth_archi.plant_yield_data_gold (\n",
    "    GENOTYPES STRING,\n",
    "    average_plant_hieght_CM DOUBLE,\n",
    "    fruit_area_cm DOUBLE,\n",
    "    fruit_yield_plant_Kg DOUBLE,\n",
    "    yield_status STRING,\n",
    "    last_updated TIMESTAMP\n",
    ")\n",
    "USING DELTA\n",
    "LOCATION \"dbfs:/FileStore/agri_data_archi/growth_gold.delta\"\n",
    "PARTITIONED BY (yield_status)\n",
    "TBLPROPERTIES (delta.enableChangeDataFeed = true);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "68b73288-28ea-4cfb-9ae3-ae3b7214f5cd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>database</th><th>tableName</th><th>isTemporary</th></tr></thead><tbody><tr><td>delta_growth_archi</td><td>plant_yield_data</td><td>false</td></tr><tr><td>delta_growth_archi</td><td>plant_yield_data_bronze</td><td>false</td></tr><tr><td>delta_growth_archi</td><td>plant_yield_data_gold</td><td>false</td></tr><tr><td>delta_growth_archi</td><td>plant_yield_data_silver</td><td>false</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "delta_growth_archi",
         "plant_yield_data",
         false
        ],
        [
         "delta_growth_archi",
         "plant_yield_data_bronze",
         false
        ],
        [
         "delta_growth_archi",
         "plant_yield_data_gold",
         false
        ],
        [
         "delta_growth_archi",
         "plant_yield_data_silver",
         false
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "database",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "tableName",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "isTemporary",
         "type": "\"boolean\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(spark.sql(\"show tables\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "41ad3f2a-2616-467d-8477-6c772ef19cf8",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Migration of data in sequence from bronze layer to gold layer (aggregation are based on business logic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "150f83af-dea7-47d1-a4c6-a47c323090f2",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>num_affected_rows</th><th>num_inserted_rows</th><th>num_skipped_corrupt_files</th></tr></thead><tbody><tr><td>0</td><td>0</td><td>0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         0,
         0,
         0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "num_affected_rows",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "num_inserted_rows",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "num_skipped_corrupt_files",
         "type": "\"integer\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "-- copy command will load only new appended or added data instead of using load into or insert into since our bronze layer true source which will have all the info\n",
    "COPY INTO delta_growth_archi.plant_yield_data_bronze\n",
    "FROM (\n",
    "  SELECT \n",
    "    cast(GENOTYPES as STRING),\n",
    "    cast(plant_height_30DAT_CM as DOUBLE),\n",
    "    cast(plant_height_60DAT_CM as DOUBLE),\n",
    "    cast(plant_height_90DAT_CM as DOUBLE),\n",
    "    cast(days_for_flower_initiation_DAYS as int),\n",
    "    cast(no_primery_branches_Num as DOUBLE),\n",
    "    cast(fruit_length_cm as DOUBLE),\n",
    "    cast(fruit_circumference_mm as DOUBLE),\n",
    "    cast(avg_fruit_weight_gram as DOUBLE),\n",
    "    cast(no_fruits_plant as DOUBLE),\n",
    "    cast(fruit_TSS_Brix as DOUBLE),\n",
    "    cast(fruit_yield_plant_Kg as DOUBLE),\n",
    "    cast(yield_status as STRING),\n",
    "    input_file_name() as filename,\n",
    "    current_timestamp() as created_on\n",
    "  FROM 'dbfs:/FileStore/agridata'\n",
    ")\n",
    "FILEFORMAT = CSV\n",
    "FORMAT_OPTIONS ('header' = 'true')\n",
    "COPY_OPTIONS ('mergeSchema' = 'true');;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "04565098-8b3b-43bf-a923-90c004c290ca",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Track down for history"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c711ebef-2783-4032-8b1f-0d918f0bdfac",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>version</th><th>timestamp</th><th>userId</th><th>userName</th><th>operation</th><th>operationParameters</th><th>job</th><th>notebook</th><th>clusterId</th><th>readVersion</th><th>isolationLevel</th><th>isBlindAppend</th><th>operationMetrics</th><th>userMetadata</th><th>engineInfo</th></tr></thead><tbody><tr><td>2</td><td>2024-06-25T17:02:55.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>COPY INTO</td><td>Map()</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>1</td><td>WriteSerializable</td><td>true</td><td>Map(numFiles -> 4, numOutputRows -> 7, numOutputBytes -> 18703, numSkippedCorruptFiles -> 0)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>1</td><td>2024-06-25T14:06:01.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>COPY INTO</td><td>Map()</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>0</td><td>WriteSerializable</td><td>true</td><td>Map(numFiles -> 0, numOutputRows -> 0, numOutputBytes -> 0, numSkippedCorruptFiles -> 0)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>0</td><td>2024-06-25T14:01:59.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>CREATE TABLE</td><td>Map(isManaged -> false, description -> null, partitionBy -> [\"yield_status\"], properties -> {\"delta.enableChangeDataFeed\":\"true\"})</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>null</td><td>WriteSerializable</td><td>true</td><td>Map()</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         2,
         "2024-06-25T17:02:55.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "COPY INTO",
         {},
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         1,
         "WriteSerializable",
         true,
         {
          "numFiles": "4",
          "numOutputBytes": "18703",
          "numOutputRows": "7",
          "numSkippedCorruptFiles": "0"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         1,
         "2024-06-25T14:06:01.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "COPY INTO",
         {},
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         0,
         "WriteSerializable",
         true,
         {
          "numFiles": "0",
          "numOutputBytes": "0",
          "numOutputRows": "0",
          "numSkippedCorruptFiles": "0"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         0,
         "2024-06-25T14:01:59.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "CREATE TABLE",
         {
          "description": null,
          "isManaged": "false",
          "partitionBy": "[\"yield_status\"]",
          "properties": "{\"delta.enableChangeDataFeed\":\"true\"}"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         null,
         "WriteSerializable",
         true,
         {},
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "version",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "timestamp",
         "type": "\"timestamp\""
        },
        {
         "metadata": "{}",
         "name": "userId",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "userName",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "operation",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "operationParameters",
         "type": "{\"type\":\"map\",\"keyType\":\"string\",\"valueType\":\"string\",\"valueContainsNull\":true}"
        },
        {
         "metadata": "{}",
         "name": "job",
         "type": "{\"type\":\"struct\",\"fields\":[{\"name\":\"jobId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"jobName\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"jobRunId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"runId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"jobOwnerId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"triggerType\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}]}"
        },
        {
         "metadata": "{}",
         "name": "notebook",
         "type": "{\"type\":\"struct\",\"fields\":[{\"name\":\"notebookId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}]}"
        },
        {
         "metadata": "{}",
         "name": "clusterId",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "readVersion",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "isolationLevel",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "isBlindAppend",
         "type": "\"boolean\""
        },
        {
         "metadata": "{}",
         "name": "operationMetrics",
         "type": "{\"type\":\"map\",\"keyType\":\"string\",\"valueType\":\"string\",\"valueContainsNull\":true}"
        },
        {
         "metadata": "{}",
         "name": "userMetadata",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "engineInfo",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "-- in function we can see no affected rows beacuse of multiple copy command run (version 1)\n",
    "-- second entry  with new_data (version 2)\n",
    "describe history delta_growth_archi.plant_yield_data_bronze"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "3ba91075-b4e2-4d45-9b8a-5fd17eafd514",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "from pyspark.sql.functions import col\n",
    "from delta.tables import DeltaTable\n",
    "\n",
    "df1 = delta_table.history() \\\n",
    "    .select(\"operationMetrics.numOutputRows\") \\\n",
    "    .where(col(\"operation\") == \"WRITE\") \\\n",
    "    .orderBy(col(\"timestamp\").desc()) \\\n",
    "    .take(2)\n",
    "\n",
    "# Extract the number of output rows\n",
    "list_no_rows = [int(i.numOutputRows) for i in df1]\n",
    "\n",
    "# Compare the number of rows and print a message if necessary\n",
    "if abs(list_no_rows[0] - list_no_rows[1]) >= 4:\n",
    "    print(\"Please check your data\")\n",
    "else:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "fc78285c-fd55-4d0e-a967-1241aa17f809",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Instead of overwriting whole data use temp view and merge the selected data into silver table (this layer will as data warehouse for several use case for our purpose)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "866c4242-eafd-4bd2-86fa-5a16c954a7b3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "-- version 1\n",
    "CREATE OR REPLACE TEMPORARY VIEW updated_growth_data AS\n",
    "SELECT * \n",
    "FROM \n",
    "  table_changes('delta_growth_archi.plant_yield_data_bronze', 1);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f46a75a3-695b-482d-b55d-a017fec15332",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "-- version 2\n",
    "CREATE OR REPLACE TEMPORARY VIEW updated_growth_data AS\n",
    "SELECT * \n",
    "FROM \n",
    "  table_changes('delta_growth_archi.plant_yield_data_bronze', 2);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "58aae803-9351-44cf-b79a-78f62bf67d8c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>GENOTYPES</th><th>plant_height_30DAT_CM</th><th>plant_height_60DAT_CM</th><th>plant_height_90DAT_CM</th><th>days_for_flower_initiation_DAYS</th><th>no_primery_branches_Num</th><th>fruit_length_cm</th><th>fruit_circumference_mm</th><th>avg_fruit_weight_gram</th><th>no_fruits_plant</th><th>fruit_TSS_Brix</th><th>fruit_yield_plant_Kg</th><th>yield_status</th><th>filename</th><th>created_on</th></tr></thead><tbody><tr><td>G6</td><td>31.68</td><td>28.71</td><td>38.61</td><td>35</td><td>10.48</td><td>9.64</td><td>19.69</td><td>56.84</td><td>5.94</td><td>5.58</td><td>341.04</td><td>High</td><td>dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv</td><td>2024-06-25T17:02:49.337+0000</td></tr><tr><td>G102</td><td>29.35</td><td>46.98</td><td>54.52</td><td>28</td><td>6.35</td><td>8.45</td><td>17.9</td><td>90.09</td><td>0.53</td><td>4.55</td><td>165.59</td><td>High</td><td>dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv</td><td>2024-06-25T17:02:49.337+0000</td></tr><tr><td>G1</td><td>47.47</td><td>52.52</td><td>63.63</td><td>35</td><td>11.39</td><td>6.21</td><td>12.63</td><td>33.89</td><td>6.06</td><td>5.95</td><td>203.34</td><td>Medium</td><td>dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv</td><td>2024-06-25T17:02:49.337+0000</td></tr><tr><td>G101</td><td>29.35</td><td>46.98</td><td>54.52</td><td>28</td><td>6.35</td><td>8.45</td><td>17.9</td><td>90.09</td><td>0.53</td><td>4.55</td><td>165.59</td><td>Medium</td><td>dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv</td><td>2024-06-25T17:02:49.337+0000</td></tr><tr><td>G74</td><td>37.83</td><td>54.32</td><td>63.05</td><td>30</td><td>7.84</td><td>7.03</td><td>14.82</td><td>70.3</td><td>1.94</td><td>4.99</td><td>140.6</td><td>Low</td><td>dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv</td><td>2024-06-25T17:02:49.337+0000</td></tr><tr><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv</td><td>2024-06-25T17:02:49.337+0000</td></tr><tr><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv</td><td>2024-06-25T17:02:49.337+0000</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "G6",
         31.68,
         28.71,
         38.61,
         35,
         10.48,
         9.64,
         19.69,
         56.84,
         5.94,
         5.58,
         341.04,
         "High",
         "dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv",
         "2024-06-25T17:02:49.337+0000"
        ],
        [
         "G102",
         29.35,
         46.98,
         54.52,
         28,
         6.35,
         8.45,
         17.9,
         90.09,
         0.53,
         4.55,
         165.59,
         "High",
         "dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv",
         "2024-06-25T17:02:49.337+0000"
        ],
        [
         "G1",
         47.47,
         52.52,
         63.63,
         35,
         11.39,
         6.21,
         12.63,
         33.89,
         6.06,
         5.95,
         203.34,
         "Medium",
         "dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv",
         "2024-06-25T17:02:49.337+0000"
        ],
        [
         "G101",
         29.35,
         46.98,
         54.52,
         28,
         6.35,
         8.45,
         17.9,
         90.09,
         0.53,
         4.55,
         165.59,
         "Medium",
         "dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv",
         "2024-06-25T17:02:49.337+0000"
        ],
        [
         "G74",
         37.83,
         54.32,
         63.05,
         30,
         7.84,
         7.03,
         14.82,
         70.3,
         1.94,
         4.99,
         140.6,
         "Low",
         "dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv",
         "2024-06-25T17:02:49.337+0000"
        ],
        [
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         "dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv",
         "2024-06-25T17:02:49.337+0000"
        ],
        [
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         "dbfs:/FileStore/agridata/growth_and_growth_attribute-1.csv",
         "2024-06-25T17:02:49.337+0000"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "GENOTYPES",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "plant_height_30DAT_CM",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "plant_height_60DAT_CM",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "plant_height_90DAT_CM",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "days_for_flower_initiation_DAYS",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "no_primery_branches_Num",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "fruit_length_cm",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "fruit_circumference_mm",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "avg_fruit_weight_gram",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "no_fruits_plant",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "fruit_TSS_Brix",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "fruit_yield_plant_Kg",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "yield_status",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "filename",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "created_on",
         "type": "\"timestamp\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "SELECT * FROM delta_growth_archi.plant_yield_data_bronze VERSION AS OF 2;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "aa800c87-e445-4eda-9ccb-748399d4e26c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>num_affected_rows</th><th>num_updated_rows</th><th>num_deleted_rows</th><th>num_inserted_rows</th></tr></thead><tbody><tr><td>7</td><td>5</td><td>0</td><td>2</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         7,
         5,
         0,
         2
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "num_affected_rows",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "num_updated_rows",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "num_deleted_rows",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "num_inserted_rows",
         "type": "\"long\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "-- merging condintion for only new update and inserted data and remaining will be same \n",
    "MERGE INTO delta_growth_archi.plant_yield_data_silver PYDS\n",
    "USING updated_growth_data UGDB\n",
    "ON PYDS.GENOTYPES = UGDB.GENOTYPES\n",
    "WHEN MATCHED THEN \n",
    "UPDATE \n",
    "SET \n",
    "    PYDS.plant_height_30DAT_CM = UGDB.plant_height_30DAT_CM, \n",
    "    PYDS.plant_height_60DAT_CM = UGDB.plant_height_60DAT_CM, \n",
    "    PYDS.plant_height_90DAT_CM = UGDB.plant_height_90DAT_CM, \n",
    "    PYDS.fruit_length_cm = UGDB.fruit_length_cm, \n",
    "    PYDS.fruit_circumference_mm = UGDB.fruit_circumference_mm, \n",
    "    PYDS.avg_fruit_weight_gram = UGDB.avg_fruit_weight_gram, \n",
    "    PYDS.fruit_yield_plant_Kg = UGDB.fruit_yield_plant_Kg, \n",
    "    PYDS.yield_status = UGDB.yield_status, \n",
    "    PYDS.created_on = current_timestamp()\n",
    "WHEN NOT MATCHED THEN \n",
    "INSERT (\n",
    "    GENOTYPES,\n",
    "    plant_height_30DAT_CM,\n",
    "    plant_height_60DAT_CM,\n",
    "    plant_height_90DAT_CM,\n",
    "    fruit_length_cm,\n",
    "    fruit_circumference_mm,\n",
    "    avg_fruit_weight_gram,\n",
    "    fruit_yield_plant_Kg,\n",
    "    yield_status,\n",
    "    created_on,\n",
    "    modified_on\n",
    ") \n",
    "VALUES (\n",
    "    GENOTYPES,\n",
    "    plant_height_30DAT_CM,\n",
    "    plant_height_60DAT_CM,\n",
    "    plant_height_90DAT_CM,\n",
    "    fruit_length_cm,\n",
    "    fruit_circumference_mm,\n",
    "    avg_fruit_weight_gram,\n",
    "    fruit_yield_plant_Kg,\n",
    "    yield_status,\n",
    "    current_timestamp(), \n",
    "    current_timestamp()\n",
    ");\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "107b6683-ed0a-4221-a85b-44a74cc5985a",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>GENOTYPES</th><th>plant_height_30DAT_CM</th><th>plant_height_60DAT_CM</th><th>plant_height_90DAT_CM</th><th>fruit_length_cm</th><th>fruit_circumference_mm</th><th>avg_fruit_weight_gram</th><th>fruit_yield_plant_Kg</th><th>yield_status</th><th>created_on</th><th>modified_on</th></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "GENOTYPES",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "plant_height_30DAT_CM",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "plant_height_60DAT_CM",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "plant_height_90DAT_CM",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "fruit_length_cm",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "fruit_circumference_mm",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "avg_fruit_weight_gram",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "fruit_yield_plant_Kg",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "yield_status",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "created_on",
         "type": "\"timestamp\""
        },
        {
         "metadata": "{}",
         "name": "modified_on",
         "type": "\"timestamp\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "select * from delta_growth_archi.plant_yield_data_silver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "collapsed": true,
     "inputWidgets": {},
     "nuid": "6eb46772-d1c8-4409-9612-4f3e1516501a",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>version</th><th>timestamp</th><th>userId</th><th>userName</th><th>operation</th><th>operationParameters</th><th>job</th><th>notebook</th><th>clusterId</th><th>readVersion</th><th>isolationLevel</th><th>isBlindAppend</th><th>operationMetrics</th><th>userMetadata</th><th>engineInfo</th></tr></thead><tbody><tr><td>10</td><td>2024-06-25T17:25:50.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#30243 = GENOTYPES#30196)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>9</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 4, numTargetBytesAdded -> 13599, numTargetBytesRemoved -> 10590, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 5, executionTimeMs -> 8570, numTargetRowsInserted -> 2, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 2505, numTargetRowsUpdated -> 5, numOutputRows -> 7, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 4, numSourceRows -> 7, numTargetFilesRemoved -> 3, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 3716)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>9</td><td>2024-06-25T17:25:14.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#28637 = GENOTYPES#28590)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>8</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 4, numTargetBytesAdded -> 13604, numTargetBytesRemoved -> 10590, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 5, executionTimeMs -> 10464, numTargetRowsInserted -> 2, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 3131, numTargetRowsUpdated -> 5, numOutputRows -> 7, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 4, numSourceRows -> 7, numTargetFilesRemoved -> 3, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 4871)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>8</td><td>2024-06-25T17:24:33.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#27031 = GENOTYPES#26984)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>7</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 4, numTargetBytesAdded -> 13604, numTargetBytesRemoved -> 9939, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 5, executionTimeMs -> 11120, numTargetRowsInserted -> 2, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 3607, numTargetRowsUpdated -> 5, numOutputRows -> 7, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 4, numSourceRows -> 7, numTargetFilesRemoved -> 3, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 5323)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>7</td><td>2024-06-25T17:24:06.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#25741 = GENOTYPES#25694)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>6</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 4, numTargetBytesAdded -> 12736, numTargetBytesRemoved -> 0, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 0, executionTimeMs -> 5746, numTargetRowsInserted -> 7, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 1269, numTargetRowsUpdated -> 0, numOutputRows -> 7, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 0, numSourceRows -> 7, numTargetFilesRemoved -> 0, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 2538)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>6</td><td>2024-06-25T17:23:18.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#24649 = GENOTYPES#24592)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>5</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 0, numTargetBytesAdded -> 0, numTargetBytesRemoved -> 0, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 0, executionTimeMs -> 3652, numTargetRowsInserted -> 0, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 1178, numTargetRowsUpdated -> 0, numOutputRows -> 0, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 0, numSourceRows -> 0, numTargetFilesRemoved -> 0, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 1466)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>5</td><td>2024-06-25T17:21:45.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#23541 = GENOTYPES#23484)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>4</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 0, numTargetBytesAdded -> 0, numTargetBytesRemoved -> 0, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 0, executionTimeMs -> 3178, numTargetRowsInserted -> 0, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 730, numTargetRowsUpdated -> 0, numOutputRows -> 0, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 0, numSourceRows -> 0, numTargetFilesRemoved -> 0, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 1380)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>4</td><td>2024-06-25T17:17:09.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#22145 = GENOTYPES#22088)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>3</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 0, numTargetBytesAdded -> 0, numTargetBytesRemoved -> 0, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 0, executionTimeMs -> 3319, numTargetRowsInserted -> 0, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 763, numTargetRowsUpdated -> 0, numOutputRows -> 0, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 0, numSourceRows -> 0, numTargetFilesRemoved -> 0, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 1284)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>3</td><td>2024-06-25T17:08:59.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#18725 = GENOTYPES#18668)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>2</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 0, numTargetBytesAdded -> 0, numTargetBytesRemoved -> 0, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 0, executionTimeMs -> 3309, numTargetRowsInserted -> 0, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 892, numTargetRowsUpdated -> 0, numOutputRows -> 0, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 0, numSourceRows -> 0, numTargetFilesRemoved -> 0, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 1077)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>2</td><td>2024-06-25T17:08:38.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#17194 = GENOTYPES#17137)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>1</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 0, numTargetBytesAdded -> 0, numTargetBytesRemoved -> 0, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 0, executionTimeMs -> 13007, numTargetRowsInserted -> 0, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 1272, numTargetRowsUpdated -> 0, numOutputRows -> 0, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 0, numSourceRows -> 0, numTargetFilesRemoved -> 0, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 1137)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>1</td><td>2024-06-25T14:46:03.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>MERGE</td><td>Map(predicate -> [\"(GENOTYPES#7876 = GENOTYPES#7819)\"], matchedPredicates -> [{\"actionType\":\"update\"}], notMatchedPredicates -> [{\"actionType\":\"insert\"}], notMatchedBySourcePredicates -> [])</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>0</td><td>WriteSerializable</td><td>false</td><td>Map(numTargetRowsCopied -> 0, numTargetRowsDeleted -> 0, numTargetFilesAdded -> 0, numTargetBytesAdded -> 0, numTargetBytesRemoved -> 0, numTargetDeletionVectorsAdded -> 0, numTargetRowsMatchedUpdated -> 0, executionTimeMs -> 5142, numTargetRowsInserted -> 0, numTargetRowsMatchedDeleted -> 0, scanTimeMs -> 2705, numTargetRowsUpdated -> 0, numOutputRows -> 0, numTargetDeletionVectorsRemoved -> 0, numTargetRowsNotMatchedBySourceUpdated -> 0, numTargetChangeFilesAdded -> 0, numSourceRows -> 0, numTargetFilesRemoved -> 0, numTargetRowsNotMatchedBySourceDeleted -> 0, rewriteTimeMs -> 886)</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr><tr><td>0</td><td>2024-06-25T14:02:08.000+0000</td><td>4514929696160</td><td>faizanscf@gmail.com</td><td>CREATE TABLE</td><td>Map(isManaged -> false, description -> null, partitionBy -> [\"yield_status\"], properties -> {\"delta.enableChangeDataFeed\":\"true\"})</td><td>null</td><td>List(2011140080832843)</td><td>0625-121832-cmnnvtxp</td><td>null</td><td>WriteSerializable</td><td>true</td><td>Map()</td><td>null</td><td>Databricks-Runtime/12.2.x-scala2.12</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         10,
         "2024-06-25T17:25:50.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#30243 = GENOTYPES#30196)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         9,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "8570",
          "numOutputRows": "7",
          "numSourceRows": "7",
          "numTargetBytesAdded": "13599",
          "numTargetBytesRemoved": "10590",
          "numTargetChangeFilesAdded": "4",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "4",
          "numTargetFilesRemoved": "3",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "2",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "5",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "5",
          "rewriteTimeMs": "3716",
          "scanTimeMs": "2505"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         9,
         "2024-06-25T17:25:14.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#28637 = GENOTYPES#28590)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         8,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "10464",
          "numOutputRows": "7",
          "numSourceRows": "7",
          "numTargetBytesAdded": "13604",
          "numTargetBytesRemoved": "10590",
          "numTargetChangeFilesAdded": "4",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "4",
          "numTargetFilesRemoved": "3",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "2",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "5",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "5",
          "rewriteTimeMs": "4871",
          "scanTimeMs": "3131"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         8,
         "2024-06-25T17:24:33.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#27031 = GENOTYPES#26984)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         7,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "11120",
          "numOutputRows": "7",
          "numSourceRows": "7",
          "numTargetBytesAdded": "13604",
          "numTargetBytesRemoved": "9939",
          "numTargetChangeFilesAdded": "4",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "4",
          "numTargetFilesRemoved": "3",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "2",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "5",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "5",
          "rewriteTimeMs": "5323",
          "scanTimeMs": "3607"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         7,
         "2024-06-25T17:24:06.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#25741 = GENOTYPES#25694)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         6,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "5746",
          "numOutputRows": "7",
          "numSourceRows": "7",
          "numTargetBytesAdded": "12736",
          "numTargetBytesRemoved": "0",
          "numTargetChangeFilesAdded": "0",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "4",
          "numTargetFilesRemoved": "0",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "7",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "0",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "0",
          "rewriteTimeMs": "2538",
          "scanTimeMs": "1269"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         6,
         "2024-06-25T17:23:18.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#24649 = GENOTYPES#24592)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         5,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "3652",
          "numOutputRows": "0",
          "numSourceRows": "0",
          "numTargetBytesAdded": "0",
          "numTargetBytesRemoved": "0",
          "numTargetChangeFilesAdded": "0",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "0",
          "numTargetFilesRemoved": "0",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "0",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "0",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "0",
          "rewriteTimeMs": "1466",
          "scanTimeMs": "1178"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         5,
         "2024-06-25T17:21:45.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#23541 = GENOTYPES#23484)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         4,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "3178",
          "numOutputRows": "0",
          "numSourceRows": "0",
          "numTargetBytesAdded": "0",
          "numTargetBytesRemoved": "0",
          "numTargetChangeFilesAdded": "0",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "0",
          "numTargetFilesRemoved": "0",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "0",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "0",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "0",
          "rewriteTimeMs": "1380",
          "scanTimeMs": "730"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         4,
         "2024-06-25T17:17:09.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#22145 = GENOTYPES#22088)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         3,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "3319",
          "numOutputRows": "0",
          "numSourceRows": "0",
          "numTargetBytesAdded": "0",
          "numTargetBytesRemoved": "0",
          "numTargetChangeFilesAdded": "0",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "0",
          "numTargetFilesRemoved": "0",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "0",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "0",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "0",
          "rewriteTimeMs": "1284",
          "scanTimeMs": "763"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         3,
         "2024-06-25T17:08:59.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#18725 = GENOTYPES#18668)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         2,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "3309",
          "numOutputRows": "0",
          "numSourceRows": "0",
          "numTargetBytesAdded": "0",
          "numTargetBytesRemoved": "0",
          "numTargetChangeFilesAdded": "0",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "0",
          "numTargetFilesRemoved": "0",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "0",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "0",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "0",
          "rewriteTimeMs": "1077",
          "scanTimeMs": "892"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         2,
         "2024-06-25T17:08:38.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#17194 = GENOTYPES#17137)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         1,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "13007",
          "numOutputRows": "0",
          "numSourceRows": "0",
          "numTargetBytesAdded": "0",
          "numTargetBytesRemoved": "0",
          "numTargetChangeFilesAdded": "0",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "0",
          "numTargetFilesRemoved": "0",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "0",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "0",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "0",
          "rewriteTimeMs": "1137",
          "scanTimeMs": "1272"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         1,
         "2024-06-25T14:46:03.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "MERGE",
         {
          "matchedPredicates": "[{\"actionType\":\"update\"}]",
          "notMatchedBySourcePredicates": "[]",
          "notMatchedPredicates": "[{\"actionType\":\"insert\"}]",
          "predicate": "[\"(GENOTYPES#7876 = GENOTYPES#7819)\"]"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         0,
         "WriteSerializable",
         false,
         {
          "executionTimeMs": "5142",
          "numOutputRows": "0",
          "numSourceRows": "0",
          "numTargetBytesAdded": "0",
          "numTargetBytesRemoved": "0",
          "numTargetChangeFilesAdded": "0",
          "numTargetDeletionVectorsAdded": "0",
          "numTargetDeletionVectorsRemoved": "0",
          "numTargetFilesAdded": "0",
          "numTargetFilesRemoved": "0",
          "numTargetRowsCopied": "0",
          "numTargetRowsDeleted": "0",
          "numTargetRowsInserted": "0",
          "numTargetRowsMatchedDeleted": "0",
          "numTargetRowsMatchedUpdated": "0",
          "numTargetRowsNotMatchedBySourceDeleted": "0",
          "numTargetRowsNotMatchedBySourceUpdated": "0",
          "numTargetRowsUpdated": "0",
          "rewriteTimeMs": "886",
          "scanTimeMs": "2705"
         },
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ],
        [
         0,
         "2024-06-25T14:02:08.000+0000",
         "4514929696160",
         "faizanscf@gmail.com",
         "CREATE TABLE",
         {
          "description": null,
          "isManaged": "false",
          "partitionBy": "[\"yield_status\"]",
          "properties": "{\"delta.enableChangeDataFeed\":\"true\"}"
         },
         null,
         [
          "2011140080832843"
         ],
         "0625-121832-cmnnvtxp",
         null,
         "WriteSerializable",
         true,
         {},
         null,
         "Databricks-Runtime/12.2.x-scala2.12"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "version",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "timestamp",
         "type": "\"timestamp\""
        },
        {
         "metadata": "{}",
         "name": "userId",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "userName",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "operation",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "operationParameters",
         "type": "{\"type\":\"map\",\"keyType\":\"string\",\"valueType\":\"string\",\"valueContainsNull\":true}"
        },
        {
         "metadata": "{}",
         "name": "job",
         "type": "{\"type\":\"struct\",\"fields\":[{\"name\":\"jobId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"jobName\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"jobRunId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"runId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"jobOwnerId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"triggerType\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}]}"
        },
        {
         "metadata": "{}",
         "name": "notebook",
         "type": "{\"type\":\"struct\",\"fields\":[{\"name\":\"notebookId\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}]}"
        },
        {
         "metadata": "{}",
         "name": "clusterId",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "readVersion",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "isolationLevel",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "isBlindAppend",
         "type": "\"boolean\""
        },
        {
         "metadata": "{}",
         "name": "operationMetrics",
         "type": "{\"type\":\"map\",\"keyType\":\"string\",\"valueType\":\"string\",\"valueContainsNull\":true}"
        },
        {
         "metadata": "{}",
         "name": "userMetadata",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "engineInfo",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "describe history delta_growth_archi.plant_yield_data_silver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "080f319b-c6aa-4b26-b685-2d2eeb816037",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>num_affected_rows</th><th>num_inserted_rows</th></tr></thead><tbody><tr><td>6</td><td>6</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         6,
         6
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "num_affected_rows",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "num_inserted_rows",
         "type": "\"long\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "INSERT OVERWRITE TABLE delta_growth_archi.plant_yield_data_gold\n",
    "-- gold layer will be for dash boarding with high level of aggreagtion (serving layer for some use case)\n",
    "SELECT \n",
    "    GENOTYPES, \n",
    "    AVG((plant_height_30DAT_CM + plant_height_60DAT_CM + plant_height_90DAT_CM) / 3) AS average_plant_height_CM, \n",
    "    SUM((fruit_circumference_mm / 10) * fruit_length_cm) AS fruit_area_cm, \n",
    "    SUM(fruit_yield_plant_Kg) AS total_fruit_yield_plant_Kg,\n",
    "    yield_status, \n",
    "    current_timestamp() AS last_updated\n",
    "FROM \n",
    "    delta_growth_archi.plant_yield_data_silver\n",
    "GROUP BY \n",
    "    GENOTYPES, \n",
    "    yield_status;\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql\n",
    "describe history delta_growth_archi.plant_yield_data_gold"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "2f037e71-5685-4cd6-8a2b-26475f5b6761",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## lets work on one usecase where we update and add new data from backend with same name = \n",
    "####dbfs:/FileStore/agridata/growth_and_growth_attribute.csv\n",
    "## analyse the version once again through migrating  data"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": null,
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 1482034367046535,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "crop_Growth_delta_archi",
   "widgets": {}
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}