jthandy

with
gains as (
	select date_trunc('month', signup_date) as month, count(*) total_gains
	from {{env.schema}}.mailchimp_members
	group by date_trunc('month', signup_date)
),

hard_bounces as
(
	-- get the first hard bounce date for each email

jthandy

with email_summary as (

  select *
  from {{env.schema}}.mailchimp_email_summary

), best_day_of_week as (

  select date_part(dow, sent_date), avg(opened::float)
  from email_summary
  group by 1

jthandy

with email_summary as (

  select *
  from {{env.schema}}.mailchimp_email_summary

)

select email_id, sum(clicked)::float / sum(opened)
  from email_summary
 group by 1

jthandy

  select
    md5(
      coalesce(date_day::varchar, '') ||
      coalesce(destination_url, '') ||
      coalesce(utm_medium, '') ||
      coalesce(utm_source, '') ||
      coalesce(utm_campaign, '') ||
      coalesce(utm_term, '') ||
      coalesce(utm_content, '') ||
      coalesce(ad_group_id::varchar, '') ||

jthandy

select 
  ad_performance.*,
  sessions.*
from ad_performance
left outer join sessions on ad_performance.id = sessions.ad_performance_id

jthandy

with discounts as (

    select * from {{ref('stripe_discounts')}}

),

invoice_items as (

    select * from {{ref('stripe_invoice_items')}}

jthandy

Running vacuum and analyze in Sinter

dbt and Sinter have the ability to run regular Redshift maintenance jobs. It's great to set these up early on in a project so that things stay clean as the project grows, and implementing these jobs in Sinter allows the same easy transparency and notifications as with your other dbt jobs.

This document will go through the specific steps necessary to configure vacuum and analyze jobs in the current version of dbt and Sinter. In the future, there will likely be a more idiomatically consistent way to express this logic using native dbt operations. Currently, this does work even if it is not elegant.

Step 1: Create the macros

macros/redshift_maintenance.sql

jthandy

{% macro source_select_sql(relation, column_name) %}
    select {{column_name}} as f from {{relation}}
{% endmacro %}


{% macro single_pass_stats(relation, column_name) %}
    {%- call statement('single_pass_column_stats', fetch_result=True) -%}

        with source as ( {{ fishtown_internal_analytics.source_select_sql(relation, column_name) }} ),
        single_pass_stats as (

jthandy

create table dbt_jthandy.flatten_test (
    order_id int, 
    json_text varchar(1000)
    )
;

insert into dbt_jthandy.flatten_test
  (order_id, json_text) 
values
  (1, '{  

jthandy