Our client, a leading US solar services provider enhanced their data ecosystem by switching from Snowflake to Google BigQuery for improved scalability, performance, and centralized access. Over six months, 2000+ tables and 100+ procedures were migrated, overcoming challenges such as text handling and tooling limits. Robust ETL workflows and validations provided a smooth, timely transition.
The migration from Snowflake to BigQuery posed several hurdles due to the scale and complexity of the data environment.
Snowflake included datasets from Salesforce, NetSuite, and analytics, making it difficult to centralize and migrate these diverse data sources. The approach required precise management to guarantee data integrity and analytical functionality following the migration.
Informatica Intelligent Cloud Services (IICS) defaulted to STRING(MAX) for text columns in the target schema, leading to storage and query performance inefficiencies.
IICS Mass Ingestion lacked native support for Snowflake as a source, creating a significant hurdle in the migration process. This limitation required finding alternative solutions to ensure seamless data extraction and integration.
Migrating tables with over 100 million rows and more than 500 columns required effective partitioning to avoid performance bottlenecks.
Aligning Snowflake table names with BigQuery’s best practices presented the challenge of maintaining consistency while ensuring traceability. The naming convention had to be carefully developed to meet BigQuery’s requirements without losing reference to the original structure.
With over 200 procedures and views in Snowflake, many of which were obsolete, the challenge was ensuring that these scripts were properly preserved for future reference without complicating the migration process or affecting system performance.
To address the problems of migrating from Snowflake to BigQuery, NeenOpal provided a variety of tailored solutions to ensure a smooth transition, improve performance, and protect data integrity. These methods aimed to improve data management, maintain compatibility, and streamline the migration process.
ETL workflows were established to extract data from Snowflake while ensuring analytics pipelines were either adapted or re-engineered for BigQuery’s architecture.
Customized mappings in IICS limited string column lengths (e.g., 10,000 characters) for optimized storage and improved performance in BigQuery.
Informatica CDI jobs were leveraged to ensure compatibility and support efficient migration workflows.
To handle large datasets efficiently, partitioned data loads were implemented alongside tested incremental migrations. This approach ensured that data was processed in manageable chunks, preventing performance bottlenecks and enabling smooth transitions during the migration process.
A naming convention was implemented to transform Snowflake table names into BigQuery-friendly formats, embedding original database and schema names for consistency.
A script was developed to back up unused procedures and views, creating a metadata table in BigQuery for historical reference.
NeenOpal combines technical expertise with customized solutions to provide smooth migrations and comprehensive analytics platforms. Our client-centric approach provides scalability, performance, and data integrity, allowing organizations to maximize the value of their data.
The successful migration to Google BigQuery provided the customer a strong and unified data platform that streamlined reporting and analytics. This change increased performance, scalability, and data integrity while ensuring seamless operations.
The migration empowered the client with a unified, high-performing data ecosystem that supports advanced analytics and reporting. By transitioning to BigQuery, the organization is now positioned for future scalability, reduced operational overhead, and improved decision-making capabilities, laying the foundation for data-driven growth and innovation.
Engagement Manager
Content Writer
