[新機能]dbt Projects on Snowflake で Slim CI / Defer to Production が一般提供になりました

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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
);

CREATE TABLE IF NOT EXISTS raw.stripe.payment (
  id INTEGER,
  orderid INTEGER,
  paymentmethod VARCHAR,
  status VARCHAR,
  amount INTEGER,
  created DATE,
  _batched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COPY INTO raw.stripe.payment (id, orderid, paymentmethod, status, amount, created)
FROM 's3://dbt-tutorial-public/stripe_payments.csv'
FILE_FORMAT = (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  SKIP_HEADER = 1
);

USE ROLE ACCOUNTADMIN;

SET env_name = 'prd';
SET db_name = concat($env_name,'_db');
SET wh_name = concat($env_name,'_dbt_whs');
SET role_name = concat($env_name,'_dbt_role');
SET dbt_schema_name = concat($db_name,'.dbt_output');

CREATE WAREHOUSE IF NOT EXISTS identifier($wh_name)
  WITH
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS identifier($db_name);
CREATE SCHEMA IF NOT EXISTS identifier($dbt_schema_name);

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS identifier($role_name);
GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

GRANT USAGE, OPERATE ON WAREHOUSE identifier($wh_name) TO ROLE identifier($role_name);

GRANT USAGE ON DATABASE raw TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA raw.stripe TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw.stripe TO ROLE identifier($role_name);

-- 固定の dbt 出力スキーマへの書き込み権限
GRANT USAGE ON DATABASE identifier($db_name) TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA identifier($dbt_schema_name) TO ROLE identifier($role_name);
GRANT CREATE TABLE ON SCHEMA identifier($dbt_schema_name) TO ROLE identifier($role_name);
GRANT CREATE VIEW ON SCHEMA identifier($dbt_schema_name) TO ROLE identifier($role_name);

USE ROLE ACCOUNTADMIN;

SET env_name = 'dev';
SET db_name = concat($env_name,'_db');
SET wh_name = concat($env_name,'_dbt_whs');
SET role_name = concat($env_name,'_dbt_role');

CREATE WAREHOUSE IF NOT EXISTS identifier($wh_name)
  WITH
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

-- スキーマは開発者ごとに CREATE SCHEMA で作らせるため、ここでは DB のみ作成
CREATE DATABASE IF NOT EXISTS identifier($db_name);

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS identifier($role_name);
GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

GRANT USAGE, OPERATE ON WAREHOUSE identifier($wh_name) TO ROLE identifier($role_name);

GRANT USAGE ON DATABASE raw TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA raw.stripe TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw.stripe TO ROLE identifier($role_name);

-- 固定スキーマではなく、dev_db 配下に開発者ごとのスキーマを自由に作成できるようにする
GRANT USAGE ON DATABASE identifier($db_name) TO ROLE identifier($role_name);
GRANT CREATE SCHEMA ON DATABASE identifier($db_name) TO ROLE identifier($role_name);

USE ROLE ACCOUNTADMIN;

SET env_name = 'ci';
SET wh_name = concat($env_name,'_dbt_whs');
SET role_name = concat($env_name,'_dbt_role');

CREATE WAREHOUSE IF NOT EXISTS identifier($wh_name)
  WITH
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS identifier($role_name);
GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

GRANT USAGE, OPERATE ON WAREHOUSE identifier($wh_name) TO ROLE identifier($role_name);

GRANT USAGE ON DATABASE raw TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA raw.stripe TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw.jaffle_shop TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA raw.stripe TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw.stripe TO ROLE identifier($role_name);

-- --defer で未選択モデルの ref() が prd_db.dbt_output を直接参照するための読み取り権限
GRANT USAGE ON DATABASE prd_db TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA prd_db.dbt_output TO ROLE identifier($role_name);
GRANT SELECT ON ALL TABLES IN SCHEMA prd_db.dbt_output TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA prd_db.dbt_output TO ROLE identifier($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA prd_db.dbt_output TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA prd_db.dbt_output TO ROLE identifier($role_name);

-- dev_db 配下に PR ごとのスキーマを作成できるようにする（CREATE DATABASE ON ACCOUNT より狭い権限で済む）
GRANT USAGE ON DATABASE dev_db TO ROLE identifier($role_name);
GRANT CREATE SCHEMA ON DATABASE dev_db TO ROLE identifier($role_name);

version: 2

sources:
    - name: jaffle_shop
      description: This is a replica of the Postgres database used by our app
      database: raw
      schema: jaffle_shop
      tables:
          - name: customers
            description: One record per customer.
          - name: orders
            description: One record per order. Includes cancelled and deleted orders.

    - name: stripe
      description: Stripe payment data used by our app
      database: raw
      schema: stripe
      tables:
          - name: payment
            description: One record per payment. Includes payment method and status.

select
    id as customer_id,
    first_name,
    last_name,
    first_name || ' ' || last_name as full_name

from {{ source('jaffle_shop', 'customers') }}

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle_shop', 'orders') }}

with customers as (

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

),

orders as (

    select * from {{ ref('stg_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