-
Notifications
You must be signed in to change notification settings - Fork 10
/
mixpanel__event.sql
61 lines (42 loc) · 1.56 KB
/
mixpanel__event.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
{{
config(
materialized='incremental',
unique_key='unique_event_id',
partition_by={
"field": "date_day",
"data_type": "date"
}
)
}}
with stg_event as (
select *
from {{ ref('stg_mixpanel__event') }}
where
{% if is_incremental() %}
-- events are only eligible for de-duping if they occurred on the same calendar day
occurred_at >= coalesce((select cast( max(date_day) as {{ dbt_utils.type_timestamp() }} ) from {{ this }} ), '2010-01-01')
{% else %}
-- limit date range on the first run / refresh
occurred_at >= {{ "'" ~ var('date_range_start', '2010-01-01') ~ "'" }}
{% endif %}
),
dedupe as (
select * from (
select
{{ dbt_utils.surrogate_key(['insert_id', 'people_id', 'event_type', 'date_day']) }} as unique_event_id,
*,
-- aligned with mixpanel' s deduplication method: https://developer.mixpanel.com/reference/http#event-deduplication
-- de-duping on calendar day + insert_id but also on people_id + event_type to reduce the rate of false positives
row_number() over(partition by insert_id, people_id, event_type, date_day order by mp_processing_time_ms asc) as nth_event_record
from stg_event
)
where nth_event_record = 1
),
pivot_properties as (
select
*
{%- if var('event_properties_to_pivot', []) != [] %},{% endif %}
{{ pivot_event_properties_json(var('event_properties_to_pivot', [])) }}
from dedupe
)
select * from pivot_properties