# dbt deps Command: Usage & Examples

## Introduction

Welcome to this tutorial on managing dependencies in [dbt](https://github.com/dbt-labs/dbt-core) using the **`dbt deps`** command. dbt is a powerful tool for transforming raw data into a format that’s useful for analysis. One of its key features is its ability to manage dependencies, ensuring that your data transformations run in the correct order. The **`dbt deps`** command is an integral part of this process.

## Understanding the dbt deps Command

The **`dbt deps`** command is used to manage the dependencies of your dbt project. It pulls the most recent version of the dependencies listed in your **`packages.yml`** file from git. This ensures that your project is always using the most up-to-date versions of its dependencies.

## Using the dbt deps Command

To use the **`dbt deps`** command, simply navigate to your dbt project directory in your terminal and run **`dbt deps`**. Here’s what the output might look like:

```bash
$ dbt deps

Installing fishtown-analytics/dbt_utils@0.7.1  
Installed from version 0.7.1  
Up to date!
Installing dbt-labs/codegen@0.4.0  
Installed from version 0.4.0  
Up to date!
```

This output tells you that dbt has installed the specified versions of the **`dbt_utils`** and **`codegen`** packages, and that these versions are up to date.

## Updating Packages

If you want to update a package to a newer version, you can do so by changing the version number in your **`packages.yml`** file. For example, if you wanted to update **`dbt_utils`** to version **`0.7.2`**, you would change your **`packages.yml`** file to look like this:

```yaml
packages:  
  - package: fishtown-analytics/dbt_utils    
  version: 0.7.2  

  - package: dbt-labs/codegen    
  version: 0.4.0

```

Then, you can run **`dbt deps`** again to install the updated version:

```bash
$ dbt deps

Installing fishtown-analytics/dbt_utils@0.7.2  
Installed from version 0.7.2  
Up to date!
Installing dbt-labs/codegen@0.4.0  
Installed from version 0.4.0  
Up to date!

```

## Troubleshooting Common Issues

If you run into any issues while using the **`dbt deps`** command, the first thing you should do is check the error message. It will often tell you what the problem is. Common issues include not having a **`packages.yml`** file, having a syntax error in your **`packages.yml`** file, or trying to install a version of a package that doesn’t exist.

## Conclusion

The **`dbt deps`** command is a powerful tool for managing your dbt project’s dependencies. By keeping your dependencies up to date, you can ensure that your project is always using the latest and greatest tools and techniques.

dbt deps

# dbt Artifacts Package: semantic_manifest, manifest, catalog, run_results, sources

[dbt](https://github.com/dbt-labs/dbt-core) is a transformative tool in the world of data analytics, enabling data professionals to transform and model data in the warehouse. One of its powerful features is the generation of **dbt artifacts**—structured outputs from dbt runs that provide insights into the dbt project and its operations.

## Basics of dbt Artifacts

dbt artifacts are JSON files generated every time dbt runs. They include:

- **`semantic_manifest.json`**: Contains the compiled SQL code for each model.
- **`manifest.json`**: Offers a comprehensive view of your dbt project at the time of the last run.
- **`catalog.json`**: Provides details about the database schema, including column data types and descriptions.
- **`run_results.json`**: Contains the results of the last dbt run, including success or failure status.
- **`sources.json`**: Details about the source data tables used in the project.

These artifacts are essential for documentation, understanding the state of your dbt project, and visualizing source freshness.

## Generating and Accessing Artifacts

Every time you invoke dbt, it generates artifacts. For instance, when you run:

```bash
dbt run

```

dbt will produce artifacts in the **`target/`** directory of your dbt project. You can access these JSON files directly and use tools like dbt's built-in documentation site to visualize their content.

## Practical Application: dbt artifacts Package

Brooklyn Data's **[dbt_artifacts package](https://hub.getdbt.com/brooklyn-data/dbt_artifacts/latest/)** is a powerful tool that models a dbt project and its run metadata. To utilize it:

### Installation

Add the package to your **`packages.yml`**:

```
packages:
  - package: brooklyn-data/dbt_artifacts
    version: 2.5.0

```

### Configuration

Adjust your **`dbt_project.yml`** to specify where data is uploaded:

```
models:
  dbt_artifacts:
    +database: your_destination_database
    +schema: your_destination_schema

```

### Usage

After setting up, run:

```bash
dbt run --select dbt_artifacts

```

## Advanced Usage with Elementary dbt Package

Another package that can be useful when using dbt artifacts is the **[Elementary dbt package](https://hub.getdbt.com/elementary-data/elementary/latest/).** It offers advanced artifact modeling capabilities:

### Uploading Artifacts

Elementary uses macros to extract fields from artifacts and insert them into tables, such as **`dbt_run_results`** and **`dbt_models`**.

### Model Execution

When you make changes to your dbt projects, run:

```bash
dbt run --models dbt_models

```

---

## Practical Examples

### Generate Artifacts for a Business Sales dbt Project

1. Create a dbt project focused on sales data.
2. Run the project using **`dbt run`**.
3. Explore the generated artifacts in the **`target/`** directory.

### Use the dbt Artifacts Package

1. Install the package as described above.
2. Configure it to upload data to a **`sales_artifacts`** schema.
3. Run the package and explore the generated tables.

## Best Practices and Tips

- **Consistency**: Always ensure that your dbt models are consistent in naming and structure. This ensures that your artifacts are reliable.
- **Optimization**: Use the **`run_results.json`** artifact to identify slow-running models and optimize them.
- **Collaboration**: Share your artifacts with team members to ensure everyone is aligned.

## Conclusion and Further Resources

dbt artifacts are a powerful feature that provides deep insights into your dbt projects. By understanding and utilizing them effectively, you can optimize your data transformation processes and ensure data reliability.

- [dbt artifacts package](https://hub.getdbt.com/brooklyn-data/dbt_artifacts/latest/)
- [elementary package](https://hub.getdbt.com/elementary-data/elementary/latest/) for loading artifacts into warehouse tables

dbt artifacts

# dbt build Command: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool used by data engineers and analysts to transform data in their warehouses more effectively. One of the essential commands in dbt is **`dbt build`**, which allows you to run models, test tests, snapshot snapshots, and seed seeds in your dbt project. This tutorial will guide you through understanding and using the **`dbt build`** command effectively.

## Understanding the dbt build Command

The **`dbt build`** command operates in Directed Acyclic Graph (DAG) order. This means it runs commands based on the dependencies between your models, tests, seeds, and snapshots. When you run **`dbt build`**, it creates two important artifacts: a single manifest and a single run results artifact. The manifest is a file that contains representations of all the resources in your dbt project, while the run results artifact contains detailed information about the output of the **`dbt build`** command, including executed models and tests, the time to run the models, test failure rates, and more.

## Setting Up dbt build

To use **`dbt build`**, you first need to have a dbt project set up. If you don't have one yet, you can create one using the **`dbt init`** command. Once your project is set up, you can use **`dbt build`** with various flags and options to control its behavior. For example, you can use the **`--select`** option to specify which models to run, or the **`--exclude`** option to specify which models to exclude. The **`--resource-type`** flag allows you to filter the resources that **`dbt build`** will operate on.

## Running dbt build

Let's say you have a dbt project with two models: **`orders`** and **`customers`**. The **`orders`** model depends on the **`customers`** model. Here's how you can run **`dbt build`** to build these models:

```bash
dbt build --select orders,customers

```

When you run this command, dbt will first build the **`customers`** model because the **`orders`** model depends on it. If the build for **`customers`** succeeds, dbt will then build the **`orders`** model. If the build for **`customers`** fails, dbt will skip the **`orders`** model.

## Understanding the Output of dbt build

The output of **`dbt build`** provides detailed information about the build process. For each model, test, seed, or snapshot that **`dbt build`** runs, it will show whether the operation was successful or not, and how long it took. If there are any errors, **`dbt build`** will also provide error messages to help you troubleshoot the issues.

For example, if you run **`dbt build`** on the **`orders`** and **`customers`** models, you might see output like this:

```bash
$ dbt build --select orders,customers

Running with dbt=0.21.0
Found 2 models, 0 tests, 0 snapshots, 0 analyses, 341 macros, 0 operations, 0 seed files, 0 sources

14:49:43 | Concurrency: 1 threads (target='dev')
14:49:43 |
14:49:43 | 1 of 2 START table model my_project.customers..................... [RUN]
14:49:43 | 1 of 2 OK created table model my_project.customers................ [SELECT 100 in 0.12s]
14:49:43 | 2 of 2 START table model my_project.orders........................ [RUN]
14:49:43 | 2 of 2 OK created table model my_project.orders................... [SELECT 500 in 0.15s]
14:49:43 |
14:49:43 | Finished running 2 table models in 0.45s.

Completed successfully

Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

```

This output tells you that **`dbt build`** successfully created the **`customers`** and **`orders`** models, selecting 100 and 500 rows respectively.

## Comparing dbt build vs run

While **`dbt build`** and **`dbt run`** might seem similar, they have significant differences. The most noticeable difference is in their operating principles. **`dbt build`** validates tests and then runs the downstream models, while **`dbt run`** runs prior to testing. This means that **`dbt build`** is optimized to obtain better data quality, while **`dbt run`** can lead to data quality issues if not used carefully.

For example, if you have a unique test on a column in the **`customers`** model and you use **`dbt run`** to build the models, **`dbt run`** will not check the unique test before building the **`orders`** model. This could lead to data quality issues in the **`orders`** model if there are duplicate values in the column in the **`customers`** model.

## Best Practices for Using dbt build

When using __`dbt build`__, it's important to follow best practices to ensure that your data transformations are reliable and efficient. Here are some tips:

- __`dbt build`__ is the preferred way to run models in production environments. This ensures that your data transformations are tested before they are run, which can help prevent data quality issues.
- Use the __`--full-refresh`__ flag when there is a change in the schema of the incremental model or if you want to reprocess the logic changes in the models.
- Use [graph operators](https://docs.getdbt.com/reference/node-selection/graph-operators) (__`+`__ before or after model names) or __`--select`__ and __`--exclude`__ to control which models, tests, seeds, and snapshots `dbt build` operates on. This can be useful for testing changes to specific parts of your dbt project.
- Regularly check the output of `dbt build` for errors and warnings. This can help you catch and fix issues early.

## Conclusion

The **`dbt build`** command is a powerful tool for data transformation in dbt. By understanding how it works and following best practices, you can use **`dbt build`** to create reliable and efficient data transformations in your dbt projects. Whether you're a data engineer or a data analyst, mastering **`dbt build`** is a valuable skill that can help you get the most out of dbt.

dbt build

# dbt clean & clean-targets Commands: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is an essential tool for data professionals, enabling them to transform, test, and document data in the modern analytics engineering workflow. As with any project, maintaining a clean environment is crucial for efficiency and accuracy. This tutorial will guide you through the **`dbt clean`** command, ensuring your dbt projects remain clutter-free.

## What is the `dbt clean` Command?

The **`dbt clean`** command is a utility function designed to help you manage your dbt project's environment. Its primary purpose is to delete specified folders, ensuring that your project remains organized and free from unnecessary files.

## The Role of `clean-targets` in `dbt_project.yml`

The **`clean-targets`** configuration in the **`dbt_project.yml`** file plays a pivotal role. It provides a list of directories and files that the **`dbt clean`** command should remove, offering a tailored cleaning experience for your project.

## Setting Up `clean-targets`

### Syntax and Structure

To specify which files or directories the **`dbt clean`** command should target, you'll use the **`clean-targets`** configuration. For instance:

```yaml
clean-targets:
  - "target/*"
  - "dbt_modules/*"
  - "sales_data_temp.log"

```

### Placement in the Project

Ensure that the **`clean-targets`** configuration is placed within the **`dbt_project.yml`** file, which should be located in your dbt project's root directory.

### Using Wildcards

Wildcards, represented by the `*****` character, can be incredibly useful. For example, **`target/*`** will match all files and directories within the **`target`** directory, ensuring a thorough cleanup.

## Types of Files and Directories to Clean

In a business context, you might have directories related to specific departments or projects, such as:

- **`finance_reports/`**
- **`marketing_data/`**
- **`temp_files/`**

You might also have log files or temporary data files from various business operations:

- **`Q1_sales_temp.csv`**
- **`employee_data_log.log`**

To ensure these are cleaned, you'd add them to your **`clean-targets`** configuration.

## Dependencies and the `dbt clean` Command

When specifying directories or files in **`clean-targets`**, be aware of dependencies. If a file or directory is crucial for another part of your dbt project, removing it might cause issues. Always review dependencies before running the **`dbt clean`** command.

## Safety Precautions

Before executing the **`dbt clean`** command:

- **Backup**: Ensure you have backups of essential data or files.
- **Review**: Double-check the **`clean-targets`** configuration to avoid unintentional deletions.
- **Test**: Consider running the command in a test environment first.

## Limitations of the `dbt clean` Command

The **`dbt clean`** command doesn't work when interfacing with the RPC server powering the dbt Cloud IDE. In such cases, use the **`dbt deps`** command, which cleans before installing packages. If you're using dbt Cloud, you can manually delete the **`target`** folder from the sidebar file tree.

## Practical Exercise

Let's simulate a business scenario:

Imagine you're handling data for a retail company. Over time, you've accumulated temporary sales data, logs from marketing campaigns, and old finance reports.

1. **Setup**: Create a sample DBT project with directories: **`sales_data/`**, **`marketing_logs/`**, and **`finance_reports/`**.
2. **Populate**: Add some dummy data and logs to these directories.
3. **Configure `clean-targets`**:

```yaml
clean-targets:
  - "sales_data/temp_sales*"
  - "marketing_logs/campaign_2022.log"
  - "finance_reports/Q1_2022_report_temp.csv"

```

4. **Run**: Execute the **`dbt clean`** command and observe the specified files and directories being removed.

## Conclusion

The **`dbt clean`** command is a powerful tool in your dbt toolkit. By understanding its functionality and best practices, you can maintain a streamlined and efficient dbt project environment, especially crucial in dynamic business settings.

## Additional Resources

- [dbt clean documentation](https://docs.getdbt.com/reference/commands/clean)
- [clean-targets documentation](https://docs.getdbt.com/reference/project-configs/clean-targets)

dbt clean

# dbt CLI Overview & Install

## Why dbt CLI?

dbt CLI (Command Line Interface) is the heart of dbt. It allows users to run and manage their dbt projects directly from the command line, offering flexibility and integration capabilities.

## Setting Up the dbt CLI

### Requirements

- You'll need a dbt project, so if you don't already have one, you can [use this template](https://github.com/popsql/dbt-template) to get things started. 
- Install [dbt Core](https://github.com/dbt-labs/dbt-core) locally on your computer, or [sign up for PopSQL](https://popsql.com/users/sign_up) and connect your dbt project to skip this step.
- If developing locally, you'll also need a code editor. Popular choices include VSCode and Sublime for writing and managing dbt models.

### Installation for local development

- Download and install dbt Core from the official website or use package managers like **`pip`**.
- Verify the installation by running **`dbt --version`** in your terminal.

### Navigating the CLI

- Use **`pwd`** to check your current directory.
- Navigate using **`cd`** and list files with **`ls`**.
- For assistance, use the **`-help`** flag, e.g., **`dbt --help`** to view available commands.

These navigation issues aren't really necessary if you're doing your dbt development in PopSQL since you can easily navigate your files using the file explorer. You'll only need the CLI for reading logs and triggering advanced commands that aren't available using PopSQL's dbt action button.

![dbt CLI in PopSQL](//images.ctfassets.net/iv1sg9nibjwl/1oDxhzTQuYHew7osrTG2XJ/e2474a9cf534af63d6ee8b7e5fb5ee01/Screenshot_2023-09-15_at_9.59.11_AM.png)

## Creating and Managing a dbt Project using the dbt CLI

### Starting a New Project

- Use [dbt init](/learn-dbt/dbt-init) to create a new dbt project.
- This command generates a directory with essential files and structures for your dbt models, tests, and documentation.

### Setting the Working Directory

Ensure you're in the dbt project directory before running any dbt commands. This is crucial for dbt to recognize and manage your project files. If you're using PopSQL, we'll handle this step automatically.

### Using the `--help` Flag

Whenever in doubt, use **`dbt [command] --help`** to get a detailed explanation and available options for the command. You can also read through [more dbt tutorials here](/learn-dbt).

### Best Practices and Tips

- Always ensure you're in the right directory before running dbt commands.
- Regularly update your dbt CLI to benefit from the latest features and security patches.
- Integrate dbt with CI/CD pipelines for automated testing and deployment, ensuring data quality and consistency.

### Conclusion

- The dbt CLI is a powerful tool for modern data teams, allowing for efficient data transformation and modeling directly from the command line.
- With integrations like Spectacles and Keboola, dbt becomes even more versatile, catering to a wide range of data workflows.
- Try out a hosted dbt option like PopSQL to make setup and management easier, especially if you're not familiar with the local software development processes. 

### Additional Resources

- [PopSQL dbt Documentation](https://docs.popsql.com/docs/dbt)
- [Template dbt project](https://github.com/popsql/dbt-template)


dbt CLI

# dbt Cheat Sheet: 11 dbt Commands You Need to Know

Welcome to this comprehensive tutorial on dbt commands. [dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool used by data analysts and engineers to transform data in their warehouses. It follows the principles of transformations as code, version control, modularity, and testing. In this tutorial, we will walk you through the essential dbt commands, provide descriptions for each, and offer syntax examples to help you understand their usage. Let’s dive in!

## Understanding dbt commands

dbt commands are the primary way you’ll interact with dbt. Each command performs a specific action in your dbt project, from creating a new project to running transformations and tests. Understanding these commands and their functions is crucial to effectively using dbt.

## Basic dbt commands

### `dbt init`

This command creates a new dbt project. It sets up the necessary directory structure and provides some example models to get you started. Here’s how you use it:

```bash
dbt init my_new_project
```

### `dbt debug`

This command checks your dbt project setup. It verifies that your profiles.yml file is set up correctly, that dbt can connect to your data warehouse, and that all dependencies are installed. Here’s how you use it:

```bash
dbt debug
```

### `dbt deps`

This command is used to download and manage the dbt packages that your project depends on. It reads your packages.yml file and installs all the packages listed there. Here’s how you use it:

```bash
dbt deps
```

### `dbt clean`

This command cleans up your dbt project by deleting the dbt_modules and target directories. It’s useful when you want to ensure that you’re working with the most recent versions of your dbt packages and models. Here’s how you use it:

```bash
dbt clean
```

## Data Loading and Transformation

### `dbt seed`

This command loads CSV files into your data warehouse. It’s useful when you have small data files that you want to use in your transformations. Here’s how you use it:

```bash
dbt seed
```

### `dbt run`

This command runs transformations in your dbt project. It uses the model SQL files in your project to transform your raw data into analysis-ready tables. Here’s how you use it:

```bash
dbt run
```

### `dbt snapshot`

This command is used to track changes in your data over time. It’s useful when you have slowly changing dimensions and you want to keep a history of changes. Here’s how you use it:

```bash
dbt snapshot
```

## Testing in dbt

### `dbt test`

This command runs tests in your dbt project. It’s used to ensure that your transformations are working as expected and that your data meets the defined quality standards. Here’s how you use it:

```bash
dbt test
```

### `dbt compile`

This command compiles your dbt project to check for errors. It doesn’t run the transformations, but it does generate the SQL that would be run, which can be useful for debugging. Here’s how you use it:

```bash
dbt compile
```

## Documentation and Metadata

### `dbt docs generate`

This command generates documentation for your dbt project. It reads your model, test, and schema files and creates a set of documentation that describes your project. Here’s how you use it:

```bash
dbt docs generate
```

### `dbt docs serve`

This command serves your dbt project documentation locally, allowing you to view it in your web browser. Here’s how you use it:

```bash
dbt docs serve
```

## Conclusion

By now, you should have a good understanding of the essential dbt commands and how to use them in your data transformation projects. Remember, practice is key when it comes to mastering these commands. So, don’t hesitate to create your own dbt project and start transforming data!

dbt commands

# dbt compile Command: Usage & Examples

## Introduction

Welcome to this tutorial on the **`dbt compile`** command. [dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. The **`dbt compile`** command is a crucial part of this tool, and it’s used to generate executable SQL from source model, test, and analysis files.

## Understanding the dbt compile Command

The **`dbt compile`** command takes your dbt project’s source files and generates executable SQL code. This code is stored in the **`target/`** directory of your dbt project.

For example, if you have a model file **`orders.sql`** with the following content:

```sql
{{ config(materialized='view') }}

select * 
from raw_data.orders
where order_date >= '2023-01-01'

```

Running **`dbt compile`** will generate a SQL file in the **`target/`** directory that might look something like this:

```sql
CREATE OR REPLACE VIEW analytics.orders AS

select * 
from raw_data.orders
where order_date >= '2023-01-01'

```

## Use Cases for dbt compile

### Inspecting Compiled Output

The **`dbt compile`** command is useful for visually inspecting the compiled output of model files. This is particularly useful for validating complex jinja logic or macro usage.

For example, if you have a model file **`customers.sql`** that uses a macro to calculate the lifetime value of a customer:

```sql
{{ config(materialized='table') }}

select    
  customer_id,    
  {{ lifetime_value('order_total') }} as  lifetime_value

from raw_data.orders

```

Running **`dbt compile`** will allow you to see the actual SQL that gets generated and run against your database.

### Manually Running Compiled SQL

While debugging a model or schema test, it’s often useful to execute the underlying select statement to find the source of the bug. **`dbt compile`** allows you to do this.

### Compiling Analysis Files

**`dbt compile`** can also be used to compile analysis files. These are SQL files that allow you to perform ad-hoc analyses and explorations.

## Common Misconceptions about dbt compile

### Not a Prerequisite for dbt run

A common misconception is that **`dbt compile`** is a prerequisite for **`dbt run`** or other building commands. This is not the case. Those commands will handle compilation themselves.

### Use dbt parse for Validation

If you just want dbt to read and validate your project code, without connecting to the data warehouse, use **`dbt parse`** instead of **`dbt compile`**.

## Advanced Usage of dbt compile

The **`dbt compile`** command accesses the data platform to cache-related metadata and to run introspective queries.

You can use the **`--no-populate-cache`** flag to disable the initial cache population. If metadata is needed, it will be a cache miss, requiring dbt to run the metadata query.

You can also use the **`--no-introspect`** flag to disable introspective queries. If a model’s definition requires running an introspective query and this flag is used, dbt will raise an error.

## Conclusion

In this tutorial, we’ve covered the basics of the **`dbt compile`** command, including what it does, how to use it, and some common misconceptions about it. We’ve also looked at some advanced usage options. With this knowledge, you should be able to use **`dbt compile`** effectively in your dbt projects.

dbt compile

# dbt DAG: Definition, Usage, and Examples

**Overview of dbt**: [dbt](https://github.com/dbt-labs/dbt-core) is a transformation tool that allows data analysts and engineers to transform and model data in the data warehouse. It's a popular tool in the modern analytics engineering workflow.

**DAG in Analytics**: A [Directed Acyclic Graph](https://en.wikipedia.org/wiki/Directed_acyclic_graph) (DAG) is a type of graph where nodes represent tasks and edges represent dependencies between these tasks. In the context of dbt, these tasks are often data models, which are saved as SQL statements and result in data transformations. 

## Basics of DAG

**What is a DAG?**: When people refer to "the DAG" in the context of dbt, they usually mean the graph with directionally related nodes. The nodes can flow left to right and even back in some cases, as long as they don't form a closed loop. A closed loop would mean there's a circular dependency and the system will not work.

**Application**: In businesses, DAGs ensure that data processes run in the correct sequence. For instance, sales data might need to be cleaned and transformed before it's merged with inventory data.

## DAGs in dbt

**dbt's Lineage Graph**: This is a visual representation of your dbt project's DAG. It showcases how different data models relate to each other.

**DAGs and Data Models**: In dbt, each model represents a SQL transformation. The DAG ensures models run in the correct order based on their dependencies.

## Unpacking Relationships with dbt dag

**Upstream and Downstream Models**:

- **Upstream models** are the ones your current model depends on.
- **Downstream models** are those that depend on your current model.

**dbt Lineage Graph**: In this graph, upstream models are to the left, and downstream models are to the right. The arrows indicate the flow of data.

**Example**: Consider a business with sales and inventory data. The **`sales_cleaned`** model (which cleans raw sales data) might be upstream, and a **`sales_inventory_merged`** model (which merges sales and inventory data) would be downstream.

## Auditing Projects with a dbt DAG

- **Unique DAGs**: Every business has unique data needs, leading to unique DAGs.
- **Diagnosing Inefficiencies**: If a model takes too long to run, it might be due to expensive joins or complex logic. The DAG can help identify such bottlenecks.

## Modular Data Modeling in dbt

**Traditional vs. Modular**: Traditional modeling often pulls directly from raw sources. Modular modeling, recommended for scalability, uses intermediary stages.

**Best Practices**:

- **Staging Layers**: Start with a staging model to clean raw data. For instance, **`stg_sales`** for raw sales data.
- **Intermediate Layers**: Transform staged data further, like calculating total sales in **`int_total_sales`**.
- **Mart Layers**: Final transformations, like merging sales and inventory in **`mart_sales_inventory`**.

## Advanced Features in dbt related to DAG

- **[dbt Project Evaluator Package](https://github.com/dbt-labs/dbt-project-evaluator)**: This tool audits your dbt project, suggesting improvements.
- **[dbt Docs](/learn-dbt/dbt-docs)**: A feature that visualizes your DAG, helping you understand data flow and dependencies.
- **[Exposures in dbt](/learn-dbt/dbt-exposures)**: This newer feature lets you define downstream uses of your data models, like dashboards or machine learning pipelines, enhancing transparency.

## Practical Exercise: Building Your Own dbt DAG

**Setting up**: Install dbt and [initiate a new project](/learn-dbt/dbt-init).

### Creating Models

Below is the model code for three models that are all connected in the DAG. 

__Raw sales staging model:__

```sql
-- stg__sales.sql

select 
  date(created_at) as date,
  to_double(amount/100.00) as amount

from {{ source('raw', 'sales') }};

```

__Total sales intermediate model:__

```sql
-- int__total_sales.sql

select 
  date, 
  sum(amount) as total_sales 

from {{ ref('stg__sales') }} 
group by date;

```

__Sales inventory mart:__

```sql
-- mart__sales_inventory.sql

select 
  s.date, 
  s.total_sales, 
  i.total_inventory

from  {{ ref('int__total_sales') }}  s

  inner join {{ ref('stg__inventory') }} i 
    on s.date = i.date;

```

- **Visualizing the DAG**: Run **`dbt run`** to execute the models, then **`dbt docs serve`** to view the Lineage Graph on the hosted docs page. For this example, the DAG would look like this:

![simiple dbt DAG](//images.ctfassets.net/iv1sg9nibjwl/7uioLyGPUUipEoN5h7N0Bm/f68d04a6aa85fea5eefa398aeec0beb2/Screenshot_2023-09-15_at_9.10.12_AM.png)

## Conclusion

- **Recap**: DAGs in dbt ensure data transformations run in the correct order, providing a visual representation of data flow and dependencies.
- **Next Steps**: Explore more [advanced dbt features](https://popsql.com/learn-dbt) and keep refining your DAG for efficiency.

## Additional Resources

- [Learn about dbt docs](/learn-dbt/dbt-docs)
- [How to write a dbt model](/blog/dbt-models)

dbt DAG

# dbt Debug Command: Usage & Examples

## Introduction

Welcome to this tutorial on the **`dbt debug`** command. [dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool used by data professionals to transform data in their warehouses more effectively. Debugging is a crucial part of any data transformation process, and dbt provides a built-in command for this purpose: **`dbt debug`**.

## Understanding the dbt debug Command

The **`dbt debug`** command is a utility function that tests the database connection and displays information for debugging purposes. It checks the validity of your project file and your installation of any requisite dependencies (like git). It’s important to distinguish this from the **`--debug`** option, which increases logging verbosity but doesn’t perform the same checks as **`dbt debug`**.

## Using the dbt debug Command

To use **`dbt debug`**, navigate to your dbt project directory and run the command:

```bash
dbt debug

```

This will output information about your dbt installation, your project, and your database connection. If there are any issues with your setup, **`dbt debug`** will highlight them.

## Example: Finding the Location of the profiles.yml File

The **`profiles.yml`** file is where dbt stores database connection information. You can use **`dbt debug`** to find its location:

```bash
dbt debug --config-dir

```

This will output the directory where **`profiles.yml`** is located. To view the file, you can use the **`open`** command:

```bash
open /path/to/profiles.yml

```

Replace **`/path/to/`** with the output from the previous command.

## Common Issues and How to Debug Them

Let’s say you’re working on a business analytics project and you’ve set up a dbt project to transform your sales data. You run your transformations, but the output isn’t what you expected. This is where **`dbt debug`** comes in.

Run **`dbt debug`** and check the output. If there’s an issue with your database connection, **`dbt debug`** will tell you. If your project file is invalid, **`dbt debug`** will tell you. By using **`dbt debug`**, you can quickly identify and fix issues with your dbt setup.

## Conclusion

The **`dbt debug`** command is a powerful tool for identifying and fixing issues with your dbt setup. By using **`dbt debug`**, you can ensure that your data transformations run smoothly and produce the expected results.

dbt debug

# dbt docs generate & serve: Command Usage and Examples

## **Introduction**

[dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the key features of dbt is its ability to generate documentation for your data models, which is where the **`dbt docs`** command comes into play. This tutorial will guide you through the process of generating and serving your project documentation using dbt.

## **Understanding the dbt docs Command**

The **`dbt docs`** command has two subcommands: **`generate`** and **`serve`**. The **`generate`** command is used to create your project’s documentation, while the **`serve`** command is used to view this documentation in a web browser.

## **Generating Documentation with dbt docs generate**

To generate documentation for your dbt project, navigate to the root of your dbt project in your terminal and run the following command:

```bash
dbt docs generate

```

This command will create a static site with documentation for your project. The site includes information about your models, tests, sources, and more.

### **Serving Documentation Locally with**

After generating the documentation, you can view it locally using the **`serve`** command. Run the following command in your terminal:

```bash
dbt docs serve

```

This will start a web server and open the documentation in your default web browser. You can navigate through the documentation to view information about your dbt project.

### **Exploring the Documentation**

The generated documentation provides a wealth of information about your dbt project. You can switch between a view of your project’s folder hierarchy and a database-focused collection of tables and views using the Project/Database toggle. You can also use the search bar to find specific models in your project.

### **Visualizing Data Lineage**

One of the powerful features of dbt docs is its ability to visualize the relationships between your models. You can access this feature by clicking on the “Lineage” tab in the model’s page. This will show a graph of all the models that are upstream or downstream of the selected model, providing a clear view of your data’s lineage.

### **Uploading Documentation to re_cloud**

If you want to share your documentation with others, you can upload it to re_cloud. To do this, you need to [set up re_cloud](https://docs.getre.io/latest/docs/re_cloud/whatis_cloud) and run the following command in your dbt project directory:

```bash
re_cloud upload dbt-docs --name your_project_name

```

This will upload your generated documentation to re_cloud, where it can be accessed by others in your organization.

## dbt docs Example

Assume we have a dbt project with a simple model that transforms raw sales data into a more useful format. The model is defined in a file called **`sales.sql`**:

```sql
-- models/sales.sql
{{ config(materialized='table') }}

select    
  order_id,    
  product_id,    
  customer_id,    
  quantity,    
  price,    
  quantity * price as total_price,    
  order_date

from raw.sales

```

We also have a **`schema.yml`** file that describes this model:

```yaml
# models/schema.yml
version: 2

models:  
  - name: sales    
  description: This table contains transformed sales data.    

  columns:      
    - name: order_id        
    description: The unique identifier for each order.      

    - name: product_id        
    description: The unique identifier for each product.      

    - name: customer_id        
    description: The unique identifier for each customer.      

    - name: quantity        
    description: The quantity of the product sold in the order.      

    - name: price        
    description: The price of the product.      

    - name: total_price        
    description: The total price of the order, calculated as quantity * price.      

    - name: order_date        
    description: The date the order was placed.

```

Now, we can generate documentation for this model using **`dbt docs generate`**. In your terminal, navigate to the root of your dbt project and run:

```bash
dbt docs generate

```

This command will generate a **`target`** directory in your dbt project. Inside this directory, you’ll find a **`manifest.json`** file and a **`catalog.json`** file. These files contain metadata about your dbt project and are used to generate the documentation website.

Next, you can serve the documentation locally using **`dbt docs serve`**:

```bash
dbt docs serve

```

This command will start a web server and open your default web browser to the documentation site. Here, you’ll see a page for the **`sales`** model with all the column descriptions and other information you defined in the **`schema.yml`** file.

## **Conclusion**

The **`dbt docs`** command is a powerful tool for generating and serving documentation for your dbt projects. By using this command, you can ensure that your team and other stakeholders have access to up-to-date, accurate information about your data models.

dbt docs

# dbt Exposures: Usage & Examples

[dbt](https://github.com/dbt-labs/dbt-core) has revolutionized the way data teams view and handle transformations. Within this ecosystem, dbt exposures stand out as a powerful feature that bridges the gap between raw data models and their downstream applications. In essence, exposures allow you to define and describe how your dbt project is utilized downstream, be it in a dashboard, application, or data science pipeline.

## Why Use dbt Exposures?

Imagine making a change to a core data model and not knowing how it might affect the monthly sales dashboard the CEO looks at. This is where exposures come in. They provide:

- **Downstream Impact Analysis**: Understand potential impacts of changes made to data models.
- **Time-saving**: No need to reverse engineer data sources or analytics assets.
- **Enhanced Documentation**: Every exposure gets its dedicated page in the autogenerated documentation site, providing clarity to data consumers.

## Defining Exposures: A Step-by-Step Guide

### Understanding Downstream Usage

Before defining an exposure, identify what downstream tools or reports rely on your dbt models. For instance, a sales dashboard in Looker might be built on top of a **`monthly_sales`** dbt model.

### Creating the `.yml` File

Navigate to your dbt project directory. Create or open an existing **`.yml`** file where you'll define your exposures.

### Defining the Exposure Properties

Here's a business-related example:

```yaml
version: 2

exposures:
  - name: sales_dashboard_exposure
    type: dashboard
    owner:
      name: Marty McFly
      email: marty@FrisbiePieCompany.com
    depends_on:
      - ref('monthly_sales')
    description: Monthly sales dashboard used by the executive team.
    url: https://looker.company.com/dashboards/123
    maturity: high

```

### Linking Exposures to dbt Models

The **`depends_on`** property links the exposure to its upstream dbt models. In our example, the Looker dashboard depends on the **`monthly_sales`** dbt model.

## Working with Exposures in dbt

After defining exposures, you can reference them in the [dbt CLI](/learn-dbt/dbt-cli).

__Run models feeding into an exposure:__

```bash
dbt run -s +exposure:sales_dashboard_exposure

```

__Test these models:__ 

```bash 
dbt test -s +exposure:sales_dashboard_exposure

```

## Automating Exposures

While defining exposures manually is powerful, automation tools like AirOps can generate dbt exposures automatically after a data model is built, ensuring exposures are always up-to-date.

## Visualizing Exposures in the dbt Docs Site

Generate your dbt docs with **`dbt docs generate`** and view them with **`dbt docs serve`**. Navigate to the exposures section, and you'll see a dedicated page for each exposure, complete with all the details you provided.

## Best Practices for Using dbt Exposures

- **Regularly Update Exposures**: As your data models evolve, ensure your exposures reflect these changes.
- **Maintain Clear Descriptions**: This aids data consumers in understanding the purpose and context of each exposure.
- **Monitor Maturity Levels**: As a dashboard or report moves from development to production, update its maturity level in the exposure.

## Conclusion

dbt exposures offer a structured way to understand and document the downstream usage of your dbt models. By effectively using exposures, data teams can ensure transparency, traceability, and better communication with business stakeholders.

## Additional Resources

- [dbt documentation on exposures](https://docs.getdbt.com/docs/build/exposures)

dbt Exposures

# dbt Full Refresh: Usage and Examples

[dbt](https://github.com/dbt-labs/dbt-core) has revolutionized the way we handle data transformations in modern data stacks. It allows data analysts and engineers to transform raw data in the warehouse into meaningful datasets. One crucial aspect of dbt is the **full refresh** capability. This tutorial delves deep into this feature, helping you understand and implement it effectively.

## Why dbt Full Refresh?

Imagine a scenario where your company has a model containing all user actions that gets materialized into a table. There are millions of actions in that table, so you set it up to load data incrementally. That means each day, only the actions from the past day get added to the table.

After a few weeks you notice there are some edge cases that cause your source data to drift and become slightly different from the dbt user actions table due to some important manual updates being made to the source table. To ensure your model reflects a perfect copy of the source data, you'd need a mechanism to refresh it entirely. This is where dbt's full refresh comes into play.

## Configuring Full Refresh in dbt

To initiate a full refresh in dbt, you can use the **`--full-refresh`** flag during your dbt run or build. 

```bash
dbt run --full-refresh --models user_actions

```

However, if you want certain models always or never to undergo a full refresh, you can set the **`full_refresh`** configuration in your model's configuration (this can either be set at the top of your model file or in the projects yaml file.

```yaml
{{ config(full_refresh = true) }}

```

## dbt Incremental Models vs. Full Refresh

While full refreshes are powerful, they might not always be the most efficient choice, especially for large datasets. This is where incremental models come in. They allow dbt to process only the new or updated rows, saving time and computational resources.

In some cases, like the example from the introduction about millions of user actions, you might want to trigger incremental refreshes daily, then a full refresh once a week or once a month to ensure all historical data is accurate. 

Another scenario where you might want a full refresh is if the logic of your incremental model changes.

## Trigger Full Refresh on Schema Changes

The **`on_schema_change`** configuration in dbt offers granular control when columns in your incremental model change. For instance, if you add a new column, you can decide whether to:

- Ignore it (**`ignore`**).
- Fail the run (**`fail`**).
- Append the new column (**`append_new_columns`**).
- Sync all columns, including adding new ones and removing missing ones (**`sync_all_columns`**).

Example:

```yaml
{{ config(
  materialized='incremental', 
  on_schema_change='append_new_columns'
) }}

```

## Working with Full Refresh Models

**Scenario**: Your e-commerce company wants to maintain a model of customer profiles. Over time, customers update their profiles, and you want to ensure the model is always up-to-date.

**Step 1**: Set up your dbt environment and create a new model named **`customer_profiles`**.

**Step 2**: Define your model using the **`table`** materialization type:

```sql
--customer_profiles.sql

{{ config(materialized='table') }}

select
    customer_id,
    first_name,
    last_name,
    email,
    last_updated_date

from {{ source('shopify','customers') }}

```

**Step 3**: Run your model:

```sql
dbt run --models customer_profiles
```

**Step 4**: To simulate profile updates, modify some records in your **`shopify.customers`** source.

**Step 5**: Run a full refresh to update the **`customer_profiles`** model:

```sql
dbt run --full-refresh --models customer_profiles
```

**Step 6**: Validate that the **`customer_profiles`** model has the updated records.

## Best Practices

- **Large Datasets**: For models with especially large datasets, consider setting **`full_refresh: false`** to prevent dbt from dropping and recreating them unnecessarily.
- **Schema Changes**: If your source data schema changes frequently, consider using the **`on_schema_change`** configuration to handle these changes gracefully.
- **Optimization**: Regularly monitor the performance of your dbt runs and adjust configurations as needed.

## Conclusion

The ability to perform a full refresh is a powerful feature in dbt, ensuring data accuracy and consistency. By understanding when and how to use it, you can optimize your data transformation processes and maintain high-quality datasets.

## Additional Resources

- [Official dbt documentation](https://docs.getdbt.com/reference/resource-configs/full_refresh)

dbt Full Refresh

# dbt init Command: Creating a New dbt Project

## **Introduction**

[dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the key commands in dbt is **`dbt init`**, which helps you create a new dbt project. This tutorial will guide you through the process of installing dbt, creating a new project, setting up a connection profile, and more.

## **Installation**

Before we start, make sure you have Python and pip installed on your machine. If not, you can download Python from the official website and pip is included in the package.

To install dbt and dbt-init, open your terminal and type:

```bash
pip install dbt dbt-init
```

## **Creating a New Project**

To create a new dbt project, you can use the **`dbt init`** command. For example, if you want to create a new project for a client named “acme_corp” using Snowflake as the warehouse, and you want the project to be located in the **`~/clients/`** directory, you would run:

```bash
dbt-init --client acme_corp --warehouse snowflake --target-dir ~/clients/

```

This command will create a new project in the **`~/clients/`** directory.

## **Setting Up the Connection Profile**

After creating a new project, you need to set up a connection profile. This is done in the **`profiles.yml`** file, which is located in the **`~/.dbt/`** directory.

Open the **`sample.profile.yml`** file in your project directory and update it with the correct details for your client. Replace the placeholders below with your actual details:

```yaml
acme_corp:
  target: dev  
  outputs:    
    dev:      
    type: snowflake      
    account: <account>      
    user: <user>
    role: <role>
    database: <database>      
    warehouse: <warehouse>      
    schema: <schema>      
    threads: <threads>      
    private_key: <private_key>

```

## **Understanding the Project Structure**

After running the **`dbt init`** command, you’ll notice several files and directories in your project directory. The most important one is **`dbt_project.yml`**, which is the main configuration file for your dbt project.

## **Building Out the Starter Project**

In your starter project, there are several variables you can use to customize your project. For example, **`{{ project.name }}`** refers to the name of your project, **`{{ project.warehouse }}`** refers to the warehouse that your client is using, and so on. You can use these variables in your **`dbt_project.yml`** file to customize your project.

## **Testing the Changes**

To test the changes you’ve made to your project, you can run the **`dbt run`** command. This command will execute all models in your project. Here’s an example:

```bash
dbt run
```

## **Conclusion**

Congratulations! You’ve just created a new dbt project, set up a connection profile, and learned how to customize your project. As next steps, you can start creating models in your project and transforming your data.

dbt init

# dbt ls (list) Command: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a transformative tool in the world of analytics, allowing data teams to transform, test, and document their data workflows. One of its powerful commands is **`dbt ls`**, which provides a way to list resources in your dbt project. This tutorial will guide you through its usage, key arguments, and practical examples.

## dbt list (ls) Basic Usage

The **`dbt ls`** command, also known as **`dbt list`**, is designed to list resources in your dbt project. Unlike some other commands, it doesn't connect to the database or run any queries. It only reads your your connection profile to resolve target-specific logic.

```bash
dbt ls

```

## Key Arguments

### Resource Type (`--resource-type`)

This argument allows you to specify the type of resource you want to list, such as models, sources, seeds, or snapshots.

```bash
dbt ls --resource-type model

```

### Selection (`--select` and `--models`)

Use these arguments to filter the nodes or models you want to list. The shorthand for `--select` is `-s` and the shorthand for `--models` is `-m`.

For instance, if you have a sales model and want to list only those, you'd use:

```bash
dbt ls --select intermediate.*

```

### Exclusion (`--exclude`)

If you want to list all models except for a specific model or group of models, you can use the **`--exclude`** argument. The command below will return all models except for the ones that have the `v2` tag.

```bash
dbt ls --resource-type model --exclude tag:v2

```

### Named Selectors (`--selector`)

For complex projects, you might have predefined selectors in a **`selectors.yml`** file. Use this argument to list resources based on those selectors.

```bash
dbt ls --selector my_selector

```

## Advanced Output Options

### Output Format (`--output`)

By default, the output is in a readable format. However, for integration with other tools, you might need a JSON output:

```bash
dbt ls --output json

```

### Output Keys (`--output-keys`)

When using JSON output, you can specify which keys you want to include:

```bash
dbt ls --output json --output-keys name resource_type

```

## Practical dbt ls Examples

### Listing Specific Models for a Retail Business

Suppose you have models related to inventory and want to list all of them:

```bash
dbt ls --select inventory.*

```

### Listing Tests for Financial Reporting

For businesses in the finance sector, ensuring data integrity is crucial. List all tests related to financial data by filtering on the tag `financial` that we’ve applied on all tests in that category:

```bash
dbt ls --select tag:financial --resource-type test

```

### Listing Incremental Models in E-commerce

In e-commerce, data like user activity might be stored incrementally. To list such models:

```bash
dbt ls --select config.materialized:incremental

```

### Output for Integration with Business Dashboards

If you're integrating with a business dashboard tool and need a JSON output:

```bash
$ dbt ls --select sales.* --output json

```

## Common Pitfalls and Tips

- Always ensure that the models you're trying to list are not disabled.
- Familiarize yourself with the connection profile and target-specific logic to avoid unexpected results.

## Conclusion

The **`dbt ls`** command is a versatile tool in the dbt suite, allowing for efficient resource listing and management. By mastering its arguments and understanding its use cases, you can streamline your data workflow and improve your project's organization.


dbt ls

# dbt run Command: Usage & Examples

## Introduction

Welcome to this comprehensive tutorial on the **`dbt run`** command. [dbt](https://github.com/dbt-labs/dbt-core) is a transformative tool for data professionals who need to manage and transform data in SQL. The **`dbt run`** command is a core function of dbt, enabling the execution of compiled SQL model files against a specified target database. This is particularly beneficial in business scenarios where raw data needs to be transformed into a more digestible format for analysis and decision-making.

## Understanding the dbt run Command

The **`dbt run`** command is designed to execute your compiled SQL model files against the current target database. It connects to your database and executes the necessary SQL to materialize all data models using the strategies you’ve outlined.

Let’s consider a business scenario where you have a raw sales data table and you want to create a model that aggregates this data by product category. We'll assume you already created a staging model for that table, so we can reference the staging model here. Your SQL model file might look like this:

```sql
-- sales_by_category.sql

{{
  config(
    materialized='table'
  )
}}

select 
  product_category,
  sum(sales_amount) as total_sales 

from {{ref('stg_sales_data')}}

group by product_category

```

To run this model, you would use the **`dbt run`** command in your terminal:

```bash
dbt run --model sales_by_category
```

The command will execute the SQL in the model file against your target database, creating a new table with the aggregated sales data.

## dbt run Model Deployment

When deploying new models, **`dbt run`** handles the process smoothly. It first builds each model with a temporary name, then drops the existing model, and finally renames the new model to its correct name. This process is handled within a single database transaction for database adapters that support transactions, ensuring data integrity.

For instance, if you update the **`sales_by_category.sql`** model to also include the average sales amount per category, **`dbt run`** will create a new version of the **`sales_by_category`** table with the additional data, and then replace the old table with the new one.

## Using the full-refresh Flag

The **`--full-refresh`** flag is a powerful feature of **`dbt run`**. When used, dbt treats [incremental models](https://docs.getdbt.com/docs/build/incremental-models) as table models. This is especially useful when the schema of an incremental model changes and you need to recreate it, or when you want to reprocess the entirety of the incremental model due to new logic in the model code.

For example, if we had a daily version of our **`sales_by_category`** model from earlier and it only added new rows each day, it'd look like this:

```sql
--daily_sales_by_category.sql

select 
  sales_date,
  product_category, 
  sum(sales_amount) as total_sales, 
  avg(sales_amount) as average_sales, 
  count(*) as number_of_transactions

from {{ref('stg_sales_data')}} 

{% if is_incremental() %}

  where sales_date > (select max(sales_date) from {{ this }})

{% endif %}

group by sales_date, product_category

```

In this case if you need to run the model with all your raw data, and not just the days not yet added to the model, you can use the **`--full-refresh`** flag to recreate the table with the new data.

```bash
dbt run --model sales_by_category --full-refresh

```

This will ignore the incremental logic in the `where` statement of the model and run for all days in the source data set.

## Selecting Specific Models to Materialize

**`dbt run`** also allows you to select which specific models you’d like to materialize. This can be useful in scenarios where you may prefer running a different set of models at various intervals, or when you want to limit the tables materialized while you develop and test new models.

For example, if you have a large number of models but you’re currently only working on a few of them, you can use the **`m`** option with **`dbt run`** to only run those models. This can save time and resources during the development and testing process.

```bash
dbt run -m sales_by_category,daily_sales_by_category

```

## Conclusion

We’ve covered a lot in this tutorial, from understanding the **`dbt run`** command and its intelligent multi-threading, to deploying `dbt run` models and using the **`--full-refresh`** flag. Remember, the best way to get comfortable with these concepts is to practice. So, go ahead and start using the **`dbt run`** command in your data transformation projects.

dbt run command

# dbt run-operation Command: Usage & Examples

## Introduction

### What is dbt?

[dbt](https://github.com/dbt-labs/dbt-core) is a transformative software in analytics and engineering. It bridges the gap between raw data in your warehouse and actionable insights by allowing for sophisticated transformations and modeling.

### The Power of `run-operation`

Among dbt's arsenal of commands, **`run-operation`** stands out. It's not just about invoking macros; it's about automating repetitive tasks, ensuring data integrity, and customizing operations to suit specific business needs.

## `run-operation` Meaning and Usage

At its core, the command is a bridge to your macros, enabling you to execute them with specific arguments.

```bash
$ dbt run-operation {macro} --args '{args}'
```

**Deep Dive**:

- **`{macro}`**: This is your macro's name, which should be descriptive of its function.
- **`-args`**: These are the specific details or parameters for your action. A YAML formatted string. YAML, or "YAML Ain't Markup Language", is a human-readable data serialization format. It's crucial to get the formatting right to avoid errors.

## Practical Examples

The examples below assume that you’ve already created the mentioned macros, and you’re using `run-operation` to execute them either manually from a command line, or through a scheduled action. 

### Example 1: Granting Select Permissions

**Business Scenario**: Imagine a growing company with new hires in the data department. Instead of manually granting permissions, automate the process using this macro.

**Command**: 

```bash
$ dbt run-operation grant_select --args '{role: reporter}'

```

### Example 2: Cleaning Stale Models

**Business Scenario**: As data accumulates, old models can clutter your warehouse. Regularly cleaning them optimizes performance and storage costs.

**Command**: 

```bash
$ dbt run-operation clean_stale_models --args '{days: 7, dry_run: True}'

```

### Example 3: Updating Sales Metrics

**Business Scenario**: Monthly sales reviews are common in businesses. Automate the data gathering process to ensure timely and accurate reports.

**Command**: 

```bash
$ dbt run-operation update_sales_metrics --args '{month: "July", year: 2023}'

```

### Example 4: Archiving Old Customer Data

**Business Scenario**: Compliance, like GDPR, mandates certain data handling practices. Use this operation to ensure you're not retaining customer data longer than necessary.

**Command**: 

```bash 
$ dbt run-operation archive_old_customers --args '{inactive_days: 365}'

```

### Example 5: Generating Monthly Financial Reports

**Business Scenario**: Financial reviews are pivotal for business strategy. Generate tailored financial reports for specific departments to aid in decision-making.

**Command**: 

```
$ dbt run-operation generate_financial_report --args '{month: "August", year: 2023, department: "Sales"}'

```

## Tips and Best Practices

- **Validation**: Always validate your macros and arguments before executing to ensure data integrity.
- **Documentation**: A well-documented macro is easier to use and reduces the risk of errors.
- **Regular Updates**: As your business evolves, so will your data needs. Regularly review and update your macros to stay relevant.

## Troubleshooting Common Issues

- **YAML Formatting**: Common errors include missing spaces or incorrect indentation. Use online YAML validators for assistance.
- **Macro Errors**: Ensure your macro is correctly defined in your dbt project.
- **Argument Issues**: Missing or incorrectly formatted arguments can cause failures. Always refer to your macro's documentation.

## Conclusion

The **`dbt run-operation`** command, when mastered, can be a game-changer. It streamlines processes, ensures data accuracy, and allows for a high degree of customization. As with any tool, the key lies in understanding its potential and applying it effectively.

## Additional Resources

- [dbt macros blog post]

dbt run-operation

# dbt seed Command: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the powerful features of dbt is the **`seed`** command, which allows you to load CSV files (referred to as “seeds”) into your data warehouse. This tutorial will guide you through the process of using the **`dbt seed`** command.

## Understanding dbt Seeds

In dbt, seeds are CSV files that you can load into your data warehouse. They are particularly useful for handling static data that changes infrequently. For instance, you might have a CSV file containing a list of country codes and their corresponding country names. This data can be loaded into your warehouse as a seed and referenced in your dbt models.

## dbt seed Use Cases

### Mapping Codes to Descriptive Names

As we’ve seen in the previous example, you can use a seed to map product codes to product names. This can be extended to any scenario where you have codes that need to be mapped to more descriptive names. For example, you might have a seed that maps error codes to error descriptions, or abbreviations to their full forms.

### Excluding Certain Data from Analysis

Suppose you have a list of test email addresses or user IDs that you want to exclude from your analysis. You can create a seed with these email addresses or user IDs, and then use the **`dbt seed`** command to load this data into your warehouse. In your dbt models, you can then exclude these test email addresses or user IDs from your analysis.

### Loading Small Reference Datasets

If you have small reference datasets that don’t change often, it can be more efficient to load them into your warehouse as seeds rather than storing them as tables in your source databases. For example, you might have a small dataset of exchange rates that you want to use in your dbt models.

### Data Validation

You can use seeds to validate the data in your warehouse. For example, you might have a seed that contains the expected results of a certain calculation. You can then create a dbt test that compares the actual results in your warehouse to the expected results in your seed.

### Machine Learning Model Testing

If you’re a data scientist or machine learning engineer, you can use seeds to load test data into your warehouse. You can then use this test data to evaluate the performance of your machine learning models.

## Creating Your First dbt Seed

Let’s create a CSV file to use as a seed. For this tutorial, we’ll use a simple CSV file containing product codes and their corresponding product names. Save the following data in a file named `**product_codes.csv**`:

```
product_code,product_name
PRD01,Apple iPhone 13
PRD02,Samsung Galaxy S21
PRD03,Google Pixel 6

```

Place this file in the **`seeds`** directory of your dbt project.

## Loading dbt Seeds into Your Data Warehouse

To load the seed into your data warehouse, run the following command in your terminal:

```bash
$ dbt seed

```

This command will load all CSV files in the **`seeds`** directory into your data warehouse. If you want to load a specific seed, you can use the **`--select`** flag followed by the name of the seed (without the **`.csv`** extension). For example:

```bash
$ dbt seed --select product_codes

```

## Referencing dbt Seeds in Your Models

Once your seed is loaded into your data warehouse, you can reference it in your dbt models using the **`ref`** function. For example, if you have a model that needs to join with the `**product_codes**` seed, you could do something like this:

```sql
select    
  orders.*,    
  product_codes.product_name

from {{ ref('orders') }} as orders

  left join {{ ref('product_codes') }} as product_codes
    on orders.product_code = product_codes.product_code

```

## Configuring and Testing dbt Seeds

You can configure your seeds in the **`dbt_project.yml`** file. For example, you can specify the schema where the seed data should be loaded. You can also document and test your seeds by declaring properties in YAML. For more information, check out the **[dbt documentation](https://docs.getdbt.com/docs/building-a-dbt-project/seeds)**.

## Conclusion

The **`dbt seed`** command is a powerful tool for loading static data into your data warehouse. By using seeds, you can keep your transformation logic within dbt, ensuring that your data transformations are consistent, version controlled, and code reviewable.

dbt seed

# dbt snapshot Command: Strategies & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a powerful data transformation tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the key features of dbt is the ability to create snapshots, which are a way of tracking changes in your data over time. This tutorial will guide you through the process of creating and using dbt snapshots.

## Understanding Slowly Changing Dimensions

Slowly Changing Dimensions (SCDs) are a concept in data warehousing that refers to the ways in which data changes over time. They are called “slowly changing” because the changes, such as a customer’s address or a product’s price, usually occur infrequently, but the impact of these changes can be significant for data analysis.

SCDs are typically categorized into three types:

- **Type 1**: This approach overwrites old data with new data, and therefore, no history is preserved.
- **Type 2**: This approach adds a new record with the new data and preserves the old record as historical data. This is the most common type of SCD and the one that dbt snapshots implement.
- **Type 3**: This approach adds a new column for the new data and preserves the old data in the original column. This type is less common and is used when it’s important to see the progression of changes.

dbt snapshots are directly connected to the concept of SCDs, particularly Type 2 SCDs. A snapshot in dbt is a way of tracking changes in your data over time, effectively creating a version history of each row of data. When you create a snapshot, dbt adds metadata columns to your data, including **`dbt_valid_from`** and **`dbt_valid_to`**, which indicate the time range during which a particular version of a record was valid.

This mechanism allows dbt snapshots to implement Type 2 SCDs. When a change occurs in the source data, instead of overwriting the existing record (Type 1) or adding a new column (Type 3), dbt adds a new record with the new data (Type 2). The **`dbt_valid_from`** and **`dbt_valid_to`** columns in the snapshot table indicate when each version of the record was valid, allowing you to track the full history of changes over time.

## Creating a dbt Snapshot Model

A snapshot model in dbt is a special kind of model that tracks changes in your data over time. To create a snapshot model, you need to create a new file in the **`snapshots`** directory of your dbt project. This file should contain a **`snapshot`** block, which defines the configuration for your snapshot.

Here’s an example of a snapshot model that tracks changes in a **`users`** table:

```sql
{% snapshot users_snapshot %}  

{{
  config(      
    target_schema='snapshots',      
    strategy='timestamp',      
    unique_key='id',      
    updated_at='updated_at'    
  )  
}}  

  select * 
  from raw.users

{% endsnapshot %}

```

In this example, the **`strategy`** is set to **`'timestamp'`**, which means dbt will track changes based on the **`updated_at`** column. The **`unique_key`** is set to **`'id'`**, which is the unique identifier for each row in the **`users`** table.

## Understanding dbt Snapshot Strategies

In dbt, there are two main strategies for creating snapshots: the timestamp strategy and the check strategy.

### Timestamp strategy

The **timestamp strategy** is used when your source data includes a timestamp column that gets updated whenever a record changes. In this strategy, dbt will create a new snapshot record whenever the timestamp column is more recent than the last time the snapshot was run. This strategy is useful when you want to track changes based on when they occurred.

You can see an example of a timestamp strategy in the previous paragraph.

### Check strategy

The **check strategy**, on the other hand, is used when you want to track changes based on the values of specific columns. In this strategy, dbt will create a new snapshot record whenever the values in the specified columns are different from the last time the snapshot was run. This strategy is useful when you want to track changes based on what changed, rather than when it changed.

Here’s an example of a snapshot configuration using the check strategy:

```sql
{% snapshot users_snapshot %}  

{{    
  config(      
    target_schema='snapshots',      
    strategy='check',      
    unique_key='id',      
    check_cols=['status']    
  )  
}}  

select * 
from raw.users

{% endsnapshot %}

```

In this example, dbt will create a new snapshot record whenever the **`status`** column changes for a user.

Choosing the right strategy for your snapshots depends on the nature of your source data and the specific requirements of your use case.

## Running Your Snapshot Models

Once you’ve created your snapshot model, you can run it using the **`dbt snapshot`** command. This command will execute all snapshot models in your dbt project and create a new snapshot table in your data warehouse.

Here’s how you can run your snapshot models:

```bash
dbt snapshot

```

After running this command, you should see a new table in your data warehouse under the **`snapshots`** schema (or whatever schema you specified in your snapshot configuration).

## Working with dbt Snapshot Data

The snapshot table created by dbt contains a full history of your data, with each row representing a different version of a record. You can query this table to see how your data has changed over time.

Here’s an example of a query that shows all changes to the **`status`** column of the **`users`** table:

```sql

select 
  id, 
  status, 
  dbt_valid_from, 
  dbt_valid_to

from snapshots.users_snapshot
order by id, dbt_valid_from;

```

This query will return a result set that shows when each **`status`** value was valid for each user.

## Advanced Snapshot Techniques

In addition to the basic snapshot functionality, dbt also provides several advanced features that can help you manage your snapshots more effectively. For example, you can use the **`invalidate_hard_deletes`** configuration option to track records that have been deleted from your source data.

Here’s how you can modify the previous snapshot model to track hard deletes:

```sql
{% snapshot users_snapshot %}  

{{    
  config(      
    target_schema='snapshots',      
    strategy='timestamp',      
    unique_key='id',      
    updated_at='updated_at',      
    invalidate_hard_deletes=True    
  )  
}}  

select * 
from raw.users

{% endsnapshot %}

```

With this configuration, dbt will create a new row in the snapshot table for each record that is deleted from the **`users`** table, with the **`dbt_valid_to`** column set to the timestamp of the deletion.

## Best Practices and Guidelines

When working with dbt snapshots, there are a few best practices you should follow:

- Keep your snapshots simple: Avoid adding complex logic or joins to your snapshot models. You can always apply this logic downstream after the snapshot is created.
- Use a separate schema for snapshots: This makes it easier to distinguish between snapshot tables and other types of tables in your data warehouse.
- Ensure your unique key is unique: The unique key you specify in your snapshot configuration should uniquely identify each record in your source data.

## Conclusion

dbt snapshots are a powerful tool for tracking changes in your data over time. By following this tutorial, you should now have a good understanding of how to create and use dbt snapshots in your own projects.

dbt snapshot

dbt Snowflake

# dbt sources & dbt source freshness Commands: Usage & Examples

## Introduction

Data transformation is the backbone of modern analytics, and [dbt](https://github.com/dbt-labs/dbt-core) has emerged as a pivotal player in this space. The **`dbt source`** command, a cornerstone of dbt, allows users to manage and reference raw data tables seamlessly. This tutorial delves into the intricacies of this command, ensuring you can harness its full potential.

## dbt sources Overview

dbt sources represent raw data tables in your data warehouse, serving as the foundation for dbt transformations. Instead of directly referencing these tables, dbt sources provide an abstraction, enhancing maintainability and clarity. By declaring a table as a source, you're marking it as a trusted base for transformations, enabling functionalities like data freshness checks. In business contexts, sources could represent raw sales data, customer interactions, or inventory details.

## Understanding dbt sources

dbt sources are designed to manage source data within dbt. Its primary subcommand, **`dbt source freshness`**, allows you to query the "freshness" of your source tables, ensuring data is up-to-date and reliable.

## Working with Source Data

In analytics, source data is the raw, unprocessed information directly from data stores. For instance, if you're an e-commerce business, your source data might be raw sales transactions, a raw list of customers, and raw historical inventory data. With dbt sources, you can create references to these tables, streamlining the transformation process.

## Using the `dbt source freshness` Command

### Add `loaded_at_field` to your sources yaml file

Before you can use `dbt source freshness` you need to define your sources and a `loaded_at_field` in your sources yaml file(s). In this example we two tables from our Stripe data: `subbscriptions` and `customers` that both have a field called `_synced_at` that tells us when each row was pulled from Stripe.  We’re also including a `freshness` warning if data is more than 2 days old and an error if it’s more than 7 days old. 

```sql
sources:
  - name: stripe
    database: analytics_db
    freshness:
      warn_after: {count: 2, period: day}
      error_after: {count: 7, period: day}
    loaded_at_field: _synced_at

    tables:
      - name: subscriptions
      - name: customers
```

### Use the dbt source freshness

Imagine you want to ensure that your sales transactions data is updated at least once a day. Using **`dbt source freshness`**, you can run:

```bash
dbt source freshness

```

If any source table hasn't been updated as per the specified freshness configuration, dbt will raise a warning or error. In our example above, if the data is 3 days old, we’ll get a warning, since the `warn_after` threshold was set to 2 days. 

## Configuring Source Freshness Output

By default, the freshness information is saved to **`target/sources.json`**. To customize this:

```bash
dbt source freshness --output target/my_custom_path.json

```

## Advanced Usage of dbt Source

### Specifying Sources to Snapshot

If you only want to check the freshness of specific tables, use selectors like `--select`, `--exclude` , etc. This command will check source freshness for only the Stripe Customers source. 

```bash
dbt source freshness --select source:stripe.customers

```

### Understanding Data Lineage

For a business, understanding where data comes from is crucial. Using the **`{{ source() }}`** function, you can define the lineage. For instance, to reference our Stripe customers source in a model:

```sql
select 
  customer_id, 
  customer_name, 
  created_at

from {{ source('stripe', 'customers') }}

```

Most of the time you’ll select fields you need out of each source in a staging model, so each source has its own staging model file. That way you can choose which fields you need from the table and handle renaming and recasting data in one place.

### Testing Your Sources

To ensure data quality, you can write tests for your sources. For instance, to ensure that each Stripe customer has a unique ID:

```yaml
version: 2

sources:
  - name: stripe
    tables:
      - name: customers
        tests:
          - unique:
              column_name: customer_id
```

## Benefits of Using dbt Sources

For businesses, dbt sources offer:

- **Data Consistency**: Ensure that your analytics are based on reliable, consistent data.
- **Efficiency**: Avoid rewriting transformation logic by referencing standardized data.
- **Freshness Monitoring**: Ensure that your business decisions are based on the latest data.

## Common Pitfalls and Best Practices

- **Stay Updated**: Source data can change. Regularly run **`dbt source freshness`** to avoid disruptions.
- **Regular Checks**: Schedule the freshness command to run at regular intervals, ensuring data quality.

## Conclusion

dbt sources are a powerful tool in the dbt arsenal, especially for businesses that rely on timely and accurate data. By mastering the `dbt source freshness` command and setting up sources correctly, you ensure a robust foundation for your analytics.

dbt sources

# `dbt_utils` for Surrogate Key Generation

## Introduction

Welcome to this tutorial on surrogate key generation using dbt's utility package. [dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool for transforming data in the data warehouse. One of its many utilities is the generation of surrogate keys, which are essential for data modeling and analytics.

## Understanding Primary and Surrogate Keys

- **Primary Keys**: These are unique identifiers for records in a table. They ensure no duplicate rows, establish relationships with other tables, and help identify the grain of the table.
- **Surrogate Keys**: When your data doesn't come with a unique primary key, surrogate keys come to the rescue. They are primary keys derived in the analytics layer, ensuring each record has a unique identifier.

## The Basics of `dbt_utils.generate_surrogate_key`

**`dbt_utils`** is a [package for dbt](https://github.com/dbt-labs/dbt-utils) that offers a collection of macros and materializations to simplify common tasks. One such macro is **`generate_surrogate_key`**, which aids in creating surrogate keys.

## Generating Surrogate Keys: A Step-by-Step Guide

- **When to Use**: Whenever your table lacks a unique primary key or when combining data from multiple sources.
- **How to Use**: This macro concatenates the fields and applies a [cryptographic hash](https://en.wikipedia.org/wiki/Cryptographic_hash_function) (using MD5 by default) to produce a unique ID.

```
{{ dbt_utils.generate_surrogate_key('field_a', 'field_b') }}

```

## Handling Null Values in Surrogate Keys

Null values can be tricky when generating surrogate keys. If any value is null, the entire concatenated string might return as null.

- **Solution**: Use the **`coalesce`** function to manage null values. This function replaces null values with a default value, ensuring the concatenated string remains unique.
- **Separator Importance**: To ensure uniqueness, add separators between fields, especially when handling null values.

```
select
  {{ dbt_utils.generate_surrogate_key(
    coalesce(activity_date,'NO_ACTIVITY'), 
    '---', 
    user_id) 
  }} as primary_key, 

  activity_date,
  user_id,
  total_actions

from {{ ref('int__user_daily_activity') }}

```

## Customizing Hashing: Switching from MD5 to SHA

While MD5 is the default hashing function, you might prefer using SHA for its cryptographic advantages.

**How to Switch**: Override the default hashing macro in dbt by adding a macro called **`hash.sql`** to your dbt project's macros directory. The file should include this file:

```
{% macro default__hash(field) -%}
  sha(cast({{ field }} as {{ api.Column.translate_type('string') }}))
{%- endmacro %}

```

## Best Practices for Surrogate Key Generation

- **Consistency**: Ensure you use the same method for surrogate key generation across your project.
- **Data Integrity**: Regularly test your surrogate keys in dbt to ensure data completeness and uniqueness.
- **Avoid Pitfalls**: Be wary of null values and ensure you're using separators to maintain uniqueness.

## Conclusion

Surrogate keys are invaluable in data modeling, especially when dealing with data from diverse sources. With **`dbt_utils`**, the process becomes streamlined, allowing you to focus on analytics rather than data preparation.

## **Additional Resources**

[Official `dbt_utils` Documentation](https://docs.getdbt.com/docs/build/packages)