How can I start learning and exploring the field of Big Data Algorithms?


Someone asked this on Quora about how to learn & explore the field of Big Data Algorithms? Also, mentioned having some background in python already and wanted ideas to work on a good project so with that context, here is my reply:

There are two broad roles available in Data/Big-Data world:

  1. Engineering-oriented: Date engineers, Data Warehousing specialists, Big Data engineer, Business Intelligence engineer— all of these roles are focused on building that data pipeline using code/tools to get the data in some centralized location
  2. Business-oriented: Data Analyst, Data scientist — all of these roles involve using data (from those centralized sources) and helping business leaders make better decisions. *

*smaller companies (or startups) tend to have roles where small teams(or just one person) do it all so the distinction is not that apparent.

big data

Now given your background in python and programming, you might be a great fit for “Data engineer” roles and I would recommend learning about Apache spark (since you can use python code) and start building data pipelines. As you work with a little bit more than you can learn about how to build and deploy end-to-end machine learning projects with python & Apache spark. If you acquire these skills and keep learning — then I am sure you will end up with a good project.

Hope that helped and good luck!


Introduction to SQL Performance Tuning for Data Analysts



SQL is a common language used by data analysts (and even business users!) for data analysis — one of the reasons is popular is because it’s not that hard to pick it up. Sure, there is some learning curve especially if you don’t have a computer programming background but once you learn some basic commands, you will be able to apply it and answer a lot of questions. So it does give you lot of power! But sometimes you run into issues where your SQL queries are taking forever to complete and you wonder why that’s the case. In this post, I am going to introduce you to performance tuning that will help to troubleshoot next time you run into performance problems.

Performance Tuning SQL

Performance Tuning SQL

Performance tuning Hierarchy

your queries are slow due to one of the three reasons listed below:

#1: SQL Query optimization

#2: Database software, environment & optimization

#3. Hardware

You should start at Level 1 which is query optimization and then work your way down to other levels. This post will focus on SQL Query optimization as that is something you can control and it is also the most common root cause. Let’s focus on this first and then we will explore other options.

Performance Tuning SQL Queries

Depending on your skill level, you can look at a lot of things. But for the purpose of this blog post lets say you have beginner – intermediate SQL Knowledge and with that, you can look at following things:

  1. Size of your tables: If you are querying tables with millions of rows then that is going to slow down your queries. You can start off with limiting the amount of rows that you are working with using SQL clauses like LIMIT/TOP (depends on database system you are using) that will reduce the number of rows that database works with. This is great for exploratory analysis where you don’t need to look at all rows. Also, you should consider using “where” clause when it’s applicable. So let’s say you just care about a particular product category OR a particular product category then put where clauses where it’s applicable.
  2. Complex joins & aggregations: If you trying to join tables in such a way that returns a large number of rows then it’s going to be slow! If possible you can apply step 1 (limit your rows) to this as well — so let’s say you have two tables that are trying to join but you don’t need everything from table 1 then consider putting where clause on table 1. That would help. Also, if you are using aggregations along with joining tables then you could consider doing aggregations on individual tables first and put them in a subquery and then use them to join with other tables. Here’s an example where you are aggregating the table before joining them with other tables:

    SELECT cat.product_category,
    FROM (
    SELECT p.product_name,
    COUNT(*) AS products
    FROM Product p
    GROUP BY 1
    ) sub
    JOIN ProductCategory cat
    ON cat.product_name = sub.product_name

  3. Query plan: Also, how do you know which statement is a bottleneck in your queries. You could consider running them individually and see but even if that doesn’t help then you can use something called a “Query plan” — depending on the database system you are using the commands can differ but you can try searching the help section for that. It’s called Query/Execution plans and they help you see the order in which the query will be executed. Also, it will have “estimated” time to run stats (which may or may not be accurate) but still good starting point to see how long it might take for complex queries especially as you make changes, you can continuously evaluate without having to run the query. There’s a bit of learning curve on how to read execution/query plans but they are a great way to check the bottlenecks in a query that you wrote. You can try using a command like “EXPLAIN” before your query and check your database help section to see if that’s not the command.

Database software, Environment, Optimization & Hardware:

Let’s say you have tried everything you could to tune your SQL queries then it’s move to explore other options:

#2: Database software, environment & optimization: This is usually owned by Dev Ops or IT team and you will have to work with them. Depending on your team size, there might be a DBA, System Admin or DevOps engineer responsible for this. Here’s few things you should check out along with the IT team:

  • Are there a lot of users running queries at the same time?
  • Upgrade to database software and/or perform database optimization (e.g. indexing)
  • Consider evaluating a database that supports analytics better like Vertica, Redshift, Teradata, Google BigQuery, Microsft Azure SQL Data Warehouse among others — if you have 20+ users hitting a database for querying and have tables with 25M+ rows then this is worth evaluating! Your mileage may vary (depending on your hardware) but I am sharing these thresholds just so you have a starting point. These databases are different in architecture compared to something like MySQL which will bog down as you start to scale analytics in your org.
  • Are you querying a production database that also used to support other apps? If so, consider requesting a copy of a database to work with. IT should be able to set up a copy that gets refreshed let’s say nightly and that should help. You should then route all SQL users to this database and restrict access to production database.

#3: Hardware:

Since database is a software, it is constrained by the resources that it is allocated at hardware level just like any other software. You should dive deeper into this if #1 & #2 don’t work out — This is not the most common root-cause but as a rule of thumb, you should be scaling your hardware resources as other systems are scaled too. if that’s not done regularly then you will hit hardware issues. Also, don’t just upgrade your hardware, as I referred to earlier in #2, consider looking into databases that are better for analytics like vertica, redshift, bigquery etc. Compare all options & do an ROI analysis as upgrading hardware is usually a “duct-tape” solution and you will run into it again if you continue to grow.


So you now have a framework which should help you when you run into SQL performance problems! Now it’s your turn, I would love to hear about what you did when you ran into performance problems in any of your data analysis project.

How do I pursue career in data warehousing?


Someone asked this on quora, and here’s my reply:

In the data world there are two broad sets of jobs available:

  1. Engineering-oriented: Date engineers, Data Warehousing specialists, Big Data engineer, Business Intelligence engineer— all of these roles are focused on building that data pipeline using code/tools to get the data in some centralized location
  2. Business-oriented: Data Analyst, Data scientist — all of these roles involve using data (from those centralized sources) and helping business leaders make better decisions. *

*smaller companies (or startups) tend to have roles where small teams(or just one person) do it all so the distinction is not that apparent.

So, it seems like you are interested in engineering-oriented roles — the role that focused on building data pipelines. Since you are starting out, I would suggest that you broaden the scope to learn about other tools as well. While data warehousing is still relevant and will be in some form or another for next few years, Industry (especially tech companies) have been slowly moving towards Big Data technologies and you need to be able to adapt to these changes. So learn about data warehousing, may be get a job/internship as a ETL/BI engineer but keep an eye out on other data engineering related tools like Hadoop ecosystem, spark, python, etc.


How do you generally detect a fraud using analytics?


There are two broad range of algorithms that can help you detect fraud: 1) classification (supervised) 2) clustering (unsupervised)

Fraud Analytics Anomaly Data Science

Now it’s a fair assumption that fraud is pretty rare and it’s an outlier in your data. In other words, it’s a anomaly and the process of identifying them is called Anomaly Detection.

So under classification, there are algorithms out there specialize in “anomaly” detection like one-class SVM and PCA based anomaly detection. Try them out on your dataset and see if it’s able to capture “anomalies” in your dataset. While you are at it, don’t discount traditional classification algorithms either, they may be useful as well. You will have to train these algorithms and that’s why they are called “supervised”.

There an alternate approach. Which is to use unsupervised algorithms called “clustering” techniques. You could try something as simple as K-means or something more sophisticated. I haven’t used clustering much for solving fraud problems and have usually deferred to anomaly detection algorithms for this. But I am throwing this out there for making sure you know all the options! I can see these algorithms being applied to exploratory analysis where you are just exploring your data to find outliers to study them.

Hope that helps!


What are the differences between big data developer and data analyst?


It depends on how the Analytics & Data Science team is structured in an org but usually you will see following trend:

  1. “Big Data Developer” usually rolls up under the Engineering org. They are responsible for building the data pipelines that feed data to the “data platform” — they use things like Hadoop, Spark, Custom Code, ETL tools, etc to build data pipelines and are responsible developing and maintaining the data platform. And to succeed in this role you need to have deep technical chops. Other titles for this role: Data engineer, Software engineer, etc.
  2. “Data Analyst” usually rolls up under some “business” team like strategy, operations, growth, product, marketing, sales, etc. Data Analyst are the link between the “data platform” and the “business” — these guys are primary consumer of the “data platform” (sometimes you might see shared ownership of data platform between engineering and analytics). They help solve business problems using data and pull data from the “data platform”. These guys need to have a good balance between business and technical skills to be successful in this role.

View the question on Quora.

Machine Learning Algorithm Cheat Sheet:


If you’re getting started with Data Science & Machine Learning then I think this would be a great resource for you. This “cheat sheet” helps you select the “algorithm” to test depending on the problem you are trying to solve and the data-set that you have.

Download link: (Courtesy: Azure Machine Learning)

Also, even though the cheat sheet was created to help you with “Azure Machine learning” product, it’s still valid if you use other machine learning tools.

Azure Machine Learning Algorithm Cheat Sheet


Data puking and how T-mobile alienated a potential customer:


I saw this ad on a highway earlier today and my reaction: why would I switch to a network that has just “96%” coverage.

T mobile ad — example of data puking

…instead of converting a potential buyer, this ad actually made me more nervous. You know why? Its a case of what I like to call “data puking” where you throw bunch of numbers/stats/data at someone hoping that they will take action based off of it. So what would have helped in this ad? It would have been great to see it compared against someone else. Something like: we have the largest coverage compared to xyz. My ATT connection is spotty in downtown areas so if it said something like we have 96% coverage compared to ATT’s 80% then I would have been much more likely to make the switch.

I wrote about this adding benchmark in your analysis here

Takeaway from this blog: don’t throw data points at your customers. Give them the context and guide them through the actions that you want them to take.