# Workflow to move models from v1 to v2
### Prerequisites
1. Read the [dbt modeling best practices](https://github.com/gamechanger/transformer/blob/main/transformer/models/README.md).
2. Look at the [example](https://github.com/gamechanger/transformer/pull/8) of moving a model from v1 to v2.
3. Make sure you have the `WAREHOUSE_TARGET_SCHEMA` environment variable set in your `.zshrc` as `dev_<firstname>`
4. Build the latest transformer docker image

    docker-compose build transformer

5. Check dbt <-> data warehouse connection.

    docker-compose run transformer dbt debug

### Process
1. Pick up a modeled table to work on from the [clubhouse](https://app.clubhouse.io/gamechangermedia/story/160945/umbrella2-convert-transformer-sql-scripts-to-dbt-sql) card. (e.g., `tm2.app_user`)
2. Pull the latest main line branch

    git pull origin main

3. Create a new branch from the mainline

    git checkout -b <initials>-<short_description_of_the_task>

4. Retrieve the table type of the model from this [sheet](https://docs.google.com/spreadsheets/d/1sKXXzX36PtK3G1Ba9ZmKCWHbdGVhgTJlpxv47-J8XnA/edit#gid=0)
5. Determine if the model needs to be split and write the SQL code
1. If the model is converged, split the code into bats and tm. The bats code goes in the `bats` schema and the tm code goes in the `tm` schema (e.g., `dim_bats_user.sql` and `dim_tm_user.sql`)
2. If the model has CTE that contain heavy logic, move them into the `intermediate` schema.
3. The final minimal code of converged models should live in the `gc` schema. Code here usually unions data from different soures and does a `select *` in the end. (e.g `dim_user.sql`). If the table is tm-only or bats-only, the final location is in the `bats` or `tm` schema.
4. ! Remember, the file names are model names and cannot be repeated across schemas.
6. Determine the sort key and dist key of the model and add it as a `{{ config() }}` block at the top of the file.
7. In your modeling, make sure you
1. Reference source tables with `{{ source(<source_name>, <table_name>) }}`. These will occur only in the `clean` models
2. Reference models using `{{ ref(<model_name>) }}`
3. Add a surrogate key to all final models (they may exist in `gc` if converged, in `bats` if BATS-only, or in `tm` if TM-only). It should be at the grain of the table. For example:

        final AS (
            SELECT
                {{ dbt_utils.surrogate_key(
                     ['team_id', 'valid_from']
                     ) }} AS team_key,
                *
            FROM combined
            WHERE valid_from != valid_to
        )

4. Always end the query with a `dbt_audit` block. `created_at` and `created_date` will be unmodified if it's not the first time.

        {{ dbt_audit(
        cte_ref="final",
        created_by="@<user_name>",
        updated_by="@<user_name>",
        created_date="<date>"
        ) }}

5. Name the last CTE before `dbt_audit` `final`
6. CTE name and source/model names should be different.
8. Add the model description in the `docs.md` present in the same directory of the model
9. Add the name and description of the model in `schema.yml` present in the same directory as the model.
10. Test the model
1. Rebuild your docker image (this step deletes your old image and builds a new one)

        docker rmi -f $(docker images | grep 'transformer_transformer') | docker-compose build transformer

3. Check for syntax errors

        docker-compose run transformer bash -c "dbt deps && dbt compile"

3. If there are errors, fix and rebuild the docker image. You can delete the existing docker image by running the following command, where the image id can be found by running `docker images` in the terminal:

        docker rmi -f image_id

A shortcut for the above is to run the following command which deletes the most recent docker image (which might be the transformer docker image):

        docker rmi -f `docker images | awk '{print $3}' | awk 'NR==2'`

4. Create a dev version of the model. This creates a model in `dev_<firstname>`

        docker-compose run transformer bash -c "dbt deps && dbt run --model <model_name>"

5. Run table tool to compare data between the `dev_<firstname>` model and the `v1` model.

        docker-compose run table_tool compare <schema_table_name> <schema_table_name2> [<date_column> <from_date> <to_date>]

11. Format the sql code using [SQLFluff](https://app.clubhouse.io/gamechangermedia/story/181070/tool-to-format-lint-beautify-sql)

    sqlfluff fix test.sql

12. Push the code, create a PR and add table tool test results to the PR.