## Updates the fact_* tables by 
##  * SF: cloning the current fact_* table in snowflake to a temp_working table
##     (data will be written to the clone'd temp table, then it will be swapped in)
##  * SF: checking the max(processeddaytime) in Snowflake
##  * RS: unloading data in redshift that with processeddaytime > max(snowflake processeddaytime)
##  * SF: inserting the unloaded data into the temp table
##  * SF: archiving the current fact_* table from snowflake
##  * SF: swapping in the temp table as the new fact_* table 

wh = "LOOKER_WH_LARGE"

tbls = c("fact_analytics", "fact_sales")
schema = "production"
dbname = "prod"
transient = TRUE

.us()

&&& TODO:  integreate ctc (aks colToCheck[[tbl]]) everywhere that is currently processeddaytime
colToCheck <- c(fact_analytics="processeddaytime", fact_sales="accountingperiodid")

## Iterate over each table
for (tbl in tbls) {

  ctc <- colToCheck[[tbl]]

  ## TEMPRORARY BUCKET -- using Rick's Space
  bucket = s3_p("FACT_Tables", "incremental_updates", tbl, timeStamp(), warn_on_as.path=FALSE)

  qry_timecheck = sprintf("SELECT max(processeddaytime) AS processeddaytime FROM %s.%s", schema, tbl)
  latest_processeddaytime_sf = sfQry(qry=qry_timecheck, wh=)
  latest_processeddaytime_rs = runQry(qry=qry_timecheck, cluster=4)

  ## IF dates are the same, do nothing today
  if (latest_processeddaytime_sf == latest_processeddaytime_rs) {
      message(sprintf("No updates needed for %s.\nLatest processeddaytime is %s", tbl, timeStamp(time=latest_processeddaytime_sf, human=TRUE)))
      next;
  } else {

    ## ~~~~~~~~~ REDSHIFT ~~~~~~~~~~~~~~ ##
      options(snowflake_inuse = FALSE)  ## let the program know we are not using snowflake yet
      setDBall(cluster=4) ## run off of cluster 4

      ## UNLOAD RS to S3 Bucket
      qry_unload = sprintf("SELECT * FROM %s.%s WHERE processeddaytime > '%s'", schema, tbl, format(latest_processeddaytime_sf))
      unloadQry(qry_unload, bucket=bucket, delimiter="\v")

    ## ~~~~~~~~~ SNOWFLAKE ~~~~~~~~~~~~~~ ##
      options(snowflake_inuse = TRUE) ## let the program know we are switching to snowflake now

      ## Name of temp table to insert data into
      tbl_temp = sprintf("%s_temp_%s", tbl, timeStamp(time=now(), seconds=TRUE))

      ## CLONE THE TABLE INTO A TEMP 
      qry_clone = sprintf("CREATE OR REPLACE %1$s TABLE %2$s.%3$s CLONE %2$s.%4$s", ifelse(transient, "TRANSIENT", ""), schema, tbl_temp, tbl)
      sfQry(qry_clone)

      ## Insert the data into snowflake
      sfPopulateTable(tb=tbl_temp, cmd="COPY", schema=schema, dbname=dbname, bucket=bucket, format_name="RICKS_VTAB_REDSHIFT_DUMP", validation_mode=FALSE)

      ## Swap the tables, archiving the fact_table
      swap_in_new_table(tbl_new=tbl_temp, tbl_old=tbl, zArchive=TRUE, schema=schema, dbname=dbname, transient=transient)
  }

}