Snowflake Setting Up RBAC — Sample Script
Published in
11 min readNov 7, 2022
Databases are repositories of sensitive, confidential, and valuable business data. databases contain the gold of every organization. Successful businesses not only supply accurate and timely data, but they must also safeguard data as well. Security provides a critical competitive edge for any high-functioning database. RBAC, or Role-Based Access Controls, is a mechanism and set of tools that allow an organization to secure its data.
Database Security: A Shared Responsibility:
Database security is a shared responsibility between the vendor and the client who implements that database. The vendor must provide the tools and infrastructure to secure the data. The customer must implement the tools and protections in order to secure its data. Snowflake must prioritize protecting data in order to gain loyal customers who can trust the systems set in place to properly guard valuable information.
Some quick and easy recommendations:
Do not assign rights or permissions directly to users.
Do not assign high powered roles to users: accountadmin, useradmin, secureadmin, etc.
Do not use ACCOUNTADMIN to create objects.
Keep your custom roles simple and as flat as possible.
BUSINESS FUNCTION: Consider taking advantage of role hierarchies to align access to database objects with business functions in your organization. In a role hierarchy, roles are granted to other roles to form an inheritance relationship. Permissions granted to roles at a lower level are inherited by roles at a higher level.
For optimal flexibility in controlling access to database objects, create a combination of object access roles with different permissions on objects and assign them as appropriate to functional roles:
- Grant permissions on database objects or account objects (such as warehouses) to access roles.
- Grant access roles to functional roles to create a role hierarchy. These roles correspond to the business functions of your organization and serve as a catch-all for any access roles required for these functions.
- When appropriate, grant lower-level functional roles to higher-level functional roles in a parent-child relationship where the parent roles map to business functions that should subsume the permissions of the child roles.
- Following best practices for role hierarchies, grant the highest-level functional roles in a role hierarchy to the system administrator (SYSADMIN) role.
Please see the following script for a database creation with RBAC included. Please provide feedback if you do not agree or have suggestions.
SCRIPT:/*
-AUTHOR: JT
-DATE: 08/01/2022
-ORIGINATION OF FILE: jt
-NOTES:
THIS SCRIPT CREATES:
USERS
ROLES
GRANTS
WAREHOUSES
DATABASE
SCHEMA
TABLES
-FIRST DRAFT: 8/1/2022.
*/
— This is the first script you should run — CREATE WAREHOUSES (BEGIN)
— LOAD
USE ROLE ACCOUNTADMIN; — BATCH LOAD MEDIUM
CREATE OR REPLACE WAREHOUSE hcls_load_md WITH WAREHOUSE_SIZE = ‘MEDIUM’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 120 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = ‘ECONOMY’ COMMENT = ‘used for hcls batch load jobs’;
— QUERY XSMALL
CREATE OR REPLACE WAREHOUSE hcls_qry_xs WITH WAREHOUSE_SIZE = ‘XSMALL’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 120 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = ‘ECONOMY’ COMMENT = ‘used for hcls query jobs’;
— QUERY SMALL
CREATE OR REPLACE WAREHOUSE hcls_qry_sm WITH WAREHOUSE_SIZE = ‘SMALL’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 120 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = ‘ECONOMY’ COMMENT = ‘used for hcls query jobs’;
— QUERY MEDIUM
CREATE OR REPLACE WAREHOUSE hcls_qry_md WITH WAREHOUSE_SIZE = ‘MEDIUM’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 120 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = ‘ECONOMY’ COMMENT = ‘used for hcls query jobs’;
— QUERY LARGE
CREATE OR REPLACE WAREHOUSE hcls_qry_lg WITH WAREHOUSE_SIZE = ‘LARGE’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 120 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = ‘ECONOMY’ COMMENT = ‘used for hcls query jobs’; — SUSPEND ALL WAREHOUSES (THIS MIGHT FAIL IF THE WAREHOUSES ARE SUSPENDED)
ALTER WAREHOUSE “HCLS_QRY_XS” SUSPEND;
ALTER WAREHOUSE “HCLS_QRY_SM” SUSPEND;
ALTER WAREHOUSE “HCLS_QRY_MD” SUSPEND;
ALTER WAREHOUSE “HCLS_QRY_LG” SUSPEND;
ALTER WAREHOUSE “HCLS_LOAD_MD” SUSPEND;
— CREATE WAREHOUSES (END)
— CREATE USERS AND USER ROLES (BEGIN)
— create users/roles(groups) (SLIGHT CHANGE IN user creation with password predicate/Might want to set that to auto chang on login)
create USER hclsread PASSWORD =’hcls_pwd’;
create USER hcls_owner PASSWORD =’hcls_pwd’;
create USER hclsupdate PASSWORD =’hcls_pwd’;create or replace ROLE HCLS_OWNER_USER_ROLE;
create or replace ROLE HCLS_READER_USER_ROLE;
create or replace ROLE HCLS_UPDATER_USER_ROLE;grant role HCLS_READER_USER_ROLE to user hclsread;
grant role HCLS_UPDATER_USER_ROLE to user hclsupdate;
grant role HCLS_OWNER_USER_ROLE to user hcls_owner;
— CREATE USERS (END)
— create ROLES and GRANT new WAREHOUSES to ROLES (SLIGHT CHANGE IN SYNTAX WRT grants roles entitlements to USERS created above) CREATE FUNCTIONAL ROLES
create or replace ROLE HCLS_OWNER_ROLE;grant operate,usage on warehouse HCLS_QRY_XS to role HCLS_OWNER_ROLE;
grant operate,usage on warehouse HCLS_QRY_SM to role HCLS_OWNER_ROLE;
grant operate,usage on warehouse HCLS_LOAD_MD to role HCLS_OWNER_ROLE;
grant operate,usage on warehouse HCLS_QRY_MD to role HCLS_OWNER_ROLE;
grant operate,usage on warehouse HCLS_QRY_LG to role HCLS_OWNER_ROLE;
create or replace ROLE HCLS_READER_ROLE;
grant operate,usage on warehouse HCLS_QRY_SM to role HCLS_READER_ROLE;
grant operate,usage on warehouse HCLS_QRY_XS to role HCLS_READER_ROLE;
grant operate,usage on warehouse HCLS_QRY_MD to role HCLS_READER_ROLE; — grant role HCLS_READER_ROLE to user hclsread;create or replace ROLE HCLS_UPDATER_ROLE;grant operate,usage on warehouse HCLS_LOAD_MD to role HCLS_UPDATER_ROLE;
grant operate,usage on warehouse HCLS_QRY_SM to role HCLS_UPDATER_ROLE;
grant operate,usage on warehouse HCLS_QRY_XS to role HCLS_UPDATER_ROLE;
grant operate,usage on warehouse HCLS_QRY_MD to role HCLS_UPDATER_ROLE;
grant operate,usage on warehouse HCLS_QRY_LG to role HCLS_UPDATER_ROLE; — grant role HCLS_UPDATER_ROLE to user hclsupdate;grant role HCLS_OWNER_ROLE to role HCLS_OWNER_USER_ROLE;
grant role HCLS_UPDATER_ROLE to role HCLS_UPDATER_USER_ROLE;
grant role HCLS_READER_ROLE to role HCLS_READER_USER_ROLE; — grant role HCLS_OWNER_ROLE to user hcls_owner;
grant role HCLS_OWNER_USER_ROLE to user jthuma; — MY PERSONAL USER ADMIN ACCOUNT-NOT REQUIRED
grant role HCLS_UPDATER_USER_ROLE to user jthuma; — MY PERSONAL USER ADMIN ACCOUNT-NOT REQUIRED
grant role HCLS_READER_USER_ROLE to user jthuma; — MY PERSONAL USER ADMIN ACCOUNT-NOT REQUIRED
— DDL (BEGIN)
CREATE OR REPLACE DATABASE HCLS_DM;
USE DATABASE HCLS_DM;
CREATE OR REPLACE SCHEMA HCLS;
USE SCHEMA HCLS;
USE ROLE ACCOUNTADMIN;
grant ownership on database hcls_dm to HCLS_OWNER_ROLE;
grant ownership on schema hcls_dm.hcls to HCLS_OWNER_ROLE;
grant usage on database hcls_dm to HCLS_READER_ROLE;
grant usage on schema hcls_dm.hcls to HCLS_READER_ROLE;
grant select on all tables in schema hcls_dm.hcls to role HCLS_READER_ROLE;
grant usage on database hcls_dm to HCLS_UPDATER_ROLE;
grant usage on schema hcls_dm.hcls to HCLS_UPDATER_ROLE;
grant select,insert,update,delete on all tables in schema hcls_dm.hcls to role HCLS_UPDATER_ROLE;
grant ALL on SCHEMA hcls_dm.hcls to HCLS_OWNER_ROLE;grant usage on future schemas in database hcls_dm to role HCLS_UPDATER_ROLE;
grant usage on future schemas in database hcls_dm to role HCLS_READER_ROLE;
ALTER USER HCLS_OWNER SET DEFAULT_ROLE = ‘HCLS_OWNER_USER_ROLE’;
ALTER USER hclsupdate SET DEFAULT_ROLE = ‘HCLS_UPDATER_USER_ROLE’;
ALTER USER hclsread SET DEFAULT_ROLE = ‘HCLS_READER__USER_ROLE’;
USE ROLE HCLS_OWNER_USER_ROLE; — USE DATABASE snowflake54; — CREATE OR REPLACE SCHEMA cdm54; — snowflake CDM DDL Specification for OMOP Common Data Model 5.4CREATE OR REPLACE TABLE HCLS_DM.HCLS.PERSON (
person_id integer NOT NULL,
gender_concept_id integer NOT NULL,
year_of_birth integer NOT NULL,
month_of_birth integer NULL,
day_of_birth integer NULL,
birth_datetime TIMESTAMP NULL,
race_concept_id integer NOT NULL,
ethnicity_concept_id integer NOT NULL,
location_id integer NULL,
provider_id integer NULL,
care_site_id integer NULL,
person_source_value varchar(50) NULL,
gender_source_value varchar(50) NULL,
gender_source_concept_id integer NULL,
race_source_value varchar(50) NULL,
race_source_concept_id integer NULL,
ethnicity_source_value varchar(50) NULL,
ethnicity_source_concept_id integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.OBSERVATION_PERIOD (
observation_period_id integer NOT NULL,
person_id integer NOT NULL,
observation_period_start_date date NOT NULL,
observation_period_end_date date NOT NULL,
period_type_concept_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.VISIT_OCCURRENCE (
visit_occurrence_id integer NOT NULL,
person_id integer NOT NULL,
visit_concept_id integer NOT NULL,
visit_start_date date NOT NULL,
visit_start_datetime TIMESTAMP NULL,
visit_end_date date NOT NULL,
visit_end_datetime TIMESTAMP NULL,
visit_type_concept_id Integer NOT NULL,
provider_id integer NULL,
care_site_id integer NULL,
visit_source_value varchar(50) NULL,
visit_source_concept_id integer NULL,
admitting_source_concept_id integer NULL,
admitting_source_value varchar(50) NULL,
discharge_to_concept_id integer NULL,
discharge_to_source_value varchar(50) NULL,
preceding_visit_occurrence_id integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.VISIT_DETAIL (
visit_detail_id integer NOT NULL,
person_id integer NOT NULL,
visit_detail_concept_id integer NOT NULL,
visit_detail_start_date date NOT NULL,
visit_detail_start_datetime TIMESTAMP NULL,
visit_detail_end_date date NOT NULL,
visit_detail_end_datetime TIMESTAMP NULL,
visit_detail_type_concept_id integer NOT NULL,
provider_id integer NULL,
care_site_id integer NULL,
visit_detail_source_value varchar(50) NULL,
visit_detail_source_concept_id Integer NULL,
admitting_source_value Varchar(50) NULL,
admitting_source_concept_id Integer NULL,
discharge_to_source_value Varchar(50) NULL,
discharge_to_concept_id integer NULL,
preceding_visit_detail_id integer NULL,
visit_detail_parent_id integer NULL,
visit_occurrence_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CONDITION_OCCURRENCE (
condition_occurrence_id integer NOT NULL,
person_id integer NOT NULL,
condition_concept_id integer NOT NULL,
condition_start_date date NOT NULL,
condition_start_datetime TIMESTAMP NULL,
condition_end_date date NULL,
condition_end_datetime TIMESTAMP NULL,
condition_type_concept_id integer NOT NULL,
condition_status_concept_id integer NULL,
stop_reason varchar(20) NULL,
provider_id integer NULL,
visit_occurrence_id integer NULL,
visit_detail_id integer NULL,
condition_source_value varchar(50) NULL,
condition_source_concept_id integer NULL,
condition_status_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.DRUG_EXPOSURE (
drug_exposure_id integer NOT NULL,
person_id integer NOT NULL,
drug_concept_id integer NOT NULL,
drug_exposure_start_date date NOT NULL,
drug_exposure_start_datetime TIMESTAMP NULL,
drug_exposure_end_date date NOT NULL,
drug_exposure_end_datetime TIMESTAMP NULL,
verbatim_end_date date NULL,
drug_type_concept_id integer NOT NULL,
stop_reason varchar(20) NULL,
refills integer NULL,
quantity NUMERIC NULL,
days_supply integer NULL,
sig TEXT NULL,
route_concept_id integer NULL,
lot_number varchar(50) NULL,
provider_id integer NULL,
visit_occurrence_id integer NULL,
visit_detail_id integer NULL,
drug_source_value varchar(50) NULL,
drug_source_concept_id integer NULL,
route_source_value varchar(50) NULL,
dose_unit_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.PROCEDURE_OCCURRENCE (
procedure_occurrence_id integer NOT NULL,
person_id integer NOT NULL,
procedure_concept_id integer NOT NULL,
procedure_date date NOT NULL,
procedure_datetime TIMESTAMP NULL,
procedure_type_concept_id integer NOT NULL,
modifier_concept_id integer NULL,
quantity integer NULL,
provider_id integer NULL,
visit_occurrence_id integer NULL,
visit_detail_id integer NULL,
procedure_source_value varchar(50) NULL,
procedure_source_concept_id integer NULL,
modifier_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.DEVICE_EXPOSURE (
device_exposure_id integer NOT NULL,
person_id integer NOT NULL,
device_concept_id integer NOT NULL,
device_exposure_start_date date NOT NULL,
device_exposure_start_datetime TIMESTAMP NULL,
device_exposure_end_date date NULL,
device_exposure_end_datetime TIMESTAMP NULL,
device_type_concept_id integer NOT NULL,
unique_device_id varchar(50) NULL,
quantity integer NULL,
provider_id integer NULL,
visit_occurrence_id integer NULL,
visit_detail_id integer NULL,
device_source_value varchar(50) NULL,
device_source_concept_id integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.MEASUREMENT (
measurement_id integer NOT NULL,
person_id integer NOT NULL,
measurement_concept_id integer NOT NULL,
measurement_date date NOT NULL,
measurement_datetime TIMESTAMP NULL,
measurement_time varchar(10) NULL,
measurement_type_concept_id integer NOT NULL,
operator_concept_id integer NULL,
value_as_number NUMERIC NULL,
value_as_concept_id integer NULL,
unit_concept_id integer NULL,
range_low NUMERIC NULL,
range_high NUMERIC NULL,
provider_id integer NULL,
visit_occurrence_id integer NULL,
visit_detail_id integer NULL,
measurement_source_value varchar(50) NULL,
measurement_source_concept_id integer NULL,
unit_source_value varchar(50) NULL,
value_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.OBSERVATION (
observation_id integer NOT NULL,
person_id integer NOT NULL,
observation_concept_id integer NOT NULL,
observation_date date NOT NULL,
observation_datetime TIMESTAMP NULL,
observation_type_concept_id integer NOT NULL,
value_as_number NUMERIC NULL,
value_as_string varchar(60) NULL,
value_as_concept_id Integer NULL,
qualifier_concept_id integer NULL,
unit_concept_id integer NULL,
provider_id integer NULL,
visit_occurrence_id integer NULL,
visit_detail_id integer NULL,
observation_source_value varchar(50) NULL,
observation_source_concept_id integer NULL,
unit_source_value varchar(50) NULL,
qualifier_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.DEATH (
person_id integer NOT NULL,
death_date date NOT NULL,
death_datetime TIMESTAMP NULL,
death_type_concept_id integer NULL,
cause_concept_id integer NULL,
cause_source_value varchar(50) NULL,
cause_source_concept_id integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.NOTE (
note_id integer NOT NULL,
person_id integer NOT NULL,
note_date date NOT NULL,
note_datetime TIMESTAMP NULL,
note_type_concept_id integer NOT NULL,
note_class_concept_id integer NOT NULL,
note_title varchar(250) NULL,
note_text TEXT NOT NULL,
encoding_concept_id integer NOT NULL,
language_concept_id integer NOT NULL,
provider_id integer NULL,
visit_occurrence_id integer NULL,
visit_detail_id integer NULL,
note_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.NOTE_NLP (
note_nlp_id integer NOT NULL,
note_id integer NOT NULL,
section_concept_id integer NULL,
snippet varchar(250) NULL,
“offset” varchar(50) NULL,
lexical_variant varchar(250) NOT NULL,
note_nlp_concept_id integer NULL,
note_nlp_source_concept_id integer NULL,
nlp_system varchar(250) NULL,
nlp_date date NOT NULL,
nlp_datetime TIMESTAMP NULL,
term_exists varchar(1) NULL,
term_temporal varchar(50) NULL,
term_modifiers varchar(2000) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.SPECIMEN (
specimen_id integer NOT NULL,
person_id integer NOT NULL,
specimen_concept_id integer NOT NULL,
specimen_type_concept_id integer NOT NULL,
specimen_date date NOT NULL,
specimen_datetime TIMESTAMP NULL,
quantity NUMERIC NULL,
unit_concept_id integer NULL,
anatomic_site_concept_id integer NULL,
disease_status_concept_id integer NULL,
specimen_source_id varchar(50) NULL,
specimen_source_value varchar(50) NULL,
unit_source_value varchar(50) NULL,
anatomic_site_source_value varchar(50) NULL,
disease_status_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.FACT_RELATIONSHIP (
domain_concept_id_1 integer NOT NULL,
fact_id_1 integer NOT NULL,
domain_concept_id_2 integer NOT NULL,
fact_id_2 integer NOT NULL,
relationship_concept_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.LOCATION (
location_id integer NOT NULL,
address_1 varchar(50) NULL,
address_2 varchar(50) NULL,
city varchar(50) NULL,
state varchar(2) NULL,
zip varchar(9) NULL,
county varchar(20) NULL,
location_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CARE_SITE (
care_site_id integer NOT NULL,
care_site_name varchar(255) NULL,
place_of_service_concept_id integer NULL,
location_id integer NULL,
care_site_source_value varchar(50) NULL,
place_of_service_source_value varchar(50) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.PROVIDER (
provider_id integer NOT NULL,
provider_name varchar(255) NULL,
npi varchar(20) NULL,
dea varchar(20) NULL,
specialty_concept_id integer NULL,
care_site_id integer NULL,
year_of_birth integer NULL,
gender_concept_id integer NULL,
provider_source_value varchar(50) NULL,
specialty_source_value varchar(50) NULL,
specialty_source_concept_id integer NULL,
gender_source_value varchar(50) NULL,
gender_source_concept_id integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.PAYER_PLAN_PERIOD (
payer_plan_period_id integer NOT NULL,
person_id integer NOT NULL,
payer_plan_period_start_date date NOT NULL,
payer_plan_period_end_date date NOT NULL,
payer_concept_id integer NULL,
payer_source_value varchar(50) NULL,
payer_source_concept_id integer NULL,
plan_concept_id integer NULL,
plan_source_value varchar(50) NULL,
plan_source_concept_id integer NULL,
sponsor_concept_id integer NULL,
sponsor_source_value varchar(50) NULL,
sponsor_source_concept_id integer NULL,
family_source_value varchar(50) NULL,
stop_reason_concept_id integer NULL,
stop_reason_source_value varchar(50) NULL,
stop_reason_source_concept_id integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.COST (
cost_id integer NOT NULL,
cost_event_id integer NOT NULL,
cost_domain_id varchar(20) NOT NULL,
cost_type_concept_id integer NOT NULL,
currency_concept_id integer NULL,
total_charge NUMERIC NULL,
total_cost NUMERIC NULL,
total_paid NUMERIC NULL,
paid_by_payer NUMERIC NULL,
paid_by_patient NUMERIC NULL,
paid_patient_copay NUMERIC NULL,
paid_patient_coinsurance NUMERIC NULL,
paid_patient_deductible NUMERIC NULL,
paid_by_primary NUMERIC NULL,
paid_ingredient_cost NUMERIC NULL,
paid_dispensing_fee NUMERIC NULL,
payer_plan_period_id integer NULL,
amount_allowed NUMERIC NULL,
revenue_code_concept_id integer NULL,
revenue_code_source_value varchar(50) NULL,
drg_concept_id integer NULL,
drg_source_value varchar(3) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.DRUG_ERA (
drug_era_id integer NOT NULL,
person_id integer NOT NULL,
drug_concept_id integer NOT NULL,
drug_era_start_date TIMESTAMP NOT NULL,
drug_era_end_date TIMESTAMP NOT NULL,
drug_exposure_count integer NULL,
gap_days integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.DOSE_ERA (
dose_era_id integer NOT NULL,
person_id integer NOT NULL,
drug_concept_id integer NOT NULL,
unit_concept_id integer NOT NULL,
dose_value NUMERIC NOT NULL,
dose_era_start_date TIMESTAMP NOT NULL,
dose_era_end_date TIMESTAMP NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CONDITION_ERA (
condition_era_id integer NOT NULL,
person_id integer NOT NULL,
condition_concept_id integer NOT NULL,
condition_era_start_date TIMESTAMP NOT NULL,
condition_era_end_date TIMESTAMP NOT NULL,
condition_occurrence_count integer NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.METADATA (
metadata_concept_id integer NOT NULL,
metadata_type_concept_id integer NOT NULL,
name varchar(250) NOT NULL,
value_as_string varchar(250) NULL,
value_as_concept_id integer NULL,
metadata_date date NULL,
metadata_datetime TIMESTAMP NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CDM_SOURCE (
cdm_source_name varchar(255) NOT NULL,
cdm_source_abbreviation varchar(25) NULL,
cdm_holder varchar(255) NULL,
source_description TEXT NULL,
source_documentation_reference varchar(255) NULL,
cdm_etl_reference varchar(255) NULL,
source_release_date date NULL,
cdm_release_date date NULL,
cdm_version varchar(10) NULL,
vocabulary_version varchar(20) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CONCEPT (
concept_id integer NOT NULL,
concept_name varchar(255) NOT NULL,
domain_id varchar(20) NOT NULL,
vocabulary_id varchar(20) NOT NULL,
concept_class_id varchar(20) NOT NULL,
standard_concept varchar(1) NULL,
concept_code varchar(50) NOT NULL,
valid_start_date date NOT NULL,
valid_end_date date NOT NULL,
invalid_reason varchar(1) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.VOCABULARY (
vocabulary_id varchar(20) NOT NULL,
vocabulary_name varchar(255) NOT NULL,
vocabulary_reference varchar(255) NOT NULL,
vocabulary_version varchar(255) NULL,
vocabulary_concept_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.DOMAIN (
domain_id varchar(20) NOT NULL,
domain_name varchar(255) NOT NULL,
domain_concept_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CONCEPT_CLASS (
concept_class_id varchar(20) NOT NULL,
concept_class_name varchar(255) NOT NULL,
concept_class_concept_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CONCEPT_RELATIONSHIP (
concept_id_1 integer NOT NULL,
concept_id_2 integer NOT NULL,
relationship_id varchar(20) NOT NULL,
valid_start_date date NOT NULL,
valid_end_date date NOT NULL,
invalid_reason varchar(1) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.RELATIONSHIP (
relationship_id varchar(20) NOT NULL,
relationship_name varchar(255) NOT NULL,
is_hierarchical varchar(1) NOT NULL,
defines_ancestry varchar(1) NOT NULL,
reverse_relationship_id varchar(20) NOT NULL,
relationship_concept_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CONCEPT_SYNONYM (
concept_id integer NOT NULL,
concept_synonym_name varchar(1000) NOT NULL,
language_concept_id integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.CONCEPT_ANCESTOR (
ancestor_concept_id integer NOT NULL,
descendant_concept_id integer NOT NULL,
min_levels_of_separation integer NOT NULL,
max_levels_of_separation integer NOT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.SOURCE_TO_CONCEPT_MAP (
source_code varchar(50) NOT NULL,
source_concept_id integer NOT NULL,
source_vocabulary_id varchar(20) NOT NULL,
source_code_description varchar(255) NULL,
target_concept_id integer NOT NULL,
target_vocabulary_id varchar(20) NOT NULL,
valid_start_date date NOT NULL,
valid_end_date date NOT NULL,
invalid_reason varchar(1) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.DRUG_STRENGTH (
drug_concept_id integer NOT NULL,
ingredient_concept_id integer NOT NULL,
amount_value NUMERIC NULL,
amount_unit_concept_id integer NULL,
numerator_value NUMERIC NULL,
numerator_unit_concept_id integer NULL,
denominator_value NUMERIC NULL,
denominator_unit_concept_id integer NULL,
box_size integer NULL,
valid_start_date date NOT NULL,
valid_end_date date NOT NULL,
invalid_reason varchar(1) NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.COHORT_DEFINITION (
cohort_definition_id integer NOT NULL,
cohort_definition_name varchar(255) NOT NULL,
cohort_definition_description TEXT NULL,
definition_type_concept_id integer NOT NULL,
cohort_definition_syntax TEXT NULL,
subject_concept_id integer NOT NULL,
cohort_initiation_date date NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.ATTRIBUTE_DEFINITION (
attribute_definition_id integer NOT NULL,
attribute_name varchar(255) NOT NULL,
attribute_description TEXT NULL,
attribute_type_concept_id integer NOT NULL,
attribute_syntax TEXT NULL );CREATE OR REPLACE TABLE HCLS_DM.HCLS.COHORT (
cohort_definition_id integer NOT NULL,
subject_id integer NOT NULL,
cohort_start_date date NOT NULL,
cohort_end_date date NOT NULL );
