Tom Mitchell

Building data warehouses for fintech & investment firms.
Writing about architecture, consulting & high-value data engineering.

[Tom Mitchell @tommitchelldata]

Today I start a new consulting role as a data engineer for a London based company.

I'll be using tools like SQL, Python, Airflow, DBT and GCP.

I'm thinking about documenting the role day-by-day, challenges, tasks, my thought process etc - would that be interesting to you?

[Tom Mitchell @tommitchelldata]

@knightcode_abhi Architecture: airflow handles coordination, we have files coming from various providers on varying schedules, we dump into GCS, append to our source tables then dbt takes over we have staging -> intermediate -> marts and are using a BI tool called Omni. DWH is big query.

[Tom Mitchell @tommitchelldata]

Hey guys, it’s been a while.

I’ve been away building a data warehouse for a client.

I’m on the train home and thought I’d do an Ask Me Anything.

Got a question about GCP, Airflow, dbt, or SQL? Drop it below and I’ll do my best to answer for the next ~1 hour. 👇

[Tom Mitchell @tommitchelldata]

@calvinebarongo Yeah I agree I see it on (almost) all job specs. We use a lot of macros btw. E.g where you want to generate a surrogate key, we have a macro we use in our models we feed fields into to save us having to rewrite the code every time. Focus on macros if you have time 😊

[Tom Mitchell @tommitchelldata]

@calvinebarongo Oh and GitHub for version control/jira for project management

[Tom Mitchell @tommitchelldata]

@calvinebarongo So I created a jira ticket, added a comment to the code in the repo so when we get notification that it’s fixed we can quickly update the code to use the data correctly. Stack wise: google cloud, airflow (on GCP), dbt (dbt cloud), sql and python for ad hoc bits

[Tom Mitchell @tommitchelldata]

@SirAwesome__ Going good, for once it was building a warehouse from scratch which I’ve never done before. Always jumped onto projects where there was something there already

[Tom Mitchell @tommitchelldata]

I hope you've found this thread helpful.

If you did, I ask for 2 small favours:

1. Follow me @imtommitchell for more like this daily.

2. Click below, jump to the top and share to help someone else.

[Tom Mitchell @tommitchelldata]

P.S. If you're interested in data you'll love my weekly newsletter.

I share everything I know about building high-paying data skills from my 8+ years in the industry.

Subscribe for free here:

https://t.co/sAbqnnIU2Z

[Tom Mitchell @tommitchelldata]

Knowing how to select data in SQL isn't enough.

You need to know how to join tables.

To join tables correctly you need to understand:

- Primary keys
- Foreign keys
- Unique keys

Here's my super-simple breakdown of each one:

[Tom Mitchell @tommitchelldata]

TL;DR:

- Primary Keys are unique tags for each row of data in a table like customer ID

- Foreign keys act as Part B in the link and can point to a Primary Key (part A) in another table.

- Unique Keys are a column (or columns) that contain unique values. Not the primary identifier.

Thanks for reading.

If you liked this, follow me @imtommitchell for more content like this every day.

[Tom Mitchell @tommitchelldata]

3. Unique Key

A unique key is similar to a primary key, but it doesn't have to be the main identifier for a table. It's a column (or columns) that contains unique values, like a primary key.

However, a table can have multiple unique keys, while it can only have one primary key.

Unique keys are handy when you want to make sure that certain columns have distinct values, but they don't serve as the primary way to identify each row.

For example, in a customer table, you might have a unique key for email addresses to prevent duplicate email entries.

[Tom Mitchell @tommitchelldata]

P.S. If you're interested in data you'll love my weekly newsletter.

I share everything I know about building high-paying data skills from my 8+ years in the industry.

Subscribe for free here:

https://t.co/sAbqnnIU2Z

[Tom Mitchell @tommitchelldata]

I never realised how much I would need debating skills working in data.

Data talks but that doesn't mean people listen.

To debate effectively you need to:

- Put yourself in your stakeholder's shoes.
- Anticipate pushbacks.
- Structure your argument around your claim.

[Tom Mitchell @tommitchelldata]

P.S. If you're interested in data you'll love my weekly newsletter.

I share everything I know about building high-paying data skills from my 8+ years in the industry.

Subscribe for free here:

https://t.co/sAbqnnIU2Z

[Tom Mitchell @tommitchelldata]

Don't aim for this:

Excel - 100%
SQL - 0%
PowerBI/Tableau - 0%
Python/R - 0%

Aim for this:

Excel - 25%
SQL - 25%
PowerBI/Tableau - 25%
Python/R - 25%

You don't need to know everything straight away.

[Tom Mitchell @tommitchelldata]

P.S. If you're interested in data you'll love my weekly newsletter.

I share everything I know about building high-paying data skills from my 8+ years in the industry.

Subscribe for free here:

https://t.co/sAbqnnIU2Z

[Tom Mitchell @tommitchelldata]

Data cleaning is one of the most important skills for a data analyst.

Not Excel.
Not SQL.
Not PowerBI

Without clean data, any analysis done is unreliable.

Here's my data cleaning 101:

[Tom Mitchell @tommitchelldata]

And there you have it - my data cleaning 101.

TL;DR:

- Handle duplicates and missing data
- Fix naming conventions in values and columns.
- Validate and test: does it make sense/look right?

Thank you for your time.

If you got something from this post, consider following me @imtommitchell

I post data-related content daily.

[Tom Mitchell @tommitchelldata]

Finally, validate and test.

This is the most crucial part.

Do some test aggregations and visualisations, and ask yourself:

- Does the data make sense?
- Does it prove or disprove your theory?
- Is there anything that doesn't "feel" right when observing?