131 ) using fivetran to move data ETL from oracle/ SSMS to SNOWFLAKE

 Moving data from Oracle and SQL Server Management Studio (SSMS / Microsoft SQL Server) to Snowflake using Fivetran follows a standard ELT pipeline setup. Fivetran automates the extraction, schema creation, and loading processes.

Phase 1: Set Up Snowflake as Your Fivetran Destination

Before configuring your sources, you need to set up Snowflake in Fivetran to receive the data.

1. Log into your Fivetran Dashboard.

2. Go to the Destinations tab and click Add Destination.

3. Select Snowflake and click Add.

4. Fill in your Snowflake connection details:

   • Account: Your Snowflake account name/locator or complete hostname URL.

   • User & Password: A dedicated Fivetran user with appropriate roles (e.g., CREATE DATABASE, USAGE).

   • Database & Warehouse: The target database and virtual warehouse Fivetran will use to write data.

5. Click Save & Test to verify that Fivetran can successfully reach your Snowflake environment.

Phase 2: Connect Oracle to Fivetran (Source 1)

To sync data from an Oracle database:

1. Prepare Oracle Database: Ensure your Oracle instance allows incoming connections from Fivetran’s IP addresses (whitelist Fivetran IPs). Create a dedicated database user for Fivetran with read permissions on the schemas/tables you want to sync.

   • Note: For change data capture (CDC), ensure log-based replication features (like Oracle LogMiner or Oracle Binary Reader) are configured if required by your setup.

2. In the Fivetran dashboard, navigate to the Sources page and click Add Source.

3. Select Oracle from the database list.

4. Enter your Oracle connection credentials:

   • Host server address and port

   • Database name / SID / Service Name

   • Username and password

5. Click Save & Test to authenticate the connection.

6. Select the specific schemas and tables you want to sync to Snowflake, then complete the initial setup to launch the historical sync.

Phase 3: Connect SQL Server / SSMS to Fivetran (Source 2)

While SSMS is the management tool, the underlying data source is Microsoft SQL Server.

1. Prepare SQL Server: Ensure your SQL Server instance (on-premises or cloud-hosted) allows external traffic from Fivetran's IP range through your firewall or security groups. Create a dedicated SQL login or Windows user for Fivetran with SELECT permissions. Enable Change Tracking on the database and tables if you want low-latency incremental updates.

2. In Fivetran, go back to the Sources page and click Add Source.

3. Select SQL Server.

4. Enter your SQL Server connection details:

   • Server address (host) and port (default is usually 1433)

   • Database name

   • Username and password

5. Click Save & Test to check network reachability and credentials.

6. Choose the tables and schemas you want to replicate, then initiate the sync.

Phase 4: Monitor and Manage Data Pipelines

Once both connectors are active:

• Initial Sync: Fivetran will perform a full table scan/dump of your selected Oracle and SQL Server tables and load them into your Snowflake destination schemas.

• Incremental Updates: Fivetran automatically shifts to incremental syncing (using CDC, LogMiner, or Change Tracking) to capture INSERT, UPDATE, and DELETE events continuously.

• Schema Drift: If you alter tables (e.g., add columns or change data types) in Oracle or SQL Server, Fivetran automatically detects these structural changes and propagates them safely to your Snowflake tables.