dbt-migration-bigquery_skill

Overview

This skill converts Google BigQuery DDL (views, tables, stored procedures) into production-quality dbt models compatible with Snowflake. It preserves business logic while translating BigQuery syntax, types, and functions to Snowflake and generating schema.yml files with tests and documentation. The output includes config blocks, materialization recommendations, and inline comments for converted syntax.

How this skill works

Provide BigQuery DDL or SQL for the object you want migrated. The skill parses the DDL, maps data types and functions to Snowflake equivalents, replaces BigQuery constructs (UNNEST, backticks, STRUCT/ARRAY, SAFE_* functions) with Snowflake patterns, and emits one or more dbt model files plus a schema.yml with tests and docs. It also suggests materialization, incremental strategies, clustering keys, and flags places where procedural logic was refactored into modular dbt models.

When to use it

• Migrating BigQuery views, tables, or stored procedures to dbt models for Snowflake
• Translating BigQuery-specific SQL constructs (UNNEST, STRUCT, backticks) to Snowflake syntax
• Generating schema.yml with column descriptions and dbt tests from existing BigQuery DDL
• Refactoring procedural BigQuery logic into declarative dbt models and incremental jobs
• Validating that data types and functions are correctly mapped for Snowflake consumption

Best practices

• Prefer view materialization for lightweight transforms and table materialization for expensive or frequently queried results
• Break complex procedures into smaller models and use refs to document dependencies
• Cast columns with explicit precision/scale using ::TYPE (e.g., ::VARCHAR(100), ::NUMBER(18,2)) for deterministic schemas
• Add unique, not_null, and relationship tests in schema.yml to preserve data quality
• Use LATERAL FLATTEN for arrays and OBJECT_CONSTRUCT/PARSE_JSON for STRUCT/JSON conversions

Example use cases

• Convert a BigQuery view that uses UNNEST and ARRAY functions into a dbt view using LATERAL FLATTEN and explicit casts
• Transform a BigQuery stored procedure into a series of modular dbt models with incremental materialization
• Generate a dbt model and schema.yml for a hospital patient table, including primary key and relationships tests
• Translate BigQuery TIMESTAMP and DATETIME usage to Snowflake TIMESTAMP_TZ/TIMESTAMP_NTZ with explicit casts
• Produce inline comments in generated SQL highlighting where SAFE_CAST or backtick identifiers were converted