dbt Cloud Workshop: Mesh Solution Code

dbt_workshop_solution_ex1.sql

with campaigns as (
    select * from {{ ref('stg_campaigns') }}
),
campaign_sequence as (
    select * from {{ ref('stg_campaign_sequence') }}
),

final as (
    select
        campaign_sequence.campaign_id,
        campaign_sequence.sequence_id,
        campaigns.campaign_name,
        campaign_sequence.tier_name,
        campaigns.campaign_owner
    from campaign_sequence
    left join campaigns
    on campaign_sequence.campaign_id = campaigns.campaign_id
)

select * from final

dbt_workshop_solution_ex2.sql

with campaign as (
    select * from {{ ref('dim_campaigns') }}
),

customer as (
    select * from {{ ref('analytics','dim_customers',v=1) }}
),

final as (
    select 
        campaign.campaign_name,
        campaign.campaign_owner,
        customer.name as customer_name,
        customer.region as customer_region,
        customer.tier_name,
        customer.address as email_address
    from
        campaign
        inner join customer
            on campaign.tier_name = customer.tier_name
)
select 
    *
from
    final

dbt_workshop_solution_ex3.sql

version: 2

models:
  - name: agg_campaign_customer_contacts
    access: public
    description: ""
    config:
      contract:
        enforced: true
    columns:
      - name: campaign_name
        data_type: varchar
        description: ""
        tests:
          - not_null
        
      - name: campaign_name
        data_type: varchar
        description: ""

      - name: campaign_owner
        data_type: varchar
        description: ""
        tests:
          - accepted_values:
              values: ['Rebecca', 'Ted', 'Roy']

      - name: customer_name
        data_type: varchar
        description: ""

      - name: customer_region
        data_type: varchar
        description: ""

      - name: tier_name
        data_type: varchar
        description: ""

      - name: email_address
        data_type: varchar
        description: ""