My secret power to data intelligence

Azure SQL Data Warehouse

I already started covering the topic of managing and storing data in Azure. There is a complete process of building data flow from the source, through ETL engine, and at the end visualize it. I would call this a receipt to intelligence. Having the right storage for your data is essential. It must be powerful and organize data the way you need. Once this part is done there will be just a matter of time to rip the benefits.

Azure SQL Data Warehouse is a cloud version of the SQL Server Enterprise Data Warehouse. Microsoft is already working on the newest SQL Server 2019 version, which seems very powerful. I will explore this in a separate article.

SQL Data Warehouse is not just simple storage. You should treat this as an engine for parallel data processing. It can manage massive volumes of data. If you have petabytes of data then there are no worries, it can manage it.

There is a cool announcement that Microsoft made on the documentation site.

Querying petabytes of data become achievable with minimal effort. It’s a key component for integrating it with big data solutions. You can easily integrate it with big data stores that allow providing insights. Once in a big data store, Hadoop, Spark, and machine learning algorithms prepare and train the data.

It may look as if it has a relationship to the SQL database in Azure. Don’t be fooled it is majorly different. There is a difference between SQL Database and Azure Data Warehouse. It has been designed to manage huge volumes of data and parallel processing. But it will not work very efficiently with OLTP workloads. If you are looking for a database to support your web application then SQL Database is your choice. Data Warehouse has a node based architecture. Computation is done across multiple nodes. SQL Data Warehouse separates compute from storage which enables you to scale compute independently of the data.

Data Loads

The most efficient way to get data into SQL Data Warehouse is to use Azure Data Factory. Because of seamless integration with the whole environment. Ultimately it is the best choice. This service is ideal for the Extract Transform and Load process. There are alternatives but there are outside Azure. There are two solutions one by Redgate and another by Informatica. You can evaluate them, for me the no brainer solution is Data Factory.

Data load is best managed with batch jobs. You will most likely loads large data volumes so batch jobs will be very efficient in most scenarios.

Data model

Having access to Data Warehouse you can run complex queries. They work on multiple tables providing information by detecting relationships between them. Having this relationship is very important because they will represent your business. Your developers must put extra attention into building data models that represent your company. Having this right will create benefits later on when running complex queries. They will improve queries results proving correct answers. This is especially important in advances analytics like machine learning. Where data quality is the most important to get correct results.

You have to remember that the process of data loading should be automated as much as possible. This will improve the quality of the solution. By creating a process to manage data loads you will create a resilient solution. There are several different ways to automate processing and controlling the Azure Data Warehouse.

A very useful feature is geobackup. Since data is a business asset you must protect it from unexpected events. When you apply geo backup data will be sent to a paired region. That way you protect data reducing the possibility of losing it.

Analytics

Azure Data Warehouse has full capabilities to deliver information for business analytics. Highlighting this again; a very important feature is parallel data processing. Which means running complex queries (questions to a database) will result in fast response. SQL Data warehouse stores data in relational tables with columnar storage. This feature translates to fast query response. As they say “time is money” this means the quicker you get information the faster your decision will be.

Additional benefits are near real-time analytics by taking advantage of Azure Databricks streams of data. You need to remember that all analytical services will integrate with Azure Data Warehouse. Please remember that you can use Azure advanced analytics services with your Data Warehouse.

Security

Azure Data Warehouse has a security mechanism built in. There is threat detection and build in auditing capabilities. It will detect when strange or unusual logging happens. You can check if there are attempts to login from a suspicious location or IP address.

When you look at the admin panel you will see three options relating to security.

Advanced Data Security. Priced at 12.6495 EUR per month seems cheap for what it can offer. You will get an alert when the service will detect suspicious activity.
Auditing. All automatic logs can be sent to Storage, Log Analytics or Event Hub. After that, you can analyze them.
Transparent data encryption. Encrypts your databases, backups, and logs at rest without any changes to your application.

Costs

When you want to know how much it cost to operate, you should know what is DWU – Data warehouse unit: an abstraction of required computation to perform a calculation. It is an aggregation of CPU, RAM, IO resources and operations that were used to deliver information.

Business costs will be affected by how much storage and processing is required. Generally, storage is cheap but computation is not. There must be a lot of thought put in place to make sure the design of your solution is optimized. Azure Data Warehouse can dynamically scale to meet the increased demand for processing.

Optimization

Part of your development practice should go towards costs management. Data loads and processing must be optimal. It will benefit in fast processing and when managed correctly it will decrease costs. Optimization of queries and data model is the highest priority here.