Question 1

Why can a JOIN silently inflate your totals?

Accepted Answer

Joining two child tables of the same parent multiplies rows. One order line joined to two payment rows becomes two rows, so revenue doubles. State the grain of every table and aggregate to a shared grain before joining.

Question 2

How do you make a data load all-or-nothing?

Accepted Answer

Write the facts, the history and the watermark inside one transaction. If anything fails before commit, everything rolls back, so the next run starts from a consistent state and never skips data.

Question 3

Why is a Spark shuffle expensive?

Accepted Answer

Wide operations such as groupBy and join must move every row with the same key to the same partition. That means writing to disk and sending data across the network. Broadcast small tables, filter early and partition sensibly to avoid it.

Question 4

How does SCD Type 2 keep history?

Accepted Answer

When a tracked attribute changes, close the current row with an end date and insert a new current row. Facts keep the surrogate key they were loaded with, so old reports stay correct.

Question 5

How does an LLM generate text?

Accepted Answer

It predicts one token at a time. Each step scores every token in the vocabulary, samples one according to the temperature, appends it to the context and repeats until a stop token or the length limit.

Question 6

What makes an AI agent different from a chatbot?

Accepted Answer

An agent runs a loop: it reasons about the goal, calls a tool, observes the result and decides the next step until the task is done. A chatbot only answers; an agent acts.

Question 7

What is the grain of a table, and why define it first?

Accepted Answer

The grain is what one row represents, for example one order line per order. Define it before choosing keys, joins or measures. Mixing grains in one table, such as order totals beside line amounts, leads to double counting as soon as someone sums a column.

Question 8

Star schema or snowflake schema — which do you choose?

Accepted Answer

A star schema keeps each dimension in one wide, denormalized table, so queries need fewer joins and BI tools stay fast and simple. A snowflake schema normalizes dimensions into sub-tables, which saves some storage and avoids repeated attributes but adds joins. Most analytics warehouses default to a star and normalize only very large or shared hierarchies.

Question 9

What makes a data pipeline idempotent?

Accepted Answer

Running it twice with the same input leaves the same result as running it once. Achieve it with deterministic outputs per run (overwrite a partition, or MERGE on a business key) instead of blind appends, and record which inputs were processed. Idempotency is what makes retries and backfills safe.

Question 10

ETL or ELT — what changed with cloud warehouses?

Accepted Answer

ETL transforms data before loading it, which suited expensive, fixed-capacity warehouses. Cloud warehouses separate storage from compute, so ELT loads raw data first and transforms it inside the warehouse with SQL. ELT keeps raw history for reprocessing, but you still need to govern cost and quality of in-warehouse transformations.

Question 11

How do you handle late-arriving data in a batch pipeline?

Accepted Answer

Decide on event time versus processing time, then reprocess a lookback window, such as the last three days of partitions, on every run. Use MERGE or partition overwrite so reprocessing stays idempotent. For facts that arrive before their dimension, load a placeholder member and update it when the dimension lands.

Question 12

When does a broadcast join help, and when does it hurt?

Accepted Answer

Broadcasting copies a small table to every executor, so the large table is joined in place without a shuffle. It helps when one side comfortably fits in executor memory. It hurts when the broadcast side is larger than expected: the driver and executors run out of memory, so check sizes rather than forcing broadcasts everywhere.

Question 13

How do you detect and fix data skew in Spark?

Accepted Answer

Skew shows up in the Spark UI as a few tasks taking far longer than the median in one stage. Fixes include enabling adaptive query execution's skew-join handling, broadcasting the smaller side, filtering hot keys out for separate processing, or salting the key to spread one heavy key across partitions.

Question 14

Why declare schemas explicitly instead of inferring them?

Accepted Answer

Inference scans data to guess types, which costs time and can guess differently from one batch to the next, for example reading an ID as an integer one day and a string the next. An explicit schema makes types a contract: bad records fail loudly or go to a quarantine path instead of silently changing downstream tables.

Question 15

What does Delta Lake add on top of Parquet files?

Accepted Answer

Parquet stores columns efficiently but has no notion of a transaction. Delta Lake adds a transaction log, giving ACID commits, schema enforcement, MERGE, time travel to earlier versions and safe concurrent writers. Readers see either the whole commit or none of it.

Question 16

How does Snowflake COPY INTO avoid loading a file twice?

Accepted Answer

Snowflake keeps load metadata for each table that records which staged files were loaded, for 64 days. COPY INTO skips files already in that metadata unless you set FORCE = TRUE. Stable file names and validation before loading make reruns safe.

Question 17

Batch or streaming — how do you choose?

Accepted Answer

Start from the latency the business actually needs, then weigh cost and operational complexity. Hourly or daily decisions rarely justify streaming; fraud checks and live inventory often do. Streaming adds state, ordering and replay concerns, so choose it only when fresher data changes a decision.

Question 18

How do you keep only the latest record per key in SQL?

Accepted Answer

Number rows per key with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) and keep row number 1. Add a deterministic tie-breaker, such as an ingestion ID, so equal timestamps don't pick a random winner between runs.

Question 19

Airflow or Step Functions for orchestration?

Accepted Answer

Airflow suits code-first DAGs across many systems, rich scheduling and backfills, but you operate it or pay for a managed service. Step Functions is serverless and integrates tightly with AWS services, with per-transition pricing and visual state machines. Choose by where the work runs and who operates the scheduler.

Question 20

What is RAG, and when is it better than fine-tuning?

Accepted Answer

Retrieval-augmented generation fetches relevant documents at question time and puts them in the prompt, so answers can cite current, private sources. It suits knowledge that changes or must be traceable. Fine-tuning changes the model's behaviour, style or format; it is a poor way to add facts that change often.

Question 21

How do you choose a chunk size for retrieval?

Accepted Answer

Chunks should hold one self-contained idea: too small and they lose context, too large and they dilute the embedding and waste prompt space. Start around a few hundred tokens with some overlap, split on document structure such as headings, then measure retrieval quality on real questions and adjust.

Question 22

What is LoRA, and why is it cheaper than full fine-tuning?

Accepted Answer

LoRA freezes the base model and trains small low-rank matrices added to selected layers. Only a tiny fraction of parameters is trained, so memory, compute and storage drop sharply, and adapters can be swapped per task. QLoRA goes further by keeping the frozen base model in 4-bit precision.

Question 23

How do you evaluate a RAG system?

Accepted Answer

Evaluate retrieval and generation separately. For retrieval, check whether the right passages appear in the top results for a labelled question set. For generation, check faithfulness to the retrieved text and answer relevance, using human review or carefully validated model-based grading, and track both as the corpus changes.

Question 24

What is the Model Context Protocol (MCP)?

Accepted Answer

MCP is an open protocol that lets AI applications connect to tools and data through a standard client–server interface. A server exposes tools, resources and prompts once, and any compatible client can use them, instead of writing a custom integration for every model and app pair.

Question 25

How should an agent call tools safely?

Accepted Answer

Give each tool a narrow, typed input schema and the least privilege it needs. Validate arguments before execution, require confirmation for irreversible actions, cap steps, time and spend, and log every call. Treat tool output as untrusted input, because retrieved content can contain instructions.

Question 26

Why do LLMs hallucinate, and how do you reduce it?

Accepted Answer

A model generates the most plausible next tokens, not verified facts, so it can produce fluent but wrong statements when its context lacks the answer. Reduce it by grounding answers in retrieved sources, asking for citations, allowing the model to say it does not know, and checking high-stakes outputs against a system of record.