Deciding on an ETL tool for your business
Many business intelligence (BI) tools have features meant to streamline and demystify the ETL process. ETL stands for Extract, Transform, Load, and is a common term used within the BI space. These tools, such as Domo’s Magic ETL, allow users to build out complex transformations with an intuitive, drag-and-drop toolkit.
With tools like these ones, it’s much easier for those without much BI or data experience to build data transformations. These tools tend to abstract common data transformations, preventing the need for complex SQL formulas to do that sort of work.
Why use a drag-and-drop ETL tool?
This means that, with the help of drag-and-drop tools and other, similar features that abstract the data transformation process, users can transform their data in complex ways without any real training in SQL. This means that they can make more effective visualizations, since they have access to more useful data.
Traditional ETL: SQL tools
However, some BI tools still force their users to input a lot of SQL at the data transformation step. These aren’t just last-gen or niche programs, either. Many BI tools on the market don’t have very robust abstracted tools for performing data transformation.
Tools like these expect their users to bring more SQL knowledge to the program. Often, even simple data transformations require SQL queries to function. For those without any SQL knowledge, these tools can be nearly impossible to use. There will be a steep learning curve before the average person can build something useful.
Just because these tools are harder to use doesn’t necessarily mean they’re worse, though. A SQL-heavy approach is just as valid as an abstracted, drag-and-drop approach. Both styles have pros and cons and are good fits for different audiences.
We’ll go over what makes both approaches unique, what would make someone use one kind of tool over another, and which sorts of businesses would benefit most from each approach.
What is SQL?
To understand what makes the differences between ETL tools so important, a BI customer needs to understand what SQL actually is.
SQL stands for ‘Structured Query Language’. It’s the programming language that databases use to manage and query data. BI tools use SQL to do all sorts of different operations involving data, from data transformation, to data analysis, to filtering and cleaning data.
Why is SQL so popular?
SQL is an industry-wide standard, meaning that every BI tool uses SQL to manage its data. In fact, almost every program that stores data uses SQL, which makes it a very valuable programming language to be familiar with.
Most of what a BI tool does is perform various SQL functions on the data that it currently has stored. Even basic operations like filtering a data set or putting it in alphabetical order are done using SQL. However, most BI tools use strategies that keep the end-user from having to do literally everything themselves by typing out an SQL query.
Learning SQL
Luckily, SQL isn’t a massively complicated coding language. With just minimal training, users can often become proficient enough in the language to perform basic transformations. Users don’t need years of experience, like with some other programming languages.
Considerations when using SQL
In general, the more specific the function is to the data, the harder it is to abstract. A BI tool can easily sort a column in ascending order, because a ‘1’ is always going to be less than a ‘2’, no matter what the data actually looks like.
Most data functions can be more complex than that. Data transformation, in particular, is notoriously difficult to abstract, since so much of the processes rely on the specific data contained in each data set. Tools that abstract some data transformation functions have to explain to their users how to use them correctly.
Great flexibility for power users
Tools like these allow for greater flexibility in how a user can perform a transformation. Instead of relying on the drag-and-drop tool, users can transform their data exactly how they’d like it. They can also do more complicated transforms that wouldn’t be possible with the basic cards in a drag-and-drop tool.
However, as we’ve already mentioned, they’re not very user-friendly. It also takes longer to actually build a transformation using a coding tool, since the user has to manually type out the whole process. It’s much faster to just drag and drop cards.
How BI can simplify SQL
Most of the basic functions in a BI tool are abstracted, meaning that the BI tool is turning some sort of human input into an SQL query all by itself. For example, a user might click on a table column to put the numbers in that column in descending order. That input executes an SQL formula that sorts the column, but the user doesn’t have any idea there’s code running.
Different BI tools offer different levels of abstraction. Some tools abstract a large percentage of their functions, so that users can build visualizations and dashboards without getting too bogged down in code.
Drag-and-drop ETL tools
Drag-and-drop ETL tools allow users to build out complex transformations with minimal SQL input. They’re especially common in BI tools that are designed for business users, since many do not have prior experience with SQL. In tools like these, users don’t need to explicitly spell out what they want to happen to their data. They just select the operation they want to execute from a list, configure the operation slightly, and they’re good to go.
This kind of ETL tool often visualizes the data transformation process using a flowchart. Users start with an initial dataset or datasets, and then they can see how their data flows from operation to operation.
Key benefits to drag-and-drop ETL tools
As stated above, this sort of feature is excellent for those without any technical expertise. Users rarely need to know any SQL, and if they do, it’s pretty minimal. Often, these tools have specific ETL cards that allow users to write custom SQL code, if they’re so inclined.
Simplify complex transformations
Tools like these allow those without SQL knowledge to build far more complex transformations than they’d be able to otherwise. Operations that would be complicated to spell out in SQL, like pivoting a row or executing rank and window functions, are streamlined into sets of simple-to-use cards.
Quickly generate actionable insights
This way, it’s much easier to get actionable data out of the transformation process. Users can perform the transformations that will actually result in data that they can use, rather than having to make do with the handful of simple transformations they can actually do in SQL.
Users without much SQL experience will be very well-served by drag-and-drop ETL tools and other features that abstract the transformation process. Those with more SQL experience, who want to do more complex transformations, might feel limited by the tools.
Key benefits of ETL tools that allow for SQL inputs
Most BI systems have some sort of feature that allows users to enter in their own SQL code as part of the ETL process. Often, BI tools offer these features in addition to more user-friendly SQL builders, but for some tools, this is the only way to perform ETL.
Sometimes, these features help make the process a bit easier. For example, a tool may have a SQL editor that has a list of predefined formulas and a way to check for syntax errors. Other tools just have a box to type in without any special features.
Regardless of what the editor actually looks like, these coding tools are basically unusable for people who don’t have a comfortable level of SQL expertise. They expect the user to largely know what they’re doing.
Which approach is better?
Both of these approaches are equally valid. It’s hard to say that one approach is better or worse than the other, since they’re designed for different use cases.
However, one approach will more likely be a better fit for a given business. A business that doesn’t want to invest in SQL training or waste a lot of time on transformation will benefit from drag-and-drop tools the most. Businesses that already have a lot of SQL experts and want to do complex transformations will probably want raw coding tools.
Find a BI tool that simplifies the ETL process
Many BI systems offer both a user-friendly, no-code ETL tool and an SQL editor. This way, those without much SQL experience can build their own simpler transformations, but those with SQL expertise can build out more complex ones. If you’re not sure which sort of tool will be best for your business, it’s usually best to look for a system like these.
Conclusion
Both drag-and-drop tools and coding tools have their strengths and weaknesses, and businesses have their reasons to prefer one tool over another. By adopting an ETL tool, your company can make better use of its business data faster than ever before. Modern BI solutions offer great features for both drag-and-drop and coding ETL.
