Carl Sauter

Data about airports, runways, routes, and procedures that had previously been ingested into a central database needed to be extracted and converted into a completely different format to meet a specification set by the National Geospatial Agency. Substantial modification to how the data was structured was necessary, with the output comprising 75 files spread across 14 subfolders. Ultimately, these extracts were loaded into flight navigation systems on certain models of planes and helicopters.

I created code that thoroughly tested the extracts' output by transforming the entire source dataset into the expected dataset and comparing it with the output data. This was done using SQL and Python (including pandas). By using vectorization and multi-threading, testing at scale was possible on a single virtual machine in the cloud without Azure Virtual Scale Sets (VSS).

The back end of the website that allows customers to manage how much they contribute to their retirement account includes complex logic that factors in the customer's age and plan specifications. The system handled highly customized plans and needed to be tested to ensure that all paths of the logic were exercised. That was where I came in. I tested the stored procedures where the logic lived, as well as the APIs that served up data to the front end.

I was also asked to stay on longer to help with a portfolio the client had just purchased from a competitor, including ingesting payroll files from the new client companies. I crafted the SQL that was applied to each relevant column and configured other settings for each client's file, and made sure it was imported correctly. Often, these bits of SQL involved very long strings of nested case statements, data type conversions, date formatting, etc.

PROBLEM
With financial transactions spread across many accounts at multiple banks, a customer wanted to combine all expense transactions from his banks into a single dataset for analysis.
SOLUTION
I developed an ELT pipeline for taking downloaded CSV files from various banks, loading them into Snowflake, cleaning up the data, classifying the transactions, and combining them into one table for Power BI to extract and turn into reports.