Optimizing Data Warehouse Deployments

Distributed Databases

A distributed database is an architectural model that spreads operational data across many different computing environments. Each environment comprising a database that’s part of a distributed system is referred to as a node or an instance. 

Distributed databases can be a powerful driver of performance because they’re fundamentally elastic and enable high degrees of horizontal scalability. Having multiple distributed databases ensures that no single system is overwhelmed by workloads. Furthermore, if a system were to crash, companies can remediate quickly and avoid extended periods of downtime because identical replications of data from a primary node would exist in other nodes. 

The data engineering challenge to optimize performance via distributed databases involves:

  • Query optimization and atomicity 
  • Choosing one of many kinds of distributed databases like relational (SQL) databases and nonrelational (NoSQL) distributed databases
  • Configuring database replicas to ensure data availability 

Data engineers should ensure that distributed database systems have a few integral qualities such as fault tolerance, data replication, and location transparency. 

The optimization of distributed databases can significantly augment the entire data engineering pipeline and enable a company to operate without the fear of downtime, data unavailability, a lack of modular growth options, or unreliability.


Partitioning data is a powerful approach to improve scalability, streamline data management, and optimize performance for data-related activities. It also brings a broader and more robust array of disaster recovery options. Partitioning involves data distribution across multiple tables, helping companies manage their data more effectively and optimize enterprise-wide query processing performance. 

Data engineers need to choose the right approaches to partitioning data. Sharding is a type of horizontal partitioning that involves the splitting of a single dataset into a series of “shards” (partitions or tables) that have a shared schema. Vertical, or columnar partitioning, involves partitioning based on columns. And functional partitioning aggregates data according to a bounded context to help increase the speed and ease of data access for specific business areas. 

Although all three of these are common approaches, data engineers don’t have to choose between horizontal, vertical, or functional partitioning. Instead, they can design a combination of any or all of them to best suit their company's intricate requirements and surpass scalability, data availability, and query performance expectations.


Database indexing facilitates the process of retrieving data from a database. Robust database indexing ensures prompt and accurate responses to a data-related query, which is required in a world where data is collected and processed in enormous volumes. There’s no other way to pull out valuable data from overwhelming large databases. 

There are numerous types of indexes. Firebolt, for example, supports three types: primary (clustered), join (non-clustered), and aggregating (non-clustered). Rows in a clustered index follow the same sequence as the rows of the index itself, while a non-clustered index is like a direct pointer to a data source. Aggregating indexes store a pre-calculated result of an aggregate function, which is useful when the computation is expected to be performed repeatedly.

Primary indexes are more efficient because they are sparse with queries and a speedy way to search and retrieve data. Join indexes accelerate queries that include Joins since they are cached in engine RAM. Aggregating indexes help with the performance and cost of aggregation, as well as the integration of complex and multidimensional data into simpler, more accessible formats.

One of the ways to optimize performance via indexing is to ensure that only the exact data relevant to a query is scanned. Scanning entire tables can be expensive, inefficient, and time-consuming. Methods to bypass full-table scans include pruning down tables into smaller subsections that allow for faster and more accurate data access. Data engineers should approach pruning strategically and meticulously, as pruning for column-based indexes and row-based indexes is executed in different ways. 

The benefits of indexing are multifold. Accessing and retrieving data at high speeds will enrich every aspect of an enterprise’s operations. However, data engineers must balance those advantages with some inevitable trade-offs. For example, database indexes take up storage space; therefore, it’s important that data engineers have visibility of all indexes so they can delete irrelevant and redundant ones. Also, any time you change a row, you will need to update the index, which can decelerate database writes. 

Some of these compromises are necessary. Others might cause long-term inconveniences. Data engineers can achieve serious long-term advantages for their companies if they can identify the perfect win/loss ratio with indexing and its corresponding hurdles. 

Cost Management of Data Warehousing

Cost management and optimization is critical in cloud data warehousing. The biggest challenge is striking the proper balance between infrastructure cost, skills (employees and teams) requirements, and organizational practices. The biggest costs of a cloud data warehouse include upfront design and implementation, storage, and compute, as well as an array of data ingestion, automation, and BI tools and mechanisms. 

The two baseline cost components of a cloud data warehouse are computing and storage. Most cloud data warehousing providers typically charge for computing per second, minute, or hour and then also for storage per TB. Beyond these two critical elements, there are a variety of additional features that providers offer as buyable add-ons. Companies need to have absolute clarity on the cost structures of cloud data warehouse providers, as it is not difficult to accidentally empty a portion of the IT budget on a feature or add-on that is not essential. 

Different cloud providers offer different cost models, and companies must analyze these with a critical eye. Certain cost models may appear alluring at first glance, but upon closer inspection, they might reveal that they require third-party tools to function usefully. 

Furthermore, companies should leverage technologies that run on common languages like SQL to ensure that data projects can be staffed with ease. The global IT skills shortage has been a concern for a few years now, and the problem doesn’t seem to be receding. Therefore, any model that requires specialized skills can add to the cost. 

Businesses should also never lose sight of their specific use case for cloud data warehousing. There’s no need to commission new and exciting technologies if it doesn’t have a direct impact on their performance and profitability. Cost management for cloud data warehousing is about knowing your needs as much as it is about understanding the cost-related intricacies of cloud-based data engineering.

Top 5 Ways to Optimize Costs for Cloud Data Warehousing

  • Leverage efficient technologies to minimize compute consumption. While there are no simple answers, leveraging capabilities like elastic scaling, auto-pause, and effective data pruning through indexing can help optimize compute costs. Additionally, choosing the optimal pricing models (such as reserved instance pricing or spot pricing) and setting up mechanisms to alert you of options to right-size instances can help manage compute costs. Granular provisioning to match workloads requirements should always be a top priority.
  • Compress data wherever possible to reduce costs for cloud data storage and processing. Columnar storage on top of object storage can help reduce the cost of storage consumption. 
  • Data lifecycle management and governance to review data retention and relevance can help reduce the amount of data management. Identifying and eliminating redundant data processes, defunct pipelines, duplicate data can help streamline data warehousing.
  • Performance and Workload optimization is a process of continuous improvement. A poorly written query that repeatedly scans terabytes of data can result in tens of thousands of dollars in unforecasted spend in a pay-per-TB-scanned pricing model. 
  • Make cost optimization a constant and ongoing process across the organization, not just something that’s done at certain scheduled intervals. Only through constant vigilance can businesses sustainably save money while simultaneously improving their operations. 

A Brief Guide to Setting Up an AWS Cloud Data Warehouse on Firebolt

  • The first step is to sign up for an AWS account.
  • New users will have to click “Sign Up” on the lower right of Firebolt’s login page. Complete the verification email and subscribe to complete the Firebolt account creation process.
  • Log in to Firebolt and create a database in the region of your choice. Then, you will be able to configure the infrastructure to support their database.
  • You can review the “Getting Started” page for instructions to rapidly ingest data into Firebolt and start querying.
  • This starts the process of configuring a data warehouse on Firebolt. You can scale and manage the infrastructure from the SQL IDE, which lets you configure engines, databases, users, and role-based access, to name a few.
  • To connect a database to Firebolt with a business intelligence tool, simply follow the instructions on the integrations page.

Next: Summary

Continue reading
Send me as pdf