DEV Community: Alex Antra

Data abstinence in the name of being an ethical data company is not a sound operating model

Alex Antra — Mon, 28 Sep 2020 09:01:52 +0000

We currently exist in a dichotomy when it comes to how companies use our data.

At one end of the spectrum we have companies like Facebook and Google who have in the lovely words of HIBP have; leaked, sold, redistributed and abused [our data] to our detriment and beyond our control. I call them unethical data companies.
At the other end of the spectrum we have companies like DuckDuckGo and Signal, who strive to be seen as the ethical alternative.

However the thing that makes them ethical is the fact that they either don’t store your information or that they encrypt it to the point that not even they can look at it. Meaning that even if a government agency had a legally enforceable warrant or a rogue staff member wanted to go looking, it would not be possible.

What makes them ethical is not a comprehensive and transparent framework that explains exactly how the data is used, but rather that they have just removed all possible avenues for themselves to breach your trust. It’s like preventing getting cancer in your arm by amputating the arm.

Simply put this is the nuclear option, and like any nuclear option there will always be fallout.

Great data products require access to lots of good quality data, the more the merrier.

Facebook and Google have so much of your data they can swing a dead cat 180 degrees and accidentally release three great data products.

For example Googles predictive typing feature is built off of a decade of reading your emails.

Companies that don’t collect enough data or obscure it through encryption as a blanket rule are potentially pushing themselves out the market. (Obfuscation where appropriate is recommended obviously)

Without the data to make market competitive products their ability to said products at the same pace as the unethical companies is effectively the same as trying to win poker with a bad hand, you’ll only succeed if the other person makes a mistake.

This potentially means that unethical data companies may be able to sink or even just wait out their more ethical competitors.

DuckDuckGo is celebrating good growth at the moment but we’ve all seen market factors sink more mature companies. All it takes is for the next new thing to require the very data DuckDuckGo has hidden from themselves and they wont be able to compete.

What benefits the user, the industry, and these ethical data companies is to stop using abstinence as a data strategy and to move towards the center of the current spectrum.

We need our ethical alternative product companies to have access to the data they need to stay in the market, especially while juggernauts like Facebook still exist.

However to be able to access that data to stay competitive these companies must establish a data strategy that puts building customer trust front and center.

A data strategy that achieves this by learning how to thrive in the bounds of these three principles: transparency, controls, and education.

To be transparent you need to move beyond these vague and all encompassing privacy polices that are really used to be a ‘get out of jail free’ card. You need to explain to the user what kind of data you store, how and why you use it internally, how and why you use it externally (if you have those needs) and what sort of things you would be open to doing in the future that you don’t quite do just now. And you need to do this in a way that the average user can understand.

You also need to pair that with some some form of framework so your customers know the rules you’re committed to play between. Something like ‘we will never send your personal data to a third party but we may share aggregated non-identifying usage figures under the following examples.’

Say what you will do, why you’re doing it and what you won’t do. Sentences like ‘we may use your data to improve our products’ or ‘we may share data with third parties’ are just not good enough any more.

Then once you’ve written a proper privacy policy you need to provide your end user with granular control.

We need to move away from this gun to the users head approach of denying access to the product if they don’t agree to every single demand of yours. It’s blackmail and doesn’t build trust, it just tests it until a competitor comes along.

Giving the end user an appropriate level of control is paramount to building that trusting relationship. If they can go into your product and choose to prevent their data from being used for X but still allow it to be used for Y you are not only building trust through control, but leading them down a path of making informed decisions.

You want to provide so much freedom and control in your own product that the user can’t help but notice how everyone else doesn’t live up to the same standards.

Then once you have those processes in place you need to keep communicating with your users and give them the space to engage back.

If you’re changing how you’re using the data, if you’re collecting new data, or if you’re about to do something controversial, tell your users early, give them the ability to opt out before hand and reward them for exercising their choice.

If you do this, you may find that people who opt out as a first response may opt-in at a later date. Either because they believe in your product enough to change their stance or because they spoke with others and realized that it may not have been as bad as they feared.

Also communicate about things happening around you and your customer. Actions done by other companies may have negative impacts on you and saying nothing will often perpetuate them.

If a rival company is using ethical data jingo for their own benefit like Facebook does with their end-to-end encryption of WhatsApp messages you have need to face into that and explain why you are still the ethical choice, especially if what the company is doing is lying to their users.

If the only thing keeping users of your product is the ethical sales pitch then the second Google and Facebook have appeared to ‘fix their ways’ then your user base will go straight back to them.

I love these new privacy conscious companies, I’ve been using DuckDuckGo for over two years now and have found my ads less creepy and my data being involved in less breaches.

However I fear the thing that makes them different might be the thing that puts them out of business. Building an entire strategy around doing the opposite of the bad guys is arguably the easy way around the problem and can potentially prevent them from being competitive or surviving.

It’s about time companies serious about being trusted by their users actually took the time to tackle that problem rather than chopping off every single possible avenue that could lose them user trust.

I currently work for a great company called Xero doing all sorts of fun data things. When you're reading my articles I need you to understand that my words are my own, I'm not speaking on behalf of my employer and If i'm talking about something negative in the field that may not be indicative of Xero, I've worked many interesting roles and I read a lot about my field.

Who am I?

Ron-Resist Racism-Soak 🏳️‍🌈🇳🇿

@ronsoak

Hi, I'm Ron!
🛰️ I ❤️ space!
☕ Coffee Addict
📊 Data Analysis Team Lead for @Xero
⚠️ My words are my own.
✍️ Writer on @ThePracticalDev (dev.to/ronsoak)
✍️ Writer on @Medium (medium.com/@ronsoak)
|🇳🇿 |🇬🇧 |🏳️‍🌈

11:11 AM - 29 Jun 2020

You should read....

Article No Longer Available

Google plays the victim in an open letter to Australians. A wolf in sheep’s clothing.

Alex Antra — Mon, 17 Aug 2020 11:01:46 +0000

Open letters seem to be the new way tech throw their weight around, sometimes it’s for good (example) and sometimes it’s for evil (example).

Today we an open letter from Google. And it’s bad. Link.

The letter pushed on Googles Search Engine and YouTube pages outlined how the upcoming Australian News Media Bargaining Code would not only ‘hurt’ their services, but more importantly ‘hurt’ you, the loyal customer they care so deeply about.

The code, currently only aimed at Facebook and Google (Facebook’s open letter probably pending) is designed to address the inequalities that come where these major digital companies can behave like monopolies.

The kind of behavior that means Australian news companies may loose out on ‘airtime’ to multi-billion media companies willing to outbid them, or where Australian news companies have to accept less favorable agreements with Facebook and Google because other parties can pay more money, or where Australian news stories show up on Facebook feeds or Google Search results without receiving any of the ad revenue each company makes from those interactions.

In a letter straight out of the PR handbook, and later signed by Australia’s Managing Director to give it that personal touch, Google claims that having to give space to local Australian news media is very very very bad for Australians.

How bad?

Well this is where things go a bit sideways…

Apparently it puts your google search data at risk.

What does that have to do with the news I hear you say?
Well the code has some key stipulations that the big G and F must comply with to ensure that the code is being fairly applied.
The first is that they must proactively give notice of any changes to the algorithm. Secondly, they must recognize the news as original content, and thirdly they must provide usage data on users interacting with the news content.
It’s that last one google is crying foul of. Effectively the code mandates that Google must provide user data to prove that Google isn’t fudging the numbers.

So is your personal data at risk?

No more than usual.

If you read their privacy policies(Facebook, Google), it’s absolute standard practice to share non-personally identifiable data with third parties. Google explicitly says:

We may share non-personally identifiable information publicly and with our partners — like publishers, advertisers, developers, or rights holders. For example, we share information publicly to show trends about the general use of our services. We also allow specific partners to collect information from your browser or device for advertising and measurement purposes using their own cookies or similar technologies.

Nowhere in the code does it say personally identifiable data, it merely wants to know what demographics are clicking their links so that they can cater their content, because even news outlets still have to do good marketing. Performance and engagement data is something Google and Facebook have always provided their partners, and this isn’t any different.
Which means, no, Google won’t be sharing your search history with Australian News sites.
Google’s entire profit model is built off of your user data helping them sell trends and usage metrics. They have offered every single person and company who uses their services the ability to see who is engaging with their content. So to pivot and complain that they will be forced to provide usage data is a deliberate scare tactic to confuse those who don’t understand just how much of your data Google uses and abuses.

They’ve threatened to make Australians pay for Google.

Google claims this code ‘hurts the free service you use.’

Read this, and read it again until you realize the thinly veiled threat that it us.

It’s currently free to use Google Search, Google News,Google Discover, and YouTube. What’s the only thing that could risk a free service?

Having to pay for it.

Does the new code enforce that Google should charge for their products. Nope.

This means Google is threatening to charge Australians to use their free services, if they don’t get their way.

Wow this Open letter went from nice to ‘nice search engine shame if something happened to it’ real quick.

If nothing else it tells you Google is in the wrong here it should be that the first thing you heard from Google about this code was a bold faced threat.

Another fun claim in the open letter is that apparently the code also gives Big Media Companies Special Treatment, and unfairly benefits large media companies.

So let’s dive in and take a closer look at which Big Media Companies the code says is eligible:

Companies whose annual revenue must exceed $150k on average. While this excludes small news companies $150k revenue is more ‘medium’ sized company, not big.
Outlets that predominantly produce ‘core news’ and publish online. Which means places that cover political, crime, and works journalism. Not gossip mags.
Outlets that adhere to professional editorial standards
They maintain editorial independence from the subject of their news coverage: Which means controlled media, fake news, magazines, politically owned, or influenced media companies are not eligable.
They operate primarily in Australia for the purpose of serving Australians.

Not exactly the scary Big Media Company version of Big Brother.

The code won't actually cover a lot of Australian media companies, just primarily national and state news companies. This is a major lifeline to local newspapers which are vital for a functioning democracy, doing so much to push transparency at the local level. It also takes a stand against fake news and outside influences in the media Australians are exposed to. This allows a fighting chance in case Russia decides to try and turn the next election using the same news influencing tactics in 2016. (link)

So that's what the code outlines is it’s rules to play by.

But what are Googles own rules on ensuring their services don’t unfairly benefit big media companies?

Interestingly, the open letter doesn’t cover that, though they did say ‘You’ll hear more from us in the coming days — stay tuned.” so maybe it’s coming, but the open letter does say one thing that’s important to note:

we already pay them millions of dollars and send them billions of free clicks every year.

The reason why I draw your attention to this is that it’s not relevant. The code isn’t arguing that the news media companies don’t already earn money and get free clicks, the code seeks to address the fundamental bargaining power imbalance caused by Google allowing other media companies to outbid them, or where google earns money off the news coverage but doesn’t pass a cut of the revenue on.

Google is claiming that what is currently in place is fair and that Google is being unfairly hard done by and unrewarded for their charity.

It’s an attempt to protect the major beneficiaries of the status quo, the global media conglomerates, NOT the consumer.

I always get my hackles up when large companies take to writing ‘open letters’.

Traditionally an open letter is something the downtrodden use to get their voice heard, however its recently become a new tactic where large companies use it to attack something they can’t sue.

It’s a marketing scheme used to turn Australians against their government in favor of the company that is earning billions and billions of dollars in profit off of everything you search, every gmail you send, and every YouTube video you watch.

An organization who time and again has demonstrated they have no loyalty to you, the average consumer.

Australians are being used as pawns to protect Googles right to absolute control over how they conduct their business. There is no clear risk here, Google just doesn’t want to be told what to do, no search history will be shared, no content creator will get less views and no media company will be given unfair special treatment.

This has all happened before by the way. This is no different to what happened to news via the airways, when TV channels had to meet certain standards.

Now Google will have to be transparent about usage and fairly negotiate with Australian News Companies on advertising, accurately identify original content, and pay them a cut of the revenue they earn. The only thing being lost here is Google’s chance to profit from anyone with deep enough pockets to shove their content to the front of the queue.

Have your say!

The ACCC is accepting written submissions from anyone in the world to voice their concerns on the code.

Written submissions are due by 5 pm on 28 August 2020 and can be sent to bargainingcode@accc.gov.au.

Reminder: All views expressed here are my own and do not represent my employer, Xero.

Who am I?

Ron-Resist Racism-Soak 🏳️‍🌈🇳🇿

@ronsoak

Hi, I'm Ron!
🛰️ I ❤️ space!
☕ Coffee Addict
📊 Data Analysis Team Lead for @Xero
⚠️ My words are my own.
✍️ Writer on @ThePracticalDev (dev.to/ronsoak)
✍️ Writer on @Medium (medium.com/@ronsoak)
|🇳🇿 |🇬🇧 |🏳️‍🌈

11:11 AM - 29 Jun 2020

You should read....

Article No Longer Available

Doing the Impossible, using ASSISTANT to make a SQL Linter (and how you can make it lint whatever you want)

Alex Antra — Thu, 13 Aug 2020 12:51:44 +0000

First of all what is a linter?

For those who don't know a Linter is a tool that analyses code to flag errors, bugs, and stylistic errors

It's a great way to ensure that people are coding in a similar and consistent manner and not like a psychopath......

These sadly tend not to exist for SQL.

The highly contextual, and wildly variable nature of data stores means that while we all use the same keywords, but the devil is in the details.

For example it's quite common to champion the KISS principle in SQL code, just because you can join 15 tables together in one super query doesn't mean you should, it makes it difficult to test and maintain, and a clear sign you're a psychopath......

And while we could potentially build a coding framework or a linter to enforce that, there are just times where you have no choice but to join 15 tables together as its all down to how the data works together. Can you see the dilemma? You really can't tell people off if they have no choice.

In practice this generally means a data team may agree on a set of coding standards, which will differ in their level of comprehensiveness, and are left enforcing it based on how vigilant the peer reviews are.

They don't exist but you have one now?

Enter stage left!

Assistant by Tomasz Smykowski

Tom SmykowskiFollow

Follow me, if you want to become 10x Developer. I am sharing tips based on 20 years of coding, that gave me a satisfactory career and balanced life.

A generic linter VSCODE extension that allows you to program in your own custom rules using regex.

I was genuinely very excited when I first saw this. I was in a bar at 2am and had seen the article and sent it to my work email right there right then. My friends where quite rightly judging my commitment to sparkle motion.

But I digress!

How did it go?

Well at first, not well!

I gave it a whirl and found that it wasn't working for the sorts of things I wanted it to catch. I quickly discovered that it didn't evaluate rules across multiple lines, which is important in SQL because we are forever writing:

Select    column_name,
          column_name, 

From      table_name;

And then running it, and then it errors, and then we change it, and then we do it again.

So I emailed Tomasz just to confirm whether that was intended functionality. That was on the 10th of July.

On the 13th of July Tomasz responded confirming that the linter does not work across multiple lines and that he had created an issue to get that changed as a feature request.

I thought, that's nice of him, but I was realistic. We all groom backlogs for a living and we all know that some backlogs last multiple presidencies.

Then Friday July 17th, 5:45pm, I'm drunk, work drinks, great time, email from Tomasz.

He did it!

Houston we have multi line support.

And yes I couldn't wait until Monday to try it out.

So how did it go? (The sequel)

Amazing....

We've loaded in fifteen rules in the past month and they're working well.

How to add rules

Well first you need to download the extension in VSCODE.

Then you need to go to your settings.json file, either in your global or workspace area, depending on how you work.

Add the following code to start loading in rules in regex:

"assistant": {
      "rules": 
      [
        load rules here :) 
      ]
      }
}

Note:

You will need to double escape regex syntax i.e \b should be \\b

You may need to change the modifiers however, i = case insensitive, s = multiline, and g = global so I would only remove them if you 100% know what you're doing.

Some Examples

Comma on the last column

As mentioned earlier this is the the number one mistake we commonly make.

To add this rule to your linter:

       {
        "regex": "(,)(\\s*\\W*)(from)",
        "message": "Reminder: you don't need a comma on the last column",
        "modifiers": "sig"
       },

Don't forget the ON after a join

Sometimes I go straight into adding 'and' conditions after my join and forget the on. (I've also been guilty of having more than one ON clause but that's not covered here.)

To add this rule to your linter:

       {
        "regex": "(join)(\\s*\\w*)(\\s*\\w*\\s*)(and)",
        "message": "An ON keyword must follow a join condition",
        "modifiers": "sig"
       },

Ban people from using CTE's

In our environments CTE's aren't great for performance. However I still stand by never using them in production code even if they perform well. Something I'm not alone on:

How To Write Better SQL: Advanced SQL Episode 2 - Please Stop Using So Many CTEs

SeattleDataGuy ・ Jul 6 '20 ・ 6 min read

#sql #datascience #database

       {
        "regex": "(with\\s)(\\w*\\s*)(as)",
        "message": "Please refrain from using CTE's in production code. They are bad for redshift.",
        "modifiers": "sig"
       },

Don't use USING in joins

Other than trying to avoid an accidental bad join, we ban the use of USING as ON is just what we decided is best practice.

        "regex": "(join\\s)(\\w*\\s*\\w*\\s*)(using)",
        "message": "Do not use USING when joining tables, use ON.",
        "modifiers": "sig"
       },

Don't get your keywords out of order

How many times have you gotten your order and limit the wrong way around? Or Group / Having / order?

       {
        "regex": "(limit\\s*\\d*\\s*)(order|group|where|having)",
        "message": "Limit should be the last keyword in the query.",
        "modifiers": "sig"
       },

To reassure commitment...

In modern environments you don't need to commit your code any more.

       {
        "regex": "commit;",
        "message": "I can ensure you that Redshift does not have commitment issues, you do not need to write commit.",
        "modifiers": "sig"
       },

Prevent UNION

You should only ever use UNION ALL. A union scans for duplicates which BURNS compute and it will throw out duplicate rows. My advice is that if you want to exclude duplicates there are better ways.

       {
        "regex": "(union\\s*)(select)",
        "message": "I can't think a valid reason to use union. You should always be using union all. ",
        "modifiers": "sig"
       },

Curb some bad compression

Only compression nerds like me know that a varchar(1) in redshift takes up over 7 bytes of space, when you could just use BOOL for a small 1 byte.

       {
        "regex": "char\\W1\\W",
        "message": "You should be using a boolean if you are building a char with 1 space.",
        "modifiers": "sig"
       },

More info about Redshift Compression here:

Article No Longer Available

And More

We've made rules to catch out common spelling mistakes like 'selct' or to enforce certain practices like using '!=' over '<>'!

So how do I make my own?

So as mentioned earlier you need to download the extension in VSCODE and then set up your settings.json

Now you need to set up a rule, use this template:

       {
        "regex": "",
        "message": "",
        "modifiers": ""
       }

Don't forget to add a , after each rule, i.e

       {
        "regex": "",
        "message": "",
        "modifiers": ""
       },
       {
        "regex": "",
        "message": "",
        "modifiers": ""
       }

Then figure out what you want to catch out.

Beware: You are limited to what you can detect using a REGEX pattern. So you'll need to think about how you will do that and just accept that some rules you won't be able to catch.

For example not closing brackets ( ), it's hard to write a REGEX rule to catch that as what you really want is an exception rule.

To test whether I can do what I want to do I use something like rubular to test my regex.

If I get it working, I load it into the settings file and away we go!

Things to Note:

It's got its limitations, it won't work for ever rule, primarily due to the limitations of REGEX not the extension.
It works all over VSCODE (even in the settings file) so if that annoys you, make sure to use the workspace feature, though there is a feature request to be able to lock the rules to specific file types (this will be handy for those of us who switch coding languages)
If you make your rule too generic you risk crashing VSCODE as the rule will scan as much as the rule allows.

Share your findings!

Tomasz has a github for this extension where he asks that you submit your own rule file to help others! CSS, HTML, Ruby, Python!
I've already added my sql rules as a pull request :)

tomaszs / Hintly

Boost your development by providing custom tips displayed in the code in Visual Studio Code

Demo

Hinty (formely: Assistant) is language and framework agnostic.

Example workspace configuration for Angular/TypeScript. It informs about a bad boolean Input declaration in Angular component. Normally it does not trigger build or linter errors and is a hard to track problem:

{
 ...
 "settings": {
  "assistant": {
   "rules": [
    {
     "regex": "@Input\\\\(\\\\) .*: false;",
     "message": "Define property value with =, not with:"
    }
   ]
  }
 },
 ...
}

Result:

Hinty - Dynamic Hints For Your Code

Are you annoyed that your notes on hard to fix issues are not available when you need them the most - while coding? Is setting standards for the team code broken even if you have a centralized place for rules because it i hard to keep tabs on them all the time?

Never make the same mistakes again!

At last there is a solution to these problems. Let me present you a groundbreaking Visual…

View on GitHub

Reminder: All views expressed here are my own and do not represent my employer, Xero.

Who am I?

Ron-Resist Racism-Soak 🏳️‍🌈🇳🇿

@ronsoak

Hi, I'm Ron!
🛰️ I ❤️ space!
☕ Coffee Addict
📊 Data Analysis Team Lead for @Xero
⚠️ My words are my own.
✍️ Writer on @ThePracticalDev (dev.to/ronsoak)
✍️ Writer on @Medium (medium.com/@ronsoak)
|🇳🇿 |🇬🇧 |🏳️‍🌈

11:11 AM - 29 Jun 2020

You should read....

Article No Longer Available

What they don’t tell you about being an analyst

Alex Antra — Mon, 29 Jun 2020 12:01:36 +0000

There are a boat load of articles across the blog-o-sphere outlining all the amazing reasons you should switch to a career as an analyst, however as someone who has been an applications analysts, an information analyst, an operations analyst, a data analyst and now a Data Analyst Team Lead I feel obliged to outline the stuff those articles seem to consistently gloss over.

Now the goal isn’t to scare you away. Due to the lack of formal education around data, a vast majority of those working in data have come from another profession which makes it a very welcoming field for those making the switch, as we’ve all been there. But it’s important you make the switch fully informed.

It's an old but immature field.

Data and databases in this format have been around nearly half a century, the structure query language first appeared in 1974! However this hasn’t brought with it 50 years of advancement per se. The field has moved along, some key people have worked very hard to get it here, however when compared to other fields in tech we appear quite immature.

Compare us to software development which in less time has found a way to build entire coding frameworks to enforce standard ways of coding, and end to end CICD flow with automated testing even.

Data on the other hand....

The actual SQL language differs depending on the databases and you have, and you need to remember those differences yourself and adapt, no one has made an universal sql interpreter yet.

Then, depending on the version of the database some functionality may be missing and not all databases get an upgrade. I remember one role where I was responsible for two TSQL 2003 DB’s and one 2009.... in 2017. Sometimes I would come across the perfect solution on Stack-overflow only to find that that code didn’t work on those older versions.

Best practices also differ depending on the underlying technology. A common example is the short cut practice known as a CTE. In databases where the memory is centralised CTE’s can be very quick to execute and while not considered best practice when making production code its very commonly used in day to day adhoc queries. For databases that run on distributed processing which have multiple memory banks (ie the job is shared) CTE’s run horribly and should be avoided at all costs.

The lack of coding frameworks isn’t for a lack of trying, the key difference between the data field and software development field is that the data is completely different in every single company. Which makes coding highly context dependant which is why we don’t have as much opportunity to standardise the code going in or to automate what's coming out.

This problem of highly contextual data goes beyond just the SQL language itself, some analysis is done in SAP, Python, SPSS, and R. The problem remains the same in each of those methodologies.

We’re also a volatile field. No one has really settled on a best practice for very long and you’ll see a lot of migrations in your time as companies ditch now obsolete hardware or methodologies for the new shiny only to do the same again within 2-5 years. I’ve witnessed six migrations in seven years.

We’ll get there eventually but until we do, data analysis will continue to be very manual, inconsistent, and messy. Which just slows everything down, sharing code is hard to do as everyone codes differently, the data bases are different, and the underlying data is contextual. Code that works on one platform, won’t work on another, Testing someone's code is often down to ‘best efforts’ at the time. It's not an exact science and as other fields get better at what they do it leaves the us looking a bit immature by comparison.

You’ll be limited by both technology and customer.

In the same vein as the above point your technical ability will be limited by what systems you use and what's being asked of you. For many a career as an analyst is a responsive thing, you’ll provide what you're being tasked to do.

99% of what my customers need are in our SQL database, which means my skills in other languages are not being challenged. I know python, dabbled in google analytics, and I'm familiar with doing analysis on JSON but I'm forever loosing my edge as all I need to do my current role is just SQL.

In some roles this can limit your ability to grow out of your role and to the next, and we don’t all have the luxury of dedicating time outside of work, you can potentially find yourself stunted in a role and unable to apply for other roles as you don’t meet their requirements but your current role can’t grow you in that direction.

Your customer has an influence on this too. If all that's been requested of you is ‘simple’ stuff you again won’t have an opportunity to grow. A rising tide will lift all boats and it may be necessary to educate your customer base (or wherever your workload comes from) in order for you to be stretched mentally in the role.

Some people may end up in what we call ‘report farms’ roles where you just pump out reports for a demanding user base. These can be some of the most un-rewarding roles and sometimes the least technical ie. just spreadsheets. I would of course caution you to try and avoid these roles but they are hard to distinguish up front.

In the end It’s a customer service job

This isn't a job where you hide in a back room alone for eight hours a day chugging coffee. Nine times out of ten your job is to give other people reports. You will need to talk to those people and often before, during, and after you’ve delivered a report.

Unless your customer has been an analyst in their previous life they will have no context around how long something should take or whether its feasible or not. They will almost always assume its easy and will want it by tomorrow. They will also not be very data literate and I found expectations will most likely be in the polar opposite place from where you need them to be.

They’ll be overly concerned about accuracy when they don’t need to be and will want you to ‘wing it’ and ‘just find a pattern’ in the most nebulous of data sets. They will never really 100% understand what they are asking for and you’ll quickly learn the difference between deliberate scope creep and scope creep as a result of the customer starting to understand what they really need at the later stages of the analysis.

They will be rude and demanding at times, uninterested in answering your questions, “just do it they’ll” say and sometimes they just wont’ understand the problem. Your job, every step of the way, is to be nice and hold their hand. You’ll quickly discover that the only way to navigate through those scenarios is to proactively manage your customer and it’s very hard work but believe me, leaving your customer in the dark and not managing them will cause more work in the long run and a deteriorating stakeholder relationship.

I nearly always hire staff with good stakeholder management skill over those with the technical ability. I can teach you SQL easier than I can teach you to be a customer service super star.

It’s actually very difficult to train for this job outside of the field.

This job is actually nigh impossible to prepare for if you're not already in the field. All the code academy and medium articles will have you believe that learning SQL and how to make a graph is 90% of the job. They'll have you do some left joins and a group by and you're ready for your first day.

Sadly that's not the case.

Knowing SQL is 20% of the job. It's like how knowing how to drive is only a small part of being a taxi / Uber driver. The SQL teaches you how to navigate the companies data. Learning the data, much like a taxi driver learning the city is most of the job. When someone says they want sales figures for a certain region but for only for a customer that has done X activity. You need to know where all that data is housed, and how it functions and how to join it together and if you don’t know, your going to have to figure it out.

The job is also a largely complex problem solving. Figuring out how to smush together, and in what order, multiple data sets to get your customers result turns you into a mini inventor. There is a high likelihood that sometimes you will need to match up a combination of data that no one ever in history has ever done before. There won’t be an answers page for you to turn to at the end and again no online course get truly teach you that, as the complexity of the problem solving needed will be dictated by your companies unique data.

You also can’t be prepared for big data. Nothing online can prepare you for joining a billion rows of data with another hundred million. Much of being an analyst in a big data space (not all roles are big data) is doing some light data engineering. Knowing how to move such large data sets around in the safest, quickest, most efficient manner is nothing i’ve seen an online data course teach.

The quality of the data will plague you.

In 2020 it’s actually uncommon for companies to be sitting on high quality data. Undocumented and inconsistent data is common place across the field and made infinitely worse by ‘big data’. By the time most companies realized they needed to understand the fidelity of their own data they where already years of data down the line and to go back and retrospectively fix that data would take a lot of work.

You’ve also got the reality that these data sets weren’t built to be joined or analysed, they were built for an entirely different purpose and then your data warehouse has just made a copy. There are scenarios where joining two datasets together is impossible, or requires an enormous amount of manual work to get it done something your customer won’t think about when they make the request.

You'll forever find issues with data, and the bigger it is, the more issues are hiding from you. You’ll find legacy data that no longer done that way, entire days or months of missing data because there was a known or unknown issue at that time. Some data will only be current state and is missing history, while some historic data will be difficult to accurately parse, columns won’t always be named in a descriptive or consistent manner and tables will be named as good as the passing fancy on the day. And don’t get me started on dates. I'm lucky enough to live in a data environment mostly in UTC but I've worked in environments where data was recorded in multiple timezones and that's not easy to work with.

If you're lucky, those datasets will be documented right down to the column, however most companies are deathly allergic to documentation and what you’ll most likely find is a bit of documentation (most likely created by a frustrated analyst) and the rest of information you need will be stored in various peoples heads. In one scenario I had to do reporting on an abandoned feature that wasn’t documented and the only person left in the company who knew how it worked was one of the founders so I had to wrestle my way into their diary ask them to cast their memory back 9 years.

You’ll struggle with primary and secondary data.

As data and companies grow so will the spread of data. In my early days it was common to run reports directly off the production environments, now you risk slowing down the product and causing customer complaints if you do so.
So in most enterprise scenarios that data is streamed or replicated elsewhere into a data warehouse. You now have a primary (source) and secondary (replicated) data set scenario, this can cause issues..

If the source data is changed after it is copied some environments don’t capture that and every day that passes between your two out-of-sync data sets the more inaccurate your secondary gets. Which can be an issue if you're trying to do accurate financial reporting or trying to gauge the impact to customers after an issue.

Some primarily datasets don’t retain history, often overwriting themselves in the interest of speed and saving space, this again can cause issues between sets as both set will no longer be inline with each other.

Then within your own data warehouse you may have a different primary and secondary relationship.

Models which are best described as short cuts are a good example of this.

Say the most commonly requested customer information is actually separated across 12 different tables. Rather than join those 12 tables together over and over again one might create a model that is a single table holding those values in one convenient place. This is a very simplified example of a model but now within your own data warehouse you have primary and secondary data sets of the same data. In simple models this shouldn't cause issue however if data was transformed for better reading or to align with business logic you may find scenarios where you need to make the decision as to whether the model and how it was built suits your needs and you may have to parse modelled and un-modelled data together, again neither one was built to work together and no one could have predicted your exact scenario and an easy solution isn’t promised to you.

You are always going to be tackling the issues that come from having the same data split out into different buckets and it's on you to get familiar with their strengths and pitfalls so to understand what ones to use.

You’ll constantly struggle with metadata, business logic, operational logic, and best practice.

Completely stepping around the fact that most data isn’t documented properly or of a good enough quality, what you’ll struggle with next is what exact documentation is needed and how many different types you'll need to juggle and discover.

Metadata is the descriptive information about the smallest data point, ie what data goes into this column, and if its secondary data what transformations have been applied to it. Aka why does this column exist and what goes into it.

Business Logic is the application of the companies rules applied over the top of the data. So say your a company with a couple of million customers. However some of those customers are test accounts, free accounts, press accounts and so internally the company will have an understanding of what types of customers they should always exclude when they are referring to ‘customers’. This should normally be held across every department so that different departments can be on the same page. This of course will result in some common SQL code back at your end when your customer goes ‘only valid customers please.’ While business logic should be company wide it more often than not ends up being department specific and so marketing will have their own logic you'll have to remember and then so will finance. The bigger the company is, the harder it is to get unified ‘business logic’, sometimes the right business logic can’t be done in the data or takes 9 times a long, something else you’ll bear the responsibility to solve.

Operational Logic is more how the data changes when its interacted with. Sometimes a single action in your product will change 9 different things in 7 tables, you won’t catch or understand that with meta data or business logic alone and its often hard baked into the code of your application. Say a customer misses a payment and your terms and conditions say that after 12 days of non payment the account is deleted. That entire process will change data in all sorts of places at different times over those 12 days and those interactions are hard to figure out by watching the data as those movements will be hidden within the noise. Most of the time you will need to be told by whomever created the operational logic what is happening at what times.

Best practice is similar to business logic but really its more your own internal logic that you and other analysts discover over time or to make up for poor data quality. Like excluding data from a certain ID as that's a testing ID or excluding a certain threshold of data because the data is less realiable past that threshold. Over time you’ll build up a lot of best practice.

At all times of doing your job you need to rely on the above, which may be missing in most cases and so overtime you’ll build up your own understanding but you’ll still be missing most of it.

Most of an analysts job is navigating through the uncertainty of the data caused by the fact that you lack all of the above.

It can be unforgiving.

There's a lot that can go wrong and it can from time to time go wrong all at once. There will always be ‘that’ report an analyst can remember. The one that kept going wrong.

This can be extremely frustrating and I've seen it push some people out of the field. It can feel like the cards are stacked against you, if the data's bad it’s your job to work around that, if the data’s missing it’s your to explore every avenue, if no one knows what to do or where to look it’s your job to figure it out. If the customer isn't data literate it’s your job to up-skill them, if your customer comes to you with a short deadline it's your job to sprint to the finish line, if the database is slow it’s your job to make sure that doesn’t impact the customer’s delivery date, if they think of something last minute it’s your job to just add that ‘one more thing’. If there is a problem or issue, nine times out of ten you will be the one bearing the brunt of the issue. It is sadly a role where the power dynamic is uneven by default. You can’t account for everything because the spectrum of issues is very very broad and you're too busy doing reports to fix them up.

Conclusion.

I’m still in data after all these years and it’s a career I like, regardless of the above, in fact what I like is tackling the above issues head on but that's not everyone's cup of tea. People enjoy different things and I hope that if you do make the switch you quickly find the thing in data that makes you happy. But if you are considering a career in data, keep the above in mind, it’s not all rainbows and high paying data science roles the medium articles will have you believe!

Reminder: All views expressed here are my own and do not represent my employer, Xero.

Who am I?

Ron-Resist Racism-Soak 🏳️‍🌈🇳🇿

@ronsoak

Hi, I'm Ron!
🛰️ I ❤️ space!
☕ Coffee Addict
📊 Data Analysis Team Lead for @Xero
⚠️ My words are my own.
✍️ Writer on @ThePracticalDev (dev.to/ronsoak)
✍️ Writer on @Medium (medium.com/@ronsoak)
|🇳🇿 |🇬🇧 |🏳️‍🌈

11:11 AM - 29 Jun 2020

You should read....

Article No Longer Available

I built my own SQL tester in Python, then rebuilt it again from scratch,here's what I learned.

Alex Antra — Mon, 23 Mar 2020 18:55:19 +0000

Context

As previously mentioned in another Redshift Flavored Automation Article AWS Redshift isn't that common and so tools on the internet are hard to come by.

Testing tables are important in the data world, did the criteria you specify work? Are there anomalies in your data?

But tables can be big and be made up of multiple different data points and logic. This can mean that to do due diligence for testing a table you might need to run five, ten, fifteen, even twenty separate checks on the table after its been built. Human nature means that this doesn't always happen, and certainly not consistently across every table and analyst.

One of my team vocalized this issue and we got talking about a checklist that we could generate once we've built a table to know what to test, this would also go a long way to helping the tester in seeing whats been mitigated. I played around with the idea of creating some VSCode plugin that reads your code and generates a checklist but the more I looked into it the more a different solution made more sense, and felt easier to do.

Why build a checklist of things to test, which would then require the analyst to create the test queries when I can just jump straight to building a tool that generates the test queries??? Sure this won't cover all scenarios but nothing ever would, the reason you built the table may never be apparent in the code.

So I knew what I wanted to build, however I only knew HTML/CSS, SQL/PLSQL, Batch and a splash of power shell. While I could have done this in PLSQL or power shell I saw this as a great opportunity to learn Python.

Version 1

I had briefly been exposed to Pandas and so I was familiar with data frames, a familiar to someone who works with data every day. A few Pandas tutorials later and I had a general direction.

Methodology

Where I landed in terms of a mechanic was to use the tables Data Definition Language or DDL which is the blueprint for how the table was created. See below for an example.

My script would read this DDL and then based on what data type the column was, String, Integer, Date, would spin off a bunch of per-fabricated tests. So if the column was a date it would spit out a query that tested the min and max value of that date column, something we do to check that we have got records in the correct date range.

I did this by getting the user to copy the DDL to their clipboard and then using the Pandas copy clipboard would ingest the DDL into the script.



sql_load = pd.read_clipboard(sep='\n',header=None, squeeze=1)

I then checked to make sure that the content being loaded was valid, the first word needed to be 'create' so I enforced that and forced an error if it didn't find that.If it did pass the test the script read what the table name was as this would be used later.



#GET FIRST ROW OF DDL
sql_header = sql_load.iloc[0]                                               

#VALIDATE THAT THIS IS A VALID DDL
sql_validate = re.search(r'^\w*\b',sql_header, flags=re.U)[0]               
sql_validate = sql_validate.upper()  
if sql_validate != 'CREATE':                                                
    filename = "script_terminated_with_error.txt"
    error_output = open(filename,"w")
    error_output.write("If you are reading this message, the python script has terminated. \n")   
    error_output.write("Reason? The first word on the clipboard wasn't CREATE.\n")
    error_output.write("This means you have not copied a valid Redshift SQL Table Create Statement to your clipboard. \n")
    error_output.write("For more help refer to: https://github.com/ronsoak/SQL_Test_Script_Gen.\n")
    error_output.close
    os.startfile(fr'{filename}')
    sys.exit("Not a valid DDL, must start with CREATE")
else: sql_table =  re.search(r'\w+\.\w+',sql_header, flags=re.U)[0]

I decided that the output of this script would be written to a text file so I had to get python to create a text file, rename it the name of the table name and then write content to it. But tables are often called schema.tablename and we can't have a dot in a filename so I had to re-arrange how all that looked.



sql_table_file = sql_table.replace(".","][") 
filename = "[table_testing]["+sql_table_file+"].txt" 
filename = filename.lower()
sql_output = open(filename,"w")

So now I've got this whole DDL loaded in but the only thing I need are the column names and there data types, I don't need their compression types nor do I need the tables diststyle,distkey, or sortkey. So seeming I'm using data frames I went through the process of whittling down the entire DDL to just rows that contained a data type, to which I had loaded all of the possible data types into a list called red_types.



#LIST OF DATA TYPES
red_types   = ("SMALLINT|INT2|INTEGER|INT|INT4|BIGINT|INT8|DECIMAL|NUMERIC|REAL|FLOAT4|DOUBLE|DOUBLE PRECISION|FLOAT8|FLOAT|BOOL|BOOLEAN|DATE|TIMESTAMP|TIMESTAMPTZ|CHAR|CHARACTER|NCHAR|BPCHAR|VARCHAR|CHARACTER VARYING|NVARCHAR|TEXT|GEOMETRY")
#READ THE COL NAMES AND DATA TYPES
sql_load = pd.Series(sql_load) 
sql_load = sql_load.str.upper()  
sql_load = sql_load.str.replace(r"\(\S*\)","") 
sql_reduce = sql_load.loc[(sql_load.str.contains(fr'(\b({red_types})\b)', regex=True,case=False)==True)] 
sql_reduce = sql_reduce.str.split(expand=True) 
sql_cols = sql_reduce.loc[:,0:1] 
sql_cols = sql_cols.rename(columns = {0:'COL_NAME',1:'DATA_TYPE'}) 
sql_cols['COL_NAME'] = sql_cols['COL_NAME'].str.replace(',','') 
sql_cols['DATA_TYPE'] = sql_cols['DATA_TYPE'].str.replace(',','')

Then once I had whittled down the data frame to the exact rows I wanted, I created a function that wrote the tests to a text file dependent on the rows data type, and then looped through the data frame.



#DEFINE FUNCTION FOR PRINTING
def col_func(a,b,c):
    if      b in red_nums: 
        sql_output.write("select min("+a+"), avg("+a+"), max("+a+") from "+c+"; \n \n" )
        sql_output.write("select median("+a+") from "+c+"; \n \n" )

    elif    b in red_dates: 
        sql_output.write("select min("+a+"), max("+a+") from "+c+"; \n \n" )

    elif    b in red_string:  
        sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 50; \n \n" )
        sql_output.write("select count(distinct("+a+")), count(*) from "+c+" limit 50; \n \n" )

    elif    b in red_bool: 
        sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 10; \n \n" )

    elif    b in red_geo: 
        sql_output.write("Geospatial Data not currently supported. \n \n")
    else:    
        sql_output.write("Column:"+a+"is not a know Datatype. Datatype passed was:"+b+"\n \n")

for index, row in sql_cols.iterrows(): 
    col_func(row['COL_NAME'], row['DATA_TYPE'], sql_table)

Finally once the script was done, I wrapped a batch script around it so a user could just double click the script and they would be prompted to copy the DDL to their clipboard and then they could prompt the batch script to kick off the python script.

Outcomes

Once I had it up and running I gave it to my team to use and test. We encountered a few issues right off the bat, like the regex used to detect table names needed a few iterations. And data types that had parameters after them like varchar(300) continued to cause issues. It also would hard fail if the wrong thing was copied to the clipboard (like a file).

After a month of testing I showed it off to some other analysts and got them using it. It would throw an error every now and again but not the end of the world for my first python automation.

Learnings

I knew when I made the script that it was over-engineered and relied too heavily on external libraries and hacky workarounds. The header of my script looked like this.



#IMPORTS
import pandas as pd  
import re            
import warnings      
import sys           
import os            

#SUPRESS WARNINGS
warnings.filterwarnings("ignore", 'This pattern has match groups')

I posted the script on a reddit thread looking for feedback and pretty succinctly was told that I shouldn't be using Pandas for this, and if I was going to use Pandas not to use itterows in it.

I knew I could do better.

Version 2

So I set out to rebuild this script not using Pandas, in fact as little external libraries as possible was my aim. So I brushed up on my lists, tuples, and dicts and got coding.

Methodology

Not using Pandas meant I couldn't copy a DDL off of the clipboard, native python can't do this, other libraries can but I decided the DDL could be in a text file and get loaded that way. So the first thing the batch file does now is open up the text file and gets the user to save the DDL into it.

So now I've locked the DDL load to a text file the next few steps are the same. Read the table name and prepare the output text file, and it's file name.

You will note that I've removed the validation that checks that the first word is 'Create' the analyst will figure it out soon enough.



#LOAD THE DDL
load_file = open('test_script_input.sql','r')
file_contents = load_file.readlines()

#GET THE TABLE NAME
table_head = file_contents[0:1]
table_name = re.search(r'\w+\.\w+',str(table_head), flags=re.U)
table_name = table_name.group(0)
table_name = str(table_name)

#FILE OUTPUT
sql_table_file =  table_name.replace(".","][")
filename = "[table_testing]["+sql_table_file+"].txt" 
filename = filename.lower()
sql_output = open(filename,"w")

So no data frames this time right? Which means the DDL has been loaded into a list. How did I refine that list down to just the right columns? Last time took 15 lines of code, this time it's all done in a one line lambda.



#RESTRICT TO APPLICABLE COLUMNS
valid_rows = list(filter(lambda x: re.search(r'\b(SMALLINT|INT2|INTEGER|INT|INT4|BIGINT|INT8|DECIMAL|NUMERIC|REAL|FLOAT4|DOUBLE|DOUBLE PRECISION|FLOAT8|FLOAT|BOOL|BOOLEAN|DATE|TIMESTAMP|TIMESTAMPTZ|CHAR|CHARACTER|NCHAR|BPCHAR|VARCHAR|CHARACTER VARYING|NVARCHAR|TEXT|GEOMETRY)',x,flags=re.IGNORECASE),file_contents))

The methodology is the same, looking for entries in the list that only contain a redshift data type, but this was vastly more elegant, I don't even need to have the data types loaded in a separate list. It could also handle full data type names, where before my script went to the effort to first reduce decimal(50,5) down to just decimal, then it would detect it, while in this script it can detect data types without needed to first remove the brackets.

The next steps are very similar. Now I've got my valid rows, I needed to convert some of the values into strings, and rather than pass the actual data types into the function I passed the high level type. So where it was a int, bigint, decimal I passed it as 'Number'.



#CREATE TEST SCRIPT
for table_line in valid_rows:
    table_line = str(table_line)
    col_name = table_line.split()[0:1]
    col_name = str(col_name)[1:-1]
    col_name = col_name.replace("'","")
    col_name = col_name.replace("(","")
    col_name = col_name.replace(")","")
    dat_type = table_line.split()[1:2]
    dat_type = str(dat_type)
    if (re.search(r'\b(CHAR|CHARACTER|NCHAR|BPCHAR|VARCHAR|CHARACTER VARYING|NVARCHAR|TEXT)',dat_type,flags=re.IGNORECASE)): col_type= 'STRING'
    elif (re.search(r'\b(SMALLINT|INT2|INTEGER|INT|INT4|BIGINT|INT8|DECIMAL|NUMERIC|REAL|FLOAT4|DOUBLE|DOUBLE PRECISION|FLOAT8|FLOAT)',dat_type,flags=re.IGNORECASE)): col_type= 'NUMBER'
    elif (re.search(r'\b(BOOL|BOOLEAN)',dat_type,flags=re.IGNORECASE)): col_type = 'BOOL'
    elif (re.search(r'\b(DATE|TIMESTAMP|TIMESTAMPTZ)',dat_type,flags=re.IGNORECASE)): col_type = 'DATES'
    elif (re.search(r'\b(GEOMETRY)',dat_type,flags=re.IGNORECASE)): col_type = 'GEO'
    else: col_type= 'BAD'
    col_type = str(col_type)
    script_gen(col_name,col_type,table_name)

In the previous version I generated one or two test queries per column type. This time I wanted to go further, and so I thought about more scenarios we would want to check, like if it's an number, checking the 25, 50, and 75 quartiles. I even added headings to give each area structure, while before it was a text file with a number queries. Needless to say my function got a lot bigger.




#SCRIPT_GEN FUNCTION
def script_gen(a,b,c):
    if      b == 'NUMBER':
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing for Column: "+a+"\n")
        sql_output.write("-- Column Type: Number \n")
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing counts of column \n")
        sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
        sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
        sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
        sql_output.write("-- Testing mins, avgs and max values \n")
        sql_output.write("select min("+a+"), avg("+a+"), max("+a+") from "+c+"; \n \n" )
        sql_output.write("-- Testing median, redshift doesn't like doing medians with other calcs \n")
        sql_output.write("select median("+a+") from "+c+"; \n \n" )
        sql_output.write("-- Testing 25% quartile, can be slow \n")
        sql_output.write("select percentile_cont(.25) within group (order by"+a+") as low_quartile from "+c+"; \n \n" )
        sql_output.write("-- Testing 50% quartile, can be slow\n")
        sql_output.write("select percentile_cont(.50) within group (order by"+a+") as mid_quartile from "+c+"; \n \n" )
        sql_output.write("-- Testing 75% quartile, can be slow\n")
        sql_output.write("select percentile_cont(.75) within group (order by"+a+") as high_quartile from "+c+"; \n \n" )
    elif    b == 'DATES':
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing for Column: "+a+"\n")
        sql_output.write("-- Column Type: Date \n")
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing counts of column \n")
        sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
        sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
        sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
        sql_output.write("-- Checking for highs and lows, are they as you expected? \n")
        sql_output.write("select min("+a+"), max("+a+") from "+c+"; \n \n" )
        sql_output.write("-- Checking how many dates are in the future. \n")
        sql_output.write("select count(*) from "+c+" where "+a+" >sysdate; \n \n" )
        sql_output.write("-- Checking how many dates have a timestamp. \n")
        sql_output.write("select count(*) from "+c+" where substring("+a+",12,8)<> '00:00:00'; \n \n" )
    elif    b == 'STRING':
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing for Column: "+a+"\n")
        sql_output.write("-- Column Type: String \n")
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing counts of column \n")
        sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
        sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
        sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
        sql_output.write("-- Top 50 values \n")
        sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 10; \n \n" )
        sql_output.write("-- Check string lengths \n")
        sql_output.write("select min(len("+a+")) as min_length,max(len("+a+")) as max_length  from "+c+"; \n \n" )
    elif    b == 'BOOL':
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing for Column: "+a+"\n")
        sql_output.write("-- Column Type: BOOL \n")
        sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
        sql_output.write("-- Testing counts of column \n")
        sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
        sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
        sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
        sql_output.write("-- Breakdown of boolean \n")
        sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 10; \n \n" )
    elif    b == 'GEO':
        sql_output.write("Geospatial Data not currently supported. Suggest Something?  \n \n")
    else:
        sql_output.write("Column:"+a+"is not a know Datatype. Datatype passed was:"+b+"\n \n")

And voila we get this:

Outcomes

This script is a lot more 'pure' in my eyes, doesn't import an excessive amount of libraries and definitely no suppressed warnings. It can handle nuances in different code a lot better than the previous one did. Doesn't matter whether it's varchar(15) or varchar (400000) it handles both correctly.

Sure I've made a few compromises, like not validating the input, or allowing the user to just copy the DDL to the clipboard, it doesn't even open the test script automatically for them any more, but those are all very minor things that hardly impact the analyst.

Things to do in V3

Originally I wanted a GUI to load the DDL, where an input box would open up and the user would paste the DDL there. Couldn't quite spare the mental energy to learn Tkinter this time around but definitely something to explore.

Another thing I envisioned when I set out to rebuild this was that this would actually send the queries to our Redshift Enviroment, run them, and return the results. Again biting off more than I can chew on this pass, primarily because I don't have permission to install the command line postgress tool needed to send commands to Redshift at work so something I'll look into that another day.

Git Repo

ronsoak / SQL_Test_Script_Gen

A python application for generating SQL test scripts based off of a table DDL.

SQL Test Script Generator

For Redshift Table Builds

What is this?

This is a python script that reads a table build written in Redshift Syntax and then for every column outputs a text file with some basic test scripts.

Why?

Part of thorough testing in SQL is ensuring that each column you have pulled through is operating as expected, this script quickly creates the basic tests you should run, leaving you to write more personalised tests.

Pre-requisites

Latest version of Python 3.0+
Tables built using Redshift, will not (currently) work on tables built in Oracle, TSQL,Postgresql,MySQL etc.....

Instalation / Configuration

Download the git as a Zip, you only really need 'Launch_Me.bat' and 'Gen_Test_Script.py'
Place these two files in the folder of your choosing
Edit 'Launch_Me.bat' and go to line 11
Edit 'C:/path to your python install/python.exe' to be where your python.exe is installed
Edit 'c:/path to this script/Gen_Test_Script.py' to be…

View on GitHub

Reminder: All views expressed here are my own and do not represent my employer, Xero.

Who am I?

// Detect dark theme var iframe = document.getElementById('tweet-1183218570164965376-807'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1183218570164965376&theme=dark" }

You should read....

Article No Longer Available

Whelp, they got all our data, now what? - A guide, well a lecture first, then a guide.

Alex Antra — Mon, 09 Mar 2020 11:27:03 +0000

Reminder: All views expressed here are my own and do not represent my employer, Xero.

Remember how we laughed at previous generations for thinking smoking was healthy, that sun burn was harmless and knowing more than one language made you dumb?

Well chuckle a bit quieter as it won't be long before people are laughing at us. "You did WHAT with your data!!!!!!!!?"
"You DIDN'T read the terms of service???"
"You LET them track you???"

For we are, and I won't mince words here, the data stupid generation.

We have given away so much of our data to companies who made billions off of it with absolutely nothing in return, we have sacrificed privacy, safety and in some circumstances our own genetic blueprints all because we were so oblivious as to what was happening.

And while I will concede that we didn't know what was happening until it was too late, much like Edna with her 1963 Lincoln Continental and 6 pack a day habit, it's something we should be trying to rectify as soon as possible.

So how bad is it?

To quote HIBP:

Our data is leaked, sold, redistributed and abused to our detriment and beyond our control

Let's first talk about the data we knowingly gave away.

So think about every social media account, every newsletter, every loyalty program, and every other account you've set up in the past ten years.

Every tweet, Facebook post, email, picture, video, GIF you put online you willingly made it the property of the company hosting it to do whatever they wanted to do with it. You agreed to it in the really really really long Terms of Service you skipped to the bottom and agreed to.

Now we can discuss how intentionally problematic these sort of legal documents are another day but as a surprise to no one, a clear majority of people do not read them, and of those who do, less actually understand them.

But the devil is in the details.

It's in Google's Terms and Conditions that they read all of your emails not just to use to cater adverts back to you, but to aide internal development (ML anyone) and other stuff (profit from information sharing).

It was Apples Privacy Policy that tells you that a human may listen to what you say to Siri to "better improve her recognition of pronunciation."

Heck, if you read all the way to the bottom of the iTunes terms and conditions, you'll find that you've agreed to NOT use iTunes in the creation of Nuclear Weapons. Something I think most of us will be fine with.

And to be fair we all knew this, to some extent, that the stuff we put up on Facebook was used by them for their own purposes. What I think many people didn't realize was how long we had been doing it for, we were all so accustomed to websites coming and going it didn't need close scrutiny but next thing you knew we had willingly fed twitter five years worth of our likes, dislikes, political opinions, passing fancy's, and humor.

We also didn't realize that while you may only put certain information online, the decade you spent on Facebook allowed them piece together the missing info like a Jigsaw.

What pains me now is that we are filling these websites with information about our kids who don't get a chance to opt out, by the time they understand the problem you may have thrown away any chance of them ever having privacy.

The things we willingly do, have massive ripple effects. Signing up to a loyalty scheme at a shoe store to receive $10 off your next order makes that shoe company a hell of a lot more than $10, people don't give money away for free.

Your photos are scanned, your videos deconstructed, your emails read, and if you really need a wake up call, these companies are looking at your nudes Barry. Which actually happened at Snapchat!!!!!!!.

Let's talk about the stuff we unknowingly gave away

I talked earlier about Facebook filling in the puzzle of the stuff you didn't post online.

They all do a lot of that, all the time. You see, we leave a lot of footprints all over the world wide web.

So you log in at home right, you do that every day for a month, through your ISP they know what country, city, and region you are in. Good things ISP's don't tell them your exact location right? Well your ISP won't but your GPS enabled cellular phone has just told them your exact longitude and latitude, your phone has also told them the SSID of your wifi so the next person who logs in on that wifi network (flatmate), Facebook now knows what house they are in. One person can betray the information of everyone in the house by being the final piece of the puzzle.

Same with at work, Facebook notices that there's this second location you regularly log into five days a week and your Benedict Arnold of a phone has also told them the exact coordinates of your workplace as well.

And it's not just your home location, they are tracking, or more accurately your phone is giving them a map of where you go. What super market you go to, what shops you visit, what shops you don't visit, your bar, your gym.

They then use this, not just in advertising, but to enhance their profile of you, and to learn more about how the people in your city operate on mass.

They know when certain streets are busiest, what a stores opening hours are and when it's busiest, and what sort of people are in a city based purely on mass data collection. And while some of that stuff is useful, they never asked, they tricked us, and now we are suffering the cost of that(more on exactly how we are suffering later).

You don't even have to be logged in, Google can sometimes tell by your search habits that it's you, just give them enough time. In fact going in-incognito doesn't hide you from Google, at first it will just look like a new person in your house is looking for Busty Mature Women on the ole Porn Hub, but give them enough time (especially seeming the activity is happening on the same device every night at 10:55pm....BARRY) and google knows what ticks your box, even though you used 'Private Browsing.'

Let's talk about the stuff we didn't know they did

Facial Recognition Training

Before I purged my Facebook I downloaded a copy of everything on there. I had uploaded over 2,000 photos to the blue box in the sky.

While a good chunk would have been memes, enough of them contained pictures of people, lets say 300 have humans in them. Then consider that Facebook has over 2.5 billion active users and we all now know how Facebook trained it's Facial Recognition algorithm.

And while advancing the field of data science is a cool thing, Facebook has proven themselves to willingly sell their services to whomever can pay, regardless of the ethical implications.

Enter stage left, the Chinese Government who use facial recognition to track every citizens movements and actions as part of some personality score, and we know exactly who is most likely to purchase that data. It's all fun and games until they use your data against for unethical reasons. We fucked up.

Cookie Stealing

Did you know that Facebook is tracking the other stuff you do on other websites?
Without Facebook even being open they can track you, though all the embedded 'like' or 'share to Facebook' buttons found on other websites. If you can see a link to Facebook on a website, old Zuckerberg is watching you.

Stealing your data via the companies they own

These big companies also steal your data by the simply fact that they own other companies.

Those loyalty schemes are often owned by bigger companies, which is why 5 minutes after you sign up for your $5 voucher, your inbox is being flooded.

Back onto hating on the big wigs.

Did you know Facebook owns Instagram, WhatsApp, Occulus Rift and 82 other companies?

Google on the other hand is actually owned by a company called Alphabet, who owns everything Google, Nest, Sidewalk, is about to purchase Fitbit (yes all your health data will go to them), and nearly 300 other companies. At one stage Google bought more than one company a week.

Your data is bought and sold over and over again

Unbeknownst to most of us, the selling and buying of our data has been what has made all of the companies filthy rich.

Their are Data Enrichment companies who will sit as the middle man and buy your data off of Google, smash it together with what they have from Twitter and sell it to Facebook. One of those companies last year left one of their servers unprotected and the personal information of 1.2 billion people were leaked, which means that company was buying and selling data on over a billion people, you know how unlikely it is that your data wasn't in that leak?

Live Manipulation of pricing / products

We are all aware of surge pricing right? Ubers are more expensive based on demand.

But what if the Uber was more expensive because they knew you could afford it?

That's exactly what online retailers like Amazon do. Because they have all of our demographics, interests, habits and buying history Amazon can show someone who they think can afford a higher price, a higher price, while in return only offer a sales price to someone they need to work hard to covert.

Same with Banks, the information they have harvested on you, as well as your financial history will be used to make judgements on what to lend you. A practice some insurance companies have started to deploy.

Just imagine if your health insurance went up because they saw that you had eaten at McDonald's???? Is that a world you want to live in?

Using your data to influence your actions

And of course the one that has been all over the news. If the Facebook can use what they know about you to dynamically change what sort of content you can and can't see, how do you know that they aren't hiding stuff from you in order to influence your decision making?

We already suspect (know) that Russia influenced the previous American Election via Social media. What if your local government wants to roll out legislation to curb the amount of data Facebook can steal off of you? Can we trust them to not tweak the algorithm in their favor? Showing you more content that puts up arguments against the legislation? All signs point to no.

Let's talk about the breaches

If it's not bad enough that all these websites are harvesting your data, they keep fucking losing it.

According to Have I Been Pwned, the Internets leading website on compromised user data, over 400 websites have lost user data due to breaches to a whopping NINE POINT FIVE BILLION USER ACCOUNTS.

I will repeat again what I said earlier, the odds of your personal data not having been breached are infinitesimal right now. Your data is out there in an uncontrolled environment, what data exactly is still unknown, it could just be your NeoPets account (yes they got hacked too), but hackers don't need a lot of information to get more from you. Hackers steal and buy your data to enable them to do something called Social Engineering.

Given enough data they can either engineer what your password might be, or use an old pass word to steal some accounts off you, thus learning more about you, or even ring up technical support call centers and pretend to be you to get access to your account.

Why do they do it?

Primarily it was for Marketing and Feature Usage tracking. Over time it evolved into what it is now.

Marketing

With over a billion users on the internet it can be hard for some one wanting to advertise a product to reach the right audience. A maker of boutique headphones aimed at audiophiles could show an add to a million people and not get a single sale. To the untrained its hard to find out who should see your advert and who shouldn't. This is where Facebook / Twitter / Google excel.

Because they have information on you, they break you down into what is known as segments. Segments become groupings that they offer to these people wanting to advertise.

As a 30 year old white male, living in an English speaking western developed world who has an interest in technology, my segments probably look something like this:

Male
Disposable Income
Likely to buy tech
English speaking

In fact my Facebook download had me as 'Starting Adult Life' which goes to show what years I actively used Facebook.

The platform holder then charges the Headphone maker to show their adverts only to the segments that apply to the Headphone makers most likely customer base.

No one claims that this is a 100% hit rate, which makes this the perfect scam. Facebook might only increase the success rate of the headphone makers adverts by 5%, especially when the advert might just be a bad advert or the product poorly priced, but to the Headphone maker, it was better than blind chance and so they pay for it.

Feature Tracking

A core part of development in tech is knowing what to develop next. The collection of this data has been, for the longest time, the backbone of development. If a development team want to know what bug they should fix first, they would turn to feature usage data. If they want to know what kind of person is using X product to figure out how to entice Y person to use it, feature usage data, they want to know if a new feature is being used, again feature usage data. Its how these companies decide on what to develop. Now this isn't a get out of jail free card. They really only need anonymous data, and a lot more could be done in this space to make sure devs are only seeing the data needed to do their job.

What can we do?

We need to be both preemptive to save our future data as well as clean up the past X amount of years of accounts we have created, it's no good setting up good passwords from here on out, if your data is going to get leaked by an account you didn't use any more. This is both an exercise in security as it is best data protection practice.

Check how badly you've been hacked.

Go to have i been pwned? and enter in every email you can remember ever using (even work emails). This will give you an indication of how badly compromised you are.

Close old accounts

As a piggy back off of the above, close as many old accounts that you can remember and don't use any more.

Pretend your covered by GDPR

The GDPR is a regulation passed by the EU to give Europeans the right to be forgotten. For example before GDPR if you closed your account with Facebook, Facebook didn't delete your data, they kept it and kept using it. Now with the GDPR in place, if requested by a member of the EU Facebook legally has to scramble the users data so they can't be identified any more or risk a 4% fine to their Revenue.

Fun fact, most tech companies haven't even implemented GDPR properly as their products were never built to do this sort of thing, not to mention GDPR covers EU citizens wherever they live. Because no one has any way of proving whether someone who lives in Australia is or isn't an EU citizen, they won't challenge you. So once you've closed your account, ask them to remove your data under the GDPR.

Set up a spam email address

For any account you want to keep open, or anything in the future you want to sign up to, have a personal email address for you and a spam email account for everything else (I have whats left of my Facebook tied to a spam email). Give it a generic name like 732643_8324732_824623@gmail.com and give it a good password (nothing similar to anything else you use please).

Mask your email

Some email services will now offer you a masked email. Apple now does this, so you can be debbie_taylor@icloud.com, but apple can offer you 876545678765_dfdsfu@icloud.com to use with everyone else on the internet. You still get the emails but it means people can't extract personal information from your email and prevents hackers from matching your email addresses across the internet.

Opt out

Get into the habit of scrolling to the bottom of every spam email and hit 'unsubscribe', get yourself removed from those email lists, not only are they sending you advertising, they also include trackers in the rich content of advert to see how you interact with the advert.

Reset your advertising ID

Did you know that every software platform you use, tracks you with an advertising ID, which helps the marketing people find you faster? I bet most people don't know that.

It can be found on your iPhone, your Android, your Mac and your Windows, and you can reset it as often as you want, and you can even change some settings to make it harder for them to track you. I have BOTH change my settings to its harder to advertisers to track me and I have a reminder in my phone to reset my advertising id every month.

Read what apps can access

Many apps on the iOS and Android app store access more than you would think, and while both Apple and Android are getting better at showing you what those apps access and giving you the ability to dictate what they can and can't do, it's still worth checking from time to time. The key things I look for are, what apps want to know my location, what apps have access to my contacts, and which apps can use my camera.

A good starter guide for is here.

Prevent Apps from tracking you and chose what data they can send back to the mother ship

In the same vein as the above, start by making sure only apps that need to use your location are using it and turn it off for everything else. For example my voice memo app has location turned on.

You can also allow or deny 'usage analytics' from being sent back to the device owner. This is standard across many devices and software and it is pitched as being 'for your benefit' as they can detect bugs and improve the software to your liking however, in this day in age my preference is to not believe any 'it's for your benefit rhetoric' when I know for a fact that data will either be sold or leaked.

A handy guide for ios is here.

This advice also applies to the software you use, a lot of apps, web browsers, computer programs, and other internet connected devices in your home will also most likely have a 'opt out of sharing usage analytics' option.

Use privacy focused search engines.

So everything you search in google can be used against you, not just by Google, but also by the law.

Enter Duck Duck Go. The privacy focused search engine, it doesn't track you and encrypts your search activity so even they can't see what you are doing. They have a door mat outside their office that says COME BACK WITH A WARRANT

I've been using Duck Duck Go for over a year now and it's been a great decision as my ads get less and less targeted, it also helps me with my attempt to divorce google.

Though a word of warning, your searching will get a tad harder, because google is always tracking you, it allows them to make searching the internet easy, while they can't predict what you will search they can make finding the right result easier as they know what to exclude, especially with vague search terms.

When I started using Duck Duck Go I did notice that stuff I used to google using vague terms didn't get the results I wanted, but over time, both I and Duck Duck Go have improved. For example I live in Wellington New Zealand, when I google a restaurant google will know to show me the one in my city, because Duck Duck Go doesn't know where I am, I will often get restaurants of the same name in other countries.

Trust me it's worth it.

Use privacy focused Web Browsers / stop using Chrome.

Stop using Google Chrome is the number one answer here. They have monopolized their way into the number one position and with that monopoly they are scraping monumental amounts of data. What makes it worse is that Chrome is also the base for many other web browsers like Microsoft Edge and Opera Browser, and even is the underlying code base for non web browsers like Slack and VSCode. What this means is the Chrome based tracking stuff is tracking you even if you aren't using Chrome, its up to the developers to turn that off.

Well there are two options here.

Brave: Brave browser is a chrome based browser, however it actively blocks everything and has been built with that purpose in mind. Its turned off all of Googles back doors and even provides you with a granular, per website, tool called Shield that allows you to block what you want. What shocks me is that YouTube stops working when you block all their trackers. Bit naughty.

Firefox: Firefox is the only non-chrome base browser left (other than Safari). Not only is it stopping Chrome from being the true monopoly it also has a privacy focus similar to Brave. It actively stops trackers and gives you control over what you allow certain websites to do. Firefox where the first people to be able to provide a tool that stopped Facebook from looking at the other tabs you've got open.

Use end-end encryption services where possible

When a company tells you your data is encrypted they often mean, it's encrypted from everyone else. This is alright at protecting your data from being intercepted, but should a hacker get access to the back end, like the NINE POINT FIVE BILLION TIMES ITS HAPPENED BEFORE the encryption means nothing. It also means a legal agency like the government can get access if they have a warrant. Cough America Cough.

The phrase you want to be on the look out for is 'end to end encryption' messaging provider Telegram offers this on their secret chats function meaning, that if you used it, not even Telegram, or the government with a warrant can have a look.

However do your research, WhatsApp is encrypted end-to-end, however its parent company Facebook does still have other ways of seeing that data, primarily by seeing it before it gets encrypted. So be warned.

Change your password / use password managers / don't be dumb

Come one, you know the drill.

Don't use the same password for everything
Don't cycle passwords
Don't make them easy, or write them down.
Turn on two factor authentication
Use a password manager like LastPass
Change them often
Keep an eye on Have I been Pwned, every time that your get pwned change all your passwords.

Don't use genetic testing companies

In one of the most chilling revelations of a breach, MyHeritage revealed that they had been hacked. It's not just passwords that are out there in the wild for the customers but their genetic information. A future where we could use a genetic imprint in lieu of a password has potentially already been ruined by this sort of thing. I would urge you to resist using any of these services until security gets a bit better.

Read the ts & cs

Easier said than done right. You can use this website, Terms of Service: Didn't Read, to get terms and conditions boiled down for easier consumption.

Download your data.

In recent years, most websites give you the ability to download all of your data that they hold on you. I recommend you do this for any major platform you use. When I downloaded all my Facebook data I could even see what guesses they had made about who I was for marketing purposes.

Actively delete old data

In the past year I deleted everything off of Facebook (after downloading it) and delete all my old tweets. This doesn't remove them from the platform holders data but it prevents other sites, like Data Enrichment companies, from scraping them and selling them on.

For twitter there are some third parties who will bulk delete tweets for you.
For Facebook I used a Chrome Extension that deleted posts for you by simulating the mouse clicks over and over again. That took a few days.

Reminder: All views expressed here are my own and do not represent my employer, Xero.

Who am I?

// Detect dark theme var iframe = document.getElementById('tweet-1183218570164965376-162'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1183218570164965376&theme=dark" }

You should read....

Article No Longer Available

Data Analyst? TIME TO LEARN PYTHON!!!🐍🐼

Alex Antra — Wed, 29 Jan 2020 11:06:07 +0000

Do you work with data?
If so...
You need to learn the language of the snake...

No, not that snake language!...
Python.

After all...
It is year of the Snake....

oh...
I'm being informed that it is in fact NOT year of the snake...

Oh well the matter still resides... you should learn python.

That means....Yes this is yet another learn python article.

But if it wasn't good advice people wouldn't keep saying it...

The truth is that its fast becoming a staple skill here in the United Counties of Actionable Insights.

See it's not just for engineers and scientists, and this isn't another 'SQL is dead, learn this instead' it's more that data analysis is becoming more than just where clauses and group bys.

No longer are the oppressed forced to load data into tables and have to set the right timezone, no longer do the downtrodden need to rely on excel or tableau to visualize data NO LONGER DO THE WEAK NEED TO REMEMBER TO ADD A SEMICOLON AFTER EVERY STATEMENT AND A COMMA BETWEEN COLUMNS.....

Okay... here's why:

BI is evolving

BI is rapidly moving away from just using data that fits neatly into relational databases.

Unstructured data is getting more common, and while some teams do a good job at smushing it into a format that is consumable for a SQL database, that's not always possible or necessary. Other solutions are required if we are to provide results back to the business quickly with minimal overhead.

BI is also moving away from just a suite of reports to a suite of products, products that are part of a CI/CD pipeline and developed using languages other than SQL and while some of them will be built in Java or C you can build a lot of things with Python.

And while it may seem like Data Science is sprinting in the opposite direction from BI, its really not going to be long until Data Science is seen as a part of BI. Expect every self respecting Data Platform to have at least one AI/ML/NN model that sits alongside the other models in the platform. At the moment AI / ML is primarily being written in Python, there's no guarantee you'll be able to use a Data Science model using SQL.

And finally, BI has mostly been the process of looking at present and historic data and use those findings to guess at what you can do in the future. Data analysis is trending more and more towards making statistically sound predictive analysis and SQL currently isn't that good at that sort of thing, it reads tables not algorithms. There's no guarantee SQL will be able to, or will be the best at being able to leverage future predictive analysis.

Big Data is getting Bigger

Question. What do people do now when they need to analyse data outside of a data warehouse? They use spreadsheets.

However, the day of the spreadsheet is nearly over. Excel caps out between 500k and 1mil, in the world of big data, a million records could be the thirty minutes of events. Excel is not the adhoc analysis tool of the future, Python is. Crunching a couple of million rows of data in Python using Pandas is stupid easy, you can load in as much data as your RAM can take without any overheads, and if your're crunching too much data Python allows you to batch process data or randomly sample it, all with a few lines of code.

It can also help behind the scenes

BI us as much about the back end as it is the front end. You can use Python as part of your ETL process, you can automate tasks, monitor platforms or even build better capabilities.

For example Airflow is a data pipeline tool that is configured in Python, you can move data between systems using Airflow.

In my team we've used Python to read our SQL code and produce test scripts (article incoming.)

One of our scientists needed a data dump off of one of our internal systems and our platform team didn't have resource spare to get that data through our traditional channels, so they used Python to ping the API and directly import it in (don't worry it was above board).

It really is the tool of the future

Python has been described as 'the second best coding language for everything' and it really does so many things effortlessly. Setting up a local web server to host a web app is literally two lines of code using Flask, we really are in the future.

It's because of the above reasons that Python should be the next thing you should learn in your data career. Its going to offer you a more flexible and feature rich way to analyse data or improve the way you work over any other tool in your existing arsenal.

So how / what do I learn

Well Python can feel overwhelming to learn because it can do anything, however we'll just focus on analyzing data with Python.

You do this using Pandas and Jupyter.

Pandas

Pandas is a library you import into Python and its brings with it the functionality to hold data in virtual tables and analyse it.

Pandas Home Page

Notable things you can do in Pandas:

Import data out of a CSV / API / Parquet / or the clipboard (love that one)
Select, transform, join, group, aggregate just like SQL
EXPLAIN - tell pandas to look at a data set and explain it to you and it will run away and tell you all sorts of random information about your data set, mins, max's, upper quartiles etc the works!
Pivot data (management love pivots)
Graph your data (using Matplotlib)

Jupyter Notebook

Jupyter is the software you should use. It takes the form of a living document and allows you to present text and code in a chronological format.

Why is this important? Unlike SQL, Python won't show it's results unless you ask it to and traditional code environments will output Python code in a terminal. Jupyter is the best tool for learning on as you can write code and execute it in blocks and then as you learn you can grow your code in blocks while still being able to see earlier blocks.

Tutorials

So of course there are a million youtube videos and interactive code camps out there for you to pick up.

The video that best helped me was this guy, Keith Galli, maybe that was because he seems genuinely interested in showing you Pandas and not growing his brand....

Who am I?

Ronsoak 🏳️‍🌈🇳🇿

@ronsoak

Who Am I❓
|🇳🇿 |🇬🇧 |🏳️‍🌈
📊 Senior Data Analyst for @Xero
⚠️ My words do not represent my company.
✍️ Writer on @ThePracticalDev (dev.to/ronsoak)
✍️ Writer on @Medium (medium.com/@ronsoak)
🎨 I draw (instagram.com/ronsoak_art/)
🛰️ I ❤️ space!

03:11 AM - 13 Oct 2019

You should read....

Article No Longer Available

The Good, The Bad, The Ugly: Microsoft's three significant impacts on the world of Data.

Alex Antra — Mon, 25 Nov 2019 11:19:42 +0000

As someone who works in data, my relationship with Microsoft is mixed.

It's like having a grumpy mean father, but has never missed a ballet recital and was there for you that one time you did the thing with the golf ball that your mother can never hear about.

The tools Microsoft have provided to the field have been plentiful, some have even gone on to become staples of the data community, however, some of these are for better and some have been for worst.

The Good👍: Microsoft SQL Server + Supporting Cast.

SQL Server, SSMS, T-SQL, Analysis Services, Agent, are all industry standard tools for the data world.

It's been in the top three in terms of DB-ranking, since 2013 and the truth is, if you have a production transactional database built anytime in the past 10 years, It's probably T-SQL.

And while it looks to be slowly loosing ground to other products and new ways of crunching data, it's definitely not going anywhere, new versions and more modern variants are available. Those in the Azure cloud will find several reasons to use the latest SQL Server.

The Bad👎: Microsoft Excel

For many of us, Microsoft Excel is our first exposure to analysis. It taught us some fundamental stuff like tables, vlookups, pivot tables, graphs, and maybe a touch of VBA.

However, Excel is the awesome party guest who has overstayed their welcome. Cool at the beginning, but now they really need to leave.

This isn't me saying Excel has no place in 2019, not at all.

My problem with Excel is that for many people and companies they never move on to other tools. They very much, double down on making Excel perform tasks it was never built to do. Choosing to learn and build suites of VBA plugins and hacky work-around's to do things other analytical tools can natively do in seconds.

It's a case of the training wheels that never came off.

Too many analysts are being robbed of the opportunity to take those training wheels off, and the blame lies in two distinct camps.

Camp number one, is the customer. The customers who are themselves only familiar with Excel, it's come pre-installed on just about every computer they have ever touched. They also don't trust insights that they can't personally inspect the raw data of. In this age of big data, we are still hearing 'Can I see it in Excel?' at an alarming frequency.

Camp number two, is that Excel is a desert island. Analysts are stranded on this island, unsure which Island they should travel to next. The islands of Python, R, SQL are too far off in the distance, but closer are all the 'Excel Plugin' islands promising fresh water and tasty mango's. It's very easy to see how some very good analysts never make it to Python island and instead spend the rest of their lives searching stack overflow for answers to VBA questions.

The Ugly🤮: Microsoft Access

Microsoft Access is the child who tried to help but instead ruined everything, it's not the child's fault, their heart was in the right place, however the results are disastrous.

And that is what makes Microsoft Access the Ugly. It always ends in disaster.

Billed as "Babies first Database" it's easy to pick up and administer.

However nine times out of ten what ends up happening is that the Access Database quickly becomes a business critical piece of software being run off a random employees local machine.

This quickly becomes a major security and data risk, often requiring the Data Warehouse team to quickly consume the access database to negate the risks it posed and to keep the fancy executives who had begun to rely on it at bay.

It's often like fighting a hydra as for every one access database that is dealt with, two more pop up randomly amongst the business. It is for that exact reason that many companies running the Microsoft Suite do not allow Access to be installed at all.

Who am I?

// Detect dark theme var iframe = document.getElementById('tweet-1183218570164965376-883'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1183218570164965376&theme=dark" }

You should read....

Article No Longer Available

The R.A.G (Redshift Analyst Guide): Troubleshooting process

Alex Antra — Wed, 20 Nov 2019 11:17:45 +0000

Welcome to the R.A.G, a guide about Amazon's Redshift Database written for the Analyst's out there in the world who use it.

Previously on the R.A.G....

Article No Longer Available

Are results slow?

Is WLM killing your query?

Do tables seem to not play ball?

There is a LOT to consider when trying to solve the above.

Here's the process for you to work through:

✅ Check the Explain Plan.

Do this for a single query at a time, not your whole script. But start by getting Redshift to tell you how it's going to execute your query. Make sure to look for actions with high costs, sequential scans or nested loops. If you can avoid them, or break your query into smaller tasks this will help you a lot.

Article No Longer Available

✅ Understand the Distribution and Sorting of the tables you are dealing with

Whether the tables you are dealing with are built by you or someone else, their configuration could be working against you.

Run the below query and make note of:

distyle_and_key: How is the table distributed across nodes?
row_skew_ratio: This is the effectiveness of the dist key, the closer to 1 the better.
first_sortkey: How is it sorted on the node?
no_sort_keys: How many sort keys?
sortkey_skew_ratio: This is the effectiveness of the sort key, the closer to 1 the better.
percent_unsorted: How long has this table been since vacuum
stats_needed: If yes, the table needs 'analyst statistics' before the leader node knows how to handle it properly.

select  i.schema        as schema_location, 
        i.table         as table_name,
        i.encoded       as are_columns_encoded, 
        i.diststyle     as distyle_and_key,
        i.sortkey1      as first_sortkey,
        i.sortkey1_enc  as sortkey_compression,
        i.sortkey_num   as no_sort_keys,
        i.skew_sortkey1 as sortkey_skew_ratio,
        i.size          as size_in_blocks_mb,
        i.tbl_rows      as total_rows,
        i.skew_rows     as row_skew_ratio,
        i.pct_used      as percent_space_used,
        i.unsorted      as percent_unsorted,
        i.stats_off     as stats_needed
from    svv_table_info i
where   i.table = 'table_name'
limit   50

Article No Longer Available

✅ Run your query - read the error logs

If you can run your query and its not being killed by the WLM or crashing, then check the Redshift error logs on how to make it run faster.

The table contains an EVENT and a SOLUTION table, this may provide some key information on how to make your query run faster.

select      l.event,
            l.solution,
            q.querytxt

from        stl_alert_event_log l

join        stl_query q
on          q.query = l.query

where       l.userid in (select usesysid from pg_user where usename ilike '%name%') --change to your name

order by    l.event_time desc

limit       10

✅ Are you fighting for resources?

In some scenarios your query may be slow because of lack of resources, or you had to wait until a slot opened up. Run the below query to see how your queries are being handled.

select      c.name as process_queue,
            w.slot_count,
            datediff(seconds,w.queue_start_time,w.queue_end_time) as q_wait_time_seconds,
            w.exec_start_time,
            w.exec_end_time,
            datediff(seconds,w.exec_start_time,w.exec_end_time) as exec_time_seconds,
            w.final_state,
            w.est_peak_mem,
            q.querytxt
from        stl_wlm_query w
join        stv_wlm_service_class_config c
on          c.service_class = w.service_class
join        stl_query q
on          q.query = w.query
where       w.userid in (select usesysid from pg_user where usename ilike '%user%') --change to your name
order  by   w.xid desc;

✅ Understand best practice

Best practice

Article No Longer Available

header image drawn by me

Who am I?

Ronsoak 🏳️‍🌈🇳🇿

@ronsoak

Who Am I❓
|🇳🇿 |🇬🇧 |🏳️‍🌈
📊 Senior Data Analyst for @Xero
⚠️ My words do not represent my company.
✍️ Writer on @ThePracticalDev (dev.to/ronsoak)
✍️ Writer on @Medium (medium.com/@ronsoak)
🎨 I draw (instagram.com/ronsoak_art/)
🛰️ I ❤️ space!

03:11 AM - 13 Oct 2019

You should read....

Article No Longer Available

The R.A.G (Redshift Analyst Guide): Things to avoid / Best Practice

Alex Antra — Wed, 20 Nov 2019 11:16:38 +0000

Welcome to the R.A.G, a guide about Amazon's Redshift Database written for the Analyst's out there in the world who use it.

Previously on the R.A.G....

Article No Longer Available

Below is a collection of do's, don'ts, and 'be wary of's that we have come across in our travels.

None of these are hard and fast, sometimes you have no choice but to do all of the Don'ts.

Things to Avoid

A series of things NOT to do, mainly sourced from official documentation.

❌ Don't Compress your first sort key

Compressing your first compound sort key runs the risk of actually making your query run slower in some scenarios.

From AWS: "If sort key columns are compressed much more highly than other columns in the same query, range-restricted scans might perform poorly."

As a general rule of thumb compress your first sort key as raw. Feel free to compress the other sort keys.

Create table test
    (
    customer_id     char(36) encode  ZSTD,
    createddate     timestamp encode RAW,
    customer_type   char(50) encode  ZSTD,
    amount_due      decimal(10,2) encode AZ64
    )
diststyle   key
distkey     (customer_id)
sortkey     (createddate, customer_type);

Source:AWS Doco
Source:Sort Key Investigation

❌ Don't select * unless it's a small query

Redshift has a dedicated resource stream for handling small queries, so this rule doesn't apply to you if you are just wanting to do a quick select * from table where limit 50 as your query will be given its own resources.

HOWEVER, for everything else you should never be doing select * from unless you absolutely NEED every column. Redshift works faster the fewer columns are pulled in.
Source:AWS Best Practice

❌ Don't assign a column with NULL values as a Dist key

If the column you set as your dist key has a lot of NULL values, then all the NULLS will end up on one slice. This could potentially cause a bad SKEW
Source:AWS Doco

❌ Don't join the same table multiple times in the same query

Referencing the same table in a query can come at a high performance cost, explore other options like breaking down the query into smaller datasets or use a CASE expression.
Source:AWS Best Practice

❌ Don't use sub-queries for large complex operations

Avoid using sub-queries on data sets that have multiple conditions and are large in size. Sub-queries perform best over JOINS where its a simple IN clause.

The example below shows a good use of sub-query over a join. The reason why the join condition is bad is that one data set is huge the other is small and by JOINING the data you will force data to be redistributed across nodes.
Source:AWS Best Practice

❌ Don't use highly unique columns as a Dist Key

For Example using a timestamp for a dist key would be bad.
If we used a timestamp as our dist key, that would potentially lead to 86,000 unique dist keys PER day. This would vastly reduce the benefits of having a dist key.
Source:AWS Doco

❌ Don't use UNION, instead use UNION ALL

When you use UNION, Redshift tries to remove any duplicate rows, so depending on the size of your data the performance overhead could be huge. Use UNION ALL instead and if you need to remove duplicate rows look at other methods to do so like a row_number and delete statement.

UNION is believed to perform ~150% worse than UNION ALL.
Source:Investigation on Github

❌ Don't use an Interleaved Sort key unless you 100% know what your doing

Interleaved Sort keys are complicated, only use them if you know what you're doing, by default use compound sort keys.

Not implementing an Interleaved sort key can result in very poor result return time and long write / update / vacuum wait times.
Source:AWS Doco

❌ Don't use Dist Style All on very large or small tables.

About 500k-1m is the sweet spot for Dist Style ALL, remember it gets copied to every node.

❌ Don't use LZO, when you can use ZSTD or AZ64

LZO's best of all worlds compression has been replaced by ZSTD and AZ64 who do a better job. AZ64 should be used on your numbers, ZSTD on the rest.

Things to Be Wary Of

A series of things to be aware of, most of them have come from my own experience.

🤔 Redshift Inserts columns in the order you specify - even if they don't line up

If you build a table with the columns in this order:

Customer_id
Createddate
Customer_type

But your insert statement is in this order:

Customer_id
Customer_type
Createddate Redshift will attempt to insert it in that order. So it will try and put customer_type data into the createddate column. If the data types don't match, Redshift will throw an error, but if they do match, you won't notice.

🤔 Comparison Operators > LIKE > Similar to > REGEX/POSIX

Applying Logic to your dataset comes at a cost in terms of performance.

Comparison Operators such as < > = perform better than LIKE, which in turn perform better than Similar To.

And anything and everything performs better than REGEX / POSIX

🤔 Conditional Logic on Joins performs the worst

When we join tables on conditional logic, i.e join table on blah > blah or join table on blah between bleh and bloh.

Redshift has no choice but to do a nested loop which means every SINGLE row in table a has to be checked against every row in table b, which can have massive amounts of overhead.

🤔 All Functions will come at a cost

Using functions can slow down performance. For example where invoicedate >= date_trunc('month',sysdate) has a higher performance cost than invoicedate >= "2019-10-01".

This will forever be a balancing act of course. Over time you will learn what Functions cost more

🤔 Some data types have a base size you can't reduce.

For example varchar(4) takes up 8 bytes of space even though you have specified 4.

🤔 Query's against system tables can be slow

Querying the system tables for logs or usage history can be slow as all of that stuff is taken care of by the leader node only. So they can't distribute that query out to the other nodes sadly.

🤔 CASE When statements take the first match

Case when statements will take the first 'when' statement that is true, even if there are multiple that are true.

create table if not exists case_when_experiment
(
  fruit   varchar(100)
)
;

insert into case_when_experiment
values ('apple')
;

select fruit,
       case 
       when fruit ilike 'a%'  then 'apple first match'
       when fruit ilike '%e'  then 'apple second match'
       when fruit ilike '_p%' then 'apple third match'
       else 'no match'
       end
from case_when_experiment
;

The result of the select statement is:
Fruit: Apple
Case: apple first match
...even though all three of the 'when' conditions are true.

🤔 Avoid Delete if you can drop/truncate. Take note of what UPDATE does.

Delete doesn't delete the row. It hides it and marks it to be deleted when you next vacuum.

Likewise, UPDATE, just hides the row and replaces it with a new row, and the next Vacuum will remove the hidden row.

If you can, drop table or truncate. A table that is constantly deleted from without a vacuum will increase in size.

🤔 MD5 works faster than Func_sha1

MD5 hashing has a faster read and write due to being 120-bit however its not as secure as func_SHA1. Use MD5 when you want to obscure something without a security implication, using SHA1 for security

🤔 Be aware of the SQL execution order.

SQL is executed in the following order:

FROM and JOIN are loaded into memory
Filters in the WHERE & JOIN's are applied
GROUP BY aggregates the data
Having applies logic to the aggregates
Select brings in the columns needed
Order by sorts the final product
Limit then limits the final data set

So Limit 10 will not just crunch 10 rows, it will crunch them all and show you 10.

Things to Do

The opposite of the things to avoid list :)

✔️ Use CHAR over VARCHAR if you have an exact length field.

For example if we have a column for ORGID's which are ALWAYS 36char long. Redshift will perform better if you set it to CHAR(36) over VARCHAR(36)
Source:Best Practices by DWGeeks

✔️ Use numeric / boolean logic where possible

The cost to apply logic to numbers and and boolean is so much lower than if you are using strings.

This is why OLTP systems will use number to represent things and why we use keys in Datamarts (dimmarketkey) as where dimmarketkey = 4 performs better than dimmarketkey = "Australia"

✔️ Repeating the same filters on multiple tables still helps.

If two tables you are joining have the same filters, specify them both, even if you feel like its redundant. See the below example from amazon

-- BAD EXAMPLE
select listing.sellerid, sum(sales.qtysold)
from sales, listing
where sales.salesid = listing.listid
and listing.listtime > '2008-12-01'
group by 1 order by 1;

-- GOOD EXAMPLE
select listing.sellerid, sum(sales.qtysold)
from sales, listing
where sales.salesid = listing.listid
and listing.listtime > '2008-12-01'
and sales.saletime > '2008-12-01'
group by 1 order by 1;

Source:AWS Best Practice

✔️ Match Sort Keys to Group By clauses - but don't skip them.

If you build a table, to later group it by cust_type, cust_city , and cust_plan, consider sorting the base table by Region, Industry, and Product Plan.

However if you instead grouped by cust_type and cust_plan, the first and third sort key, you will lose all benefit of the sortkey.
Source:AWS Best Practice

✔️ Match Order by and Group by clauses if possible.

Do:

group by a, b, c

order by a, b, c

Don't:

group by b, c, a

order by a, b, c

Source:AWS Best Practice

header image drawn by me

Who am I?

Ronsoak 🏳️‍🌈🇳🇿

@ronsoak

Who Am I❓
|🇳🇿 |🇬🇧 |🏳️‍🌈
📊 Senior Data Analyst for @Xero
⚠️ My words do not represent my company.
✍️ Writer on @ThePracticalDev (dev.to/ronsoak)
✍️ Writer on @Medium (medium.com/@ronsoak)
🎨 I draw (instagram.com/ronsoak_art/)
🛰️ I ❤️ space!

03:11 AM - 13 Oct 2019

You should read....

Article No Longer Available

The R.A.G (Redshift Analyst Guide): Understanding the Query Plan (Explain)

Alex Antra — Wed, 20 Nov 2019 11:15:18 +0000

Welcome to the R.A.G, a guide about Amazon's Redshift Database written for the Analyst's out there in the world who use it.

Previously on the R.A.G....

Article No Longer Available

Redshift has the ability to explain to you how it's going to interpret the query you are about to run, going so far as to estimate how hard it's going to be, how much data it's going to crunch, and what moving around of data it's going to have to do.

This explanation (Query Plan) can help you understand the cost your query is going to have on Redshift which may help give you some tips on how to improve it.

Reading the Query Plan

Example Query plan from AWS

QUERY PLAN
XN Merge  (cost=1015345167117.54..1015345167544.46 rows=1000 width=103)
  Merge Key: category.catname, sum(sales.pricepaid)
  ->  XN Network  (cost=1015345167117.54..1015345167544.46 rows=170771 width=103)
        Send to leader
        ->  XN Sort  (cost=1015345167117.54..1015345167544.46 rows=170771 width=103)
              Sort Key: category.catname, sum(sales.pricepaid)
              ->  XN HashAggregate  (cost=15345150568.37..15345152276.08 rows=170771 width=103)
                    Filter: (sum(pricepaid) > 9999.00)
                        ->  XN Hash Join DS_BCAST_INNER  (cost=742.08..15345146299.10 rows=170771 width=103)
                              Hash Cond: ("outer".catid = "inner".catid)
                              ->  XN Hash Join DS_BCAST_INNER  (cost=741.94..15342942456.61 rows=170771 width=97)
                                    Hash Cond: ("outer".dateid = "inner".dateid)
                                    ->  XN Hash Join DS_BCAST_INNER  (cost=737.38..15269938609.81 rows=170766 width=90)
                                          Hash Cond: ("outer".buyerid = "inner".userid)
                                          ->  XN Hash Join DS_BCAST_INNER  (cost=112.50..3272334142.59 rows=170771 width=84)
                                                Hash Cond: ("outer".venueid = "inner".venueid)
                                                ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..3167290276.71 rows=172456 width=47)
                                                      Hash Cond: ("outer".eventid = "inner".eventid)
                                                      ->  XN Merge Join DS_DIST_NONE  (cost=0.00..6286.47 rows=172456 width=30)
                                                            Merge Cond: ("outer".listid = "inner".listid)
                                                            ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=14)
                                                            ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=24)
                                                      ->  XN Hash  (cost=87.98..87.98 rows=8798 width=25)
                                                            ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=25)
                                                ->  XN Hash  (cost=2.02..2.02 rows=202 width=41)
                                                      ->  XN Seq Scan on venue  (cost=0.00..2.02 rows=202 width=41)
                                          ->  XN Hash  (cost=499.90..499.90 rows=49990 width=14)
                                                ->  XN Seq Scan on users  (cost=0.00..499.90 rows=49990 width=14)
                                    ->  XN Hash  (cost=3.65..3.65 rows=365 width=11)
                                          ->  XN Seq Scan on date  (cost=0.00..3.65 rows=365 width=11)
                              ->  XN Hash  (cost=0.11..0.11 rows=11 width=10)
                                    ->  XN Seq Scan on category  (cost=0.00..0.11 rows=11 width=10)

Cost

As you can see, the word cost is found everywhere in the explain plan. Cost isn't the exact cost of how long it will run end to end, but is instead the relative cost of what it will take to execute the parts of the query.

So what does '1015345167117.54..1015345167544.46 rows=1000 width=103' actually tell you?

Well let's start with the REALLY long number, for one it's actually two numbers separated by two decimal points. So in this example it's 1015345167117.54 & 1015345167544.46 . The first number is the cost to get just the FIRST row of the operation, with the second being the cost to complete the whole job. The costs are cumulative as you go from bottom to top. So the top value is the total cost that includes all the ones below.

Lets break down some theoretical cost scenarios:

First cost is low, second is high: This means that it's easy to get to the data but hard to complete the whole thing, this may just indicate a huge data set that it just needs to slug through. Not necessarily a bad thing, think of using some basic logic against a very large table . It could be an indication that some of your logic has an unreasonably high cost for what your wanting it to do, maybe you've saved a date field in a VARCHAR and Redshift is doing unnecessary conversion in the background.
First and second cost are low: This is a good thing, don't change a thing.
First and second cost are high (but both different): This indicates possibly a bad join, where two large tables are being joined on each other and/or heavy amounts of logic are being applied to both tables. Or possibly you are including far too many actions in a single query, remember to keep code simple.
First cost is high, second is about equal.: This possibly indicates an overly complex query where it takes a lot of processing just to get the first row but once it has that it's not exponentially longer to complete the task. Think of searching a very large table for specifically three things without adding any other criteria.

Second is the rows part of the cost. This is an estimated amount of rows to return and relies heavily on table metadata, so tables that haven't been analysed in a while will return false values to the query plan and can lead to a bad query plan being executed. Row count is the closest thing to understanding how to improve the query as you can possibly add additional logic to bring that row count down.

Last is the width, this refers to the size cost of the columns being returned in bytes which is why it's recommend that you only bring back the rows you need. The only way to reduce this is to select fewer columns.

Sequential Scan

Where you see this, this means that Redshift will scan the entire object (table, cte, sub-query) all rows and all columns checking for the criteria you have specified. This is why it's important to only be dealing with tables that are as small in both rows and columns as possible to speed up query time. In scenarios where you are hitting a source table with a sequential scan, which is unavoidable as you will always need to go a source table at least once, this is where you need to be taking advantage of the table's dist key and sort key as those are the only ways to hit the table as fast as possible.

Inner and Outer

The EXPLAIN output also references inner and outer tables. The inner table is scanned first, and appears nearer the bottom of the query plan. The inner table is the table that is probed for matches. It is usually held in memory, is usually the source table for hashing, and if possible, is the smaller table of the two being joined. The outer table is the source of rows to match against the inner table. It is usually read from disk. The query optimizer chooses the inner and outer table based on database statistics from the latest run of the ANALYZE command. The order of tables in the FROM clause of a query doesn't determine which table is inner and which is outer.

Join Types

Merge Join

In a merge join both tables are perfect for each other. Which means that the join condition on each side is the dist key and the sort key. Meaning both tables perfectly line up without any meddling needed.

This is the best join. Though rare to pull off.

Hash Join

In a hash join, the join conditions aren't perfect for each other but Redshift can mange with a bit of work. So what Redshift does is look at both tables and between them creates a hash table which is like a lookup table that sits in the middle.

Once Redshift has created the hash table it can then do its job and match the two.

Obviously a Merge Join is better, but a Hash Join is fine if you can't swing a Merge, and is very favorable over a Nested Loop.

Nested Loop Join

This is the bad one.

A nested loop occurs when a hash table can't be created between the two. This occurs when you use conditional criteria in the join, like between or greater than.

This will require the database to check every value in the left table against every value in the right table. The complexity of a Nested Loop Join would be “quadratic”, in that you need to do about N*N (or N²) different operations to process the join. Not great! Nested Loop Joins don’t hold up when you’re joining million-row tables together – your database might end up needing to complete trillions of operations to execute that join

Broadcast or Redistribution

When Redshift has to do a join, it may have to move the data around its nodes to complete the join being asked of it. The task of moving that data around can take up time and so obviously if you can avoid this then you can speed up your query.

Broadcast

In a broadcast, the data values from one side of a join are copied from each compute node to every other compute node, so that every compute node ends up with a complete copy of the data.

If this is occurring you will see the phrase DS_BCAST_INNER in the explain plan.

Redistribution

In a redistribution, participating data values are sent from their current slice to a new slice (possibly on a different node). Data is typically redistributed to match the distribution key of the other table participating in the join if that distribution key is one of the joining columns.

If redistribution is occurring, you will see the following phrases:

DS_DIST_ALL_NONE: No redistribution is required, because the inner table has already been distributed to every node using DISTSTYLE ALL.
DS_DIST_NONE: No tables are redistributed. Collocated joins are possible because corresponding slices are joined without moving data between nodes.
DS_DIST_INNER: The inner table is redistributed.
DS_DIST_OUTER: The outer table is redistributed.
DS_DIST_ALL_INNER: The entire inner table is redistributed to a single slice because the outer table uses DISTSTYLE ALL.
DS_DIST_BOTH: Both tables are redistributed.

header image drawn by me

Who am I?

// Detect dark theme var iframe = document.getElementById('tweet-1183218570164965376-963'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1183218570164965376&theme=dark" }

You should read....

Article No Longer Available

The R.A.G (Redshift Analyst Guide): Data Types and Compression

Alex Antra — Wed, 20 Nov 2019 09:53:18 +0000

Welcome to the R.A.G, a guide about Amazon's Redshift Database written for the Analyst's out there in the world who use it.

Previously on the R.A.G....

Article No Longer Available

When building tables you determine what 'data type' the column(s) will be. You do this column by column and while it may seem silly to specify exactly what is going to go into each column, especially when Redshift can guess for you, it can be a big contributing factor in terms of speeding up performance and decreasing table size.

You can also choose to compress your columns.
Compression will allow for more data to fit inside a block, again decreasing table size. It will also improve the efficiencies of the Zone Maps, another thing that can speed up performance.

Data Types

Data Keywords	Description
SMALLINT ,INT2	This is for WHOLE numbers that only take up 2 bytes of data, range: -32768 to +32767
INTEGER, INT, INT4	Also for whole numbers that only take up 4 bytes of date, range: -2147483648 to +2147483647
BIGINT, INT8	Also for whole numbers that only take up 8 bytes of date, range: -9223372036854775808 to 9223372036854775807
DECIMAL, NUMERIC	For numbers with decimal points, up to 38 digits total. When you classify a column as decimal you must declare both the TOTAL length and then how many decimals. For example decimal(10,2) means ten numbers max with two decimal places, this equates to 8 digits on the left of the decimal, and 2 on the right.
REAL, FLOAT4	For storing smaller, rounded down, floating point numbers
DOUBLE PRECISION, FLOAT , FLOAT8	For storing larger, non rounded, floating point numbers
BOOLEAN, BOOL	Boolean is a single byte flag which is either 1 or 0, true or false. Though it can hold a null value. It can also get represented as a checkbox. You can specify that the default value is true or false, if you don't specify a default value then the default value will be null
CHAR, CHARACTER, NCHAR , BPCHAR	CHAR is a fixed length text string, ignore references to NCHAR and BPCHAR those are old functionality merged all into one. CHAR always takes up all of the space you specify, so if you specify char(100) but only put 'Hi' into the column, the remain 98 characters of space will be filled with spaces. Which can cause issues with EXACT object matching. Use when your column is always going to be a fixed length. CHAR will always use up at least 4 bytes of data, even if you specify CHAR(2)
VARCHAR, CHARACTER VARYING, NVARCHAR, TEXT	VARCHAR allows for varying character length which is good for free text fields. Unlike CHAR it will only use however much space has been entered. So a in a VARCHAR(100) the word 'Christmas' will only use 9 of that 100, saving space. VARCHAR will always use up at least 6 bytes of data, even if you specify VARCHAR(2)
DATE	Use this for dealing with time spanning as small as a day, ie. this will not use / show / handle time.
TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE	Use this for dealing with time AND where your entire data warehouse is in the same timezone
TIMESTAMPZ, TIMESTAMP WITH TIMEZONE	Use this for dealing with time across various time zones

Data Type Usage Examples

Below are some examples of how to use the above data types in your code.

CREATE TABLE    <TABLE_NAME>
(
    COLUMN_NAME     SMALLINT,
    COLUMN_NAME     INT,
    COLUMN_NAME     INTEGER,
    COLUMN_NAME     INT,
    COLUMN_NAME     INT4,
    COLUMN_NAME     BIGINT,
    COLUMN_NAME     INT8,
    COLUMN_NAME     DECIMAL(20,2),
    COLUMN_NAME     NUMERIC(20,2),
    COLUMN_NAME     REAL,
    COLUMN_NAME     FLOAT4,
    COLUMN_NAME     DOUBLE PRECISION,
    COLUMN_NAME     FLOAT,
    COLUMN_NAME     FLOAT8,
    COLUMN_NAME     BOOLEAN DEFAULT FALSE,
    COLUMN_NAME     BOOL DEFAULT FALSE,
    COLUMN_NAME     CHAR(36),
    COLUMN_NAME     CHARACTER(36),
    COLUMN_NAME     NCHAR(36),
    COLUMN_NAME     BPCHAR(36),
    COLUMN_NAME     VARCHAR(36),
    COLUMN_NAME     CHARACTER VARYING(36),
    COLUMN_NAME     NVARCHAR(36),
    COLUMN_NAME     TEXT(36),
    COLUMN_NAME     DATE,
    COLUMN_NAME     TIMESTAMP,
    COLUMN_NAME     TIMESTAMP WITHOUT TIMEZONE,
    COLUMN_NAME     TIMESTAMPZ,
    COLUMN_NAME     TIMESTAMP WITH TIMEZONE,    
);

Which Data Type to use?

So there's a lot of data types to pick, and plenty of overlap, so why not just use VARCHAR(999) for everything and go about your day? Reasons, that's why! The below will help.

Dealing with numbers

Firstly, numbers are WAY more performant than text so you should never use CHAR or VARCHAR when you could be using INT, DECIMAL, or DATE.
INTEGERS don't have decimal places, so don't use them when your customer needs to go down to the decimal level, i.e Currency.
INT2 is obviously more performant than INT4/INT8 however it will cap out at 32767 so only use it for small numbers.
When dealing with decimals, be smart about how many decimal points, it helps performance. If you only need two decimal places then just specify 2

Dealing with Flags

Bool is what you should use for flags,
Don't use CHAR(1) or VARCHAR(1) when you could use BOOL as CHAR(1) uses up 4 bytes of data and VARCHAR(1) uses 6 bytes when BOOL uses 1 byte.
Try and set a default flag as it will save you in the long run

Dealing with Dates

When you are just dealing with the DATE, i.e the day, month, or year then use DATE
For Time just use TIMESTAMP
Only use TIMESTAMPZ when dealing with multiple time zones, good data warehouses use the same timezone across all data
Never put a date into a VARCHAR

Dealing with Text

Don't use CHAR if you don't know how long the text is going to be, you will hit size errors, i.e. this text is too big for the field, or you will be forced to set the limit too high wasting space (char uses up all the space by filling it in with spaces).
Conversely don't use VARCHAR if you know the length of all your values. For example a GUID, which is always 36 characters long should be char(36) not VARCHAR(36) as VARCHAR(36) is actually 40 bytes long. Redshift will perform better on char in these scenarios.
Don't use VARCHAR for anything less than 6 bytes, you won't gain any space with VARCHAR(2)
Don't use CHAR or VARCHAR if you are using a flag as BOOL will be quicker and smaller

Data Compression

Compression, also known as Encoding, makes the column smaller. You can chose different types of compression for different scenarios, and some compression types can only be used on certain data types. In theory, compressing data too much can make it longer to read, however that's not often the case as Amazon makes sure it's compression methods balance out storage and reading. In some scenarios, compression actually can use up more space.

Compression Types

Type	Keyword	Applicable Data Types
Raw (no compression)	RAW	All Types
AZ64	AZ64	INT2/INT4/INT8/DECIMAL/DATE/TIMESTAMP/TIMESTAMPZ
Byte Dictionary	BYTEDICT	All but BOOL & TEXT
Delta	DELTA	INT2/INT4/INT8/DECIMAL/DATE/TIMESTAMP/TIMESTAMPZ
Delta 32k	DELTA32K	INT4/INT8/DECIMAL/DATE/TIMESTAMP/TIMESTAMPZ
LZO	LZO	All but BOOL and FLOAT/FLOAT8
Mostly8	MOSTLY8	INT2/INT4/INT8/DECIMAL
Mostly16	MOSTLY16	INT4/INT8/DECIMAL
Mostly32	MOSTLY32	INT8/DECIMAL
Run-length	RUNLENGHT	All but TEXT
Text	TEXT255, TEXT32K	VARCHAR Only
Zstandard	ZSTD	All but TEXT

What one do I use?

Short Answer

AZ64, unless it doesn't apply, then ZSTD for everything else. Most analysis won't require you to nail compression.

Long Answer

Each compression has their specific use cases. Some are more general in their storage while some work in a very specific manner which lead to some very specific downsides if not used properly.

Some TLDRs...

RAW: No compression, your supposed to not compress your first sort key, so use it there.
AZ64: An aggressive compression algorithm with good savings and performance. A general all rounder for Integers and Dates.
BYTEDICT: Creates a dictionary of values, and so repeated values take up no space. Only use on tables with values that repeat a lot. It's dictionary is limited to 256 values, before new ones get stored as RAW.
DELTA /DELTA32K: Compresses data by only recording the difference between values. Only good if the delta (difference) is small and incremental. Large differences can actually cause DELTA to use more space than if it was uncompressed.
LZO: An aggressive compression algorithm with good savings and performance. Used to be the go to, though now you should use ZSTD or AZ64 instead as they are newer and perform better.
MOSTLY8/16/32: This method is one you use when most of the values in a column are 8/16/32 bits, however their are some outlying larger values. So by specifying a Mostyl8, you are saying the majority of values can be compressed to 8 bytes and the outliers left as raw. Only use when you know you can compress most of the columns.
RUNLENGHT: Very similar to ByteDict in the fact that repeat values, are recycled with one value. It's not limited like ByteDict to a set number of values, however you shouldn't use this on any a sort key column .
TEXT255/TEXT32K: For text values only, works similarly to ByteDict in that it makes a dictionary of values. TEXT255 stores values in 1 byte indexes and is limited to the first 245 unique words in a column before new values are stored uncompressed. TEXT32k stores values in 2 byte indexes and will keep storing new words until it hits a hard limit of 32k bytes of data for the combined dictionary. Only low unique text values will result in good compression.
ZSTD: An aggressive compression algorithm with good savings and performance. Will seldom result in using more data than it saves unlike other compression method. Use this where AZ64 does not apply.

Pro-Tip: If sort key columns are compressed more aggressively than other columns in the same query, Redshift may perform poorly.

Redshift can tell you what it recommends.

If you build a table and run the below command, Redshift will recommend, per column, what the compression should be and will even include it's guess at how MUCH the new compression will help by.

analyse compression <table_name>

header image drawn by me

Who am I?

// Detect dark theme var iframe = document.getElementById('tweet-1183218570164965376-966'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1183218570164965376&theme=dark" }