Data warehouse design is crucial in deploying a data warehouse that serves the needs of all stakeholders. Some common data warehouse design considerations include:
Data Modeling
Data modeling defines how data structures are accessed, connected, processed, and stored in a data warehouse. Data sources are identified during this step in data warehouse design process, including where necessary data sets live and their availability. Once key data sources have been identified, the design team can build the physical and logical structures based on requirements.
ETL
ETL processes can be time consuming but identifying data sources during the data modeling step can help shorten ETL development time. Optimized load speeds while retaining high data quality is the end goal. Failure to get ETL right can end up impacting data warehouse performance down the road.
Query Tuning
Query optimization and tuning involve choosing adequate optimization techniques in order to make queries and updates run faster. In addition, it seeks to maintain high performance by maximizing data warehouse resource use. Using frequently asked queries and typical updates as guides, data warehouse administrators select optimization techniques such as materialized views, advanced index schemes, parallel processing, denormalization, and vertical and horizontal partitioning.
Snowflake and Data Warehousing Design
What data warehouse modeling approach does Snowflake support best? Snowflake's platform supports various data modeling approaches equally.
Snowflake also uses online analytical processing (OLAP) as a foundational part of its snowflake database schema. Learn more about Snowflake architecture and data modeling.