Table of Contents
Loading Data From SQL Server to Snowflake – An Overview
In today’s business environment, enterprises depend on data analytics to power growth and expansion and to take key decisions. It is therefore essential that Database Administrators have access to the latest technologies and optimized platforms so that their task of managing data is made easy and seamless.
Organizations have to process data that not only flows between various departments in-house but also from external sources. One of the most optimized methods is to load databases from traditional existing systems to one that offers a host of benefits. And keeping this in mind, businesses are today opting to load databases from Microsoft SQL Server to Snowflake.
Before the process is studied in detail, a look at the two will be in order.
Microsoft SQL Server
Microsoft SQL Server has been the backbone of organizations for decades and has many leading features. The server integrates seamlessly into the Microsoft ecosystem and supports .NET framework out of the box. It is a relational database management system (RDMS) and supports all applications on a single machine regardless of whether they are based on the web or a local area network.
SQL Server is ideal for analytics and database transactions processing and has been considered to be at par with other leading databases like Oracle Database and DB2 from IBM. The server is designed on SQL which is a programming language generally used by DBAs to query data in the databases.
The question now is that given the edge provided by this server to businesses why would they want to load databases from SQL Server to Snowflake.
Snowflake
Snowflake is a data warehousing solution that is based entirely in the cloud. The reasons for its popularity are two-fold. First, it resolves issues that are inherent in traditional database systems and second, it brings all the benefits of working on the cloud to users.
Here are some of the main benefits of Snowflake.
The Snowflake architecture supports multiple cloud vendors and hence users can use the same tools to work with any of them. The Snowflake platform has separate computing and storage platforms that are both flexible and reliable. Users can work on either of them, seamlessly scale up or down and pay only for the quantum of resources used.
Further, a unique feature is that both structured and unstructured data can be loaded to Snowflake, something that is not available on traditional databases like SQL Server or Oracle. Snowflake supports JSON, Avro, XML, AND Parquet data. Again, Snowflake is a high-performing database solution. Several users can execute intricate queries simultaneously without facing any lag in speeds or a drop in performance.
Loading data from Microsoft SQL Server to Snowflake
There are several steps for loading data from SQL Server to Snowflake. It is automated and hence DBAs can avoid a long and tedious manual process.
- Mining data from SQL Server – Mining of data from the SQL Server is commonly done through queries for extraction. Select statements are used to sort and filter the data before it is retrieved. The in-built Microsoft SQL Server Management Studio tool helps to extract entire databases or bulk data in CSV, SQL queries, or text format.
- Formatting extracted data – This mined data cannot be loaded directly to Snowflake. It has to be ensured that the data type matches with those supported by Snowflake. The extracted data has to be formatted and processed and made to be in sync with Snowflake architecture. For loading JSON or XML data into Snowflake, it is not necessary to specify a schema beforehand.
- Staging of data files – Even at this stage, the data files cannot be transferred to Snowflake. Instead, they have to be kept in a separate location or staging area. There are two parts here.
Internal Stage – It is created by the DBAs with SQL statements and a name for the stage and type of file format is allotted to it. This stage provides a great degree of flexibility when loading SQL Server to Snowflake.
External stage – Currently, the external storage locations that are supported by Snowflake are Microsoft Azure and Amazon Simple Storage Service (S3). An external stage can be designed using these locations and the data can be uploaded using the respective cloud vendor interfaces.
- Loading data to Snowflake – The data is now ready to be loaded into Snowflake. For large databases, the Data Loading Overview in Snowflake guides users through the process. The data loading wizard in Snowflake is good for small databases. For bulk databases, the PUT command is used to stage files and the COPY INTO command to load processed data into an intended table in Snowflake from an internal or external staging area where the database is temporarily located.
Even after loading an entire database from SQL Server to Snowflake, the issue of incremental data and updating changes remain. It is therefore preferable to create a script that recognizes new data at source and uses an auto-incrementing field as a tool to continually update the data at target.
