Data Warehouse Migration Challenges and How to Meet Them
3840
Of your peers have already read this article.
7:30 Minutes
The most insightful time you'll spend today!
In the last blog post, we discussed why legacy data warehouses are not cutting it any more and why organizations are moving their data warehouses to cloud.
At GCP, we often hear that customers feel that migration is an uphill battle because the migration strategy was not deliberately considered.
Migrating to a modern data warehouse from a legacy environment can require a massive up-front investment in time and resources. There’s a lot to think about before and during the process, so your organization has to take a strategic approach to streamline the process.
At Google Cloud, we work with enterprises shifting data to our BigQuery data warehouse, and we’ve helped companies of all kinds successfully migrate to cloud. Here are some of the questions we frequently hear around migrating a data warehouse to the cloud:
- How do we minimize any migration risks or security challenges?
- How much will it cost?
- How do we migrate our data to the target data warehouse?
- How quickly will we see equal or better performance?
These are big, important questions to ask—and have answered—when you’re starting your migration. Let’s take them in order.
How do we minimize any migration risks or security challenges?
It’s easy to consider an on-premises data warehouse secure because, well, it’s on-site and you can manage its data protection. But if scaling up an on-prem data warehouse is difficult, so is securing it as your business scales.
We’ve built in multiple features to secure BigQuery. For enterprise users, Cloud Identity and Access Management (Cloud IAM) is key to setting appropriate role-based user access to data.
You can also take advantage of SQL’s security views within BigQuery. And all BigQuery data is encrypted at rest and in transit.
You can add the protection of customer-managed encryption keys to establish even stronger security measures. Using virtual private cloud (VPC) security controls can secure your migration path, since it helps reduce data exfiltration risks.
How much will it cost?
The cost of a cloud data warehouse has a different structure from what you’re likely used to with a legacy data warehouse. An on-prem system like Teradata may depend on your IT team paying every three years for the hardware, then paying for licenses for users who need to access the system. Capacity increases come at an additional cost outside of that hardware budget.
With cloud, you’ve got a lot more options for cost and scale. Instead of a fixed set of costs, you’re now working on a price-utility gradient, where if you want to get more out of your data warehouse, you can spend more to do so immediately, or vice versa. While cloud data warehouses help reduce or eliminate capital and fixed costs, they are not all the same.
You’ll find varying levels of simplicity and cost savings across vendors, so it’s important to check out the operational costs of each data warehouse in relation to its performance.
With a cloud data warehouse like BigQuery, TCO becomes an important metric for customers when they’ve migrated to BigQuery (check out ESG’s report on that), and Google Cloud’s flexibility makes it easy to optimize costs.
How do we migrate all of our data to the target data warehouse?
This question encompasses both migrating your extract, transform, load (ETL) jobs and SAS/BI application workloads to the target data warehouse, as well as migrating all your queries, stored procedures, and other extract, load, transform (ELT) jobs.
Actually getting all of a company’s data into the cloud can seem daunting at the outset of the migration journey. We know that most businesses have a lot of siloed data. That might be multiple data lakes set up over the years for various teams, or systems acquired through acquisition that handle just one or two crucial applications. You may be moving data from an on-prem or cloud data warehouse to BigQuery and type systems or representations don’t match up.
One big step you can take to prepare for a successful migration is to do some workload and use case discovery.
That might involve auditing which use cases exist today and whether those use cases are part of a bigger workload, as well as identifying which datasets, tables, and schemas underpin each use case.
Use cases will vary by industry and by job role. So, for example, a retail pricing analyst may want to analyze past product price changes to calculate future pricing. Use cases may include the need to ingest data from a transactional database, transforming data into a single time series per product, storing the results in a data warehouse table, and more.
After the preparation and discovery phase, you should assess the current state of your legacy environment to plan for your migration. This includes cataloging and prioritizing your use cases, auditing data to decide what will be moved and what won’t, and evaluating data formats across your organization to decide what you’ll need to convert or rewrite.
Once that’s decided, choose your ingest and pipeline methods. All of these tasks take both technology and people management, and require some organizational consensus on what success will look like once the migration is complete.
How quickly will we see equal or better performance?
Managing a legacy data warehouse isn’t usually synonymous with speed. Performance often comes at the cost of capacity, so users can’t do the analysis they need till other queries have finished running.
Reporting and other analytics functions may take hours or days, which is especially true for running large reports with a lot of data, like an end-of-quarter sales calculation. As the amount of data and number of users rapidly grows, performance begins to melt down and organizations often face disruptive outages.
However, with a modern cloud data warehouse like BigQuery, compute and storage are decoupled, so you can scale immediately without facing capital infrastructure constraints.
BigQuery helps you modernize because it uses a familiar SQL interface, so users can run queries in seconds and share insights right away. Home Depot is an example of a customer that migrated their warehouse and reduced eight-hour workloads to five minutes.
Moving to cloud may seem daunting, especially when you’re migrating an entrenched legacy system. But it brings the benefits of adopting technology that lets the business grow, rather than simply adopting a tool. It’s likely you’ve already seen that the business demand exists. Now it’s time to stop standing in the way of that demand and instead make way for growth.
How Ather Energy is leveraging the Cloud to build and scale smart mobility solutions for India
8377
Of your peers have already read this article.
8:30 Minutes
The most insightful time you'll spend today!
In 2013, long before the world was discussing clean energy and sustainable practices, two IIT Madras graduates — Swapnil Jain and Tarun Mehta — had an idea to develop India’s first-ever electrical scooter.
This was at a time when auto manufacturers were still focusing on fossil-fuel-driven vehicles and ‘eco-friendly’ mobility solutions were more a trendy alternative catering to a niche market.
The duo founded Ather Energy in 2013 and launched their first fully-electric scooter, the Ather S340, in Bengaluru in 2016. Since then, the company has released several new models into the market and is planning to expand to eight more cities by the end of the year.
To support the smooth running of their vehicles, lower costs, improve time to market, and create great customer experience, Ather turned to Google Cloud.
Read the Full Story on YourStory
3194
Of your peers have already watched this video.
32:30 Minutes
The most insightful time you'll spend today!
Accelerating Insights with the Health Analytics Platform
The healthcare industry is beset with a data challenge, specifically clinical data, and challenges around the proliferation of EHR instances and ancillary clinical systems.
In this video, Vivian Neilley, Solution Architect, Google Cloud and Marianne Slight, Product Manager, Google Cloud discuss how some of these challenges can be overcome with the Health Analytics Platform.
The Health Analytics Platform enables companies who have healthcare data in multiple sources on their own infrastructure to harmonize data across systems, monitor analytics, and create compelling visualizations that enable easier decision-making.
This new product suite allows for healthcare and life science organizations to manage data pipelines and run advanced analytics on managed infrastructure with interactive UIs and health data-aware features.
The Health Analytics Platform provides and end-to-end solution, from ingestion of data to harmonization to a common data model, to dashboard visualizations and machine learning.
Whether organizations leverage a data lake or are utilizing a full data warehouse approach, the platform gives users the flexibility to meet customers where they are today. This session will demonstrate how to leverage the health analytics platform to accelerate time to insights in a healthcare or life sciences organization.
The Query Execution Graph: Your Key to Better BigQuery Analytics
3080
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
BigQuery offers strong query performance, but it is also a complex distributed system with many internal and external factors that can affect query speed. When your queries are running slower than expected or are slower than prior runs, understanding what happened can be a challenge.
The query execution graph provides an intuitive interface for inspecting query execution details. By using it, you can review the query plan information in graphical format for any query, whether running or completed.
You can also use the query execution graph to get performance insights for queries. Performance insights provide best-effort suggestions to help you improve query performance. Since query performance is multi-faceted, performance insights might only provide a partial picture of the overall query performance.
Execution graph
When BigQuery executes a query job, it converts the declarative SQL statement into a graph of execution, broken up into a series of query stages, which themselves are composed of more granular sets of execution steps. The query execution graph provides a visual representation of the execution stages and shows the corresponding metrics. Not all stages are made equal. Some are more expensive and time consuming than others. The execution graph provides toggles for highlighting critical stages, which makes it easier to spot the potential performance bottlenecks in the query.
Query performance insights
In addition to the detailed execution graph BigQuery also provides specific insights on possible factors that might be slowing query performance.
Slot contention
When you run a query, BigQuery attempts to break up the work needed by your query into tasks. A task is a single slice of data that is input into and output from a stage. A single slot picks up a task and executes that slice of data for the stage. Ideally, BigQuery slots execute tasks in parallel to achieve high performance. Slot contention occurs when your query has many tasks ready for slots to start executing, but BigQuery can’t get enough available slots to execute them.
Insufficient shuffle quota
Before running your query, BigQuery breaks up your query’s logic into stages. BigQuery slots execute the tasks for each stage. When a slot completes the execution of a stage’s tasks, it stores the intermediate results in shuffle. Subsequent stages in your query read data from shuffle to continue your query’s execution. Insufficient shuffle quota occurs when you have more data that needs to get written to shuffle than you have shuffle capacity.
Data input scale change
Getting this performance insight indicates that your query is reading at least 50% more data for a given input table than the last time you ran the query and hence experiencing query slowness. You can use table change history to see if the size of any of the tables used in the query has recently increased.
What’s next?
We continue to work on improving the visualization of the graph. We are working on adding additional metrics to each step and adding more performance insights that will make query diagnosis significantly easier. We are just getting started.
Bit Capital Rolls Out a Digital Financial Solution in Under 3 Months and at 2/3 the Cost
5680
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
In the past few years, a series of new technologies and regulatory changes has been transforming Brazil’s financial industry. The concept of blockchain added security and agility to financial transactions. The open banking system being deployed by the country’s Central Bank (BC) allows for platform integration and data sharing -with the user’s consent- between financial institutions. Recently, the launch of Pix, also by the BC, has shaken the market by creating a new payment method that is instant, free for individuals and 24/7.
Since 2018, the startup Bit Capital has been working on the development of 100%-digital financial solutions in the cloud to help its clients adapt to this new scenario in a convenient way, without the need to build an infrastructure for that or hiring different providers. Pix was not the exception.
In less than 3 months, the team was able to build and go live with a solution that can be used by both direct participants (i.e. those with banking licenses granted by BC) and indirect participants (i.e. companies depending on direct participants to offer payments on Pix) through Bit Capital’s platform.
Besides this differential, Pix and the startup’s other solutions are based on blockchain and Google Cloud’s cloud, ensuring greater security, scalability and availability for customers and allowing for an easy integration between Bit Capital’s platform’s solutions and those from other companies.
“Now we have a platform with an API and various microsservices, allowing clients to connect and develop their own financial product without having to start from scratch.”
—Francesco Miolo, CFO, Bit Capital
A robust structure to handle Pix’s high demand
Bit Capital was established with a certain urgency to develop its platform. The idea was well-developed and customers were interested. The fast deployment of Google Cloud’s tools was one of the main factors that attracted the company to use it as a basis for its infrastructure.
“We managed to deliver everything we have today in Google Cloud with a small team. That was another challenge: being able to grow with a few people,” says Juliano Souza, the startup’s head of IT infrastructure. “We sought other cloud partners, but they had a steep curve. We chose Google Cloud because we needed quick, quality scaling.”
The same thing happened when they built the solution for Pix, despite the specific challenges involved. The BC had performance requirements that led the company to spend some time experimenting until they reached the best suite of tools to meet those requirements. The startup wanted to create a unique architecture that could help both large and small customers and be integrated to the platform’s other microservices.
With the support from Google Cloud’s team to answer doubts and make the best decisions, the solution was built in a few months, at a cost about two-thirds lower than other providers.
The solution’s architecture is based on apps running in Docker in Google Kubernetes Engine (GKE), with interconnected microservices. The blockchain system runs on Compute Engine, its rows are managed in Pub/Sub and Dataflow, and persistent data, in Cloud SQL. Cloud Interconnect is used for the connection with BC. Cloud KMS and Secrets Management store the company’s pre-credentials. All of this is supported by Cloud Load Balancing for load balancing and autoscaling.
According to the team, GKE was essential for the solution’s success. Using infrastructure as code in the tool made uploading of a group of microservices significantly easier. Developers are able to help set up this environment, which has helped spread the DevOps culture in the team. Besides orchestrating and integrating apps, GKE also provides an elastic structure to handle demand peaks and visibility to monitor internal components.
“Google Kubernetes Engine was the only way to ensure availability, observability and elasticity for all clients, whether small, middle-sized or large.”
—Juliano Souza, Head of IT Infrastructure, Bit Capital
Nowadays, the solution’s environment has 20 clusters with over 1,500 pods – 250GB in data per month, providing a robust structure to support a service involving periods of intense demand such as Black Friday.
Ease to monitor, fix and improve
Just 10 days after Pix’s official launch date, the company had its first test: Black Friday. This allowed a major e-commerce customer to test the scale and see the success of the architecture that had been built. “It worked great regarding what the cloud could deliver. And we found what we needed to fix very quickly. Operations [formerly Stackdriver], in particular Cloud Trace, showed us clearly what needed to be done to improve performance,” Souza explains.
Using Operations added reliability by putting deliveries into production. Checking Cloud Trace to see if there were any performance issues and the exact point where they were happening became routine for Bit Capital’s developers. Google Cloud’s security tools and Google Safety Center provided the resources needed to monitor and secure the environment, with automatic data encryption at rest and in transit.
“Google Cloud has security as a premise. When we upload any kind of component, like a database or a virtual machine, the drive is encrypted by default. With other providers, you must specify that you want it encrypted.
”—Juliano Souza, Head of IT Infrastructure, Bit Capital
The easy service monitoring and management accelerates product and technology development because the team no longer needs to worry about infrastructure. Automated management allows professionals to spend more time on new projects and the company’s business. Also, the deployment of services in a Google Cloud multi-region impacts on customer experience, providing high availability for their solutions.
In the coming months, Bit Capital aims to ramp up service usage and the creation of new projects in Google Cloud by adding more customers to Pix’s solution and Banking as a Service (BaaS) solutions. Anthos will be incorporated to the tool suite to make it easier to connect apps with the customers’ on-prem environments. And the deployment of open banking has the potential to be a business driver for the company.
“We joke that we’re already doing open banking, because we have various connections with different providers, which allows us to offer an integrated solution,” says Francesco Miolo, the startup’s CFO. “With the arrival of the new regulations, something we are waiting for since we started out, we will be able, through our platform, to take our customers to the open finance ecosystem, enabling the development of disruptive business models.”
Google Cloud Celebrates Journey of 3 Inspiring Founders for the Asian Pacific American Heritage Month
8143
Of your peers have already read this article.
1:30 Minutes
The most insightful time you'll spend today!
May is Asian Pacific American Heritage Month —a time for us to come together to celebrate and remember the important people and history of Asian and Pacific Island heritage. This feature highlights three AAPI founders from the Google For Startups community.
Read on to learn how these three founders built their businesses, leverage Google tech, and suggestion they have for aspiring entrepreneurs.
CultivatePeople
Founder: Lola Han
Description: CultivatePeople’s compensation software, Kamsa, provides global market pay rate data and helps companies make data-driven salary decisions so they can attract and retain their most valuable asset: employees.
Why GCP: “CultivatePeople began using GCP when integrating SSO/SAML authentication after our SaaS product became a high priority. We were able to exceed our clients’ expectations and increase their level of trust in our platform’s capabilities. We’re currently investigating additional machine learning products, such as Cloud AutoML, that will allow us to quickly deliver exciting features.”
Note from the Founder: “I grew up with immigrant parents who value stability and are risk-averse. My parents discouraged me from starting my own company because they didn’t want to see me struggle financially or see my health suffer (due to stress). I felt strongly about what CultivatePeople could do, so I started the company as a sole founder in 2017 and watched it double in size year over year since. While the ones I love most may not have cheered me on initially, it was important for me to hang on to the encouraging words of former bosses, executives, and founders to keep me focused on my mission.
My advice for other AAPI founders is to be a “silent assassin” and believe in the mission and values of your organization. Always remember to stop along the way and:
1) Enjoy the journey by celebrating wins and giving yourself credit;
2) Follow your intuition—it’s (almost) always right;
3) Recognize and invest in your people regularly (ie. give increases more than once a year, if warranted);
4) Give regular words of affirmation to employees on even small achievements.”
Swit
Founder: Josh Lee and Max Lim
Description: Swit is a team collaboration platform that seamlessly combines team chat with task management by allowing teams to turn their conversations into trackable tasks and share tasks to chat with simple drag-and-drop functionality, ensuring everyone is on the same page and projects get done faster.
Why GCP: “Swit is a cross-category hybrid work tool for chat and tasks. This functionality requires more complicated and heavier architecture for performance. So, configuring and managing virtual machines was really challenging to scale up our systems, while handling occasional unexpected traffic surges and frequent updates. Eventually we divided our monolithic architecture into 35 microservices when we launched our official product. The migration to GKE took around one month, and it turned out to be well worth the effort—our systems became able to offer high scalability and enough resilience to keep its uptime no matter what happened. Now we’re operating 84 workloads and 252 microservices with high stability with remarkably low downtime – less than 0.00001%/year.”
Note from the Founder: “As an AAPI founder based in Silicon Valley, I feel proud of the work ethics and diligence fellow Korean American entrepreneurs and professionals have long demonstrated here. Especially with K-pop breaking into the mainstream, I feel even more proud of our culture that strives toward an absolute perfection molded through years of training and dedication. The mission-driven culture of Silicon Valley coupled with Google’s edging technology and creativity really helped us build a product that not only encompasses verticals but also transcends cultures. Swit is growing at an unprecedented rate, and we hope to join the long list of successful AAPI entrepreneurs here. Swit’s close network with the AAPI community wouldn’t have been possible without Google support. We are grateful for this collaborative environment, and we hope to become the next-generation ambassador for collaboration after Google.”
Check out more from Swit in their founder story.
WISY
Founder: Min Chen
Description: Wisy develops technology to bring digital efficiency into the physical world, supporting consumer products businesses and making them thrive in the new economy. All of us have a bad experience when we can’t find the product we want to buy. That is a $1.9T problem in the consumer-packaged goods industry that Wisy is solving with AI and analytics to help manufacturers and retailers sell more by reducing out-of-stocks and waste at a global scale.
Why GCP: “GCP has an intuitive, easy to use interface, was lower cost, and offered preemptible instances with flexible compute options. Some of the reasons why Wisy decided to use GCP include instance and payment configurability, privacy and traffic security, cost-efficiency, and Machine Learning.
Wisy has been able to advance quickly with product development, as well as collaborate better and iterate faster in the creation of our AI models, while reducing costs by 40%. At Wisy, we are solving a problem that affects everyone who shops at a store.“
Note from the Founder: “Two years ago, I moved to San Francisco to expand my second startup, Wisy. This is when I learned that my name ‘Min’ stands for ‘minority.’ I was born in China, raised in a Black community in Panama, received scholarships to attend both Carnegie Mellon and UC Berkeley. I worked for 20 years in several countries, but I have never felt so discriminated against due to my race, ethnicity, gender and age than during my time in Silicon Valley. However, this is also the place I learned that my diverse life experience is my competitive advantage. My background enables me to recruit and relate to people in different countries, create scalable and flexible products for multinational customers, and run global operations efficiently.
My recommendation to AAPI founders is to find strength in their multicultural background. Don’t hide what makes you unique, do not limit yourselves, and do not let others limit you. You will lose your edge when trading authenticity for validation. Be proud and own your story.”
If you want to learn more about how Google Cloud can help your startup, visit our Startup Program application page here and sign up for our monthly startup newsletter to get a peek at our community activities, digital events, special offers, and more.To learn more about how you can help #StopAsianHate during Asian Pacific American Heritage Month and beyond, visit their website here.
More Relevant Stories for Your Company
How TeamSnap Improved Return on Ad Spend Significantly
Anyone who has ever coached or played on a sports team, or had a child involved in sports, knows how difficult scheduling and logistics can be. From game and practice schedules, to uniforms and who’s bringing the snacks, it can be a lot for coaches, administrators, parents, and players to
Build Open Data Platform on GCP with Delta Lake, Presto and Dataproc
Organizations today build data lakes to process, manage and store large amounts of data that originate from different sources both on-premise and on cloud. As part of their data lake strategy, organizations want to leverage some of the leading OSS frameworks such as Apache Spark for data processing, Presto as a query engine and
The Real Drivers of Efficiency, Growth, and Customer Experience
The increasing adoption of technologies like connected devices, augmented reality, and machine learning has changed the way we shop, and retailers are evolving how they do business to meet the needs of their customers. Retailers say it’s no longer enough to keep pace with shoppers’ growing expectations—they must get ahead
Haaretz on Google Cloud Guarantees Faster, Reliable & Responsive Services to its Audience
Israeli centenarian newspaper, Haaretz relied on on-prem infrastructure to serve readers digitally. As the need for scalability, security and business intelligence grew alongside their readership, Haaretz was looking for more than just a cloud-based solution to replace their infrastructure. Inon Gershovitz, CTO, Haaretz takes us through the journey of recreating
