11 Productivity Hacks for Data Developers — Don’t Let Inefficiency Consume an Entire Workday
Whether working faster or working smarter, these hacks lay the foundation for BI efficiency.
Ill habits gather by unseen degrees, as brooks make rivers, rivers run to seas.
— Ovid, “Book XV,” Metamorphoses, trans. Sir Samuel Garth, John Dryden, et al.
“Unseen degrees” is the poetic way of saying that bad habits have knock-on effects that often go unattributed, though certainly not unnoticed. When left unchecked, inefficiency will eat up chunks of your day and frustrate you through tedious and unrewarding work.
Although there are volumes of books and articles written specifically on productivity and getting things done (GTD), they tend to orient toward a general audience. Finding practical tips specifically geared towards developers, specifically BI developers, can be difficult.
Another difficulty lies in the estimation of the time savings. Some of these tips yield instant rewards while others pay off in the long term. Some yield small daily dividends while others pay off big on infrequent tasks. That means on average, the time benefit of the hack will depend on the likelihood of being used on any given day.
So, with that in mind, let’s attempt to reclaim an entire workday from the clutches of inefficiency!
1. Use a dedicated SQL IDE — 30 min.
Whether cloud-based or on-premise, database vendors provide some interface to write and execute SQL. Vendor-specific interfaces will vary in their look and feel and the features they provide (some of which may even be exclusive.) However, if wiring SQL is your bread and butter, a dedicated IDE like DBeaver (free) or DataGrip (paid) will outshine any standard console.
DBeaver and DataGrip offer very similar sets of features, starting with a customizable and theme-able UI, which includes syntax highlighting (helping you parse SQL quickly and avoid eye strain.)
Both of these tools excel at context-aware autocomplete — helping you find the exact column, table, or function, as you type (even with a partial match.) Formatting features like bulk indentation, auto-format, and case conversion help keep scripts neat and legible. You can even transform a SELECT * into a fully qualified list of columns with a single keystroke.
You can (auto)save SQL scripts for later reference, work on multiple active scripts across multiple tabs, and even across multiple databases, all from a single screen. The ability to hyperlink to a table or view definition directly from the editor is also very convenient.
These tools can bidirectionally load data to and from a file and even allow you to update table records directly from query results, thus saving you from having to write the DML by hand.
We’re only starting to scratch the surface of what a dedicated IDE can do. Still, the features mentioned so far easily merit a thirty-minute time-saving in any given workday — to say nothing of the peace of mind that comes from not having to type out exact column names or look up definitions across multiple screens.
2. Text Expander — 10 min
Text expanders output pre-programmed strings of text when called via “hotkey” or “hotstring.” Just about anyone can benefit from using a text expander but database developers and data analysts, who frequently type out the same patterns of text throughout the day, stand to gain the most.
Tools like AutoHotkey (free, opensource) for PC or Mac’s built-in Keyboard settings and Automator can help you make the most of this functionality and configure it to your exact needs. But how does one put hotkeys and hotstrings to use?
A hotstring is a string of characters that tools like AutoHotkey or other text expanders are pre-programmed to expand into something else. Hotstrings are used to type out long strings of text using just a few keystrokes. Here are some practical examples (using AutoHotkey format):
; hotstrings ; expand 'btw' to 'By the way' as you type
::btw::By the way; expand 'jtel' to Jenny's telephone number
::jtel::867-5309; correct 'teh' to 'the'
::teh::the; expand '!!SG' to select from table and group by template
::!!SG:: SELECT col_1, COUNT(*) cnt FROM table `nwhere true `ngroup by 1 `norder by 1 desc
A hotkey is a combination of keyboard (or mouse) keys that, when pressed together, run a pre-defined command. Hotkeyes are similar to shortcuts like CTRL+Z (undo) or CTRL+PgUp/PgDn (switch tabs), except that they work from anywhere and do whatever you tell them to. Here are some practical examples (in AutoHotkey syntax):
; hotkeys ; press winkey-z to go to Google
#z::
Run http://google.com
Return; press ctrl+alt+s to type your email signature
^!s:: Send Sincerely,
{Enter}John Smith
{Enter}Senior Life Hacker
return; press shift+n to open Notepad or focus active Notepad window
+n::
IfWinExist Untitled - Notepad
{
WinActivate
}
else
{
Run Notepad
WinWait Untitled - Notepad
WinActivate
}
As a rule of thumb: if a task is repetitive and repeatable, automate it! Here are some more time-saving automation of common quotidian frustrations:
- Common SQL templates: create or replace table as select, is today’s data loaded in the table, row count for latest n loads in the table, filter using inner join, etc.
- Canned responses such as “on a call, I’ll get back to you” or “have you checked the documentation, available at this <URL>.”
- filling out forms with predefined structure: name {tab} email {tab} pass {enter}.
3. SqlDBM — 2 hrs
SqlDBM, the time-saving tool masquerading as a modeling tool. Its ever-expanding list of features is meant to empower various roles in the BI team — from developer to project manager. For an in-depth look at all the ways SqlDBM can be used by the various members of an organization, check out “What Can SqlDBM Do For You?” For this article, the focus will be on just one, albeit common, use case.
Whether through data transformation, or simple data analysis, a complete understanding of the database landscape is required. Unfortunately, most databases are too large for anyone to maintain a functioning mental model reliably. SqlDBM’s core feature — searchable, visual database diagrams — helps solve that problem.
Using database diagrams, which are automatically built by SqlDBM by analyzing PK and FK relationships, users can quickly find relevant tables through a universal search feature. The wildcard search scans the names and descriptions of table and column names and orients the user to their corresponding place in the database landscape.
Without a searchable visual model, users would have to choose between scheduling a meeting with a knowledgeable teammate, or doing the analysis by hand, table by table. Either option would easily consume at least thirty minutes.
Migrating tables from external sources and integrating them into the existing landscape is another common and time-consuming process. This work typically requires copying SQL by hand, converting it to a compatible target database syntax, then adding database-specific properties where applicable. That’s before the actual modeling can begin.
Using, SqlDBM’s reverse engineering feature, ingesting relevant tables from a source database is a simple process. Once ingested, the DDL can be converted into any of the supported database flavors. Project conversion ensures that all the compatible features are translated to valid target database syntax, and that non-compatible features are removed from the DDL (e.g., index declarations when converting to Snowflake.)
The desired tables will now be available for modeling in your primary database project and will automatically link up with existing tables, where applicable.
These features work as easily for a single table as they do for an entire schema. Even assuming that you’re only migrating a couple of tables, the work saved through an automated process, which guarantees consistent, valid SQL every time can easily amount to an hour of work saved (or, say, a whole week, depending on how many tables you intend to migrate.)
So tables have been created and modeled, and they are now ready for deployment. Of course, SqlDBM ensures that the DDL is neat and error-free, but there are more things to consider. For example, which tables need to be created from scratch and which ones require alters? Does the same hold true across all your environments?
SqlDBM’s “compare revisions” feature allows users to generate “create” or “alter” scripts from any project save point, current or prior. But it also goes one step further. With “Live DB Compare,” SqlDBM can baseline any revision against any of your database instances and generate the requisite alter scripts — thus removing all the guesswork from a deployment.
Systematic, automated, version controlled, and error-free DDL every time. Nothing done by hand means nothing to debug and adjust. Shall we say, another half-hour rescued?
4. dbt — 2 hrs
Dbt is a config and parameter-based data transformation tool. Once you familiarize yourself with dbt’s functionality, you’ll also have to invest a bit of time doing the initial project parametrization. However, once that has been taken care of, you’ll be able to do in a few lines of config what would have otherwise taken hours to code from scratch.
In the example below, I instantiate an incremental loading strategy for a table using a few basic parameters:
{{ config(materialized='incremental',unique_key = 'record_id',incremental_strategy='delete+insert',database="my_db",schema= "my_schema")}}
select * from my_source_table-- this filter will only be applied on an incremental run
{% if is_incremental() %}where etl_id = current_date(){% endif %}
Add some role assignments that I’d like to declare once in the project config and never have to worry about again:
+post-hook:
- "grant ownership on {{ this }} to role my_dev_role copy current grants"
- "grant select on {{ this }} to role my_analyst_role"When dbt runs the model above, the requisite temporary tables are created, overlapping records are deleted, new records are inserted, source and target validations are performed, and the necessary grants are applied:
dbt run --models my_model
The script doesn’t even change if the model has to be reinstantiated. Simply adding a “full-load” parameter to the existing job would do the trick.
Sure, I could have written the above SQL by hand. It would have taken roughly two hours to develop and test. However, being able to condense repeatable steps into parameterized templates not only saves hours of work upfront, it also saves you having to maintain and troubleshoot these scripts in the event of future changes.
5. Excel — 30 min
Excel is NOT a database! The UK Health Ministry learned this the hard way when it attempted to use a spreadsheet for COVID-19 contact tracing and ran out of rows, losing over fifteen thousand records (and costing lives.)
However, this doesn’t mean that Excel can’t be a powerful time-saving tool in the hands of a competent BI developer.
Excel easily outperforms any SQL IDE for a small dataset when it comes to data analysis. The aforementioned DBeaver and Data Grip will gladly export any query result to CSV and allow Excel to filter, search, and pivot the entire dataset at will.
Thanks to its formulas, Excel also shines when it comes to generating SQL! CREATE and INSERT statements are examples of repetitive syntax with no tolerance for missing commas or unclosed quotations. A simple “CONCAT” formula can merge the column names, types, and descriptions and transform them into valid SQL by systematically inserting the requisite commas and quotations.
It seems there is a theme emerging here. Auto-generated code saves time because the formula is written once and repeatedly applied without risk of typos.
6. Shortcuts — 10 min
Shortcuts in your methodology and deliverables should never be permitted. Keyboard shortcuts, on the other hand, are an enormous boon to productivity and peace of mind.
A keyboard shortcut is faster than mouse navigation and infinitely preferable to navigating a menu. Yet, many people never go beyond copy, paste, and undo.
Whether it’s Excel, DBeaver, or even your internet browser, you can level up your game by learning the shortcuts to operations that you use most frequently. If you find yourself performing an action more than once a day, it would be worth your time to divine the associated shortcut.
Hoving over the relevant button or finding the relevant operation in the program menu will usually reveal the associated shortcut.
Here are some examples of the less common shortcuts that I find myself turning to again and again:
- Ctrl+PgUp/PgDn — Switch to next/previous tab (Excel, Browser, DBeaver, others)
- Alt+Shift+Space —expand SELECT * into a full list of columns (DBeaver)
- Ctrl+Enter — execute SQL script (DBeaver, Snowflake)
- Ctrl+‘+’— execute SQL script in new results tab (DBeaver)
- Ctrl+T — open new tab and position cursor in the address bar (Browser)
- Ctrl (+Shift) +Left/Right/Up/Down/PgDown/PgUp… — the ability to navigate text will come in handy in all kinds of scenarios. From jumping over words to highlighting lines or an entire document without moving your fingers from the keyboard is crucial because it works nearly everywhere (DBeaver, notepad, office, email/chat, etc.)
If you can’t find a shortcut for what you need, many programs will allow you to define your own. If not, don’t forget that anything is possible with the aforementioned AutoHotkey.
Interlude
A little neglect may breed mischief. Until now, we have been talking about ways to save time. Shortcuts. Hacks.
Knowing the time a task currently consumes makes it easy to gauge the effectiveness of a new tool or a better method. There is a direct relationship between process and time.
However, time saved is not the same as time not lost. Efficiency doesn’t just concern itself with reduction but with prevention as well.
Focusing too narrowly on how quickly a thing can be done often overlooks the obvious question of whether it should be done at all. Sometimes we ignore inefficiency because it’s tough to measure, like refocusing after a distraction.
Finally, and most distressingly, time is lost because we cannot tie countless failures and blunders to some trivial detail that was overlooked in the past. Here, the relationship between process and time is indirect, probabilistic, and, therefore, difficult to attribute accurately.
A little neglect may breed mischief …
for want of a nail, the shoe was lost;
for want of a shoe the horse was lost;
and for want of a horse the rider was lost.— Benjamin Franklin, Poor Richard’s Almanac
Let’s cover some tips and habits that help ensure that no harm befalls neither horse nor rider by avoiding such mischief in the first place.
7. Reminders — 10 min
Besides the obvious benefit of helping make sure the task at hand gets done, scheduled reminders also carry two non-obvious benefits.
First, they help declutter headspace by having fewer items to keep track of. Offloading reminders reduces anxiety and improves concentration. The exact time savings is difficult to measure, but it’s certainly more than what it takes to set a reminder (which is nothing.)
Second, reminders prevent the fallout caused by forgetting. Naturally, a reminder is set for the most ideal or convenient time to execute a given task. Not setting a reminder and potentially forgetting implies that the task must now be squeezed in at a less convenient time (among other tasks or outside working hours.)
Of course, this is assuming that the task is not time-sensitive. The fallout from not executing a time-sensitive task will drain more time yet. From writing an apology email (short) to recovering from a system failure (long) to a loss of confidence in yourself or your product (indefinite.)
Whether remembering to run an ad-hoc job or reply to someone before a given date, don’t think twice. Email programs such as outlook, or better yet, personal digital assistants like Siri or Google, make scheduling reminders trivially easy.
Set it, forget it, so you don’t regret it.
8. Time Blocking — 30 min
Time blocking, time chunking, time segmentation — among the many names given to this productivity technique, I like “blocking” most. “Blocking” speaks its subtle double benefit in a way that, say, “chunking” does not.
That blocking out segments of time for specific tasks and to-dos invariably blocks out distractions from others.
Time blocking reduces distraction (aka. multitasking) by designating exactly what tasks should be worked on and when. What’s the ideal breakdown? Well… it’s not that simple.
Because different types of work require different attention profiles, we could begin by identifying two opposing archetypes: makers and managers.
Makers (aka. developers) require extended periods of deep focus, punctuated with occasional time for coordination and communication. This could mean scheduling meetings and checking email in the morning and after lunch, thus leaving two undisrupted blocks of work time in which to zero in and concentrate.
On the other hand, the manager profile is characterized by its high availability and being in continuous contact with others. However, this doesn’t mean being available to all people through all channels, all the time.
While prioritizing meetings and availability, managers would still benefit from smaller blocks of distraction-free time to work on project plans and presentations.
Another popular technique, the Pomodoro method, makes no distinction between attention profiles. It suggests that optimal concentration is achieved in 25-minute blocks, with 5-minute breaks, after which a different task is prioritized in the same fashion.
Benjamin Franklyn, Bill Gates, Jack Dorsey, and Elon have all sung the praises of time blocking. Experiment and decide which method best suits your job and work environment. Whatever strategy you adopt will be preferable to the alternative: letting notifications and impulses drive your schedule.
9. Git Repository — 45 min
Storing code in a shared and version-controlled repository is the first step in a robust continuous integration process. Its use is non-negotiable. Without git, enterprise BI would quickly devolve into anarchy. Even then, there is a right and a wrong way to do git.
The wrong way to use git is: manually. Copying and pasting code by hand after having made the changes in the database. This approach is error-prone and adds an extra step to your workflow — encouraging corner-cutting.
Still, it’s better than nothing.
The right way to use git is: seamlessly. By integrating a repository directly into your workstream, you ensure that all changes are captured and tracked. The SQL IDEs mentioned above both come with plugins that allow seamless git integration. Cloud-based data warehouses usually offer their own git flavors (e.g., Azure DevOps, AWS CodeCommit) or allow for external connections. Dbt, taking a hardline stance, won’t even instantiate a project without a connected repo.
Here’s why.
When a process fails, you’ll inevitably have two pressing questions: “what changed” and “who changed it.” The repository change log saves you the detective work and allows you to instantly home in and fix or revert the offending code.
Seeing no changes recorded in the repository is itself a valuable clue — it means you can stop eyeing your colleagues with suspicion and start investigating the source data.
Having a change history and being able to revert selectively means that you can recover from a mistake in minutes instead of hours.
When it comes to code, git proves the old clichè: use it or lose it.
10. Comments — 30 min
Comments may be ignored by the compiler, but they should never be ignored by the developer.
There is no greater frustration than looking at another developer’s uncommented code attempting to reverse engineer an “ought” from an “is.” Even our own code may wax perplexing if allowed to fade into the recesses of our memory over time.
Short comment snippets like “procedure to calculate sales tax” or “union to get historic records” before a lengthy bit of code can save untold amounts of investigation. Comments can serve as reminders of why a change exists (e.g., “patch until source system is updated”) or even pointers within project tracking systems (e.g., “requested by Sales dept. in ticket-1234.”)
Tools like SqlDBM and dbt understand that comments have value beyond just the technical team. Both of these tools provide ways to maintain functional comments at column and table levels to make life easier for those who may not be familiar with the data (SqlDBM via code-free browser-based interface and dbt via a YAML file.)
The ability to read descriptions in plain English regarding the uniqueness of fields or the nature of the data in individual columns helps save analysis and prevent tickets and emails from users.
11. Meetings — 45 min
Meetings are toxic. They convey a pitifully small amount of information per minute compared to other mediums (email, IM) and tend to center on vague, abstract ideas rather than concrete points (have you ever held a meeting about a specific line of code?)
Despite this, and because we are social creatures, we accept, and then feel compelled to throw our colleagues into this time-sucking vortex lest they feel excluded or on the off chance that they might have something to add.
Before scheduling a meeting, ask yourself these basic questions:
- Does this need to be a meeting?
- Do all these people need to be here?
- What is the central aim or agenda to be covered, and is it clearly stated in the invite?
- Is thirty minutes enough, or could this be shorter?
Before accepting a meeting, run through the above checklist, and if any of the principles are violated, say so, politely, in your decline response.
Here’s a typical script:
- receive 30 min meeting with vague title (e.g., “sales data questions”) and no description.
- decline politely (e.g., “not sure what sales questions you have, please let me know in advance so that I may best prepare.”)
- receive an updated invite, description now included (e.g., “understand why client country in sales data doesn’t match client master data?”)
- decline politely with explanation or documentation (e.g., “client country is stored at branch level which is what sales data is showing. To obtain client country please see <document link>…)
Great, so you just dodged a 30-minute time-sink, but the heading promised 45! That’s not a rounding error; that’s dead time.
We’ve all been there. We complete a large chunk of work, then, with a meeting looming on the horizon, whittle away the time, unable to begin anything meaningful—dead time.
Keeping meetings short, precise, and in the best case, non-existent will help salvage valuable productivity time and won’t break up your workflow. Your colleagues will be grateful too.
The sum in summary
Work as efficiently as possible while avoiding distractions — that’s productivity in a nutshell.
The tips in this article are generic enough to cover the day-to-day workings of any BI team and beyond. What’s more, these tips are intended to plant a seed and make you aware of how you could recognize and optimize opportunities that may be specific to your exact style of work.
Let’s review the eleven suggestions and see how well we did.
Mins / Hack
30 — SQL IDE
10 — Text Expander
120 — SqlDBM
120 — dbt
30 —Excel
10 — Shortcuts
10 — Reminders
30 — Time Blocking
45 — Git
30 — Comments
45 — Declined meetings
— — — —
480 — Total minutes saved
That translates into one eight-hour workday, and that’s not counting lunch!
Is your favorite time-saving hack on the list? If not, share it in the comments!
