# dbt-accounting

[dbt](https://dbt.readme.io/docs/overview) models for revenue analysis used in the MoneyHub app.

## Requirements

* Python 3.11
* [Docker](https://www.docker.com/)

## Installation

```shell
pip install --upgrade pip # this upgrades pip
```

Create an environment and install dependencies:

```shell
make env
```

Copy `.env.shadow` to `.env` and fill in your credentials and configs:

```shell
cp .env.shadow .env
```

Make sure to use a custom schema in `DEV_ENGINEERING`. You can create one by running the following
command in Snowflake:

```sql
CREATE SCHEMA DEV_ENGINEERING.<MY_CUSTOM_DBT_SCHEMA>;
```

If everything is set up correctly, you should be able to run the tests using the command:

```shell
make test
```

To see the available commands use the `help` make target:

```shell
make help
```

## SSH Authentication

To use this project you must setup SSH authentication. The following is a
shortened version of the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/key-pair-auth).

You will need to setup an SSH key on your machine:

```shell
mkdir ~/.ssh/snowflake && cd ~/.ssh/snowflake # recommended
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```

Use the [utility script in swf-monitoring](https://github.com/theorchard/swf-monitoring/blob/master/swf_monitoring/utils/bin/generate_snowflake_private_key_string.py)
to combine the private key and passphrase into a base64-encoded private key.

```shell
python3.9 -m venv env
source env/bin/activate
pip install cryptography
python swf_monitoring/utils/bin/generate_snowflake_private_key_string.py <YOUR PASSPHRASE> ~/.ssh/snowflake/rsa_key.p8
```

Put the key from the resulting `generate_snowflake_private_key_string.txt` file
(without the `' '`) into the following variable in your `.env` file:

```
SNOWFLAKE_PRIVATE_KEY=<YOUR KEY>
```

You will need to add your public key to your user in the [Snowflake terraform config](https://github.com/theorchard/terraform-infra/blob/master/prod/snowflake/orchard/users/users.auto.tfvars).


# Testing

Unit testing is a fundamental part of the development process, as they allow you
to test the models on a much smaller scale (with a couple of handpicked rows) in the source
tables, instead of millions. This is achieved by first creating _seeds_ of the source tables 
using a csv file. Tests will then be run against the source table using the seeded table. 
How to set up tests will be discussed further below. 

To run all of our tests **together**, use

```sh
$ make test
```
N.B
> When running `make test` dbt will take the fixtures (csv files) found in the 
>`tests/fixtures` folder, copy them to the `seeds/` folder, then create and populate new 
> `EXPECTED_*` tables. This process is called seeding. 

<br/>

Alternatively, if you would like to run one test at a time, you can copy the generated .csv files 
found in the `tests/fixtures` directory into the `/seeds` directory then run: 

```sh
dbt seed -s <name_of_the_csv_file>
````

Next, run the test:
```sh
dbt test -s <test_name_of_test>
```

## Writing a Test

The basic idea behind defining a test scenario is that for each model we:

1. Create an expected table outcome, called a **seed** (using the seed generator or manually).
2. Test against that expected table outcome, using a _filtered_ and _limited_ version of 
   the source tables. You should use the same parameters used for creating the .csv file.


### Creating Seeds

The expected table is seeded by first creating a `.csv` file inside the `tests/fixtures/` folder. 
Next, copy it to the `seeds/` folder then run the `seed` and `test` commands as mentioned above. 
The 
`.csv` file can be created using the `seed_generator.py` script found in the `scripts/` folder. 

You gan generate a fixture from any existing table using [seed_generator.py](scripts/seed_generator.py) script.
This script uses the connection variables you have defined in your `.env` file. That way you can run
your model and create a table from that model. Consequently, you need to ensure that your SSH keys 
have been set up already first before using the generator.

This script accepts the following flags:

```shell
-t   the name of the table
-l   the number of records to limit the query by. The default is 10.
-w   to filter results using the WHERE clause in SQL.
-o   to order the results returned in the SQL query using the ORDER BY clause.
-f   to generate a file with a given file name. For e.g. 'expected_<your_file_name>.csv' instead of 
'expected_database.schema.table.csv'
```

Run it with:
```shell
python scripts/seed_generator.py -t database.schema.table -w "optional_where_clause='value'"
python scripts/seed_generator.py -h  # for help
```

### Creating a Test

There are 2 types of DBT test: data test and unit tests. When creating DBT unit tests, we check for 
differences between the results of your model and the seeded file using the `MINUS` function 
in SQL. If the difference is zero rows, the test is graded as `SUCCESS`. If the results do not 
match up, you will get an error message like `Got 4 results, expected 0.` More on how to troubleshoot can be found 
in the next section. 


# Troubleshooting
When writing DBT test for the first time, it may be difficult to decipher error messages printed to 
to the console. Here are common scenarios.

On some occasions, your tests may fail even though you are using the same data. This can be due 
to nuances in the data such as `null` values. This often appears as a _red herring_ that prints to 
the console `Numeric value 'Usher' is not recognised`. DBT is not very clear about which column 
is causing this error. In these scenarios, you can debug this by selecting columns one by one 
until you find the offending column.

In addition, there are some scenarios where dbt behaves unexpectedly. For example, tests failing 
that really should not fail - especially after switching between branches. This is usually caused 
by having previously compiled SQL code in the `target/` folder that can interfere with the 
current run. This can be fixed by deleting the target folder before running tests locally. 
Alternatively, you can clean your entire environment.

```sh
make clean
make env
```

Another cause of error can be an outdated dbt version. To make sure you have the latest versions 
installed to run the models, run

```sh
pip install -r requirements.txt
dbt deps
```