How to load data from BigQuery to Snowflake destination

1. Open the BigQuery Console: Navigate to the BigQuery console within your Google Cloud Platform (GCP) account.
2. Select the Dataset and Table: Locate the dataset and table you wish to export.
3. Export Table Data: Use the BigQuery UI or the bq command-line tool to export your table data to Google Cloud Storage (GCS) in a format compatible with Snowflake, such as CSV, JSON, Avro, or Parquet.
   
   For example, using the bq tool:
   bq extract --destination_format CSV 'mydataset.mytable' gs://my-bucket/myfolder/mydata.csv
   
   
4. Replace mydataset.mytable with your dataset and table name, and gs://my-bucket/myfolder/mydata.csv with your GCS bucket and desired file path.

1. Choose a Staging Area: Decide on a staging area that Snowflake can access. Snowflake supports data loading from AWS S3, Azure Blob Storage, Google Cloud Storage, and Snowflake’s own staging area.
2. Transfer to Staging Area:some text
   • If you're using GCS as your staging area and your Snowflake account is on GCP, you can use the data directly from GCS.
   • If your Snowflake account is not on GCP, you may need to transfer the data to a supported storage service like AWS S3 or Azure Blob Storage using cloud data transfer services or tools.

1. Login to Snowflake: Access your Snowflake account.
2. Create a File Format: Define a file format that matches the data files you exported from BigQuery.
   For example, for CSV files:
   CREATE FILE FORMAT my_csv_format

TYPE = 'CSV'

FIELD_DELIMITER = ','

SKIP_HEADER = 1

NULL_IF = ('NULL', 'null');

Create a Stage: Set up a stage in Snowflake that points to the location of your data files.
If using GCS:
CREATE STAGE my_gcs_stage

URL = 'gcs://my-bucket/myfolder/'

FILE_FORMAT = my_csv_format

CREDENTIALS = (AWS_KEY_ID = '' AWS_SECRET_KEY = '');

Adjust the URL to point to your GCS bucket and folder, and provide the necessary credentials.

1. Create a Target Table: Ensure that you have a target table in Snowflake with a schema that matches the data you're importing.
2. Copy Data: Use the COPY INTO command to load the data from the stage into the target table.
   COPY INTO my_target_table

FROM @my_gcs_stage/mydata.csv

FILE_FORMAT = (FORMAT_NAME = my_csv_format)

ON_ERROR = 'CONTINUE';

1. Replace my_target_table with the name of your target table and adjust the file path if necessary.

1. Check the Loaded Data: After the COPY INTO operation, check the loaded data for any errors or discrepancies.
2. Verify Row Counts: Compare the row counts in Snowflake with the original row counts in BigQuery to ensure completeness.
3. Perform Data Quality Checks: Run queries to validate the data quality, ensuring that the migration process hasn't altered the data.

1. Remove Temporary Files: After the data is successfully loaded into Snowflake, remove the temporary files from the staging area to avoid unnecessary storage costs.
2. Delete GCS Data: If you no longer need the exported data in Google Cloud Storage, delete the files to free up space.

• Security: Ensure that all data transfers are secure, using encryption in transit and at rest.
• Cost: Be aware of the costs associated with data export, storage, and transfer in both GCP and Snowflake.
• Automation: For recurring data transfers, consider automating the process with scripts or cloud functions.
• Data Types: Make sure that data types are correctly mapped between BigQuery and Snowflake.

By following these steps, you can move data from BigQuery to Snowflake without using third-party connectors or integrations. Always test the process with a subset of data before migrating the entire dataset.