Blog
Data Warehouse Architecture Best Practices
31 July 2019
Want to read a couple of mind-blowing statistics? Over 2.5 quintillion bytes of data are created every single day, and by 2020, researchers estimate 1.7MB of information will be created every second for every person on earth. All of this information has to go somewhere, preferably someplace where it can be analysed easily.
That’s where the data warehouse comes in but not all data warehouses are created equally; you need to follow data architecture best practices to get the analytics that meet your business needs well.
Data Warehouse Architecture: The Basic Structure
There are a few data warehouse architectural options you can explore but all contain these four must-have components regardless of your architectural choice:
- The data source layer – where the original information “lives” in the database
- The data staging layer – where information is extracted and transformed so it’s ready for the data warehouse
- The data storage layer – where information is stored
- The data presentation layer – where end-users interact with information
What Are Your Data Warehouse Architecture Options?
You have four basic data warehouse architecture options:
- Top-down
- Bottom-up
- Hybrid
- Federated
Top-Down
In the top-down approach, the data warehouse is the centre of the analytic environment. From the data warehouse, information is distributed to data marts (a subject-specific repository of information).
The reason it’s called a “top-down” approach is that the data warehouse is the departure point for all information and as a result, information is consistent and standardised. However, because the top-down approach requires a detailed enterprise data model as well as the physical infrastructure for the staging area, data warehouse, and marts, it’s more expensive than other architectural choices.
Bottom-Up
Data marts are deployed quickly through the bottom-down approach. The idea is that these marts will contain all of the information users will ever want or need. Each data mart builds on the next one, so users can query information across marts for comprehensive results.
The bottom-down approach tends to be less successful in distributed, decentralised organisations because business units or departments are inclined to create independent marts that aren’t integrated.
Hybrid
A hybrid model combines the best of the top-down and bottom-up approaches. It relies upon an ETL to store and manage local models in data marts while synchronising differences between them. After you deploy a few dependent data marts, you’ll flesh out the data warehouse by back-filling it with information.
The benefit of a hybrid approach is that it’s fast, although back-filling a data warehouse is a disruptive process that doesn’t really deliver value. Also, users might get confused about which data mart to query.
Federated
The federated approach is all about integration. It involves integrating a variety of heterogeneous architectures into one for greater simplicity. Some call it the “architecture of architectures,” because it involves unifying a bunch of disparate architectures into one.
One of the shortcomings of the federated approach is that it’s not well-documented. Moreover, it encourages fragmenting analytics, so you don’t have a single organisational view.
Data Warehouse Best Practices
When creating the architecture for your data estate, keep in mind the following data warehouse best practices.
For a start, think about your future business needs, rather than focusing on what’s happening right now. Experts say that data warehouses can take several years to deliver ROI, so carefully consider what your requirements will be down the road, and what possible technologies are in the pipeline so you’re not locked into an outdated approach.
Second, data quality matters enormously. If your data isn’t clean and accurate, you’ll have problems later on. Errors tend to creep in at the transactional level. If someone doesn’t enter information correctly before it even goes into the data warehouse, it will have a negative impact on analytics.
Third, you must think of creating a data warehouse as a business-wide initiative, rather than just something the IT department is doing. The point of a data warehouse is to benefit the whole organisation; it enables effective data management, which saves time, effort, and money. Moreover, you’ll get deeper insights into your information, so you can make better business decisions.
Azure SQL Data Warehouse: Putting Data Architecture Best Practices into Play
The Microsoft Azure SQL Data Warehouse helps you put best practices into play at your firm. One of the benefits of using the Azure SQL Data Warehouse is that the partner ecosystem is huge. It spans data preparation providers, ingestion services, and visualisation tools. Further, the ecosystem will just keep growing, meaning that the data warehouse will only become more useful with time.
In May 2018, Microsoft announced that the Azure Databricks Service would be available. This Apache-based analytics service allows you to develop ETL processes, otherwise known as data cleaning and also has built-in support for the Azure SQL Data Warehouse.
A third advantage of using the Azure SQL Data Warehouse is that it allows you to democratise data throughout the enterprise. This means workers across the company have access to information that they didn’t have before, which can lead to better decision making and new opportunities.
Although the Azure SQL Data Warehouse makes information more accessible, it doesn’t sacrifice availability for security. The data warehouse features auditing, threat detection, and data encryption (among other features) so that your information is always safe.
Enlighten: Helping You Implement Data Warehouse Architecture Best Practices
Enlighten is a proud Microsoft partner, winning two awards in 2017, including one for data platforms and analytics. We have over 20 years of experience helping our customers get the most out of their data. To learn more about implementing data warehouse architecture best practices at your company, contact us today
Recent Articles
Want to stay updated with what we are doing?
Subscribe to our newsletter for our ideas about development, marketing, and technology. See our latest work, find out about career opportunities, and stay notified about upcoming events.
Subscribe to our newsletter
