Databases and Data warehouses: The need for a single source of truth

Aspiring data scientists are keen on getting hold of data in excel files and start experimenting with machine learning algorithms.

However, the world of data is messy and getting data into a structured format that could be mined and analyzed isn’t a very straightforward process. You won’t just be handed data in an excel file and asked to build models. You will have to bear the pain of acquiring data from multiple systems, cleaning and formatting it and only then can you proceed with your analysis.

The need for a single source of truth

A business comprises various divisions or departments: sales, marketing, finance, HR. Each department could be using its own system to generate its data, e.g. the sales team might be using Salesforce to keep track of prospects and clients, the marketing team could be using Hubspot or Marketo to run campaigns, the accounting team could be using an ERP system like SAP to log all the financial data. A business, however, would like to have a single centralized repository where it could dump all its data in a structured format. This repository could then act as a single source of truth from which data can be retrieved to generate reports, build dashboards or build machine learning models for predictions/ forecasting that could be leveraged to improve business decision-making.

What is a database?

We can define a database as an organized collection of structured data which makes it easy to access, manage and update data. An Excel spreadsheet is a database and so is a telephone directory. You might have heard the names MySQL, MSSQL and Oracle among others. They are all popular Database Management Systems (DBMS) that can store and modify data in their respective databases.

The AI & Analytics Engine lets you seamlessly upload your data from multiple sources. It supports multiple file formats such as CSV, MS SQL, MySQL, Postgre SQL, Oracle, MongoDB and others.  Therefore, you can easily connect your database to the Engine to fetch your company's data and start extracting useful business insights.

What is a data warehouse?

A data warehouse fetches, aggregates and summarizes data from multiple systems within an organization for reporting and analysis. It stores historical data, thus acting as a single source of truth for a company. A data warehouse can be connected to one or more databases and usually has the following elements:

• a relational database

• an ETL (Extract, Transform, Load) tool

• reporting & analytics capabilities

From database to data warehouse via ETL

In a typical scenario, a business initially collects data in an operational or production database. As stated earlier, each department within a business might have its own internal system/ database to generate data (CRM, ERP, Excel etc). Hence, the need arises to combine and organize the data residing in different database systems and present a unified, coherent view to the end-user. This is called data integration, which is performed by a process known as ETL. An ETL tool copies data from multiple disparate systems and transforms it into a common format and then finally writes it to a single database that is called a data warehouse.

The illustration below explains the flow of data from multiple systems to a single data warehouse from where it is retrieved to serve different purposes such as reporting, visualization, BI, statistical modelling, etc.

Wrapping up

As a data scientist, you should know the difference between a database and a data warehouse. Proficiency in SQL is highly desirable as it empowers you to write queries to filter and fetch the desired data. Data is initially collected in an operational or production database. Each department within a business can have its own database. Data from multiple systems are combined and transformed through ETL and then written to a single destination that is called a data warehouse. The primary goal of a data warehouse is to support the decision-making needs of the enterprise. Business users access data from the data warehouse to carry out detailed analyses.

Not sure where to start with machine learning? Reach out to us with your business problem, and we’ll get in touch with how the Engine can help you specifically.