ernestoongaro

/* Create a pivot table with dynamic columns based on the ship modes that are in the system */

{%- call statement('result', fetch_result=True) -%}

    {# this pulls the unique ship modes from the fct_order_items table #}
    select ship_mode from {{ ref('fct_order_items') }} group by 1 

{%- endcall %}

{% set ship_modes = load_result('result').table.columns[0].values() %}

ernestoongaro

===
Compiled SQL:
select
    EXTRACT(YEAR from ship_date),
    sum(case when ship_mode = 'FOB' then gross_item_sales_amount end) as FOB_A_amount,
    sum(case when ship_mode = 'TRUCK' then gross_item_sales_amount end) as TRUCK_N_amount,
    sum(case when ship_mode = 'MAIL' then gross_item_sales_amount end) as MAIL_N_amount,
    sum(case when ship_mode = 'SHIP' then gross_item_sales_amount end) as SHIP_R_amount,

ernestoongaro

{% macro add_search_optimization(relation,columns) %}

    {# check if the relation has search optimization added #}
    {%- call statement('search_optimization', fetch_result=True) -%}
    describe search optimization on {{ relation }}
    {%- endcall %}

    {% if execute %}
    {% set result = load_result('search_optimization') %}
    {%- set result_data = result['data'] -%}

ernestoongaro

ernestoongaro

#example from https://towardsdatascience.com/reduce-warehouse-space-with-the-pareto-principle-using-python-e722a6babe0e
#optimizing warehouse location based on sku frequency using pareto 80/20 principle 

import pandas as pd


def model(dbt, session):
    dbt.config(
        materialized="table",
        packages=["pandas"] #https://repo.anaconda.com/pkgs/snowflake/

ernestoongaro

{% materialization incremental_lastrun_auto, adapter='snowflake' -%}

  {% set original_query_tag = set_query_tag() %}

  {%- set unique_key = config.get('unique_key') -%}
  {%- set full_refresh_mode = (should_full_refresh()) -%}

  {% set target_relation = this %}
  {% set existing_relation = load_relation(this) %}
  {% set tmp_relation = make_temp_relation(this) %}

ernestoongaro

import enum
import os
import time

# Be sure to `pip install requests` in your python environment
import requests

ACCOUNT_ID = 39
JOB_ID = 302

ernestoongaro

import modin.pandas as pd
import snowflake.snowpark.modin.plugin
from prophet import Prophet

def model( dbt, session ):
    
    dbt.config(
        materialized="table", # the incremental materialization is also supported
        packages=['pandas==2.2.1','modin==0.28.1','Prophet','holidays==0.18','snowflake-snowpark-python[modin]'], # how to import python libraries in dbt's context
        python_version="3.11"

ernestoongaro

âPNG


IHDRXXqï04	pHYs.#.#
x•?vtEXtSoftwarewww.inkscape.orgõÓ<kIDATxúÌùmpT’«ˇÁÓ›ÕyÅ$ª!/ê í *-¡$†¡P,„Î4VöXâäC[–Rù;ïù°È⁄ë©R)Tf;$ä≈“¡ÑjiH@ !/K≤	y#…f˜Ó=˝P„dsŒÕ€fisw‹‹fl«ÁÓ=ˇgˇ≥{ÓπÁ<Á^B)Ö§¨»&w≈ˇÄPRJ≤)°…pê5O
m
tJ⁄ ©’Tï ª´Çñz¥N <ÉIIâds¥Ø£¿NÈ˛ŒCÅV¸—€ë¯&›æ]{ú18Úı-ÛBfl∞‹®$CÑYë|˛’ñ—A?Ém{~πúG(0◊ÙB8©*=‚Ÿ≤ªˆ€ÿà¡≥v?üϱ¯™	ê¥C\∑(rˆ»/YRR"{-æLsásYyèîYÄo∂:⁄7¬Ïsı$«⁄ô4î⁄‰ŒÑ+ÊØW_(°-J|◊BIvŸ◊òÊÍ°$Uæó/BÇùL®BT©@êÏDBòlâ…¡Œ"T°Ñ&K∞;ëPÖP2W¬Ãù∏1Y
v°éi∞`tÌbl·ÿ±Ú±Ä⁄8fiRè˜.◊N¸AÎf·æ‘≈
Èo˚Ï0˙<ÓÄ⁄çÆGXl¯˘íºÄ⁄z¶mäƒ[÷/©>Ç>Ëg∞ŸE∆4X0¶¡Ç—µÓ˜∫·ÏERd¨_ºœ„∆øöÎ&’∆W]-hús'€Øù∑1∂pøòs∞˝^˝˙_ ÷◊ü”^VûYˆ˘¯‰—≠àîm~ÒÕ÷ÖJ=•¶ÕìôπxsÕøò€Á≈Àw°∫ΩQW-›ªàZ◊5<WÒˇÀ™«qó#Moπ)ììòé=˘≈L¸ˇ~[ws
A}?.U„ÌÜ*øX∏≈äÉkÜhk∏∆Y‚ôeµaˇ}œ Âfl3ÓØ؃°KgÑh
ª»˝˙”R4ˆu˘≈ƒ$`Á›ÎDIN»Æº"‹Îã]Ì΃÷Se¬4Ö‹Ôu„∑ïÔ2ÒçKÓ∆Ω…ãD…jrO“BlXºíâo=]Üõfiaa∫Bái\˝«[Í˝b؉¬Bå! íÑ◊V=
‚?÷\áˇ#T[¯∑‹qˆc&∂4>?]d‹B ˙å‹œÆ+º|ˆ®pm·ühªà≥Æ&&˛R÷˝ê·ú°/!¯›≤˚ô¯ôé&úv^Ø/\
¿¡Üj&∂xŒ\<8ˇ·⁄•›âåŸâL¸@√Á¬µ
É>|Â(ÿ˚ôß2W