You’re convinced—you’ve witnessed the value and power of the Snowflake Modern Data Stack—and now you think, “How do I go about bringing Snowflake into my organization?”
As someone who’s spent their entire career working with data, data warehouses, and business intelligence platforms, there’s the natural desire to acquire the latest tech and start tinkering with it.
While tinkering will expose you to the functionality and capabilities of tools like Snowflake, Fivetran, and Coalesce, it is not the best approach for understanding how to implement these technologies in your organization.
So, let’s take a deep breath and consider the right approach to architecting your Snowflake data platform.
It all starts with designing the ideal data architecture for your company’s near- and long-term goals.
Let’s dive in!
Data Challenges
You and your business users are likely facing various data-related challenges, which is why you are considering Snowflake in the first place.
Here are some common data and analytics challenges we tend to see for our clients.
Disparate, siloed data sources
What does having disparate and siloed data sources mean?
Organizations capture all kinds of data throughout a business’s day-to-day operations via various systems and applications.
What are some of the source systems that companies bring into Snowflake?
- Enterprise Resource Planning (ERP)
- Customer Relationship Management (CRM)
- Human Resources Information System (HRIS)
- Point-of-Sale (POS)
- Enterprise Performance Management (EPM)
- Master Data Management (MDM)
- Flat files
- Databases
- And many, many more!
Each of these environments captures and maintains business processes and the data critical to their success.
If you were to start a business and enable each of these applications by default, most of them would not be integrated – or, simply put – would not share or exchange data with each other.
Ultimately, these are silos of data.
For businesses to excel, they need to break away from doing everything in silos and provide the information business leaders need to make informed decisions to run the business more efficiently.
By being locked into data silos without proper data integration infrastructure, the business users must rely on manually downloading data from these systems and combining the outputs in Excel.
This leads to our next challenge: manually performing data processes.
Manual processes
At some point in your career, I bet an executive has asked you to provide a key report by the end of the day, as it was critical for winning a major customer.
So, like many people without the proper data management infrastructure and reporting platform, you are left to your own devices to figure out how to create this report.
You likely downloaded data from a couple of systems, did some VLOOKUPs in Excel, manually typed in values based on conversations from other departments, and barely met your boss’s deadline.
Talk about a high-pressure situation!
Now, imagine a scenario where this was your job 55% of the time. Sounds crazy, right? But the reality is that many people in corporate roles—ranging from junior employees to experienced leaders—deal with these exact situations.
Some accept it as inevitable because that’s all they‘ve ever known. Fortunately, leveraging Snowflake’s Modern Data Stack will alleviate most of these situations and allow people to focus on the most important aspects of their jobs.
As painful as manual data processes are, the pain extends well beyond the individual. Let’s examine the adverse side effects of manually cleansing data and building reports.
Error-prone reporting
We are all human. Humans make mistakes. Therefore, we make mistakes. It’s an inevitability.
When handling data and using it to build reports focused on company KPIs and metrics, the risk of mistakes is heightened when performing these tasks manually.
A mapping is missed, the wrong number is entered, an Excel formula is broken – all things that can easily happen and go unnoticed when rushing to meet a time-sensitive deadline.
Mistakes like these can cost companies minor inconveniences to millions of dollars. No company wants to be the next news headline. Yet, we are seeing them increasingly because companies fail to scale their data management infrastructure, ultimately opening the door for more mistakes.
Inconsistent Metrics
People love to talk about data as a significant asset to companies, but it is also one of the biggest liabilities for many companies. Without a defined data governance process, it’s not easy to understand the sources of KPIs.
Worse yet, multiple versions of the same KPI or metric show up. Finance’s version of revenue is different from Sales’s, and both are different from what was submitted to the Board. And they likely are all called revenue!
Having seen this situation on a scale for billion-dollar organizations, I can confidently say that business users lose trust in the data and the systems they provide. Tracking down the source of each version of each metric takes excessive amounts of time that could otherwise be used for more important tasks. How can you run a business effectively without trust in the data?
It’s simple: You can’t. Big businesses are much more inefficient than you might think because their data problems, like this example, are multiple factors worse than those of smaller organizations.
Backlog of data requests
Suppose you are a lucky business user with access to data engineering, data analysis, or data science teams. In that case, these situations above can create extensive backlogs of requests from across the business that these teams can never keep up with.
This is to say that throwing people at an ever-growing problem will not solve it. You have to get to the root of the problem, which, in this case, is the lack of data management and analytics infrastructure to support the business’s growing needs.
Snowflake’s ease of use and emphasis on scaling as the business scales will alleviate many of these backlog requests because data teams can fulfill them faster and more easily.
You’ve run out of Band-Aids and workarounds
If you’ve gotten this far, I guess these challenges resonate with you. Given that you are curious to learn what other companies are facing about data management and infrastructure, I bet you are someone who has taken it upon yourself to do whatever you possibly can to help your business stakeholders – because you are someone who cares and wants to help other people.
Some of these workarounds could look like this:
- Writing one-off SQL queries in your legacy database for specific business cases
- Using whatever tools possible, like Windows batch on your local machine, to get any semblance of automation for the repeatable tasks
- Creating VBA macros in Excel to perform some light data transformations to help users avoid the same mistakes over and over again
But time has run out, and you are looking for a better solution to these data challenges.
Fortunately, there is hope! It starts by understanding how to deploy Snowflake and other tools within the ecosystem, such as Fivetran, Coalesce, Orchestra, and Sigma, to help fix these ongoing data challenges for good.
Solution: The Snowflake Modern Data Stack
Now that we’ve covered the data challenges—and wow, are there a lot of them—it’s time to get into the fun stuff—understanding how to start designing and deploying your Snowflake data management solution.
Let’s break down this data architecture diagram piece by piece to give you a sense of how you’d want to think about designing your ideal Snowflake modern data architecture for your organization.
Source Data
We talked above about having disparate, siloed data sources, so the first area in the Snowflake architecture that we’ll focus on is getting those sources into your Snowflake data platform.
We will focus on extracting and loading (EL) from the data sources into the Snowflake data platform for our data ingestion. Below, we’ll discuss the benefits of extract-load-transform (ELT) compared to extract-transform-load (ETL) to give greater context as to how we are approaching our data ingestion with ELT.
Approaches
Not all data sources are created equal; therefore, you need your Snowflake modern data architecture to be flexible enough to source data from various formats. Some of the most common sources we see are:
- On-premise or Cloud-based applications
- Unstructured, semi-structured, or structured files
- Databases
You want to ensure your data ingestion strategy can easily accommodate each of these use cases. To capture the data from these sources, some ways of doing this are:
- APIs for accessing data from applications
- SFTP for secure file transfer management
- Data sharing from other databases
- Querying databases directly
Data Volume and Frequency
Not all data is created equal, and therefore, you want to ensure that your data ingestion platform can accommodate small and high volumes of data with the potential for real-time frequency.
You may not use the full capabilities for real-time data since they will vary based on the use case, but should your business need real-time data in the future, you’ll be able to more easily source that data into your Snowflake data platform without having to rebuild a portion of the infrastructure.
There are many technology options for data ingestion, but our clients tend to prefer Fivetran because it can accommodate all of the use cases above with minimal maintenance.
Data Layer
Once you’ve staged your data into your Snowflake data platform, you must prepare, cleanse, and align it to build a data model your business users will easily understand.
Data transformation is where many companies get into trouble using the Modern Data Stack because they don’t think about the long-term consequences of not having sound governance around their data transformation models.
Our preference and recommendation for data teams working on the Snowflake data platform is to use Coalesce because of its easy-to-use interface and its ability to quickly perform data modeling techniques using prebuilt models. Using Coalesce significantly reduces the speed of constructing a data warehouse and lowers the barrier to entry for data teams.
The data transformation tier also enables us to create the modeled or structured layers of the data platform—specifically, your data warehouse, data marts, semantic layer, and any other data objects that need some structure.
Ideally, you will follow a consistent architecture throughout your organization to manage governance easily and apply the same data governance framework across the enterprise.
This could be the Medallion architecture, which is simplified as:
- Bronze: staging your raw data
- Silver: modeling the data into a structured data warehouse
- Gold: apply additional logic and aggregations in a semantic layer before exposing it to the business in the consumption layer
When you determine how the future-state data environment will be governed, you’ll want to define who should access each data layer and the processes they must follow to maintain your data environment.
ELT vs. ETL
Extract-Transform-Load (ETL)
Historically, extract-transform-load (ETL) was the preferred method of ingesting and transforming data. It allowed companies to more effectively manage infrastructure costs since on-premise resources (servers, processors, memory, disk space) were expensive to procure.
ETL worked as follows:
- Extract – extract the data from the source systems into the data integration tool or process
- Transform – cleanse the data and apply business logic to narrow down the information that will be used for reporting
- Load – load the cleansed and aggregated results to tables in your data environment
What are some examples of ETL tools?
Some tools that focus on ETL are Informatica PowerCenter, Oracle Data Integrator, and even procedural SQL. With the advent of the cloud, the pricing model for infrastructure resources has changed, and storage is cheap. That said, you must learn and understand the new consumption models in the cloud so as to properly manage your cloud infrastructure costs.
Extract-Load-Transform (ELT)
Given that cloud infrastructure, such as coupling AWS and Snowflake, lowered the cost of data management infrastructure, companies realized they could start harvesting data from across the enterprise (as well as external sources) and create a data lake.
Thus, extract-load-transform (ELT) was born.
ELT works as follows:
- Extract – extract the data from your source systems
- Load – load the data into your data platform
- Transform – cleanse and model the data for the consumption layer
This approach allows companies to ingest their data into a single location before cleansing or transforming it, allowing users to do data discovery on data in its raw form.
Following the business’s data discovery exercise, they can then provide guidance to their IT and Data teams on what data needs to be modeled and why.
It enabled the beginning of self-service analytics and empowered business users to own and access their data in a timely manner.
Of course, data lakes spiraled out of control, and people had access to too much information without much guidance, data modeling, or data quality.
To avoid this trap, it’s important to have a data governance process to ensure a clear framework for determining how data gets brought into your Snowflake data platform, who owns it, and how it will be funneled through your data modeling process.
Consumption Layer
The data consumption or analytics layer is where your users will consume the data you’ve prepared.
What types of applications are in the consumption layer?
Some examples of applications include:
- Business intelligence (BI) reports & dashboards – sets of repeatable reports that help teams understand and run the business
- Artificial intelligence (AI) and Machine Learning (ML) models – advanced learning models to help inform specific business processes
- Ad-hoc analyses – answer various questions from leadership as they come up
- Reverse ETL – send data to other downstream applications
It can be overwhelming to decide where to prioritize your time and effort. I always recommend starting with the basics. Meet with your business users to understand their biggest data challenges. For many people, this could be as simple as downloading data from any of your source systems, transforming it themselves within Excel, and putting the results into a PowerPoint presentation.
Look for quick-win opportunities that you are confident could be solved by deploying the Snowflake Modern Data Stack. Use those conversations to inform how you will consider your data architecture and data model.
Meeting with the business users before embarking on this data journey would be best because then you’ll know the most significant benefits the business will gain from using the new cloud infrastructure.
Data Orchestration
The final piece of the architecture we’ll focus on is how to link the end-to-end data processes together.
Data orchestration is how you schedule and manage the processes across your data environment. With the advent of the modern data stack, we’ve found it valuable to get an orchestration tool rather than relying on the scheduling capabilities of each specific tool. That way, you can easily manage your end-to-end data workflows.
Our clients like Orchestra because of its user-friendly interface and the ease with which you can configure and manage your data pipelines.
By understanding the full lineage of the data execution processes, you can easily troubleshoot errors when unexpected issues arise.
What are the benefits of using the Snowflake Modern Data Stack?
- From disparate, siloed data sources to a unified platform – by leveraging the power of the Snowflake Data Cloud and the complimentary tools in the Snowflake ecosystem, companies can move away from operating in data silos to having access to all their information in one cohesive platform. This will give business users faster access to their data and value their data team providing them with the information.
- From manual processes to automated workflows – nobody wants to spend half their job downloading and manipulating data to the format they need. That’s why it’s important to look for opportunities to help automate data pipelines from the source systems and transform the results in your Snowflake platform using a tool like Coalesce, ultimately saving your business users a lot of time.
- From error-prone reporting to dashboards that users trust – it’s a wonderful sight to see when users retire their complex Excel models and consistently use that new set of dashboards you created for them. By properly modeling data in Snowflake, you’ll build a new cohort of users who will spread the good word about the solution you’ve created.
- From inconsistent metrics to a single source of truth – seeing the same metric name with different numbers can frustrate leadership. By taking advantage of data ingestion and modeling best practices in your Snowflake environment, coupled with sound governance processes, you can ensure that your business users and leadership never get confused by metrics again.
- From a backlog of data requests to a feeling of satisfaction – data teams are tired of playing catch-up and never feel like they can get ahead. By deploying Snowflake’s Modern Data Stack, you can take control of your data requests by empowering your business users to be self-sufficient and by having data pipelines that are easy to maintain.
Summary
The Snowflake Modern Data Stack is a powerful ecosystem that has helped thousands of companies overcome their most challenging data infrastructure, management, and analytics issues.
It can be daunting to embark on the journey, but if you follow some of the guidance in this article, you’ll be well ahead of the game.
If you need help along this journey, one of our seasoned Snowflake Architects would be happy to brainstorm your vision with you.
