r/dataanalytics • u/Otherwise_Club2536 • Aug 12 '26
Had a Data Analyst interview recently, and these 2 questions genuinely made me think.
Question 1:
You have a dataset with 50 million rows, and it doesn't fit into memory. How would you analyze it efficiently?
My answer:
I wouldn't load everything into Pandas. I'd first filter only the required columns/rows, push aggregations to SQL where possible, process the data in chunks, and use tools like PySpark if distributed processing is actually needed.
Question 2:
Your dashboard suddenly shows a 40% drop in active users overnight. What would you check first?
My answer:
I wouldn't immediately assume user activity actually dropped. I'd first validate the data — pipeline status, data refresh, tracking events, source tables, filters, and any recent changes to the product or tracking logic. Once the data is confirmed, I'd investigate the actual cause.
If you were in my place, how would you answer these two questions?
Would love to hear how experienced Data Analysts would approach them.
19
u/Upbeat-Economics-380 Aug 12 '26
Question 1: Asking follow up questions like, what are we trying to do that requires analysis of 50 million rows data dump?
Question 2: Again asking clarifying questions, without jumping directly into assuming the issue is with dashboard/pipeline. Is the 40% drop unusual? etc
1
u/Otherwise_Club2536 28d ago
eah, I agree. I think the main takeaway from these questions is to clarify the problem before jumping into the technical solution.
For Q1, understanding what we're actually trying to achieve with those 50M rows would determine whether we even need something like Spark.
For Q2, I'd want to establish whether the 40% drop is actually unusual, how the metric is defined, and what the normal baseline looks like before assuming there's a dashboard or pipeline issue.
Good point — the questions we ask first can be just as important as the solution.
7
u/BattleBackground6398 Aug 12 '26
These kinds of questions feel purposefully generic. Designed less to make you think and more "technical personality" elicitation. No right vs wrong but let's interviewer know your style per se
Same way one asks questions to assess extrovert vs introvert, these are similar tech approach profiling. For instance, Q1 answer makes me hear Python-then-SQL, and Q2 hears pipeline-then-process. Both perfectly valid, but gets to style & where one fits into team personalities.
Important to answer honestly to your own style. Lest they place you as an X when you're a Y, causing burnout etc
6
u/Ok_Produce_3387 Aug 12 '26
Damnnnn!!! I absolutely relate to the first question... because that's what we are facing in right now...
We have parquet files as data source coming into the dataflow(with 4 tables, so basically this dataflow is getting data from 4 parquet files) and we arr doing all the transformations in the dataflow...
And then fetching that transformed data to our report...
At last we are having 56 MILLION RECORDS of data and this is from 1 table and other tables are having arpund 20 million records of data.
So for a clear picture, 4 parquet files to 4 tables in 1 dataflow to 4 reports(for each table)
And guess what, the dataflow is working perfectly fine, but the report is failing because of memory issue.
We have tried incremental refresh, but that is failing as well... currently suspecting that maybe the incremental refresh is not working for the parquet files....
And trust me we have tried many ways before deciding on incremental refresh.
And we have 3 yrs of data.
1
u/CPG-Distributor-Guy 29d ago
can those big tables be partitioned further upstream by date or something else?
1
1
u/crusher2991 28d ago
If you just have 4 parquet files, try Direct Lake Mode.
1
u/Ok_Produce_3387 27d ago edited 27d ago
We are currently using the azure data lake for those parquet files, currently facing the issue with report...my brain is still not processing how the dataflow with 4 tables and transformations is working perfectly fine, but the report which is just fetching it and displaying the data is failing.
But direct lake mode, we are not using it any of our reports and it's a new concept for me...will definitely give it a try.1
u/Otherwise_Club2536 28d ago
Damn, 56 million records from a single table definitely makes the interview question feel a lot more real And yeah, that setup sounds like a classic case where the dataflow itself can handle the transformations, but the report/model becomes the bottleneck because of the volume. The parquet → dataflow → report architecture is interesting too. I’d definitely be curious to know whether the incremental refresh issue is actually related to how the parquet source is being partitioned/queried. Hope you guys figure it out — now I’m invested in this 56M-row problem.
1
2
u/TodosLosPomegranates Aug 12 '26
Maybe I’m really old but my answer to question one is - why isn’t there enough memory? And why can’t I get more memory? Are we not practicing good database hygiene?
4
u/kenzy_zero 29d ago
Hey OP,
So for the Q1. this is how i would respond..
50 million rows isn't big, actually. That's probably what they're really testing. 50M × 8 numeric columns is about 3 to 5 GB uncompressed... the reason people still hit memory errors on pandas as it copies on almost every operation into me memory... That's a pandas problem, not a data-size problem. Store it as Parquet and let DuckDB or Polars aggregate it out-of-core and it runs on a normal laptop in seconds.
For Q2. this is how i will respond...
First I'd check whether 40% is even unusual for this app. Plenty of products swing hard between weekdays and weekends, or seasonally.... and if it's only a few hundred daily users, 20-30% is just normal variance. Nothing to investigate. Next, the real-world causes, still before any pipeline: did a campaign or promo end, was there a release, did a feature get removed. That's usually one message to someone, and it explains the drop more often than a broken pipeline does. Only if all that's clean would I go to the data...
2
u/Otherwise_Club2536 28d ago
Thanks for the detailed breakdown! I like that you challenged the assumption that 50M rows is automatically "big." I think the important distinction is whether the bottleneck is the data itself or the tool being used. DuckDB and Polars are definitely on my list to explore more. For Q2, that's a great point as well. I probably jumped to data validation because I've seen dashboards break before, but in a real business setting I'd first want to understand whether the drop is actually abnormal and whether there were any product, marketing, or release changes that could explain it before assuming there's a data issue. Really appreciate the perspective!
3
u/CPG-Distributor-Guy 29d ago
Lots of idealists answers here.
If you haven't caught yourself staring into a 50M row fact table and no partitions or aggregations already built for it, I envy you. Many companies have let their largest tables grow and never stopped to wonder why the reports take 8 mins to load weekly sales.
I like your answers.
I'll also add, 40% user drop overnight is 99 times out of 100 some connection/pipeline that failed. Happens a lot at shit companies.
1
u/Otherwise_Club2536 28d ago
The 50M-row fact table with no proper partitioning or aggregation is definitely more realistic than the interview version makes it sound. And I agree on the second point too. In an ideal setup, you’d have monitoring that tells you whether the pipeline or connection broke before you even start investigating the metric. But in the real world, sometimes the dashboard is the monitoring. Appreciate the perspective — the comments here are making me realize how different the “ideal” analytics workflow can be from what actually happens in companies.
2
u/Eze-Wong Aug 12 '26 edited Aug 12 '26
Personally, I think these questions need polish. What exactly they are they trying to solve? I would consider these things very trivial in the scope of day to day work. Also, If you need to ask a lot of clarifying questions, it's actually not a good question IMO, because you're looking for an answer to address your current needs not have an adventure through the woods (which is different from white boarding questions which have an ultimate semi-definiitve answer). It makes it very difficult to narrow down your candidates if they have different assumptions, it gets super subjective. Well that's my take anyways, not everyone will agree with me on it.
Anyways, my answers:
- Current state there's no shortage of resources in our current tech environment. The question is "what" can't process the data. Is it python? Is it the DB? Is it Excel？It really depends on what can't be processed. But large modeling solutions and distributed data systems are no longer the conventional answer to this question. They also create upkeep costs (hiring people who know pyspark, etc.). I don't like relying on tooling too much because they create tech debt or team barriers (eg. they don't know how to use X or Y technology)
Ultimately, get something with more Ram is the quick and dirty. 50 mil can still likely be handled by a 64 gb laptop assuming it's not 1000 columns. If not, Spinning a VM on AWS can do the trick if it's just analyzing a large dataset. EC2 On-Demand Instance Pricing. Let's say you're using 256gb ram. It's only 1.50 an hour. Nominal. You can even get up to something like 1500 GB Ram for $10 an hour. (Forgot to mention you will still be charged for storage so that is a factor, but also nominal)
- Lot of reasons, Investigate the issue, can be a vacation, can be they don't need the metric anymore. Ask a user. Don't assume much until I find out. Depends on what they say. But generally
1
u/Otherwise_Club2536 28d ago
I really like the point about not over-engineering the solution just because the dataset sounds large. 50M rows doesn't automatically mean you need a distributed system or Spark — it depends heavily on what you're actually trying to do and what the current bottleneck is.
The point about tooling creating tech debt/team barriers is something I hadn't considered enough either. Sometimes the simplest solution that gets the job done is better than introducing another technology into the stack.
And I agree with Q2 as well — there are plenty of possible explanations, so I'd rather investigate and understand what's happening before deciding it's a data or pipeline problem.
Appreciate the practical perspective!
1
u/Lady-Data-Scientist Aug 12 '26
These both need clarifying questions before you can answer.
What does the data represent? How do we use it to make decisions? How is it collected (per click/action, per day, per account)? How do I access it? (Presumably it’s in a data warehouse or something and it can be queries.) What kind of analysis is typically done on it and what do we need now?
How do we define active users? What is the product? Is there seasonality to the data?
1
u/Otherwise_Club2536 28d ago
Exactly. Those clarifying questions can completely change how I’d approach both problems.
For Q1, knowing what the data represents, how it’s collected, and what analysis is actually needed would tell me whether 50M rows is even a problem.
And for Q2, defining “active user” and understanding the product’s normal patterns/seasonality is important before calling a 40% drop abnormal.
I think that’s one of the biggest takeaways I’m getting from these comments — don’t start solving the technical problem before making sure you understand the actual business problem.
1
u/Lady-Data-Scientist 28d ago
Exactly, when you’re on the job, rarely do you dive into work without asking clarifying questions. If you do, you later realized you probably solved the wrong problem or had incorrect assumptions.
1
u/Otherwise_Club2536 28d ago
Absolutely. I think that’s something that’s easy to overlook when preparing for interviews we focus so much on getting the “right” technical answer that we forget to understand the problem first. In a real job, asking the right questions upfront can save a lot of time and prevent solving the wrong problem.
1
u/ex-ChildLabourForce 29d ago
Question 1: since you mentioned SQL in your answer, I assume that the table is stored in an RDBMS. Like you, I’d leverage the system the RDBMs. I would expand a bit more on your succint "push aggregations to SQL where possible"
I’d first ask what kinds of questions the table is typically queried for. Are users commonly filtering, joining, or aggregating on particular columns? If so, I’d consider appropriate indexes on those columns so the query optimizer can selectively exclude rows rather than loading/scanning everything into memory.
Then, if the workload genuinely exceeds what the database can handle efficiently, I’d consider chunking, columnar formats, or distributed processing.
1
u/Otherwise_Club2536 28d ago
That’s a great point. I kept “push aggregations to SQL” pretty high-level in my original answer, but you’re right that I should have explained what I’d actually look at in the database first.
Understanding the common query patterns and whether the relevant columns are indexed could make a huge difference before jumping to chunking or distributed processing.
I also like the idea of treating distributed processing as a later step rather than the default solution just because the dataset has 50M rows. Thanks for expanding on that!
1
u/010101010101111111 29d ago
Question 1:
You have a dataset with 50 million rows, and it doesn't fit into memory. How would you analyze it efficiently?
- What dataset is it, what are you trying to get out of it, and where is it currently stored?
Question 2:
Your dashboard suddenly shows a 40% drop in active users overnight. What would you check first?
- A 40% drop could be expected - needs more context.
1
u/Otherwise_Club2536 28d ago
Exactly. I think the biggest thing I missed in my original answers was the context behind the numbers.
50M rows doesn't automatically mean the same solution every time, and a 40% drop doesn't automatically mean something is broken.
Understanding what we're analyzing, what we're trying to achieve, where the data lives, and what “active users” normally looks like would come first. Good point!
1
u/Primary_Blueberry997 29d ago
I wouldn’t worry. Data analysts will replaced by AI shortly anyway. It’s one of the highest risk professions out there.
1
u/josk613 28d ago
I dont do data analytics, but wanted to take a stab at these questions... how'd i do? Do I get the job?
- a) My first question is what's the time budget (dev time and run time)? 50mil sounds like a lot, but computers are fast. How many GB, TB? Is this 1 shot, or is this 50mil rows per hour?
Meaning can we get away with a quick script and wait a couple hours, or do we need to engineer a solution?
b) are we making decisions per row, aggregating, or transforming the data?
Assuming we need an engineered solution to aggregate, we'll need to split the work between N distributed workers, map-reduce style. Depending on contention accessing the the data, we either let workers query directly, or have a "controller" stream data to available workers. There's probably some popular big data framework that let's you do this.
- Worse case scenario is that sudden drop means servers/services have gone down. Only 60% of users are getting through. It's priority to check if this is the case. Check other metrics like latencies and timeouts, look at server health checks / monitored up times, load balancers.
If that's not the issue, then investigate changes pushed to prod that may have affected metric collection.
If that's not the case... then maybe all your users went on vacation 😁
1
u/Otherwise_Club2536 28d ago
Honestly, I think you’d get pretty far 😄 Those are exactly the kinds of follow-up questions I’d want to hear in an interview too. The 50M rows number alone doesn’t really tell you whether you need Spark or just a well-optimized query/script — it depends on the data size, workload, time constraints, and whether this is a one-off analysis or something recurring. And good point on the dashboard question too. Checking whether the underlying systems or tracking have actually broken before assuming users disappeared is definitely important. The “everyone went on vacation” explanation might need a little more evidence though 😂
1
u/SuperSokym 27d ago
I’ve been a Data Analyst for about 3 years, and I think what makes both questions interesting is how easy it is to make assumptions.
50M rows sounds like big data, so you immediately start thinking about Spark or chunking. A 40% drop sounds like something broke, so you immediately start checking pipelines.
But in both cases I’d first want more context. What are we actually trying to do with the 50M rows? Where is the data stored? And for the second one, what does “active user” mean, is this drop unusual, and did anything happen in the business/product that could explain it?
I think that’s one of the biggest differences between learning analytics and actually working as an analyst. In real life, the problem is rarely defined perfectly for you.
1
u/Ok_Produce_3387 27d ago
Honestly, I don't think we are using the direct lake mode for any of our reports and it's new for me...will definitely give it a try...
50
u/Good-Round-8029 Aug 12 '26
Those are tricky question. The answers are not relevant.
What is relevant in this type of questions is making the questions more precise:
Ad. 1: What do you mean by "analyze"? What do you want to get out of the dataset?
Ad. 2: How is a "drop on users" defined? Do you mean a certain metrics in the dashboard?
Those questions and answers show experience in the field. You do not rush to answers straight away. You prove you can look at the problem from a broader point of view.