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:
- One or more dbt models with complete SQL for every column
- A corresponding schema.yml file with appropriate tests and documentation
- A config block with materialization strategy
- Explanation of key changes and architectural decisions
- 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
-- 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
# 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"
# 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
-- 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-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
and is © Copyright Snowflake Inc. All rights reserved. Used for reference purposes only.
Reference Index
| Folder |
Description |
| bigquery |
Subqueries to Snowflake |
1---2name: dbt-migration-bigquery-23description:4 Convert Google BigQuery DDL to dbt models compatible with Snowflake. This skill should be used5 when converting views, tables, or stored procedures from BigQuery to dbt code, generating6 schema.yml files with tests and documentation, or migrating BigQuery SQL to follow dbt best7 practices.8metadata:9 source: https://skillmd.com/skills/majiayu000/dbt-migration-bigquery-210---1112# BigQuery to dbt Model Conversion1314## Purpose1516Transform Google BigQuery DDL (views, tables, stored procedures) into production-quality dbt models17compatible with Snowflake, maintaining the same business logic and data transformation steps while18following dbt best practices.1920## When to Use This Skill2122Activate this skill when users ask about:2324- Converting BigQuery views or tables to dbt models25- Migrating BigQuery stored procedures to dbt26- Translating BigQuery SQL syntax to Snowflake27- Generating schema.yml files with tests and documentation28- Handling BigQuery-specific syntax conversions (UNNEST, STRUCT/ARRAY, backtick identifiers)2930---3132# Task Description3334You are a database engineer working for a hospital system. You need to convert BigQuery DDL to35equivalent dbt code compatible with Snowflake, maintaining the same business logic and data36transformation steps while following dbt best practices.3738# Input Requirements3940I will provide you the BigQuery DDL to convert.4142# Audience4344The code will be executed by data engineers who are learning Snowflake and dbt.4546# Output Requirements4748Generate the following:49501. One or more dbt models with complete SQL for every column512. A corresponding schema.yml file with appropriate tests and documentation523. A config block with materialization strategy534. Explanation of key changes and architectural decisions545. Inline comments highlighting any syntax that was converted5556# Conversion Guidelines5758## General Principles5960- Replace procedural logic with declarative SQL where possible61- Break down complex procedures into multiple modular dbt models62- Implement appropriate incremental processing strategies63- Maintain data quality checks through dbt tests64- Use Snowflake SQL functions rather than macros whenever possible6566## Sample Response Format6768```sql69-- dbt model: models/[domain]/[target_schema_name]/model_name.sql70{{ config(materialized='view') }}7172/* Original Object: [project].[dataset].[object_name]73 Source Platform: BigQuery74 Purpose: [brief description]75 Conversion Notes: [key changes]76 Description: [SQL logic description] */7778WITH source_data AS (79 SELECT80 -- INT64 converted to INTEGER81 customer_id::INTEGER AS customer_id,82 -- STRING converted to VARCHAR83 customer_name::VARCHAR(100) AS customer_name,84 -- NUMERIC converted to NUMBER85 account_balance::NUMBER(18,2) AS account_balance,86 -- TIMESTAMP converted to TIMESTAMP_TZ (BigQuery stores UTC)87 created_date::TIMESTAMP_TZ AS created_date88 FROM {{ ref('upstream_model') }}89),9091transformed_data AS (92 SELECT93 customer_id,94 UPPER(customer_name)::VARCHAR(100) AS customer_name_upper,95 account_balance,96 created_date,97 CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS loaded_at98 FROM source_data99)100101SELECT102 customer_id,103 customer_name_upper,104 account_balance,105 created_date,106 loaded_at107FROM transformed_data108```109110```yaml111# models/[domain]/[target_schema_name]/_models.yml112version: 2113114models:115 - name: model_name116 description: "Table description; converted from BigQuery [Original object name]"117 columns:118 - name: customer_id119 description: "Primary key - unique customer identifier"120 tests:121 - unique122 - not_null123 - name: customer_name_upper124 description: "Customer name in uppercase"125 - name: account_balance126 description: "Current account balance; Foreign key to OTHER_TABLE"127 tests:128 - relationships:129 to: ref('OTHER_TABLE')130 field: OTHER_TABLE_KEY131 - name: created_date132 description: "Date the customer record was created"133 - name: loaded_at134 description: "Timestamp when the record was loaded by dbt"135```136137```yaml138# dbt_project.yml (excerpt)139models:140 my_project:141 +materialized: view142 domain_name:143 +schema: target_schema_name144```145146## Specific Translation Rules147148### dbt Specific Requirements:149150- If the source is a view, use a view materialization in dbt151- Include appropriate dbt model configuration (materialization type)152- Add documentation blocks for a schema.yml153- Add descriptions for tables and columns154- Include relevant tests155- Define primary keys and relationships156- Assume that upstream objects are models157- Comprehensively provide all the columns in the output158- Break complex procedures into multiple models if needed159- Implement appropriate incremental strategies for large tables160- Use Snowflake SQL functions rather than macros whenever possible161- **Always cast columns with explicit precision/scale** using `::TYPE` syntax (e.g.,162 `column_name::VARCHAR(100)`, `amount::NUMBER(18,2)`) to ensure output matches expected data types163- **Always provide explicit column aliases** for clarity and documentation164165### Performance Optimization:166167- Suggest clustering keys if needed168- Recommend materialization strategy (view vs table)169- Identify potential performance improvements170171### BigQuery to Snowflake Syntax Conversion:172173- Convert backtick identifiers (\`project.dataset.table\`) to Snowflake format174- Replace UNNEST with LATERAL FLATTEN175- Convert STRUCT/ARRAY types to VARIANT/ARRAY176- Translate SAFE*\* functions to TRY*\* equivalents177- Convert IS TRUE/IS FALSE operators178- Handle DATE/TIMESTAMP differences179- Replace ARRAY_AGG with Snowflake equivalent180- Convert BigQuery-specific window functions181182### Key Data Type Mappings183184| BigQuery | Snowflake | Notes |185| -------------------------- | ------------- | ----------------------------- |186| INT64/INT/INTEGER/BIGINT | INT | Alias for NUMBER(38,0) |187| SMALLINT/TINYINT/BYTEINT | Same | |188| NUMERIC/DECIMAL/BIGNUMERIC | NUMERIC | BIGNUMERIC may lose precision |189| FLOAT64 | FLOAT | |190| BOOL/BOOLEAN | BOOLEAN | |191| STRING | VARCHAR | |192| BYTES | BINARY | |193| DATE | DATE | |194| TIME | TIME | |195| DATETIME | TIMESTAMP_NTZ | |196| TIMESTAMP | TIMESTAMP_TZ | BigQuery stores in UTC |197| ARRAY<T> | ARRAY | |198| STRUCT | VARIANT | Use OBJECT_CONSTRUCT |199| JSON | VARIANT | Use PARSE_JSON |200| GEOGRAPHY | GEOGRAPHY | |201| INTERVAL | VARCHAR | |202203### Key Syntax Conversions204205```sql206-- Backtick identifiers → Double quotes207\`project.dataset.table\` → "project"."dataset"."table"208209-- UNNEST → LATERAL FLATTEN210SELECT * FROM table, UNNEST(array_col) AS elem →211SELECT * FROM table, LATERAL FLATTEN(input => array_col) AS f212213-- STRUCT → OBJECT_CONSTRUCT214STRUCT(1 AS a, 'x' AS b) → OBJECT_CONSTRUCT('a', 1, 'b', 'x')215216-- ARRAY access217array_col[OFFSET(0)] → array_col[0]218array_col[ORDINAL(1)] → array_col[0]219220-- SAFE_* functions → TRY_* or :: with TRY_221SAFE_CAST(x AS INT64) → TRY_TO_NUMBER(x)::INTEGER222SAFE_CAST(x AS STRING) → x::VARCHAR -- regular cast when safe223SAFE_DIVIDE(a, b) → a / NULLIF(b, 0) -- returns NULL on divide by zero224225-- IS TRUE/IS FALSE226WHERE col IS TRUE → WHERE col = TRUE227228-- ARRAY_AGG229ARRAY_AGG(col) → ARRAY_AGG(col)230231-- JSON functions232JSON_VALUE(col, '$.key') → col:key::STRING233```234235### Common Function Mappings236237| BigQuery | Snowflake | Notes |238| -------------------------- | ------------------------------------ | ------------------- |239| `IF(cond, a, b)` | `IFF(cond, a, b)` | |240| `IFNULL(a, b)` | `IFNULL(a, b)` | Same |241| `COUNTIF(cond)` | `COUNT_IF(cond)` | |242| `LOGICAL_AND(col)` | `BOOLAND_AGG(col)` | |243| `LOGICAL_OR(col)` | `BOOLOR_AGG(col)` | |244| `SAFE_CAST(x AS type)` | `TRY_CAST(x AS type)` | |245| `ARRAY_CONCAT(a, b)` | `ARRAY_CAT(a, b)` | |246| `ARRAY_LENGTH(arr)` | `ARRAY_SIZE(arr)` | |247| `FORMAT_DATE(fmt, d)` | `TO_CHAR(d, fmt)` | Format codes differ |248| `CURRENT_DATETIME()` | `CURRENT_TIMESTAMP()::TIMESTAMP_NTZ` | |249| `JSON_VALUE(col, '$.key')` | `col:key::STRING` | Path syntax differs |250| `JSON_EXTRACT_SCALAR(...)` | `JSON_EXTRACT_PATH_TEXT(...)` | |251| `STARTS_WITH(str, prefix)` | `STARTSWITH(str, prefix)` | |252| `ENDS_WITH(str, suffix)` | `ENDSWITH(str, suffix)` | |253| `REGEXP_CONTAINS(val, re)` | `REGEXP_INSTR(val, re) > 0` | |254| `TIMESTAMP_MILLIS(ms)` | `TO_TIMESTAMP(ms / 1000)` | |255| `UNIX_MILLIS(ts)` | `DATE_PART('epoch_millisecond', ts)` | |256| `ST_GEOGFROMTEXT(wkt)` | `ST_GEOGRAPHYFROMWKT(wkt)` | |257| `ST_GEOGPOINT(lon, lat)` | `ST_POINT(lon, lat)` | |258259### Dependencies:260261- List any upstream dependencies262- Suggest model organization in dbt project263264---265266# Validation Checklist267268- [] Every DDL statement has been accounted for in the dbt models269- [] SQL in models is compatible with Snowflake270- [] BigQuery-specific syntax converted (UNNEST, backticks, STRUCT/ARRAY)271- [] All business logic preserved272- [] All columns included in output273- [] Data types correctly mapped274- [] Functions translated to Snowflake equivalents275- [] Materialization strategy selected276- [] Tests added277- [] SQL logic description complete278- [] Table descriptions added279- [] Column descriptions added280- [] Dependencies correctly mapped281- [] Incremental logic (if applicable) verified282- [] Inline comments added for converted syntax283284---285286## Related Skills287288- **dbt-modeling**: For CTE patterns and SQL structure guidance289- **dbt-testing**: For implementing comprehensive dbt tests290- **dbt-architecture**: For project organization and folder structure291- **dbt-materializations**: For choosing materialization strategies (view, table, incremental,292 snapshots)293- **dbt-performance**: For clustering keys, warehouse sizing, and query optimization294- **dbt-commands**: For running dbt commands and model selection syntax295- **dbt-core**: For dbt installation, configuration, and package management296- **snowflake-cli**: For executing SQL and managing Snowflake objects297298---299300## Supported Source Database301302<!-- prettier-ignore -->303| Database | Key Considerations |304|---|---|305| **Google BigQuery** | UNNEST, STRUCT/ARRAY types, backtick identifiers, IS TRUE/FALSE operators, SAFE\_\* functions |306307## Translation References308309Detailed syntax translation guides are available in the `translation-references/` folder.310311> **Copyright Notice:** The translation reference documentation in this repository is derived from312> [Snowflake SnowConvert Documentation](https://docs.snowflake.com/en/migrations/snowconvert-docs)313> and is © Copyright Snowflake Inc. All rights reserved. Used for reference purposes only.314315### Reference Index316317<!-- prettier-ignore -->318| Folder | Description |319|---|---|320| bigquery | [Subqueries to Snowflake](translation-references/bigquery/subqueries.md) |