bigquery-migration-data-exporter
Generates type-safe, schema-aligned database data export SQL files (.sql) for Snowflake, Databricks, Teradata, Oracle, and PostgreSQL directly from BigQuery Migration Service (BQMS) translation_output/metadata configs, exporting into target file formats (such as Parquet). Use when generating source database export SQL scripts for BigQuery ingestion without connecting to the source database. Don't use for executing SQL against source databases or streaming/staging exported files, extracting source database catalog metadata, translating SQL queries or DDL schemas (use bigquery-migration-sql-tran
Try your first task
A source-grounded starter task has not been verified for this skill yet.
SKILL.md
name: bigquery-migration-data-exportermetadata: version: "1.0.0" category: BigDataAndAnalytics publisher: googledescription: >- Generates type-safe, schema-aligned database data export SQL files (.sql) for Snowflake, Databricks, Teradata, Oracle, and PostgreSQL directly from BigQuery Migration Service (BQMS) translation_output/metadata configs, exporting into target file formats (such as Parquet). Use when generating source database export SQL scripts for BigQuery ingestion without connecting to the source database. Don't use for executing SQL against source databases or streaming/staging exported files, extracting source database catalog metadata, translating SQL queries or DDL schemas (use bigquery-migration-sql-translator), loading staged Parquet files into BigQuery, or running post-migration data validation.BigQuery Migration Data Exporter
This skill coordinates the generation of type-aware data export SQL scripts
(.sql files) for source data warehouses (such as Snowflake, Databricks,
Teradata, Oracle, and PostgreSQL) into target file formats (such as
BigQuery-aligned Parquet files), guided by dialect and format specifications
in the references/ directory.
All templates in this skill are pure SQL generators with zero JDBC or database connector dependencies.
[!TIP] Point directly to the BigQuery Migration Service (BQMS)
translation_output/metadatadirectory (local path or Cloud Storage URI). It automatically discovers all physical tables and target BigQuery logical types frommetadata/config/*.yaml.
Usage Attribution
MUST READ: Before executing any gcloud, REST API, or MCP command, you
MUST read and follow the instructions in
Usage Attribution.
Before you begin
-
Google Cloud CLI & Project Setup: When reading translation outputs or metadata archives from
gs://Cloud Storage URIs, ensure the Google Cloud CLI is installed, verify that your Google Cloud project has an active Cloud Billing account attached, and configure the target project:gcloud config set project {project_id} -
Enable the API: When reading metadata from Cloud Storage (
gs://), ask the user before enabling APIs, then enable the Cloud Storage API (storage.googleapis.com):gcloud services enable storage.googleapis.com -
Authentication & Required Identity and Access Management (IAM) Roles: Authenticate with
gcloud auth loginandgcloud auth application-default login(or setGOOGLE_APPLICATION_CREDENTIALSfor service account authentication). Verify that your identity or service account hasroles/storage.objectViewer(Storage Object Viewer) on any Cloud Storage bucket holding input metadata. Creating a staging bucket requiresroles/storage.admin(Storage Admin) on the Google Cloud project, and deleting staged files requiresroles/storage.objectAdmin(Storage Object Admin) on the staging bucket. -
Python Environment: The generator templates require Python 3.9 or later. Ask the user for confirmation before installing Python packages, then install PyYAML in a virtual environment (generator templates have zero JDBC or database connector dependencies):
pip install pyyaml==6.0.2 -
Region, Cost & Cleanup: Ask the user for confirmation before creating a new Cloud Storage bucket for staging exported Parquet files (
gcloud storage buckets create gs://{bucket} --location={region}), and default tous-central1unless your target BigQuery dataset uses another region (co-locate buckets with your dataset; see Google Cloud locations). Review Google Cloud Pricing before staging large exports. After BigQuery ingestion, temporary staged Parquet prefixes can be removed withgcloud storage rm -r gs://{bucket}/{export_prefix}. This deletion is permanent: run it only after the user explicitly confirms the exact URI, and never with an empty{export_prefix}(which would delete every object in the bucket). -
Snowflake Storage Integration: To unload Snowflake tables directly to a private Cloud Storage bucket (
--stage-path 'gcs://...'), Snowflake requires a storage integration that grants its service account access to the bucket. Ask the user for the integration name and pass it with the--storage-integrationflag, which adds aSTORAGE_INTEGRATIONclause to each generatedCOPY INTOstatement. Unloads to a Snowflake stage (for example,@~/bqms_export/or a named external stage) don't need this flag.
1. Direct Consumption of Translation Output Metadata
bigquery-migration-data-exporter consumes the native BigQuery Migration
Service (BQMS) metadata translation output:
translation_output/metadata/config/<database>/<schema>/<table_name>.yaml
Each configuration file specifies:
sourceName: Fully qualified source table name.columns: Source data types, precision, scale, and BigQuery target types.- Table versus view: A config with neither
externalTableNamenorexternalTableDdlPathdescribes a view, which is recreated as a SQL view and isn't exported. The Databricks generator also honors an explicitTableTypefield when present.
2. Format-Guided Type-Safe Export Fundamentals
A plain unguided COPY INTO @stage FROM table or SELECT * often fails during
BigQuery ingestion (bq load) due to serializer discrepancies (for example,
VARIANT JSON encoding, CHAR(n) fixed-width space padding, unbounded
Teradata/Oracle/PostgreSQL NUMBER/NUMERIC scale truncation, PostgreSQL
MONEY locale currency formatting, Oracle DATE time-of-day components, or
timestamp nanosecond ticks).
To ensure zero type drift and clean BigQuery ingestion, apply type-aware
projections guided by the target format and dialect specifications in the
references/ directory. While Parquet is the standard and recommended
format for BigQuery batch ingestion due to columnar compression and strict
typing, the export structure adapts based on target format requirements.
For Parquet data exports, the core rules are summarized below; the mapping references listed after them hold the full per-dialect type matrices:
-
Session Parameters (Snowflake):
ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF6'; ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6'; ALTER SESSION SET TIMESTAMP_TZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6 TZHTZM'; -
Semi-Structured / JSON (
VARIANT,OBJECT,STRUCT,MAP,ARRAY,JSON,JSONB,XMLTYPE,XML,UUID,INET):- Snowflake:
TO_VARCHAR(col) AS col. - Databricks / Spark SQL:
TO_JSON(col) AS colforMAP(and forSTRUCT/ARRAYwhen targetingJSONorSTRING; native passthroughcolwhen targetingRECORD/ARRAY);CAST(col AS STRING) AS colforVARIANT/JSON/INTERVAL. - Teradata:
CAST(col AS CLOB) AS col. - Oracle:
JSON_SERIALIZE(col RETURNING CLOB) AS colfor 21c+ nativeJSON, andXMLSERIALIZE(CONTENT col AS CLOB) AS colforXMLTYPE. - PostgreSQL:
CAST(col AS TEXT) AS colforJSON,JSONB,XML,UUID,INET,CIDR,MACADDR,INTERVAL,BIT, andARRAY.
- Snowflake:
-
Decimals, Numbers, Integers & Floats:
NUMBER(p, s)/DECIMAL(p, s)/NUMERIC(p, s)withs > 0→CAST(col AS DECIMAL(p, s)) AS col(orCAST(col AS NUMBER(p, s)) AS colin Oracle, orCAST(col AS NUMERIC(p, s)) AS colin PostgreSQL).- Unbounded or unconstrained
NUMBER/NUMBER(*)/NUMERIC/DECIMAL→CAST(col AS DECIMAL(38, 9)) AS colin Snowflake and Teradata,CAST(col AS NUMBER(38, 9)) AS colin Oracle, orCAST(col AS NUMERIC(38, 9)) AS colin PostgreSQL (supports<= 29integer digits and9fractional digits by default; override scale in Teradata, Oracle, and PostgreSQL by using--unbounded-number-scale); PostgreSQLMONEY→CAST(col AS NUMERIC(38, 2)) AS col. TINYINT/BYTEINT/SMALLINT/INT/INTEGER/BIGINT/SERIAL→CAST(col AS BIGINT) AS col. In Oracle,SMALLINT→CAST(col AS NUMBER(19, 0)) AS coland the ANSINUMBER(38)aliasesINT/INTEGER→CAST(col AS NUMBER(38, 0)) AS col. Scale-0 decimals with aTargetTypeofINT64project as 64-bit integers; the mapping references list whichNUMBER(p, 0)/DECIMAL(p, 0)precisions become 64-bit integers by default.- Databricks / Snowflake
FLOAT/REAL→CAST(col AS DOUBLE) AS col; PostgreSQLREAL/DOUBLE PRECISION→CAST(col AS DOUBLE PRECISION) AS col; OracleBINARY_FLOAT/BINARY_DOUBLE/FLOAT(p)→CAST(col AS BINARY_DOUBLE) AS col(widens to 64-bit IEEE-754FLOAT64).
-
Temporal (
DATE,TIME,TIMESTAMP,TIMESTAMP_NTZ,TIMESTAMP WITH TIME ZONE):- Snowflake:
CAST(col AS TIMESTAMP_NTZ(6)) AS colandCAST(CONVERT_TIMEZONE('UTC', col) AS TIMESTAMP_NTZ(6)) AS col. - Databricks / Spark SQL:
CAST(col AS TIMESTAMP) AS colwhen targetingTIMESTAMP, or leaveTIMESTAMP_NTZuncast when targeting BigQueryDATETIME.TIMESTAMP_NTZtoTIMESTAMPcasts (and back) use the session time zone, so run the scripts in a UTC session (see the Databricks reference). - Teradata:
CAST(col AS TIMESTAMP(6)) AS col,CAST((col AT TIME ZONE 'GMT') AS TIMESTAMP(6)) AS col, andCAST((col AT TIME ZONE 'GMT') AS TIME(6)) AS col. - Oracle:
CAST(col AS TIMESTAMP(6)) AS colforDATEandTIMESTAMP(p)(orTRUNC(col) AS colwhenDATEexplicitly targets BigQueryDATE),CAST(SYS_EXTRACT_UTC(col) AS TIMESTAMP(6)) AS colforTIMESTAMP WITH TIME ZONE, andCAST(SYS_EXTRACT_UTC(CAST(col AS TIMESTAMP WITH TIME ZONE)) AS TIMESTAMP(6)) AS colforTIMESTAMP WITH LOCAL TIME ZONE(UTC, whatever the session time zone). - PostgreSQL:
CAST(col AS TIME(6)) AS colforTIMEandTIMETZ,CAST(col AS TIMESTAMP(6) WITHOUT TIME ZONE) AS colforTIMESTAMP [WITHOUT TIME ZONE], andCAST(col AS TIMESTAMP(6) WITH TIME ZONE) AS colforTIMESTAMPTZ/TIMESTAMP WITH TIME ZONE.
- Snowflake:
-
Fixed-Width Character Padding (
CHAR(n)/NCHAR(n)/BPCHAR/GRAPHIC(n)):TRIM(TRAILING FROM col) AS col(Teradata) orRTRIM(col) AS col(Oracle and PostgreSQL) to strip fixed-width trailing pad spaces. -
Explicit Target Types: A BigQuery
TargetTypefrom the BQMS config (for example a schema override toINT64,NUMERIC,DATE, orTIMESTAMP) wins over the source-type defaults above so the Parquet column matches the BigQuery DDL. Cross-type overrides that a template doesn't convert keep the default projection:STRING,JSON, andBYTESin Oracle and Teradata (text columns stay text, binary columns stay bytes, and numeric or datetime columns keep their type);BOOLin Teradata and Oracle;TIMEin Oracle and Databricks;DATEon Oracle columns other thanDATE/TIMESTAMP; and targets other than numeric orSTRINGon DatabricksDECIMALcolumns. Ifbq loadreports a type mismatch, edit the template's# CUSTOMIZE: Custom Type Projectionshook.
[!IMPORTANT] Always consult the
references/directory (Snowflake Parquet mapping reference, Databricks and Spark SQL Parquet mapping reference, Teradata Parquet mapping reference, Oracle Parquet mapping reference, and PostgreSQL Parquet mapping reference) to verify type conversions, formatting options, and dialect-specific export syntax.
3. Asset Templates & Agent Workflow
This skill provides pure-Python SQL generator asset templates in assets/ (with
no JDBC or warehouse driver dependencies):
- Snowflake export SQL generator template
(
assets/export_snowflake_tables_template.py) - Databricks export SQL generator template
(
assets/export_databricks_tables_template.py) - Teradata export SQL generator template
(
assets/export_teradata_tables_template.py) - Oracle export SQL generator template
(
assets/export_oracle_tables_template.py) - PostgreSQL export SQL generator template
(
assets/export_postgres_tables_template.py)
Agent Workflow: Copy, Conditionally Customize, and Generate SQL
-
Copy the Asset Template: Copy
assets/<template_name>_template.pyfrom this skill's directory (<SKILL_DIR>) into your working directory (copy the file; do not paste or re-type the template contents into the response).<SKILL_DIR>is the directory containing thisSKILL.md:# For Snowflake: cp <SKILL_DIR>/assets/export_snowflake_tables_template.py ./export_snowflake_tables.py # For Databricks: cp <SKILL_DIR>/assets/export_databricks_tables_template.py ./export_databricks_tables.py # For Teradata: cp <SKILL_DIR>/assets/export_teradata_tables_template.py ./export_teradata_tables.py # For Oracle: cp <SKILL_DIR>/assets/export_oracle_tables_template.py ./export_oracle_tables.py # For PostgreSQL: cp <SKILL_DIR>/assets/export_postgres_tables_template.py ./export_postgres_tables.py -
Conditionally Customize Extension Points: Run the copy as-is. Modify it only if one of the following applies (prefer editing the marked
# CUSTOMIZE:sections;# DEFAULT SCAFFOLDINGsections can be kept unchanged unless deeper orchestration adjustments are needed):- Custom Type Projections (
# CUSTOMIZE: Custom Type Projections): Adjustformat_column_cast()inexport_snowflake_tables.py,export_databricks_tables.py,export_teradata_tables.py,export_oracle_tables.py, orexport_postgres_tables.pyfor non-standard data types, custom precision bounds, or special JSON/geography serialization formats. - Stage & Storage Configuration (
# CUSTOMIZE: Stage & Storage Configuration): CustomizeCOPY INTOfile format options (MAX_FILE_SIZE, compression codecs, or encryption flags) in_build_copy_into_query()or Spark SQLINSERT OVERWRITE DIRECTORYoptions in_build_insert_overwrite_query(). Pass Snowflake storage integration names with the--storage-integrationflag instead of editing the template. - Table & Row Filtering (
# CUSTOMIZE: Table & Row Filtering/# CUSTOMIZE: Row Filters): Add customWHEREclauses, partition predicates, or custom schema/table inclusion/exclusion filters in_filter_tables_to_export(),_build_select_query(), orload_tables_from_metadata_path().
- Custom Type Projections (
-
Generate the Export SQL Files: Run the copied script with
python3(python3 ./export_snowflake_tables.py,python3 ./export_databricks_tables.py,python3 ./export_teradata_tables.py,python3 ./export_oracle_tables.py, orpython3 ./export_postgres_tables.py) to output a unified.sqlscript (--output-sql) and/or per-table*_export.sqlscripts (--output-dir). -
Check the Output: Confirm the script exited with status
0, report anyWARNING:lines from stderr (such as skipped duplicate tables), and check that the number of-- Unload Table:markers (or*_export.sqlfiles) matches the tables you expect to export.
A. Snowflake Export SQL Generator (./export_snowflake_tables.py)
1. Generate Unified Export SQL Script (--output-sql)
Unloading directly to Cloud Storage (gcs://) requires a Snowflake storage
integration (see Before you begin):
python3 ./export_snowflake_tables.py \
--translation-metadata-dir gs://my-bucket/translation_output/metadata/ \
--stage-path 'gcs://my-bucket/data_unload/' \
--storage-integration my_gcs_integration \
--output-sql export_snowflake_to_parquet.sql
2. Generate Per-Table Export SQL Files (--output-dir)
python3 ./export_snowflake_tables.py \
--translation-metadata-dir ./translated_output/metadata \
--stage-path '@~/bqms_export/' \
--output-dir ./export_sql
When the tables span more than one Snowflake database (tables whose metadata
omits the database count as --default-database), the generator
prefixes each unload subpath, -- Unload Table: label, and per-table file name
with the database (for example, @~/bqms_export/<DB>/<SCHEMA>/<TABLE>/) so that
identically named <SCHEMA>.<TABLE> pairs don't overwrite each other.
Snowflake resolves unquoted identifiers to uppercase, so names that aren't
uppercase are double-quoted to keep their exact case. Pass --default-database
and --default-schema exactly as the catalog stores them (usually uppercase,
such as ANALYTICS).
B. Databricks Export SQL Generator (./export_databricks_tables.py)
Generates catalog-agnostic 2-part <schema>.<table> Spark SQL INSERT OVERWRITE DIRECTORY ... USING PARQUET (or standalone SELECT by using --export-mode select) scripts (.sql) with explicit type-safe projections for all discovered
Delta / Spark tables. Leave --catalog unset so the generated .sql files work
across both Unity Catalog and hive_metastore (activated by using USE CATALOG
on the target Spark or Databricks session at execution time), or pass --catalog <CATALOG> for explicit 3-part table qualification. --catalog also exports
only the tables of that catalog when the metadata names catalogs (3-part YAML
sourceName values or the extractor metadata.zip); the generator exits with
an error that lists the available catalogs when it matches none of them:
python3 ./export_databricks_tables.py \
--translation-metadata-dir ./translated_output/metadata \
--stage-path '/tmp/databricks_export' \
--output-sql export_databricks_to_parquet.sql \
--output-dir ./export_sql
C. Teradata Export SQL Generator (./export_teradata_tables.py)
Generates explicit-cast Teradata SELECT scripts (.sql) with -- Unload Table: <DB>.<TABLE> -> Parquet and -- Required Columns: markers consumed by
downstream Python Parquet streaming scripts (for example, by using teradatasql
and pyarrow). Use --default-database <DB> as a fallback qualifier when
metadata omits TableSchema (defaults to $TERADATA_DATABASE), --database <DB> (repeatable) as an allow-list filter to export only specific databases
(the generator exits with an error when it leaves no tables),
--unbounded-number-scale <S> to adjust the fractional scale for unbounded
NUMBER / NUMBER(*) columns (default 9), and --keep-char-padding if
fixed-width CHAR(n) trailing spaces must be preserved instead of trimmed:
# 1. Generate per-table Teradata export SQL files from BQMS translation output:
python3 ./export_teradata_tables.py \
--translation-metadata-dir ./translated_output/metadata \
--default-database my_db \
--output-dir ./export_sql
# 2. Generate a unified export SQL script from a Teradata extractor metadata.zip:
python3 ./export_teradata_tables.py \
--metadata-zip gs://my-bucket/teradata_metadata/dwh-migration-teradata-metadata.zip \
--database my_db \
--output-sql teradata_export.sql
D. Oracle Export SQL Generator (./export_oracle_tables.py)
Generates explicit-cast Oracle SELECT scripts (.sql) with -- Unload Table: <SCHEMA>.<TABLE> -> Parquet and -- Required Columns: markers consumed by
downstream Python Parquet streaming scripts (for example, by using
python-oracledb and pyarrow). Use --default-schema <SCHEMA> as a fallback
qualifier when metadata omits TableSchema (defaults to $ORACLE_SCHEMA),
--schema <SCHEMA> (repeatable) as an allow-list filter to export only specific
schemas, --unbounded-number-scale <S> to adjust the fractional scale for
unconstrained NUMBER columns (default 9), and --keep-char-padding if
fixed-width CHAR(n) / NCHAR(n) trailing spaces must be preserved instead of
trimmed. Identifiers that aren't uppercase are double-quoted to keep their exact
case, so pass --default-schema exactly as the catalog stores it (usually
uppercase, such as HR):
# 1. Generate per-table Oracle export SQL files from BQMS translation output:
python3 ./export_oracle_tables.py \
--translation-metadata-dir ./translated_output/metadata \
--default-schema MY_SCHEMA \
--output-dir ./export_sql
# 2. Generate a unified export SQL script from an Oracle extractor metadata.zip:
python3 ./export_oracle_tables.py \
--metadata-zip gs://my-bucket/oracle_metadata/dwh-migration-oracle-metadata.zip \
--schema MY_SCHEMA \
--output-sql oracle_export.sql
E. PostgreSQL Export SQL Generator (./export_postgres_tables.py)
Generates explicit-cast PostgreSQL SELECT scripts (.sql) with -- Unload Table: <SCHEMA>.<TABLE> -> Parquet and -- Required Columns: markers consumed
by downstream Python Parquet streaming scripts (for example, by using psycopg2
and pyarrow). Use --default-schema <SCHEMA> as a fallback qualifier when
metadata omits TableSchema (defaults to $PG_SCHEMA), --schema <SCHEMA>
(repeatable) as an allow-list filter to export only specific schemas,
--database <DB> (repeatable) to keep only one database's tables when the
metadata spans several PostgreSQL databases (the generator exits with an error
when --schema / --database leave no tables),
--unbounded-number-scale <S> to adjust the fractional scale for unconstrained
NUMERIC columns (default 9), and --keep-char-padding if fixed-width
CHAR(n) / BPCHAR trailing spaces must be preserved instead of trimmed.
Identifiers that aren't lowercase are double-quoted to keep their exact case,
so pass --default-schema exactly as the catalog stores it (usually lowercase,
such as public). BQMS YAML configs record PostgreSQL NUMERIC columns as a
NUMERIC(18, 0) placeholder, so pass the extractor --metadata-zip together
with --translation-metadata-dir (example 3) to restore exact NUMERIC(p, s)
and NOT NULL details:
# 1. Generate per-table PostgreSQL export SQL files from BQMS translation output:
python3 ./export_postgres_tables.py \
--translation-metadata-dir ./translated_output/metadata \
--default-schema public \
--output-dir ./export_sql
# 2. Generate a unified export SQL script from a PostgreSQL extractor metadata.zip:
python3 ./export_postgres_tables.py \
--metadata-zip gs://my-bucket/postgres_metadata/postgresql-metadata.zip \
--schema public \
--output-sql postgres_export.sql
# 3. Combine BQMS translation output with the extractor metadata.zip:
python3 ./export_postgres_tables.py \
--translation-metadata-dir ./translated_output/metadata \
--metadata-zip gs://my-bucket/postgres_metadata/postgresql-metadata.zip \
--default-schema public \
--output-dir ./export_sql
4. Generated SQL Pattern
Snowflake COPY INTO Pattern
ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF6';
ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6';
ALTER SESSION SET TIMESTAMP_TZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6 TZHTZM';
COPY INTO 'gcs://my-bucket/data_unload/RAW_POS/MENU/'
FROM (
SELECT
CAST(MENU_ID AS BIGINT) AS MENU_ID,
CAST(COST_OF_GOODS_USD AS DECIMAL(38, 4)) AS COST_OF_GOODS_USD,
TO_VARCHAR(MENU_ITEM_HEALTH_METRICS_OBJ) AS MENU_ITEM_HEALTH_METRICS_OBJ
FROM MY_DB.RAW_POS.MENU
)
STORAGE_INTEGRATION = my_gcs_integration
FILE_FORMAT = (
TYPE = PARQUET
COMPRESSION = SNAPPY
)
HEADER = TRUE
OVERWRITE = TRUE
MAX_FILE_SIZE = 104857600;
Databricks, Teradata, Oracle, and PostgreSQL Patterns
Section 4 of the Databricks, Teradata,
Oracle, and PostgreSQL mapping references shows
the complete generated script: Spark SQL INSERT OVERWRITE DIRECTORY ... USING PARQUET SELECT ... for Databricks, and explicit-cast SELECT ... statements
(LOCKING ROW FOR ACCESS SELECT ... in Teradata) with -- Unload Table: and
-- Required Columns: markers for the others.