# Paradime Turbo CI Schema Cleanup

## Overview

During CI/CD processes, Paradime Turbo CI creates temporary schemas for testing.

 The schemas will be dropped automatically on Pull Request merged if:

* [Snowflake](https://docs.paradime.io/app-help/documentation/settings/connections/scheduler-environment/snowflake) is your connected Data Warehouse
* [GitHub](https://docs.paradime.io/app-help/documentation/bolt/ci-cd/turbo-ci/github) is your git provider

 This macro identifies and drops these schemas on demand by identifying all schemas with prefix `paradime_turbo_ci`

## Configure the `drop_turbo_ci_schema` macro

To clean up temporary schemas created during CI runs, you can create a custom macro in your dbt™️ project. Here's how:

1. Navigate to your dbt project's `macros` folder
2. Create a new file called `drop_turbo_ci_schema.sql`
3. Add the following code to the file:

{% code title="drop\_turbo\_ci\_schema.sql" lineNumbers="true" %}

```sql
{# Deletes BigQuery datasets created by Paradime Turbo CI #}
{% macro drop_turbo_ci_schema(dryrun=True) %}

{# Get project ID from BigQuery connection config based on the target used to executed the macro #}
{%- set default_database = target.database -%}

{# Set schema pattern to match with schemas created by Paradime Turbo CI #}
{%- set paradime_turbo_ci_schema = 'paradime_turbo_ci%' -%}

{# 
  IMPORTANT: Set your BigQuery region here based on your configuration
  Common region options include:
  - us (United States)
  - eu (European Union)
  - asia-east1 (Taiwan)
  - asia-northeast1 (Tokyo)
  - asia-southeast1 (Singapore)
  - australia-southeast1 (Sydney)
  - europe-west1 (Belgium)
  - europe-west2 (London)
  - europe-west3 (Frankfurt)
  - northamerica-northeast1 (Montreal)
  - southamerica-east1 (São Paulo)
  
  For a complete list of regions, see: https://cloud.google.com/bigquery/docs/locations
#}
{%- set region = 'us' -%}

{# Query to generate DROP commands for matching schemas #}
{% set cleanup_query %}
WITH TURBO_CI_DROP_SCHEMA AS (
SELECT
  catalog_name,
  schema_name
FROM {{default_database}}.`region-{{region}}`.INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name LIKE '{{ paradime_turbo_ci_schema }}'
)
SELECT
'DROP SCHEMA ' || '`' || catalog_name || '.' || schema_name || '`' || ' CASCADE' || ';' as DROP_COMMANDS
FROM
  TURBO_CI_DROP_SCHEMA
{% endset %}

{# Get list of DROP commands to execute #}
{% set drop_commands = run_query(cleanup_query).columns[0].values() %}

{# Execute or print DROP commands based on dryrun parameter #}
{% if drop_commands %}
  {% if dryrun | as_bool == False %}
    {% do log('Executing DROP commands...', True) %}
  {% else %}
    {% do log('Printing DROP commands...', True) %}
  {% endif %}
  {% for drop_command in drop_commands %}
    {% do log(drop_command, True) %}
    {% if dryrun | as_bool == False %}
      {% do run_query(drop_command) %}
    {% endif %}
  {% endfor %}
{% else %}
  {% do log('No relations to clean.', True) %}
{% endif %}

{%- endmacro -%}
```

{% endcode %}

{% hint style="info" %}
The above example is based on BigQuery, use [DinoAI](https://docs.paradime.io/app-help/documentation/dino-ai) to adapt it to other Data Warehouse providers.
{% endhint %}

### Usage

The `drop_turbo_ci_schema` macro helps clean up temporary schemas created during CI runs, with an optional dry-run mode for safe testing.

### Arguments

* **dryrun** (bool, optional)
  * `True`: Preview DROP commands without executing them
  * `False`: Execute DROP commands and remove schemas
  * Default: `True` (Safe mode)

```bash
# Preview DROP commands
dbt run-operation drop_turbo_ci_schema

# Execute DROP commands
dbt run-operation drop_turbo_ci_schema --args '{dryrun: false}'
```

## Configure a Bolt Schedule

Set up a scheduled job in Paradime Bolt with the Below:

#### Schedule Settings

<table><thead><tr><th>Setting</th><th width="262">Value</th><th>Explanation</th></tr></thead><tbody><tr><td><strong>Schedule Type</strong></td><td><code>Standard</code></td><td>Ensures consistent execution for production workloads in a single environment. Best for regular data pipeline runs</td></tr><tr><td><strong>Schedule Name</strong></td><td><code>drop turbo ci schemas</code></td><td>Descriptive name that indicates purpose</td></tr><tr><td><strong>Git Branch</strong></td><td><code>main</code></td><td>Uses your default production branch to ensure you're always running the latest approved code</td></tr></tbody></table>

#### Command Settings

The template uses one commands that execute our macro and passes the argument **dryrun** set to `false`

* `dbt run-operation drop_turbo_ci_schema --args '{dryrun: false}' --target ci`

{% hint style="info" %}
Make sure to use the same `--target` used when running Paradime Turbo CI
{% endhint %}

#### Trigger Type

* **Type**: Scheduled Run (Cron)
* **Cron Schedule**: `0 8 * * 0` (Every Sunday at 8AM UTC)

{% hint style="info" %}
For custom command configurations, see [Command Settings](https://docs.paradime.io/app-help/documentation/bolt/creating-schedules/command-settings) documentation.
{% endhint %}

#### Notification Settings

* **Email Alerts**:
  * **Success**: Confirms all schemas were dropped successfully, letting you know your data pipeline is healthy
  * **Failure**: Immediately alerts you when the macro fails to drops your Paradime Turbo Ci schema