Capital One data interview pack

The Capital One loop

1. SQL + warehouse

   MERGE with a late CDC event. Masking vs row access. Resource monitors.

2. Controls

   Who can UNDROP prod. Why ACCOUNTADMIN is not a dashboard role.

SQL (4)

1. medium · Recent signal, 2026-08-10

   Revenue doubled after a “harmless” dimension join. How do you prove it in five minutes?

   Row counts before and after the join, then GROUP BY the join key HAVING count(*) > 1 on the dimension. Fix the grain or aggregate the dim. Never DISTINCT the fact.

2. medium · SQL

   A join exploded revenue. How do you find the grain mismatch in five minutes?

   Count rows before and after the join. If the fact grew, the “unique” key on the dimension is not unique at the grain you joined. Check duplicates with GROUP BY key HAVING count(*) > 1, then decide: aggregate the dimension or pick the correct role-playing key. Never “fix” it with DISTINCT on the fact.

3. easy · SQL

   EXISTS vs IN for an anti-join. Which do you write first?

   NOT EXISTS (or LEFT JOIN … IS NULL). NOT IN dies on NULLs. That is the answer they want.

4. hard · SQL

   PERCENTILE_CONT vs PERCENTILE_DISC. When does a hiring manager care?

   CONT interpolates; DISC returns an actual row value. Finance medians on sparse amounts should say which. “The median” without the function is a junior tell.

Data modeling (4)

1. hard · dbt

   A unique test failed on fct_orders. What is your first query?

   Select the key, count(*), min/max loaded_at grouped by the key having count > 1. Decide: source duplicate, fan-out join, or a timezone that split one order into two days. Do not --full-refresh and hope. Fix the grain, then re-run the test.

2. hard · dbt

   Two marts both named fct_revenue. How did that happen, and how do you kill one?

   No semantic owner. Freeze both, diff grain and filters with finance, pick a survivor, deprecate the other with a failing test if it is still queried. Renaming in place without a sunset is how Slack fills with “which revenue.”

3. medium · Python & modeling

   Type 1 vs Type 2 SCD — two sentences.

   Type 1 overwrites the attribute; no history. Type 2 inserts a new row with valid_from / valid_to (and usually is_current) so a fact can join the dimension as it looked on the event date. If they ask Type 3, that is “limited history in extra columns.”

4. hard · Python & modeling

   You hashed emails as a join key. Legal says stop. What was the modeling mistake?

   A hash of PII is still PII if it is reversible or unique. Use a vault token from a real identity service, or a surrogate that is not derived from the email. Hashing is not anonymization.

Orchestration (4)

1. medium · Airflow

   Pools, priority_weight, and a Monday-morning stampede. Design the guardrail.

   Give expensive warehouse tasks a small pool. Separate extract vs transform pools. Do not let 80 backfills starve the 8:00 SLA DAG. priority_weight is a hint; pools are the hard cap. Say this as an on-call story, not a docs recitation.

2. easy · Airflow

   catchup=False forever. What history did you silently skip?

   Every interval between start_date and now when the DAG was paused or deployed late. That is fine for “from now on” jobs; it is a hole for daily finance. Say it in the design review.

3. medium · Dagster

   When do you keep Airflow instead of moving to Dagster?

   A large heterogeneous ops estate, vendor sensors, and a team already staffed on Airflow. Move when the DAG is secretly a data graph and assets would delete half the glue. Fashion is not a reason.

4. easy · Dagster

   Resources and IO managers: where do warehouse credentials live?

   In a Dagster resource / secret backend, not in the asset body. The IO manager writes parquet/Snowflake; the asset function should not open a raw connection with a hardcoded URL.

Streaming (1)

1. easy · Apache Flink

   Exactly-once into a warehouse. What do you still need?

   A transactional or idempotent sink and a business key. Checkpoint barriers without a sink contract are at-least-once in practice. Dual-write without MERGE is a duplicate table.

Warehouse (9)

1. medium · Snowflake

   A Medium warehouse is slower but cheaper than Large. When is that true?

   Size doubles credits per hour. If Medium takes only a little longer, total credits drop. Single-threaded SQL and tiny result sets will not scale with size — you just pay more for the same clock. Test one size down on a real job and compare credits, not elapsed time alone.

2. medium · Snowflake

   Search Optimization vs a clustering key — one sentence each.

   Clustering helps range/filter scans on poorly ordered columns. Search Optimization helps highly selective point lookups. They cost extra credits. Do not buy both “just in case.”

3. medium · Snowflake

   SnowPro Specialty: Gen AI — where does COMPLETE belong?

   In incremental ELT that writes a versioned column. Not in a Looker explore, not on every dashboard refresh, and not on raw PII.

4. medium · Snowflake

   Auto-suspend 60s and a dashboard that ticks every 90s. What happens to the bill?

   The warehouse resumes almost every tick. You pay the resume (and the minimum billed window) all day. Either lengthen suspend, use a dedicated XS that stays warm, or materialize the tile.

   Hint: Resume cost, not bytes scanned.

5. hard · Snowflake

   ACCOUNTADMIN ran a Cortex call on a PII column. What was missing besides “don’t”?

   A masking policy that still applies to functions, a dedicated role for LLM jobs, and an allow-list of columns. Cortex is another consumer of the column, not a bypass.

6. easy · Databricks

   Unity Catalog volume vs a DBFS path in a notebook. What do you refuse to merge?

   Hard-coded dbfs:/ in production jobs. Volumes + UC grants are the paved road. DBFS mounts with a personal token are how you leak prod.

7. medium · BigQuery

   Authorized view in project analytics_prod, source in project raw. What must the viewer role not get?

   Direct SELECT on raw. The view’s owner needs source access; the analyst gets the view only. Copying the table into their project is how PII spreads.

8. easy · Trino

   When is federation the wrong design?

   When you need a stable SLA, cost attribution, or joins that hammer three systems. Land a table. Federation is for exploration and for sources you refuse to copy — not for the 9:00 exec dashboard.

9. easy · PostgreSQL

   Can Trino or the warehouse query the primary?

   Not for BI. Replica or CDC copy. A federated scan of prod is an outage. Say this even if they did not ask.

Cloud (5)

1. medium · AWS

   Glue job vs Lambda vs EMR. Who owns a 40-minute Spark transform?

   Lambda is for minutes and megabytes, not Spark. Glue is managed Spark for batch ETL you do not want to babysit. EMR (or EKS + Spark) when you need custom images, long-running clusters, or cheaper reserved capacity. Say the timeout and the data size first.

2. medium · AWS

   Cost tags on Glue/Athena/Redshift. Why do interviewers ask?

   Untagged shared resources become a mystery bill. You should be able to say which DAG spent the credits.

3. medium · AWS

   Athena query billed 1.2 TB. The table is partitioned by day. First three checks?

   Did the query filter the partition column (not DATE(ts) wrapping)? Are there thousands of tiny files? Is SELECT * pulling 400 columns? Then look at partition projection vs Glue partitions that never got added.

4. hard · AWS

   Lake Formation LF-Tags vs bucket policy. When is LF worth the ticket?

   When many accounts/roles need column- and table-level grants on a shared catalog. A single team with two buckets can stay on IAM + bucket policy. LF is not a substitute for not putting PII in the key name.

5. easy · Azure

   Key Vault references vs a secret in an ADF global parameter. What do you say in a bank interview?

   Key Vault (or Databricks secrets). Parameters end up in JSON, git, and ARM exports. Rotation is the test: can you rotate without editing 40 pipelines?

System design (5)

1. medium · System design

   A late dimension change must not rewrite last month’s facts. Pattern?

   Type-2 dimension with valid_from/valid_to. Facts join as-of the event timestamp. Type-1 overwrite changes history. Say which product wants.

2. hard · System design

   PII in bronze, marketing wants emails. Draw the control plane.

   Separate raw PII, tokenize or mask in silver, grant only the columns they are allowed, audit the extract. Slack SELECT * from bronze is the incident.

3. easy · System design

   Disaster recovery for the lake. What is the actual runbook?

   Cross-region replica or object-lock, a metastore backup, and a restore drill. “S3 is durable” is not a runbook. Know RPO/RTO in hours, not adjectives.

4. easy · System design

   Cost attribution: five teams, one Snowflake account. Minimum viable design?

   One warehouse (or tag) per team, resource monitors, and QUERY_HISTORY grouped by warehouse/role. Shared XS for ad-hoc with a tight auto-suspend. Without tags, FinOps is a spreadsheet of villains.

5. medium · System design

   PII in bronze, marketing wants a campaign table with emails. Controls you actually draw.

   Separate schema, masking/row policies, approved join to a consent table, no SELECT * grants, and an access log. A hashed email is still often PII. Marketing gets a view, not bronze.

Data quality (2)

1. medium · Data quality

   Fail the pipeline or open a ticket?

   Fail on grain, uniqueness, and PII contract breaks. Ticket on a soft anomaly you cannot yet explain. Write the policy down. “It depends” without a rule is a fail.

2. easy · Data quality

   Four checks on every gold table before you argue about anomaly ML.

   unique on the grain, not_null on keys and amounts, a row-count vs source reconciliation, and a freshness/SLA timestamp. Anomaly tools sit on top of those, they do not replace them.

AI / ML (3)

1. medium · AI / RAG / MLOps

   ACL-aware retrieval. What breaks if you embed docs and forget permissions?

   The index will retrieve a document the caller cannot see. Filter on ACL metadata at query time (or partition indexes by tenant). “We will prompt the model not to reveal it” is not a control. This question shows up at every bank and healthcare loop.

2. easy · AI / RAG / MLOps

   PII in prompts. How do you not leak the warehouse into the vendor?

   Redact or tokenize before the call, keep a deny-list, and do not paste raw rows into a chat UI. Contracts and DLP, not hope.

3. medium · AI / RAG / MLOps

   Eval for a classifier in the warehouse vs a Python job. What must both have?

   A labeled holdout that was not used to tune, a threshold policy, and a timestamp. Warehouse UDF metrics are fine; leaking the holdout through a JOIN is not. Say the positive class rate.

Behavioral (6)

1. medium · Behavioral

   Tell me about a pipeline that failed in production. What did you do in the first hour?

   Name the SLO you missed (freshness, volume, or grain), how you found it (Datadog, row-count check, a Slack from BI), and the first safe move — stop writers, clone or time-travel, do not reload on a live table. Then the permanent fix: a test at that grain, an owner, and a page that is not “job succeeded.” Interviewers want the clock and the control, not a hero story.

   Hint: Lead with the SLO you missed, then the first safe move.

2. hard · Behavioral

   Warehouse cost doubled and leadership asked you in a meeting. Walk the room through it.

   Do not guess. Query history, warehouse resume count, a clustering job, a Cortex or BI refresh loop. Show credits by warehouse and by user, then one change you made (auto-suspend, a smaller size, materialize the explore). Chargeback tags if you have them. “We will migrate to Spark” without a number is a fail.

3. hard · Behavioral

   Someone queried bronze PII from Slack. What did you do that week?

   Revoke, audit the extract, tell security, then the control: separate PII, column grants, no SELECT * on bronze, and a test that fails if an email lands in a marketing mart. “I asked them not to do it again” is not a control. Banks and healthcare loops will push until you name the grant.

4. medium · Behavioral

   Tell me about a time you shipped a dashboard that was wrong. What did you do in public?

   STAR: situation (wrong grain), task (stop the blast), action (revert pointer, post the invariant, add a unique test), result (hours of bad numbers, then a written owner). Do not skip who you told. Hiding it is the fail.

   Hint: Name the grain bug and the test you added.

5. hard · Behavioral

   Finance says your number does not match the ERP. Walk the first meeting.

   Do not defend the dashboard first. Align grain (invoice vs cash), timezone, and filters. Then reconcile a single day in SQL together. Bring the query, not a feeling. Offer a bridge table if both definitions must live.

6. medium · Behavioral

   How do you say no to ACCOUNTADMIN for an analyst “just this once”?

   Give a timed break-glass with logging, or a narrower role that unblocks the task. Explain blast radius (UNDROP, shares, billing). If leadership overrules, write the exception. Permanent ACCOUNTADMIN is how incidents become existential.

SQL coding labs

Problem statements only. Run them against tests on DataLane; solutions live in the pad.

1. medium · about 10 min · tables: orders, payments

   Orders with no payment

   Every order should have a payment row. Find the ones that do not. This is the anti-join interviewers use to see whether you reach for NOT IN, which silently returns nothing when the subquery contains a NULL.

   https://www.datalane-data.blog/coding/problems/orders-without-payment/

2. hard · about 18 min · tables: orders

   Median completed order amount

   Report the median completed order amount without a percentile function. The median is the middle value for an odd count and the average of the two middle values for an even count, so the solution has to handle both.

   https://www.datalane-data.blog/coding/problems/median-order-amount/

3. hard · about 14 min · tables: orders, payments

   Reconcile orders against payments

   A revenue report and a settlement report disagree. Find the orders where the paid amount does not match the order amount — this is the reconciliation check that belongs in a data quality test, not a dashboard.

   https://www.datalane-data.blog/coding/problems/orders-vs-payments-mismatch/