Snowflakeとdbt

use role accountadmin;

create database raw;
create database analytics;
create schema raw.jaffle_shop;

create or replace role dbt_demo_role;
create or replace user dbt_demo_user password = "dbtdemopassword";
grant role dbt_demo_role to user dbt_demo_user;
grant role dbt_demo_role to role sysadmin;

grant all on warehouse compute_wh to tole dbt_demo_role;
grant all on database raw to role dbt_demo_role;
grant all on database analytics to role dbt_demo_role;

grant all on all schemas in database raw to role dbt_demo_role;
grant all on all schemas in database analytics to role dbt_demo_role;
grant select on all tables in schema raw.jaffle_shop to role dbt_demo_role;

​​create table raw.jaffle_shop.customers
( id integer,
  first_name varchar,
  last_name varchar
);

copy into raw.jaffle_shop.customers (id, first_name, last_name)
from 's3://dbt-tutorial-public/jaffle_shop_customers.csv'
file_format = (
  type = 'CSV'
  field_delimiter = ','
  skip_header = 1
  );


create table raw.jaffle_shop.orders
( id integer,
  user_id integer,
  order_date date,
  status varchar,
  _etl_loaded_at timestamp default current_timestamp
);

copy into raw.jaffle_shop.orders (id, user_id, order_date, status)
from 's3://dbt-tutorial-public/jaffle_shop_orders.csv'
file_format = (
  type = 'CSV'
  field_delimiter = ','
  skip_header = 1
  );

select * from raw.jaffle_shop.customers;
select * from raw.jaffle_shop.orders;

$ pip install dbt-snowflake

$ dbt init jaffle_shop

04:11:36  Running with dbt=1.3.2
Which database would you like to use?
[1] snowflake
(Don't see the one you want? https://docs.getdbt.com/docs/available-adapters)
Enter a number: 1

account (https://<this_value>.snowflakecomputing.com): xx12345.ap-northeast-1.aws

user (dev username): dbt_demo_user

[1] password
[2] keypair
[3] sso
Desired authentication type option (enter a number): 1

password (dev password): dbtdemopassword <-実際は見えません

role (dev role): accountadmin

warehouse (warehouse name): compute_wh

database (default database that dbt will build objects in): analytics

schema (default schema that dbt will build objects in): jaffle_shop

threads (1 or more) [1]:

$ cat ~/.dbt/profiles.yml

jaffle_shop:
  outputs:
    dev:
      account: xx12345.ap-northeast-1.aws
      database: analytics
      password: dbtdemopassword
      role: dbt_demo_role
      schema: jaffle_shop
      threads: 1
      type: snowflake
      user: dbt_demo_user
      warehouse: compute_wh
  target: dev

$ dbt debug

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  account: xx12345.ap-northeast-1.aws
  user: dbt_demo_user
  database: analytics
  schema: jaffle_shop
  warehouse: compute_wh
  role: dbt_demo_role
  client_session_keep_alive: False
  Connection test: [OK connection ok]

All checks passed!

models:
  jaffle_shop:
    +materialized: table

## /models/marts_customers.sql

with customers as (
    select
        id as customer_id,
        first_name,
        last_name
    from raw.jaffle_shop.customers
),
orders as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from raw.jaffle_shop.orders
),
customer_orders as (
    select
        customer_id,
        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders
    from orders
    group by 1
),
final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders
    from customers
    left join customer_orders using (customer_id)
)

select * from final

$ dbt run

05:55:48  Running with dbt=1.3.2
05:55:49  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 303 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
05:55:49
05:55:51  Concurrency: 1 threads (target='dev')
05:55:51
05:55:51  1 of 1 START sql table model jaffle_shop.mart_customers ........................ [RUN]
05:55:53  1 of 1 OK created sql table model jaffle_shop.mart_customers ................... [SUCCESS 1 in 2.35s]
05:55:53
05:55:53  Finished running 1 table model in 0 hours 0 minutes and 4.46 seconds (4.46s).
05:55:53
05:55:53  Completed successfully
05:55:53
05:55:53  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1