dbt Projects on Snowflakeの初期セットアップ～一般的な開発＆本番実行手順のまとめ

USE ROLE ACCOUNTADMIN;

-- 1. warehouses 作成と権限付与
CREATE WAREHOUSE IF NOT EXISTS dbt_dev_wh
  WITH
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE IF NOT EXISTS dbt_prod_wh
  WITH
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

-- 2. 基本データベースとスキーマ作成

CREATE DATABASE raw_db;
CREATE SCHEMA raw_db.jaffle_shop;
CREATE SCHEMA raw_db.stripe;

CREATE DATABASE dev_db;
CREATE SCHEMA dev_db.dbt_ssagara;

CREATE DATABASE prod_db;
CREATE SCHEMA prod_db.jaffle_shop_staging;
CREATE SCHEMA prod_db.jaffle_shop_mart;
CREATE SCHEMA prod_db.dbt_projects; -- dbt Projectのデプロイとタスク作成用

-- 3. テーブル作成とデータロード

CREATE TABLE raw_db.jaffle_shop.customers (
  id INTEGER,
  first_name VARCHAR,
  last_name VARCHAR
);

COPY INTO raw_db.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_db.jaffle_shop.orders (
  id INTEGER,
  user_id INTEGER,
  order_date DATE,
  status VARCHAR,
  _etl_loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COPY INTO raw_db.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 raw_db.stripe.payment (
  id INTEGER,
  orderid INTEGER,
  paymentmethod VARCHAR,
  status VARCHAR,
  amount INTEGER,
  created DATE,
  _batched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COPY INTO raw_db.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
);

-- 4. ロール作成し、SYSADMINの子ロールに

USE ROLE SECURITYADMIN;
CREATE ROLE dbt_dev_role;
CREATE ROLE dbt_prod_role;

GRANT ROLE dbt_dev_role TO ROLE SYSADMIN;
GRANT ROLE dbt_prod_role TO ROLE SYSADMIN;

-- 5. 権限付与

-- 5A. warehouses の使用権限
GRANT USAGE,OPERATE ON WAREHOUSE dbt_dev_wh TO ROLE dbt_dev_role;
GRANT USAGE,OPERATE ON WAREHOUSE dbt_prod_wh TO ROLE dbt_prod_role;

-- 5B. raw_db に対する read 権限 + Future Objects（dev_role）

GRANT USAGE ON DATABASE raw_db TO ROLE dbt_dev_role;
GRANT USAGE ON SCHEMA raw_db.jaffle_shop TO ROLE dbt_dev_role;
GRANT USAGE ON SCHEMA raw_db.stripe TO ROLE dbt_dev_role;

GRANT SELECT ON ALL TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_dev_role;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_dev_role;

GRANT SELECT ON FUTURE TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_dev_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_dev_role;

GRANT SELECT ON ALL VIEWS IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_dev_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA raw_db.stripe TO ROLE dbt_dev_role;

GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_dev_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw_db.stripe TO ROLE dbt_dev_role;

GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_dev_role;
GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_dev_role;

GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_dev_role;
GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_dev_role;

-- 5C. raw_db に対する read 権限 + Future Objects（prod_role）

GRANT USAGE ON DATABASE raw_db TO ROLE dbt_prod_role;
GRANT USAGE ON SCHEMA raw_db.jaffle_shop TO ROLE dbt_prod_role;
GRANT USAGE ON SCHEMA raw_db.stripe TO ROLE dbt_prod_role;

GRANT SELECT ON ALL TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_prod_role;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_prod_role;

GRANT SELECT ON FUTURE TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_prod_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_prod_role;

GRANT SELECT ON ALL VIEWS IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_prod_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA raw_db.stripe TO ROLE dbt_prod_role;

GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_prod_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA raw_db.stripe TO ROLE dbt_prod_role;

GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_prod_role;
GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_prod_role;

GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA raw_db.jaffle_shop TO ROLE dbt_prod_role;
GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA raw_db.stripe TO ROLE dbt_prod_role;

-- 6D. dev_db.dbt_ssagaraに対する権限（dev_role のみ、作成・書き込み等）

GRANT USAGE ON DATABASE dev_db TO ROLE dbt_dev_role;
GRANT USAGE ON SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;

GRANT CREATE TABLE ON SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;
GRANT CREATE VIEW ON SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;
GRANT CREATE DYNAMIC TABLE ON SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;

GRANT OWNERSHIP ON ALL TABLES IN SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;
GRANT OWNERSHIP ON ALL DYNAMIC TABLES IN SCHEMA dev_db.dbt_ssagara TO ROLE dbt_dev_role;

-- 6E. prod_db.jaffle_shopに対する権限（prod_role のみ、作成・書き込み）

GRANT USAGE ON DATABASE prod_db TO ROLE dbt_prod_role;
GRANT USAGE ON SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;
GRANT USAGE ON SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;

GRANT CREATE TABLE ON SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;
GRANT CREATE VIEW ON SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;
GRANT CREATE DYNAMIC TABLE ON SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;

GRANT OWNERSHIP ON ALL TABLES IN SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;
GRANT OWNERSHIP ON ALL DYNAMIC TABLES IN SCHEMA prod_db.jaffle_shop_staging TO ROLE dbt_prod_role;

GRANT CREATE TABLE ON SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;
GRANT CREATE VIEW ON SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;
GRANT CREATE DYNAMIC TABLE ON SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;

GRANT OWNERSHIP ON ALL TABLES IN SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;
GRANT OWNERSHIP ON ALL DYNAMIC TABLES IN SCHEMA prod_db.jaffle_shop_mart TO ROLE dbt_prod_role;

-- 6F. prod_db.dbt_projectsに対する権限（タスク、dbt project関係）

GRANT USAGE ON SCHEMA prod_db.dbt_projects TO ROLE dbt_prod_role;

GRANT CREATE TASK ON SCHEMA prod_db.dbt_projects TO ROLE dbt_prod_role;
GRANT EXECUTE TASK ON ACCOUNT TO ROLE dbt_prod_role;
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO ROLE dbt_prod_role;

GRANT CREATE DBT PROJECT ON SCHEMA prod_db.dbt_projects TO ROLE dbt_prod_role;