Skip to content

questions.md

Latest commit

 

History

History
119 lines (75 loc) · 6.61 KB

questions.md

File metadata and controls

119 lines (75 loc) · 6.61 KB
Raw

Data Lineage Interview Questions

Senior Data Engineer — Field Origin & Tracing

Setup: Run setup.sql first. All questions reference the ecommerce_db database.

Difficulty scale: [E] Easy · [M] Medium · [H] Hard

Q1 — View Column Aliasing [E]

A BI report queries vw_customer_summary and returns a column called loyalty_tier.

  1. Which base table and column does loyalty_tier actually map to?
  2. Write a query that retrieves the same value directly from the base table for customer alice@example.com.
  3. Bonus: What column does tier_discount come from, and which table owns it?

Q2 — Computed / Derived Column [E]

The vw_customer_summary view exposes a column called full_name.

  1. How is full_name constructed? There is no full_name column anywhere in the base tables — explain where the value comes from.
  2. Write a query to find any customer rows where full_name as returned by the view would not equal a simple first_name || ' ' || last_name concatenation.
  3. What MySQL function could you use instead of CONCAT to handle the NULL case gracefully, and how would it change the output?

Q3 — Hunting a Column with INFORMATION_SCHEMA [M]

You've inherited a codebase and someone mentions "the discount_pct column." You don't know which table it lives in.

  1. Write a query against INFORMATION_SCHEMA that lists every table and column in ecommerce_db whose name contains the string discount.
  2. Extend that query to also show the column's data type and whether it allows NULLs.
  3. Write a second query that searches view definitions in INFORMATION_SCHEMA for any view that references the string discount_pct.

Q4 — Nested View Lineage [M]

The view vw_high_value_customers exposes a column called tier_discount.

  1. Trace tier_discount all the way back to the physical base table and original column name. How many hops does it take?
  2. Write the chain as: vw_high_value_customers.tier_discount → ??? → table.column.
  3. Write a single query using INFORMATION_SCHEMA.VIEWS to retrieve the definition of each view in the chain and identify the hops programmatically (rather than reading the DDL by hand).

Q5 — Trigger-Populated Field [M]

You're reviewing the application code and notice that every INSERT into order_items omits the line_total column entirely — yet when you SELECT * FROM order_items, every row has a non-NULL line_total.

  1. Explain how this is possible. Where is line_total being set?
  2. Write the SQL you would run to discover the trigger(s) responsible — without looking at the application code or knowing the trigger name in advance.
  3. Verify by running: 
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 2, 3, 9.99);
    What do you expect line_total to be? Why?
  4. T-SQL note: Write the equivalent metadata query for SQL Server instead of MySQL.

Q6 — Status Code Mapping [E/M]

A downstream report shows order_status = 'Delivered' for some orders. You search every table in the database and find no column named order_status and no row containing the string 'Delivered'.

  1. How would you find where order_status comes from?
  2. Identify the view responsible and explain the transformation it applies.
  3. The orders table stores status_code. List all the valid codes and their human-readable meanings as defined in this database.
  4. A new status 'BO' (Backordered) needs to be added. What exactly needs to change, and in how many places?

Q7 — Stored Procedure + Staging Table [M/H]

Orders with source_system = 'IMPORT' appear in the orders table, but no application INSERT directly into orders for those rows. The orders_staging table holds raw data from an external feed.

  1. How would you find the stored procedure responsible for moving data from orders_staging into orders?
  2. The external feed sends raw_status = 'confirmed'. What value ends up in orders.status_code? Trace the mapping.
  3. The external feed sends raw_total = '149.98' as a VARCHAR. What happens to the data type during the load? Is there a risk here?
  4. Carol has a row in orders_staging with raw_status = 'confirmed'. Write the call to execute the load procedure and then verify the result.

Q8 — Multi-Path Field Modification [H]

Focus on the customers table, specifically the columns customer_tier and last_modified_by.

  1. Enumerate every code path (triggers, procedures, direct SQL) through which customer_tier can be changed. For each path, describe what drives the change.
  2. Do the same for last_modified_by. Notice that different paths stamp different values — describe each one.
  3. After running the setup script, query customers to see the current values of customer_tier, lifetime_orders, and last_modified_by for all three customers. Explain why each customer has the values they do.
  4. You find a row where last_modified_by = 'TRIGGER:trg_update_customer_tier'. What does that tell you about the most recent action that modified that customer?
  5. You find a row where last_modified_by = 'root@localhost' (or your connection's USER()). What does that tell you — and why might it be less informative than the trigger value?

Q9 — Ghost Writes: Finding Hidden INSERTs [M]

Rows are appearing in order_status_log but nobody can find any INSERT INTO order_status_log in the application code or stored procedures.

  1. Write the query to find all triggers in ecommerce_db and show their event, timing, and the table they fire on.
  2. Identify which trigger is writing to order_status_log. What condition must be true for a row to be written?
  3. Demonstrate the trigger by running an UPDATE that changes status_code on an order, and then verify the log entry was created.
  4. How would you write this same discovery query in T-SQL (SQL Server)?

Exploratory / Discussion Questions

These don't have SQL answers but are worth thinking through:

  • D1: You're given a column processed_by in the orders table. It's sometimes NULL, sometimes 'sp_process_staged_orders', and sometimes an employee ID. How would you approach understanding all the paths that can write to this field in a large production system you've never seen before?

  • D2: A colleague says "the total_amount column is always subtotal - discount_amount." How would you verify or disprove this claim? Write the query.

  • D3: You need to understand data lineage for an audit. order_status_log tracks status changes — but what doesn't it track? What gaps would you flag to the engineering team?