SQL and Data Warehouse Operations

• Navigate data warehouse schemas using INFORMATION_SCHEMA, dbt docs, and data dictionaries to identify key tables, grain, and relationships Apply
• Write complex SQL queries using CTEs, window functions (ROW_NUMBER, LAG, LEAD, RANK), and multi-table JOINs with correct join type selection based on business logic Apply
• Analyze query execution plans using EXPLAIN to identify full table scans, missing filters, and inefficient join strategies before running expensive queries Analyze
• Evaluate warehouse compute costs and implement safeguards — session timeouts, query size limits, and warehouse scaling policies — to prevent runaway spend Evaluate
• Construct dbt models following the staging/intermediate/marts layering pattern, including source definitions, model documentation, and ref() dependencies Create
• Implement dbt tests (not_null, unique, accepted_values, relationships) on data models to guarantee data quality for downstream consumers Apply
• Distinguish between production databases, read replicas, and data warehouses, articulating the risk and appropriate use case for each Understand
• Write reusable, documented SQL following team style guides: consistent formatting, meaningful CTEs, business-logic comments, and explicit column selection Apply

Levels: Remember · Understand · Apply · Analyze · Evaluate · Create — highest demands most original thinking.