# dbt Models for Building Analytics Data Welcome to dbt-analytics, a Python package for building database models using [dbt](https://dbt.readme.io/docs/overview)! ## What is dbt, and why do we use it? dbt (data build tool) is a third party application designed to make the process of **transforming** data simple (The 'T' in ETL). On the surface dbt is a compiler and a runner for SQL statements which can be extended using the templating language [Jinja](http://jinja.pocoo.org/). The idea is that we as developers define projects and models, and let dbt turn those into Snowflake tables. A model is always a `SELECT` statement, and will (mostly) be transformed into a view or a table (so called [materializations](https://docs.getdbt.com/docs/introduction#section-what-makes-dbt-so-powerful-) ). Models are grouped into projects. The basic setup of a project is defined in a [YAML](https://en.wikipedia.org/wiki/YAML) file called `dbt_project.yml`. An example from our codebase would be the `streams` package, which contains modules for different levels of aggregation: `by_track_feed`, `by_track_country_feed`, `by_track_country_region_feed`. These tables get more detailed on each level, containing more and more rows. The idea here is that we model different levels of detail, suited for different kinds of questions. So while the `by_track_country_region_feed` tables would be used to answer the question "How often has track X been streamed in New York?" they _could_ also answer the question: "How often has track X been streamed in total?" - this however would require summing up all streams from all regions and countries when we query the table, which could take some time. The tables in `by_track_feed` already contains the sum, and would answer that question much faster. The tables in these modules come in two flavors: `DAILY` and `ROLLUP`. `DAILY` tables usually contain a column with a reference date, for example `DOWNLOAD_ACTIVITY_DATE`. When we build a `DAILY` table, we add new rows to it while leaving the existing rows untouched. In the case of `streams` tables that happens when new data is added to `FACT_ANALYTICS`. A `ROLLUP` table "rolls up" the data from the `DAILY` tables, for example the per-day streaming numbers into all-time streams. A `ROLLUP` table is rebuilt from scratch everytime we run dbt. dbt allows to refer to other models from within a model, in our case the model `STREAMS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT` is referred to from within `STREAMS_BY_TRACK_FEED_DISTRIBUTOR_DAILY_DBT`. In the code for the model the reference looks like this: ```sql SUM(streams) AS streams, [...] FROM {{ ref('STREAMS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT') }} ``` dbt maintains an internal dependency graph and can therefore determine the order in which the tables need to be built, in our example dbt knows that when we want to run the `streams` package, the `STREAMS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT` table needs to be built before the `STREAMS_BY_TRACK_FEED_DISTRIBUTOR_DAILY_DBT` table. As mentioned above `DAILY` tables use the **incremental** table materialization, which means dbt creates the table on the initial run, and **only adds new data** from its source tables on consequent runs, thus saving us time and money compared to a complete rebuild of a table (which is still possible by triggering a `FULL REFRESH`). The materialization of the model can be set at the beginning of its .sql file: ```sql config({ 'materialized': 'incremental', ``` We still need to tell dbt how to exactly identify new rows. We stated above that we're using dates to determine which rows are new. In practice we are actually using a "rolling refresh" window, that refreshes the last X amount of days. We are doing this to account for data that is being reported with a delay, in the case of streaming data this can happen when stores retroactively report additional data for streams from several weeks ago. So most `DAILY` tables use a `WHERE` clause that looks like this: ```sql WHERE download_activity_date BETWEEN DATEADD(day, -({{ env_var('DAYS_TO_RECALCULATE', 3) }} - 1), {{ max_available_date }}) AND {{ max_available_date }} ) ``` The `{{ ... }}` syntax is part of the [Jinja](http://jinja.pocoo.org/) templating language, and its contents are replaced at runtime. `DAYS_TO_RECALCULATE` is derived from an env var (set for example in the jenkins job that is running dbt) with a default value of 3. `max_available_date` is representing the latest date we actually have data for. We have different max available dates (for streaming numbers and YouTube views for example). The macros to populate these vars live in `utils/macros/dates`, which themselves are simple queries which target the dbt package `data_availability`, in which we actually determine these dates by running some statistical SQL analysis on `FACT_ANALYTICS`. # Setup The following parts will guide you through the process of setting up the project on your machine. ## About pipenv This project uses [pipenv](https://docs.pipenv.org/) to manage package dependencies (top-level and all subdependencies) and Python versions, install virtual environments, automatically load environment variables, and more! Most importantly, it produces deterministic builds. It uses `Pipfile` and `Pipfile.lock` files, instead of a `requirements.txt`. To install, please follow the instructions [here](https://docs.pipenv.org/#install-pipenv-today). ## Installation From the project root directory, install dependencies and create a virtual environment using the instructions below. Ensure you're using Python 3.11 (`python -V`). If you're not using Python 3.11 we suggest using pyenv (or other Python version manager) to install it. If you use pyenv when changing directories `.python-version` should set your Python version to 3.11.2. ```sh pyenv install 3.11.2 ``` Install pipenv. ```sh pip install --upgrade pip # this upgrades pip pip install pipenv ``` Install pip dependencies. ```sh $ make pipenv_dev ``` Install dbt packages. ```sh $ make install_dbt_deps ``` Copy `.env.shadow` to `.env` and fill in your credentials. ```sh $ cp .env.shadow .env ``` Make sure to use a custom schema in `DEV_ENGINEERING`. You can create one by running ```sql CREATE SCHEMA DEV_ENGINEERING.; ``` in Snowflake. #### Snowflake authentication using key pair To authenticate using SSH use following documentation: https://docs.snowflake.net/manuals/user-guide/snowsql-start.html#using-key-pair-authentication TL;DR: ```bash mkdir ~/.ssh/snowflake && cd ~/.ssh/snowflake # recommended openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub ``` set key path to SNOWFLAKE_PRIVATE_KEY_PATH variable set passphrase to SNOWFLAKE_KEY_PASSPHRASE variable set DBT_PROFILES_DIR profiles/sf_ssh_auth Then send newly generated public key to systems@theorchard.com and create SYS ticket. Note: SNOWFLAKE_PASSWORD variable is still required but it can be empty If everything is set up correctly, you should be able to run the integration tests using the command ```shell script $ make test_integration ``` ## Development ### Running models locally You can build dbt models locally by reading source data from a production or QA schema while writing the output to your personal dev schema. This is done by setting the `SOURCE_*` and `DESTINATION_*` env vars in your `.env` file: ```sh # Where dbt writes model output (your dev schema) DESTINATION_SNOWFLAKE_DATABASE=DEV_ENGINEERING DESTINATION_SNOWFLAKE_SCHEMA=_DBT_DEV # Where dbt reads source tables from (QA or PROD) SOURCE_SNOWFLAKE_DATABASE=FACTS SOURCE_SNOWFLAKE_SCHEMA=QA ``` Then run a specific model with: ```sh make run models= ``` or a full package: ```sh make run models='.*' ``` When these vars are not set, both source and destination default to `SNOWFLAKE_DATABASE` / `SNOWFLAKE_SCHEMA`, preserving the original behavior. > **Note:** Integration tests (`make test_integration`) always use `SNOWFLAKE_DATABASE` / > `SNOWFLAKE_SCHEMA` for both source and destination (via the `test` target), regardless > of the `SOURCE_*` / `DESTINATION_*` vars. This is because tests seed CSV fixtures and > query them in the same schema. ## Testing The integration tests are a fundamental part of the development process, as they allow you to test the models on a much smaller scale (with a couple of hand picked rows in the source tables, instead of millions. How to setup tests will be discussed further below). To run the integration tests, use ```sh $ make test_integration ``` **The strongly recommended approach however is to run individual packages instead**. Taken from the `Makefile`: ```sh $ scripts/test_integration_models.sh streams ``` There are some scenarios where dbt behaves unexpectedly, for example tests failing that really shouldn't 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 running ```sh $ make clean $ make install_dbt_deps ``` before running tests locally. Another source of error can be an outdated dbt version. To make sure you have the latest versions installed to run the models, run ```sh $ make pipenv_dev ``` ### Writing a test The basic idea behind defining a test scenario is that for each model, we define a reduced version of the source tables and an expected outcome-table in `.csv` file format, so called 'fixtures'. Looking at the `streams` model for example, we find these files in `streams/tests/integration/fixtures`. In this folder there are two sub-folders, `full-refresh` and `incremental`. These contain the fixtures for our two main scenarios: With a full-refresh we build a new table from scratch (or rebuild it completely). An incremental build adds new rows to an existing table. Therefore these two tests are performed one after another. The full-refresh folder contains a fixture for the source tables `FACT_ANALYTICS`, which would contain several million rows in the production table. Here we can hand pick or modify the rows we want to test specific scenarios. The expected results are defined in the files with the prefix `EXPECTED_`. When running `make test_integration` dbt will take the source files and create and populate tables (also called seeding) in the database / schema you defined in your `.env` file, run your model against it and create a table from that. It will also create a table from the `EXPECTED` files and will then check for differences between the results of your model and the seeded file. If the diff is zero rows, the test is graded as SUCCESS. If the results don't match up, you will get an error message like `Got 4 results, expected 0.` NOTE: If your model is referencing an entirely new source table, you will need to add a new fixture for this table into both the `incremental/` and `full_refresh/` folders. Note that a field from that table may be referenced and need to be updated in other fixtures. ### Updating Test Fixtures When making updates it is often necessary to update the snapshots used for test validation. Executing `make test_integration` will run through both a full refresh and incremental builds, and snapshots will likely need to be updated for each. 1. Put the full refresh fixtures into the `fixtures/full_refresh/` folder of your package, put incremental fixtures into `fixtures/incremental/`. 2. Run the tests for the package ```sh $ scripts/test_integration_models.sh ``` 3. The tests will fail once the results of the current build no longer match the data in the `EXPECTED` files. This can happen either on the `full-refresh` (fisrst) or `incremental` (second stage). 4. Look up the affected table in Snowlake via `SELECT * FROM DEV_ENGINEERING.._DBT;` 5. Click the "Download or View results" button (an arrow pointing downwards) in the Snowflake console, make sure `.csv` is selected, click "Show in Dialog" and copy the contents. 6. Replace the data in the snapshot file(s) (`EXPECTED_.csv`) with the data obtained via Snowflake. 7. Repeat this process for the incremental build snapshots **Note: Snapshots should be updated with caution and only when the data produced by the model has been verified to fit the specifications.** ## Project structure ``` dbt-analytics/ scripts/ dbt-project.yml -- global config, you don't need to change it packages.yml -- specifies dbt packages base/ -- dbt package which wraps source tables into the views utils/ -- dbt package which contains common utils like macros / -- isolated dbt package with a set of models, e.g. summary_streams macros/ models/ tests/ integration/ fixtures/ full_refresh/ .csv .csv .csv incremental/ .csv .csv .csv test_.sql dbt-project.yml -- separate dbt config for this ``` # Patterns and conventions ## Model naming conventions Before transitioning to these new naming patterns, there were no rules on how to name dbt tables (apart from the developers best intentions). Some names described the context a table may have been used in (e.g. `MOBILE_TOP_MARKETS`) while other names tried to suggest what kind of data the tables contained (e.g. `PLAYLIST_METADATA`, `SUMMARY_METRICS_BY_ISRC`. However as the number of models continued to grow, and some models became the source of data for applications beyond their originally intended purpose, it became more important to accurately describe what a model contained, and therefore what their purpose could be. ### Naming a table The first step is to look at what category of data is described in the table. At The Orchard, we maintain lots of tables for categories like streams, downloads, stores, etc. The first word of the model name should indicate which category of data it contains. If a model contains data about streaming numbers for example, the model name should begin with `STREAMS_` ### Column types **Note: We established the convention that all dbt column names have to be in snake_case. This is currently limited to tables created by dbt and may not be the case in tables found elsewhere at The Orchard. This also means that when you create a new table which SELECTs from a table that doesn’t follow this convention, you should convert the column names in your model, e.g. `SELECT TRACKID AS TRACK_ID`** Next we need to clearly define which columns make up the primary keys, filters and metrics of the table. A revised `SELECT` statement could look like this: ```sql SELECT --primary key track_id, download_activity_date, store_id, --filter only label_id, subaccount_id, product_id, isrc, --metrics SUM(units) AS streams ``` The *primary key* is made up by one or more columns where each combination of their values will be unique in the entire table. They identify exactly one row. The *filter* columns’ only purpose is to allow to filter the table further down. Removing or adding filter columns does not affect the total row count of the table (unlike adding or removing primary key columns would). The *metrics* columns represent the data points the table is actually built for. These are usually calculated values that will only appear in this table. The column names should also indicate what data they represent in a very explicit way. Like the name of the table, the first word in the column name also reflects the category of data, followed by the parameters they were calculated with. If we sum up streaming data for a specific period of time for example, the column name could be `streams_7_days`. Ideally you should be able to deduce what kind of data a column represents without knowing in which table it appears. So after stating the category of data, the naming of the model should then list the primary keys, with the prefixed keyword BY. In this case the name would be `STREAMS_BY_TRACK_DATE_STORE`. However `DATE` is a special dimension that does not need to be included here, the reasons why are discussed further below. Please also note that the words used do not exactly mirror the names of the columns, so `track_id` becomes `TRACK`, `store_id` becomes `STORE` etc. The table name at this point is therefore `STREAMS_BY_TRACK_STORE`. If we ever intend to change the primary keys of the table, we then must also change the name of the table accordingly. This is to prevent from names becoming “out of sync” with the structure of the models. Additionally, changing the name clearly indicates that the nature of the table has fundamentally changed. Removing or adding filter columns can be done without renaming the models. ### dbt table materializations The final step is to identify how the table is built by dbt. Normally a table is either (re)built from scratch every time the model is run, or only new data gets added to an existing table (a so called incremental table). A special case are the tables that only contain “top” data, e.g. only the top ten streamed tracks of a label, as compared to a table that contains the data for all tracks of that label. These tables are also rebuilt every day from scratch but their content is limited and can change completely on every rebuild. The model’s name must also describe in which of these three ways it is built. We will therefore suffix the model name with either `_DAILY` for incrementally built tables (as the models are run on a daily basis and have new rows added to them every day), `_ROLLUP` for full-refresh tables (since these are very often downstream models of the DAILY models and rollup their data, e.g. by calculating the all time number of streams for a track on each run), and `_TOP` for the third category. So our example model name would now be `STREAMS_BY_TRACK_STORE_DAILY`. As mentioned above, the `DATE` would also be a primary key and should be included in the name of the table. However, since we are anticipating that these very explicit names can become very long, and that the keyword `DAILY` already implies that each row is also identified by a specific date, we can omit the primary key DATE from the name, which leaves us with `STREAMS_BY_TRACK_STORE_DAILY`. ### feed filter mechanism The basic idea behind this mechanism is that we want to be able to only refresh the data for a given set of `feed_ids`. For example if we find out that Amazon has been providing falsy analytics data within the last 20 days, it doesn't make sense to re-calculate iTunes, Spotify and YouTube data as well. To allow this, we introduced a jinja macro that should be used as the first argument in a `WHERE` clause of all `_DAILY` tables. The syntax is very simple, just add ```sql WHERE {{ utils.feed_filter() }} ``` to your sql statement. if a `feed_filter` variable has been included in vars via ```shell script $ dbt run --vars '{"feed_filter": "1, 2, 3"}' ``` this will be replaced at runtime with ```sql WHERE feed_id IN (1, 2, 3) ``` in the dbt model. If no `feed_filter` variables have been passed to dbt, it will simply evaluate to ```sql WHERE TRUE ``` (Further info on the variable syntax can be found here: https://docs.getdbt.com/docs/building-a-dbt-project/building-models/using-variables) Please note that the `{{ utils.feed_filter()) }}` macro supports an optional parameter that allows you to set the syntax for `feed_id` in your query, while `feed_id` is the standard in dbt models, it's called `feedid` in `FACT_ANALYTICS` for example. So you may use the corresponding syntax whenever you rely on a different source table like so: ```sql FROM {{ source(this.schema, 'fact_analytics') }} fa WHERE {{ utils.feed_filter("fa.feedid") }} ``` which would evaluate to ```sql FROM FACT_ANALYTICS fa WHERE fa.feedid IN (1, 2, 3) ``` ***It is extremely important that you include the feed_id alongside the download_activity_date in your incremental table config as the unique_key. Otherwise all other data apart from the feed_id that has been filtered to will be deleted from the table within the recalculated time range*** Therefore your incremental table config should look something like this: ```sql {{ config({ 'materialized': 'incremental', 'incremental_strategy': 'delete+insert', 'unique_key': 'CONCAT(download_activity_date, \' - \', feed_id)', 'post-hook': [ 'ALTER TABLE {{ this }} CLUSTER BY ()', 'ALTER TABLE {{ this }} RESUME RECLUSTER' ] }) }} ``` It is recommended to only run the `streams` package with the FEED_FILTER variable set. FULL_REFRESH must be false!!! Other packages don't benefit much from the feed filter mechanism and should be run as usual. The job: https://scheduler.theorchard.io/job/dbt-scheduler-analytics-build-views/ # Jenkins ## Commits and Pull Requests If you push up a few commits in quick succession, you can use [dbt-analytics-pull-request](https://pipeline.theorchard.io/job/dbt-analytics-pull-request/) to kill the test runs. Why? Because each push adds another test run to the end of the queue. Therefore, if you push three commits in quick succession, for example, you could be looking at a wait time of ~3 hours if you don’t kill the first two runs at the start of the queue manually. ## Pipelines & Schedulers The jenkins job used to verify and deploy dbt models is at https://pipeline.theorchard.io/job/dbt-analytics-pipeline/ This pipeline schedules daily builds on PROD and clones the tables from FACTS.PROD -> FACTS.QA: https://scheduler.theorchard.io/job/dbt-scheduler-analytics-pipeline/ * Please don't forget to add every new model to https://github.com/theorchard/sql-snowflake-utils/blob/master/TEMP_qa_dbt_models_refresh.sql The actual runs of the jenkins jobs can be found at https://scheduler.theorchard.io/job/dbt-scheduler-analytics-build-views/ The jenkins job that runs the integration tests when a PR is created or updated lives at https://pipeline.theorchard.io/job/dbt-analytics-pull-request/ ## Process Once PR is Merged These are the common steps to take when building a new model or updating an existing model (such as adding a new column to an existing model). Please note that different steps may need to be taken for different scenarios. Unlike other repos, merging your dbt-analytics PR will not automatically kick off the [dbt-analytics-pipeline](https://pipeline.theorchard.io/job/dbt-analytics-pipeline/). You must kick off the build manually: 1. Go to the `Build with Parameters` section of the [dbt-analytics-pipeline](https://pipeline.theorchard.io/job/dbt-analytics-pipeline/) 2. In the `MODELS` field, enter the name of the model(s) to build. If you need to build all models in a particular package, enter the package name e.g. `metrics.*`. If you only need to build a specific model(s) in a package, enter the model name(s) e.g.`METRICS_BY_PRODUCT_FEED_DISTRIBUTOR_ROLLUP_DBT`. 3. If you are running the pipeline shortly after merging your PR, you can uncheck `RUN_UNIT_TESTS`, as the tests were run in the PR job. You can check out the [dbt-analytics-pull-request](https://pipeline.theorchard.io/job/dbt-analytics-pull-request/) pipeline to see your PR build running. 4. Press `Build`. You can now build your model(s) on QA: 1. Head to [dbt-scheduler-analytics-build-views](https://scheduler.theorchard.io/job/dbt-scheduler-analytics-build-views/) and click on `Build with Parameters`. 2. Set the `SNOWFLAKE_SCHEMA` to `QA`. 3. In the `MODELS` field, enter the same model(s) you specified in the pipeline job. 4. If this is an entirely new model, you do not need to select `FULL_REFRESH`. If adding a column to an existing table, for example, you should select `FULL_REFRESH`. 5. Press `Build`. 6. If the build is successful, you should see your table(s) in `FACTS.QA`. N.B. Unless you do the following steps before the daily build from PROD to QA, your model(s) will disappear from QA after the daily build. After verifying the model(s) in QA, you can now build your model(s) in PROD: 1. Head to [dbt-scheduler-analytics-build-views](https://scheduler.theorchard.io/job/dbt-scheduler-analytics-build-views/) and click on `Build with Parameters`. 2. Set the `SNOWFLAKE_SCHEMA` to `PROD`. 3. In the `MODELS` field, enter the same model(s) you specified in the pipeline job. 4. If this is an entirely new model, you do not need to select `FULL_REFRESH`. If adding a column to an existing table, for example, you should select `FULL_REFRESH`. 5. Press `Build`. 6. If the build is successful, you should see your table(s) in `FACTS.PROD`. Alternatively, you can wait for the daily run of the [dbt-scheduler-analytics-pipeline](https://scheduler.theorchard.io/job/dbt-scheduler-analytics-pipeline/). But don't forget to add your model to the qa_dbt_model_refresh (see below). You can now add your new model to the [sql-snowflake-utils](https://github.com/theorchard/sql-snowflake-utils) repo: 1. Add your model to the `TEMP_qa_dbt_models_refresh.sql` file. This will ensure the model is cloned from `FACTS.PROD` to `FACTS.QA` in the [daily scheduler](https://scheduler.theorchard.io/job/dbt-scheduler-analytics-pipeline/). * Note - If you have added/removed a column from a model and the `materialization` type is `table`, DBT will rebuild the entire model for you, so you don't need to run a full refresh. # Maintenance / Housekeeping ## Identifying and deleting deprecated tables TODO: Add a section with a Snowflake queries which can find tables not accessed for the last month. 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. It can take weeks to notice this in the frontend, usually when someone complains that the data they changed hasn't been updated in a while or should no longer be visible at all. The best course of action is therefore: - If you update or delete a model, make sure that all references to this table are updated as well. Use github's [code search](https://github.com/search?q=org%3Atheorchard+MY_TABLE&type=code) (across the entire organization). Make sure to search for the production table (the one without the `_DBT` at the end) first and foremost! - Create a database PR to drop these tables (it could look something like [this](https://github.com/theorchard/database/blob/6143c923e26b745b799122d0113652ec8a7299b9/snowflake/FACTS/build/changelog/dml/DROP_deprecated_dbt_tables_2.sql) \- though technically, these PRs belong in the `ddl` folder) Experience has shown that these steps are often forgotten, and it's good practice to actively search for these tables every once in a while. To identify these tables we leverage the fact that all dbt models have a table variant that ends in `_DBT`. - In snowflake, create a new, empty schema (or DROP and re-CREATE your local schema you use for tests). In this example we'll use the schema name `NLITTMANN_DBT` as an example. You can drop and recreate your schema with ```sql DROP SCHEMA DEV_ENGINEERING.NLITTMANN_DBT; CREATE SCHEMA DEV_ENGINEERING.NLITTMANN_DBT; ``` - In your dbt `.env` file make sure that this is the schema you set up as `SNOWFLAKE_SCHEMA` - Run `make test_integration` and wait for it to finish. Your schema should now contain only those dbt tables that are actually used and maintained by dbt. We're using this set of tables to identify the remaining tables in `FACTS.QA` and `FACTS.PROD` that end in `_DBT` - those would be the deprecated tables then. This can be achieved using the following query (make sure to replace the schema name with your local schema!) ```sql -- run make test_integration on a clean (empty) schema before running this query, substitute NLITTMANN_DBT for your personal dbt schema -- ignores seeded tables like STAGING_RAW_ or FACT_ tables with current_dbt_tables as (select table_name from dev_engineering.information_schema.tables where table_schema = 'NLITTMANN_DBT' and table_name like '%_DBT'), possible_production_table_names as -- find all upgraded tables without '_DBT' suffix (select left(table_name, length(table_name) - 4) as table_name from current_dbt_tables), -- identify deprecated tables in PROD/QA that still have the '_DBT' suffix, but are nowhere to be found in the dbt repo deprecated_dbt_tables_to_delete as (select table_name from facts.information_schema.tables where table_schema in ('PROD', 'QA') and table_name not in (select table_name from current_dbt_tables) and table_name like '%_DBT'), -- identify deprecated tables in PROD/QA that are upgraded dbt tables deprecated_production_tables_to_delete as (select table_name from facts.information_schema.tables where table_schema in ('PROD', 'QA') and table_name in (select left(table_name, length(table_name) - 4) from deprecated_dbt_tables_to_delete)) SELECT * FROM ( SELECT * FROM deprecated_dbt_tables_to_delete UNION ALL SELECT * FROM deprecated_production_tables_to_delete ) GROUP BY 1 ORDER BY 1; ``` Make sure to double-check every entry this list produces against the existing models in the dbt codebase as well as existing queries from other parts in the codebase (using the github code search linked above). Also be on the lookout for mock fixtures, config entries, `EXPECTED_` fixtures and entries in `base/models/sources.yml`. You can now create a db PR to drop these tables. ## Changing clustering key Unfortunately Snowflake auto clustering is not smart enough yet to efficiently re-arrange partitions if we just change the clustering key. This will result not only in a giant spike of auto clustering credits, but also in daily overconsumption of them. In order to avoid such a disaster we should leverage the CTAS approach. 1. Disable the [dbt scheduler job](https://scheduler.theorchard.io/job/dbt-scheduler-analytics-pipeline/). 2. Prepare a DB PR like [this one](https://github.com/theorchard/database/pull/11231/files). 3. Ask someone with SYSADMIN/ACCOUNTADMIN Snowflake rights to run these statements. This will allow to JENKINS_CLI_USER of the db-deploy Jenkins job to execute the statements from the DB PR, and also not to interfere with clients reading from the table (it would be better not to copy-paste, but to check the real grants on the table beforehand): ``` GRANT OWNERSHIP ON FACTS.PROD.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO FACTS_DB_PROD_SCHEMA_READWRITE REVOKE CURRENT GRANTS; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO FACTS_DB_PROD_SCHEMA_READ; GRANT SELECT, TRUNCATE, REFERENCES, REBUILD, UPDATE, INSERT, DELETE ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO FACTS_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO LOOKERADMIN; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO ROYALTYACCOUNTING_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO ROYALTYACCOUNTING_DB_QA_SCHEMA_READWRITE; GRANT OWNERSHIP ON FACTS.PROD.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO FACTS_DB_PROD_SCHEMA_READWRITE REVOKE CURRENT GRANTS; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO FACTS_DB_PROD_SCHEMA_READ; GRANT SELECT, TRUNCATE, REFERENCES, REBUILD, UPDATE, INSERT, DELETE ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO FACTS_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO LOOKERADMIN; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO ROYALTYACCOUNTING_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO ROYALTYACCOUNTING_DB_QA_SCHEMA_READWRITE; ``` 4. Deploy the DB PR. For the big table it might take many hours, and it will block other Snowflake DB PRs. 5. Ask someone with SYSADMIN/ACCOUNTADMIN Snowflake rights to run these statements in order to revert ownership change (it would be better not to copy-paste, but to check the real grants on the table beforehand): ``` GRANT OWNERSHIP ON FACTS.PROD.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO PROD_DBT REVOKE CURRENT GRANTS; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO FACTS_DB_PROD_SCHEMA_READ; GRANT SELECT, TRUNCATE, REFERENCES, REBUILD, UPDATE, INSERT, DELETE ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO FACTS_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO LOOKERADMIN; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO ROYALTYACCOUNTING_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY TO ROYALTYACCOUNTING_DB_QA_SCHEMA_READWRITE; GRANT OWNERSHIP ON FACTS.PROD.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO PROD_DBT REVOKE CURRENT GRANTS; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO FACTS_DB_PROD_SCHEMA_READ; GRANT SELECT, TRUNCATE, REFERENCES, REBUILD, UPDATE, INSERT, DELETE ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO FACTS_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO LOOKERADMIN; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO ROYALTYACCOUNTING_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON facts.prod.STREAMS_BY_MULTI_PRODUCT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_DBT TO ROYALTYACCOUNTING_DB_QA_SCHEMA_READWRITE; ``` 6. Deploy the dbt-analytics PR to change clustering key, like [this one](https://github.com/theorchard/dbt-analytics/pull/801/files). 7. Re-enable the [dbt scheduler job](https://scheduler.theorchard.io/job/dbt-scheduler-analytics-pipeline/). 8. Monitor auto clustering credits usage for a few days. ## _RECENT and _HISTORY tables We split many _DAILY (and _ROLLUP, using more sophisticated approach) tables in the streams package in two: _RECENT and _HISTORY. That allow us to save money on auto reclustering, since the _HISTORY tables typically stay untouched (there is one exception: backfilling of old data). Using the split rollups we can save significant amount of money on the daily dbt builds. The _HISTORY tables are tagged with the "history" tag, and we completely exclude them from the daily runs. In order to backfill some old data, one should MANUALLY remove ""tag:history" from the EXCLUDE param of the Jenkins job. We move data from _RECENT to _HISTORY tables once a month, and we do it automatically (there is a corresponding job in the https://scheduler.theorchard.io/job/dbt-scheduler-analytics-pipeline/ pipeline. ## Jenkins jobs + https://scheduler.theorchard.io/job/dbt-scheduler-analytics-build-views/: triggered twice a day, by Spotify SME ETL an Apple Music SME ETL. + https://scheduler.theorchard.io/job/dbt-scheduler-export-to-elasticsearch/: runs once a day by schedule, exports data to Elasticsearch (GP and GSR indexes) + https://scheduler.theorchard.io/job/dbt-scheduler-analytics-daily-legacy-video-playlists-pipeline/: runs once a day by schedule, builds legacy (prod-ows-analytics-legacy for Workstation Analytics legacy pages) and video dbt models + https://scheduler.theorchard.io/job/dbt-scheduler-analytics-pipeline-playlists-hourly/: runs hourly, builds that portion of the playlists models which contains fresh data (the rest, mostly with private data, is built by the daily job) ## Terraformed S3 permissions required for unload to S3 https://github.com/theorchard/terraform-infra/pull/29879/changes This is required for the EXPORT_ models which are populating the Opensearch indexes for GP and GSR. ## Operations ### Upgrade data_availability, streams and metrics models within the main pipeline In the `operations` folder you can find the `upgrade_main_pipeline.sql` macro and the UPGRADE_MAIN_PIPELINE macro. They handle simultaneous upgrade of the data_availability, streams and metrics models (non-video ones). This is required to avoid inconsistencies in the data. The models where the `upgrade_view` macro in the post-hook has `run_always=True` parameter set, will be upgraded immediately and independently of the `upgrade_main_pipeline.sql` macro. If you're adding a new table which needs to be upgraded along with the streams and metrics tables, you should add it to the `upgrade_main_pipeline.sql` macro and `UPGRADE_MAIN_PIPELINE_DBT` model as well. There is still a way to build a model or package outside the main pipeline: the param `FORCE_MODEL_UPGRADE` should be set to `True` in the Jenkins job https://scheduler.theorchard.io/job/dbt-scheduler-analytics-build-views/.