Mid-Career Resumes

As we exit the Great Resignation, and move back to more traditional hiring patterns, application materials are increasingly important again. Over the course of my career I’ve been involved in a lot of hires, and read a large number of resumes. I know what I like to see, what I don’t like, and I have a bunch of friends in a similar position (although their likes and dislikes are sometimes different).

Recently, I realized that much of the advice online about resume writing is for people early in their career. That’s fair; they are the people with the least experience and need the most help. But as someone who is now mid-career, and reading resumes for other people who are also mid-career, I am noticing resumes from people who seem to still follow the early career advice.

So a few weeks ago I reached out to my friends who, like me, sometimes review mid-career resumes. While none of us are a full-time recruiter, we are the people who you need to impress if you want a job on our team. This post is a combination of my take, and the input I got from those people.

There are NO Hard Rules About Resumes

Resumes are not a regulated industry. There are no hard and fast rules. Any advice you see is just a set of suggestions. In the end, you have to decide what makes you look good and guess at what is effective.

Studies are rare, and even the best are poorly done. That is not the researchers’ fault. You cannot double blind a job hire. You cannot have 1,000 managers at different companies all hire for the same job from the same pool of applicants. Any one who knows a researcher is watching them work, likely changes their behaviors. Any study that finds bias creates legal risk for companies that participate which in turn limits participation and openness to data publication. List of problems with studying the process goes on and on.

Anyone who tells you there is one best way to create your resume, is wrong.
Anyone who is entirely focused on the hiring manager, risks failing to give advice to beat automated filters.
Anyone who is entirely focused on beating the automated filter, ignores that nearly ½ of the jobs in America are at small companies and unlikely to use such filters.

Write the best resume you can. Ask friends, particularly those who do hiring, for feedback. Consider paying a resume writer for help. But don’t expect even paid experts to be correct all the time.

Mid-Career Resumes Should Highlight Your Experience

The biggest mistake I see in resumes of people in mid-career, or even late career, is failing to highlight their experience. People who were at one employer for a long time struggle with this the most, but I’ve seen resumes for people with 15 years of experience that read like a recent graduate.

Your experience should be front and center. Everything about your resume should say “this is an experienced person.”

I like some form of summary at the top. Tell me what kind of employee and colleague you are. Not an objective section, but a summary of who you are. It can come in many forms:

a short paragraph:

Salesforce MVP, developer, administrator, and consultant with 20 years of experience in the nonprofit and higher education sectors. Seven Salesforce certifications, experience in more then 20 programming languages. Proven experience leading teams and working closely with non-technical clients.

list of titles, or key phrases

Salesforce MVP, Technical Architect, Nonprofit Fundraising Expert

After that, your job experience and skills are next. How exactly you do this can vary. Some people like skills in a sidebar. Some people put a list at the top. Some people put that list after their job experience. Frankly, as a reader, I don’t care. But I want to be able to find your list of skills and your relevant job history fast.

Your currently valid certifications should be included near your skills. But only those the reviewer will find relevant.

Think About Your Audience

Likely the person reading the resume of an experienced person is an experienced person. We have habits, routines, and work styles that are built on experience. We also have things like aging eyes, old printers, out of date external monitors, and other things that it are tempting to ignore.

Text should be high contrast, print well in black and white (there is a huge exception here for graphic designers, who benefit from showing off graphic design skills), and be generally easy to read. I don’t want your pretty three color graph, head shot, or blue text that prints light gray.

If I am reading a handful or resumes, I’ll do that on a screen and I can zoom in if I need. But if I’m digging through a big pile, I’ll print them. I will print them on my 20+ year old laser jet, blank and white, printer. When I last worked in an office and reviewed resumes, I used the office’s even older laser jet black and white printer. Your shaded background might make the whole thing unreadable on those devices. Besides, you should have too much experience to waste space on a picture (and that’s before we talk about companies trying to avoid identity based biasing who might not want reviewers to know what you look like too early in the process).

I strongly recommend going for simple, clean, classic, design approaches.

Mid-Career Resumes Should be More Than One Page.

I haven’t used a one page resume in more than 20 years. I don’t know who is still saying one page is the magic number. A new graduate might benefit from the one-pager, but if you have 10-30 years work experience, and you only need one page to tell me, it better be the most amazing page of text you’ve ever created. When I see a one-page resume, before I see the words I see a person with limited experience.

Personally, I like the two pager. Two very full pages. I want to see that you were forced to edit and format aggressively to make it fit on two pages. You want me to think you have 5 pages of content, but you compressed it effectively.

Two pages gives you plenty of room to show off, without wasting my time. It shows me you can edit and filter content. Ideally, it’ll leave me wanting more information, that gives me questions I can ask in your interview.

Some people like longer. When I spoke with friends who hire, most people liked two pages. But some were open to 3-4. Beyond four you are into academic CV land, which is a different thing entirely.

Connect the Dots

You have experience, you are showing it off well, good. But are you showing off the right experience? One of the most consistent pieces of feedback I got from friends who do hiring is that we want to know you know who we are as an employer.

No every detail, but tell us what your public persona is. Is there a values statement in the job ad? Reflect some of that language back in a cover letter. Do we work in a specific market? Make sure to include some experience that connects you to that market.

When I worked at a nonprofit, we wanted people excited by the work we did. Which means they needed to find ways to tell us in their resume, cover letter, application, and interview they knew something about that work. Since becoming a consultant, I’ve been consistently amazed that people will send resumes and come to interviews that don’t know what kind of customers we have.

Write a Cover Letter whenever Invited

This applies not just to mid-career applicants, but everyone else too. Not all jobs accept a cover letter, but when given the chance to say more: say more. The numbers I can find on resume review suggest an average of 6-7 seconds. I think that’s low in practice (see comments on studies), I know when I dig through a large stack I find ways to filter out some very fast, and others get more careful review. So an average will likely be far from my median or modal times. Even so, a resume that isn’t tossed out because it’s an applicant who is wildly unqualified, will get 15-30 seconds in my first pass. You add a cover letter, now I’m spending more time reading. You could double, or even triple, the time you get in the first review 45-90 seconds – that’s huge.

It also means you can connect some additional dots for me. If your resume includes experience that you consider related, but that might not be obvious, you have a couple sentences now to tell me that story. Are you career pivoting? Tell me what about your old career makes you better than your experience suggests. Do you volunteer in your community? Tell me what about that helps you understand our work, or support our company values.

In Mid-Career Resumes the Basics Still Matter

Details matter: fix your typos, use consistent formatting, etc. I saw a resume recently with a red-line through their summary line. That’s a bad first impression.

Write resumes you want to read: If you have read resumes as part of your job, think about the ones that impressed you and mimic those.

Get feedback from a friend: You probably have friends and professional contacts who will give you blunt feedback. Ask for it. I did as part of writing this post.

Consider hiring an expert: There are people who do this for a living. Some of them are really good. When you ask your friends for feedback, ask them for references to services they used.

Not everything is needed: Edit down your experience. Keep the stuff that says you’re awesome, cut stuff that’s not relevant to the hiring manager.

References for More Thoughts on Mid-Career Resumes:

The internet is full of advice on resume writing. Most for beginners, but some for people with more experience. Here are a few things I found useful:

The Queries Part 3 of 3

This is the third and final post in a series of posts to break down the questions from my Queries on Queries talk. The full talk is available here.

Is your solution reusable?

Migrations feel like one off processes, but teams that migrate once usually migrate again.

Have you ensured that as much of your solution as possible can be reused? Do you have a shared library of migration tools that your whole team can access? When you create new functionality are you thinking about ways to make it usable in your next project?

On any technology project you will generally benefit from designing for re-usability. I mentioned in my comments on the question about repeatability that people get tempted to see migration work as fundamentally one-off, but you need to plan for many runs. That question is focused on repeating the same project, this is about recycling parts of this project in another.

To a consultant, the value of reuse should be obvious: we like to sell projects to new clients based on successful projects for another client. For that I want libraries of tools the developer designed for rapidly assembled to meet a new client’s needs.

But even when I was the client, I was moving similar data into the same systems over and over. I created API libraries, and rough interfaces, to handle some of that work so I didn’t have to do the same tedious work again and again.

In both cases those libraries are only useful if whoever needs them knows they exist, has access to them, and can figure out how to leverage them.

Is your migration testable?

All good processes are rigorously tested.

Do you have an automated testing solution that validates your process? Can you tell if the data migrated accurately after each test run? Do your tests cover the positive and negative cases?

Testing migrations is hard. Testing software is hard. The testing tools that developers are most familiar with are unit testing tools, test one very small thing at a time. Multi-system data comparison is not their forté. The tools that do exist for such work tend to be quite expensive and/or so complex the task of creating tests is nearly as hard as the task of creating the migration jobs themselves.

But just because testing is hard does not mean you shouldn’t do what you can do within the budget and time you have. When you cannot use something like MuleSoft’s MUnit you can still create queries that sanity check the migrated and generated data. You select records for spot checking that cover edge cases you are aware of, and some that represent primary use cases. You can look for records that create invalid data states that would violate your new validation rules.

Is your work fixable?

Migrated data often needs to be updated after the jobs have all run.

Do you have a plan to fix your data if errors are found post migration? Does your plan include ensuring you have external Ids, or other connections, to be able to update all records of every type? Have you validated this plan will work in practice?

When you do a data migration, because everything is determinant, you feel like perfection is possible. But when you’re moving millions of records that were entered by humans, extracted by humans, mapped by humans, validated by humans, and represent human behaviors, there is a lot of room for human error.

You can either pretend your process is good enough to squeeze out the error, or build a process that allows you to fix the errors that slip through. Obviously I don’t believe the first is possible, so I encourage the second.

Make sure you can go back and update anything. If you’re migrating into a database that allows for a lot of easy changes – great. If you’re migrating into a financial system – make sure you understand the rules for editing.

Planning for mistakes you don’t want to have makes it far easier to recover from those mistakes when they appear.

The Queries Part 2 of 3

This is the second in a series of posts to break down the questions from my Queries on Queries talk. The full talk is available here.

Is your work repeatable?

You will need to do this more than once.

Is your process designed so you can run it over and over without error? Can you easily erase test attempts and start over from a clean slate? Do you have the capacity to do all the practice runs you need to complete your project successfully and on schedule?

Because a migration is fundamentally a one-way operation, designed to move data once, it’s tempting to build the whole process as a one-off affair. I’ve seen (even used) migration processes that required hours or days of hand polishing data to get it to load – this is a terribly way to do the job.

A good migration process should be automated. To automate anything you need to test it. If you test something you should expect it to fail many times before it works. And when it fails you need to run it again and again until it works.

By their very nature data migrations create data – in a target system no less – and so you need a way to roll back your changes to migration to a pre-run state for each subsequent test. I like to use a staging database for the main complex parts of my migrations. I created Salesforce2Sql just to make that so easy no one would be tempted to skip that step. When I create processes in an ETL, I like to have jobs start by deleting data from the staging database related to the job, so I can make Idempotent jobs as much as possible. Run, test, adjust, repeat. If you know how many times you ran your migration process, you didn’t run the jobs enough.

Is your work measurable?

To know you moved all the data, you must know how much data is going in and how much should come out.

Can you accurately predict your output data volume based on the input size? Do you have valid estimates of the running time required for each stage based on the data volumes? Are the estimates of expected data set size from a reliable source?

It seems like knowing how to measure your work should be obvious, but in truth most interesting migrations are not a simple record-in, record-out – they involve splitting records, combining tables, filtering data, converting tables to fields, fields to tables, and other similar adjustments. But the only way to know if you got it all to work out right is to work out the math wherever you can.

It’s also important to know how long a process will take. Sometimes a few thousand records here or there doesn’t matter much, but sometimes that is a matter of hours. Particularly when running samples it’s important to know the average running time. I’m working on a project right now where we know that the first 3 million records will load in about 6 hours, the last 45,000 records will take 12 hours.

In that project we’ve worked out those running times, and we have a good understanding of total records counts. In other projects we thought we knew, only to discover the person giving us the source record counts was talking about the per-year instead of total expected migration size. But with per-record estimates we can adjust expectations quickly when information changes.

Do you scope your data migrations carefully?

Limiting bad data in your system allows for better decisions in the future.

Do you only load data into the new system that you truly need? Can you easily spot the difference between new and old records? Are there data points getting loaded that have no use case or maintenance plan in the target system?

Everyone wants to keep all their data. My entire career I have understood that storage is cheap, and big data is king. AI driven data analytics have been around for a few years, and now we have all the attention on generative AIs, both benefit from large data sets.

These all tools are great, but they aren’t magic.

Big data processing, whether it be AI driven or not, is all about correlations. If you give a correlation engine bad data, it will give you bad results. Garbage in is still garbage out.

You only want to migrate data that’s good.
You only want to migrate data that’s useful.
You only want to migrate data that you will maintain.

So before you start a migration make sure you know your data will fall into those categories. Organizations can always archive data they don’t migrate.

There are other reasons more data isn’t always better.

If your system, or data archive, is ever breached that presents a risk to an organization. Privacy laws are steadily tightening, increasing the chances you will have to admit to your audience you were the cause of their information falling into the hands of bad actors.

Also, old data is often bad data. Colleges often have the email address used by their applicants squirreled away in their alumni systems. How useful do you think the AOL address I used in 1997 is to Hamilton College today? If they use it, they will fail to reach me. It provides them no value, but does provide them the chance to make mistakes. Same is true of old phone numbers, addresses, and more.

Keep the good stuff, let go of the stuff you don’t need.

The Queries Part 1 of 3

This is the first in a series of posts to break down the questions from my Queries on Queries talk. The full talk is available here.

Are your tools good enough?

Our migrations live and die by our tools.

Are your tools built for the scale of your project? Do they empower you to do your best work or impede rapid progress? Would a new tool serve you better now or in the future?

Having the right tools is critical to any job. In data migration we primarily talk about ETLs (Extract, Transform, and Load): tools like Jitterbit, Informatica, Mulesoft, Talend, etc. We also use additional tools to help support the process: a task tracker like Jira, a Data Modeler like Lucidchart, staging database prep like Salesforce2Sql, and more.

It’s easy to say that it’s a poor carpenter who blames his tools, but anyone who has spent time with actual carpenters knows they care a great deal about what tools they use. They might be able to make due with poor tools, but they will do their best work with the right tools for the job.

Each tool you use needs to meet your team’s needs. It should play to your strengths, supports the kinds of projects do you do, and has an eye to the future. A tool that works great for a team of declarative Salesforce consultants might drive developers crazy. A tool that works great for 10’s of thousands of records might struggle with millions; a tool scaled for 10s of millions of records may be overly complex for a project of 30,000.

Make sure you’re using the tools that let you do your best work, now and in the future.

Do you make the data atomic for processing?

Smaller pieces of data are easier to track, manipulate, and test.

Do you divide the source data into its constituent parts? Can you process individual pieces of data easily and cleanly? Can you stop your process after each stage to validate the results?

It can be tempting to process data as it comes: handling whole rows of data in the form they were provided and treating fields as a single data point. In practice exports may have extra rows or columns to deal with related records. Organizations may have encoded multiple points of data into fields like ticket names including a show name, date, and time into the name field. Fields can also contained semi-structured data, like Joomla’s use of arbitrary JSON blobs.

To process this data it is often easier and clearer to extract it from these structures prior to direct processing. It’s not always needed, and rarely required, but doing this clean up of structure – like creating interstitial database tables or predictable data objects – can greatly ease the rest of your job.

Like many problems in software engineering, it’s easier to do good work when you are operating on atomic pieces. Think about the right ways to pull your data into constituent parts when they aren’t there already.

Can you process samples of your data set?

When you have lots of data you need to test small parts to be sure your process works.

Do you know how to create and run small segments of your total input? Are your segments made up of complete and valid samples? Does your sample include all the errors and edge cases your data set will throw at your process?

If you are working with small data sets your sample can be all the data. But when you have a large data set you need to test your process with samples. When you have a multi-step migration you likely need to test the second phase while the first phase is still under construction – again a sample data set is critical.

Having valid test data, that covers all your edge cases, is critical to making sure you have a working solution.

A few years ago I worked on a project that involved a two stage migration for a membership organization with some 600,000 active contacts. Every one of them needed to be migrated into Salesforce and then into Drupal. To test the Drupal migration we needed samples of all the types of membership statuses we would see, which involved hand creating several hundred records. At the next Salesforce Commons Sprint I raised the idea of needing a better tool for this kind of work, that question eventually helped lead to Paul Prescod‘s creation of Snowfakery. Snowfakery will build you testing data sets of any size and complexity to make sure your processes will succeed.

Queries on Queries: Improve your data migration

Last week I gave my Queries on Queries talk, intended to help you improve your data migration process, as a webinar for Attain Partners. It’s a revised and improved version from the last time I gave it.

These questions aren’t like the old Joel Test (which is still useful) where the right answer is “yes”. These questions are designed to point you in a direction but allow you to change your answer over time. I generally answer this questions with a paragraph not a word. Use these questions as a challenge to make you and your team better.

Over the next few weeks I’m planning to publish a series that will include each query and why I think it’s useful in helping you think about how to improve your process.

Salesforce Data Migration Lessons

Last week I was part of a webinar for Attain Partners talking about Salesforce data migrations. One of the questions the moderator, Eric Magnuson, asked was the three lessons I’d learned doing data migration work.

The answers I chose weren’t so much from my first data migration projects, as from more recent projects. Those early migrations were tiny by my current standards. Back then, I was mainly the consumer of migrated data. When I did migrations they were small and manual. The intellectual process was similar but the scale meant I had lots left to learn about large data projects. The lessons I chose are focused on what I wish I knew when doing the migration for clients with vastly larger data sets. When I became the producer of migrated data.

My three lessons:

Manage expectations from the start.
Don’t be a hero.
Understand your tools, and use them well.

Manage Expectations from the Start

This still surprises me, but it’s true: one of the biggest reasons data migrations get into trouble is bad expectations.

People tend to think data migrations are easy. You create a map from the old system to the new, run some processes to convert the data, load into Salesforce. But in practice, that map might involved thousands of details. Those conversions are hard to get right for every variation of the old data. And loading large amounts of data is never as easy as it looks.

When people think something is easy, and then the outcome is less then perfect, they get mad.

The problem is, data is always messy. We do migrations when we replace systems. We replace systems when the old one has problems. Problems in a system, lead to data errors.

That reality is made worse by the reality of big system switches. The freshly migrated data lands in a system the primary users are still learning. Those factors lead to confusion, mistakes, errors, and misunderstandings.

From the very first conversation I tell our clients to expect migration errors. Our first mock run of the migration is messy – sometimes very messy. The whole point is to find errors. The second mock run will be much better, but still imperfect; we’ll find more errors. No matter how many times we do test runs, there is only some much we can do with imperfect inputs.

We can make your data better then it is, but we can’t make it perfect. If you expect perfect data, you will be disappointed. I want you to be thrilled by your new system, and that means you need to understand your migrated data will have flaws.

Don’t Be A Data Hero

When I first did data migrations I was often a one-person show. I was responsible for figuring out all the details, implementing the process, reporting to the client, and fixing all the flaws. It’s a common story for people doing migrations.

We find ourselves up late at night, working through piles of detail, trying to make sure the client is satisfied. It encourages a hero mentality: I’ll make the project successful through shear will.

And most of us can do it. Being a data hero isn’t that hard if you put the hours in. It is, however, miserable.

People doing migrations need, and deserve, support. Now that I’m leading a team of people doing migrations I have added the support I should have had. I created a space for us to come together and talk about our projects.

We ask each other for help and suggestions. We offer ideas for how to improve our processes. We talk about ways to address client concerns. And yes, we complain to one another. But mostly what we do is make sure that no one is alone. Everyone, myself included, has support and back up.

A team is stronger than a person. We don’t need to be heroes. We do better work, and are happier people, when we support one another. Good work, by happy people, makes for successful projects.

Understand Your Migration Tools

Data processing tools are code generators – understanding that allows you to use them well.

Both parts of that make sense once you say it:

Tools that allow you to design an arbitrary process that takes inputs and generates outputs are obviously writing code at some level.
If you understand any tool better, you will use it better. That’s true of a hammer, a screw driver, or a piece of software.

I learned how to migrate data from people who weren’t formally trained developers. They were using the tools the best way they knew how, but didn’t have the background to apply software development best practices.

When I combined the tool usage they taught me, with the software engineering practices I already knew, I created vastly superior solutions. Our team now creates processes that are easier to setup, run faster, and allow us to fix all errors (even if we missed them until after launch).

Apply Lessons our Salesforce Data Migrations

I apply these lessons to the Salesforce data migrations I lead in my work at Attain Partners. I combine that with my queries on queries review process, and am constantly building better solutions for our clients, our team, and myself.

Becoming a Salesforce MVP

This week I learned that I am now a Salesforce MVP!

I know that I was nominated by at least Chris Pifer, Samantha Shain, Paul Ginsberg, and Cassie Supilowski. I appreciate their support, and also everything they have taught me over time. They are an august group. Two are Rock Star Admins and two are existing MVPs (three now, as Sam is also in the 2023 MVP class). It is my a privileged to know, work with, and learn from all of them.

I got here with community support

The Salesforce MVPs are a group of people who are recognized within the Salesforce community as leaders and contributors. It’s a group of people who, in addition to our day-to-day work, also support other people’s work and learning.

For me that looks like posts about Salesforce here, participating in Salesforce.org’s Commons program, and generally supporting people I meet along the way. While I contribute as I’m able, my own contributions are fueled by support from my nominators and others.

Sam, Cassie, and I are all leaders the Commons’ Data Generation Toolkit project. Chris has been a friend and colleague nearly my entire adult life – we often remark that we’ve had about every possible professional relationship. I met Paul planning Nonprofit Dreamin’ a couple years ago and we continue to talk regularly about life, Salesforce, and nonprofit (in no particular order). In other posts I have talked about the influence mentors had on my work.

Looking forward to more

Being a Salesforce MVP gives me access to tools, events, information, and opportunities that are new to me. It also comes with the expectation of continuing contributions to the community.

The good news for me is that I generally like contributing and supporting others. I am looking forward to continuing to advancing my personal projects, contribute to Data Gen Toolkit, presenting at events, and generally helping other succeed.

If your group needs a speaker reach out and let me know. I love to talk about Salesforce, data, the web, career paths, and just about anything you find me writing about here.

What I Brought from Drupal to Salesforce

If you look over this blog, or know me, you will see that I’ve now have reasonably significant experience as both a Salesforce and Drupal developer. The last couple weeks I have been thinking about what from my Drupal experience supports my work as a Salesforce developer.

I think there are three parallels that are encouraged in Drupal developers that helped me learn to be a good Salesforce developer quickly.

Embrace, don’t fight, Platform Constraints
Extend the Platform’s Strengths
Leverage Events

Embrace Salesforce Platform Constraints

Both Drupal and Salesforce run in constrained environments. Web applications, regardless of their purpose, have to protect themselves against bad actors and bad neighbors. There are execution timeouts and memory limits, for both platforms. Salesforce adds a variety of additional limits and governors, but they are all logical extensions about memory and time. Lots of other platforms allow developers to ignore resource use until they reach a crisis point. Don’t believe me, just check the memory used by Chrome, Electron Apps, or any other Chromium-based application.

Working in resource constrained environments forces you to think through how to use the resources you do have efficiently. While these platforms aren’t like working on hardware with highly limited resources, they still can test your resourcefulness.

Drupal and Salesforce both provide ways to run large jobs across many processing contexts. New developers on both platforms often only resort to using batch and queueable operations as a last resort, but learning to use those solutions is critical to success on most interesting projects. When you try to avoid them you create solutions that appear to work and fail at scale.

Coming to Salesforce from Drupal, I already knew and understood the importance of asynchronous batch processing. So when solutions needed batch processing it was second nature to learn that part of the platform.

Extend the Platform’s Strengths

For all Salesforce’s push and marketing to avoid code, Salesforce developers are often taught that once you write code you just do everything in your code. The interfaces you can use to extend the platform’s existing solutions are treated as advanced topics. But when you work with Drupal, you are encouraged from the start to create modules that build on and extend the platform’s existing strength. Drupal developers are encouraged to leverage the features and utilities all around them.

This has always been true, but even more after Drupal’s move to leverage Symphony plugins and services. As a developer used to extending the platform, I came to Salesforce looking for ways to extend the platform’s declarative tools.

Often Salesforce developers create powerful solutions built purely in code triggered by record changes or simple buttons. They look passed Apex Actions that extend the Flow declarative automatons, platform events, and other tools that extend the system. But when you embrace a platform’s basic structures you often create more flexible solutions than your could with pure code.

The mind set of extending a platform, which I brought with me from my Drupal work helps me create tools and solutions that are designed to adapt over time.

Leverage Events

Event driven architectures are not new, but their popularity continues to grow. Where platforms used to follow informal patterns that equated to event systems, now we see formal event structures being build to replace old habits.

Drupal and Salesforce both have had event frameworks for a long time: Drupal had hooks (events by naming convention), Salesforce had triggers.

Both have seen major upgrades to their event patterns in recent versions. Platform events in Salesforce, still making their full power clear to a lot of developers. Symphony brought proper events to Drupal in version 8 and continue to help push the platform forward.

I have learned to leverage the events systems on both platforms. Understanding them as tightly constrained state machines, and learning to push them to their limits, helps me get the most from both platforms.

My experience with Drupal hooks and events has made it obvious to me when to leverage Salesforce’s Platform events. As Salesforce increases the number of places you can use them in their declarative tools, it increases the value of this approach.

So What?

As a developer, what you learn in one part of your career can make you stronger in the next. As a field we’re not actually that creative. Even if the details are different, the concepts will often carry forward because they are built on the same fundamentals. Whatever platform you are using today, learn how to make it sing – it’ll help you learn the next faster and better.

Getting Started with Salesforce2Sql

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.

Setup

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.

Preferences

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.

Picklists

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.

Auto Indexing

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.

Additional Settings

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.

Final Notes

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.

Salesforce Developer Podcast Episode 119

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.

We talk about Snowfakery, Salesforce Open Source Commons, the evolution of PHP, Drupal, my career in general, and even a bit about spinning. I’d love to hear what you think.