Know Thy Data and to Thine Own Data Be True

This is a great quote that I read about making sure that you know your data and that by knowing your data you are being true to it when you say "These are the numbers for sales you were looking for" to your boss or higher up requesting the numbers. Without knowledge of the data, how can you be certain that the numbers you are giving are correct? The answer, you can't.

When you are tasked with, let's say getting the sales of a current store for the day, if you don't know how to query the database for the information that's a problem. You will need to spend some time getting to know the data, or playing with the data, that you are working with. That means opening up whatever database tool your company is using whether it be MySQL, pgAdmin, or SQL Server 20XX and figure out how to manipulate the data to get the results that you want.

But What If You Don't Know What Table the Data Comes From?

That is what I mean by "playing" with the data. In order to know what table or tables in the database store the information you are looking for you need to know the data well. If you don't then take some time getting to know it. It can't hurt for you to play around with some SQL queries and see what comes back. Just make sure you don't do any commands that overwrite the existing tables. That would definitely be a bad thing.

An Example Project

The other day I was tasked with creating a report that generates the sales data for a particular store location by a time interval of 15 minutes. Now this was my first actual report task and the company I work for knew this. I was given a PDF report that was actually just a scanned copy of the printed report that was saved as a .pdf file. My first instinct was to try to read the information from this pdf document using PyPDF2 and Python. Well because it was images of a table and not actual text that wasn't going to work. Which meant that I would actually have to get my hands dirty writing some SQL queries.

I had some experience with SELECT statements and the likes but I knew absolutely nothing of the data. Most of my time was spent querying the data and manipulating through an API to show the data in our Admin site that I created several months ago. I knew of the data and how to access it, as in what database the data was in, but no idea which table or tables the data actually resided in. I had a choice. I could reach out to the API that I knew and try to get the data that way then try my best to clean it and make it nice and neat while not truly understanding if the data was correct. Or my other option was to play around with some SQL commands and query the database tables to find the answers.

Which Option?

I chose the second option. I needed to get better at querying database tables and also I needed to get a better understanding of the data. So I spent the next 2 weeks understanding how to manipulate the data from these tables. I learned that the data I needed was placed in four different tables. Really it was only one table but in order to make sure I was querying data for the right store and had the correct count of sales and guest counts, I would need to query the four tables find the data and make sure it was correct. Then I could use the one table that joined the other three together to manipulate the data and produce a report.

What Did I Learn?

By going down the road of querying the database and learning the data I know have a better idea of how the sales are calculated as well as where they are in the database. Although this particular report is only for one location I now know how to generate the data to produce a report for any of the other stores that might request the number of sales for this interval. The next step is to use SSRS and SSIS to create an automated report that the store can use.

Conclusion

As a software engineer that primarily used Python, TypeScript and other various programming languages I was naïve in thinking that SQL wasn't an actual programming language. I can now officially say that I was wrong on that. I enjoy using SQL and will continue to use SQL for any and all database tasks. Unless of course the data is stored in say a Redis or MongoDB instance. SQL won't really help me there. If you were like me and thought that SQL wasn't a real programming language or if you thought that you wouldn't really need to know it because you worked with creating web sites then I urge you to take a step back and maybe give SQL a try. You might be surprised at how much you enjoy it. And how much better of a developer you will be by knowing how the data is stored that your backend logic will benefit from it.