ian-whitestone

Baby Boot Camp: Weeks 8–12

Target schedule: 7AM–7PM days, 7PM–7AM nights

---

Prerequisites

Before starting Boot Camp, baby must meet all three:

• Weight: at least 4 kg (9 lbs)
• Food: eating at least ~710 ml/day

ian-whitestone

test

ian-whitestone

# Let's add an row number to indicate the first message per app & microservice
# This code is analagous to the SQL: row_number() over (partition by id, topic order by msg_ts asc)
df['row_num'] = df.sort_values(['id', 'msg_ts'], ascending=True).groupby(['id', 'topic']).cumcount() + 1

ian-whitestone

ian-whitestone

ian-whitestone

with
warehouse_periods as (
select
  warehouse_name,
  timestamp as valid_from,
  lead(timestamp) over (partition by warehouse_name order by timestamp asc) as    valid_to,
  event_name = 'RESUME_WAREHOUSE' as is_active
from snowflake.account_usage.warehouse_events_history
where
  -- double check these names, can't remember exact values

ian-whitestone

"""
Read accompanying blog post: https://ianwhitestone.work/Zappa-Zip-Callbacks
"""
import os
import re
import shutil
import tarfile
import zipfile

ian-whitestone

with sample_data as (
    select *
    from snowflake_sample_data.tpch_sf1.customer
),

nation_14_customers as (
    select *
    from sample_data
    where c_nationkey = 14
),

ian-whitestone

Presto Specific

• Don’t SELECT *, Specify explicit column names (columnar store)
• Avoid large JOINs (filter each table first)
  • In PRESTO tables are joined in the order they are listed!!
  • Join small tables earlier in the plan and leave larger fact tables to the end
  • Avoid cross joins or 1 to many joins as these can degrade performance
• Order by and group by take time
  • only use order by in subqueries if it is really necessary
• When using GROUP BY, order the columns by the highest cardinality (that is, most number of unique values) to the lowest.

ian-whitestone

{% macro set_query_tag() -%}
    {# Start with any model-configured dict #}
    {% set tag_dict = config.get('query_tag', default={}) %}

    {# Regardless of resource type, we can always access the config via the 'model' variable #}
    {%- do tag_dict.update(
        dbt_snowflake_query_tags_version='1.1.3',
        app='dbt',
        dbt_version=dbt_version,
        project_name=project_name,