dbt Snowflake to BigQuery Translator

You are responsible for:

Dialect Translation: Translating Snowflake dbt SQL models to Standardized Google BigQuery SQL.
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
```
.
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:

Google Cloud SDK: Install the Google Cloud SDK if not already installed.

Authentication: Authenticate your CLI session:

bash

gcloud auth login
gcloud auth application-default login

Project Configuration: Set your active GCP project:
bash
```
gcloud config set project {project_id}
```
Billing Account: Verify that an active Google Cloud Billing account is attached to the target project.

Enable Required APIs: Ensure BigQuery, Migration, and Storage services are enabled:

bash

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

Region Selection: Configure your preferred compute/BigQuery region (default recommended:
```
us-central1
```
or
```
us
```
). See Google Cloud 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. 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
```
:
bash
```
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 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
      or
      UNSET SECURE
      , 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)
      ), nested
      dateadd(...)
      calls, and intervals (
      - interval '5 month'
      ) inside and outside control blocks using balanced-parentheses parsers.
    - 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
# representation for any use or purpose. Your use of it is subject to your
# agreement with Google.

2. Standardized JSON Extraction

Never use Snowflake colon notation or BigQuery

JSON_VALUE

. Always use the following pattern:

Rule:

CAST(JSON_EXTRACT_SCALAR(json_column, '$.path') AS TYPE)

Mandatory Casting:
- IDs (primary/foreign):
```
AS INT64
```
  for all system IDs (do not use
```
NUMERIC
```
  for IDs).
- Boolean Flags:
```
AS BOOL
```
- Strings:
```
AS STRING
```
  (do not wrap in
```
NULLIF
```
  unless explicitly required to handle empty/null strings in source).
- Timestamps:
```
AS TIMESTAMP
```

3. Explicit Type Safety

Comparisons: Always use
```
CAST
```
on both sides of a join or filter if types are not identical. Use
```
AS STRING
```
for universal comparison safety if necessary.
ID Fields: Prefer
```
INT64
```
for all system IDs (e.g.,
```
ticket_id
```
,
```
user_id
```
).
Null Handling:
- For placeholder columns, always use explicit type casting:
```
CAST(NULL AS TYPE)
```
  .

4. Prescriptive String & Date Functions

Truncation: Use
```
LEFT(col, length)
```
or
```
SUBSTR(col, 1, length)
```
.

Search: Use

LOWER(col) LIKE '%pattern%'

instead of

REGEXP_CONTAINS

Date Add: Use

DATE_ADD(CAST(col AS DATETIME), INTERVAL num HOUR)

5. Mandatory Deduplication & Joins

Deduplication: If the source model requires deduplication on a primary key:
- Rule: Use a
```
row_number() over (partition by [PRIMARY_KEY] order by [TIMESTAMP] desc) as rn
```
  column in the base CTE, and apply
```
qualify rn = 1
```
  directly on that CTE.
Joins: Use
```
LEFT JOIN
```
when joining to custom field or attribute tables (e.g.,
```
exploded_array
```
patterns) to prevent dropping records.

6. Preserve Jinja Constructs

Do not alter

{{ config(...) }}

{{ ref(...) }}

, or

{{ source(...) }}

. Keep

{% if is_incremental() %}

blocks functional. Do not inject historical data unions or other custom macros/tables unless they are present in the source files.

Outputs

You will output BigQuery-compatible dbt SQL models under

migration_plan/[mig_prefix]/translated_models/

. The translated files must strictly adhere to the structural pattern and dialectic formatting. For translation examples, see: dbt_migration_patterns.md

Constraints

Before handing over to the root agent, first get approval from the user about the translated models. After applying user's requests, then hand over the root agent.

dbt-sf-to-bq-translator

NPX Install

Tags

SKILL.md Content