Structure Snowflake database, schema

In-Depth Discussions
2

I do not know what’s “common”, but we decided on a few things when setting ours up:

  1. We use databases to separate user role permissions (raw for loaders to load raw data, dbt_dev for analysts to build development dbt models, and analytics for our production models).

  2. The raw database houses our raw data as ingested by connectors. Connectors use the loader role, which is the only role with write access to this db, and it does not have access to any other db. The schemas in this database are named with a {source}_{connector} convention (eg salesforce_stitch). The purpose here is twofold, 1) It makes it easy to identify which connector is being used to ingest the data, and 2) It removes any naming conflict awkwardness when changing a source connector (eg if moving to Fivetran we would just create salesforce_fivetran):

raw.salesforce_stitch.leads
raw.delighted_stitch.responses
raw.paycom_airflow.employees, etc.

  1. The dbt_dev database is for (surprise) our analysts to develop dbt models. Each analyst has a namespaced default schema (eg dbt_dev.dpearce). Analysts use the transformer role, which has read access to all dbs but can only write to the dbt_dev database.

dbt_dev.dpearce
dbt_dev.dpearce_base, etc.

  1. Finally, the analytics database is for our production models. We separate our base tables and transformed models into different schemas (analytics.base and analytics.analytics). Our base models use a naming convention of {source_table}. Only our Sinter account can write to this database. Our BI tool (Looker) is read-only and only has access to the analytics.analytics schema. So:

analytics.base.freshdesk_agents
analytics.base.paycom_employees
analytics.base.salesforce_users

analytics.analytics.employees, etc.

3 Likes