CHALLENGE

• Build an open-source framework for acquiring and transforming data from a variety of relational sources to persist into Snowflake.

• Develop a configurable open source library for processing DDL (Data Definition Language) and DML (Data Manipulation Language) events published on Kafka topics for automatic propagation of the DDL/DML changes to mapped target tables in Snowflake.

OUTCOME

• Created an end-to-end data acquisition, transformation, and persistence layer to enable change data capture and automation across a variety of database operations

• Designed pre-hooks to detect schema changes from Confluent Schema registry 

• Created processing flows for data replication using Snowflake Streams, Tasks, & Stored Procedures to publish AVRO events configured to Snowflake target tables

• Configurated a module for source & target table column mappings along with auditing and traceability 

• Set up Snowflake configuration to ensure backup(s) when DDL changes on completed tables to provide a trail of the changes performed

APPROACH

• Discover - Collaborated with key stakeholders to develop an overall architecture design

• Design - Supplied documentation for overall architecture, incremental flows, data models, audit/traceability, etc.

• Develop - Built new coding artifacts for github repository, validated solution, and implemented monitoring and tracking metrics

• Deliver - Provided performance and functional testing of the solution and open source repository with necessary documentation on Github

SOLUTION

• Snowflake 

• Confluent Kafka

• Debezium

• Avro Messaging

• Github

• Hashmap Consulting Services 

  • Snowflake Cloud Data Strategy & Advisory

  • Snowflake Cloud Data Design, Engineering, & Architecture

  • Snowflake Optimization & Management