SQL Data Lineage Interview Questions

Generation prompt:

my friend is interviewing at a company and asked for help reviewing the following style of question: a lot of what you do trying to figure out what a field in a database is actually coming from

You're a senior data engineer and I'd like you to prepare a test set of questions related to the prompt for mysql and have an answer key in a separate file. Ideally, you'd create interactive examples that could be used with the questions to query against or use to get the answers. The language will probably be MySQL or T-SQL

---

Contents

File	Description
setup.sql	Full database setup — DDL, views, triggers, stored procedures, and sample data
questions.md	9 interview questions (Easy → Hard) + 3 discussion questions
answer_key.md	Detailed answers with runnable SQL, explanations, and MySQL/T-SQL notes

Setup

mysql -u root -p < setup.sql

Or paste setup.sql directly into MySQL Workbench, DBeaver, or any MySQL 8.0+ client.

Schema Overview

The ecommerce_db database contains an intentionally layered e-commerce schema designed to surface realistic lineage puzzles:

• Base tables: customers, orders, order_items, products, customer_tier_rules, orders_staging, order_status_log
• Views: vw_customer_summary (column renaming + join), vw_high_value_customers (nested view), vw_orders_detail (CASE-based status mapping)
• Triggers: auto-compute line_total, auto-update customer tier on new orders, audit last_modified_by, log status changes
• Stored procedures: sp_process_staged_orders (ETL from staging), sp_set_customer_tier (admin override)

Skills Tested

• Tracing column aliases through views (including nested views)
• Using INFORMATION_SCHEMA to discover columns, views, triggers, and procedures
• Understanding trigger-populated vs. application-populated fields
• Reading stored procedure logic and data type transformations
• Identifying all write paths to a single column (triggers + procs + ad hoc SQL)
• MySQL vs. T-SQL metadata query equivalents