Ingestion

1.A checkout event is delivered twice because the producer retried. The warehouse counts two orders. What should the load use as its identity?

2.The source sends a correction with the same order id and a new total. Your job only inserts. The warehouse still shows the first total. What kind of write is missing?

3.Two corrections for the same order arrive out of order. The older total lands last and overwrites the newer one. What do you compare?

4.Yesterday's partition was declared complete at 02:00. A late event for yesterday arrives at 15:00. The daily report already ran. Where does the late event go?

5.You alert when no event has arrived for two hours. A parking-lot sensor is quiet every Sunday. The alert fires every Sunday. What is the check actually measuring?

6.The producer stamps event time in local time with no zone. A server in another region loads it as UTC. A morning order looks like the previous evening. What was omitted?

7.The source adds a field and marks it required on the same day. Files already in your backlog do not have the field. The load rejects the backlog. What should 'required' wait for?

8.A column that was a number arrives as a string for one hour, then goes back to a number. Your load casts everything to a number and drops the bad hour. What did you fail to keep?

9.You parse JSON into columns and throw away the original document. A month later someone needs a field you did not extract. Where is it?

10.Two regions stamp ids from their own sequences. Both emit order 100. Your primary key is the id alone. One order overwrites the other. What is the key?

11.The producer sends epoch milliseconds. A loader written for seconds multiplies nothing and stores a time in the year 50000. What unit did you fail to agree?

12.You batch files by the hour they arrived, not by the hour the event happened. A late event from 09:00 arrives at 14:00 and is stored under 14:00. A query for the 09:00 hour misses it. What is the partition?

13.A hourly job lists the files present at the start and loads them. A file arrives while the list is being processed and is not in the list. The next hour's job also starts from new files and skips it. What was the bug?

14.Two loaders pick up the same pending file and both insert. You have no unique key. What do you add so the second loader does not double the rows?

15.The load commits the rows and then deletes the pending mark. It crashes between the two. The next run loads the file again. With a unique key, what happens?

16.A file is half-written when the loader starts reading it. You load a truncated JSON array and commit. The writer then finishes. What should the writer do before you are allowed to read?

17.The source says a batch is complete when the byte count matches a manifest. You load a file whose count matches and whose last record is cut off mid-character because the count was computed wrong upstream. What else can you check?

18.Bad rows are skipped so the job stays green. A week later a required total is short by the skipped amount. Where should those rows have gone?

19.You alert on quarantined rows as a percentage of the file. A file with one row, and that row bad, is 100%. A file of ten million with a thousand bad is nearly 0%. Which one can hide a broken producer?

20.Schema registry says the new field is optional and has a default. An old consumer that does not know the field reads the stream. What must be true for that consumer to keep working?

21.You rename a field in the producer and keep the same field id in a format that keys by id. Old and new consumers both work. A teammate renames the id as well. Who breaks?

22.Change data capture reads the database log. A load reads the application table with a timestamp column the app sometimes forgets to bump. Which source sees the forgotten update?

23.The capture connector restarts and replays the log from a saved offset. The offset was saved before the last commit of rows. What do you need on the rows?

24.A snapshot of the source is taken while capture starts. Rows that change during the snapshot appear both in the snapshot and in the log, or in neither, depending on timing. What do you reconcile?

25.Deletes in the source are hard deletes. Your capture turns them into missing rows and your warehouse still shows the customer. What event did you fail to apply?

26.You compact the event log and drop tombstones after a day. A consumer that was offline for two days never sees the delete and never drops the row. What retention did the consumer need?

27.A stream is partitioned by customer id. Two events for one order are produced by two services onto different partitions because they hashed different fields. The loader assumed one partition is one order's sequence. What broke?

28.You key the partition by a millisecond timestamp. One hot second sends every event to one partition and the others sit idle. Why was time a poor key?

29.A consumer commits its offset and then writes the rows. It crashes after the commit and before the write. Those events are not in the table and will not be read again. What did you commit too early?

30.You commit after the write, and the commit fails. The next attempt writes the same rows again. The table has no key. You get duplicates. Which half of the pair is still missing?

31.A backfill reprocesses 90 days into the same table the live job writes. The backfill is slower and overwrites a live correction with an older extract. How do you keep the live correction?

32.The backfill is correct and the live job had a bug for a week. You need the week replaced and the days after the fix left alone. What do you bound?

33.Analysts query a table while you swap a partition for a backfill. They can see an empty partition mid-swap. How do you publish the replacement?

34.A daily total is stored, and late events are also stored, and the total is not updated. Finance reads the total. They are short. Who owns the rule that says whether the total moves?

35.You store the raw file in object storage and the parsed rows in a table. The table is wrong and the file is still there. What is the recovery?

36.The raw bucket has a lifecycle rule that deletes objects after seven days. A bug is found on day eight. What did the rule cost?

37.Personally identifiable payloads are in the raw bucket, and the bucket is readable by the whole data team. Analysts only need the parsed, minimized table. What is overshared?

38.You hash email with a fast hash and no salt so you can join two systems. A colleague reverses common addresses with a dictionary. Why was the hash not a secret?

39.A load copies a column called notes into the warehouse. Notes sometimes contain a card number a customer typed. A scanner finds one a week later. What is the late part?

40.The source database is in another company. They give you a nightly full dump, and you also want changes every five minutes. They will not open the log. What can you still ask for?

41.The partner's change file has updates and no deletes. Customers who left still appear in your copy. What question do you send back?

42.You diff a full snapshot against yesterday to invent deletes. The partner's dump today is partial because their job failed halfway, and you treat every missing customer as deleted. What check stops that?

43.Events arrive with a sequence number per source. You see 1, 2, 4. You load 4. What did you do to 3?

44.Sequence 3 never arrives. The source says it was a failed send and will not be retried. Your loader is still waiting. What closes the gap?

45.You convert currency at ingest using today's rate and throw away the original amount. A restatement next month uses a different rate and you cannot rebuild the original dollar figure. What did you drop?

46.Rates are looked up at query time, so yesterday's revenue changes when today's rate is corrected. Finance wants yesterday frozen. When do you apply the rate?

47.Null and zero both appear in a quantity field. The source says null means 'unknown' and zero means 'none.' Your load turns null into zero. What did analysts lose?

48.Booleans arrive as 'Y', 'N', 'y', 'true', '1', and ''. You map only 'true' to true and everything else to false. What happens to 'Y'?

49.Countries arrive as names, 'USA', 'United States', and 'US'. You store them as sent. A group-by shows three countries. What belongs in the warehouse?

50.A lookup table maps product codes to names. The load inner-joins to it and drops lines whose code is new. Revenue is short the new product. What join keeps the line?

51.The product name changes. You overwrite the dimension in place. Last year's orders now show this year's name. For a name that is a label, when is that fine, and when is it not?

52.You implement history by copying the whole dimension every day. Analysts who join on product id alone double-count orders. What should the fact point at?

53.Watermarks advance to the latest event time you have seen. A late event older than the watermark arrives, and a streaming job has already emitted the window. What does the watermark mean?

54.Event time is sometimes the client clock, which can be tomorrow when a phone is set wrong. The watermark jumps to tomorrow and closes today's window early. What do you bound?

55.You count distinct users in a stream with a set in memory. The set grows for a year and the job dies. The product accepts an approximate count. What structure replaces the set?

56.A join of clicks to users is done in a stream by keeping every user in memory. Users are huge and clicks are the stream. Which side is the broadcast?

57.You reprocess a stream job and write into the same output table as the live job, both appending. Counts double for the overlap. How do you publish a reprocess?

58.The job is 'exactly once' because the framework says so. The sink is an email API that sends on every call. A retry sends a second email. What is not exactly-once?

59.You test the loader with a sample of 100 rows and ship it. Production files have a nested array the sample lacked. The job throws on the first real file. What was the sample missing?

60.A producer promises files by 06:00. Your loader starts at 05:55 to be early and finds no file. It succeeds with zero rows. The 06:10 file is never loaded because success was already recorded. What should zero rows mean?

61.Downstream jobs use your success marker to start. You mark success at the start of the load. They read a half-loaded table. When do you mark it?

62.Two downstream jobs read the table, and one of them also needs a second source that is late. You have one success marker for the table. The second job starts too soon. What is the marker attached to?

63.Orchestration retries a failed task three times with no delay. The failure is a locked account at the source. You hammer it. What should the retry notice?

64.A source throttles you at 10 requests a second. Your backfill opens 100 parallel readers. The source starts returning errors and then blocks the account. What limit did you ignore?

65.You land data in a staging schema and then insert into the published schema. Analysts were granted access to staging because it was convenient. They build a dashboard on staging and it breaks when you truncate it. What did the grant imply?

66.A published table's column type changes from integer to string overnight. A dashboard query fails. Who was supposed to know before the morning?

67.You add a column and leave it null for old rows. A consumer's code selects * and binds by position. Their third field is now the new column. What did select * cost them?

68.The loader's service account can drop tables in the published schema. A bad deploy runs a drop left in a script. What permission was unnecessary?

69.Credentials for the source sit in the job definition in the scheduler UI, visible to everyone who can edit a job. What is a better place?

70.A load that used to take 20 minutes now takes two hours. The volume grew. You still run one statement that reads the whole history every time. What are you redoing?

71.Incremental loads use a timestamp column. Clocks on the source move backward by a minute during a correction, and rows stamped in that minute are never picked up again because the watermark already passed them. What do you overlap?

72.The source timestamp is updated on any column change, including a touch that does not matter to you. Your incremental job reloads half the table every hour. What watermark would move less?

73.You partition the raw table by day. A query for one customer over a year scans 365 partitions. The common query is by customer, not by day. What did you partition for?

74.Small files arrive all day, one per event, and the warehouse slows down because each query opens millions of files. What do you compact?

75.Compaction rewrites a partition while the loader appends to it. The compact job misses the new file or deletes it. How do you separate them?

76.You encrypt the raw bucket and leave the decryption key in the same bucket's readme. Who can read the data?

77.A column is marked sensitive and the load copies it into a debug table you forgot. The debug table is world-readable inside the company. What is the inventory missing?

78.You sample 1% of events for a sandbox. The sample is the first 1% of the file, which is one region, because the file is grouped. Analysts treat it as a random sample of the world. What is biased?

79.A sandbox copy is refreshed from production every night, including emails. Contractors have the sandbox. What should the refresh have removed?

80.The pipeline diagram shows extract, load, and transform as three boxes with no owner. A failure sits in the middle box and both teams say it is the other's. What is missing on the box?

81.You are on call. The load failed with a permissions error after a deploy. The on-call runbook says 'rerun the job.' You rerun and it fails the same way. What should the runbook have said first?

82.After the load, a row-count check compares today to yesterday and pages when today is half. A holiday is supposed to be half. You get paged. What should the check compare against?

83.The check pages you. You acknowledge and go back to sleep because this check pages every night. The night the source is actually empty, nobody looks. What did the noisy check cost?

84.You add a metric for rows loaded and forget a metric for rows quarantined. The loaded count looks steady because the bad rows never enter it. What number is missing?

85.A dashboard shows the pipeline green because the scheduler says the task succeeded. The task succeeded at writing zero rows after a upstream failure was swallowed. What is green measuring?

86.You are asked to ingest a spreadsheet a vice president updates by hand. There is no key. Rows move. What do you tell them you need before this is a feed?

87.The spreadsheet's header row is sometimes on line 1 and sometimes on line 3 under a title. Your mapper uses positions. Totals land in the name column. What should you map on?

88.A CSV field contains a comma inside quotes. A split on every comma shifts the columns. The row count still matches. Why did the count not save you?

89.The file is UTF-8 except for one legacy extract in a different encoding. You decode everything as UTF-8 and a few names become replacement characters. What do you record?

90.Line endings differ. One source uses a carriage return only, and your splitter looks for a newline. The whole file looks like one line and then fails a column check. What is the cheaper lesson?

91.You gunzip a file into a disk that is smaller than the uncompressed size. The load fails halfway and leaves a partial extract that the next step might read. What do you write first?

92.The same logical dataset is delivered as CSV, then as parquet, by two teams who did not talk. Column names differ by an underscore. You fork the loader. What would you rather have?

93.Parquet stores its own types. You also keep a JSON schema beside it. They disagree: the file says integer and the JSON says string. Which do you treat as the contract the producer broke?

94.A nested parquet field is an array of structs. You flatten it by taking the first element and dropping the rest. Orders with two shipments lose a shipment. When is flattening acceptable?

95.You explode an array into child rows and the parent total is repeated on each child. A sum of the total column multiplies revenue by the number of children. How do you publish the total?

96.The interview asks how you would replay a bad day. You say 'rerun the job.' The job deletes nothing and appends. What does the rerun do to the bad rows that are already there?

97.You overwrite the day's partition on replay. A late file for that day arrives after the replay and is loaded by the live job into the same partition with an append. The overwrite had removed the late-file path. What is the race?

98.A stakeholder asks for the pipeline to be 'real time.' Their report is reviewed once a morning. What latency do you actually need?

99.You chose a stream because the slide said real time. The consumer still writes hourly files. Operators now debug two systems for an hourly result. What would you cut?

100.The source offers a firehose and a cleaned feed. The cleaned feed drops events the source considers bots. Your fraud team wants the bots. Which feed do you land?

101.You finish an ingest design. The interviewer asks what you would do on the first morning the file does not arrive. You have described only the happy path. What is the answer?

102.A column of ids arrives with leading zeros. You store it as an integer and the zeros disappear. A later join to a string id fails. What type preserves the id?

103.Phone numbers are stored as integers. A number with a leading plus and a country code does not fit your assumption, and the plus is gone. What did the cast destroy?

104.You deduplicate with SELECT DISTINCT on every column. Two real events differ only by a timestamp in the milliseconds and both should remain. A second run collapses them because you trimmed the timestamp. What is the dedupe key?

105.The interviewer asks what you monitor after the pipeline has been quiet for a month. You say nothing, because quiet means healthy. What quiet can hide?