dbt Projects on Snowflake でアカウントレベルで環境が分離されている場合の開発を試してみる

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');
SET schema_name = concat($db_name,'.raw');
SET user_schema_name = concat($db_name,'.dbt_tyasuhara');

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

-- 基本データベースとスキーマ作成
CREATE DATABASE identifier($db_name);
CREATE SCHEMA identifier($schema_name);  -- rawデータ用スキーマ
CREATE SCHEMA identifier($user_schema_name); -- 開発者用スキーマ

-- テーブル作成とデータロード
USE SCHEMA identifier($schema_name);

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

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

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

COPY INTO 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 SECURITYADMIN;
CREATE ROLE identifier($role_name);
GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

-- 権限付与:warehouse
GRANT USAGE,OPERATE ON WAREHOUSE identifier($wh_name) TO ROLE identifier($role_name);

-- 権限付与:rawデータへの読み取り
GRANT USAGE ON DATABASE identifier($db_name) TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA identifier($schema_name) TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);

GRANT SELECT ON ALL VIEWS IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);

-- 権限付与:開発者スキーマへの書き込み権限
GRANT USAGE ON SCHEMA identifier($user_schema_name) TO ROLE identifier($role_name);
GRANT CREATE TABLE ON SCHEMA identifier($user_schema_name) TO ROLE identifier($role_name);
GRANT CREATE VIEW ON SCHEMA identifier($user_schema_name) TO ROLE identifier($role_name);

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 schema_name = concat($db_name,'.raw');
SET dbt_schema_name = concat($db_name,'.dbt_output');

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

-- 基本データベースとスキーマ作成
CREATE DATABASE identifier($db_name);
CREATE SCHEMA identifier($schema_name);  -- rawデータ用スキーマ
CREATE SCHEMA identifier($dbt_schema_name); -- dbt出力用スキーマ

-- テーブル作成とデータロード
USE SCHEMA identifier($schema_name);

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

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

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

COPY INTO 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 SECURITYADMIN;
CREATE ROLE identifier($role_name);
GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

-- 権限付与:warehouse
GRANT USAGE,OPERATE ON WAREHOUSE identifier($wh_name) TO ROLE identifier($role_name);

-- 権限付与:rawデータへの読み取り
GRANT USAGE ON DATABASE identifier($db_name) TO ROLE identifier($role_name);
GRANT USAGE ON SCHEMA identifier($schema_name) TO ROLE identifier($role_name);

GRANT SELECT ON ALL TABLES IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);

GRANT SELECT ON ALL VIEWS IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA identifier($schema_name) TO ROLE identifier($role_name);

-- 権限付与:dbt出力用スキーマへの書き込み権限
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);