# Dbt Migration Bigquery > Transform Google BigQuery DDL (views, tables, stored procedures) into production-quality dbt models Use when this capability is needed. - Skill: `tomevault-io/dbt-migration-bigquery` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/dbt-migration-bigquery` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/dbt-migration-bigquery/raw - Safety review: pending (external: skill-scanner PASS, skillspector PASS) - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/dbt-migration-bigquery --- # BigQuery to dbt Model Conversion ## Purpose Transform Google BigQuery DDL (views, tables, stored procedures) into production-quality dbt models compatible with Snowflake, maintaining the same business logic and data transformation steps while following dbt best practices. ## When to Use This Skill Activate this skill when users ask about: - Converting BigQuery views or tables to dbt models - Migrating BigQuery stored procedures to dbt - Translating BigQuery SQL syntax to Snowflake - Generating schema.yml files with tests and documentation - Handling BigQuery-specific syntax conversions (UNNEST, STRUCT/ARRAY, backtick identifiers) --- ## Task Description You are a database engineer working for a hospital system. You need to convert BigQuery DDL to equivalent dbt code compatible with Snowflake, maintaining the same business logic and data transformation steps while following dbt best practices. ## Input Requirements I will provide you the BigQuery DDL to convert. ## Audience The code will be executed by data engineers who are learning Snowflake and dbt. ## Output Requirements Generate the following: 1. One or more dbt models with complete SQL for every column 2. A corresponding schema.yml file with appropriate tests and documentation 3. A config block with materialization strategy 4. Explanation of key changes and architectural decisions 5. Inline comments highlighting any syntax that was converted ## Conversion Guidelines ### General Principles - Replace procedural logic with declarative SQL where possible - Break down complex procedures into multiple modular dbt models - Implement appropriate incremental processing strategies - Maintain data quality checks through dbt tests - Use Snowflake SQL functions rather than macros whenever possible ### Sample Response Format ```sql -- dbt model: models/[domain]/[target_schema_name]/model_name.sql {{ config(materialized='view') }} /* Original Object: [project].[dataset].[object_name] Source Platform: BigQuery Purpose: [brief description] Conversion Notes: [key changes] Description: [SQL logic description] */ WITH source_data AS ( SELECT -- INT64 converted to INTEGER customer_id::INTEGER AS customer_id, -- STRING converted to VARCHAR customer_name::VARCHAR(100) AS customer_name, -- NUMERIC converted to NUMBER account_balance::NUMBER(18,2) AS account_balance, -- TIMESTAMP converted to TIMESTAMP_TZ (BigQuery stores UTC) created_date::TIMESTAMP_TZ AS created_date FROM {{ ref('upstream_model') }} ), transformed_data AS ( SELECT customer_id, UPPER(customer_name)::VARCHAR(100) AS customer_name_upper, account_balance, created_date, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS loaded_at FROM source_data ) SELECT customer_id, customer_name_upper, account_balance, created_date, loaded_at FROM transformed_data ``` ```yaml ## models/[domain]/[target_schema_name]/_models.yml version: 2 models: - name: model_name description: "Table description; converted from BigQuery [Original object name]" columns: - name: customer_id description: "Primary key - unique customer identifier" tests: - unique - not_null - name: customer_name_upper description: "Customer name in uppercase" - name: account_balance description: "Current account balance; Foreign key to OTHER_TABLE" tests: - relationships: to: ref('OTHER_TABLE') field: OTHER_TABLE_KEY - name: created_date description: "Date the customer record was created" - name: loaded_at description: "Timestamp when the record was loaded by dbt" ``` ```yaml ## dbt_project.yml (excerpt) models: my_project: +materialized: view domain_name: +schema: target_schema_name ``` ### Specific Translation Rules #### dbt Specific Requirements - If the source is a view, use a view materialization in dbt - Include appropriate dbt model configuration (materialization type) - Add documentation blocks for a schema.yml - Add descriptions for tables and columns - Include relevant tests - Define primary keys and relationships - Assume that upstream objects are models - Comprehensively provide all the columns in the output - Break complex procedures into multiple models if needed - Implement appropriate incremental strategies for large tables - Use Snowflake SQL functions rather than macros whenever possible - **Always cast columns with explicit precision/scale** using `::TYPE` syntax (e.g., `column_name::VARCHAR(100)`, `amount::NUMBER(18,2)`) to ensure output matches expected data types - **Always provide explicit column aliases** for clarity and documentation #### Performance Optimization - Suggest clustering keys if needed - Recommend materialization strategy (view vs table) - Identify potential performance improvements #### BigQuery to Snowflake Syntax Conversion - Convert backtick identifiers (\`project.dataset.table\`) to Snowflake format - Replace UNNEST with LATERAL FLATTEN - Convert STRUCT/ARRAY types to VARIANT/ARRAY - Translate SAFE*\* functions to TRY*\* equivalents - Convert IS TRUE/IS FALSE operators - Handle DATE/TIMESTAMP differences - Replace ARRAY_AGG with Snowflake equivalent - Convert BigQuery-specific window functions #### Key Data Type Mappings | BigQuery | Snowflake | Notes | | -------------------------- | ------------- | ----------------------------- | | INT64/INT/INTEGER/BIGINT | INT | Alias for NUMBER(38,0) | | SMALLINT/TINYINT/BYTEINT | Same | | | NUMERIC/DECIMAL/BIGNUMERIC | NUMERIC | BIGNUMERIC may lose precision | | FLOAT64 | FLOAT | | | BOOL/BOOLEAN | BOOLEAN | | | STRING | VARCHAR | | | BYTES | BINARY | | | DATE | DATE | | | TIME | TIME | | | DATETIME | TIMESTAMP_NTZ | | | TIMESTAMP | TIMESTAMP_TZ | BigQuery stores in UTC | | ARRAY | ARRAY | | | STRUCT | VARIANT | Use OBJECT_CONSTRUCT | | JSON | VARIANT | Use PARSE_JSON | | GEOGRAPHY | GEOGRAPHY | | | INTERVAL | VARCHAR | | #### Key Syntax Conversions ```sql -- Backtick identifiers → Double quotes `project.dataset.table` → "project"."dataset"."table" -- UNNEST → LATERAL FLATTEN SELECT * FROM table, UNNEST(array_col) AS elem → SELECT * FROM table, LATERAL FLATTEN(input => array_col) AS f -- STRUCT → OBJECT_CONSTRUCT STRUCT(1 AS a, 'x' AS b) → OBJECT_CONSTRUCT('a', 1, 'b', 'x') -- ARRAY access array_col[OFFSET(0)] → array_col[0] array_col[ORDINAL(1)] → array_col[0] -- SAFE_* functions → TRY_* or :: with TRY_ SAFE_CAST(x AS INT64) → TRY_TO_NUMBER(x)::INTEGER SAFE_CAST(x AS STRING) → x::VARCHAR -- regular cast when safe SAFE_DIVIDE(a, b) → a / NULLIF(b, 0) -- returns NULL on divide by zero -- IS TRUE/IS FALSE WHERE col IS TRUE → WHERE col = TRUE -- ARRAY_AGG ARRAY_AGG(col) → ARRAY_AGG(col) -- JSON functions JSON_VALUE(col, '$.key') → col:key::STRING ``` #### Common Function Mappings | BigQuery | Snowflake | Notes | | -------------------------- | ------------------------------------ | ------------------- | | `IF(cond, a, b)` | `IFF(cond, a, b)` | | | `IFNULL(a, b)` | `IFNULL(a, b)` | Same | | `COUNTIF(cond)` | `COUNT_IF(cond)` | | | `LOGICAL_AND(col)` | `BOOLAND_AGG(col)` | | | `LOGICAL_OR(col)` | `BOOLOR_AGG(col)` | | | `SAFE_CAST(x AS type)` | `TRY_CAST(x AS type)` | | | `ARRAY_CONCAT(a, b)` | `ARRAY_CAT(a, b)` | | | `ARRAY_LENGTH(arr)` | `ARRAY_SIZE(arr)` | | | `FORMAT_DATE(fmt, d)` | `TO_CHAR(d, fmt)` | Format codes differ | | `CURRENT_DATETIME()` | `CURRENT_TIMESTAMP()::TIMESTAMP_NTZ` | | | `JSON_VALUE(col, '$.key')` | `col:key::STRING` | Path syntax differs | | `JSON_EXTRACT_SCALAR(...)` | `JSON_EXTRACT_PATH_TEXT(...)` | | | `STARTS_WITH(str, prefix)` | `STARTSWITH(str, prefix)` | | | `ENDS_WITH(str, suffix)` | `ENDSWITH(str, suffix)` | | | `REGEXP_CONTAINS(val, re)` | `REGEXP_INSTR(val, re) > 0` | | | `TIMESTAMP_MILLIS(ms)` | `TO_TIMESTAMP(ms / 1000)` | | | `UNIX_MILLIS(ts)` | `DATE_PART('epoch_millisecond', ts)` | | | `ST_GEOGFROMTEXT(wkt)` | `ST_GEOGRAPHYFROMWKT(wkt)` | | | `ST_GEOGPOINT(lon, lat)` | `ST_POINT(lon, lat)` | | #### Dependencies - List any upstream dependencies - Suggest model organization in dbt project --- ## Validation Checklist - [] Every DDL statement has been accounted for in the dbt models - [] SQL in models is compatible with Snowflake - [] BigQuery-specific syntax converted (UNNEST, backticks, STRUCT/ARRAY) - [] All business logic preserved - [] All columns included in output - [] Data types correctly mapped - [] Functions translated to Snowflake equivalents - [] Materialization strategy selected - [] Tests added - [] SQL logic description complete - [] Table descriptions added - [] Column descriptions added - [] Dependencies correctly mapped - [] Incremental logic (if applicable) verified - [] Inline comments added for converted syntax --- ## Related Skills - $dbt-migration - For the complete migration workflow (discovery, planning, placeholder models, testing, deployment) - $dbt-modeling - For CTE patterns and SQL structure guidance - $dbt-testing - For implementing comprehensive dbt tests - $dbt-architecture - For project organization and folder structure - $dbt-materializations - For choosing materialization strategies (view, table, incremental, snapshots) - $dbt-performance - For clustering keys, warehouse sizing, and query optimization - $dbt-commands - For running dbt commands and model selection syntax - $dbt-core - For dbt installation, configuration, and package management - $snowflake-cli - For executing SQL and managing Snowflake objects --- ## Supported Source Database | Database | Key Considerations | | ------------------- | --------------------------------------------------------------------------------------------- | | **Google BigQuery** | UNNEST, STRUCT/ARRAY types, backtick identifiers, IS TRUE/FALSE operators, SAFE\_\* functions | ## Translation References Detailed syntax translation guides are available in the `translation-references/` folder. > **Copyright Notice:** The translation reference documentation in this repository is derived from > [Snowflake SnowConvert Documentation](https://docs.snowflake.com/en/migrations/snowconvert-docs) > and is © Copyright Snowflake Inc. All rights reserved. Used for reference purposes only. ### Reference Index - [Create Table](translation-references/bigquery-create-table.md) - [Create View](translation-references/bigquery-create-view.md) - [Data Types](translation-references/bigquery-data-types.md) - [Functions](translation-references/bigquery-functions.md) - [Identifiers](translation-references/bigquery-identifiers.md) - [Operators](translation-references/bigquery-operators.md) - [Overview (README)](translation-references/bigquery-readme.md) - [Subqueries](translation-references/bigquery-subqueries.md) --- > Converted and distributed by [TomeVault](https://tomevault.io/claim/sfc-gh-dflippo) — claim your Tome and manage your conversions.