DataBurst Data Engineering Wiki - Roadmap Step-by-Step: https://wiki.databurst.tech/docs/roadmap/ - Introduction: https://wiki.databurst.tech/docs/roadmap/introduction/ - Programming Language: https://wiki.databurst.tech/docs/roadmap/programming-language/ - Python: https://wiki.databurst.tech/docs/roadmap/programming-language/python/ - Java: https://wiki.databurst.tech/docs/roadmap/programming-language/java/ - Scala: https://wiki.databurst.tech/docs/roadmap/programming-language/scala/ - Go: https://wiki.databurst.tech/docs/roadmap/programming-language/go/ - Rust: https://wiki.databurst.tech/docs/roadmap/programming-language/rust/ - Operating System: https://wiki.databurst.tech/docs/roadmap/operating-system/ - Windows: https://wiki.databurst.tech/docs/roadmap/operating-system/windows/ - Linux: https://wiki.databurst.tech/docs/roadmap/operating-system/linux/ - Networking: https://wiki.databurst.tech/docs/roadmap/networking/ - Protocols: https://wiki.databurst.tech/docs/roadmap/networking/protocols/ - TCP/IP: https://wiki.databurst.tech/docs/roadmap/networking/protocols/tcp/ - DNS: https://wiki.databurst.tech/docs/roadmap/networking/protocols/dns/ - HTTP: https://wiki.databurst.tech/docs/roadmap/networking/protocols/http/ - HTTPS: https://wiki.databurst.tech/docs/roadmap/networking/protocols/https/ - SSH: https://wiki.databurst.tech/docs/roadmap/networking/protocols/ssh/ - UDP: https://wiki.databurst.tech/docs/roadmap/networking/protocols/udp/ - Concepts: https://wiki.databurst.tech/docs/roadmap/networking/concepts/ - IP addressing: https://wiki.databurst.tech/docs/roadmap/networking/concepts/ip-addressing/ - DHCP: https://wiki.databurst.tech/docs/roadmap/networking/concepts/dhcp/ - Firewalls: https://wiki.databurst.tech/docs/roadmap/networking/concepts/firewalls/ - VPN: https://wiki.databurst.tech/docs/roadmap/networking/concepts/vpn/ - General Software Engineering Skills: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/ - OAuth: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/oauth/ - JWT: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/jwt/ - GraphQL: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/graphql/ - REST: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/rest/ - gRPC: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/grpc/ - Caching: https://wiki.databurst.tech/docs/roadmap/general-software-engineering-skills/caching/ - Web Frameworks and API Development: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/ - Python: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/python/ - Go: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/go/ - Java: https://wiki.databurst.tech/docs/roadmap/web-frameworks-and-api-development/java/ - Version Control System: https://wiki.databurst.tech/docs/roadmap/version-control-system/ - git: https://wiki.databurst.tech/docs/roadmap/version-control-system/git/ - Version Control System Hosting: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/ - Github: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/github/ - Gitlab: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/gitlab/ - BitBucket: https://wiki.databurst.tech/docs/roadmap/version-control-system-hosting/bitbucket/ - Distributed Systems Concepts: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/ - CAP: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/cap/ - PACELC: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/pacelc/ - Sharding: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/sharding/ - Clock: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/clock/ - ACID: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/acid/ - Replication: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/replication/ - Distributed Transactions: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/transaction/ - Indexing: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/indexing/ - Batch and Stream Processing: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/batch-and-stream-processing/ - Consistency vs Eventual Consistency: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/consistency-vs-eventual-consistency/ - Normalization or Denormalization: https://wiki.databurst.tech/docs/roadmap/distributed-systems-concepts/normalization-or-denormalization/ - SQL Fundamentals: https://wiki.databurst.tech/docs/roadmap/sql-fundamentals/ - Basic SQL: https://wiki.databurst.tech/docs/roadmap/sql-fundamentals/basic-sql/ - Advanced SQL: https://wiki.databurst.tech/docs/roadmap/sql-fundamentals/advanced-sql/ - Databases: https://wiki.databurst.tech/docs/roadmap/databases/ - Relational: https://wiki.databurst.tech/docs/roadmap/databases/relational/ - NoSQL: https://wiki.databurst.tech/docs/roadmap/databases/nosql/ - Columnar: https://wiki.databurst.tech/docs/roadmap/databases/nosql/columnar/ - Document Based: https://wiki.databurst.tech/docs/roadmap/databases/nosql/document-based/ - Key-Value: https://wiki.databurst.tech/docs/roadmap/databases/nosql/key-value/ - Time Series: https://wiki.databurst.tech/docs/roadmap/databases/nosql/time-series/ - Vector: https://wiki.databurst.tech/docs/roadmap/databases/nosql/vector/ - Graph: https://wiki.databurst.tech/docs/roadmap/databases/nosql/graph/ - NewSQL: https://wiki.databurst.tech/docs/roadmap/databases/nosql/newsql/ - Data Modeling: https://wiki.databurst.tech/docs/roadmap/data-modeling/ - Star Schema (Ralph Kimball): https://wiki.databurst.tech/docs/roadmap/data-modeling/star-schema/ - Snowflake: https://wiki.databurst.tech/docs/roadmap/data-modeling/snowflake/ - Data Vault: https://wiki.databurst.tech/docs/roadmap/data-modeling/data-vault/ - Data Management Architecture: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/ - Relational Data Warehouse: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/relational-data-warehouse/ - Data Lake: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-lake/ - Modern Data Warehouse: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/modern-data-warehouse/ - Data Fabric: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-fabric/ - Data Mart: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-mart/ - Data Lakehouse: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-lakehouse/ - Data Mesh: https://wiki.databurst.tech/docs/roadmap/data-management-architecture/data-mesh/ - Data Integration: https://wiki.databurst.tech/docs/roadmap/data-integration/ - ETL: https://wiki.databurst.tech/docs/roadmap/data-integration/etl/ - ELT: https://wiki.databurst.tech/docs/roadmap/data-integration/elt/ - CDC: https://wiki.databurst.tech/docs/roadmap/data-integration/cdc/ - Reverse ETL: https://wiki.databurst.tech/docs/roadmap/data-integration/reverse-etl/ - Storage: https://wiki.databurst.tech/docs/roadmap/storage/ - Block Storage: https://wiki.databurst.tech/docs/roadmap/storage/block-storage/ - Object Storage: https://wiki.databurst.tech/docs/roadmap/storage/object-storage/ - File Formats and Serialization: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/ - File Formats: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/ - CSV: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/csv/ - JSON: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/json/ - Parquet: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/parquet/ - XML: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/file-formats/xml/ - Serialization: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/serialization/ - Avro: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/serialization/avro/ - Protocol Buffers: https://wiki.databurst.tech/docs/roadmap/file-formats-and-serialization/serialization/protobuf/ - Query Engine: https://wiki.databurst.tech/docs/roadmap/query-engine/ - Apache Hive: https://wiki.databurst.tech/docs/roadmap/query-engine/hive/ - SparkSQL: https://wiki.databurst.tech/docs/roadmap/query-engine/sparksql/ - Trino: https://wiki.databurst.tech/docs/roadmap/query-engine/trino/ - Presto: https://wiki.databurst.tech/docs/roadmap/query-engine/presto/ - Data Architecture: https://wiki.databurst.tech/docs/roadmap/data-architecture/ - Kappa: https://wiki.databurst.tech/docs/roadmap/data-architecture/kappa/ - Lambda: https://wiki.databurst.tech/docs/roadmap/data-architecture/lambda/ - Data Processing: https://wiki.databurst.tech/docs/roadmap/data-processing/ - Pipeline Orchestration: https://wiki.databurst.tech/docs/roadmap/pipeline-orchestration/ - Messaging and Streaming Platform: https://wiki.databurst.tech/docs/roadmap/messaging-and-streaming-platform/ - Data Governance: https://wiki.databurst.tech/docs/roadmap/data-governance/ - Data Quality: https://wiki.databurst.tech/docs/roadmap/data-governance/data-quality/ - Data Visualization: https://wiki.databurst.tech/docs/roadmap/data-visualization/ - Containerization: https://wiki.databurst.tech/docs/roadmap/containerization/ - Docker: https://wiki.databurst.tech/docs/roadmap/containerization/docker/ - Podman: https://wiki.databurst.tech/docs/roadmap/containerization/podman/ - containerd: https://wiki.databurst.tech/docs/roadmap/containerization/containerd/ - Container Orchestration: https://wiki.databurst.tech/docs/roadmap/container-orchestration/ - Kubernetes: https://wiki.databurst.tech/docs/roadmap/container-orchestration/kubernetes/ - Docker Swarm: https://wiki.databurst.tech/docs/roadmap/container-orchestration/docker-swarm/ - Apache Mesos: https://wiki.databurst.tech/docs/roadmap/container-orchestration/apache-mesos/ - CI/CD: https://wiki.databurst.tech/docs/roadmap/cicd/ - DataOps: https://wiki.databurst.tech/docs/roadmap/cicd/dataops/ - Infrastructure as Code: https://wiki.databurst.tech/docs/roadmap/infrastructure-as-code/ - Configuration Management: https://wiki.databurst.tech/docs/roadmap/infrastructure-as-code/configuration-management/ - Infrastructure Provisioning: https://wiki.databurst.tech/docs/roadmap/infrastructure-as-code/infrastructure-provisioning/ - Observability: https://wiki.databurst.tech/docs/roadmap/observability/ - Logging: https://wiki.databurst.tech/docs/roadmap/observability/logging/ - Monitoring: https://wiki.databurst.tech/docs/roadmap/observability/monitoring/ - Tracing: https://wiki.databurst.tech/docs/roadmap/observability/tracing/ - Glossary: https://wiki.databurst.tech/docs/roadmap/glossary/
$ SELECT ... OVER (PARTITION BY ... ORDER BY ...)
# Advanced SQL Advanced SQL is mostly about expressing multi-step logic **in one readable query** instead of exporting data to Python. The core tools are **common table expressions** (CTEs) to break a query into named steps, **window functions** to compute running totals, rankings and comparisons across rows without collapsing them, and **MERGE** to upsert data. These features are standard SQL and work in PostgreSQL, Snowflake, BigQuery, Spark SQL, Trino and DuckDB with small syntax differences. ## Common table expressions (CTEs) A CTE names a subquery so the main query reads top to bottom like a pipeline: ```sql WITH paid AS ( SELECT * FROM orders WHERE status = 'paid' ), by_customer AS ( SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue FROM paid GROUP BY customer_id ) SELECT * FROM by_customer WHERE revenue > 1000; ``` This is the style most dbt models are written in. Modern optimizers usually inline CTEs, so they cost nothing extra; check the plan if a CTE is referenced many times. **Recursive CTEs** walk hierarchies such as org charts or category trees: ```sql WITH RECURSIVE chain AS ( SELECT id, manager_id, name, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, e.name, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain ORDER BY depth; ``` ## Window functions A window function computes a value for each row using a **window** of related rows, and unlike `GROUP BY` it keeps every row. The window is defined by `OVER (...)`: - `PARTITION BY` splits rows into independent groups (per city, per customer). - `ORDER BY` orders rows within each partition. - A **frame** (`ROWS BETWEEN ...`) picks which rows around the current one to include. {{< fig kicker="$ AVG(revenue) OVER (PARTITION BY city ORDER BY day ROWS 2 PRECEDING)" title="A 3-day moving average: the frame slides, and resets per partition" >}} CITYDAYREVENUEAVG_3DFRAME FOR EACH ROW New York11-01100 New York11-02140 New York11-0390 New York11-04130 Toronto11-0160 Toronto11-0280 AVG(revenue) OVER (PARTITION BY city ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3d r1 100.0 r2 120.0 r3 110.0 r4 120.0 r5 60.0 r6 70.0
  1. Daily revenue for two cities. We want a 3-day moving average that restarts for each city.
  2. Row 1 (New York, Nov 1): no preceding rows yet, so the frame holds only itself. avg = 100.
  3. Row 2: the frame is rows 1–2, because only one preceding row exists. avg = 120.
  4. Row 3: the full frame, 2 preceding + current. avg = (100 + 140 + 90) / 3 = 110.
  5. Row 4: the frame slides down one row and Nov 1 drops out. avg = (140 + 90 + 130) / 3 = 120.
  6. Toronto is a new partition: the frame resets and never reaches into New York's rows. avg = 60.
  7. All six input rows are still in the output. Window functions add a column; GROUP BY would have collapsed the rows.
{{< /fig >}} ### The main window functions | Family | Functions | Example use | |---|---|---| | Ranking | `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE` | Top 3 products per category, deduplicate rows | | Offset | `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE` | Change vs previous day, time between orders | | Aggregate | `SUM`, `AVG`, `COUNT`, `MIN`, `MAX` with `OVER` | Running totals, moving averages, share of total | ```sql SELECT customer_id, order_id, ordered_at, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS order_seq, ordered_at - LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS since_prev, SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS running_total, amount / SUM(amount) OVER (PARTITION BY customer_id) AS share_of_customer FROM orders; ``` `ROW_NUMBER` gives 1, 2, 3, 4 even for ties; `RANK` gives 1, 2, 2, 4; `DENSE_RANK` gives 1, 2, 2, 3. ### Filtering on a window: QUALIFY You cannot use a window function in `WHERE` (it runs before `SELECT`). Either wrap the query in a CTE, or use `QUALIFY`, supported by Snowflake, BigQuery, Databricks, DuckDB and others (not PostgreSQL): ```sql -- keep the latest version of each customer (a classic dedup) SELECT * FROM customers_raw QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1; ``` ## More aggregation: GROUPING SETS, ROLLUP, CUBE Compute several levels of totals in one pass: ```sql SELECT city, status, SUM(amount) AS revenue FROM orders GROUP BY ROLLUP (city, status); -- per (city, status), per city, and grand total ``` `CUBE (city, status)` adds every combination; `GROUPING SETS ((city), (status), ())` lists exactly the levels you want. `FILTER (WHERE ...)` computes conditional aggregates: `COUNT(*) FILTER (WHERE status = 'paid')` (PostgreSQL, DuckDB, Trino, Spark SQL); engines without it use `COUNT(CASE WHEN status = 'paid' THEN 1 END)`. ## Upserts with MERGE `MERGE` inserts, updates or deletes in one atomic statement, which is how incremental loads into warehouses and lakehouse tables (Delta, Iceberg) are written: ```sql MERGE INTO dim_customer t USING staged_customers s ON t.customer_id = s.customer_id WHEN MATCHED AND t.city <> s.city THEN UPDATE SET city = s.city, updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT (customer_id, city, updated_at) VALUES (s.customer_id, s.city, s.updated_at); ``` PostgreSQL supports `MERGE` since version 15 and also has `INSERT ... ON CONFLICT (customer_id) DO UPDATE`. Make sure the source has **one row per key**, or the merge fails or behaves unpredictably. ## Views and materialized views - A **view** is a saved query. It always shows current data and costs a full query each time it is read. - A **materialized view** stores the result. Reads are fast; the data is as fresh as the last refresh. PostgreSQL refreshes manually (`REFRESH MATERIALIZED VIEW sales_summary;`, or `... CONCURRENTLY` to keep it readable during the refresh, which requires a unique index on the view); Snowflake, BigQuery and ClickHouse can maintain some materialized views incrementally and automatically. ```sql CREATE MATERIALIZED VIEW sales_summary AS SELECT city, date_trunc('day', ordered_at) AS day, SUM(amount) AS revenue FROM orders WHERE status = 'paid' GROUP BY 1, 2; ``` In dbt, the same choice is the model's materialization: `view`, `table`, `incremental` or `materialized_view`. ## Reading the query plan `EXPLAIN` shows how the engine will run a query; `EXPLAIN ANALYZE` runs it and shows real timings and row counts. Look for full scans of big tables where you expected an index or partition pruning, row estimates far from the actual counts, and joins that explode row counts. See [indexing]({{< relref "/docs/roadmap/distributed-systems-concepts/indexing" >}}). ## Common mistakes - **Default frame surprise**: with `ORDER BY` and no frame, the default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Ties are treated as one peer group, so a running total jumps by several rows at once, and `LAST_VALUE` returns the current row (or its last tie), not the last row of the partition. Spell out `ROWS BETWEEN ...` when it matters. - Using `ROW_NUMBER` for dedup without a deterministic `ORDER BY`, so reruns keep different rows. - `ROWS BETWEEN 6 PRECEDING` as a "7-day average" when some days have no rows. Fill gaps with a calendar table or use `RANGE` with an interval. - `MERGE` from a source with duplicate keys. - Deeply nested subqueries instead of named CTEs that someone else can read. ## Learning Resources ### Docs - [PostgreSQL: Window Functions tutorial](https://www.postgresql.org/docs/current/tutorial-window.html) - [PostgreSQL: WITH Queries (CTEs)](https://www.postgresql.org/docs/current/queries-with.html) - [Modern SQL](https://modern-sql.com/): standard features and support across databases - [Use The Index, Luke](https://use-the-index-luke.com/): performance and query plans ### Tutorials - [Mode: SQL Window Functions](https://mode.com/sql-tutorial/sql-window-functions/) - [SQL PARTITION BY Clause](https://www.sqltutorial.org/sql-window-functions/sql-partition-by/) ### Books - [SQL Antipatterns, Volume 1](https://pragprog.com/titles/bksap1/sql-antipatterns-volume-1/): Bill Karwin - [SQL Performance Explained](https://sql-performance-explained.com/): Markus Winand ### Videos - [Advanced SQL Tutorial | CTE (Common Table Expression)](https://www.youtube.com/watch?v=K1WeoKxLZ5o) - [Advanced SQL Tutorial | Partition By](https://www.youtube.com/watch?v=D6XNlTfglW4)