Our data team embarked on a transformative journey to centralize data and ensure its unwavering reliability across diverse business domains. Our core objective was to construct a robust analytical foundation, empowering our product and strategic teams with data-driven insights. A pivotal aspect of this endeavor involved migrating eight core data models – the very backbone of three critical products, including interactive dashboards, comprehensive profiling tools, and in-depth analytical studies. This is the story of how we revolutionized our ETL processes, transitioning from AWS Glue to a modern data stack leveraging the power of Snowflake and dbt.
The Initial Challenge: Taming a Fragile and Untamed Data Environment
Prior to this migration, our existing environment was plagued by significant limitations that hindered our agility and data reliability:
- Lack of Comprehensive Documentation and Robust Governance: Tracing and debugging errors proved exceedingly difficult due to inadequate documentation and a lack of clear data governance policies. This resulted in prolonged troubleshooting and increased the risk of data inaccuracies.
- Risky Development Practices: The absence of a dedicated development environment meant that every code change posed a direct threat to the production environment. This created constant stress for our developers and increased the potential for critical system failures.
- Complex and Inefficient Data Transformations: Data transformations were executed on AWS Glue using Spark SQL. These tasks required a time-consuming environment setup and made thorough testing during development virtually impossible. This significantly slowed down our development cycles and increased the likelihood of errors.
- Inadequate Access Control Mechanisms: All users were granted the same level of data access, regardless of their specific roles and responsibilities. This created unclear permissions management, compromised data security, and increased the risk of unauthorized access to sensitive information.
Embracing the Modern Data Stack: Snowflake and dbt
To overcome these challenges, we adopted a modern data stack that seamlessly integrates dbt (data build tool) and Snowflake. This powerful combination provides isolated development environments, automated data quality tests, and comprehensive, integrated documentation through dbt’s code-as-documentation approach. Furthermore, it enables enhanced data governance and security. dbt serves as a robust framework for data transformations, rigorous quality assurance, and clear documentation. Snowflake, a cloud-based data warehouse renowned for its exceptional scalability and performance, provides the ideal platform for storing and processing our data.
Our decision to embrace dbt and Snowflake was driven by the need for a more scalable, reliable, and maintainable data infrastructure. We sought to empower our data engineers with tools that promote collaboration, streamline development workflows, and ensure data quality at every stage of the pipeline. This strategic shift has enabled us to unlock new levels of efficiency and agility in our data operations.
Empowering Data Engineers: Automating and Streamlining Workflows
As Data Engineers, we spearheaded the migration of all data transformations, automating essential processes such as data cleaning, standardization, and enrichment. We also meticulously rebuilt key dashboards and KPIs that are shared with our clients, ensuring their accuracy, reliability, and relevance. This transformation has significantly improved the quality and timeliness of the data insights we provide.
By automating these critical processes, we freed up valuable time for our data engineers to focus on more strategic initiatives, such as exploring new data sources, developing advanced analytical models, and collaborating with business stakeholders to identify new opportunities for data-driven decision-making.
The Legacy Architecture: AWS Glue and Spark SQL
In our previous architecture, each data transformation was stored in a Git repository and deployed as individual AWS Glue jobs. These Python jobs leveraged a Spark environment and were orchestrated using AWS Workflows. All data within our medallion architecture (Bronze, Silver, Gold) was meticulously cataloged within the AWS Glue Data Catalog.
Testing was rudimentary and lacked the rigor required for reliable deployments. Developers were forced to run scripts directly in the production environment, without the benefit of a robust CI/CD pipeline. Each developer was required to install a Jupyter Notebook and a Glue PySpark kernel, a process that could take upwards of 40 seconds to initialize. This slow iteration loop, coupled with the absence of CI/CD, made it exceedingly difficult to detect and resolve errors before they reached the production environment.
The limitations of our legacy architecture highlighted the need for a more streamlined and efficient development process. The lack of proper testing and CI/CD capabilities resulted in frequent production incidents and increased the risk of data inconsistencies. We recognized that a modern data stack would be essential to overcome these challenges and enable us to deliver reliable and accurate data insights to our business stakeholders.
The New Solution: dbt and Snowflake Architecture
With dbt, we gained the ability to document our entire data architecture using its innovative documentation as code approach. We implemented comprehensive data quality tests and unit tests to validate our transformations and proactively identify and resolve bugs early in the development lifecycle. The dbt Power User extension further enhanced our productivity, allowing us to preview data, test model execution, and visualize data lineage with ease. Crucially, all of this is now done within an isolated development environment for our entire medallion architecture!
Snowflake provides granular resource control through its sophisticated Billing and Cost Management tools, enabling precise consumption tracking and cost optimization. Compared to AWS Glue, which could lead to unpredictable and potentially high costs due to auto-scaling, we anticipate a significant reduction in our overall infrastructure costs.
The combination of dbt and Snowflake has revolutionized our data engineering workflows. The ability to develop and test our data transformations in isolated environments, coupled with the comprehensive data quality testing capabilities of dbt, has significantly reduced the risk of production incidents and improved the overall reliability of our data pipelines. Snowflake’s scalable and cost-effective architecture has also enabled us to optimize our infrastructure costs and improve the performance of our data processing operations.
Preparation: Mapping and Planning the Migration
Our migration commenced with the meticulous setup of Snowflake environments and the implementation of robust access control policies. We established a dedicated dbt repository and carefully distributed Snowflake user access based on clearly defined roles and responsibilities. This ensured that only authorized personnel had access to sensitive data and resources.
A crucial aspect of our preparation involved a thorough assessment of our existing data models and transformations. We meticulously documented each data pipeline, identifying dependencies and potential challenges. This comprehensive mapping exercise enabled us to develop a detailed migration plan that minimized disruption to our business operations.
The Iso-functional Migration of Data Products
Migrating complex systems inevitably raises critical questions: How do we ensure that we don’t lose any essential data models? How can we reassure our stakeholders that critical dashboards will remain reliable and accurate? How do we avoid inadvertently breaking key performance indicators (KPIs)?
We mitigated these risks by meticulously cataloging all entities that needed to be migrated and categorizing them according to the medallion architecture (Bronze, Silver, Gold). Our solution architecture encompasses data in these three states, representing varying levels of refinement and transformation, as well as dashboarding and KPI products consumed by Qlik and Metabase. Creating this comprehensive inventory of everything we needed to migrate ensured that no critical data assets were overlooked.
To ensure an iso-functional migration, we adopted a phased approach. We first migrated the Bronze layer, focusing on replicating the raw data ingestion processes from AWS Glue to Snowflake. Next, we migrated the Silver layer, rebuilding the data transformations using dbt to cleanse, standardize, and enrich the data. Finally, we migrated the Gold layer, recreating the
