Reverse ETL
Knock CRM|RealPage ∙
March 2024
- Requirement: Calculate business metrics using combined data in snowflake and load to salesforce daily
- Implementation: Identified the correct unique ID for loading records to salesforce. Created transformations needed via dbt so metrics could be accessed via self service analytics and not just in salesforce. Wrote a python script to load the new metrics to salesforce daily via bulk API if there was a change in the metric. Automated script to run daily via AWS ECS Fargate.
- Results: Salesforce users were able to use the new metrics in their reports and all other users were able to add the new metrics to their own reporting via self-service analytics in metabase, ensuring continuity across the business and consistency in metrics across departments.
Snowflake Data Lake Pipelines
Knock CRM ∙
February 2023
- Requirement: Add salesforce raw data to s3 data lake/snowflake
- Implementation: Created external stage/snow pipe and linked it to S3 for data lake setup. Used AWS Appflow (via CLI/json templates for mapping and version control) to extract from Salesforce and load into S3+Snowflake staging tables. Sqitch was used for version controlled change management of all non-dbt tables and objects in snowflake written in SQL, including staging tables, snowflake task DAGs for orchestration and scheduling, and any routines/procedures. Created repeatable process for adding more objects from other instances; documented procedures and naming conventions.
- Results: Automated E/L of data from Salesforce objects into S3/Snowflake staging into change data capture tables ready for transformation via dbt.
Product Launch Dashboards
Knock CRM ∙
December 2022
- Requirement: Deliver product launch dashboards for AI products soon after product reaches general availability to track product adoption, usage and health.
- Implementation: Created metrics based on discussions with AI product managers such as transfer to agent rate, . Metrics were created at 2 levels (property/company) using dbt for transformation of the underlying product data. Metabase was used to build out the dashboards with dynamic filtering on date ranges, properties and companies and visuals were selected to maximize readability and impact.
- Results: Dashboards delivered actionable metrics on AI product performance and adoption and continue to be used as the base data for Knock AI case studies.
Product Health Reporting
Knock CRM ∙
August 2022
- Requirement: Deliver product health reporting dashboards for CRM product.
- Implementation: Created metrics based on discussions with product subject matter experts such as lead to lease ratio, visit to lease ratio, active products, engagement score, etc. Metrics were created at 2 levels (property/company) using dbt for transformation of the underlying product data. Metabase was used to build out the dashboards with dynamic filtering on date ranges, properties and companies and visuals were selected to maximize readability and impact.
- Results: Dashboards became the executive leadership level product health dashboards for the Knock organization. Further iterations of the dashboards to include more complex transformations such as month over month reporting were delivered using the same process and tools.
Pricing ETL
Avery Dennison ∙
October 2021
- Requirement: Develop a process to mass change pricing for thousands of products based on complex pricing calculations
- Implementation: Engineered an ETL pipeline for mass price changes via Python, PriceFX REST API and SQL to create a full end to end mass price change process.
- Results: Pipeline resulted in millions of dollars of added revenue within a short timeframe, saving the org millions and months of manual work.