clrcrl

{% macro clean_data(relation) %}

{%- set columns = adapter.get_columns_in_relation(relation) -%}

{% set where_clauses={
    'id': 'not in (1, 2)',
    'last_name': "not in ('Carroll')"
} %}

select

clrcrl

Question on Slack:

Hello everyone! I have a Jinja DBT question! I have set a list and I'm looping over it.

{% set my_list= ["$apple",
"$avocado",
"tomato"] %}

I want now to loop over this list, retrive the data from each column (each item is a column name), and in the column name I want to remove the dollar symbol $. I was trying to do it with this code bellow:

clrcrl

Create transient tables in dev, else use a non-transient table

Related loom video

Instructions

1. Put the below code in to your macros directroy
2. Use this materialization in a model like so:

{{
    config(
 materialized='transient_if_dev_table'

clrcrl

on-run-end:
    - 'grant usage on schema "{{ target.schema }}" to role learner'
    - 'grant select on all tables in schema "{{ target.schema }}" to role learner'
    - 'grant select on all views in schema "{{ target.schema }}" to role learner'

clrcrl

dbt Slack is special. We currently have {{ n_slack_users }} data professionals in our Slack, many of whom genuinely love this community. It’s filled with smart, kind, and helpful people who share our commitment to elevating the analytics profession.

We are committed to maintaining the spirit of this community, and as such have written some guidelines to help new members understand how to best participate in our Slack.

You can read the full rules here, but here’s what you need to know before joining:

The unbreakable rule: be respectful

We want everyone to have a fulfilling and positive experience in dbt Slack and we are continuously grateful for your help in ensuring that this is the case.

The guidelines that follow are important, but transgressions around Slack etiquette are forgivable. This first rule, however, is serious–-we simply will not tolerate disrespectful behavior of any kind.

clrcrl

The subscriptions table contains the current product_id.

subscription_id	product_id	created_at	example_case
1	900	2018-01-01	subscription that hasn't changed
2	907	2018-01-01	subscription that has changed once
3	956	2018-01-01	subscription that has changed many times

The subscription_change_log shows when changes to this product_id occurred:

clrcrl

Subscriptions can change their product ID over time, as captured in a subscription_change_log table.

subscription_id	new_product_id	changed_at
1	52	2019-01-10
1	57	2019-01-15
2	53	2019-01-12

Here, subscription 1 changes multiple times, but the change only takes effect once the customer gets rebilled.

clrcrl

-- This goes in the models/ directory

with companies as (
  select 1 as company_id, null as parent_company_id, 'Hooli' as name, 45 as revenue
  union all
  select 2, 1, 'HooliPhones', 84
  union all
  select 3, 1, 'HooliChat', 32
  union all
  select 4, 3, 'HooliMessage', 25

clrcrl

This is the process I used to put redshift user defined functions into dbt.

1. Created a subdirectory: macros/udfs/
2. Created a file for each udf, e.g. macros/udfs/f_future_date.sql.

{% macro f_future_date() %}
CREATE OR REPLACE FUNCTION {{target.schema}}.f_future_date()
RETURNS TIMESTAMP
IMMUTABLE AS $$
SELECT '2100-01-01'::TIMESTAMP;

clrcrl

-- this is a model - put it in the models/ directory
WITH nested_table AS (
  SELECT 1 AS id, 'a' AS tags
  UNION
  SELECT 2 AS id, 'a, b' AS tags
  UNION
  SELECT 3 AS id, 'c , d' AS tags
  UNION
  SELECT 4 AS id, 'a,b,e' AS tags
  UNION