Reusing a CTE Across Multiple Branches

Concepts covered: sqlCte

The assertion CTE pattern Five CTEs. monthly_gross and monthly_refunds compute the aggregates. net_revenue joins them. quality_violations is the gate: any (segment, month) with negative net revenue is a violation. The final SELECT returns the violations. If the data is clean, the result set is empty. If there are violations, the result set is non-empty and the consumer can inspect each one. This is the same pattern dbt uses for its 'singular tests': write a SELECT that returns rows representing violations, then assert the result is empty. Two consumption modes for the violations CTE Mode one: human inspection. The final SELECT returns the violations; an engineer reads them and decides whether to fix the data or the query. Mode two: pipeline gate. The query is wrapped in a check (Airflow se

About This Interactive Section

This section is part of the CTEs (Common Table Expressions): Intermediate lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.