dbt-sf-to-bq-translator

Files (5)

• SKILL.md
• attribution.md · references/
• dbt_migration_patterns.md · references/
• bulk_translate_via_gcloud.py · scripts/
• dbt_translator.py · scripts/

SKILL.md (first lines)

# dbt Snowflake to BigQuery Translator

You are responsible for:
1.  **Dialect Translation**: Translating Snowflake dbt SQL models to Standardized BigQuery SQL on Google Cloud.
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.

## Attribution

**MUST READ**: Before executing any `gcloud`, REST API, or MCP command, you
MUST read [Usage Attribution](references/attribution.md). For commands executed
directly in the terminal (never in generated scripts, Terraform, or user code),
prefix `gcloud` with
`CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:dbt-sf-to-bq-translator)"`
and append `agent-skills/1.0 (skill:dbt-sf-to-bq-translator)` to
`User-Agent` headers.

# 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://docs.cloud.google.com/sdk/docs/install-sdk.md.txt) if not already installed.
2.  **Authentication**: Authenticate your CLI session:
    ```bash
    gcloud auth login
    gcloud auth application-default login
    ```
3.  **Project Configuration**: Set your active Google Cloud project:
    ```bash
    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:
    ```bash
    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):
    ```bash
    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. Cloud Storage bucket name for staging translation assets.
  5. Google Cloud 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`:
  ```bash
  python3 scripts/bulk_translate_via_gcloud.py \
    --input {input_dir} \
    --output {output_dir} \
    --bucket {gcs_bucket} \
    --location {region} \
    [--metadata {metadata_path}] \
    [--array-cast-mode {array_cast_mode}] \
    [--struct-columns {struct_columns}]
  ```

  | Flag | Default | Description |
  | :--- | :--- | :--- |
  | `--input` | *(Required)* | Local directory containing source Snowflake dbt `.sql` and `.yml` files. |
  | `--output` | *(Required)* | Target directory for translated BigQuery dbt `.sql` and `.yml` files. |
  | `--bucket` | *(Required)* | Cloud Storage bucket name used to stage translation inputs and outputs. |
  | `--location` | `us` | Google Cloud region for BigQuery Translation Service execution (e.g., `us`, `eu`). |
  | `--metadata` | `None` | Optional path to a directory or `.zip` archive containing source `tables.csv` and `columns.csv`. |
  | `--array-cast-mode` | `array_string` | Target conversion for BQMS `ARRAY<JSON>` casts. Use `array_string` (`ARRAY<STRING>`) by default; use `string` (`STRING`), `json_extract_string_array`, or `preserve` as escape hatches when required by the source table schema. |
  | `--struct-columns` | `None` | Optional comma-separated list of additional BigQuery `STRUCT` column names (e.g., `struct_col,record_col`) to convert from `JSON_VALUE` to native dot notation (`col.field`). |

  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, Cloud Storage 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/STRUCT/ARRAY extraction sanitization, unimplemented opcode cleanup, 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_`
      - Normalize Snowflake bracket notation (`col['prop']` -> `col:prop`), `::variant:field`, and Jinja `{% else %}` / loop `UNION ALL` branches, eliminating Jinja curly braces (`{{ ... }}`) prior to translation so 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 Cloud Storage 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 Cloud Storage.
      - Upload staging SQL files to Cloud Storage:
        `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 Cloud Storage:
        `gcloud storage cp gs://[YOUR_BUCKET]/migration_output/*.sql <translated_output_dir>/`
  4.  **Restore Placeholders & Re-Embed dbt Logic**:
      - Take the translated BigQuery SQL files and perform advanced post-processing:
        * **AST-Based Config Transformation**: Parse the original `{{ config(...) }}` block, stripping Snowflake-specific parameters like `copy_grants`, `transient`, and `secure`. Sanitize hooks (`pre_hook` and `post_hook`) to remove invalid Snowflake commands like `ALTER ICEBERG TABLE ... REFRESH`, `UNSET SECURE`, or `COPY INTO`, while preserving valid ones.
        * **Reference Resolver (Namespace Resolution)**: Scan the SQL for hardcoded Snowflake database/schema table paths in FROM and JOIN clauses, and map them back to native dbt `{{ ref(...) }}` or `{{ source(...) }}` macros by resolving against discovered project models and sources.
        * **Macro & Syntax Audit**: Scan all `{{ ... }}` Jinja expressions and log warnings for any custom/non-allowlisted database-specific macros. Also audit these blocks for Snowflake-specific syntax (e.g. `::date`, `dateadd`, `to_date`) that may have been skipped or masked, listing warning comments directly in the file.
        * **Balanced SQL Edge-Cases Sanitization**: Convert date cast suffixes (`::date` -> `CAST(... AS DATE)`), datetime cast suffixes (`::timestamp` -> `CAST(... AS TIMESTAMP)`), float cast suffixes (`::float` -> `CAST(... AS FLOAT64)`), nested `dateadd(...)` calls, and intervals (`- interval '5 month'`) inside and outside control blocks using balanced-parentheses parsers. Fix displaced Jinja tags around CTEs and strip 16MB `substr(string(...), 1, 16777216)` wrappers.
        * **Array Type & STRUCT Access Normalization**: Convert BQMS untyped `ARRAY<JSON>` casts to `ARRAY<STRING>` (or the configured `--array-cast-mode` target), convert `json_value` on `STRUCT` columns to direct dot notation (`col.field`) and on `ARRAY<STRUCT>` columns to `col[SAFE_OFFSET(idx)].field`, and clean `error_unimplemented` opcodes.
        * **Copyright Header Placement**: Prepend the mandatory Google copyright header at the very top of the file above the config block.
  5.  **Write to the New BigQuery dbt File & Copy YAML Configurations**:
      - Save the newly assembled, BigQuery-compatible files preserving directory structure. Also, copy all `.yml`/`.yaml` files from the input directory to the output directory.

- Put a summary to the tasks file at the end.
- Before handing over, ask for user approval for the outcome. Apply necessary changes from the user.
- Mark your task is done in the tasks file.

# Mandates & Behavioral Rules

### 1. Mandatory File Header
Every translated file **MUST** start with the following exact header at the very top:
```sql
# Copyright 2026 Google. This software is provided as-is, without warranty or

From google/skills, Apache-2.0.

More from google/skills