Ryan Goodman has been in the business of data and analytics for 20 years as a practitioner, executive, and technology entrepreneur. Ryan recently created DataTools Pro after 4 years working in small business lending as VP of Analytics and BI. There he implanted an analytics strategy and competency center for modern data stack, data sciences and governance. From his recent experiences as a customer and now running DataTools Pro full time, Ryan writes regularly for Salesforce Ben and Pact on the topics of Salesforce, Snowflake, analytics and AI.
View our recorded webinar with Datameer and Data Tools Pro discover how you can optimize your Salesforce data migration process in 2023 using a reverse ETL process powered by Datameer and Snowflake. This webinar will be presented by Datameer and Ryan Goodman, creator of Data Tools Pro. Ryan will showcase how Datameer has been the secret sauce to accelerate a reverse ETL data stack to effectively prepare, transform, and analyze data.
Whether you’re a Salesforce administrator, a data analyst, or data professional, this webinar will equip you with practical insights to streamline your data migration. Don’t miss out on this opportunity to learn and ask live questions how to enhance your data migration practices.
What you will Learn?
During the webinar, we’ll cover , real-world examples of successful reverse ETL scenarios for Snowflake specifically for Salesforce. Additionally, Ryan will share best practices and pitfalls to avoid during a typical Salesforce.com data migration.
A year ago, I worked on a small project to help us improve our data driven funnel. I learned what I called “Snowflake to Salesforce analytics sync” had a more buzzworthy term called “Reverse ETL.” This article shares some of the lessons learned along the way and some thoughts about where reverse ETL is headed.
Low Level of Effort Solution
All of the data and metrics were already available and calculated in Snowflake for reporting, so the process to push those measurements back into a Salesforce object using Azure Data Factory was quite simple.
As data analysts, we often find ourselves needing specialized functions in Snowflake. Working in Financial services, there are specific Excel functions that provide significant value. I had a need but developer resources were not readily available… Until ChatGPT changed everything!
Now, with the help of ChatGPT even non-developers can prototype and experiment and contribute powerful capabilities. For Snowflake User Defined Functions (UDFs) in particular, ChatGPT is a game changing resource for self paced learning, debugging, and translating existing concepts and patterns you know into Snowflake.
Snowflake has helped democratize the data platform eliminating layers of technology and administrative traditionally required to enable data workers. The next generation of data preparation tools arrived in recent years and continues to accelerate the process for preparing business ready data assets.
In my previous role managing data and analytics, I had a big problem. All of my data was staged in Snowflake but data engineering was backlogged with requests and my analysts were stuck between SQL and the last generation of desktop BI data preparation tools. I solved this problem and tripled my team’s throughput with Datameer.
Datameer is a native Snowflake data preparation and analysis solution that does not require extracting any data out of Snowflake. I used for all of my BI reporting and dashboard projects, allowing me to roll out 3 times more business ready data assets in 2 months than the first 5 months of my Snowflake initiative.
There are a few key features of Datameer that helped us wrangle data faster. Enterprise data is imperfect, so you need the right tools to profile, explore, and understand imperfections while you build.
Data-Driven Join Analysis
One of these features is Join Analysis, which offers unmatched Rows that enable me to quickly see which records fall out of the left and right side of the join. With this feature, I can easily identify records that are missing IDs or recognize that I didn’t fully understand the grain of data before I joined. The Join Keys analysis feature also identifies duplicate records and highlights which data source is causing duplicates or potential cartesian products as a result of duplicate keys. These features enable me to understand my data both inside and out of each join, allowing me to move forward more efficiently in my data flow.
Another useful feature of Datameer is inline no-code Data Exploration. This feature is essential when exploring data and validating it with collaborators. Datameer provides intuitive and fast exploration capabilities so you can create many cuts of data through your data pipeline. You can employ filtering, aggregation, binning, and sorting. It only takes about 5 minutes to master this feature, and it has enough functionality to cover most real-world slicing and dicing. For repeatable or reusable scenarios, the exploration nodes feature enables me to make my exploration view available or deploy it as its own view back to Snowflake for recurring validation.
Field Exploration is yet another useful feature of Datameer, as it prepares a summary profile for each field and provides a visual reference point for quickly identifying outliers, NULLs, district values, and unique records. This feature is similar to Snowflake and helps me quickly and efficiently understand my data.
Datameer is No-code where you want it.. SQL Coding where you need it
Datameer offers a no-code user experience that will technically allow you to build and deploy business intelligence views and tables without writing a line of code. There are conversely many experienced SQL developers who are more proficient writing SQL than using no-code interfaces. Datameer is best of both worlds because you can visually abstract your SQL code into a flow and have all Snowflake SQL functions on hand. This way you can still beneifit from the aforementioend features while coding. Datameer with generate your SQL as a CTE that runs natively on Snowflake.
Change Tracking and Revisions
In addition to a rich meta and tagging, Datameer offers deployment history and version control natively, allowing you to comment revisions, single click restoration to previous deployments in Snowflake, and full access to the SQL code.
Overall, I am impressed with Datameer’s capabilities and look forward to every release with incremental updates focused on bringing data teams and data analysts together in a practical solution.
ChatGPT from OpenAI is a very powerful generative AI system for research that has quickly captivated millions of users.
I decided to put ChatGPT to the test to do a focused Q&A session about moving to Salesforce.com and how to plan for your data migration. The results were absolutely amazing!
Check out this article on Medium to get the most common questions answered by ChatGPT about moving to Salesforce.com and what to expect for migrating your data into Salesforce:
Data migration can be a complex process, especially when moving to a new system like Salesforce.com. In this article, we’ll explore 3 common Salesforce.com Data Migration Issues that you should avoid at all costs. Whether it’s poor project planning, communication gaps, or lack of engagement from business stakeholders, these challenges can all contribute to a less-than-successful migration. But with the right approach, Salesforce.com Data Migration can be a smooth and efficient process.
Issue 1: Poor project planning and expectations
One of the biggest challenges of Salesforce.com Data Migration is poor project planning and expectations. Without clear communication and process gaps between the Salesforce development, business, and data migration teams, a successful migration becomes less likely. In order to avoid this pitfall, it’s important to establish crystal clear expectations between all parties involved.
Salesforce team and data migration specialists need to communicate changes to the Salesforce.com data model in the final weeks leading up to go-live. This sets best practices for managing Salesforce and prevents un-intended, time consuming issues.
Data migration specialists and business stakeholders need to agree what data is migrated from the old business app into Salesforce.com. In some cases this mapping exercise will surface missing fields or functionality.
Delivery team and business stakeholders should work together to ensure training occurs with real data if you are moving your entire process from an old system into Salesforce.com.
Issue 2: Attempt to fix too many data quality while moving data
When migrating data from one business application to another, such as Salesforce.com, it is important to address any data quality issues that may have been previously ignored. This is because automations and validation rules in the new system may highlight these issues. To ensure that the data migration process is as smooth as possible, it is best to perform extensive data quality adjustments in the source system or after the data has been migrated.
Additionally, Salesforce.com offers a vast ecosystem of solutions and apps, one of which is Cloudingo, which can be a valuable tool for data cleanup during migration. By cleaning the data at the source or after migration, you can ensure that the data is accurate and consistent in your new system.
Issue 3: Manual, human initiated data movement
If your Salesforce.com developers or consultant suggests using DataLoader and Excel for your data migration, it is important to keep in mind that this approach is definitely not be suitable for complex migrations. It is crucial to thoroughly test the migration process in a sandbox environment, running end-to-end migration many times before migrating to production. With that said, you also shouldn’t need to spend a lot of money on technology to migrate your data. Here are some free data tools for Salesforce.com that you can share with your tech team.
Have a Salesforce Data Migration Blueprint for Success
There are lots of resources on the web including the ones we provide for free. Here are key components that you should build into your own successful data migration plan.
Project timeline + milestones Having clear milestone and working backwards from “go-live” no less than 6 weeks will help you avoid under-scoping your data migration into Salesforce.com.
Data migration object inventory Scorecard to track and report progress and issues through the entire migration lifecycle
Data mapping docs Excel / Google Sheets with data mapping and definitions is how a lot of data mapping gets complete. Now we are working on Data Tools Pro free utility to help with the work.
End to end migration checklist Complete, documented data migration checklist of all steps that need to occur for migration.
Business continuity plan Not typically a part of data migration but something that helps align expectations for what happens on day 1. There are no-redos on migration once users start updating data in Salesforce.com.
Salesforce DevTools extension for Chrome is by far the most useful Salesforce data migration tool for administrators looking to inventory objects and fields. For data migrations in particular this tool has been so valuable as a time saver and provided foundation and inspiration our own Salesforce Migration tools.
Generate Salesforce Relationships Diagrams (ERD)
In DevTools, similar to exporting an Excel file, you can also export a Salesforce ERD, giving you an entity relationship diagram with a few clicks.
Download Salesforce Fields to Excel
In DevTools, you can search and select a list of objects, then download the field definitions directly to Excel.
Workbench: Great for Salesforce Data Migration
Workbench for Salesforce.com is a very useful free that offers the most functionality for working with Salesforce data. With a variety of administrative functions you can execute a wide range of tasks including but not limited to
Testing SOQL queries
Explore your data model
Explore the Salesforce Rest API
Workbench Trick: Mass Delete Records in Salesforce
Inside of Workbench, you can run APEX code, so one helpful trick is deleting data from a SOQL query which opens the door to not only delete more than the 250 cap but more importantly allow deletion of records for custom objects.
Here is an example code snippet shown above that would delete 10,000 accounts where we can specify any conditions like records created after 11-20-2022.
delete[SELECT id FROM Account WHERE CreatedDate > 2022-11-20T01:01:01Z LIMIT 1000];
Need more Salesforce Data Migration Tool Recommendations?
We are here to help provide recommendations for free and premium solutions to move salesforce.com data into Salesforce.com. Feel free to check out our Salesforce.com Migration Assessment for to lean on our experience.
