A visual guide to transformations: The Salesforce data model

Data transformation is crucial for converting raw data into analysis-ready states that can be used to generate meaningful insights. Fivetran Data Models are pre-built tools that help data analysts and engineers transform raw data from common sources into data models for reports and dashboards, saving significant time and effort. In this blog, the process of creating a running snapshot of a sales team's performance using the Fivetran data model for Salesforce is discussed. The Salesforce data model allows for reading from several raw tables such as account, user, opportunity, and user role. These are then transformed into finished data models like sales snapshot, owner performance, manager performance, and opportunity enhanced. The steps to produce the sales snapshot data model, which summarizes bookings, pipeline, losses, and wins in the last month and quarter, involve staging the data, determining whether each opportunity was created and/or closed in the specified timeframe, joining tables, assigning statuses to opportunities, creating fields for amounts and counts by quarter and month, and finally aggregating data from opportunity enhanced by status. The resulting data model provides metrics that can be used to create reports or dashboards summarizing a sales team's performance over the past month and quarter.