A moderately powerful desktop processor has memory bandwidth of over 50TB/s so yeah it'll take a couple of minutes sure.

Running awk on an in-memory CSV will come nowhere even close to the memory bandwidth your machine is capable of.

The slow part of using awk is waiting for the disk to spin over the magnetic head.

And most laptops have 4 CPU cores these days, and a multiprocess operating system, so you don’t have to wait for random access on a spinning plate to find every bit in order, you can simply have multiple awk commands running in parallel.

Awk is most certainly a better user interface than whatever custom BrandQL you have to use in a textarea in a browser served from localhost:randomport

I haven't been using spinning disks for perf critical tasks for a looong time... but if I recall correctly, using multiple processes to access the data is usually counter-productive since the disk has to keep repositioning its read heads to serve the different processes reading from different positions.

Ideally if the data is laid out optimally on the spinning disk, a single process reading the data would result in a mostly-sequential read with much less time wasted on read head repositioning seeks.

In the odd case where the HDD throughput is greater than a single-threaded CPU processing for whatever reason (eg. you're using a slow language and complicated processing logic?), you can use one optimized process to just read the raw data, and distribute the CPU processing to some other worker pool.

> The slow part of using awk is waiting for the disk to spin over the magnetic head.

If we're talking about 6 TB of data:

- You can upgrade to 8 TB of storage on a 16-inch MacBook Pro for $2,200, and the lowest spec has 12 CPU cores. With up to 400 GB/s of memory bandwidth, it's truly a case of "your big data problem easily fits on my laptop".

- Contemporary motherboards have 4 to 5 M.2 slots, so you could today build a 12 TB RAID 5 setup of 4 TB Samsung 990 PRO NVMe drives for ~ 4 x $326 = $1,304. Probably in a year or two there will be 8 TB NVMe's readily available.

Flash memory is cheap in 2024!

You can go further.

There are relatively cheap adapter boards which let you stick 4 M.2 drives in a single PCIe x16 slot; you can usually configure a x16 slot to be bifurcated (quadfurcated) as 4 x (x4).

To pick a motherboard at quasi-random:

Tyan HX S8050. Two M.2 on the motherboard.

20 M.2 drives in quadfurcated adapter cards in the 5 PCIe x16 slots

And you can connect another 6 NVMe x4 devices to the MCIO ports.

You might also be able to hook up another 2 to the SFF-8643 connectors.

This gives you a grand total of 28-30 x4 NVME devices on one not particularly exotic motherboard, using most of the 128 regular PCIe lanes available from the CPU socket.

A high-end regular desktop is around 100 GB/s to DRAM, 750 GB/s to L3, 1.5 TB/s to L2, 4 TB/s to L1. 50 TB/s would require about 1000 channels of RAM.

I agree with this. BigQuery or AWS s3/Athena.

You shouldn't have to set up a cluster for data jobs these days.

And it kind of points out the reason for going with a data scientist with the toolset he has in mind instead of optimizing for a commandline/embedded programmer.

The tools will evolve in the direction of the data scientist, while the embedded approach is a dead end in lots of ways.

You may have outsmarted some of your candidates, but you would have hired a person not suited for the job long term.

It is actually pretty easy to do the same type of processing you would do on a cluster with AWS Batch.

Possibly, but it seems like overkill for the type of analysis that the OP expected the interviewee to do with awk.

SQL should be fine for that.

Actually, I have a feeling that the awk solution will struggle if there are many unique keys.

For example if they in that dataset have a million customers and want to extract the top 10. Then there is an intermediate map stage that will be storage or memory consuming.

It is like matrix multiplication. Calculating the dot product is trivial, but when the matrix has n:m dimensions and n,m starts to grow, it becomes more and more resource heavy. And then the laptop will not be able to handle it.

(in the example, m is the number of rows, and n is the number of unique customers. The dot product is just a sum over one dimension, while the group by customer id is the tricky part)

I agree completely for this scale. I did want to point out that it's fairly easy these days to do the kinds of things one would do on a cluster, which I learned just a few months ago myself :)

quick addition: there are modules (eg cloudknot) for Python that make it possible to run a Python callable that launches an AWS Batch environment and job with a single method, which you could do anywhere that runs Python.

Hes hiring data scientists not building a service though. This might realistically be a one off analysis for those 6tb. At which point you are happy your data scientists has returned statistical information instead of spending another week making sure the pipeline works if someone puts a greek character in a field.

Even if I'm doing a one off, depending on the task it can be easier/faster/more reliable to load 6TiB into a big query table than waiting hours for some task to complete and fiddling with parallelism and memory management.

It's a couple hundred bucks a month and $36 to query the entire dataset, after partitioning thats not terrible.

A 6T hard drive and Pandas will cost you a couple hundred bucks, one time purchase, and then last you for years (and several other data analysis jobs). It also doesn't require that you be connected to the Internet, doesn't require that you trust 3rd-party services, and is often faster (even in execution time) than spooling up BigQuery.

You can always save an intermediate data set partitioned and massaged into whatever format makes subsequent queries easy, but that's usually application-dependent, and so you want that control over how you actually store your intermediate results.

I wouldn't make a purchase of either without knowing a bit more about the lifecycle and requirements.

If you only needed this once, the BQ approach requires very little setup and many places already have a billing account. If this is recurring then you need to figure out what the ownership plan of the hard drive is (what's it connected to, who updates this computer, what happens when it goes down, etc.).

This brings up a good point: why is the data scientist being asked architecture questions anyway? This seems more like the desired answer for a posting like "hiring for a scrappy ML engineer / sysadmin".

Once you understand that 6tb fits on a hard drive, you can just as well put it in a run-of-the-mill pg instance, which metabase will reference just as easily. Hell, metabase is fine with even a csv file...

I worked in a large company that had a remote desktop instance with 256gb ram running a PG instance that analysts would log in to to do analysis. I used to think it was a joke of setup for such a large company.

I later moved to a company with a fairly sophisticated setup with Databricks. While Databricks offered some QoL improvements, it didn't magically make all my queries run quickly, and it didn't allow me anything that I couldn't have done on the remote desktop setup.

And here we see this strange thing that data science people does in forgetting that 6TB is small change for any SQL server worth it's salt.

Just dump it into Oracle, postgre, mssql, or mysql and be amazed by the kind of things you can do with 30year old data analysis technology on an modern computer.

you wouldn't have been a 'winner' per OP. real answer is loading it on their phones not on sqlserver or whatever.

To be honest OP is kind of making the same mistake in assuming that the only real alternatives is "new data science products" and old school scripting exists as valuable tools.

The extend people goes to to not recognize how much the people creating the SQL language and the relational database engines we now take for granted actually knew what they were doing, are a bit of an mystery to me.

The right answer to any query that can be defined in SQL is pretty much always an SQL engine even if it's just sqlite running on an laptop. But somehow people seems to keep comming up with reasons not to use SQL.

you can scale vertically with a much better tech than awk.

enter duckdb with columnar vectorized execution and full SQL support. :-)

disclaimer: i work with the author at motherduck and we make a data warehouse powered by duckdb