# dbt-audience
dbt Core transformations for the Audience app


### Using this repository

#### Prerequisites
* UV
* Python 3.13

#### Install dependencies
From command line run "uv sync". This will resolve all
dependencies listed in pyproject.toml file.

#### Setup DBT
In order to run locally DBT models against database
you must run "dbt init" from command line.
Read more: https://docs.getdbt.com/reference/commands/init

From command line run "dbt deps" to install additional libraries for running data quality tests.

#### Validate connection
From command line run "dbt debug". You should be in a folder where dbt_project.yml file exists.

#### Makefile
You can run additional commands from makefile.

#### Identifying and deleting deprecated tables
When we rename or delete a dbt model in the codebase, the actual tables in snowflake are not updated or dropped
automatically. This is a common source of some nasty errors where the actual tables in snowflake are still being
queried from other parts of the system. They still contain entries which seems to make sense, but they're no longer
getting updated and become stale over time.

To identify these tables, run
```bash
make prepare_orphan_tables_delete
```
This will list all tables in snowflake PROD and QA schema that are not exist in dbt models with delete statements.

**Please note that you must use the latest version of the code to prevent unnecessary tables from being deleted.**

After you have made sure that the table data is really outdated and not used, create a PR in the database and delete it.

### Visualising DBT models
DBT provides browser based UI for viewing resource dependencies. You can also view model descriptions etc.

* First step is to create up to date catalog.
```bash
dbt docs generate
```

* Then you can serve catalog.
```bash
dbt docs serve # by default opens web page at http://localhost:8080/#!/overview
```
_Read more from here:_ https://docs.getdbt.com/reference/commands/cmd-docs


### Introduction to DBT commands
<b>Here are somme commands with explanations that help to run different
parts of this project.</b>

Usually it's good practice to update seeds table when continuing working on DBT repository:
```bash
dbt seed # this will update all models located in seeds folder
```
_Read more from here:_ https://docs.getdbt.com/docs/build/seeds
<br>
_In some cases the structure for seed table has changed, then it maybe be rquired to
drop the table directly from database and then rerunning seed command._



<b>3 main commmands for running models are:</b>
* DBT RUN - runs selected resources
* DBT TEST - runs tests for selected resources
* DBT BUILD - executes both run and test commands on selected resources


<b>Selecting or excluding resources(models, tests, tags, folders) for execution:</b>
_Tags are defined in the dbt_project.yml_
* DBT RUN --select "MODEL"
* DBT RUN --exclude "MODEL"

```bash
dbt run --select "EVENT_DBT"
dbt run --select models/artist/event_ingestion/dummy_data
dbt run --select models/core --exclude "FAN_AGE_DBT"

dbt test --select "EVENT_DBT"

dbt build --select "EVENT_DBT"
```
_Read more from here:_ https://docs.getdbt.com/reference/node-selection/syntax

<b>Specifying more complex selections. Use can use graph operators to run
either descendants or ancestors models:</b>
```bash
dbt run --select "EVENT_DBT+" # select model and all descendants
dbt run --select "+EVENT_DBT" # select model and all ancestors

```
_Read more from here:_ https://docs.getdbt.com/reference/node-selection/graph-operators <br>
https://docs.getdbt.com/reference/node-selection/set-operators#unions <br>
Yaml selector provides options to write complex select queries: https://docs.getdbt.com/reference/node-selection/yaml-selectors

<b>DBT retry command </b><br>
re-executes the last dbt command from the node point of failure. If the previously executed dbt command was successful, retry will finish as no operation.
```bash
dbt retry
```
_Read more from here:_ https://docs.getdbt.com/reference/commands/retry

<b>Troubleshooting with the ls command.</b><br>
Constructing and debugging your selection syntax can be challenging. To get a "preview" of what will be selected, we recommend using the list command.
```bash
dbt ls --select models/artist/event_ingestion/dummy_data
dbt ls --select "EVENT_DBT"
```
_Read more from here:_ https://docs.getdbt.com/reference/node-selection/syntax#troubleshoot-with-the-ls-command

<b>DBT full refresh.</b><br>
If you provide the --full-refresh flag to dbt run, dbt will treat incremental models as table models. This is useful when
* The schema of an incremental model changes and you need to recreate it.
* You want to reprocess the entirety of the incremental model because of new logic in the model code.
```bash
dbt run --full-refresh
```
_Read more from here:_ https://docs.getdbt.com/reference/commands/run#refresh-incremental-models

<b>Additional resources:</b><br>
The Ultimate Guide to DBT- https://count.co/canvas/JpkaYdqr9oN


### Backfilling segments
In order to back fill segments for some artists,
we have models in models/backfill folder.
Those can be called using this command:
```bash
make backfill_segments
```
This will run scripts/backfill_segments.py
You should also modify variables inside that Python file
to match artist, period and interval for backfilling.

Also, make sure you enable related models. This can be done
by setting enabled flag true in the dbt_project.yml file:
```bash
models:
  dbt_audience:
      backfill:
        +enabled: false
```
After back filling is done, disable models again.

Disclaimer:
* Back filling uses latest model version. It doesn't take into consideration
 previous model changes.
* It also doesn't account for fan data deletions related to privacy or
retention policies.
* Some of the segmentation related features might be calculated using
latest date not date used for back filling.
* Always make sure that back filled data more or less matches
the pattern from latest Fansifter dashboard.