Tools of the Trade is your destination for data and analytics skill building: From dashboards and reports to embedding analytics and building custom analytic apps to SQL secrets and data deep-dives, whatever you need to know to be better at your job, you can find it here.
Whether you’re on-site or connecting to distant data sources, the ability to query and manipulate data via SQL is the data team’s bread and butter. Whether you’re new to the world of databases or just want to refresh those skills, this robust list of how-to’s will give you the terms, tricks, and tips you need to get more out of your data and uplevel your skills.
Python, R, and SQL in under 4 minutes
As datasets become bigger and more complex, Python and R will be more and more necessary to execute the advanced analytics necessary to pull deeper insights out of them. SQL, a venerable and versatile query language, is still a reliable workhorse for a wide array of use cases and helps lay the foundation for materialized views and other forms of data preparation to enable advanced analytics. Get an intro to these three powerful languages in this quick video.
Joining the first row is such an essential task that we’ve laid out not one, not two, but four different ways to do it, depending on how your database is set up and the kind of information you’re dealing with. As with most of these how-tos, you get copy-pasteable code to help you build your own customizable query. Give it a try and start joining rows with ease.
If you want something done right, you have to follow the proper steps in the proper order. The SQL order of operations is all about helping you put together error-free queries that run efficiently. Dig into the “from” and “where” commands and understand the best practices for deploying them and other instructions for optimal outcomes.
Like any language, SQL has a wide array of terms and syntax to keep orderly in your mind. If you could use a brush-up or want a comprehensive introduction, this cheat sheet is made for you. Learn about operators, expressions, variables, and more, in this handy reference that you can keep around as you grow your skills or anytime you want to make sure your queries are in top shape.
Ideally, most companies have an analytical database for in-house queries, that’s separate from their production database, which is used to power the company’s actual software. However, regardless of your organization’s setup, you may find yourself querying the production database and when you do, you want your query to run as efficiently as possible, to avoid hamstringing the system and slowing performance for your users. This guide will help you do just that.
There’s more to life on the data team than just SQL. A wide variety of modern databases have arisen to tackle the challenges of Big Data, taking a variety of forms, each suited for different kinds of data and tasks. Whatever your company does, choosing the right database to build your product or service on top of is a vital decision. Explore your different options as you figure out which of the many databases will be the best fit for you.
Informational sessions, user acceptance training, and performance load testing are all vital parts of making sure that your new software suits everyone’s needs and can handle the strains that your organization will throw at them. Get an in-depth introduction to this important process.
Redshift, like BigQuery and Snowflake, is a cloud-based distributed multi-parallel processing (MPP) database, built for big data sets and complex analytical workflows. Fundamentally, they are different than transactional databases often seen in the past, and it’s important to understand the distinctions. This detailed introduction will give you the background you need to set up your own Redshift instance.
Laying the foundation with data
Data and advanced analytics are the future of every business. Data teams are key to making that future a reality by connecting software systems to the right datasets and making sure that the right insights come out of them. Whatever your company does, they’ll rely on you for accurate, efficient queries to help make smarter decisions, which require the proper set-up of your pipelines and data warehouses. You help lay the foundation for your company’s success and with this collection of articles, we hope you’re a little better able to do that now.
With over seven years of experience in a variety of technologies, former Sisenser Carmen DeCouto is dedicated to empowering advanced data teams as they tackle the next wave of industry-redefining challenges.