Data Warehouse System Requirements
Hardware and software requirements for cloud, hybrid, and on-premises data warehouses/data marts.
DataSelf Cloud Data Warehouse
There are no system requirements. DataSelf will securely host your data warehouse(s) in our AWS or Azure servers.
On-Premises or Private-Cloud Data Warehouse
Please use the following guidelines when hosting your data warehouse on your own MS SQL Server:
MS SQL Server versions: 2022, 2019, 2017, or Azure SQL. It can work with prior SQL versions, but limitations might apply.
MS SQL architecture:
Server or serverless, on-premises or cloud.
Azure SQL (click here for more info).
MS SQL Editions: any.
SQL authentication supported: Windows, SQL, Azure Entra ID Password.
Guidelines for hardware specs:
Data space (HD or cloud space): In most deployments, the data warehouse data space will be 5% to 20% of the source database data space (source’s data space without logs). For server-based SQL, we recommend using SSD with at least 200GB free or 5+ times the expected data warehouse data space, whichever is the largest.
CPU and RAM: Here are guidelines based on the expected data warehouse data space:
Up to 4GB: 2+ CPU threads, 2GB+ of RAM. Azure SQL eDTUs: 100 to 200
Up to 16GB: 4+ CPU threads, 8GB+ of RAM. Azure SQL eDTUs: 200 to 300
Up to 32GB: 4+ CPU threads, 16GB+ of RAM. Azure SQL eDTUs: 300 to 800
Up to 128GB: 8+ CPU threads, 64GB+ of RAM. Azure SQL eDTUs: 400 to 1,600
Up to 1TB: 16+ CPU threads, 256GB+ of RAM. Azure SQL eDTUs: 800 to 3,000
Above 1TB: contact us.
RAM and Hard Drive (HD) swap considerations:
SQL runs efficiently when queries can be performed within the available RAM. Otherwise, SQL might use HD swaps and performance degradates.
For fast performance, allocate at least twice as much RAM for SQL as the size of the largest table to be hosted in the data warehouse. For instance, allocate at least 16GB of RAM for SQL if the largest data warehouse table will have up to 8GB of data.
For top performance, allocate more RAM than the total size of the data warehouse.
For server-based SQL, have plenty of disk space for your SQL app and databases. It can be very time consuming to fix if one of these disks run out of space.
Recovery model: Simple is enough and will be the fastest model. Using a Full recovery model is OK, but it might affect performance, especially during data warehouse uploads and transformations.
Compatibility level: Latest available.
Collation: the same as your source systems', if applicable. Our default is SQL_Latin1_General_CP1_CI_AS.
ETL+ requires db_owner role for the MS SQL database hosting the data warehouse.
Recommended to set up SQL with an SSL certificate,
Encrypt connection, and encryption its HD at rest.Data Warehouse Indexes/Keys: Unlike OLTP systems, use the following guidelines for a well-designed environment:
ETL+ automatically manages indexes for tables using delta refreshes.
By default, most tables do not require additional indexes.
Indexes become valuable on large tables especially when filtered with
WHEREconditions.Targeted indexing should be considered when performance issues arise, rather than added preemptively.
Related Pages
Keywords: Data Warehouse Minimum System Requirements, Data Warehouse Specs, Data Warehouse Specifications