Hello!

Who Am I?

Lenny (Leonard) Meerwood
A Bachelor of Computer Science from Charles Sturt University
Worked at:

I'm a Data Engineer

Work on a Data Pipeline

Business Intelligence Analyst
Business Insight Analyst

My Presentation Plan

Overview of the data pipelines (Project)

My contributions

Stakeholder engagement
Tools and technical solutions
The teams I involve
Documentation and development methodologies
Success criteria for project

Data Pipeline

What does the data pipeline provide?

Datasets

The Data Warehouse

What are Datasets?

Bespoke information generated for a narrow purpose.

Advantages

Easy to make
Quick to deliver
Fits the intended purpose

Disadvantages

Not always quick and easy
Maintenance
Stuck to a specific scope
Need to know all the columns at build time

Some datasets are quite complex. For instance, Ray created a dataset that has nearly 500 lines of code and 10 pages of documentation explaining it.
They require lots of upkeep. Made a dataset for Sathish and TM1, he is constantly asking for adjustments
Not very good at being used outside their intended purpose. For instace, TM1 datasets.
You need to know exactly what fields the end user requires.

The Data Warehouse

So what does it actually do?

Takes data from production systems, cleans it, applies business rules and provides the data in a format which allows cross-source analysis and encourages exploration.

Why not just use the source systems?

Designed for reading and writing

Slow for massive queries

At the wrong grain
Lacks dimensionality

Designed for fast reading and writing, however, only limited reading. Looking at 100 records simultaneously would be pushing it
The grain is at what level you want to see the information. For instance, we only care about a students daily movements, not anything finer. In all likelihood the grain is not going to match what you need for analysis
Dimensionality is what allows the facts to answer so many different questions, quickly. Want to know the students who are international? You could probably do that with the source data pretty easily. What about the international students who are enrolled in MA1301? Getting a bit slower. What about a count of all international students who enrolled in MA1301 split by year and study period? You've probably just brought the production system to its knees while the data warehouse hasn't broken a sweat.

What does this look like?

The whole system with emphasis on Data Warehouse

A crop of the whole system showing just the Data Warehouse

The Process

Extract
Transform
Load

Extract

Transform

Load

Future plans

Apache Airflow

File B relies on File A and File C

Benefits

Reduces chance for human error
Allows for parallel execution
Better error recovery

Current Error Notification

Airflow Error Notification

Other Airflow Advantages

Design with cloud in mind
Can be used for rudimentary data checks

My Involvement

Stakeholder engagement

Usual flow

Notified by Dan
Communicate directly with stakeholder
CC Dan and Team Mates in correspondance
Leave a paper trail

People email Dan letting him know about changes upcoming to their system. Dan then informs me.
I'll the start comminicating directly with person to get more information, and keep them informed of progress.
Speaking of keeping people informed, I tend to CC Dan and other applicable team members in to the conversation to keep them in the loop. That way any changes that occur does not blindside them
Middle of last year I had to handle a massive change from HR where they updated a few of their definitions around the Work Function and Work Profile. Throughout this time I was in contact with them via Teams keeping them updated on the progress. I also kept others in my team up-to-date as the changes effected nearly eveyrone in the team.
Communication is pretty straight forward, my biggest belief is always leave a paper trail of communications so you can refer back to decisions without bothering the stakeholders again.

Tools and Technical Solutions

Hand-crafted SQL
Oracle SQL Databases
Shell scripts for orchestration

Why Hand Crafted SQL?

It's performant
Not locked into any vendor
SQL is widely known, allowing for easier hiring of staff

Why Oracle SQL?

The business uses it
Looking to move to open source alternative PostgreSQL
- No licensing fees
- Better community support
- No hidden black boxes
- It's mature, in development for 25 years

Internal and External Teams

Internal teams

BIAs and Manager
Talk about this more in the next section

External Teams

Database Administrators
Application Solutions

Documentation and Development Methodology

The move to Git

Code needs to be stored with all revisions
SVN was not good at this
Benefits of Git are:
- Better history
- Easier code reviews
- Tighter integration with existing Atlassian ecosystem
- Accountability for changes

It is important that code is not only stored, but the history of the code is also kept.
We were initially using SVN, but that was clunky and old. Downloading an enitre code repository consisted of download full copies of every branch someone had made
Git was an alternative that is used in many companies the world over.
It provides better history management, storing only the changes to files rather than the a full copy of the repository.
Atlassian offers a service for git called Bitbucket, which integrates nicely with both Confluence and Jira.
Git allows you to look at the person responsible for each line of code. This is critical for data governance

At the beginning of the year I pushed hard for the move to Git. It had been planned for quite some time, however, projects always kept coming up and blocking the way. Misty had created a Git methodoligies page in Confluence, and keeping those methods in mind I moved all four environments to Git.

Knowledge Base

Loss of team members brought knowledge base to mind
Made me reflect on what issues we faced
- Too many single points of knowledge
- Other members where unaware of what we were doing

Reorgaization of Knowledge Base

Johnny Decimal system
Diátaxis Framework of documents
- Tutorials
- How-to Guides
- Refernce
- Explanation
Page ownership and expiration dates

Hello!

Who Am I?

I'm a Data Engineer

My Presentation Plan

Overview of the data pipelines (Project)

My contributions

Data Pipeline

What does the data pipeline provide?

What are Datasets?

Advantages

Disadvantages

The Data Warehouse

So what does it actually do?

Why not just use the source systems?

What does this look like?

The Process

Extract

Transform

Load

Future plans

Apache Airflow

Benefits

Current Error Notification

Airflow Error Notification

Other Airflow Advantages

My Involvement

Stakeholder engagement

Usual flow

Tools and Technical Solutions

Why Hand Crafted SQL?

Why Oracle SQL?

Internal and External Teams

Internal teams

External Teams

Documentation and Development Methodology

The move to Git

Knowledge Base

Reorgaization of Knowledge Base

How does that help?

When someone asks for information, direct them to the relevant KB article

If it doesn't exist, make it and then point them to it.

Team Communication

Team Communication

Validating Success

Thank you for time!