Answer Key — Data Lineage Interview Questions

(Keep this file separate from the candidate packet)

Q1 — View Column Aliasing

1. Base table and column: loyalty_tier is an alias for customers.customer_tier. From setup.sql:

c.customer_tier AS loyalty_tier

2. Direct base table query:

SELECT customer_tier
FROM customers
WHERE email = 'alice@example.com';

3. tier_discount origin: vw_customer_summary.tier_discount → customer_tier_rules.discount_pct The view joins customers to customer_tier_rules on customer_tier = tier_name and aliases r.discount_pct as tier_discount.

Q2 — Computed / Derived Column

1. How full_name is constructed:

CONCAT(c.first_name, ' ', c.last_name) AS full_name

It is a concatenation of two columns with a space literal. There is no full_name column in any base table.

2. Query to find rows where the view's value would differ: The view uses CONCAT, which returns NULL if any argument is NULL. A row with a NULL first_name or last_name would produce full_name = NULL in the view, while a custom expression might return a partial name.

SELECT customer_id, first_name, last_name,
       CONCAT(first_name, ' ', last_name) AS view_full_name
FROM customers
WHERE first_name IS NULL OR last_name IS NULL;

With the current sample data this returns no rows, but it's the right query.

3. NULL-safe alternative: CONCAT_WS(' ', first_name, last_name) — "Concat With Separator" skips NULL arguments rather than propagating NULL. For a customer with last_name = NULL it would return 'Alice' rather than NULL.

SELECT
    CONCAT(first_name, ' ', last_name)       AS current_behavior,
    CONCAT_WS(' ', first_name, last_name)    AS null_safe_behavior
FROM customers;

Q3 — Hunting a Column with `INFORMATION_SCHEMA`

1. Find all columns containing 'discount':

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'ecommerce_db'
  AND COLUMN_NAME LIKE '%discount%';

Expected results: orders.discount_amount, order_items (none), customer_tier_rules.discount_pct.

2. Extended with data type and nullability:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'ecommerce_db'
  AND COLUMN_NAME LIKE '%discount%'
ORDER BY TABLE_NAME, COLUMN_NAME;

3. Search view definitions:

SELECT TABLE_NAME AS view_name, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'ecommerce_db'
  AND VIEW_DEFINITION LIKE '%discount_pct%';

Returns vw_customer_summary and vw_high_value_customers.

Caveat: VIEW_DEFINITION in MySQL stores the parsed SQL, not the original DDL — whitespace and formatting may differ. Use SHOW CREATE VIEW vw_customer_summary\G for the original.

Q4 — Nested View Lineage

1 & 2. Full chain (2 hops):

vw_high_value_customers.tier_discount
  → vw_customer_summary.tier_discount        (vw_high_value_customers SELECTs cs.tier_discount)
    → customer_tier_rules.discount_pct       (vw_customer_summary aliases r.discount_pct AS tier_discount)

3. Programmatic discovery via INFORMATION_SCHEMA:

-- Step 1: find views that reference 'tier_discount'
SELECT TABLE_NAME AS view_name, SUBSTRING(VIEW_DEFINITION, 1, 500) AS definition_excerpt
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'ecommerce_db'
  AND VIEW_DEFINITION LIKE '%tier_discount%';

-- Step 2: get the full definition of each view in the chain
SHOW CREATE VIEW vw_high_value_customers;
SHOW CREATE VIEW vw_customer_summary;

The disciplined approach in a large system is to build a graph: for each view, extract what it SELECTs from, then recurse until you hit base tables only.

Q5 — Trigger-Populated Field

1. Explanation: line_total is computed by a BEFORE INSERT trigger on order_items. The application never needs to calculate or supply it — the database engine intercepts the INSERT, runs the trigger, and sets NEW.line_total = NEW.quantity * NEW.unit_price before writing the row.

2. Discovery query (MySQL):

SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING,
    ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'ecommerce_db'
  AND EVENT_OBJECT_TABLE = 'order_items';

Or, to see the full definition:

SHOW TRIGGERS FROM ecommerce_db LIKE 'order_items';
-- or
SHOW CREATE TRIGGER trg_order_items_before_insert\G

3. Expected line_total after the test insert: 3 * 9.99 = 29.97 The BEFORE INSERT trigger fires, sets NEW.line_total = 3 * 9.99, and that value is written to the row.

-- Verify
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 2, 3, 9.99);

SELECT item_id, quantity, unit_price, line_total
FROM order_items
ORDER BY item_id DESC
LIMIT 1;
-- Expected: line_total = 29.97

4. T-SQL (SQL Server) equivalent:

SELECT
    t.name            AS trigger_name,
    te.type_desc      AS event_type,
    OBJECT_NAME(t.parent_id) AS table_name,
    m.definition      AS trigger_body
FROM sys.triggers t
JOIN sys.trigger_events te ON t.object_id = te.object_id
JOIN sys.sql_modules m     ON t.object_id = m.object_id
WHERE OBJECT_NAME(t.parent_id) = 'order_items';

Q6 — Status Code Mapping

1. How to find order_status:

-- Look for a column named order_status anywhere in the database
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'ecommerce_db'
  AND COLUMN_NAME = 'order_status';
-- Returns no rows — it is not a stored column

-- Check views
SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'ecommerce_db'
  AND VIEW_DEFINITION LIKE '%order_status%';
-- Returns vw_orders_detail

2. Responsible view and transformation: vw_orders_detail uses a CASE expression on orders.status_code to produce order_status. The string 'Delivered' never exists in storage; it is generated at query time.

3. All valid codes and meanings:

`status_code`	`order_status`
`PE`	Pending
`CF`	Confirmed
`SH`	Shipped
`DL`	Delivered
`CN`	Cancelled
`RF`	Refunded
anything else	Unknown

4. Adding 'BO' (Backordered): Exactly one place: the CASE expression in vw_orders_detail. Recreate (or ALTER) the view:

CREATE OR REPLACE VIEW vw_orders_detail AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    CASE o.status_code
        WHEN 'PE' THEN 'Pending'
        WHEN 'CF' THEN 'Confirmed'
        WHEN 'SH' THEN 'Shipped'
        WHEN 'DL' THEN 'Delivered'
        WHEN 'CN' THEN 'Cancelled'
        WHEN 'RF' THEN 'Refunded'
        WHEN 'BO' THEN 'Backordered'   -- new
        ELSE            'Unknown'
    END                AS order_status,
    o.subtotal,
    o.discount_amount,
    o.total_amount     AS order_total,
    o.source_system
FROM orders o;

Strong follow-up: is this the right design? A reference table (order_status_codes) would let you add codes without touching view DDL.

Q7 — Stored Procedure + Staging Table

1. Finding the responsible procedure:

-- List all routines
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'ecommerce_db'
  AND ROUTINE_TYPE = 'PROCEDURE';

-- Search for routines that reference the staging table
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'ecommerce_db'
  AND ROUTINE_DEFINITION LIKE '%orders_staging%';
-- Returns: sp_process_staged_orders

Or: SHOW PROCEDURE STATUS WHERE Db = 'ecommerce_db'\G

2. Mapping raw_status = 'confirmed' → status_code: The procedure applies:

CASE LOWER(TRIM(s.raw_status))
    WHEN 'confirmed' THEN 'CF'
    ...
END

Result: orders.status_code = 'CF'

3. Data type risk with raw_total: raw_total is VARCHAR(50). The procedure casts it with CAST(s.raw_total AS DECIMAL(12,2)).

Risks:

Non-numeric values ('N/A', '149,98' with a comma as decimal separator) produce a warning and convert to 0 in MySQL — no error is raised by default.
Currency symbols ('$149.98') will also silently become 0.
In MySQL strict mode (STRICT_TRANS_TABLES), a bad cast raises an error and rolls back the row.

Best practice: validate raw_total before casting, or load into a staging column typed as DECIMAL.

4. Execute and verify:

-- Before
SELECT staging_id, raw_status, raw_customer_email, raw_total, processed
FROM orders_staging
WHERE processed = 0;

-- Run the load
CALL sp_process_staged_orders();

-- Verify orders were created
SELECT o.order_id, o.customer_id, o.status_code, o.total_amount, o.source_system, o.processed_by
FROM orders o
WHERE o.source_system = 'IMPORT';

-- Verify staging rows marked processed
SELECT staging_id, processed FROM orders_staging;

Carol's 'confirmed' row becomes status_code = 'CF', Bob's 'new' row becomes 'PE'.

Q8 — Multi-Path Field Modification

1. Every path that can write customer_tier:

Path	Trigger / Who	What drives the value
`trg_update_customer_tier` (AFTER INSERT on `orders`)	Automatic	`customer_tier_rules` lookup based on `lifetime_orders` count
`sp_set_customer_tier(customer_id, tier, operator)`	Manual call	`p_tier` parameter — admin override
Direct `UPDATE customers SET customer_tier = ...`	Ad hoc SQL	Whatever value the caller provides

2. Every path that can write last_modified_by:

Path	Value written	Why
`trg_update_customer_tier`	`'TRIGGER:trg_update_customer_tier'`	Hardcoded string in the trigger body
`trg_customers_audit` (BEFORE UPDATE)	`USER()` — e.g., `'root@localhost'`	Fires when `last_modified_by` was NOT explicitly changed by the caller
`sp_set_customer_tier`	`p_operator` parameter	Caller must supply an operator name
Direct UPDATE that also sets `last_modified_by`	Whatever value is passed	`trg_customers_audit` detects the change and leaves it alone
Direct UPDATE that does NOT set `last_modified_by`	`USER()` from `trg_customers_audit`	The BEFORE UPDATE trigger auto-fills it

3. Query and explain current state:

SELECT customer_id, email, customer_tier, lifetime_orders, last_modified_by
FROM customers;

Expected results after running setup.sql:

customer	customer_tier	lifetime_orders	last_modified_by
Alice	SILVER	5	TRIGGER:trg_update_customer_tier
Bob	SILVER	1	TRIGGER:trg_update_customer_tier
Carol	STANDARD	0	NULL

Alice: trigger fired 5 times; on the 5th order lifetime_orders = 5 which maps to SILVER.
Bob: trigger fired once; lifetime_orders = 1, still STANDARD... wait — Bob has 1 order only. After the staged orders are processed via sp_process_staged_orders, Bob gets a second order and reaches lifetime_orders = 2 (still STANDARD). Without running the proc, Bob has 1 order, customer_tier = STANDARD.
Carol: no orders → tier stays at default 'STANDARD', last_modified_by is NULL (never updated).

Interviewer note: After CALL sp_process_staged_orders(), Bob will have lifetime_orders = 2 (still STANDARD) and Carol will have lifetime_orders = 1 (still STANDARD). Their last_modified_by gets set to 'TRIGGER:trg_update_customer_tier' by the trigger.

4. last_modified_by = 'TRIGGER:trg_update_customer_tier': The most recent change to that customer row was caused by the automatic tier recalculation trigger, meaning a new order was inserted for that customer. No human or application code touched the row directly.

5. last_modified_by = 'root@localhost' (or similar USER() value): The row was updated by a direct SQL statement (ad hoc query or a code path that didn't set last_modified_by explicitly), and the trg_customers_audit trigger auto-stamped the database connection's user. This is less informative because many different operations share the same DB user — you know which connection user made the change but not which application feature or stored procedure triggered it.

Q9 — Ghost Writes: Finding Hidden INSERTs

1. Find all triggers in the database:

SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION   AS event,
    ACTION_TIMING        AS timing,
    EVENT_OBJECT_TABLE   AS fires_on,
    ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'ecommerce_db'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;

Alternatively: SHOW TRIGGERS FROM ecommerce_db\G

2. Responsible trigger and condition: trg_orders_status_change — AFTER UPDATE on orders.
Condition: OLD.status_code <> NEW.status_code
A row is written to order_status_log only when a status change actually occurs; updates to other columns do not produce log entries.

3. Demonstration:

-- Confirm current status
SELECT order_id, status_code FROM orders WHERE order_id = 1;

-- Trigger the log write
UPDATE orders SET status_code = 'CN' WHERE order_id = 1;

-- Verify
SELECT * FROM order_status_log WHERE order_id = 1;
-- Should show a row: old_status='DL', new_status='CN'

-- Confirm no log row for a non-status update
UPDATE orders SET processed_by = 'test' WHERE order_id = 1;
SELECT COUNT(*) FROM order_status_log WHERE order_id = 1;
-- Count unchanged

4. T-SQL (SQL Server) equivalent:

SELECT
    t.name                       AS trigger_name,
    te.type_desc                 AS event_type,
    OBJECT_NAME(t.parent_id)     AS fires_on,
    CASE t.is_instead_of_trigger
        WHEN 1 THEN 'INSTEAD OF'
        ELSE 'AFTER'
    END                          AS timing,
    m.definition                 AS trigger_body
FROM sys.triggers      t
JOIN sys.trigger_events te ON t.object_id = te.object_id
JOIN sys.sql_modules    m  ON t.object_id = m.object_id
WHERE t.parent_class = 1   -- table triggers only
ORDER BY OBJECT_NAME(t.parent_id), t.name;

Discussion Questions

D1 — Investigating processed_by: Start with INFORMATION_SCHEMA.ROUTINES (search for processed_by in body), INFORMATION_SCHEMA.TRIGGERS (same search), then INFORMATION_SCHEMA.VIEWS. For application code, grep the codebase for INSERT INTO orders and UPDATE orders. Check if an ORM is involved — ORM-generated SQL won't appear in stored procedures.

D2 — Verifying total_amount = subtotal - discount_amount:

SELECT
    order_id,
    subtotal,
    discount_amount,
    total_amount,
    (subtotal - discount_amount)  AS expected_total,
    (total_amount - (subtotal - discount_amount)) AS discrepancy
FROM orders
WHERE ABS(total_amount - (subtotal - discount_amount)) > 0.001;

If the result set is empty, the claim holds for all current rows. It does not prove it will always be true — look for triggers or procedures that set total_amount independently.

D3 — Gaps in order_status_log:

Records the who as USER() (connection user), not application-level user or operator ID.
Does not capture the reason for the status change.
Does not log initial status_code set at INSERT time — only transitions from one status to another.
Does not log changes to other sensitive fields (e.g., total_amount, discount_amount).
Rows are never deleted — the table will grow unbounded with no archival strategy.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Answer Key — Data Lineage Interview Questions

(Keep this file separate from the candidate packet)

Q1 — View Column Aliasing

Q2 — Computed / Derived Column

Q3 — Hunting a Column with `INFORMATION_SCHEMA`

Q4 — Nested View Lineage

Q5 — Trigger-Populated Field

Q6 — Status Code Mapping

Q7 — Stored Procedure + Staging Table

Q8 — Multi-Path Field Modification

Q9 — Ghost Writes: Finding Hidden INSERTs

Discussion Questions

FilesExpand file tree

answer_key.md

Latest commit

History

answer_key.md

File metadata and controls

Answer Key — Data Lineage Interview Questions

(Keep this file separate from the candidate packet)

Q1 — View Column Aliasing

Q2 — Computed / Derived Column

Q3 — Hunting a Column with INFORMATION_SCHEMA

Q4 — Nested View Lineage

Q5 — Trigger-Populated Field

Q6 — Status Code Mapping

Q7 — Stored Procedure + Staging Table

Q8 — Multi-Path Field Modification

Q9 — Ghost Writes: Finding Hidden INSERTs

Discussion Questions

Q3 — Hunting a Column with `INFORMATION_SCHEMA`