stable_ndts.view.lookml

### This should only run if the session facts table is empty
view: session_facts_full_load {
  derived_table: {
    sql_trigger_value: select case when count(distinct partition_date) < 1 then "full re-build" else "daily incremental load" end from ${session_facts_pdt.SQL_TABLE_NAME} ;;
    explore_source: ga_sessions {
      column: id {}
      column: partition_raw {}
      column: keyword_count { field: trafficSource.keyword_count }
      column: add_to__cart { field: hits_eventInfo.add_to__cart }
      filters: {
        field: ga_sessions.partition_date
        value: "" #no filter on partition date, build it all!
      }
    }
  }
}

### This should every day
view: session_facts_yesterday {
    derived_table: {
     sql_trigger_value: select CURRENT_DATE() ;; #every day build yesterday's table
      explore_source: ga_sessions {
        column: id {}
        column: partition_raw {}
        column: keyword_count { field: trafficSource.keyword_count }
        column: add_to__cart { field: hits_eventInfo.add_to__cart }
        filters: {
          field: ga_sessions.partition_date
          value: "1 day ago"
        }
      }
    }
  }


###
view: session_facts_pdt {
  derived_table: {
    publish_as_db_view: yes # make it stable!
    sql_trigger_value: select max(partition_date) from ${session_facts_yesterday.SQL_TABLE_NAME}  ;;
    create_process: {
      sql_step: CREATE TABLE IF NOT EXISTS ${SQL_TABLE_NAME} PARTITION BY DATE(partition_raw) as select * from ${session_facts_yesterday.SQL_TABLE_NAME} WHERE 1=2  ;;
      ### to do - skip the next step if we're doing incremental load
      #sql_step: INSERT INTO ${SQL_TABLE_NAME} SELECT * FROM ${session_facts_full_load.SQL_TABLE_NAME}  ;;
      sql_step: INSERT INTO ${SQL_TABLE_NAME} 
                  SELECT yesterday.* FROM ${session_facts_yesterday.SQL_TABLE_NAME} yesterday
                    LEFT JOIN ${SQL_TABLE_NAME} full_table ON yesterday.id = full_table.id
                    WHERE full_table.id IS NULL;; #only values not matched by join
      
    }

  }
  dimension: id {
    label: "Session ID"
  }
  dimension: keyword_count {
    label: "Session: Traffic Source Keyword Count"
    type: number
  }
  dimension: add_to__cart {
    label: "Session: Hits: Events Info Add to Cart"
    type: number
  }
}