For memorial day weekend my wife and I went camping at Price Lake for a few days. I took a couple hours one afternoon to hike around the Boone Fork Trail. In a moment when the country is in another downward spiral about all I can think to offer are a few images of a place worth being.
Salesforce2Sql is a tool dedicated to doing one thing very well: mirroring Salesforce schema.
A little over a year ago I started work on Salesforce2Sql to help support people doing data work with Salesforce. Salesforce2Sql is a simple Electron app that allows you to clone the schema of a Salesforce org into an SQL database (currently supports MySql/MariaDB and Postgres). These mirrors are useful when you want to stage data during migrations in or out of Salesforce.
When is Salesforce2Sql useful
Salesforce2Sql is a tool dedicated to doing one thing very well: mirroring Salesforce schema. It does not attempt to extract data or convert data in any way.
Salesforce provides excellent APIs for data import and export, but they work best with some prep work first. Salesforce2Sql gives you a staging database that mimics your Salesforce schema. In these schema mirrors you can prepare all your data for high speed processing off-platform. Anyone who is looking to move data into or out of Salesforce at high speeds and large volumes benefits from this setup.
I have seen people write large complex ETL jobs meant to go right from one source system into Salesforce. These jobs can be hard to test and they are slow to run. They generally don’t give you an easy way to review the data before you push to Salesforce. By landing the data in a database clone you can break the jobs into stages, review transformations before they are loaded, and run thousands of iterations for testing instead of dozens.
Salesforce2Sql is built and released for MacOS, Windows, and Linux (most testing is on Mac and Windows). You can download the installers from the current release and follow the standard patterns for your OS. From there start the application to get to work.
You will also need API access to a Salesforce org and a database to create your schema within.
Basic Salesforce2Sql Use
As of this writing Salesforce2Sql uses the old security token connection method. I would like to add OAuth2 support as well but haven’t gotten that done; contributions are welcome. So with the application running, and your security token in hand, click the big “Create New Connection” button on the left side of the main interface.
Step 1: Fetch all objects
Once connected click “Fetch Objects”, and the tool will download a list of every object in your org. There will be several hundred. So the next step is to select which you want to mirror. You will notice Salesforce2Sql selected defaults for you (well I did). It will select all custom objects and based on your org’s structure Salesforce2Sql guess which standard objects to select. There is a search box at the top right to help you find any others you’d like to add but simply checking the box.
Step 2: Fetch all fields
Click the next button to move to the Proposed Schema tab, and then the “Fetch Details” button. Now Salesforce2Sql will query every field on every object you just selected (this may take a moment but honestly I find it much faster than I expected when I first started this project). Once that is complete you can either save that schema to JSON for later re-use, or click Next to move to the “Generated Database” tab.
Step 3: Generate tables
This is the last step. Click “Create Tables” and Salesforce2Sql will ask you for your database credentials. Once you click okay on this final screen the tool will attempt to create all those tables for you. Again this will take a couple minutes if you have a large schema. Once the process is complete you can also save the SQL statements for editing and/or later re-use.
That’s it, you now have a database with a schema that matches your org’s structure.
There are a few preferences you might want to experiment with (although I tried to pick smart defaults) when building mirrors. For me the right choices depend on my use case.
Salesforce Picklists are a bit of a special beast. The obvious choice is to make a picklist into a SQL Enum to support validation of data. But not all picklists are restricted in Salesforce and aren’t always required. By default the tool will use enum for restricted picklists, varchar for unrestricted picklists, and add blank values to all picklists (since it can’t easily determine if a given picklist is required or not across all page layouts).
If the picklist values in your org are pretty much set, the default settings make a lot of sense. If the picklist values in your org are likely to change you might want to make them all into regular varchar columns.
The next important section contains the index settings. While it is possible to over-index a database I think the three sets of default indexes are pretty good guesses: Id columns, external Ids, and picklists. The one you are most likely not to care about are the enums, and therefore the one you might consider disabling if you aren’t processing on those fields at all. Id columns are now case-sensitive even in MySQL by default as of version 0.7.0 since Salesforce Ids are case sensitive.
The other defaults section let’s you pick a few other system behaviors. The most common to fuss with are first and last in the box.
By default it expects Lookup fields to be 18 character Salesforce Ids – cause that’s what they will be in Salesforce. But during a data migration some people like to put legacy Ids into these fields (I recommend a proper legacy Id field marked as an external Id) so I give the option to use 255 characters instead of 18.
Salesforce also has two categories of fields that are common to ignore in a migration and you may wish to keep out of your database just for ease of use: the audit fields (createdBy and the like) and read-only fields (like formulas). The final two checkboxes in that other defaults section lets you keep those fields out of your clone schema.
The middle two fields control the behavior of field defaults – generally I like these two settings as is in just about every use case, but you may feel differently.
Salesforce2Sql uses Bootswatch themes for design elements. The preference pane also lets you pick a different look-and-feel from their theme list.
There are a couple other details worth knowing.
First, if the process runs into a problem where the SQL engine complains about row size limits Salesforce2Sql will automatically switch all varchar fields to TEXT fields in an attempt to reduce the row size. That will override all preference settings for these fields.
Second, Knex.js – which Salesforce2Sql uses to handle the actual SQL writing – adds indexes as a table alter even when they could be part of the create statement. This makes the process a bit slow and it means that if there are errors during the creation of indexes you may see some errors in the interface but leave you with a pretty-good schema clone.
Finally, yes there is lots of room for improvement. I work on this project when I can, or when I need a bug fixed for my own work. I am excited to get suggestions, ideas, feedback, documentation edits, and code submissions.
During a recent department event my wife introduced me to the sister of one of her students. The event was an award ceremony for some of the history and political science majors – the student’s sister was along to support her brother (and as a smart college student get a free meal outside the dinning hall).
As a CS major, this student is trying to understand her options for what kind of programming she might be interested in. As a good professor my wife introduced us so the CS major wouldn’t have to pretend to be as excited about history as everyone else present. Listening to me talk about what I’ve done in my work she commented that maybe she should be a web developer – my reply was that she should do the work with problems she finds interesting.
Dodge the Gate Keepers
Like many people who are going through a CS education she has been surrounded by people who are confused about the difference between IT and CS. She talked about going to a conference and running into a bunch of guys who belittled her because she wasn’t into computer hardware. Apparently one even criticized her for misstating the directionality of a Lightning adapter (I can’t remember the last time I cared about cable directionality in a digital connector). My suggestion that was old, familiar, and involved one finger. I also pointed out the guy was probably just wrong.
That kind of adolescent gate keeping out of other college students isn’t surprising, but it is annoying. I work in a field that’s short handed, and we need smart people interesting creating great systems. We were short handed before the whole U.S. economy started to run short on workers.
From a short conversation I could tell she was smart, capable, and friendly – exactly the kind of person any employer will be lucky to have some day soon. But she also felt discouraged, as if she was weak in some important part of the field. Assembling her own PC hadn’t been fun for her (I have no shame in admitting that I’ve never built a PC from scratch); fussing with hardware just doesn’t excite her like coding does right now.
I really love having a good IT team to support my work. And having great hardware at my disposal is critical to good work. But I have minimal interest in working on that part of the technology stack myself. Sure, I’ve done my time installing RAM chips onto mother boards, and re-seating PCI cards, but I never really wanted to care about the details of those components.
I always wants to create tools that solved interested problems.
What Problems are Interesting
We are all attracted to ideas and projects that sound exciting. This student became interested in the kind of work I do because I can talk about it with excitement and confidence. I enjoy the problems I get to solve on a day-to-day basis and that shows. I have no idea if she’d enjoy them. Being a Salesforce or web developer might bore her to tears.
A problem is not intrinsically interesting. We find problems interesting for our own reasons. That interest makes us intrinsically motivated to solve them.
I like writing middle-ware and creating related tools. Filling gaps left between other tools is interesting to me. I know people who love to create great UIs because it makes people love the product. Other friends love to work on security problems because it keeps systems secure (and gives them excuses to break into systems they should access). Some of my friends work on creating software to advance science. Still others love to create high performance solutions to handle big data problems. And others who help create games. I could go on, byt you get the picture.
My point is all the problems are interesting – to someone. None of the problems are interesting to all of us.
If you want to have or want a career creating software, look for jobs that solve problems you think are interesting. It doesn’t matter if I think your work is exciting. If you are excited about it, I’ll be excited to hear what you’re doing.
This week’s Salesforce Developer Podcast featured an interview I did with the host, Josh Birk, the end of last year. As much as I still don’t like the sound of my voice on recordings it was a fun interview and I am really excited to see it come out.
For a little over two years I’ve had the privilege of helping lead the Data Generation Toolkit Project for Salesforce. In general, Open Source projects are not known for their inclusive and supportive communities. I believe it is fair to say our project demonstrates that building a supportive community can yield great results.
Started by a question I wrote on a piece of paper in 2019 and posted to a wall, the project grows more every time I look around. That first meeting inspired the creation of Snowfakery and the recent training for Snowfakery has attracted more than 300 registrants. Contributors created documentation and presentations to help Salesforce admins learn to seed sandboxes. We are building a recipe library to help people starting out on Snowfakery. We launched two faker data providers. This year we reviewing and documenting other tools to generate and move Salesforce data.
More importantly we’ve built a project that is useful to the community, and supports new contributors to open source projects.
We did not get here accidentally. The project leadership wants to support the community members as much as create new tools. From the beginning we chose to encourage people unfamiliar with open source projects, contributions, and technologies.
Leading an Supportive Open Source Project
To be a supportive project starts with the leaders.
The project currently has seven identified leaders: Alisa Edwards, Allison Letts, Jung Mun, Paul Prescod, Samantha Shain, Cassie Supilowski, and myself. For those who like diversity statistics: that can be seen as 5 women, 2 men, 3 countries of residence, 3 counties of origin (not the same 3), 3 developers, 4 Salesforce admins, and at least 3 racial identities. No sub-group perfectly reflects its community, but that’s not a bad start in my opinion.
We have agreed that some of us lead specific sub-projects, while others tend to the health of the community. Everyone has a role.
When we started out with just 4 leaders either Paul (as creator and maintainer of Snowfakery) or I (as the person who started the project) could have dominated the project claiming founder status. Certainly men leading other open source projects have used that status to control the project direction. But Salesforce Open Source Commons projects are designed to discourage that behavior, and Paul and I embraced that design. Cori O’Brien created a wonderful space for our project to grow within.
Any open source project should focus on supporting its users. Our goal as a project is to support the Salesforce community – particularly nonprofit and education users. To do that we need the insights that only come from being open to outside ideas.
Our project’s leadership also established a pattern of self-review and reflection. Each year we will gather to discuss if the leadership group is the right size, and if anyone needs to step down. That creates a space for us to routinely reflect on what we’re doing and if we’re doing it well. The invitation to leave frees people from responsibilities they have to the project without frustration or burn out.
Recipes for Open Source Projects
Beyond our leadership team structure we also are intentional about how we encourage all contributors.
The project as a whole has space for all kinds of contributions. If someone wants to write documentation we will support them. When someone wants to learn to write recipes, we will work in pairs to get through the first one. People who want to write Python code for our faker providers get the chance to do that too.
We try to be kind to all contributors. We thank everyone when they open pull-requests or issues. Even I get thanked when I open a PR. Even if someone needs to make significant changes to a contribution before we commit it, we make sure to praise their effort.
These aren’t shit sandwiches. We genuinely appreciate all contributions and divorce that from any corrections that we request. As a long time open source contributor I am surprised at how much I appreciate the messages.
Maintaining Supportive Discussion Channels
Most open source maintainers know we have to maintain good ways for contributors to ask questions. Over time projects have used a wide variety of tools: IRC, News Groups, Email, issue trackers, and now Slack. In most projects those are the spaces that tend to become ugly. You see RTFM-style answers, personal criticisms, identity-based attacks, and other ugliness. The Slack channels for our project are a key piece of how we engage with each other, and support new contributors. We work hard to make sure people get timely answers, clear directions, and steady encouragement.
Our work falls within a community where our reputations matter and so there is a level of decorum absent in some open source projects. But no group is perfect and we will address issues when they arise. The open source common’s DEI Framework project will hopefully help us continue to deepen our understanding of the community. The kinds of attacks open source community spaces frequently allow in the name of good code, are simply unacceptable.
Come Join Us
Now and then something happens to us all that reminds us we’re not perfect. I’ve written about the strengths of B Students before, and well last week I proved that sometimes I still have room to improve.
When I started this blog I set the goal of posting once a month. I have stuck with, or exceeded, that target except for two months since 2016. I missed September 2019 and last month. Last time around I set the goal of four posts in the following month, so that is my established case law.
I did have a piece written that I had planned to release. But it needed another editing pass. Before I got those edits done a project at work took over my life. I will have those edits done for next weekend, and I already know the general topic of the post what will follow it.
Last time the increased pressure to write actually resulted in some descent pieces. I wrote that B Student piece, which I quite like, in part as a response to that failure. Two others from that month are how-to articles that continue to get good readership years later.
Hopefully this will be an equally good month (although this post probably isn’t the best example of that).
I recently had to disable a Salesforce Trigger from a client’s production environment. Having discovered the need last minute for a project, I needed to react quickly and make the change quickly. Since the official documentation is a bit lacking I decided it was time for blog post of better directions.
The Salesforce CLI directions in the article above make a few annoying assumptions:
- That you’re happy to use MDAPI not SFDX.
- That all your tests pass in production (which should be true, but let’s be real it;s always).
- You enjoy working out CLI commands in a rush.
What you need
Disabling a Trigger Using SFDX in VS Code.
Connect VS Code to your target org. Easiest solution here is to just go to the command palate and authorize an org.
Pull down the trigger from your org. I find it easiest to go to the metadata browser, find the trigger under Apex Triggers and click the download icon on the right.
Update the trigger’s XML file to change the status. All project code files in SFDX are accompanied by an XML file of class metadata. Open the file and change the status to “Inactive”.
Generate a Manifest file for your trigger. Right click on the trigger code file in VS Code’s file explorer and select Generate Manifest, provide the file a useful name (in this case used DisableTrigger)
Deploy the change. If all your org’s tests currently pass you can just right click on the new manifest file and instruct VS Code to deploy the source in the manifest to the org.
What if tests fail on trigger deploy?
While all our tests should always pass all the time, Salesforce admins frequently find that’s not actually true in practice. Heck this trigger could be part of that problem in your org. But to deploy a code change we have to run some tests. Since we are disabling this trigger, the trigger code doesn’t need to be in tests we just need to run a working test with enough coverage to get the job done. So go find a test class and then we can deploy.
VS Code doesn’t currently have a setting to set the testing mode on a deployment, so you’ll need to do this last step in VS Code’s terminal (available by hitting control-` if you don’t have it open already). In the terminal run the following command (replacing [good_tests] with the name of your test class).
sfdx force:source:deploy -x manifest/DisableTrigger.xml -l RunSpecifiedTests -R [good_tests]
It should deploy pretty quickly, but you can check the status by going to settings in your org and checking the Deployment Status.
A few years ago I wrote a piece about project time estimation and created an estimating tool. My goal was to get project managers to listen to the fact that estimates were inherently a guess not promise. The tool I created took a series of project tasks, the estimated time range, and a level of estimator confidence. It then ran a Monte Carlo simulation with those tasks, and generated a histogram of possible outcomes.
Five years later I still use it for project estimates. But I have grown tired of its interface weaknesses and needed to add cost estimation to keep it useful. So I recently heavily revised the tool and posted an updated version (the old version is still available here).
The interface is still very utilitarian (pull requests welcome), but this version makes it make easier to adjust the tasks. Much more importantly it now also estimates costs, not just time.
The new version is faster than the previous. And it adjusts the graph type based on the range of possible outcomes.
Each task now includes inputs for min and max time, confidence, and hourly cost of that task. So if different people at different bill rates are part of the project it can still give you useful numbers.
The histograms broke down when faced with too many bars. So I settled on an XY scatter approach to help visualize the broader range that the cost estimator made normal.
Please give it a try, I’m always open to feedback, suggestions, and pull requests.
For this version I added the ability to include a unit cost for each task. The first tool worked just fine when you were estimating the tasks for one person who had one billing rate (or where hourly costs aren’t important). In practice teams need to be to able to do an estimate across all work streams, and different roles will have different billing rates.
This version includes a rate for each task and a graph of projected project costs.
Why I Created A Project Estimator
I wrote the original when I was struggling with project managers who would take any estimate you gave them as a range, pick a number, and promise the client (and themselves) we would hit it. To them an estimate was a promise – one that had to be kept. That lead me to badly overestimate projects so that the lowest end of my range would be a safe number – but that’s just a different form of bad estimation.
I had a good amount of experience providing estimates, and had read a lot on the topic. I knew there were teams that did better and I wanted to help our team improve.
The original tool was loosely inspired by one Joel Spolsky described ten years earlier. He has several important ideas on his process regardless of your project methodology. But his idea of using Monte Carlo simulations had stuck with me since that article had been new. After failing to find a tool that included it, I wrote my own.
Are the Project Estimates Any Good?
Fundamentally the simulations are only as good as the estimates provided. For any project I have been able to compare my simulated project estimates to final hours my work fell within one standard deviation of the median.
The confidence measure helps more than I expected. Originally, I added the measure of confidence because I needed something to determine how often the simulator should assume people are just plain wrong – and by how much. While I could have hard coded a solution I did not know how to pick good values. I knew that my confidence varies by the task. I also knew the less confident I am the more I am likely to be wildly off. So decide to make confidence an estimator provided variable, and use that to pick the size of overruns.
For every 10% you reduce the confidence, the simulation will allow the upper bound of the estimate to increase by the size or the entered upper bound. On a task you estimate at 7-10 hours, a 90% confident estimate will allow overruns up to 20 hours (just in the 10% of times that aren’t in the 7-10 range), and 30 hours for an 80% estimate.
That extra box also immediately helped me feel comfortable with my estimates. Knowing that the simulator would offset optimism bias for me I could stop trying to do that myself. My estimates can use tighter ranges trusting the software to offset expected bias.
A Value of the Graphs in Project Estimates
The graph has turned out to be the most important feature. Initially I included it because I wanted to play with D3 and have something more impressive than numbers to show. What I discovered was a reminder of the importance of data visualizations – even simple ones.
As I said before I created this tool when working with project managers who simplified all estimate ranges to a single number and held everyone to that number. The first time I presented numbers from the simulator those project managers picked the median and complained I made it too hard. The median was better than what we had before, but not enough to treat as a promise.
When I started presenting the graph those same people immediately started to change how they talked about the project. By visualizing the impact of uncertainty over several tasks they could see that the project might run far over my estimate – or far under. The more uncertainty, the longer the tail on the graph.
Suddenly they were comfortable talking about risks from overruns, finding ways to help clients understand the possible risks, and being understanding when a task proved harder than expected.
The graphs tell the story, and empowers the team to have an honest and productive about project estimates.
For the last two years I’ve been fortunate to serve as a leader of the Salesforce Open Source Commons Data Generation Toolkit project. That project has produced and inspired a variety of efforts, including Snowfakery and a collection of starter recipes.
This week, along with my colleague Allison Letts, Salesforce’s Paul Prescod (the creator of Snowfakery), and our fellow project contributor Jung Mun, I helped create two new faker providers for Snowfakery:
What These Faker Providers do
The use of Snowfakery is growing. The more we use it, the more we want the data tailored to specific projects. And the more we find places where the Faker project’s providers do not have quite what we want. In particular the project does not (well did not) have providers for nonprofits and education specific data.
The Nonprofit provider currently just provides organization names:
$ snowfakery snowfakery_nonprofit_example.recipe.yml --target-count 10 nonprofit
nonprofit(id=1, nonprofit_name=Eastern Animal Asscociation)
nonprofit(id=2, nonprofit_name=1st Animal Foundation)
nonprofit(id=3, nonprofit_name=Upper Peace Alliance)
nonprofit(id=4, nonprofit_name=Southern Peace Home)
nonprofit(id=5, nonprofit_name=Unity Home)
nonprofit(id=6, nonprofit_name=Western Peace Home)
nonprofit(id=7, nonprofit_name=Upper History Foundation)
nonprofit(id=8, nonprofit_name=Upper Friends Committee)
nonprofit(id=9, nonprofit_name=Eastern Pets Center)
nonprofit(id=10, nonprofit_name=Northern Animal Foundation)
For the Education provider we have a bit more. You can generate college names, departments, and faculty titles.
$ snowfakery snowfakery_edu_example.recipe.yml
Account(id=1, Name=South Carolina University)
Contact(id=1, FirstName=Roberto, LastName=Stanton, Title=Associate Professor of Microbiology & Immunology)
The providers can of course run as a standard faker community provider. Once you are setup with Faker just add the new providers with pip:
pip install faker-edu faker-nonprofit
Then you can use the libraries in your code:
from faker import Faker import faker_edu fake = Faker() fake.add_provider(faker_edu.Provider) for _ in range(10): print(fake.institution_name())
from faker import Faker import faker_nonprofit fake = Faker() fake.add_provider(faker_nonprofit.Provider) for _ in range(10): print(fake.nonprofit_name())
How we got here
A few months ago I posted on how to extend Faker to create nonprofit organization names. Allison took that as a starting point to create a similar project to generate names of colleges, departments, and academic titles (and a greatly improved phone number generator but that’s not included since it’s more general). Both of these projects were good proof-of-concept but were rough around the edges. So this week, with Paul’s guidance and Jung’s input, we contributed the more polished versions to the community.
During a virtual working session on Wednesday we restructured the projects, cleaned up code, added sample recipes for Snowfakery, and published them to PyPi. By publishing these as Faker providers on PyPi, and not as Snowfakery plugins, they are available to a wider audience. By having them owned by a larger open source community we are expecting them to enjoy long-term support.
Both are still just getting started. For example the nonprofit one still just generates organization names, but would benefit from job titles, program names, and more. The EDU provider right now just handles colleges, but is expected to generate other education related data in the future. We also have a plan for a third provider to help improve the diversity of the names generated by Faker to make our fake data more representative of real communities.
The Data Generation Toolkit project has been a great example of what happens when you bring people from a wide variety of backgrounds together to solve technical problems. Like the larger Data Generation Toolkit team Paul, Allison, Jung, and I all have different backgrounds, skills, and experiences. By coming together we are able to help each other find better solutions than any one of us would have found on our own.
It’s been an exciting week, and I’m looking forward to more to come.
This is Faith. Faith has found joy, happiness, and contentment – except when she needs her toenails trimmed, dinner is late, or she gets woken up for a nap.
Life for Faith wasn’t always easy. She spent a little time on the race track before they closed, and was among the last dogs to ever race in Florida. Her first adoptive home didn’t treat her well, and she bounced back to the rescue group underweight, with a cracked tooth, and uncontrolled multi-drug resistant worms. But those problems are gone (at least we hope, the worm treatment takes several months and we just got our first clean test last month – need three in a row). But Faith found a life she loves in our home.
She loves to cuddle with her brother.
She loves paper towels.
She loves a good inverted nap.
She even loves hiking with us in the mountains of North Carolina (trust me on this one, she doesn’t hold still again until she’s napping when we are done).
Like many people I aspire to be more like my dog. She has handled life’s ups and down with grace (mostly) and forgiveness (graciously – even after I trim her nails). I hope to learn to be as openly loving as forgiving as her.