# ROYALTY_ACCOUNTING DB

The `ROYALTY_ACCOUNTING` Snowflake database is used by the ABACUS team during and after various processes related to the Accounting Run:tm:.

## Creating a New File

Create a new file according to [Formatting and Best Practices](#formatting-and-best-practices).

For example:
- `changelog/ddl/JIRA-TICKET-db-change-you-want-to-run.sql`

## ABACUS Snowflake Stages

The ABACUS team uses snowflake stages to move data between S3 (particularly s3://<SCHEMA_NAME>-royalties-sales-files) and snowflake. At the time of this writing there are two stages per `ROYALTY_ACCOUNTING` schema, each handling a different file type:
- `ABACUS_PARQUET_FILE`
- `ABACUS_TSV_FILE`

List of ABACUS snowflake stages can be found in the [ABACUS System Inventory](https://docs.google.com/spreadsheets/d/16x-qfzHrdJkwQ-Xfa16vhh8V1YTHFNgVNL80Gj_qLuM/edit#gid=1860798524).

### Creating a New Stage

The process for creating a new snowflake stage is as follows:
1. Create the stage with a DB PR.
2. When creating the stage, make sure to include AWS credentials (key and secret) and GRANT PRIVILEGES to the `ROYALTYACCOUNTING_DB_<SCHEMA_NAME>_SCHEMA_READ` and `ROYALTYACCOUNTING_DB_<SCHEMA_NAME>_SCHEMA_READWRITE` roles. ([Reference](/changelog/ddl/ACC-3278-create-stages-qa-2.sql))
3. Deploy the database PR.
4. Create a DEVOPS ticket asking them to create the actual AWS credentials (key/secret) with access to the particular S3 bucket(s) the stage needs. They will then update the newly created stage to use the new AWS credentials. ([Reference](https://jira.theorchard.com/browse/SYS-17277))
5. When DEVOPS says they have given the stage access to S3, test the access in snowflake using `LIST @STAGE` in the correct `role.warehouse.database.schema`. Any files in the bucket should be returned.


## The TEST Schema

It should be noted that `ROYALTY_ACCOUNTING` has a `TEST` schema that should mirror what is in QA. This schema is used during ABACUS integration tests.