DataStack Daily

Slow queries are the most common thing that makes a data engineer look bad in a meeting. Someone runs a dashboard, the spinner turns for 20 seconds, and the Slack message hits your DMs before the query finishes. This guide covers the five fixes that resolve 80% of production slowdowns—each with a concrete example you can run against your own warehouse. In this guide Fix 1: Missing (or wrong) indexes Fix 2: SELECT * on wide tables Fix 3: Nested subqueries vs CTEs vs JOINs Fix 4: Implicit type casting in JOINs Fix 5: Unanchored JOINs (cartesian products) FAQ Quick answer: If a query is slow and you can only check one thing, check the JOIN columns for indexes. A single missing index on a foreign-key column will turn an O(log n) lookup into a full table scan across millions of rows. Fix 1: Missing (or wrong) indexes An index is a sorted lookup structure. Without one on a JOIN or WHERE column, the database scans every row in the table. With one, it jumps dire...

A star schema is the data-modeling pattern you’ll hit the moment you build your first real dashboard, and it’s far simpler than the name suggests. It organizes your tables into one central table of measurements surrounded by tables of context — shaped, when you draw it, like a star. This guide is for analysts and beginners who want facts, dimensions, and grain to finally make sense, with one worked sales example. In this guide Why analytics needs a schema pattern Fact tables: the measurements Dimension tables: the context A worked sales example Star vs snowflake Grain: the mistake beginners make How this powers fast dashboards FAQ Quick answer: A star schema organizes analytics tables into one central ‘fact’ table of measurements (like sales) surrounded by ‘dimension’ tables of context (like product, customer, and date). It makes reporting queries simple to write and fast to run. Why analytics needs a schema pattern Databases...

SQL window functions add per-row calculations — rankings, running totals, comparisons to the previous row — without collapsing your result set the way GROUP BY does. If you’ve ever wanted a column that says “rank within this group” while keeping every original row, this guide shows you how to write the patterns you’ll reach for weekly. In this guide What a window function is (and why it’s not GROUP BY) The anatomy of OVER(): PARTITION BY and ORDER BY Ranking rows: ROW_NUMBER, RANK, and DENSE_RANK Running totals and moving averages Looking at other rows: LAG and LEAD Worked example: top-N per group Common pitfalls to avoid FAQ Quick answer: A window function computes a value for each row using a related set of rows — its “window” — and adds it as a new column without removing any rows. Declare the window with OVER() . PARTITION BY (optional) divides rows into independent groups; ORDER BY inside OVER() ...

Slowly changing dimensions are what every data engineer eventually runs into: the moment you realize your dimension table only stores today’s values and someone is asking about the past. Understanding SCD types comes down to one design decision you make every time a dimension attribute changes. In this guide What a slowly changing dimension is The core problem: overwrite or keep history? SCD Type 1: overwrite the old value SCD Type 2: add a new row with validity dates SCD Type 3: add a column for the previous value Type 1 vs 2 vs 3 side by side How to implement it (and common pitfalls) FAQ Quick answer: A slowly changing dimension is any dimension attribute that drifts over time — customer city, product category, sales region. Type 1 overwrites in place (no history). Type 2 adds a new row with validity dates — the workhorse. Type 3 adds a previous-value column (one transition only). Most teams need Type 2 more than they expec...