Measure Or Perish

Been outside of engineering-land and in computer-science land as of late. Been spending a lot of time investigating some different data-warehousing patterns for a project at work, it's been a fascinating look into the difference between engineering problems and running actual scientific inquiries into a problem. Engineering is so often concerned with constraints and implementation concerns because most problems have fairly well-established solutions within the computer science field. But at a certain point with engineering you run into problems that require a detailed and scientific inquiry that leads you by first principles into territory you, the engineer, were unfamiliar with.

I have to avoid speaking about specifics due to NDA concerns, but the problem involves a flattened

.bak

file holding proprietary data dumped from a vendor. Our client wants us to host some partition of this data, which we pull from an SFTP transaction. The problem is that Microsoft SQL Server has to run the decryption on this data, and then we need to translate this data to our application surface. We don't use MSSQL in our application, so our plan is to read from a remote RDS instance in AWS which can host an ephemeral image of the .bak file.

If any reader is familiar with massive data transfers, they'll start to piece together the solution, but if you are not familiar with data warehousing you might start out naively reading tens of millions of records from your RDS instance into a postgres instance, performing ETL while holding the RDS connection open, and finding out you've spent 40 minutes synchronously writing data from one to the other in a process that is supposed to become an hourly dump. Hahaha! By my estimates this implementation would have cost us well over a thousand dollars a month to run!

Where engineering is concerned with this is relating to the process of how to efficiently perform this operation in a fraction of this naive implementation's runtime, but it is the process of Computer Science which ultimately leads the engineer to his solution. having spent the better part of the last two days, I've managed to derive "sophisticated" Transient Storage Techniques for Data Lakes: literally just dumping the MSSQL data out to .tsv files in an s3 bucket for asynchronous writes to my application warehouse database. Sometimes you spend hours chasing parallelism, batch-write techniques, microsecond differences between INSERT and COPY, and ultimately realize the bonehead implementation is the fastest, cheapest, and most-integral!

And the Novice Data Technician (me) can really only arrive at this conclusion via research and metrics. It Is By Measurement We Live And Die!