Articles | Open Access | DOI: https://doi.org/10.37547/tajiir/Volume07Issue07-04

A Five-Layer Framework for Cost Optimization in Snowflake: Applied to P&C Insurance Workloads

Shreekant Malviya , Tata Consultancy Services, Plano, Texas, USA

Abstract

The use of Snowflake as a cloud-native data warehouse has dramatically changed the management of analytics workload for Property and Casualty (P&C) insurers, while simultaneously presenting serious cost governance challenges. The heavy volume of searches, big data retention, and decentralized business intelligence operations are industry-standard procedures that tend to lead to uncontrolled credit usage and overspending on storage. This research introduces a modular five-layer optimization framework focused on property and casualty insurance data, combining workload segmentation, and compute sizing with Snowflake's account usage metadata. The framework is tested and validated using Kaggle’s Insurance Agency Data, representing real-world P&C operations across 17 states. Benchmark queries simulating core insurance workloads were designed using modified TPC-H logic, a standard decision support benchmark that enables realistic performance evaluation under analytical query conditions, achieving up to 82% cost reduction and a 64% reduction in execution time without compromising the results. These results highlight the efficiency of the framework to facilitate proactive and elastic cost control. Future studies can investigate AI-driven query forecasting, scalable warehouse dynamics, and real-time anomaly detection to further advance cloud-native data ecosystem governance.

Keywords

Snowflake Cost Optimization, Property & Casualty Insurance Data Workloads, Metadata-Driven Cost Control, Query Performance Tuning

References

Cyber Security Senior Data Analyst, Department of Cyber Security, Truist Financial, CA, USA and D. Kodi, “Performance and Cost Efficiency of Snowflake on AWS Cloud for Big Data Workloads,” Int. J. Innov. Res. Comput. Commun. Eng., vol. 12, no. 06, Jun. 2024, doi: 10.15680/IJIRCCE.2023.1206002.

D. Mazumdar, J. Hughes, and J. Onofre, “The Data Lakehouse: Data Warehousing and More,” 2023, arXiv. doi: 10.48550/ARXIV.2310.08697.

“The Cost of Redundancy.” Accessed: Jun. 15, 2025. [Online]. Available: https://www.highwing.io/insights/the-cost-of-redundancy

A. Pimpley et al., “Optimal Resource Allocation for Serverless Queries,” Jul. 19, 2021, arXiv: arXiv:2107.08594. doi: 10.48550/arXiv.2107.08594.

“Insurance Data.” Accessed: Jun. 15, 2025. [Online]. Available: https://www.kaggle.com/datasets/moneystore/agencyperformance

“TPC-H Homepage.” Accessed: Jun. 15, 2025. [Online]. Available: https://www.tpc.org/tpch/

K. Allam, “Cloud Data Warehousing: How Snowflake Is Transforming Big Data Management”.

“Multi-cluster warehouses | Snowflake Documentation.” Accessed: Jun. 15, 2025. [Online]. Available: https://docs.snowflake.com/en/user-guide/warehouses-multicluster?utm_source=chatgpt.com

“(5) Snowflake’s Multi-Cluster Shared Data Architecture: Scalability, Performance & Cost Optimization | LinkedIn.” Accessed: Jun. 15, 2025. [Online]. Available: https://www.linkedin.com/pulse/snowflakes-multi-cluster-shared-data-architecture-scalability-anuj-r--nbi9f/

D. A. S. George, “Deciphering the Path to Cost Efficiency and Sustainability in the Snowflake Environment,” Partn. Univers. Int. Innov. J. PUIIJ, vol. 01, no. 04, pp. 231–250, Aug. 2023, doi: 10.5281/zenodo.8282654.

D. Seenivasan, “OPTIMIZING CLOUD DATA WAREHOUSING: A DEEP DIVE INTO SNOWFLAKE’S ARCHITECTURE AND PERFORMANCE,” Mar. 31, 2021, Social Science Research Network, Rochester, NY: 5148190. doi: 10.2139/ssrn.5148190.

“Snowflake Documentation.” Accessed: Jun. 15, 2025. [Online]. Available: https://docs.snowflake.com/

X. Zeng, Y. Hui, J. Shen, A. Pavlo, W. McKinney, and H. Zhang, “An Empirical Evaluation of Columnar Storage Formats,” Nov. 07, 2023, arXiv: arXiv:2304.05028. doi: 10.48550/arXiv.2304.05028.

T. Koreeda, H. Honda, and J. Onami, “Snowflake Data Warehouse for Large-Scale and Diverse Biological Data Management and Analysis,” Genes, vol. 16, no. 1, Art. no. 1, Jan. 2025, doi: 10.3390/genes16010034.

D. M. Compagnoni, “Optimize Snowflake performance and reduce credit usage,” Nimbus Intelligence. Accessed: Jun. 15, 2025. [Online]. Available: https://nimbusintelligence.com/2024/10/5-ways-to-optimize-snowflake-performance-and-reduce-credit-usage/

“Fundamentals of Snowflake Query Design & Optimization | Keebo.” Accessed: Jun. 15, 2025. [Online]. Available: https://keebo.ai/2024/10/29/fundamentals-of-snowflake-query-design-optimization/

JayaAnanth, “Part 2 - Orchestrating Snowflake Data Transformations with DBT on Amazon ECS through Apache Airflow,” JayaAnanth. Accessed: Jun. 15, 2025. [Online]. Available: https://jayaananthdevops.github.io/posts/snowflake_dbt_ecs_part2/

“FinOps Principles.” Accessed: Jun. 15, 2025. [Online]. Available: https://www.finops.org/framework/principles/

“Understanding Data Warehouse Cost & Pricing Models | Rivery.” Accessed: Jun. 15, 2025. [Online]. Available: https://rivery.io/data-learning-center/data-warehouse-costs/

C. Wang, Z. Arani, L. Gruenwald, and L. d’Orazio, “Adaptive Time, Monetary Cost Aware Query Optimization on Cloud Database Systems,” in 2018 IEEE International Conference on Big Data (Big Data), Seattle, WA, USA: IEEE, Dec. 2018, pp. 3374–3382. doi: 10.1109/BigData.2018.8622401.

V. Leis and M. Kuschewski, “Towards cost-optimal query processing in the cloud,” Proc. VLDB Endow., vol. 14, no. 9, pp. 1606–1612, May 2021, doi: 10.14778/3461535.3461549.

P. Bhardwaj, “The Role of FinOps in Large-Scale Cloud Cost Optimization,” INTERANTIONAL J. Sci. Res. Eng. Manag., vol. 09, no. 01, pp. 1–5, Jan. 2025, doi: 10.55041/IJSREM28086.

Article Statistics

Downloads

Download data is not yet available.

Copyright License

Download Citations

How to Cite

Shreekant Malviya. (2025). A Five-Layer Framework for Cost Optimization in Snowflake: Applied to P&C Insurance Workloads. The American Journal of Interdisciplinary Innovations and Research, 7(07), 28–43. https://doi.org/10.37547/tajiir/Volume07Issue07-04