dbt Media publishing

Click New

Click New model for media publishing.

inferred

Blocks

dbt SQL LKML LookML

click_new.sql · 83 lines

inferred

-- models/click_new.sql
{{ config(materialized='view') }}

SELECT
  click.click_id,
  click.parent_visit AS parent_visit_id,
  click.click_duration AS "click_duration",
  click.click_number AS "click_sequence",
  click.internal_search AS "internal_search",
  click.is_constructed AS "is_constructed",
  click.is_first AS "is_first",
  click.is_last  AS "is_last",
  click.line_number AS "line_number",
  click.link_traversal_index AS "link_traversal_index",
  click.number_of_requests AS "number_of_requests",
  click.page_bytes_sent AS "page_bytes_sent",
  click.page_visit_index AS "page_visit_index",
  click.processing_time AS "processing_time",
  click.referral_url AS "referral_url",
  click.source_batch AS "source_batch",
  click.status_code AS "status_code",
  click.timestamp AS "timestamp_date",
  click.total_bytes_sent AS "total_bytes_sent",
  click_general.country_code AS "country_code",
  click_general.edition AS "edition",
  click_general.edition_date AS "edition_date",
  click_general.load_time_ms AS "load_time_ms",
  click_general.navigational AS "is_navigational",
  click_general.num_comments AS "num_comments",
  click_general.num_likes AS "num_likes",
  click_general.num_mentions AS "num_mentions",
  click_general.num_share_facebook AS "num_share_facebook",
  click_general.num_share_twitter AS "num_share_twitter",
  click_general.pid AS "pid",
  click_general.segid AS "segid",
  click_general.segment_id AS "segment_id",
  click_general.sub_type AS "sub_type",
  click_general.video AS "is_video",
  click_general.weekend  AS "is_weekend"
FROM sanitized_db.si_cs_click_recent AS click
LEFT JOIN sanitized_db.sanitized_click_general AS click_general ON click.click_id = click_general.click_id
WHERE
  ((click.timestamp) >= (DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) )) AND (click.timestamp) < (DATEADD(day,90, DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) ) )))

# models/schema.yml
version: 2

models:
  - name: click_new
    columns:
      - name: click_duration
      - name: parent_visit_id
      - name: click_sequence
      - name: internal_search
      - name: is_constructed
      - name: is_first
      - name: is_last
      - name: line_number
      - name: link_traversal_index
      - name: number_of_requests
      - name: page_bytes_sent
      - name: page_visit_index
      - name: processing_time
      - name: referral_url
      - name: source_batch
      - name: status_code
      - name: total_bytes_sent
      - name: country_code
      - name: edition
      - name: edition_date
      - name: load_time_ms
      - name: is_navigational
      - name: num_comments
      - name: num_likes
      - name: num_mentions
      - name: num_share_facebook
      - name: num_share_twitter
      - name: pid
      - name: segid
      - name: segment_id
      - name: sub_type
      - name: is_video
      - name: is_weekend

Assumptions

materialized='view' is the default; no persist hint found.
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

click_new.sql · 40 lines

source-preserved validation: parses

SELECT
  click.click_id,
  click.parent_visit AS parent_visit_id,
  click.click_duration AS "click_duration",
  click.click_number AS "click_sequence",
  click.internal_search AS "internal_search",
  click.is_constructed AS "is_constructed",
  click.is_first AS "is_first",
  click.is_last  AS "is_last",
  click.line_number AS "line_number",
  click.link_traversal_index AS "link_traversal_index",
  click.number_of_requests AS "number_of_requests",
  click.page_bytes_sent AS "page_bytes_sent",
  click.page_visit_index AS "page_visit_index",
  click.processing_time AS "processing_time",
  click.referral_url AS "referral_url",
  click.source_batch AS "source_batch",
  click.status_code AS "status_code",
  click.timestamp AS "timestamp_date",
  click.total_bytes_sent AS "total_bytes_sent",
  click_general.country_code AS "country_code",
  click_general.edition AS "edition",
  click_general.edition_date AS "edition_date",
  click_general.load_time_ms AS "load_time_ms",
  click_general.navigational AS "is_navigational",
  click_general.num_comments AS "num_comments",
  click_general.num_likes AS "num_likes",
  click_general.num_mentions AS "num_mentions",
  click_general.num_share_facebook AS "num_share_facebook",
  click_general.num_share_twitter AS "num_share_twitter",
  click_general.pid AS "pid",
  click_general.segid AS "segid",
  click_general.segment_id AS "segment_id",
  click_general.sub_type AS "sub_type",
  click_general.video AS "is_video",
  click_general.weekend  AS "is_weekend"
FROM sanitized_db.si_cs_click_recent AS click
LEFT JOIN sanitized_db.sanitized_click_general AS click_general ON click.click_id = click_general.click_id
WHERE
  ((click.timestamp) >= (DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) )) AND (click.timestamp) < (DATEADD(day,90, DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) ) )))

click_new.view.lkml · 266 lines

inferred

view: click_new {
  derived_table: {
    sql: SELECT
  click.click_id,
  click.parent_visit AS parent_visit_id,
  click.click_duration AS "click_duration",
  click.click_number AS "click_sequence",
  click.internal_search AS "internal_search",
  click.is_constructed AS "is_constructed",
  click.is_first AS "is_first",
  click.is_last  AS "is_last",
  click.line_number AS "line_number",
  click.link_traversal_index AS "link_traversal_index",
  click.number_of_requests AS "number_of_requests",
  click.page_bytes_sent AS "page_bytes_sent",
  click.page_visit_index AS "page_visit_index",
  click.processing_time AS "processing_time",
  click.referral_url AS "referral_url",
  click.source_batch AS "source_batch",
  click.status_code AS "status_code",
  click.timestamp AS "timestamp_date",
  click.total_bytes_sent AS "total_bytes_sent",
  click_general.country_code AS "country_code",
  click_general.edition AS "edition",
  click_general.edition_date AS "edition_date",
  click_general.load_time_ms AS "load_time_ms",
  click_general.navigational AS "is_navigational",
  click_general.num_comments AS "num_comments",
  click_general.num_likes AS "num_likes",
  click_general.num_mentions AS "num_mentions",
  click_general.num_share_facebook AS "num_share_facebook",
  click_general.num_share_twitter AS "num_share_twitter",
  click_general.pid AS "pid",
  click_general.segid AS "segid",
  click_general.segment_id AS "segment_id",
  click_general.sub_type AS "sub_type",
  click_general.video AS "is_video",
  click_general.weekend  AS "is_weekend"
FROM sanitized_db.si_cs_click_recent AS click
LEFT JOIN sanitized_db.sanitized_click_general AS click_general ON click.click_id = click_general.click_id
WHERE
  ((click.timestamp) >= (DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) )) AND (click.timestamp) < (DATEADD(day,90, DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) ) ))) ;;
  }

  dimension: click_duration {
    type: number
    sql: ${TABLE}.click_duration ;;
  }

  dimension: parent_visit_id {
    type: int
    hidden: yes
    sql: ${TABLE}.parent_visit_id ;;
  }

  dimension: click_sequence {
    type: number
    sql: ${TABLE}.click_sequence ;;
  }

  dimension: internal_search {
    sql: ${TABLE}.internal_search ;;
  }

  dimension: is_constructed {
    type: yesno
    sql: ${TABLE}.is_constructed ;;
  }

  dimension: is_first {
    type: yesno
    sql: ${TABLE}.is_first ;;
  }

  dimension: is_last {
    type: yesno
    sql: ${TABLE}.is_last ;;
  }

  dimension: line_number {
    type: number
    sql: ${TABLE}.line_number ;;
  }

  dimension: link_traversal_index {
    type: number
    sql: ${TABLE}.link_traversal_index ;;
  }

  dimension: number_of_requests {
    type: number
    sql: ${TABLE}.number_of_requests ;;
  }

  dimension: page_bytes_sent {
    sql: ${TABLE}.page_bytes_sent ;;
  }

  dimension: page_visit_index {
    type: number
    sql: ${TABLE}.page_visit_index ;;
  }

  dimension: processing_time {
    type: number
    sql: ${TABLE}.processing_time ;;
  }

  dimension: referral_url {
    type: int
    hidden: yes
    sql: ${TABLE}.referral_url ;;
  }

  dimension: source_batch {
    type: number
    hidden: yes
    sql: ${TABLE}.source_batch ;;
  }

  dimension: status_code {
    type: int
    sql: ${TABLE}.status_code ;;
  }

  dimension: total_bytes_sent {
    type: number
    sql: ${TABLE}.total_bytes_sent ;;
  }

  dimension: country_code {
    sql: ${TABLE}.country_code ;;
  }

  dimension: edition {
    sql: ${TABLE}.edition ;;
  }

  dimension: edition_date {
    type: date
    sql: ${TABLE}.edition_date ;;
  }

  dimension: load_time_ms {
    type: number
    sql: ${TABLE}.load_time_ms ;;
  }

  dimension: is_navigational {
    type: yesno
    sql: ${TABLE}.is_navigational ;;
  }

  dimension: num_comments {
    type: number
    hidden: yes
    sql: ${TABLE}.num_comments ;;
  }

  dimension: num_likes {
    type: number
    hidden: yes
    sql: ${TABLE}.num_likes ;;
  }

  dimension: num_mentions {
    type: number
    hidden: yes
    sql: ${TABLE}.num_mentions ;;
  }

  dimension: num_share_facebook {
    type: number
    hidden: yes
    sql: ${TABLE}.num_share_facebook ;;
  }

  dimension: num_share_twitter {
    type: number
    hidden: yes
    sql: ${TABLE}.num_share_twitter ;;
  }

  dimension: pid {
    type: int
    hidden: yes
    sql: ${TABLE}.pid ;;
  }

  dimension: segid {
    hidden: yes
    sql: ${TABLE}.segid ;;
  }

  dimension: segment_id {
    sql: ${TABLE}.segment_id ;;
  }

  dimension: sub_type {
    type: int
    hidden: yes
    sql: ${TABLE}.sub_type ;;
  }

  dimension: is_video {
    type: yesno
    sql: ${TABLE}.is_video ;;
  }

  dimension: is_weekend {
    type: yesno
    hidden: yes
    sql: ${TABLE}.is_weekend ;;
  }

  dimension_group: click {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.timestamp_date ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: total_likes {
    type: sum
    sql: ${num_likes} ;;
  }

  measure: total_comments {
    type: sum
    sql: ${num_comments} ;;
  }

  measure: total_mentions {
    type: sum
    sql: ${num_mentions} ;;
  }

  measure: total_fb_shares {
    type: sum
    sql: ${num_share_facebook} ;;
  }

  measure: total_twitter_shares {
    type: sum
    sql: ${num_share_twitter} ;;
  }

  measure: first_clicks {
    type: count
    filters: [is_first: "True"]
  }

  measure: last_clicks {
    type: count
    filters: [is_last: "True"]
  }

  measure: video_clicks {
    type: count
    filters: [is_video: "True"]
  }
}

click_new.view.lookml · 187 lines

source-preserved

- view: click_new
  derived_table:
    sql: SELECT
  click.click_id,
  click.parent_visit AS parent_visit_id,
  click.click_duration AS "click_duration",
  click.click_number AS "click_sequence",
  click.internal_search AS "internal_search",
  click.is_constructed AS "is_constructed",
  click.is_first AS "is_first",
  click.is_last  AS "is_last",
  click.line_number AS "line_number",
  click.link_traversal_index AS "link_traversal_index",
  click.number_of_requests AS "number_of_requests",
  click.page_bytes_sent AS "page_bytes_sent",
  click.page_visit_index AS "page_visit_index",
  click.processing_time AS "processing_time",
  click.referral_url AS "referral_url",
  click.source_batch AS "source_batch",
  click.status_code AS "status_code",
  click.timestamp AS "timestamp_date",
  click.total_bytes_sent AS "total_bytes_sent",
  click_general.country_code AS "country_code",
  click_general.edition AS "edition",
  click_general.edition_date AS "edition_date",
  click_general.load_time_ms AS "load_time_ms",
  click_general.navigational AS "is_navigational",
  click_general.num_comments AS "num_comments",
  click_general.num_likes AS "num_likes",
  click_general.num_mentions AS "num_mentions",
  click_general.num_share_facebook AS "num_share_facebook",
  click_general.num_share_twitter AS "num_share_twitter",
  click_general.pid AS "pid",
  click_general.segid AS "segid",
  click_general.segment_id AS "segment_id",
  click_general.sub_type AS "sub_type",
  click_general.video AS "is_video",
  click_general.weekend  AS "is_weekend"
FROM sanitized_db.si_cs_click_recent AS click
LEFT JOIN sanitized_db.sanitized_click_general AS click_general ON click.click_id = click_general.click_id
WHERE
  ((click.timestamp) >= (DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) )) AND (click.timestamp) < (DATEADD(day,90, DATEADD(day,-89, DATE_TRUNC('day',GETDATE()) ) )))
  fields:
  - dimension: click_duration
    type: number
    sql: ${TABLE}.click_duration
  - dimension: parent_visit_id
    type: int
    hidden: true
    sql: ${TABLE}.parent_visit_id
  - dimension: click_sequence
    type: number
    sql: ${TABLE}.click_sequence
  - dimension: internal_search
    sql: ${TABLE}.internal_search
  - dimension: is_constructed
    type: yesno
    sql: ${TABLE}.is_constructed
  - dimension: is_first
    type: yesno
    sql: ${TABLE}.is_first
  - dimension: is_last
    type: yesno
    sql: ${TABLE}.is_last
  - dimension: line_number
    type: number
    sql: ${TABLE}.line_number
  - dimension: link_traversal_index
    type: number
    sql: ${TABLE}.link_traversal_index
  - dimension: number_of_requests
    type: number
    sql: ${TABLE}.number_of_requests
  - dimension: page_bytes_sent
    sql: ${TABLE}.page_bytes_sent
  - dimension: page_visit_index
    type: number
    sql: ${TABLE}.page_visit_index
  - dimension: processing_time
    type: number
    sql: ${TABLE}.processing_time
  - dimension: referral_url
    type: int
    hidden: true
    sql: ${TABLE}.referral_url
  - dimension: source_batch
    type: number
    hidden: true
    sql: ${TABLE}.source_batch
  - dimension: status_code
    type: int
    sql: ${TABLE}.status_code
  - dimension: total_bytes_sent
    type: number
    sql: ${TABLE}.total_bytes_sent
  - dimension: country_code
    sql: ${TABLE}.country_code
  - dimension: edition
    sql: ${TABLE}.edition
  - dimension: edition_date
    type: date
    sql: ${TABLE}.edition_date
  - dimension: load_time_ms
    type: number
    sql: ${TABLE}.load_time_ms
  - dimension: is_navigational
    type: yesno
    sql: ${TABLE}.is_navigational
  - dimension: num_comments
    type: number
    hidden: true
    sql: ${TABLE}.num_comments
  - dimension: num_likes
    type: number
    hidden: true
    sql: ${TABLE}.num_likes
  - dimension: num_mentions
    type: number
    hidden: true
    sql: ${TABLE}.num_mentions
  - dimension: num_share_facebook
    type: number
    hidden: true
    sql: ${TABLE}.num_share_facebook
  - dimension: num_share_twitter
    type: number
    hidden: true
    sql: ${TABLE}.num_share_twitter
  - dimension: pid
    type: int
    hidden: true
    sql: ${TABLE}.pid
  - dimension: segid
    hidden: true
    sql: ${TABLE}.segid
  - dimension: segment_id
    sql: ${TABLE}.segment_id
  - dimension: sub_type
    type: int
    hidden: true
    sql: ${TABLE}.sub_type
  - dimension: is_video
    type: yesno
    sql: ${TABLE}.is_video
  - dimension: is_weekend
    type: yesno
    hidden: true
    sql: ${TABLE}.is_weekend
  - dimension_group: click
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.timestamp_date
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_likes
    type: sum
    sql: ${num_likes}
  - measure: total_comments
    type: sum
    sql: ${num_comments}
  - measure: total_mentions
    type: sum
    sql: ${num_mentions}
  - measure: total_fb_shares
    type: sum
    sql: ${num_share_facebook}
  - measure: total_twitter_shares
    type: sum
    sql: ${num_share_twitter}
  - measure: first_clicks
    type: count
    filters:
    - is_first: True
  - measure: last_clicks
    type: count
    filters:
    - is_last: True
  - measure: video_clicks
    type: count
    filters:
    - is_video: True

click_new.sql
click_new.sql
click_new.view.lkml
click_new.view.lookml

Click New

Blocks

Assumptions

Contents