My Professional Portfolio

Here we will find my less-flashy but more technical projects that I have been involved in. I’ll provide as many details as possible, but due to NDAs and HIPAA regulations, the details are limited. (Screenshots and illustrations coming soon)

Metadata driven ETL Engine

SQL, C#, Dimensional Data Modeling, ETL Architecture

This has been the project that has followed me throughout most of my professional career due to its versatility and becoming a very handy tool in my belt.

When faced with the challenges of:

1.) Being in a small department and the only ETL architect, and

2.) Having a metric ton of data to consume on a daily basis from hundreds of tables

I needed to come up with a solution that could take a lot of the manual work out of the <Source System> to <Data Lake> to <Data Warehouse> steps. Since this would have to involve programmatically generating a lot of ETL code, I would need to know as much of the source systems’ table designs as possible, and what better way to gather that than to pull the metadata directly? And further, what better way to store that metadata than in a series of SQL tables that would naturally extend the INFORMATION_SCHEMA. This provided a straightforward way of programmatically extracting source tables and loading the necessary data into the data lake, and then through a series of scripts that would parse the SQL code that transformed this data into the target data warehouse, the inter-dependencies between these tables could be inferred and handled automatically during the target system load.

The final result was a very straightforward, hierarchical approach to loading the data warehouse — from the source system all the way down to the individual column — taking into consideration all source system availability, data reconciliation, CDC requirements, and object dependencies, and then load the required target model. The cool thing about the programmatic approach to this was that any single attribute could be tweaked by simply updating a record in the “ETL Control” database, and letting the objects rebuild themselves. For example, if a certain dimension table was set to be Type I and there was a change in requirements that required it to be Type II, a simple flag in the control database would rebuild all necessary tables and regenerate the code that populates this table accordingly. Best of all, since all changes in the data were captured at the data lake level, a shift from Type I to Type II could be created retroactively, and all of the historic changes to each dimension were immediately available as of the start of the data lake.

ETL Agent

C#, SQL, Log Parsing

Of course, all of the above is great and all, but without some means of pulling the most critical information out when it is needed the most, and allowing the developer to actively monitor the health of all processes, it would be easy to get lost in the amount of output that is being produced.

So I created a (very simple) C# Form that would keep tabs on all tables and all of the steps they would go through during a regular load. Failures would jump right out at you, and a simple double-click would give you the exact error message that was captured from the process. Since everything was driven by their inter-dependencies, then as soon as the error was resolved, everything would continue on as normal, without the need to rerun or cleanup tables from a bad sync-up.

mETL – The Final Form

SQL, Java, .NET, cron, Azure, AWS CLI, etc.

Metadata driven ETL…mETL…or “metal” as I call it. It’s catchy, right? Through many different incarnations of the above approach, I started to hone in on the most universally sound means of implementation. What started out as a strictly “SQL Server + SQL Agent jobs” combination that was controlled by some .Net applications and coordinated through the Windows task scheduler, eventually became a “DB Agnostic” solution (connections for the ETL Control database set in the config files of the implementation) and the rest was handled by a series of Java applications that ran as daemons in a perpetual loop to constantly be on the lookout for what tasks needed to be run, and reporting back to the control database as needed. The leap from a Windows implementation to a Linux/Unix environment was absolutely painless.

This final step allowed for a much easier startup for whatever environments I would come across — whether it was a Windows or Linux shop, or the target system was a Greenplum, Netezza, or any of the cloud providers, or the preferred control database was Postgres, MySQL, MSSQL, or even AzureSQL or Amazon RedShift, the changes required to the base code were minimal and the engine could be up and running that same day.

My Portfolio

Welcome to my portfolio. Here you will find more information on my personal and professional projects that I have created over the years. Since most of my professional projects deal strictly with ETL methods and data analysis, let’s start with the flashier projects.

GLaDOS Robotic Ceiling Lamp

3D Printing, Arduino, Microcontrollers, LEDs

Some would call me crazy for building this, but I would ask them to wait until it’s finished before making that judgement — because it will definitely be warranted at that point. As of now, it’s just a pretty ceiling lamp with servos to make it move and pivot. In the future, it will connect to my smart home and control everything via voice commands, with responses vocoded to sound just like the real deal.

The Binding of Isaac Custom Water Fountain

3D Printing, Epoxy finishing

As a huge fan of the indie game, and wanting to pay tribute to its final installation, I’ve made this centerpiece for a fountain. Printed completely hollow using “stone” PLA, and glazed with an epoxy resin to keep it waterproof, it’s fed with a small water pump for completely realistic (according to the game) tears.

Starcraft Protoss Pylon USB Charging Hub

3D Printing, Arduino, Addressable LEDs

After hearing throughout my whole life “YOU MUST CONSTRUCT ADDITIONAL PYLONS” I finally did! This was my largest print to date, with the center crystal standing at 28″ tall. I printed the frame in metallic gold PLA, and the crystal in transparent PETG. I printed the crystal completely hollow so that it could easily house the USB hub, Arduino, and strip of addressable LEDs.

This was my first foray into programming addressable LEDs, because I wanted to make sure it had the signature “pulsing blue” effect. This would also be the spark that started many….many months of various addressable LED related projects, with some mentioned below.

Pool Lighting Project (in progress)

Arduino, and 85 feet of waterproof addressable LEDs

So, if I could use 3 feet of LEDs to light up my Pylon project above, the next natural step seemed to be “let’s try to go for 85 feet in an environment highly prone to water exposure”. I quickly ran into limitations of the Arduino, learned a lot about voltage and amperage requirements, and had many, many failures, but eventually…

Pool Lighting Project (complete!)

Arduino, Addressable LEDs, Audio Analysis

After about seven different iterations due to one failure or another, I finally came up with a stable solution that has withstood many months of abuse. As time went on, I started thinking of ways that the LEDs could be programmed to be reactive to audio input. I started using basic audio sensors to gather binary input, but that only provided the most basic of data.

After a lot of research and a bunch of trial and error, I came up with a solution using the MSGEQ7 IC that could be used to split the analog signal from an audio cable input into the base 7 frequency ranges for further analysis. Once I had that data, I could use the Arduino to perform real-time analysis on the audio in order to pick out rhythm, tempo, bpm, and other musical elements. Once I had those, that’s when the fun began, and I could start designing patterns and designs for the LEDs to show in reaction to the changes in the music. When you have control over 750 LEDs that you can programatically change every 30 milliseconds based on whatever parameters you can think of, and have real-time data of the music being played, the possibilities are really endless.

(Video demo below! Sorry for the poor audio quality. I’ll splice in a clean track sometime soon.)

Audio Desk Project

Arduino, Addressable LEDs, Audio Analysis

So, the pool project was cool and all, but there’s only so much you can do to represent audio in a single LED strip. This project was designed to use multiple, smaller LED strips that could be individually controlled and react to different elements of the music (and also to give myself a really flashy battle station!)

Enchanted Minecraft Sword

3D Printing, Arduino, Addressable LEDs

This is a fairly basic project, but it holds a special place in my heart because of this is what really got my son (4 years old at the time) really interested in these concepts. Sure, you could buy the foam version of this at most retail stores…or you could science yourself up one that actually lights up and pulses like the ones in game! After this project, his questions definitely shifted from “can we buy *some toy*?” to “can we make something like *some toy*?”, sparking a whole new series of fun projects that we’ve enjoyed ever since.