How to Generate a Schema for a New Data Warehouse

New Year’s Resolutions 2019: Enterprise Goals for the Cloud


State of Cloud Computing: What We Learned at Gartner Data and Analytics Summit


Once your enterprise has made the decision to migrate its database to a cloud data warehouse, there are a few steps that need to be taken to make sure your existing applications keep running smoothly. This blog post will focus on clearing up one of the most important steps by describing how to generate a schema for a new data warehouse.

The steps to replatforming are:

  • Get a good idea of what your workloads actually look like, how many unique statements you have, and how much of your existing stack is compatible with the destination data warehouse. An automated database workload analysis tool is handy for this.
  • Generate a new schema on the target data warehouse that matches your existing one – or is close enough that minimal changes will have to be made to the applications.
  • Replicate all the features and tools that the new database doesn’t have (like stored procedures.)
  • Migrate your data to the new data warehouse and connect it to your applications.

Why Do You Have to Generate a New Database Schema?

A database schema is similar to the headers on an Excel table. If your application needs to display someone’s username, or a customer’s value, or the date of a transaction, it knows it needs to look at the corresponding fields: username, customer_value, or transaction_date, to give a few hypothetical examples. The data stored in each field also needs to be in the correct format (string, text, date, etc.)

When you move from one database to a new database, the schema may be different, and may have limitations or differences that need to be accounted for. For example, if your application calls for first name and last name fields, and your new database only has full name, that’s going to cause issues.

Generating a new schema requires the following:

  • Finding out all of the fields your applications need to function, and all of the fields your business uses in general.
  • Creating those fields in the new database’s schema so that they match perfectly.

Some database schemas are case sensitive, and others are not, so that needs to be taken into account.

The most common way enterprises generate a new schema is to hire consultants to do it, and they may take six months to a year and charge several hundred thousand dollars.

There is also a newer way to do it – using a special piece of software that automatically generates the new schema – taking all of the aforementioned considerations into account.

How to Generate a Schema for a New Data Warehouse Using Software

Datometry qShift is one such tool. Using qShift, you can automatically convert the legacy data warehouse schema into the corresponding schema for a cloud data warehouse or any modern data warehouse of choice. qShift takes into account all of the existing objects in the source data warehouse schema and generates the appropriate DDL queries for the destination data warehouse.

Using qShift, you can save months of manual specialized effort, save a significant amount of money, and remove most of the risk from the process. Best of all, it’s completely automated, and transparent. Combined with software like Datometry Hyper-Q, qShift can significantly reduce the overall time and effort required to replatform.

If your enterprise has decided to move to the cloud, you should consider taking the approach with the least risk, the fastest completion time, and the lowest cost. Manual replatforming projects involve rewriting your entire application stack from scratch, and very few enterprises have done this successfully. Gartner estimates that over 60% of replatforming projects will fail, go over budget, or run late. Despite that, according to a recent survey, 67% of enterprises are actively moving to the cloud or are planning to. Moral of the story: The cloud is worth it.

Learn more about Datometry qShift and Hyper-Q for replatforming. Already at the point where you need to generate the schema? Get started with qShift and begin your journey to the cloud on the right foot.

7 things every IT leader should know before moving analytics to the cloud
Download PDF
Author avatar

About Mike Waas


Mike Waas founded Datometry with the vision of redefining enterprise data management. In the past, Mike held key engineering positions at Microsoft, Amazon, Greenplum, EMC, and Pivotal. He earned an M.S. in Computer Science from the University of Passau, Germany, and a Ph.D. in Computer Science from the University of Amsterdam, The Netherlands. Mike has co-authored over 35 peer-reviewed publications and has 20+ patents on data management to his name.

Continue Reading