How to load data from Postgres to Snowflake destination

1. Connect to PostgreSQL Database:

Use `psql` or any PostgreSQL client to connect to your database.

```sh

psql -h hostname -p port -U username -d databasename

```

2. Choose Data to Export:

Decide which tables or data you want to migrate to Snowflake.

3. Export Data to CSV:

Use the `COPY` command to export the data to a CSV file. For each table, run:

```sql

COPY (SELECT * FROM your_table) TO '/path/to/your_table.csv' WITH CSV HEADER;

```

Replace `your_table` with the name of your table and `/path/to/your_table.csv` with the path where you want to save the CSV file.

1. Check Data Types:

Review the exported data and ensure that data types are compatible with Snowflake. You may need to convert data types that are not directly compatible.

2. Clean Data:

If necessary, clean the data to remove any inconsistencies or to comply with Snowflake's data format requirements.

3. Split Large Files:

If you have very large CSV files, consider splitting them into smaller files to make the upload process more manageable and to avoid timeouts.

4. Compress Files:

Compress the CSV files using GZIP to save space and reduce upload time.

```sh

gzip /path/to/your_table.csv

```

1. Choose a Cloud Storage Service:

Snowflake supports Amazon S3, Google Cloud Storage, and Azure Blob Storage. Choose one that you have access to and that is supported in your Snowflake region.

2. Upload Files:

Use the cloud storage provider's tools or SDKs to upload your GZIP files.

1. Log in to Snowflake:

Use the Snowflake web interface or the Snowflake client to log in to your account.

2. Create a File Format:

Define a file format that matches the format of your CSV files.

```sql

CREATE OR REPLACE FILE FORMAT my_csv_format

TYPE = 'CSV'

FIELD_DELIMITER = ','

SKIP_HEADER = 1

FIELD_OPTIONALLY_ENCLOSED_BY = '"'

NULL_IF = ('NULL', 'null')

COMPRESSION = 'GZIP';

```

1. Create a Stage:

Create a stage object that points to the location of your uploaded files in the cloud storage.

```sql

CREATE OR REPLACE STAGE my_stage

URL = 's3://mybucket/myfolder/'

FILE_FORMAT = my_csv_format;

```

Replace `s3://mybucket/myfolder/` with the path to your files in cloud storage.

1. Create Tables in Snowflake:

Create tables in Snowflake that match the schema of your PostgreSQL tables.

2. Copy Data:

Use the `COPY INTO` command to load data from the stage into your Snowflake tables.

```sql

COPY INTO my_table

FROM @my_stage/your_table.csv.gz

FILE_FORMAT = (FORMAT_NAME = my_csv_format)

ON_ERROR = 'CONTINUE';

```

Repeat this step for each table you are importing.

1. Check Row Counts:

Compare the row counts in Snowflake tables with the original PostgreSQL tables to ensure completeness.

2. Sample Data:

Query random samples of data in Snowflake and compare them with the original data in PostgreSQL for accuracy.

3. Check for Errors:

Review the load history and error logs in Snowflake to identify any issues that occurred during the data load.

1. Perform Additional Data Validation:

Depending on the complexity of your data, you may need to perform additional validation, such as checking data integrity, foreign key relationships, and indexes.

2. Adjust Queries and Stored Procedures:

Update any queries, views, or stored procedures to work with Snowflake's SQL syntax and features.

3. Test Applications:

If the data is used by applications, thoroughly test them to ensure they work correctly with the new data in Snowflake.

4. Schedule Incremental Updates:

If your data in PostgreSQL will continue to change, plan for incremental updates to keep the Snowflake data in sync.

By following these steps, you should be able to move data from PostgreSQL to Snowflake without using third-party connectors or integrations. Remember to always perform thorough testing at each step to ensure the integrity and accuracy of your data migration.

‍