In the world of ever-growing data, selecting the optimal technology stack to leverage the data is increasingly complicated. There are many options for hosting databases, ranging from on-premises to cloud, and even hybrid cloud-based solutions. The number of solutions and platforms makes the decision-making process more challenging than ever, involving an intensive thought process and analysis of past failures, current hurdles, business continuity requirements, disaster recovery procedures, redundancies, compliance requirements, performance, current skillsets, cost efficiency, and the learning curve for adoption.

In addition to the above, an organization must also ensure the solutions they choose can support their type of data-needs, including for structured, semi-structured, and non-structured data. After ensuring the solution can handle your data types, you must consider performance, including data ingestion, downstream data servicing, and query execution. At this point, I am not even scratching the surface of the complexity of making the right decision.

Data services

In this article, I am going to concentrate on identifying scenarios where one would select data services from Azure and/or Snowflake without concentrating or showing bias towards either. This article is to help you improve your decision-making process for determining the correct data technology for your organization.

Example: Thought process of selecting from two market leaders of Database-as-a-Service

Since the launch of Snowflake in 2014, Snowflake has experienced rapid growth via rapid platform adoption. The key differentiator for Snowflake is that it allows organizations to centralize data for unlimited performance and scale by providing secure and governed data estate with zero maintenance.

Does this fit all database requirements in all use cases? The short answer is no, but it does shine in many scenarios. Microsoft Azure is a cloud platform with a huge matrix of services and products. A huge part of the offers on Azure revolves around supporting all different kinds of data and data requirements.

Since data comes in many varieties, volumes, and velocities, a modern data estate cannot be complete with only a “database”.  The concept of best practices for handling data, storing, and recoverability, to name a few, are at the highest priority for selecting database/datastores. Azure provides everything an organization needs to build a centralized, full-scale data environment, including data pipelines, orchestration, transformations, applications, security, streaming, messaging, databases, and more.

Identifying the right database for you

Now, let’s deep dive into the factors which will allow us to select the right database for our needs.

We will identify this by analyzing some key points like data types, data frequency, size growth trends, data storage, and strategy, data serving and consumption requirements, scaling and availability, etc.

Below flow chart shows the overview of the sequence:

The data generation – i.e. where the data is coming from – will also have an impact on database choice. Whether the data is generated in a transactional or semi-structured way, there are three main types we can bucket them in:

• Logs, events, and e-commerce
• Batch
• Near real-time / real-time

Let’s look at the typical use cases that generate these data types:

Logs, events, and e-commerce

• Log analytics
• Alert management
• Customer service
• Troubleshooting
• Proactive maintenance

Batch

• Data collection quality
• Self-service BI
• Analytical consumption
• Warehouse transformation

Near real-time / real-time

• Predictive maintenance
• Real-time site recovery
• Device management
• Manufacturing insights

As depicted in the diagram above, and regardless of whether the data generated is transactional or semi-structured, it’s its consumption requirements (transactional, analytical, or analytical + visualization) that will determine the most appropriate database for the application.

Comparison between closely related databases (Azure Synapse Analytics and Snowflake)

There are pros and cons for both Azure Synapse Analytics and Snowflake, depending on the use case.

There is an essential best practice for handling data from one stage to another, i.e., maintaining traceability back to the data source. The technology known as Polybase allows Azure Synapse Analytics to keep data at the storage layer and access directly from it by using external schema definitions from Synapse. This assures that the data is not stored in the database but brought in during query on-demand.

In Snowflake, there are 3 options for how to store the data:

1. Point the source dataset and allow Snowflake to apply the optimization. This allows Snowflake to leverage optimal performance when querying (performance is great when leveraged).
   • PROS: Good performance, compressed to save storage cost
   • CONS: Unable to access raw data outside Snowflake
2. Point to source dataset and do not allow Snowflake to convert to optimize:
   • PROS: Able to maintain and access RAW data directly
   • CONS: Require additional configuration at the workspace level to improve performance and higher storage cost due to un-compressed data
3. Point to source dataset, allow to change but by replicating the dataset
   • PROS: Leverage higher performance optimization and traceability back to source data
   • CONS: Higher storage cost, as the data is replicated, once as RAW and other as compressed and optimized.

The table below shows a little more on the comparison:

Conclusion

As you can review above, it is not fair to say that one is better. Every data service offering has its merits and demerits. The important exercise for each organization is to analyze the business requirements and never forget the 5 pillars of architecting when selecting a database :

1. Operational Excellence
2. Performance Efficiency
3. Cost Optimization
4. Reliability
5. Security

There are many recommended best practices, many of which can be organization-specific and may be dictated by your existing process owners and engineering team. This information will help you decide, allowing you to narrow down the options to select from based on either it will be easily supported by the solution you are finalizing or not.

If you would like to learn more or engage in our services, contact us.