Problem Statement
Simple: Migrating the Data Lake from Snowflake to BigQuery.
| As-Is | To-Be |
|—-|—-|
| SELECT orderid::STRING AS orderidstr FROM orders; | SELECT CAST(orderid AS STRING) AS orderidstr FROM orders; |
During the Snowflake-to-BigQuery migration, the task was not only to move terabytes of data across platforms, but to do so efficiently and cost-effectively. At the same time, refactor the data pipelines.
That’s where the real complexity surfaced. We were following an ELT approach for the data lake, which meant the extraction layer would remain unchanged. But the loading and transformation logic had to be refactored. The technology stack included Python for development, AWS for infrastructure, and Snowflake as the source data store, all of which now needed to align with BigQuery’s ecosystem.
Over time, the platform had accumulated extensive SQL—complex queries, layered views, and tightly coupled stored procedures. Translating this logic was not a simple syntax conversion; it required rethinking how transformations were executed, optimized, and governed in a fundamentally different data environment.
Evolution of pattern detection and replacement
Taking a step back and checking what I used to do and what the options are now.
| ~~Regular Expressions (Regex)~~ |
|—-|
| ~~String Matching Algorithms~~ |
| ~~Traditional NLP~~ |
| ==Use AI Gents== |
Given the scale and complexity of the migration comprising thousands of Database objects(Tables, Views, Tasks, Procedures), hundreds of SQL scripts, and hundreds of ad hoc queries, it is evident that leveraging large language models embedded within the IDE was the most practical and effective approach for refactoring and validating SQL. Over the past few years, Generative AI has matured significantly, so the next step was to decide whether the instruction should be framed simply as a direct task—
- “Refactor the Snowflake SQL into a BigQuery-compatible format”—or should it establish clearer context and expertise, such as
- “Given your expertise in translating Snowflake SQL to BigQuery, translate the following SQL”? Alternatively, would a more guided approach be more effective,
- “You are a domain expert in translating Snowflake SQL to BigQuery; and here are example of few translations; now translate the sql of file …”?
AGENTS.md Adoption
Introduction
The AGENTS.md file is a guide for AI agents, providing context and instructions to help them work on the project. It is an open-format file. Unlike the structured configurations found in `*.json` or `*.yml,` the md file is a "human-to-machine" plain-text README. It is designed to give the high-level context, tribal knowledge, and specific behavioral instructions that code alone cannot convey. So no syntax error, no compilation error, and many more data engineer nightmares.
Structure: Formatting & Hierarchy
A simple Markdown-style hierarchy is followed to parse the importance of different instructions:
# (Single Hash): Indicates the highest level of hierarchy (Project Vision or Major Sections).
- ## (Double Hash): Used for the second-highest level (Specific Modules or Workflows).
- ### (Triple Hash): Used for the third level and below (Detailed Tasks or Technical Nuances).
There are no specific constraints apart from above markers.
Sample File
Here is a sample Agents.md file. All the instructions and guidelines are in plain text.
The first line states the project vision and the expectations for AI Agents. The second line has instructions with details.
# Snowflake to BigQuery SQL Refactoring Guide
## Reusable Setup for Future Refactoring Tasks
This guide provides a systematic approach to refactoring Snowflake SQL to BigQuery SQL,
reusing the patterns and tools established for the Adobe API refactoring.
---
One of the biggest challenges in this migration was related to handling the JSON structure. There are multiple pipelines that dump JSON-type response payloads into Snowflake variant data types. Snowflake gracefully handles them or parses them with the LATERAL FLATTEN keyword. However same is not the case in BigQuery. The section below in the md file covers most JSON data types in BigQuery. So a combination of UNNEST and JSON PARSE did the trick. First, identify which JSON structures to transform. Based on that UNNEST part is derived.
## Quick Start Checklist
### Step 0: MANDATORY - Detect JSON Structure First (CRITICAL)**
⚠️ ALWAYS run this diagnostic query BEFORE refactoring to avoid zero-row issues:**
sql
— Comprehensive JSON Structure Diagnostic
— Run this for EACH table with JSON content BEFORE refactoring
SELECT
— Basic info
COUNT(*) as totalrows,
COUNTIF(content IS NOT NULL) as rowswithcontent,
COUNTIF(SAFE.PARSEJSON(content) IS NULL) as parsefailures,
— JSON type (CRITICAL – determines parsing approach)
JSONTYPE(PARSEJSON(ANYVALUE(content))) as jsontype,
— Array lengths for different paths (identifies correct path)
MAX(ARRAYLENGTH(JSONQUERYARRAY(PARSEJSON(content), ‘$’))) as directarraymax,
MAX(ARRAYLENGTH(JSONQUERYARRAY(PARSEJSON(content), ‘$.content’))) as contentarraymax,
MAX(ARRAYLENGTH(JSONQUERYARRAY(PARSEJSON(content), ‘$.data’))) as dataarraymax,
MAX(ARRAYLENGTH(JSONQUERYARRAY(PARSEJSON(content), ‘$.rows’))) as rowsarraymax,
— Sample preview (shows actual structure)
SUBSTR(ANYVALUE(content), 1, 200) as samplepreview,
— Check for double-quoted JSON (escaped JSON string)
CASE WHEN JSONTYPE(PARSEJSON(ANYVALUE(content))) = ‘OBJECT’
AND JSONTYPE(PARSEJSON(JSONVALUE(PARSEJSON(ANYVALUE(content)), ‘$.content’))) = ‘ARRAY’
THEN ‘YES – Double-quoted JSON detected’
WHEN JSONTYPE(PARSEJSON(ANYVALUE(content))) = ‘STRING’
THEN ‘YES – Needs double parsing’
ELSE ‘NO’ END as isdoublequoted_json
FROM {project_id}.{dataset}.{table_name};
``**Decision Tree Based on Diagnostic Results:jsontype=”ARRAY”
**1. **Ifanddirectarray_max > 0`:**
- Structure:
[{...}, {...}](direct array) - Use:
UNNEST(JSON_QUERY_ARRAY(PARSE_JSON(content), '$'))
- If
json_type="OBJECT"andcontent_array_max > 0:
- Structure:
{"content": [{...}, {...}]}(nested content) - Use:
UNNEST(JSON_QUERY_ARRAY(PARSE_JSON(content), '$.content'))
- If
json_type="OBJECT"androws_array_max > 0:
- Structure:
{"rows": [{...}, {...}]}(nested rows) - Use:
UNNEST(JSON_QUERY_ARRAY(PARSE_JSON(content), '$.rows'))
- If
is_double_quoted_json = 'YES':
- Structure:
{"content": "[{"id":"..."}]"}(escaped JSON string) - Use:
UNNEST(JSON_QUERY_ARRAY(PARSE_JSON(JSON_VALUE(PARSE_JSON(content), '$.content')), '$')) - This is the most common cause of zero-row issues!
- If
json_type="OBJECT"and all array_max are NULL:
- Structure: Single object
{...}(not array) - Use: Direct extraction without UNNEST:
JSON_VALUE(PARSE_JSON(content), '$.field')
Below are some common environment-based refactoring guidelines.
python
Step 1: Analyze the SQL File
- [ ] Identify all Snowflake-specific syntax:
LATERAL FLATTENpatterns::typecasting- Snowflake functions (
IFF,NVL,DECODE, etc.) USE WAREHOUSE,USE DATABASE,USE SCHEMADELETE FROM ... INSERT INTOpatternsQUALIFYclauses- [ ] List all tables involved (source and target)- [ ] Identify incremental load patterns
- [ ] Note special handling (NaN, NULL, date formats, etc.)
Pattern 1: Type Casting
| Snowflake | BigQuery |
|———–|———-|
| value:field::string | JSON_VALUE(item, '$.field') |
| value:field::float | CAST(JSON_VALUE(item, '$.field') AS FLOAT64) |
| value:field::int | CAST(JSON_VALUE(item, '$.field') AS INT64) |
| value:field::date | CAST(JSON_VALUE(item, '$.field') AS DATE) |
| value:field::timestamp_ntz | CAST(JSON_VALUE(item, '$.field') AS TIMESTAMP) |
| value:field::numeric(10,2) | ROUND(CAST(JSON_VALUE(item, '$.field') AS NUMERIC), 2) |
Pattern 2: DELETE + INSERT → TRUNCATE + INSERT
“`sql
— Snowflake
DELETE FROM {edw}.schema.table;
INSERT INTO {edw}.schema.table …
— BigQuery
TRUNCATE TABLE {{projectid}}.EDW_SCHEMA.table;
INSERT INTO {{projectid}}.EDW_SCHEMA.table …
Pattern 3: Function Conversions
| Snowflake | BigQuery |
|———–|———-|
| IFF(cond, a, b) | IF(cond, a, b) |
| NVL(expr, default) | COALESCE(expr, default) |
| IFNULL(expr, default) | COALESCE(expr, default) |
| DECODE(expr, s1, r1, default) | CASE expr WHEN s1 THEN r1 ELSE default END |
| split(str, '/')[1] | SPLIT(str, '/')[SAFE_OFFSET(1)] |
| YEAR(date) | EXTRACT(YEAR FROM date) |
| TO_DATE(str) | CAST(str AS DATE) or PARSE_DATE('%Y-%m-%d', str) |
| TO_CHAR(num) | CAST(num AS STRING) |
| CHARINDEX(substr, str) | STRPOS(str, substr) |
| EDITDISTANCE(str1, str2) | EDIT_DISTANCE(str1, str2) |
### Pattern 4: QUALIFY Clause
“`sql
— Snowflake
SELECT …
QUALIFY ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) = 1
— BigQuery
SELECT … FROM ( SELECT …,
ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) AS rn
FROM …
)
WHERE rn = 1
### Pattern 5: Date/Time Functions
sql
— Snowflake
DATEADD(day, -90, CURRENTDATE())
DATEDIFF(day, date1, date2)
TOTIMESTAMP(str, ‘YYYY-MM-DDTHH24:MI:SS’)
— BigQuery
DATESUB(CURRENTDATE, INTERVAL 90 DAY)
DATEDIFF(date2, date1, DAY)
PARSETIMESTAMP(‘%Y-%m-%dT%H:%M:%S’, str)
Finally, to end the md file, we can include validation of translated SQL. We can also ask the agent to connect BigQuery and do a dummy run.
python
Step 2: Validate
- [ ] Run validation queries
- [ ] Test each table individually
- [ ] Fix any issues found
- [ ] Document assumptions and changes
“`
Overall, this is how the md file is structured. Just keep it in the repository root directory and instruct the IDE agent to follow it. The IDE can also reference different MD files depending on where the MD files are placed.
Conclusion
Wrapping this write-up, I would say translating 100s of batch SQL scripts and ad hoc queries, and validating them, would be a daunting and time-intensive effort. The introduction of AI-assisted workflows, powered by AGENTS.md file, made it simpler. By codifying domain knowledge, refactoring patterns, and validation rules in a single, human-readable guide, AGENTS.md established a reliable baseline for consistency and correctness. This approach not only reduced manual effort but also enabled the management of complexity at scale, making it a key enabler of the successful transition to BigQuery and laying a strong foundation for the future platform.
References
- https://agents.md/
- https://docs.cloud.google.com/bigquery/docs
- https://docs.snowflake.com/en/
