MaxKrog

---
version: 2
models:
  - name: elt_example_model
    description: Source table with cleaned input data from call log supplier X

    columns:
      - name: call_surr_key
        decription: surrogate key for the row
        tests: 

MaxKrog

with source_data as (
    select * from `company-a.source.source_data`
),

reading_fields as (
    select
        JSON_VALUE(_loaded_data, "$.call_time") as call_time,
        JSON_VALUE(_loaded_data, "$.number") as caller_number,
        JSON_VALUE(_loaded_data, "$.type") as call_type,

MaxKrog

...

clean_with_surr_key as (
    select

        {{ dbt_utils.surrogate_key(['call_time', 'caller_number', 'caller_number', 'call_type', 'call_outcome', 'call_duration']) }} as call_surr_key,
        row_number() over (
            partition by {{ dbt_utils.surrogate_key(['call_time', 'caller_number', 'caller_number', 'call_type', 'call_outcome', 'call_duration']) }}
            order by 
                _loaded_at desc 

MaxKrog

clean_with_surr_key as (
    select

        {{ dbt_utils.surrogate_key(['call_time', 'caller_number', 'caller_number', 'call_type', 'call_outcome', 'call_duration']) }} as call_surr_key,
        row_number() over (
            partition by {{ dbt_utils.surrogate_key(['call_time', 'caller_number', 'caller_number', 'call_type', 'call_outcome', 'call_duration']) }}
            order by 
                _loaded_at desc 
        ) end as call_surr_key_rn,
        call_time,

MaxKrog

...

cleaned_fields as (
    select
        -- upstream call_time is string representing a timestamp in CET
        parse_timestamp("%Y-%m-%dT%H:%M:%S", call_time, 'Europe/Stockholm') as ct,
        caller_number,
        case
            when call_type = "Incoming" then 'incoming'
            when call_type = "Outgoing" then 'outgoing'

MaxKrog

with source_data as (
    select * from `company-a.source.source_data`
),

reading_fields as (
    select
        JSON_VALUE(_loaded_data, "$.call_time") as call_time,
        JSON_VALUE(_loaded_data, "$.number") as caller_number,
        JSON_VALUE(_loaded_data, "$.type") as call_type,
        JSON_VALUE(_loaded_data, "$.outcome") as call_outcome,

MaxKrog

from google.cloud import *service*