Placement Id Discrep Test

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

select * from (select
coalesce(a.main_adv_sym, b.partner_name) as partner,
a.pt_id,
PT_NAME,
b.threepas_placement_id
from
(
        select
                distinct pt_id,
                main_adv_sym,
                PT_NAME
        from
                `TBL_FINAL_AGGREGATION`
        where
                `interval` >= curdate() - interval 2 day
                and imps_3p > 0
) a
left join
(
        select
                distinct threepas_placement_id,
                third_party_export_api.partner_name
        from
                `third_party_export_api` third_party_export_api
        where
                date_field >= curdate() - interval 2 day
                and impressions > 0
                and threepas_placement_id not like '6____________'
                and threepas_placement_id not like 'mm%'
) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
UNION ALL
select
        coalesce(a.main_adv_sym, b.partner_name) as partner,
        a.pt_id,
        PT_NAME,
        b.threepas_placement_id
from
        (
                select
                        distinct pt_id,
                        PT_NAME,
                        main_adv_sym
                from
                        `TBL_FINAL_AGGREGATION`
                where
                        `interval` >= curdate() - interval 2 day
                        and imps_3p > 0
        ) a
right join
        (
                select
                        distinct threepas_placement_id,
                        third_party_export_api.partner_name
                from
                        `third_party_export_api` third_party_export_api
                where
                        date_field >= curdate() - interval 2 day
                        and impressions > 0
                        and threepas_placement_id not like '6____________'
                        and threepas_placement_id not like 'mm%'
        ) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
) f
where
 --partner = 'GoDaddy.com' and
 trim(lower(pt_id)) not in (select distinct trim(lower(placement_id)) from campaign_facebook where date >= curdate() -interval 2 day)
 or threepas_placement_id is not null

# models/schema.yml
version: 2

models:
  - name: placement_id_discrep_test
    columns:
      - name: partner
      - name: pt_id
      - name: pt_name
      - name: threepas_placement_id

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.

select * from (select
coalesce(a.main_adv_sym, b.partner_name) as partner,
a.pt_id,
PT_NAME,
b.threepas_placement_id
from
(
        select
                distinct pt_id,
                main_adv_sym,
                PT_NAME
        from
                `TBL_FINAL_AGGREGATION`
        where
                `interval` >= curdate() - interval 2 day
                and imps_3p > 0
) a
left join
(
        select
                distinct threepas_placement_id,
                third_party_export_api.partner_name
        from
                `third_party_export_api` third_party_export_api
        where
                date_field >= curdate() - interval 2 day
                and impressions > 0
                and threepas_placement_id not like '6____________'
                and threepas_placement_id not like 'mm%'
) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
UNION ALL
select
        coalesce(a.main_adv_sym, b.partner_name) as partner,
        a.pt_id,
        PT_NAME,
        b.threepas_placement_id
from
        (
                select
                        distinct pt_id,
                        PT_NAME,
                        main_adv_sym
                from
                        `TBL_FINAL_AGGREGATION`
                where
                        `interval` >= curdate() - interval 2 day
                        and imps_3p > 0
        ) a
right join
        (
                select
                        distinct threepas_placement_id,
                        third_party_export_api.partner_name
                from
                        `third_party_export_api` third_party_export_api
                where
                        date_field >= curdate() - interval 2 day
                        and impressions > 0
                        and threepas_placement_id not like '6____________'
                        and threepas_placement_id not like 'mm%'
        ) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
) f
where
 --partner = 'GoDaddy.com' and
 trim(lower(pt_id)) not in (select distinct trim(lower(placement_id)) from campaign_facebook where date >= curdate() -interval 2 day)
 or threepas_placement_id is not null

view: placement_id_discrep_test {
  derived_table: {
    sql: select * from (select
coalesce(a.main_adv_sym, b.partner_name) as partner,
a.pt_id,
PT_NAME,
b.threepas_placement_id
from
(
        select
                distinct pt_id,
                main_adv_sym,
                PT_NAME
        from
                `TBL_FINAL_AGGREGATION`
        where
                `interval` >= curdate() - interval 2 day
                and imps_3p > 0
) a
left join
(
        select
                distinct threepas_placement_id,
                third_party_export_api.partner_name
        from
                `third_party_export_api` third_party_export_api
        where
                date_field >= curdate() - interval 2 day
                and impressions > 0
                and threepas_placement_id not like '6____________'
                and threepas_placement_id not like 'mm%'
) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
UNION ALL
select
        coalesce(a.main_adv_sym, b.partner_name) as partner,
        a.pt_id,
        PT_NAME,
        b.threepas_placement_id
from
        (
                select
                        distinct pt_id,
                        PT_NAME,
                        main_adv_sym
                from
                        `TBL_FINAL_AGGREGATION`
                where
                        `interval` >= curdate() - interval 2 day
                        and imps_3p > 0
        ) a
right join
        (
                select
                        distinct threepas_placement_id,
                        third_party_export_api.partner_name
                from
                        `third_party_export_api` third_party_export_api
                where
                        date_field >= curdate() - interval 2 day
                        and impressions > 0
                        and threepas_placement_id not like '6____________'
                        and threepas_placement_id not like 'mm%'
        ) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
) f
where
 --partner = 'GoDaddy.com' and
 trim(lower(pt_id)) not in (select distinct trim(lower(placement_id)) from campaign_facebook where date >= curdate() -interval 2 day)
 or threepas_placement_id is not null ;;
  }

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

  dimension: pt_id {
    label: "T1 Placement ID"
    sql: ${TABLE}.pt_id ;;
  }

  dimension: pt_name {
    label: "T1 Placement Name"
    sql: ${TABLE}.PT_NAME ;;
  }

  dimension: threepas_placement_id {
    label: "3P Placement ID"
    sql: ${TABLE}.threepas_placement_id ;;
  }

  measure: count {
    type: count
    drill_fields: [pt_name]
  }
}

- view: placement_id_discrep_test
  derived_table:
    sql: select * from (select
coalesce(a.main_adv_sym, b.partner_name) as partner,
a.pt_id,
PT_NAME,
b.threepas_placement_id
from
(
        select
                distinct pt_id,
                main_adv_sym,
                PT_NAME
        from
                `TBL_FINAL_AGGREGATION`
        where
                `interval` >= curdate() - interval 2 day
                and imps_3p > 0
) a
left join
(
        select
                distinct threepas_placement_id,
                third_party_export_api.partner_name
        from
                `third_party_export_api` third_party_export_api
        where
                date_field >= curdate() - interval 2 day
                and impressions > 0
                and threepas_placement_id not like '6____________'
                and threepas_placement_id not like 'mm%'
) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
UNION ALL
select
        coalesce(a.main_adv_sym, b.partner_name) as partner,
        a.pt_id,
        PT_NAME,
        b.threepas_placement_id
from
        (
                select
                        distinct pt_id,
                        PT_NAME,
                        main_adv_sym
                from
                        `TBL_FINAL_AGGREGATION`
                where
                        `interval` >= curdate() - interval 2 day
                        and imps_3p > 0
        ) a
right join
        (
                select
                        distinct threepas_placement_id,
                        third_party_export_api.partner_name
                from
                        `third_party_export_api` third_party_export_api
                where
                        date_field >= curdate() - interval 2 day
                        and impressions > 0
                        and threepas_placement_id not like '6____________'
                        and threepas_placement_id not like 'mm%'
        ) b
on a.main_adv_sym = b.`partner_name`
        and a.pt_id = b.threepas_placement_id
where
        pt_id is null
        or threepas_placement_id is null
        or pt_id != threepas_placement_id
) f
where
 --partner = 'GoDaddy.com' and
 trim(lower(pt_id)) not in (select distinct trim(lower(placement_id)) from campaign_facebook where date >= curdate() -interval 2 day)
 or threepas_placement_id is not null
  fields:
  - dimension: partner
    sql: ${TABLE}.partner
  - dimension: pt_id
    label: T1 Placement ID
    sql: ${TABLE}.pt_id
  - dimension: pt_name
    label: T1 Placement Name
    sql: ${TABLE}.PT_NAME
  - dimension: threepas_placement_id
    label: 3P Placement ID
    sql: ${TABLE}.threepas_placement_id
  - measure: count
    type: count
    drill_fields:
    - pt_name