"You know nothing" : can we start from the basics ?

For now, we haven't connected the Store to the database. To connect the Store to the database and start materializing our views and tables, we need to tell dbt where to look for the database credentials and connection information. We do this by creating a profiles.yml file.

What the heck is a profiles.yml file?

The need for a dbt profile file arises from the fact that dbt needs to know where your data is stored, how to connect to it, and other configuration details. By having a centralized configuration file, you can manage these settings in one place and switch between different environments (for example, development and production) without modifying your dbt models.

There are some key elements you might find in a dbt profile file:

1. Database connection details: It includes information such as the type of database (for example, BigQuery, Snowflake, Redshift, or SQL Server), host, database name, schema, and authentication details.
2. Profiles: You can define multiple profiles within the file, allowing you to switch between different environments or data warehouses easily.

In summary, the dbt profile file is essential for configuring and connecting dbt to your data warehouse, streamlining the data transformation process.

This file is not located in the project itself, because it contains credentials we don't want exposed through Git. Instead, it's usually located in the ~/.dbt folder.

How does dbt know which profile to use?

When you run dbt commands in a specific project, dbt determines which profile to use based on the configuration specified in the profiles.yml file. The profiles.yml file is typically located in the ~/.dbt/ directory.

Here's how dbt knows which profile to use for a specific project:

1. Default Profile:
   • If you have a single profile in your profiles.yml file, dbt will use that profile by default. This is the case when you only have one environment or data warehouse connection.
2. Project Configuration:
   • In the dbt_project.yml file within your dbt project directory, you can specify the profile to use for that specific project using the profile key. If this key is not set, dbt will use the default profile from the profiles.yml file.

Example dbt_project.yml:

name: my_project
version: 1.0

profile: my_custom_profile

In this example, dbt would use the profile named "my_custom_profile" for the "my_project" project.

By providing the profile information in the dbt_project.yml file, you can instruct dbt on which profile to use for a specific project.

What is the difference between a profile and a target?

In the context of dbt, a profile and a target serve different purposes:

1. Profile:
   • A dbt profile is a configuration file named profiles.yml that contains information about your data warehouse connection.
   • It includes details such as the type of database (e.g., BigQuery, Snowflake, Redshift), host, database name, schema, and authentication details.
   • You can define multiple profiles within the profiles.yml file, allowing you to configure connections for different environments (e.g., development, production).
   • Profiles are mainly concerned with specifying how dbt connects to a data warehouse.
   • The Dashboards Store should only use one profile.
2. Target:
   • A dbt target is a specific environment or deployment of your dbt project.
   • Targets are configurations for running dbt commands in a specific context, such as development or production.
   • Targets are often specified using the --target flag when running dbt commands, allowing you to switch between different environments easily.
   • Targets can reference specific profiles within the profiles.yml file, allowing you to use different data warehouse connections for different environments.
   • In summary, a profile is the overall configuration for connecting dbt to a data warehouse and is defined in the profiles.yml file. A target, on the other hand, is a specific configuration for running dbt in a particular context or environment, and it can reference a specific profile. Targets help you manage and switch between different deployment environments for your dbt project.

Got it. I need a profile. Can you help me get started ?

When you bootstrapped the cssXX.dashboards_store, you were prompted for some information:

• What's your database IP ?
• What's your database port ?

That information was used to render a profiles.yml sample, which you can find under cssXX.dashboards_store/profiles-sample.yml.

You now need to populate and move your profiles-sample.yml. You can do so by:

1. Adding your user's database password to connect to the database: edit the profiles-sample.yml file, and replace password: dontLookAtMeImSecret with your user's actual password.
2. Move profiles-sample.yml to ~/.dbt/profiles.yml:

mv profiles-sample.yml ~/.dbt/profiles.yml

Why do I have three targets in my profiles.yml?

Developing

You can see a target as an environment. Targets allow dbt to handle the different environments you might have. The profiles.yml default target is dev. Dev is configured to use your own name as a schema, and is the target you will mainly use to develop your models. Since it uses your name as a base database schema, every table you materialize and every code modification you make will not alter other people's materializations of the same project. By using targets, you can all work on the same project and the same database without stepping on each other's toes.

Staging and production

The profiles.yml contains two other targets:

1. staging: the staging target is used to test your code before deploying it to production. The staging target is configured to use the dbo schema in the store_dev database. You can use the staging schema to run your code at night and preview it before actually running against the production database.
2. prod: the prod target is used to deploy your code to production. It is configured to use the dbo schema in the store database. You should not explicitly use it unless you know what you are doing.

Ideally, your unreleased Power BI dashboards should be connected to the staging environment, and your released Power BI dashboards should be connected to the prod environment. By doing so, you can test modifications to dashboards and scripts without affecting your end users.