/dbt-sf-to-bq-translator

name: dbt-sf-to-bq-translator
metadata:
  category: BigDataAndAnalytics
description: >-
  Translates Snowflake dbt SQL models to Standardized BigQuery SQL. Handles SQL
  compilation, Jinja macro placeholder masking, BigQuery Translation Service migration
  workflows, AST-based config transformations, explicit type casting, JSON extraction
  standardization, and deduplication. Use when migrating Snowflake dbt pipelines
  or models to Google Cloud BigQuery. Don't use for generic BigQuery queries or
  non-Snowflake SQL migrations.

dbt Snowflake to BigQuery Translator

You are responsible for: 1. **Dialect Translation**: Translating Snowflake dbt SQL models to Standardized Google BigQuery SQL. 2. **Standardization & Compliance**: Enforcing Google-specific standards including copyright headers at the very top of each file, explicit type casting, standardized JSON extraction, and deduplication via `QUALIFY` with `_extracted_at`. 3. **Workflow Integration**: Preserving dbt Jinja constructs and storing the final BigQuery-compatible models.

Follow the instructions given you under `migration_plan/[mig_prefix]/tasks.md`. You will add your progress during operation and summary at the end to the tasks file so that human supervisor can track where you are. You recover from errors by checking the tasks file.

Prerequisites & Environment Setup

Before starting the translation, ensure your Google Cloud environment is properly configured: 1. **Google Cloud SDK**: Install the [Google Cloud SDK](https://cloud.google.com/sdk/docs/install.md.txt) if not already installed. 2. **Authentication**: Authenticate your CLI session:

    gcloud auth login
    gcloud auth application-default login

3. **Project Configuration**: Set your active GCP project:

    gcloud config set project {project_id}

4. **Billing Account**: Verify that an active Google Cloud Billing account is attached to the target project. 5. **Enable Required APIs**: Ensure BigQuery, Migration, and Storage services are enabled:

    gcloud services enable bigquerymigration.googleapis.com storage.googleapis.com bigquery.googleapis.com

6. **Region Selection**: Configure your preferred compute/BigQuery region (default recommended: `us-central1` or `us`). See [Google Cloud Locations](https://cloud.google.com/about/locations.md.txt):

    gcloud config set compute/region us-central1

Steps

• **Initialization & Setup**: If you do not have a defined `mig_prefix` or if the user wants to start a new translation project, you **MUST** first ask the user for:

1. Migration Project Name (e.g. `my_migration_project`). 2. Input directory containing Snowflake SQL files. 3. Output directory where BigQuery SQL files should be saved. 4. GCS Bucket name for staging translation assets. 5. GCP Region (e.g. `us` or `eu`). 6. (Optional) Local path to a directory or `.zip` file containing source database metadata (such as `columns.csv` or `tables.csv`). Once provided, create the tasks checklist file under `migration_plan/[mig_prefix]/tasks.md` with unchecked tasks representing the migration steps.

• **Automated Execution via Bundled Scripts**:

Execute the deterministic end-to-end migration using the bundled translation script `scripts/bulk_translate_via_gcloud.py`:

  python3 scripts/bulk_translate_via_gcloud.py \
    --input <input_dir> \
    --output <output_dir> \
    --bucket <gcs_bucket> \
    --location <region> \
    [--metadata <metadata_path>]

The migration tools bundled in `scripts/` perform the following coordinated actions:

• `scripts/bulk_translate_via_gcloud.py`: Orchestrates end-to-end bulk migration, automating pre-processing, GCS upload, BigQuery Translation Service invocation, download, post-processing, and YAML configuration copying.
• `scripts/dbt_translator.py`: Core translation library containing the deterministic AST parser for `config(...)`, Jinja placeholder masking and restoration, JSON extraction sanitization, macro auditing, casing/join standardization, and copyright header enforcement.

• **Detailed Translation Lifecycle (Executed by Scripts)**:

1. **Compile to Standard SQL using Placeholders (Pre-Translation)**:

• Read the original source dbt `.sql` files. Extract and strip the `{{ config(...) }}` header block from the top of each file.
• Replace dbt macro calls with standard-SQL-compliant placeholder identifiers to prevent BigQuery Translation Service from throwing syntax errors:
• Replace `{{ source('src_name', 'table_name') }}` with `_DBT_SOURCE_src_name_DBTSEP_table_name_`
• Replace `{{ ref('model_name') }}` with `_DBT_REF_model_name_`
• Eliminate Jinja curly braces (`{{ ... }}`) from the SQL prior to translation, ensuring the transpiler processes 100% valid Snowflake dialect SQL.

2. **Isolate the SQL Files**:

• Save these pre-processed, Jinja-free files to a staging input directory ready for GCS upload.

3. **Pre-Process Metadata & Translate SQL via BigQuery Translation Service**:

• If a metadata path is provided, map table entries matching discovered dbt models/sources to their placeholder names in `columns.csv` and `tables.csv`, clear catalog names to prevent namespace resolution errors, package into `metadata.zip`, and upload to GCS.
• Upload staging SQL files to GCS:

`gcloud storage cp <staging_input_dir>/*.sql gs://[YOUR_BUCKET]/migration_input/`

• Create `migration_config.yaml` specifying `snowflakeDialect` as source and `bigqueryDialect` as target (with `schemaPath` pointing to `metadata.zip` if provided).
• Trigger translation workflow:

`gcloud bq migration-workflows create --location=<region> --config-file=migration_config.yaml --no-async`

• Download translated GoogleSQL files from GCS:

`gcloud storage cp gs://[YOUR_BUCKET]/migration_output/*.sql <translated_output_dir>/` 4. **Restore Placeho