2. Instantiate your Base Tables!

You can find an overview of this section here.

2.1. Base Table Creation!

2.1.1. Folder Path

Now onto the fun stuff! We will now create our base tables for our customer analytics data model.

Assuming you have your source configuration setup, we proceed to create our base folder Base folder, seen below:

Base Table Directory

2.1.2. Developing your Query

Our first example is creating the base table snowflake_demo_catalog_sales.sql, referenced here.

Suprisingly, there is a naming convention for this table. The convention looks as such:

  • {source}_{source_table_name}.sql

Note: This is by no means an industry standard. That being said, there are different approaches to this methodology in teams or groups you follow.

snowflake_demo_catalog_sales.sql

SELECT * 
FROM
    {{ source('customers', 'CATALOG_SALES')}}
LIMIT 10000 -- Limiting due to data limitation via Trial Account

2.1.3. dbt RUN!

Now, we run the following command:

dbt run --models snowflake_demo_catalog_sales

Running a model

2.2. Materialization in (Cloud) Database

So what happened?

We had a successful run to create our view or table into our database. Congrats!

Snowflake Base Table example

2.2.1. How did I get a Schema Prefix?

But you may ask, why is there a DEV_{name} as a prefix for the specified schema?

Well, this is because our default target is set to dev, from our profiles.yml file.

This file contains the specified schema prefix, seen in “schema”, below:

customer-analytics-db:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: {Some cool account name}

      # User/password auth
      user: {Super rad username}
      password: {3.1415926535897932384626433832795}

      role: ROLENAME
      database: DEMO_DB
      warehouse: COMPUTE_WH
      schema: dev_{ldap}

Where dev_{LADP} is dev_raulingaverage, in this case.

2.2.2. Aim on the Target

Okay, how can I change that to the production (prod) target version?

You can do this by specifying your target access and materialization by this command.

dbt run --target prod --models snowflake_demo_catalog_sales

Before, we implicitly called the following target, but it wasn’t shown because we didn’t present the --target FLAG

dbt run --target dev --models snowflake_demo_catalog_sales

Aim

2.3. Next Steps

Next, we’ll be going over how to best develop your data mart