<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Suchithra Balakrishnan on Medium]]></title>
        <description><![CDATA[Stories by Suchithra Balakrishnan on Medium]]></description>
        <link>https://medium.com/@bysuchi?source=rss-497698dcd858------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*p1qnQDQZRPCnhUJClOF4OA.jpeg</url>
            <title>Stories by Suchithra Balakrishnan on Medium</title>
            <link>https://medium.com/@bysuchi?source=rss-497698dcd858------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 10:29:31 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@bysuchi/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[dbt Testing Isn’t Enough: A Data Quality Guide for Analytics Engineers]]></title>
            <link>https://bysuchi.medium.com/dbt-testing-isnt-enough-a-data-quality-guide-for-analytics-engineers-349da7110df3?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/349da7110df3</guid>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Thu, 27 Aug 2026 22:34:09 GMT</pubDate>
            <atom:updated>2026-08-27T22:34:09.353Z</atom:updated>
            <content:encoded><![CDATA[<p>Row count reconciliation, metric anomaly detection, and layered freshness monitoring, the data quality checks that catch what dbt’s built-in tests structurally can’t</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*myuuaiA_6Oxe6_izmx8vuA.avif" /><figcaption>Image from <a href="https://unsplash.com/photos/woman-fixing-kitchen-sink-EtEu1SSrYNQ">Unsplash</a></figcaption></figure><p>A pipeline can pass every single test you’ve written and still be quietly wrong. This guide is about closing that gap: not just the mechanics of dbt tests, but the layered thinking that catches problems structural tests alone can’t see, data silently lost between pipeline layers, business metrics drifting for reasons nothing has “broken,” and staleness creeping in at layers a source freshness check was never designed to watch. It ends with how to actually design a testing strategy from scratch, rather than accumulating an arbitrary pile of individually-reasonable checks.</p><p>If you want to first learn the basic to building these systems in dbt as an Analytics Engineer go to these links <a href="https://bysuchi.medium.com/query-optimization-concepts-for-an-analytics-engineer-3000f9dfbd4a">first</a>, <a href="https://bysuchi.medium.com/fact-dimension-grain-the-data-modeling-guide-nobody-gives-you-c44857e3d220">second</a> and <a href="https://bysuchi.medium.com/dbt-fundamentals-for-analytics-engineers-a-complete-guide-6f10cd2ba6c3">third</a>.</p><p><strong>What this guide covers:</strong></p><ol><li>dbt Built-In and Custom Tests, Revisited</li><li>Row Count Reconciliation Between Pipeline Layers</li><li>Anomaly Detection on Key Business Metrics</li><li>Freshness Monitoring as a General Practice</li><li>Designing a Data Quality Framework Holistically</li></ol><h3>1. dbt Built-In and Custom Tests, Revisited</h3><h3>The core idea</h3><p>Every dbt test is a query that should return zero rows if everything is correct. One or more rows returned means the test fails, nothing more exotic than that underlies every test type in this guide.</p><pre>SELECT * FROM some_model WHERE &lt;condition that should never be true&gt;</pre><h3>The four built-in generic tests</h3><pre>models:<br>  - name: fact_orders<br>    columns:<br>      - name: order_id<br>        tests: [unique, not_null]<br>      - name: customer_id<br>        tests:<br>          - not_null<br>          - relationships:<br>              to: ref(&#39;dim_customer&#39;)<br>              field: customer_id<br>      - name: status<br>        tests:<br>          - accepted_values:<br>              values: [&#39;pending&#39;, &#39;shipped&#39;, &#39;delivered&#39;, &#39;cancelled&#39;]</pre><p>unique is the direct enforcement mechanism for a fact table&#39;s grain, if grain is &quot;one row per order,&quot; unique on order_id is the literal, automated proof that the grain holds. not_null catches missing required values. accepted_values catches a new, unrecognized category silently introduced upstream. relationships is a referential integrity check most cloud warehouses don&#39;t enforce natively:</p><pre>SELECT f.customer_id<br>FROM fact_orders f<br>LEFT JOIN dim_customer d ON f.customer_id = d.customer_id<br>WHERE d.customer_id IS NULL AND f.customer_id IS NOT NULL</pre><p>This catches orphaned foreign keys, a fact row referencing a customer that doesn’t exist anywhere in the dimension table, invisible otherwise until it silently drops or nulls out a row in a downstream join. A concrete scenario: if a snapshot-based dimension’s build gets sequenced <em>after</em> a fact table’s build due to a pipeline ordering issue, a brand-new customer’s transaction could be computed before their row exists in the dimension, nothing errors, the report is just quietly incomplete. relationships is genuinely underused in practice, since it&#39;s more expensive than a single-column test (it requires a join, sometimes across large tables), worth scoping on large tables:</p><pre>config:<br>  where: &quot;order_date &gt;= dateadd(&#39;day&#39;, -30, current_date)&quot;</pre><blockquote><strong><em>Q: Where are dbt’s built-in tests actually defined, and where do custom tests live?</em></strong></blockquote><blockquote><em>The built-in tests ship as pre-written </em><em>{% test %} macros inside dbt Core itself, invisible in your own project; you only ever reference them via YAML. Custom generic tests live in your own project&#39;s </em><em>tests/ folder as </em><em>{% test %} macros you write yourself, conventionally under a </em><em>generic/ subfolder for organization, though dbt doesn&#39;t require that specific folder structure, it discovers a test by content (does the file contain a </em><em>{% test %} block), not by location.</em></blockquote><h3>The multi-column uniqueness test, the actual grain-enforcement mechanism</h3><pre>models:<br>  - name: fact_scores_daily<br>    tests:<br>      - dbt_utils.unique_combination_of_columns:<br>          combination_of_columns: [customerId, scoreDate]</pre><p>Worth restating precisely: this is not one of dbt’s four built-ins, it comes from the dbt_utils package. This is the actual mechanism that enforces a multi-column grain, and arguably the single most important test on any fact table, since grain violations are silent and compounding, they never throw an error, they just quietly inflate every downstream SUM/COUNT.</p><h3>Custom generic tests and singular tests</h3><pre>-- Custom generic test: reusable across any column on any model<br>{% test is_positive(model, column_name) %}<br>    SELECT * FROM {{ model }} WHERE {{ column_name }} &lt; 0<br>{% endtest %}</pre><pre>-- Singular test: a plain .sql file, no wrapper, no YAML reference needed<br>-- tests/assert_no_future_order_dates.sql<br>SELECT * FROM {{ ref(&#39;fact_orders&#39;) }} WHERE order_date &gt; CURRENT_DATE</pre><p>A custom generic test is right when a check is likely to apply across multiple models/columns. A singular test is right for something genuinely specific to one model. <strong>Where each is defined matters:</strong> a generic test’s location within tests/generic/ is pure convention, not a dbt requirement, dbt discovers a test macro by content, does the file contain a {% test %} block, not by folder location. A singular test is registered purely by its presence as a .sql file inside dbt&#39;s configured test-paths, no YAML attachment at all, and it derives its position in the DAG the same way a model does: dbt parses the file&#39;s own ref()/source() calls. This is precisely why a singular test, and only a singular test, can compare <strong>two different models</strong> in one query, a generic test&#39;s YAML attachment mechanism inherently scopes it to exactly one model at a time.</p><blockquote><strong><em>Q: Since singular tests have no YAML entry, does that mean they’re not part of the DAG, and don’t get the same DAG-based scheduling as everything else?</em></strong></blockquote><blockquote><em>They’re still fully part of the DAG. dbt determines a singular test’s position by parsing the </em><em>ref()/</em><em>source() calls inside the test&#39;s own SQL, the same mechanism used for any model. This means a singular test naturally runs immediately after the models it references finish building, and can depend on multiple models at once, something a generic test&#39;s one-model-at-a-time YAML attachment structurally cannot do.</em></blockquote><h3>Severity: warn vs. error, and where it&#39;s configured</h3><pre>tests:<br>  - not_null:<br>      config:<br>        severity: warn</pre><pre>-- The identical mechanism, expressed inside a singular test&#39;s own .sql file,<br>-- since there&#39;s no YAML entry to attach a config block to<br>{{ config(severity = &#39;warn&#39;, warn_if = &#39;&gt;5&#39;, error_if = &#39;&gt;100&#39;) }}</pre><p>A warn-level failure is logged but doesn&#39;t cause dbt build to exit non-zero. error_if/warn_if refine this further, only actually failing once the row count crosses a specific threshold, rather than treating one returned row identically to a thousand.</p><blockquote><strong><em>Q: Do singular tests support the same error/warn severity options as generic tests?</em></strong></blockquote><blockquote><em>Yes, identically, the mechanism just moves. A generic test’s severity is set in its YAML </em><em>config: block, since that&#39;s where the test is referenced at all. A singular test has no YAML entry, so the same </em><em>config() call is written directly inside the test&#39;s own </em><em>.sql file instead, functionally equivalent either way.</em></blockquote><h3>Where testing runs, and the actual payoff of dbt build</h3><pre>dbt run    # builds models only, no tests<br>dbt test   # runs tests only, against whatever&#39;s already built<br>dbt build  # builds and tests each model immediately, in DAG order</pre><p>With dbt build, tests run right after the specific model they&#39;re attached to finishes, not after the whole project. If a test fails, dbt <strong>skips building downstream models that depend on it</strong>, rather than continuing to build on top of data it just proved is broken. This is the actual mechanical enforcement of a quality mindset: tests aren&#39;t documentation of expected behavior, they&#39;re an active circuit breaker.</p><h3>What can and cannot be tested</h3><p>Tests run against any queryable object: tables, views, incremental models, seeds, snapshots, and even raw sources directly (tests can be attached in a source’s own YAML, catching a problem at the earliest possible point, before staging even runs). The one genuine exception is ephemeral, since that materialization never creates a database object at all, it&#39;s inlined as a compile-time CTE, there&#39;s nothing for a test query to select from.</p><blockquote><strong><em>Q: Can these tests only be applied to materialized views and tables?</em></strong></blockquote><blockquote><em>No, the real dividing line is different: any object that exists as something queryable in the warehouse can be tested, including plain views, which recompute on read but are still real, queryable objects. The only materialization that genuinely can’t be tested is </em><em>ephemeral, since it never creates a database object at all.</em></blockquote><h3>2. Row Count Reconciliation Between Pipeline Layers</h3><h3>Why this is a distinct concern from structural tests</h3><p>Structural tests check correctness within a single model. Row count reconciliation checks something different: did data survive the trip between layers correctly, as it moved from a raw source through staging, through transformations, into a final mart. A join with an unintended fan-out, an overly aggressive filter, a subtly wrong incremental merge key, none of these throw an error. The pipeline runs successfully, every individual model’s own tests can even pass, and the final mart quietly has the wrong number of rows relative to its source.</p><h3>Pattern 1: Source-to-staging reconciliation</h3><pre>SELECT<br>    (SELECT COUNT(*) FROM {{ source(&#39;raw&#39;, &#39;orders&#39;) }}) AS source_count,<br>    (SELECT COUNT(*) FROM {{ ref(&#39;stg_orders&#39;) }}) AS staging_count<br>HAVING source_count != staging_count</pre><p>If staging genuinely does nothing but rename and cast, no filtering, no joins, no aggregation, the row count should be identical, always, with zero exceptions.</p><blockquote><strong><em>Q: What if staging deliberately applies a filter to reduce the data volume flowing downstream, for performance? Wouldn’t a strict “counts must match” test fail every time?</em></strong></blockquote><blockquote><em>Yes, and that’s a real, common case worth accounting for explicitly. If staging intentionally filters (a date cutoff, for instance) to reduce downstream processing volume, the reconciliation test needs to apply that same filter to the source side of the comparison, otherwise it becomes a permanent false alarm rather than a meaningful check.</em></blockquote><blockquote><strong>A genuine, important caveat:</strong> if staging deliberately applies an early filter for performance (a date cutoff, reducing the volume flowing into every downstream model), this check as written will false-alarm on every single run. The fix is to apply the same filter to the source side of the comparison, so the test’s expectation matches what the transformation is actually supposed to do:</blockquote><pre>SELECT<br>    (SELECT COUNT(*) FROM {{ source(&#39;raw&#39;, &#39;orders&#39;) }} WHERE order_date &gt;= DATEADD(year, -2, CURRENT_DATE)) AS source_count,<br>    (SELECT COUNT(*) FROM {{ ref(&#39;stg_orders&#39;) }}) AS staging_count<br>HAVING source_count != staging_count</pre><p>The broader lesson: a reconciliation test encodes an assumption about what a transformation is supposed to do. If that logic changes, the test needs to be updated in the same commit, not left stale, since a test that fails on every run for a known, expected reason just trains a team to ignore failures, which defeats the entire purpose of having it.</p><h3>Pattern 2: Grain preservation across a join</h3><pre>SELECT<br>    (SELECT COUNT(DISTINCT order_id) FROM {{ ref(&#39;stg_orders&#39;) }}) AS distinct_source_orders,<br>    (SELECT COUNT(*) FROM {{ ref(&#39;fact_orders&#39;) }}) AS fact_table_rows<br>HAVING distinct_source_orders != fact_table_rows</pre><p>This catches a join fan-out directly, an order with 3 line items producing 3 fact rows instead of 1 aggregated row, by comparing against an independently-derived expectation rather than relying on the fact table’s own internal unique test alone.</p><h3>Pattern 3: Completeness across an aggregation step</h3><pre>SELECT<br>    (SELECT COUNT(DISTINCT account_id || &#39;-&#39; || transaction_date) FROM {{ ref(&#39;stg_transactions&#39;) }}) AS expected_grain_count,<br>    (SELECT COUNT(*) FROM {{ ref(&#39;daily_account_summary&#39;) }}) AS actual_count<br>HAVING expected_grain_count != actual_count</pre><p>This is functionally close to a multi-column uniqueness test, but framed the opposite direction: rather than checking that nothing is duplicated downstream, it checks that the <em>number</em> of expected combinations upstream matches the number of rows downstream, catching rows that should exist but are silently missing, a category of bug a plain uniqueness test structurally cannot detect, since uniqueness says nothing about completeness. This is exactly the same class of gap as the late-arriving-data bug from incremental model design, a uniqueness test alone would never catch it, since the missing row was never duplicated, it was simply absent.</p><h3>The cost tradeoff</h3><p>Every reconciliation check queries two separate models, more expensive than a single-table test. On large tables, scope to recent data rather than reconciling the full historical table on every run, reserving a complete historical audit for a less frequent, deliberate check.</p><h3>3. Anomaly Detection on Key Business Metrics</h3><h3>Why this is a genuinely different category</h3><p>A pipeline can be structurally perfect, every uniqueness test green, every reconciliation check balanced, and still produce a business metric that’s wildly wrong because of a genuine upstream data problem: a marketing campaign double-firing events, a partner integration sending duplicate webhooks, a stale currency conversion rate. Structural tests can’t catch this, because nothing about the pipeline’s mechanics is actually broken, the input data itself is just wrong or unusual.</p><h3>A naive day-over-day check, and its real weakness</h3><pre>WITH daily_totals AS (<br>    SELECT order_date, SUM(amount) AS daily_revenue<br>    FROM {{ ref(&#39;fact_orders&#39;) }}<br>    WHERE order_date &gt;= CURRENT_DATE - 8<br>    GROUP BY order_date<br>),<br>comparison AS (<br>    SELECT order_date, daily_revenue,<br>        daily_revenue / NULLIF(LAG(daily_revenue) OVER (ORDER BY order_date), 0) AS ratio_to_prev_day<br>    FROM daily_totals<br>)<br>SELECT * FROM comparison<br>WHERE order_date = CURRENT_DATE - 1 AND (ratio_to_prev_day &gt; 2.0 OR ratio_to_prev_day &lt; 0.5)</pre><p>This directly reuses the fraud-detection pattern of a LAG()-based ratio with a NULLIF guard. Its real weakness: comparing only to yesterday is fragile against normal seasonality, a Monday will legitimately look very different from a Sunday, and this would false-alarm every single week.</p><h3>A rolling baseline, more robust against seasonality</h3><pre>WITH daily_totals AS (<br>    SELECT order_date, SUM(amount) AS daily_revenue<br>    FROM {{ ref(&#39;fact_orders&#39;) }}<br>    GROUP BY order_date<br>),<br>with_baseline AS (<br>    SELECT order_date, daily_revenue,<br>        AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING) AS trailing_28day_avg,<br>        STDDEV(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING) AS trailing_28day_stddev<br>    FROM daily_totals<br>)<br>SELECT * FROM with_baseline<br>WHERE order_date = CURRENT_DATE - 1<br>  AND ABS(daily_revenue - trailing_28day_avg) &gt; 3 * trailing_28day_stddev</pre><p><strong>Why 28 days specifically:</strong> exactly 4 full weeks, meaning the trailing window contains an equal number of each weekday, smoothing out weekly seasonality far better than an arbitrary window like 30 days, which contains an uneven mix of weekdays depending on where it happens to start. ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING deliberately excludes the current row from its own baseline, the same reasoning as any anomaly baseline: today&#39;s number shouldn&#39;t contaminate the threshold it&#39;s being judged against.</p><h3>A drop is often more urgent than a spike</h3><pre>SELECT order_date, daily_revenue<br>FROM daily_totals<br>WHERE order_date = CURRENT_DATE - 1<br>  AND daily_revenue &lt; (SELECT AVG(daily_revenue) * 0.1 FROM daily_totals WHERE order_date &gt;= CURRENT_DATE - 29 AND order_date &lt; CURRENT_DATE - 1)</pre><p>A spike could be a genuine business event, worth investigating but not necessarily alarming. A sudden drop toward zero is far more likely to indicate something structurally broken upstream, a source outage, a broken integration, a pipeline that silently stopped ingesting, the same “a missing row is a stronger signal than an unchanged one” idea from periodic snapshot fact table design, applied here to an aggregate trend instead of an individual row’s presence.</p><h3>Composite checks for higher confidence</h3><pre>WITH daily_anomaly AS (<br>    SELECT order_date FROM with_baseline<br>    WHERE order_date = CURRENT_DATE - 1 AND ABS(daily_revenue - trailing_28day_avg) &gt; 3 * trailing_28day_stddev<br>),<br>top_contributor AS (<br>    SELECT customer_id,<br>        SUM(amount) * 1.0 / (SELECT SUM(amount) FROM {{ ref(&#39;fact_orders&#39;) }} WHERE order_date = CURRENT_DATE - 1) AS pct_of_daily_total<br>    FROM {{ ref(&#39;fact_orders&#39;) }}<br>    WHERE order_date = CURRENT_DATE - 1<br>    GROUP BY customer_id<br>    ORDER BY SUM(amount) DESC<br>    LIMIT 1<br>)<br>SELECT * FROM daily_anomaly, top_contributor WHERE top_contributor.pct_of_daily_total &gt; 0.3</pre><p>Layering a concentration signal on top of a metric anomaly helps distinguish a genuine business event from a data quality bug, one customer responsible for an unusually large share of an anomalous day strongly suggests duplicated data or a leaked test account, rather than a broad-based shift, the same “combine multiple independent signals for higher confidence” principle from fraud detection, applied here to pipeline health.</p><h3>Turning this into a reusable, parameterized generic test</h3><pre>{% test no_metric_anomaly(model, date_column, metric_column, lookback_days=28, zscore_threshold=3) %}<br>WITH daily_totals AS (<br>    SELECT {{ date_column }} AS metric_date, SUM({{ metric_column }}) AS daily_value<br>    FROM {{ model }}<br>    GROUP BY {{ date_column }}<br>),<br>with_baseline AS (<br>    SELECT metric_date, daily_value,<br>        AVG(daily_value) OVER (ORDER BY metric_date ROWS BETWEEN {{ lookback_days }} PRECEDING AND 1 PRECEDING) AS trailing_avg,<br>        STDDEV(daily_value) OVER (ORDER BY metric_date ROWS BETWEEN {{ lookback_days }} PRECEDING AND 1 PRECEDING) AS trailing_stddev<br>    FROM daily_totals<br>)<br>SELECT * FROM with_baseline<br>WHERE metric_date = CURRENT_DATE - 1 AND ABS(daily_value - trailing_avg) &gt; {{ zscore_threshold }} * trailing_stddev<br>{% endtest %}</pre><pre>models:<br>  - name: fact_orders<br>    tests:<br>      - no_metric_anomaly:<br>          date_column: order_date<br>          metric_column: amount<br>          lookback_days: 28<br>          zscore_threshold: 3</pre><p>Once written, this applies to any metric on any model with a date column and a numeric column, through YAML configuration alone, without rewriting the underlying window function logic each time. A composite check like the concentration example above generally can’t be parameterized this cleanly, since it pulls in a genuinely different concept, and is usually better left as a one-off singular test.</p><blockquote><strong><em>Q: How would these anomaly checks actually be defined in a YAML file?</em></strong></blockquote><blockquote><em>Simple row-level threshold checks can go straight into YAML using existing </em><em>dbt_utils tests like </em><em>expression_is_true. Anything involving a rolling window or aggregate comparison across time needs to first be written as a custom generic test, a </em><em>{% test %} macro parameterized by date column, metric column, and thresholds, which can then be attached and reused from YAML across many models. Composite checks that pull in a genuinely different concept, like customer concentration, usually don&#39;t parameterize cleanly and are better left as one-off singular tests instead.</em></blockquote><h3>Where these checks actually live</h3><p>Unlike structural and reconciliation tests, business metric anomalies are more commonly implemented to feed an alert for human judgment rather than hard-failing a build, since a metric anomaly’s correct response often genuinely depends on context (a real event vs. a bug) that a test alone can’t reliably determine.</p><h3>4. Freshness Monitoring as a General Practice</h3><h3>Why this goes beyond source freshness</h3><p>Source freshness checks only the entry point of the pipeline. It says nothing about whether every layer downstream of that source, and ultimately the dashboards people actually look at, is staying current. A source can be perfectly fresh while a mart is stale for entirely different reasons, a broken transformation, a failed incremental merge, an orchestration job that silently stopped partway through the DAG.</p><h3>Mart-level freshness, and a reusable parameterized version</h3><pre>{% test model_is_fresh(model, date_column, max_age_hours=24) %}<br>SELECT MAX({{ date_column }}) AS most_recent_value<br>FROM {{ model }}<br>HAVING DATEDIFF(hour, MAX({{ date_column }}), CURRENT_TIMESTAMP) &gt; {{ max_age_hours }}<br>{% endtest %}</pre><pre>models:<br>  - name: fact_orders<br>    tests:<br>      - model_is_fresh: {date_column: order_date, max_age_hours: 24, config: {severity: warn}}<br>  - name: fact_scores_daily<br>    tests:<br>      - model_is_fresh: {date_column: scoreDate, max_age_hours: 30, config: {severity: error}}</pre><p>A daily fraud-scoring pipeline feeding an operational team plausibly deserves a hard stop if it goes stale, since operational teams depend on gap-free daily data, while a pipeline feeding a monthly summary can tolerate a warning-level, looser threshold.</p><h3>Schedule-awareness, a real and common failure mode of naive freshness checks</h3><pre>{% test model_is_fresh_business_days(model, date_column, max_age_hours=24) %}<br>SELECT MAX({{ date_column }}) AS most_recent_value<br>FROM {{ model }}<br>WHERE DAYOFWEEK(CURRENT_DATE) NOT IN (1, 7)<br>HAVING DATEDIFF(hour, MAX({{ date_column }}), CURRENT_TIMESTAMP) &gt; {{ max_age_hours }}<br>{% endtest %}</pre><p>A naive fixed-age check with no schedule-awareness would fire a false alarm every Monday morning for a weekday-only pipeline, since the data would legitimately be roughly 60 hours old after a weekend gap, not 24. The check needs to encode the system’s actual expected behavior, not a one-size-fits-all assumption, the same underlying lesson as avoiding a naive day-over-day metric comparison.</p><h3>The boundary dbt-native tooling cannot cross</h3><p>Exposures document that a dashboard depends on a specific model, extending the lineage graph one step past the warehouse:</p><pre>exposures:<br>  - name: executive_revenue_dashboard<br>    type: dashboard<br>    depends_on: [ref(&#39;fact_orders&#39;)]<br>    owner: {name: Data Team, email: data@company.com}</pre><p><strong>Worth being precise about a real, common misconception:</strong> the owner.email field is inert, static metadata, purely for a human reading the lineage graph in the docs site to know who to contact. It is never used to send an actual notification. Real alerting comes entirely from the orchestrator layer watching dbt build&#39;s exit code, a completely separate system with no automatic connection to an exposure&#39;s owner field, if a dashboard owner changes, updating the exposure&#39;s YAML does not update who the orchestrator actually notifies. dbt itself also has no visibility into whether a BI tool&#39;s own cache or extract has refreshed, that requires the BI platform&#39;s own monitoring.</p><blockquote><strong><em>Q: What does an exposure actually mean?</em></strong></blockquote><blockquote><em>An exposure is purely a documentation and lineage-graph entry, </em><em>depends_on draws the dependency edge, </em><em>owner is metadata a human reads.</em></blockquote><blockquote><strong><em>Q: does it email the listed owner when something fails?</em></strong></blockquote><blockquote><em>No, it does not send any email, the field is inert and disconnected from any alerting system. A real notification on a test or freshness failure comes entirely from the orchestrator watching </em><em>dbt build&#39;s exit code, a separate system that has no automatic link to an exposure&#39;s owner field at all.</em></blockquote><h3>Freshness as a layered defense, not a single check</h3><pre>dbt source freshness   # Gate 1: is the raw data itself fresh?<br>dbt build               # Gate 2: mart-level freshness, reconciliation, and anomaly<br>                         #         checks all run together, as ordinary tests</pre><p>Source freshness catches an ingestion failure at the earliest point. Mart-level freshness catches a transformation-layer failure source freshness structurally cannot see. BI-layer monitoring, where feasible, catches a presentation-layer failure neither of the first two can see. Each layer catches a genuinely different class of failure, none is a substitute for the others.</p><h3>5. Designing a Data Quality Framework Holistically</h3><h3>The actual question this answers</h3><p>Given a brand-new pipeline with zero existing tests, how do you decide what to build, in what order, and how do these pieces fit into one coherent strategy rather than an arbitrary pile of individually-reasonable checks?</p><h3>Step 1: Grain and structural correctness first, non-negotiably</h3><p>Every other check in this guide implicitly assumes the tables involved already have a correct, known grain. A reconciliation check is meaningless without already knowing what “correct” looks like structurally. An anomaly check on SUM(amount) is silently wrong if the underlying table has an undetected duplication bug inflating that sum. Grain and referential integrity are the load-bearing foundation everything else sits on.</p><h3>Step 2: Reconciliation at the highest-risk boundaries, not uniformly everywhere</h3><p>Reconciliation checks are relatively expensive; applying them at every model boundary in a large project is wasteful with diminishing returns. Prioritize joins that could fan out, incremental models where a subtly wrong merge key could silently duplicate or drop rows, and filtering logic that isn’t dead simple. A one-to-one staging model with no filtering doesn’t need the same scrutiny as a complex incremental fact table with three joins feeding it.</p><h3>Step 3: Freshness thresholds matched to actual consumption</h3><p>A pipeline checked every morning by an operational team deserves a tight threshold and severity: error. A pipeline feeding a monthly summary can tolerate severity: warn and a looser window. Applying the same SLA to every model regardless of who consumes it and how urgently either over-alerts on low-stakes pipelines or under-protects genuinely critical ones.</p><h3>Step 4: Business metric anomaly detection last, and only where someone will act</h3><p>Anomaly detection is the most expensive to build correctly and the most dependent on Steps 1–3 already being solid, an anomaly check is worthless if grain violations from Step 1 aren’t already being caught, since a “revenue spike” might just be an undetected duplication bug. Scope this to metrics with a real owner and a real action on alert, a monitoring system full of checks nobody responds to trains the team to ignore alerts generally, worse than not having the check at all.</p><h3>Step 5: Decide severity as a deliberate mapping exercise</h3><p>For every check, ask explicitly: if this fires, what should actually happen? Should it stop the pipeline immediately (a grain violation feeding an operational system) → error. Should it be visible but not block anything (an unusual metric that might be a genuine event) → warn. Should it only fire past a meaningful magnitude → error_if/warn_if with an actual threshold rather than the default &quot;any row returned is a failure.&quot; This mapping is, in a real sense, the actual deliverable of a data quality framework, the reusable SQL patterns are techniques; deciding which applies where, and what the consequence should be, is the judgment-based design work.</p><h3>Step 6: A framework is never “done”</h3><p>Every pattern in this guide assumes you already know what “normal” and “correct” look like. But pipelines change: a new category gets added, a business genuinely grows 3x in a quarter, a source system migrates. A threshold that was correct when written can silently become a source of false alarms months later, exactly the staging-filter scenario earlier in this guide, where a test’s assumption stopped matching reality after a legitimate change. A mature framework includes periodically revisiting thresholds and checks, not writing them once and assuming they stay correct forever.</p><h3>The One Unifying Idea</h3><p>Each layer of this guide answers a different version of the same question, at increasing distance from the raw data:</p><blockquote><strong><em>Structural tests ask: </em></strong><em>is this table internally correct, right now?</em></blockquote><blockquote><strong><em>Reconciliation asks: </em></strong><em>did data survive the trip between layers?</em></blockquote><blockquote><strong><em>Anomaly detection asks: </em></strong><em>does the business meaning look right, even if the structure is fine?</em></blockquote><blockquote><strong><em>Freshness asks: </em></strong><em>is this data current enough to trust at all?</em></blockquote><blockquote><strong><em>And the design layer asks: </em></strong><em>given all four tools, where do you actually point them, and how do you keep that decision correct over time?</em></blockquote><p>None of these substitutes for the others. A pipeline can pass every structural test and still silently corrupt a business metric; it can be perfectly fresh at the source and stale everywhere downstream. A real quality mindset is holding all four lenses at once, and being deliberate about where each one earns its cost.</p><h3>Quick-Reference Summary</h3><ul><li><strong>Every dbt test is a query that should return zero rows</strong> — the same mechanism underlies built-in, custom, generic, and singular tests alike.</li><li><strong>unique_combination_of_columns from </strong><strong>dbt_utils, not a dbt built-in, is the actual grain-enforcement mechanism</strong> for composite-key fact tables.</li><li><strong>A singular test can compare multiple models in one query</strong>, which a generic test’s YAML attachment structurally cannot, since it always scopes to exactly one model.</li><li><strong>dbt build is what turns tests into a real circuit breaker</strong> — it skips downstream models when an upstream test fails, unlike separate run/test, where everything builds before any result is known.</li><li><strong>Only </strong><strong>ephemeral models can&#39;t be tested</strong> — views, tables, incrementals, seeds, snapshots, and even raw sources all support tests directly.</li><li><strong>Source-to-staging row counts should match exactly, unless staging deliberately filters for performance</strong>, in which case the test needs the same filter applied to the source side, or it becomes a permanent false alarm.</li><li><strong>Reconciliation checks catch completeness bugs a uniqueness test structurally cannot</strong> — missing rows look identical to “nothing happened” without an independent count to compare against.</li><li><strong>A rolling baseline beats a simple day-over-day comparison</strong> for metric anomaly detection, since a naive comparison false-alarms on ordinary weekly seasonality; a 28-day window smooths this out precisely because it’s a multiple of 7.</li><li><strong>A drop toward zero deserves more urgency than a spike</strong> — a spike might be a real business event, a sudden drop is far more likely to signal a broken pipeline.</li><li><strong>Layering a concentration or diversity signal on top of a metric anomaly</strong> helps distinguish a genuine business event from a data quality bug.</li><li><strong>Source freshness, mart freshness, and BI-layer freshness are three separate defenses</strong>, each catching a failure class the others cannot see.</li><li><strong>An exposure’s owner email is inert documentation, not a notification mechanism</strong> — real alerting comes entirely from the orchestrator watching dbt build&#39;s exit code.</li><li><strong>Build a quality framework in order</strong>: grain and structural correctness first, reconciliation at high-risk boundaries, freshness matched to actual consumption, anomaly detection last and only where someone will act on it.</li><li><strong>Severity should be a deliberate decision, not a default</strong> — ask what should actually happen if each check fires, and configure error, warn, or a magnitude threshold accordingly.</li><li><strong>A quality framework needs periodic revisiting</strong> — a threshold correct when written can silently become wrong as the business or the pipeline’s own logic changes.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=349da7110df3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Pandas for Analytics Engineers: A Practical Guide (with Fraud Detection Examples)]]></title>
            <link>https://bysuchi.medium.com/pandas-for-analytics-engineers-a-practical-guide-with-fraud-detection-examples-a92d3c518917?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/a92d3c518917</guid>
            <category><![CDATA[interview]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Sat, 22 Aug 2026 15:17:49 GMT</pubDate>
            <atom:updated>2026-08-22T15:17:49.496Z</atom:updated>
            <content:encoded><![CDATA[<p>Groupby, rolling baselines, self-merges and the vectorization habits that separate clean analytics code from a fragile notebook</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XS7ic_GZXFKdRnx_zJNR7w.avif" /><figcaption>Image from <a href="https://unsplash.com/photos/a-computer-screen-and-keyboard-on-a-blue-background-OdFtpBn0sPM">Unsplash</a></figcaption></figure><p><a href="https://bysuchi.medium.com/query-optimization-concepts-for-an-analytics-engineer-3000f9dfbd4a">SQL</a> is where most fraud detection logic eventually lives in production, but pandas is where it usually starts: exploratory analysis, prototyping a new detection rule before it becomes a warehouse model, or a one-off investigation into a suspicious account. The mental model carries over directly from <a href="https://bysuchi.medium.com/the-sql-patterns-behind-every-fraud-and-anomaly-detection-system-a-complete-guide-82153bcbd2b1">SQL</a>, groupby, windowed comparisons, joins, but the mechanics and the failure modes are genuinely different once the work moves from a distributed query engine into an in-memory DataFrame. This guide covers the pandas patterns that actually show up in fraud analytics work, and the code-quality discipline that keeps that work trustworthy.</p><p><strong>What this guide covers:</strong></p><ol><li>Groupby and Conditional Aggregation</li><li>Multi-Column Groupby and Row Numbering</li><li>Rolling Windows and Behavioral Baselines</li><li>Time-Based Rolling Windows</li><li>Merge, Self-Merge, and Combining DataFrames</li><li>Vectorization vs. Loops</li><li>Writing Clean Analytical Code</li></ol><h3>1. Groupby and Conditional Aggregation</h3><h3>The pandas equivalent of GROUP BY and SUM(CASE WHEN...)</h3><pre>import pandas as pd</pre><pre>account_stats = (<br>    transactions_df<br>    .groupby(&#39;account_id&#39;)<br>    .agg(<br>        total_txns=(&#39;transaction_id&#39;, &#39;count&#39;),<br>        declined_count=(&#39;is_declined&#39;, &#39;sum&#39;),<br>        total_amount=(&#39;amount&#39;, &#39;sum&#39;)<br>    )<br>)<br>account_stats[&#39;decline_rate&#39;] = account_stats[&#39;declined_count&#39;] / account_stats[&#39;total_txns&#39;]</pre><p><strong>Given this input:</strong></p><pre>transaction_id | account_id | is_declined | amount<br>101             | 1           | True         | 50<br>102             | 1           | False        | 30<br>103             | 1           | False        | 120<br>104             | 2           | False        | 200<br>105             | 2           | True         | 15<br>106             | 3           | False        | 75</pre><p><strong>The output looks like:</strong></p><pre>account_id | total_txns | declined_count | total_amount<br>1           | 3           | 1               | 200<br>2           | 2           | 1               | 215<br>3           | 1           | 0               | 75</pre><p><strong>Two mechanics worth being precise about:</strong></p><p>is_declined.sum() works because pandas, like SQL, treats True/False as 1/0 under arithmetic, the direct equivalent of SUM(CASE WHEN isDeclined THEN 1 ELSE 0 END).</p><p>account_id becomes the <strong>index</strong> of the result, not a regular column, account_stats[&#39;account_id&#39;] would raise a KeyError. Calling .reset_index() moves it back to a normal column, needed before merging this result with another DataFrame later.</p><p>The named-aggregation syntax (total_txns=(&#39;transaction_id&#39;, &#39;count&#39;)) is the modern, preferred style, producing clean flat column names, unlike the older dict-style .agg({&#39;is_declined&#39;: &#39;sum&#39;}), which produces a confusing multi-level column structure when mixing multiple aggregation functions.</p><p>A high decline_rate (many failed authorization attempts relative to total attempts) is a classic card-testing signal.</p><h3>2. Multi-Column Groupby and Row Numbering</h3><h3>cumcount() as the pandas equivalent of ROW_NUMBER()</h3><pre>transactions_df[&#39;nth_time_in_category&#39;] = (<br>    transactions_df<br>    .sort_values(&#39;transaction_time&#39;)<br>    .groupby([&#39;account_id&#39;, &#39;merchant_category&#39;])<br>    .cumcount() + 1<br>)</pre><pre>first_time_txns = transactions_df[transactions_df[&#39;nth_time_in_category&#39;] == 1]</pre><p>cumcount() returns a 0-indexed running count of each row&#39;s position within its group, in whatever order the DataFrame is currently sorted, + 1 converts it to the same 1-indexed convention as SQL&#39;s ROW_NUMBER().</p><p><strong>The critical detail: </strong><strong>cumcount() respects the DataFrame&#39;s current row order, not any column value directly</strong>, which is why the explicit .sort_values(&#39;transaction_time&#39;) immediately beforehand is not optional, it is the entire mechanism that makes the count meaningful. Forgetting that sort is a genuinely common, silent bug, the code runs without error but produces a meaningless ordering.</p><p>This flags an account’s very first transaction in a specific merchant category, a common risk signal, an account’s first-ever international purchase or first-ever transaction in a high-risk category.</p><h3>3. Rolling Windows and Behavioral Baselines</h3><h3>The pandas equivalent of OVER (... ROWS BETWEEN ...)</h3><pre>transactions_df = transactions_df.sort_values([&#39;account_id&#39;, &#39;transaction_time&#39;])</pre><pre>transactions_df[&#39;rolling_avg&#39;] = (<br>    transactions_df<br>    .groupby(&#39;account_id&#39;)[&#39;amount&#39;]<br>    .transform(lambda x: x.shift(1).rolling(window=10, min_periods=1).mean())<br>)</pre><pre>transactions_df[&#39;rolling_std&#39;] = (<br>    transactions_df<br>    .groupby(&#39;account_id&#39;)[&#39;amount&#39;]<br>    .transform(lambda x: x.shift(1).rolling(window=10, min_periods=1).std())<br>)</pre><p><strong>Breaking down each piece, since this is denser than the SQL equivalent:</strong></p><p>.groupby(&#39;account_id&#39;)[&#39;amount&#39;] is the PARTITION BY accountId equivalent.</p><p>.transform(lambda x: ...) — transform (not agg) is what returns a result the <strong>same length as the original data</strong>, one value per row, aligned back to the source DataFrame, rather than collapsing to one row per group. This is the critical distinction: .agg() is pandas&#39;s GROUP BY, .transform() is pandas&#39;s window function.</p><p>.shift(1) shifts the series forward by one position <strong>before</strong> computing the rolling statistic, the pandas mechanism for excluding the current row from its own baseline, the direct equivalent of ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING instead of ... AND CURRENT ROW. Without it, a transaction&#39;s own amount would be baked into its own comparison baseline, diluting the anomaly it&#39;s supposed to help detect.</p><p>.rolling(window=10, min_periods=1) — window=10 is 10 PRECEDING. min_periods=1 means compute a value even with fewer than 10 prior rows available (an account&#39;s early transactions), rather than returning NaN until a full window exists, a deliberate choice: without it, an account&#39;s first several transactions would silently show NaN and drop out of any downstream filter on this column.</p><h3>Turning the baseline into an anomaly flag</h3><pre>ZSCORE_ANOMALY_THRESHOLD = 3.0</pre><pre>transactions_df[&#39;is_anomalous&#39;] = (<br>    transactions_df[&#39;amount&#39;] &gt; transactions_df[&#39;rolling_avg&#39;] + ZSCORE_ANOMALY_THRESHOLD * transactions_df[&#39;rolling_std&#39;]<br>)</pre><p>The same z-score logic from a SQL rolling window, now expressed with .shift() and .rolling() instead of a frame clause.</p><h3>min_periods should reflect what&#39;s statistically meaningful for the specific metric</h3><p>A rolling average of dollar amounts is reasonably informative from a single prior value. A rolling <strong>rate</strong> (like a decline rate) computed from only 1–2 prior events is statistically unreliable, one report out of one post is a 100% rate that means nothing. min_periods should be chosen per metric based on how much data is genuinely needed for it to be trustworthy, not copied uniformly across every rolling feature in a pipeline.</p><h3>4. Time-Based Rolling Windows</h3><h3>.rolling(&#39;10min&#39;) as the pandas equivalent of SQL&#39;s RANGE</h3><pre>transactions_df = transactions_df.set_index(&#39;transaction_time&#39;).sort_index()</pre><pre>transactions_df[&#39;txn_count_last_10min&#39;] = (<br>    transactions_df<br>    .groupby(&#39;account_id&#39;)[&#39;transaction_id&#39;]<br>    .transform(lambda x: x.rolling(&#39;10min&#39;).count())<br>)</pre><p><strong>The key mechanical difference from Section 3:</strong> passing a <strong>time-offset string</strong> (&#39;10min&#39;) instead of an integer to .rolling() switches pandas from a row-count-based window to a genuine time-based window, exactly the ROWS vs. RANGE distinction from SQL. This only works when the DataFrame&#39;s index is a proper datetime index (.set_index(&#39;transaction_time&#39;)), pandas needs an actual datetime axis to interpret what &quot;10 minutes&quot; means, it cannot apply a time-offset window against a plain row-position index.</p><p>This is how a velocity check, “how many transactions has this account made in the last 10 minutes of real clock time,” gets computed in pandas rather than as a fixed count of rows, which could span seconds or weeks depending on how active the account is.</p><h3>5. Merge, Self-Merge, and Combining DataFrames</h3><h3>merge() for enriching one DataFrame with another</h3><pre>transactions_df = pd.DataFrame({<br>    &#39;transaction_id&#39;: [101, 102, 103],<br>    &#39;account_id&#39;: [1, 2, 4],<br>    &#39;amount&#39;: [50, 200, 75]<br>})</pre><pre>accounts_df = pd.DataFrame({<br>    &#39;account_id&#39;: [1, 2, 3],<br>    &#39;account_status&#39;: [&#39;active&#39;, &#39;active&#39;, &#39;suspended&#39;]<br>})</pre><pre>merged = transactions_df.merge(accounts_df, on=&#39;account_id&#39;, how=&#39;left&#39;)</pre><pre>transaction_id | account_id | amount | account_status<br>101             | 1           | 50      | active<br>102             | 2           | 200     | active<br>103             | 4           | 75      | NaN</pre><p><strong>Why </strong><strong>how=&#39;left&#39; is the correct default for enrichment, not </strong><strong>how=&#39;inner&#39;:</strong> if a transaction&#39;s account has no matching metadata row (a data quality gap, a deleted account, a timing issue between source tables), an inner merge would silently <strong>drop that transaction entirely</strong>. A transaction disappearing from the analysis is not a clean result, it is a silent gap that could hide exactly the anomalous case being investigated. how=&#39;left&#39; preserves the row and surfaces the missing match as NaN, honest and inspectable, rather than an inner join&#39;s invisible row loss. The four how options map directly onto SQL: inner, left, right, and outer (full outer join, keeping unmatched rows from both sides).</p><p><strong>A genuine, common gotcha:</strong> merging on a column with duplicate keys on either side produces a row explosion, the same fan-out problem from relational data modeling, an order joining to multiple matching rows and multiplying the result unexpectedly. Worth checking df[&#39;key_column&#39;].duplicated().sum() on both sides before merging whenever the join key&#39;s uniqueness isn&#39;t already certain.</p><h3>Self-merge: the pandas equivalent of a SQL self-join</h3><pre>merged = transactions_df.merge(<br>    transactions_df,<br>    on=&#39;card_number_hash&#39;,<br>    suffixes=(&#39;_a&#39;, &#39;_b&#39;)<br>)</pre><pre>shared_card_pairs = merged[merged[&#39;account_id_a&#39;] &lt; merged[&#39;account_id_b&#39;]]</pre><p>transactions_df.merge(transactions_df, ...) joins a DataFrame to itself, suffixes=(&#39;_a&#39;, &#39;_b&#39;) is necessary because both sides share identical column names before the merge, pandas needs a way to distinguish account_id from the left copy versus the right copy. merged[&#39;account_id_a&#39;] &lt; merged[&#39;account_id_b&#39;] is the exact same inequality guard from a SQL self-join, applied as a boolean filter instead of a JOIN ... ON condition, still eliminating duplicate mirrored pairs and self-matches for the same reason: without it, every account would trivially match itself, and every real pair would appear twice, once from each direction.</p><p><strong>A genuine, pandas-specific warning worth taking seriously:</strong> a self-merge on a large DataFrame is one of the fastest ways to exhaust memory in pandas specifically, since the entire operation happens in RAM, with no query optimizer and no disk-spill safety net the way a warehouse engine has. Filtering the DataFrame down to a small, relevant subset (a single day, a suspected account cluster) before attempting a self-merge is not an optional nice-to-have optimization here, it is frequently the difference between the code running at all and crashing the kernel.</p><h3>pd.concat(): the pandas equivalent of UNION / UNION ALL</h3><pre>january_df = pd.DataFrame({&#39;account_id&#39;: [1, 2], &#39;amount&#39;: [100, 200]})<br>february_df = pd.DataFrame({&#39;account_id&#39;: [3, 1], &#39;amount&#39;: [150, 90]})</pre><pre>combined = pd.concat([january_df, february_df], ignore_index=True)</pre><p>By default, pd.concat() keeps each DataFrame&#39;s <strong>original index</strong>, resulting in duplicate index values across the combined result. This is a quiet bug source, later code doing combined.loc[0] expecting one specific row can silently return two rows instead, since the index is no longer unique. ignore_index=True produces a clean, freshly numbered result, worth treating as a near-reflexive default whenever concat() is used to stack DataFrames meant to be treated as one continuous dataset.</p><pre>combined_deduped = pd.concat([january_df, february_df], ignore_index=True).drop_duplicates()</pre><p>.drop_duplicates() after a concat() is the UNION-style dedup, worth the same &quot;only when duplicates are actually possible and unwanted&quot; discipline as UNION in SQL, since deduplication genuinely requires comparing rows, not free work.</p><h3>6. Vectorization vs. Loops</h3><h3>The single most important pandas performance concept</h3><pre># SLOW: iterating row by row with a Python for-loop<br>flags = []<br>for idx, row in transactions_df.iterrows():<br>    if row[&#39;amount&#39;] &gt; row[&#39;rolling_avg&#39;] + 3 * row[&#39;rolling_std&#39;]:<br>        flags.append(True)<br>    else:<br>        flags.append(False)<br>transactions_df[&#39;is_anomalous&#39;] = flags</pre><pre># FAST: vectorized, the entire comparison runs as one operation across the whole column<br>transactions_df[&#39;is_anomalous&#39;] = (<br>    transactions_df[&#39;amount&#39;] &gt; transactions_df[&#39;rolling_avg&#39;] + 3 * transactions_df[&#39;rolling_std&#39;]<br>)</pre><p><strong>Why the vectorized version is dramatically faster:</strong> iterrows() processes the DataFrame one Python-level row at a time, incurring real interpreter overhead on every single row, often 50-100x slower on a large DataFrame. The vectorized version calls into pandas&#39;s underlying NumPy-based implementation once, operating on the entire column as a single batched array operation, no per-row overhead at all. This is the same underlying principle as choosing a set-based JOIN over a correlated subquery in SQL, do the work once across the whole set, not once per row.</p><h3>The common trap: .apply(axis=1) looks vectorized, but isn&#39;t</h3><pre># SLOW: apply() with a row-wise lambda still iterates under the hood<br>transactions_df[&#39;risk_score&#39;] = transactions_df.apply(<br>    lambda row: row[&#39;amount&#39;] * 0.3 + row[&#39;decline_rate&#39;] * 0.7, axis=1<br>)</pre><pre># FAST: vectorized arithmetic directly on the columns<br>transactions_df[&#39;risk_score&#39;] = transactions_df[&#39;amount&#39;] * 0.3 + transactions_df[&#39;decline_rate&#39;] * 0.7</pre><p>.apply(..., axis=1) looks idiomatic, since it&#39;s not an explicit for loop, but pandas still iterates row by row internally to execute the lambda, it just hides the iteration behind a cleaner-looking API. Whenever row-wise logic is expressible as simple column-level arithmetic or boolean logic, which is true for most fraud feature engineering (thresholds, ratios, flags), writing it as direct column operations is both cleaner and genuinely faster. A loop or .apply() remains reasonable when per-row logic is complex, conditionally irregular, or calls an external function per row, the goal isn&#39;t &quot;never loop,&quot; it&#39;s recognizing that most of this domain&#39;s feature engineering cleanly vectorizes and reaching for a loop first is usually the wrong default.</p><h3>7. Writing Clean Analytical Code</h3><h3>Problem 1: notebook mess — implicit state and execution-order dependence</h3><pre># Cell 1: df = pd.read_csv(&#39;transactions.csv&#39;)<br># Cell 2: df = df[df[&#39;amount&#39;] &gt; 0]<br># Cell 3: df[&#39;decline_rate&#39;] = df.groupby(&#39;account_id&#39;)[&#39;is_declined&#39;].transform(&#39;mean&#39;)<br># ...12 cells later, Cell 2 gets re-run out of order after a new filter was added to Cell 5...</pre><p>A notebook’s cells can be executed in any order and any number of times, and a shared df is silently mutated and re-mutated along the way. If a later cell filters df further and an earlier cell then gets re-run, df no longer represents what any single cell, read top to bottom, would suggest. This is a real, documented cause of incorrect analysis making it into a report, the filter logic genuinely was correct the first time the cells ran in order, but the final state of df reflects an undocumented execution history nobody can reconstruct later.</p><p><strong>The fix: functions with explicit inputs and outputs, no shared mutable global state</strong></p><pre>def load_transactions(path: str) -&gt; pd.DataFrame:<br>    return pd.read_csv(path)</pre><pre>def filter_valid_transactions(df: pd.DataFrame) -&gt; pd.DataFrame:<br>    return df[df[&#39;amount&#39;] &gt; 0].copy()</pre><pre>def add_decline_rate(df: pd.DataFrame) -&gt; pd.DataFrame:<br>    df = df.copy()<br>    df[&#39;decline_rate&#39;] = df.groupby(&#39;account_id&#39;)[&#39;is_declined&#39;].transform(&#39;mean&#39;)<br>    return df</pre><pre>raw = load_transactions(&#39;transactions.csv&#39;)<br>valid = filter_valid_transactions(raw)<br>enriched = add_decline_rate(valid)</pre><p><strong>Is </strong><strong>.copy() inside every function wasteful?</strong> It genuinely is a real cost, not a free habit, chaining several functions that each call .copy() does create several separate blocks of memory over a pipeline, not one shared object being incrementally modified. The tradeoff is real: .copy() trades some memory and CPU for guaranteed correctness, no ambiguity about whether a function&#39;s output secretly shares memory with its input. Without it, a filtered DataFrame is sometimes a genuine independent object and sometimes a view sharing memory with the source, depending on pandas internals that are notoriously hard to predict reliably, this is precisely what pandas&#39;s SettingWithCopyWarning exists to flag. Code that works correctly in testing can silently corrupt an upstream DataFrame in production purely because of how pandas happened to allocate memory for a specific filter, not because the filter logic itself was wrong.</p><p><strong>A scale-aware recommendation, rather than an absolute rule:</strong> on small-to-medium DataFrames (the realistic case for most exploratory fraud analysis), the cost of .copy() is negligible, milliseconds, against a well-documented risk, defaulting to it is the right call. On genuinely large DataFrames where memory pressure is a real, active concern, be more deliberate: only copy at the boundaries where a mutation is actually about to happen, rather than defensively copying in every pass-through function regardless of whether it modifies anything.</p><h3>Problem 2: magic numbers and undocumented thresholds</h3><pre># Bad: what is 3? Why 3?<br>df[&#39;is_anomalous&#39;] = df[&#39;amount&#39;] &gt; df[&#39;rolling_avg&#39;] + 3 * df[&#39;rolling_std&#39;]</pre><p>These thresholds are not incidental implementation details, they are business decisions with real consequences, set too aggressively they generate alert fatigue for an investigations team; set too loosely they let real fraud through. Burying them as unlabeled literals scattered through a script means nobody, including the original author months later, can quickly answer what the current definition of anomalous actually is, or safely change it.</p><pre># Anomaly thresholds, tuned against Q3 2024 fraud review outcomes.<br>ZSCORE_ANOMALY_THRESHOLD = 3.0<br>HIGH_DECLINE_RATE_THRESHOLD = 0.7<br>MIN_TRANSACTIONS_FOR_RELIABLE_RATE = 5</pre><p>Named, commented constants at the top of the script, easy to find, easy to justify, easy to change in one place.</p><h3>Problem 3: no separation between computing a feature and deciding what’s anomalous</h3><p>A single line that computes a rolling mean, computes a rolling standard deviation (recomputing the same groupby twice, wastefully), and applies a business threshold all at once is hard to read and impossible to test in pieces, there’s no way to verify “is the rolling mean correct” independently from “is the threshold logic correct.”</p><pre>def add_rolling_baseline(df: pd.DataFrame, window: int = 10) -&gt; pd.DataFrame:<br>    &quot;&quot;&quot;Adds a per-account rolling mean/std of transaction amount, excluding the current transaction.&quot;&quot;&quot;<br>    df = df.copy()<br>    grouped = df.groupby(&#39;account_id&#39;)[&#39;amount&#39;]<br>    df[&#39;rolling_avg&#39;] = grouped.transform(lambda x: x.shift(1).rolling(window, min_periods=1).mean())<br>    df[&#39;rolling_std&#39;] = grouped.transform(lambda x: x.shift(1).rolling(window, min_periods=1).std())<br>    return df</pre><pre>def flag_amount_anomalies(df: pd.DataFrame, zscore_threshold: float = ZSCORE_ANOMALY_THRESHOLD) -&gt; pd.DataFrame:<br>    &quot;&quot;&quot;Flags transactions whose amount exceeds the rolling baseline by more than zscore_threshold std deviations.&quot;&quot;&quot;<br>    df = df.copy()<br>    df[&#39;is_anomalous&#39;] = df[&#39;amount&#39;] &gt; df[&#39;rolling_avg&#39;] + zscore_threshold * df[&#39;rolling_std&#39;]<br>    return df</pre><p>Each function now has one clear job and one clear thing to test independently. It also means the threshold itself can be swapped, from a z-score rule to a percentile-based rule, without touching the feature computation logic at all, the same “each layer has exactly one job” discipline behind a well-structured staging-to-marts data pipeline, now applied inside a Python script.</p><h3>Problem 4: no handling of the edge cases real fraud data actually has</h3><pre># Fragile: assumes every account has enough history, no NaNs, clean data<br>df[&#39;decline_rate&#39;] = df[&#39;declined_count&#39;] / df[&#39;total_txns&#39;]</pre><p>An account with total_txns = 0 produces a division by zero. In pandas this doesn&#39;t raise an error, it silently produces inf or NaN, which can then behave unpredictably in a later .sort_values() call, sometimes sorting to the very top or bottom in a way that looks like a legitimate extreme value but is actually a data artifact.</p><pre>def add_decline_rate(df: pd.DataFrame, min_txns: int = MIN_TRANSACTIONS_FOR_RELIABLE_RATE) -&gt; pd.DataFrame:<br>    df = df.copy()<br>    df[&#39;decline_rate&#39;] = df[&#39;declined_count&#39;] / df[&#39;total_txns&#39;].replace(0, pd.NA)<br>    df[&#39;decline_rate_reliable&#39;] = df[&#39;total_txns&#39;] &gt;= min_txns<br>    return df</pre><p>.replace(0, pd.NA) is pandas&#39;s direct equivalent of SQL&#39;s NULLIF(total_txns, 0), swapping a genuinely undefined denominator out for a proper missing-value marker before the division happens, rather than letting an ambiguous inf/NaN silently propagate. The added decline_rate_reliable flag makes the small-sample-size concern explicit and filterable, rather than implicitly hoping nobody queries an unreliable rate without realizing it.</p><h3>Problem 5: no way to verify the code is correct</h3><pre>def test_add_decline_rate():<br>    test_df = pd.DataFrame({<br>        &#39;account_id&#39;: [1, 2, 3],<br>        &#39;declined_count&#39;: [5, 0, 2],<br>        &#39;total_txns&#39;: [10, 0, 3],<br>    })<br>    result = add_decline_rate(test_df, min_txns=5)</pre><pre>    assert result.loc[0, &#39;decline_rate&#39;] == 0.5<br>    assert pd.isna(result.loc[1, &#39;decline_rate&#39;])           # zero-txn account: correctly undefined<br>    assert result.loc[2, &#39;decline_rate_reliable&#39;] == False    # only 3 txns, below the reliability threshold</pre><p>The functions from Problems 3 and 4 are exactly the kind of code where a subtle logic bug, a forgotten .shift(1), an inverted comparison, a wrong min_periods, can silently produce a plausible-looking but wrong result, one that might not get caught for weeks, since a slightly-wrong decline rate doesn&#39;t look obviously broken the way a query returning zero rows would. A small test against a known, hand-constructed input with a hand-verified expected output catches this class of bug immediately. This doesn&#39;t require a large test suite, even 2-3 well-chosen edge cases per function, the zero-division case, the below-threshold case, one clearly normal case, catches the overwhelming majority of real bugs in this kind of analytical code.</p><h3>The One Unifying Idea</h3><p>Nearly every technique in this guide answers the same underlying question in a slightly different way:</p><blockquote><strong><em>How does this event or entity compare to a well-defined baseline, and how do I compute that comparison once, cleanly, and reproducibly, rather than by accident or one row at a time?</em></strong></blockquote><blockquote>groupby and transform build the baseline. .shift(1) keeps the current row from contaminating it. Vectorization computes the comparison across the whole dataset at once instead of row by row. And the clean-code discipline, explicit functions, named thresholds, edge-case handling, tests, exists to make sure that comparison, once correct, stays correct the next time the code runs.</blockquote><h3>Quick-Reference Summary</h3><ul><li><strong>.groupby().agg() with named aggregation is pandas&#39;s </strong><strong>GROUP BY</strong> — boolean .sum() is the direct equivalent of SUM(CASE WHEN...).</li><li><strong>groupby() turns the grouping column into the index</strong>, not a regular column — call .reset_index() before merging the result elsewhere.</li><li><strong>cumcount() is </strong><strong>ROW_NUMBER(), but depends entirely on the DataFrame already being correctly sorted</strong> — a missing .sort_values() beforehand is a silent, common bug.</li><li><strong>.transform(), not </strong><strong>.agg(), is pandas&#39;s window function</strong> — it preserves row-level output instead of collapsing to one row per group.</li><li><strong>.shift(1) before </strong><strong>.rolling() excludes the current row from its own baseline</strong> — without it, an anomaly dilutes the very threshold meant to catch it.</li><li><strong>min_periods should reflect what&#39;s statistically meaningful for the specific metric</strong>, not be copied uniformly across every rolling feature.</li><li><strong>.rolling(&#39;10min&#39;) on a datetime index is pandas&#39;s </strong><strong>RANGE BETWEEN INTERVAL ... PRECEDING</strong> — a genuine time window, not a fixed row count.</li><li><strong>how=&#39;left&#39; is the safer default for enrichment merges</strong> — how=&#39;inner&#39; silently drops unmatched rows, hiding exactly the kind of gap worth investigating.</li><li><strong>Check for duplicate join keys before merging</strong> — a non-unique key causes a silent row-count explosion.</li><li><strong>Self-</strong><strong>merge() mirrors a SQL self-join, inequality guard included</strong>, but is far riskier in pandas specifically, since everything happens in RAM with no disk-spill safety net.</li><li><strong>pd.concat() defaults to </strong><strong>UNION ALL behavior but preserves duplicate index values</strong> — ignore_index=True should be close to reflexive.</li><li><strong>Vectorized column operations beat both explicit loops and </strong><strong>.apply(axis=1)</strong>, which looks vectorized but still iterates under the hood, likely the single most testable pandas performance concept in an interview.</li><li><strong>.copy() is a real cost, not a free habit</strong> — worth defaulting to on small-to-medium data for correctness, but worth being more deliberate about on genuinely large DataFrames.</li><li><strong>Name every threshold as a constant with a comment explaining its business rationale</strong> — these are business decisions, not incidental code details.</li><li><strong>Separate feature computation from rule application into distinct, individually testable functions.</strong></li><li><strong>Guard against zero-denominator and small-sample-size cases explicitly</strong> — .replace(0, pd.NA) is pandas&#39;s NULLIF.</li><li>A handful of <strong>targeted tests per function</strong>, with hand-verified <strong>expected outputs</strong>, catches the subtle logic bugs that are common and easy to miss in analytical code<strong>.</strong></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a92d3c518917" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[So You Want to Be an Analytics Engineer?]]></title>
            <link>https://bysuchi.medium.com/so-you-want-to-be-an-analytics-engineer-d4decadb7afe?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/d4decadb7afe</guid>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[careers]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Fri, 21 Aug 2026 12:54:14 GMT</pubDate>
            <atom:updated>2026-08-21T12:54:14.606Z</atom:updated>
            <content:encoded><![CDATA[<p>Everything aspiring Analytics Engineers need to know before making the leap, without the jargon</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NuhENnsvv3YRyIJLYP1RTg.avif" /><figcaption>Image from <a href="https://unsplash.com/photos/silhouette-of-virtual-human-on-brain-delta-wave-form-3d-illustration-represent-meditation-and--deep-sleep-therapy-aHXH23QRn7w">Unsplash</a></figcaption></figure><p>The analytics engineer (AE) is one of the most exciting and misunderstood roles in the data world right now. It is new enough that most people have never heard of it, but established enough that companies like Netflix, Airbnb, Stripe, and Shopify are actively hiring for it.</p><p>Here is everything you need to know.</p><h3>What Is an Analytics Engineer?</h3><p>Think of the data world as a factory assembly line.</p><p>The <strong>data engineer</strong> builds the conveyor belts and machinery — the infrastructure that moves raw data from one place to another. The <strong>data analyst</strong> stands at the end of the line and inspects the finished product, turning it into insights and reports.</p><p>The <strong>analytics engineer</strong> works in the middle. They take the raw, messy data the engineer brought in and transform it into something clean, reliable, and ready for the analyst to actually use.</p><p>Their defining skill is solving a problem once and building a system so that everyone benefits from that solution forever.</p><p>The term itself only officially appeared in 2019, making it one of the newest titles in the data profession. It was pioneered largely by dbt Labs, the company whose open-source tool — dbt (data build tool) — became the backbone of modern analytics engineering.</p><h3>What Does an AE Actually Do Day-to-Day?</h3><p>Here is a practical look at what the work involves:</p><ul><li><strong>Transforming raw data</strong> into clean, tested, and well-documented datasets using SQL and dbt</li><li><strong>Building data models</strong> in structured tables and schemas that make it easy for analysts to answer business questions</li><li><strong>Writing data quality tests</strong> to catch errors before they reach dashboards or reports</li><li><strong>Documenting everything with </strong>what each dataset means, where it came from, and how it should be used</li><li><strong>Bridging the gap</strong> between technical data engineers and non-technical business stakeholders</li><li><strong>Enabling self-service analytics</strong> so that teams across the company can find and use trusted data on their own</li></ul><p>If that sounds like a hybrid between engineering discipline and business understanding, that is exactly what it is.</p><h3>Is It a New Role?</h3><p>Yes and no. The job title is genuinely recent, but the underlying need has always existed.</p><p>Before AEs had a formal name, senior analysts were doing transformation work on the side, or data engineers were reluctantly building business-facing models that were not really their specialty. The modern data stack cloud warehouses like Snowflake and BigQuery, ingestion tools like Fivetran, and transformation tools like dbt -created the conditions for this work to become a dedicated role with its own identity.</p><h3>What Skills Do You Need?</h3><h3>The Technical Core</h3><p><a href="https://medium.com/@bysuchi/query-optimization-concepts-for-an-analytics-engineer-3000f9dfbd4a"><strong>SQL</strong></a> is the single most important skill. Not just basic queries -you need to be comfortable with window functions, CTEs, complex aggregations, and query optimization. Clean, modular, well-documented SQL is a professional standard in this role.</p><p><a href="https://medium.com/@bysuchi/dbt-fundamentals-for-analytics-engineers-a-complete-guide-6f10cd2ba6c3"><strong>dbt</strong></a> is the defining tool of the profession. Almost every AE role requires it. You will use it to build and test data models, write documentation, and manage your transformation code like a software engineer manages application code.</p><p><a href="https://medium.com/@bysuchi/fact-dimension-grain-the-data-modeling-guide-nobody-gives-you-c44857e3d220"><strong>Data modeling</strong></a> is the conceptual skill that separates good AEs from great ones. This means understanding how to structure data using frameworks like Kimball’s star schema — facts, dimensions, and the relationships between them. It is less about memorizing theory and more about knowing how to design data that is easy to query and hard to misuse.</p><p><strong>Cloud data warehouses</strong> like Snowflake, BigQuery, and Redshift are the big three. You need to understand how to work within them, not just query them.</p><p><strong>Git and version control</strong> because AEs treat their SQL like production code. Pull requests, code reviews, and CI/CD pipelines are a normal part of the job.</p><p><strong>Python</strong> is useful but not the primary focus. You will use it for scripting, pipeline support, and automation rather than machine learning or statistics.</p><h3>The Non-Technical Skills (Often Underestimated)</h3><p>Here is what most people miss: the technical skills get you the interview, but the non-technical skills determine how far you go.</p><p><strong>Stakeholder communication</strong> is central to the role. You will regularly need to take a vague business question — “why are our numbers different in two dashboards?” — and translate it into a precise, testable technical problem. Then translate the solution back into plain language.</p><p><strong>Business acumen</strong> matters more than most AE job postings admit. Understanding how your company makes money, what metrics the business actually cares about, and how data flows through real-world processes is what makes the difference between a data model that is technically correct and one that is genuinely useful.</p><p><strong>Documentation habits</strong> are a core deliverable, not an afterthought. Your data dictionary, model descriptions, and lineage notes are what allow other people to trust and use your work without needing to ask you every time.</p><h3>How Do People Get Into AE Roles?</h3><p>There is no single path. The most common entry routes are:</p><p><strong>Data analysts</strong> are arguably the most natural fit for the AE role. They already understand the business side of data where they know how to talk to stakeholders, how to frame a question, and what a useful answer actually looks like. Their SQL skills are usually solid, and they have spent enough time fighting with unreliable data to appreciate why clean, well-structured models matter. The gap to close is primarily technical: learning dbt, picking up version control habits, and shifting from consuming data to producing the infrastructure that others consume. For many analysts, this transition feels less like a career change and more like a natural level-up.</p><p><strong>Data engineers</strong> bring the opposite strengths. They know how data pipelines are built, how warehouses are structured, and how to write code that runs reliably in production. What they often lack is proximity to the business like an understanding of what the data is actually being used for and by whom. Moving into an AE role means reorienting from building infrastructure for its own sake toward building infrastructure that directly serves business questions. For engineers who have grown frustrated being disconnected from the impact of their work, that shift can be deeply motivating.</p><p><strong>BI engineers</strong> have perhaps the most seamless transition of all. As the industry moved away from traditional drag-and-drop BI platforms toward code-first tools like dbt and Looker, many BI engineers found themselves doing AE work without the title. They understand data modeling, they know how business users consume data, and they are comfortable sitting between technical and non-technical teams. In many cases, BI engineers are already analytics engineers where they just need the vocabulary and tooling to formalize it.</p><p><strong>Data scientists</strong> and this one surprises people. Data scientists are actually well-positioned for the transition for several reasons. They already have strong Python skills, understand data quality deeply from years of cleaning data before modeling it, and often have the business context awareness that many pure engineers lack. The shift requires learning dbt, investing in formal data modeling concepts like Kimball star schemas, and importantly adjusting their mindset. Data science is about producing answers: a prediction, a model, an insight. Analytics engineering is about building the infrastructure that lets everyone else find their own answers reliably. For data scientists who find themselves spending 60 to 80 percent of their time wrangling data instead of modeling it, moving into an AE role is essentially choosing to solve that problem permanently rather than working around it every day. If that sounds appealing, the transition is more natural than it might first appear.</p><p>A computer science degree is not required for any of these paths. Many working AEs came from finance, economics, or even non-technical backgrounds and built their skills on the job and through self-study.</p><h3>What Are the Challenges?</h3><p>Being honest about the hard parts is important too.</p><p>The AE sits between two teams, engineering and analytics, and sometimes does not have a clear home in either. Proving impact can be harder than for a data analyst who surfaces a specific insight, because AE work is infrastructure, not a finished product. And data quality problems that originate upstream often land on the AE to diagnose and explain, even when they did not cause them.</p><h3>What About AI? Is the Role Under Threat?</h3><p>This is the most common question in the field right now, and the short answer is: no, but the job is changing.</p><p>AI tools like Cursor and GitHub Copilot are making it faster to write SQL and Python, and AEs who use them well are dramatically more productive. But the most valuable part of the job which is understanding what to model, why, and for whom is exactly what AI tools struggle with. The business translation layer, the contextual judgment, the ability to push back on a stakeholder’s request because you understand what they actually need: those skills are becoming more valuable, not less.</p><p>The AEs who will thrive are the ones who use AI to accelerate the technical work while doubling down on the human skills that AI cannot replicate.</p><h3>Final Thought</h3><p>Analytics engineering is one of the most accessible high-impact data roles available right now. It does not require a decade of software engineering experience. It does not require a PhD. It requires deep SQL skills, strong data modeling instincts, proficiency with dbt, and the ability to speak fluently with both engineers and business people.</p><p>If you are sitting at the intersection of technical and business thinking and wondering where you fit in the data world, you might have just found your answer.</p><p><em>Have questions about breaking into analytics engineering? Drop them in the comments.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d4decadb7afe" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The SQL Patterns Behind Every Fraud and Anomaly Detection System: A Complete Guide]]></title>
            <link>https://bysuchi.medium.com/the-sql-patterns-behind-every-fraud-and-anomaly-detection-system-a-complete-guide-82153bcbd2b1?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/82153bcbd2b1</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[interview]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[fraud-detection]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Wed, 19 Aug 2026 21:07:33 GMT</pubDate>
            <atom:updated>2026-08-19T21:07:33.805Z</atom:updated>
            <content:encoded><![CDATA[<p>Window functions, self-joins, sessionization and outlier scoring — the exact query patterns fraud and risk teams use to tell normal behavior from suspicious behavior</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qotPjfcnPWMRzIhNu14hig.avif" /><figcaption>Image from <a href="https://unsplash.com/photos/white-bullet-type-camera-8EYMcqG5GRU">Unsplash</a></figcaption></figure><p>Fraud detection SQL looks deceptively similar to ordinary reporting SQL, the same window functions, joins, and aggregations, but it gets applied with a distinctly different mindset: comparing an entity’s behavior against its own history, against the whole population, and against other entities it might be secretly connected to. This guide walks through the core SQL patterns that actually show up in fraud and anomaly detection work, building from the mechanics you already know (window functions, joins, aggregation) toward the specific behavioral analytics techniques, velocity checks, sessionization, outlier scoring, that this domain relies on every day.</p><p>If you want to first learn the basic to building these systems as an Analytics Engineer go to these links <a href="https://bysuchi.medium.com/query-optimization-concepts-for-an-analytics-engineer-3000f9dfbd4a">first</a>, <a href="https://bysuchi.medium.com/fact-dimension-grain-the-data-modeling-guide-nobody-gives-you-c44857e3d220">second</a> and <a href="https://bysuchi.medium.com/dbt-fundamentals-for-analytics-engineers-a-complete-guide-6f10cd2ba6c3">third</a>.</p><p><strong>What this guide covers:</strong></p><ol><li>Window Functions for Behavioral Analysis</li><li>Aggregations for Fraud Signals</li><li>Complex Joins for Relationship Detection</li><li>Sessionization</li><li>Outlier and Anomaly Detection Patterns</li><li>Rolling Aggregates and Moving Averages</li><li>Query Optimization Applied to Fraud Patterns</li></ol><h3>1. Window Functions for Behavioral Analysis</h3><h3>Why fraud-focused window functions differ from typical reporting use</h3><p>Standard window function use cases (ranking, running totals) usually answer “what’s the top N” or “what’s the cumulative total.” Fraud detection uses the exact same mechanics, PARTITION BY, ORDER BY, frame clauses, to answer a different question: how does this specific event compare to this specific account&#39;s own recent, normal behavior.</p><h3>Pattern 1: Velocity checks — time between consecutive events</h3><pre>SELECT<br>    accountId,<br>    transactionId,<br>    transactionTime,<br>    LAG(transactionTime) OVER (PARTITION BY accountId ORDER BY transactionTime) AS prevTransactionTime<br>FROM transactions</pre><p>PARTITION BY accountId resets the &quot;previous&quot; value per account, so one account&#39;s history never leaks into another&#39;s.</p><pre>SELECT * FROM (<br>    SELECT<br>        accountId,<br>        transactionId,<br>        transactionTime,<br>        DATEDIFF(second,<br>            LAG(transactionTime) OVER (PARTITION BY accountId ORDER BY transactionTime),<br>            transactionTime<br>        ) AS secondsSinceLastTxn<br>    FROM transactions<br>) t<br>WHERE secondsSinceLastTxn &lt; 5</pre><p>A gap of a few seconds between transactions on the same account is often too fast to be genuine human behavior. The first transaction for any account naturally gets NULL for this column, which is correctly excluded by the filter, since NULL &lt; 5 evaluates to UNKNOWN, not TRUE.</p><h3>Pattern 2: Rolling counts over a genuine time window — RANGE vs. ROWS</h3><pre>SELECT<br>    accountId,<br>    transactionId,<br>    transactionTime,<br>    COUNT(*) OVER (<br>        PARTITION BY accountId<br>        ORDER BY transactionTime<br>        RANGE BETWEEN INTERVAL &#39;10&#39; MINUTE PRECEDING AND CURRENT ROW<br>    ) AS txnCountLast10Min<br>FROM transactions</pre><p>This is one of the sharpest distinctions in fraud SQL, worth having crisp and ready:</p><pre>-- ROWS: always exactly the last 2 ROWS, regardless of how much time<br>-- separates them. Could be 3 seconds apart or 3 weeks apart.<br>SUM(amount) OVER (PARTITION BY accountId ORDER BY transactionTime ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)</pre><pre>-- RANGE: every row within the last 10 minutes of actual CLOCK TIME<br>SUM(amount) OVER (PARTITION BY accountId ORDER BY transactionTime RANGE BETWEEN INTERVAL &#39;10&#39; MINUTE PRECEDING AND CURRENT ROW)</pre><p>ROWS counts a fixed number of physical rows back, blind to elapsed time. RANGE counts based on the actual value in the ordering column, correctly capturing &quot;everything within this real time window,&quot; which is what a velocity rule genuinely needs.</p><h3>Pattern 3: A rolling behavioral baseline — why the current row must be excluded</h3><pre>SELECT<br>    accountId,<br>    transactionId,<br>    transactionTime,<br>    amount,<br>    AVG(amount) OVER (<br>        PARTITION BY accountId<br>        ORDER BY transactionTime<br>        ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING<br>    ) AS avgOfPrior10Txns,<br>    STDDEV(amount) OVER (<br>        PARTITION BY accountId<br>        ORDER BY transactionTime<br>        ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING<br>    ) AS stddevOfPrior10Txns<br>FROM transactions</pre><p><strong>Why the frame ends at </strong><strong>1 PRECEDING, not </strong><strong>CURRENT ROW:</strong> if the frame included the current row, the very transaction being evaluated would be baked into its own comparison baseline. A single, genuinely fraudulent, unusually large transaction would pull its own rolling average upward, diluting the signal meant to catch it. Ending the frame one row earlier keeps the baseline as &quot;what this account looked like <em>before</em> this event,&quot; the only fair thing to compare the current event against.</p><pre>SELECT *,<br>    CASE WHEN amount &gt; avgOfPrior10Txns + (3 * stddevOfPrior10Txns) THEN 1 ELSE 0 END AS isAnomalousAmount<br>FROM (-- query above --) t</pre><p>Flagging anything more than three standard deviations above an account’s own recent average is a standard, simple outlier rule, computed per-account with a rolling window rather than one global cutoff.</p><h3>Pattern 4: Ratio against a trailing baseline</h3><pre>SELECT<br>    accountId,<br>    transactionDate,<br>    dailyTotal,<br>    AVG(dailyTotal) OVER (<br>        PARTITION BY accountId<br>        ORDER BY transactionDate<br>        ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING<br>    ) AS trailing30dayAvg,<br>    dailyTotal / NULLIF(<br>        AVG(dailyTotal) OVER (<br>            PARTITION BY accountId<br>            ORDER BY transactionDate<br>            ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING<br>        ), 0<br>    ) AS ratioToBaseline<br>FROM daily_account_totals</pre><p>NULLIF(..., 0) prevents a division-by-zero error for an account with zero spending in its trailing window, a real edge case for new or dormant accounts. Because NULLIF swaps the 0 out for a NULL before the division runs, and any arithmetic involving NULL in SQL returns NULL, the resulting ratioToBaseline is NULL, not 0, correctly representing &quot;undefined&quot; rather than falsely claiming &quot;0% of baseline.&quot; A NULL ratio also naturally drops out of a filter like WHERE ratioToBaseline &gt; 5, since NULL &gt; 5 is UNKNOWN, not TRUE.</p><p>A ratioToBaseline of 8 means today&#39;s activity is 8x this specific account&#39;s own normal average, a more robust signal than a fixed dollar threshold, since it adapts to each account&#39;s individual baseline instead of applying one global cutoff that would either constantly flag naturally high-spending accounts or miss real anomalies on low-spending ones.</p><h3>Pattern 5: First-time-ever behavior with a multi-column partition</h3><pre>SELECT<br>    accountId,<br>    transactionId,<br>    merchantCategory,<br>    transactionTime,<br>    ROW_NUMBER() OVER (<br>        PARTITION BY accountId, merchantCategory<br>        ORDER BY transactionTime<br>    ) AS nthTimeInThisCategory<br>FROM transactions<br>WHERE nthTimeInThisCategory = 1</pre><p>Partitioning on two columns together marks the very first time a specific account transacted in a specific category, a common risk signal, an account’s first-ever international purchase or first-ever transaction in a high-risk merchant category.</p><h3>2. Aggregations for Fraud Signals</h3><h3>Pattern 1: Conditional aggregation — multiple metrics in one pass</h3><pre>SELECT<br>    accountId,<br>    COUNT(*) AS totalTransactions,<br>    SUM(CASE WHEN transactionType = &#39;international&#39; THEN 1 ELSE 0 END) AS internationalCount,<br>    SUM(CASE WHEN isDeclined = TRUE THEN 1 ELSE 0 END) AS declinedCount,<br>    SUM(CASE WHEN isDeclined = TRUE THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) AS declineRate<br>FROM transactions<br>GROUP BY accountId</pre><p>A high declineRate, many failed authorization attempts relative to total attempts, is a classic card-testing signal: someone systematically running stolen card numbers through a checkout flow, most of which fail before one succeeds. Computing several conditional metrics together avoids scanning the underlying table multiple times, the same &quot;avoid redundant scans&quot; principle that applies whether the metric is a decline rate or anything else.</p><h3>Pattern 2: HAVING for group-level velocity rules</h3><pre>SELECT<br>    accountId,<br>    COUNT(*) AS txnCount,<br>    SUM(amount) AS totalAmount<br>FROM transactions<br>WHERE transactionDate &gt;= CURRENT_DATE - 1<br>GROUP BY accountId<br>HAVING SUM(amount) &gt; 5000 AND COUNT(*) &gt; 10</pre><p>HAVING is where actual velocity and volume rules get expressed, since SUM(amount) &gt; 5000 is a condition on the aggregated result, which only exists after grouping. Filtering rows first with WHERE (restrict to today) and filtering groups after with HAVING (unusually high volume and count) mirrors the standard WHERE-before/HAVING-after order of operations.</p><p><strong>A worthwhile clarification:</strong> referencing SUM(amount) in both the SELECT list and the HAVING clause does <strong>not</strong> compute it twice. The aggregate is computed once per group as part of the single GROUP BY pass; both clauses simply reference that already-computed value. A CTE that moves the aggregation into its own step and filters with WHERE instead of HAVING on the outer query is functionally and computationally identical, same single scan, same cost, purely a readability preference, not an optimization.</p><h3>Pattern 3: ROLLUP for multi-grain summaries</h3><pre>SELECT<br>    merchantCategory,<br>    transactionType,<br>    SUM(amount) AS totalAmount<br>FROM transactions<br>GROUP BY ROLLUP(merchantCategory, transactionType)</pre><p>Produces detail rows per combination, subtotal rows per merchantCategory, and one grand total, all in a single scan, useful for a fraud-ops dashboard showing where volume is concentrated at every level of granularity at once, instead of running and UNION ALL-ing several separately-grained GROUP BY queries. GROUPING(transactionType) disambiguates a rollup-generated subtotal NULL from a genuinely missing value in the raw data.</p><h3>Pattern 4: COUNT(DISTINCT ...) for diversity of behavior</h3><pre>SELECT<br>    accountId,<br>    COUNT(DISTINCT deviceId) AS distinctDevices,<br>    COUNT(DISTINCT ipAddress) AS distinctIpAddresses<br>FROM transactions<br>WHERE transactionDate &gt;= CURRENT_DATE - 1<br>GROUP BY accountId</pre><p>A single account transacting from many distinct devices or IPs in a short window is a classic account-takeover signal, legitimate activity is usually concentrated on one or two devices. This is a case where COUNT(DISTINCT)&#39;s deduplication cost is genuinely earning its keep, the diversity itself is the signal, not a reflexive habit.</p><h3>Pattern 5: Period-over-period comparison</h3><pre>SELECT<br>    accountId,<br>    SUM(CASE WHEN transactionDate &gt;= CURRENT_DATE - 7 THEN amount ELSE 0 END) AS thisWeekAmount,<br>    SUM(CASE WHEN transactionDate &gt;= CURRENT_DATE - 14 AND transactionDate &lt; CURRENT_DATE - 7 THEN amount ELSE 0 END) AS lastWeekAmount<br>FROM transactions<br>WHERE transactionDate &gt;= CURRENT_DATE - 14<br>GROUP BY accountId</pre><p>A sudden week-over-week jump in spend is a common fraud signal, especially combined with other flags like a new device or a first-time merchant category. This single-query conditional-aggregation version scans transactions once, versus writing two separately-filtered CTEs and joining them, which scans it twice, the same CTE-reuse tradeoff that applies to any repeated table scan.</p><h3>Combining aggregation with window functions: a rolling decline rate</h3><p>A plain GROUP BY collapses to one row per account, a single snapshot number. Wrapping the same conditional-aggregation logic in a window function instead keeps every row intact while still computing the aggregate, giving a decline rate <em>as of every transaction</em>, evolving over time.</p><pre>SELECT<br>    accountId,<br>    transactionId,<br>    transactionDate,<br>    isDeclined,<br>    COUNT(*) OVER (<br>        PARTITION BY accountId<br>        ORDER BY transactionDate<br>        RANGE BETWEEN INTERVAL &#39;1&#39; YEAR PRECEDING AND CURRENT ROW<br>    ) AS txnCountTrailing1Y,<br>    SUM(CASE WHEN isDeclined THEN 1 ELSE 0 END) OVER (<br>        PARTITION BY accountId<br>        ORDER BY transactionDate<br>        RANGE BETWEEN INTERVAL &#39;1&#39; YEAR PRECEDING AND CURRENT ROW<br>    ) AS declineCountTrailing1Y<br>FROM transactions</pre><h3>Multiple trailing windows and their ratios</h3><p>The same shape repeated for 1 month, 6 months, 1 year, and 2 years, plus the ratios between adjacent windows:</p><pre>-- (1M, 6M, 1Y, 2Y versions of the same COUNT/SUM CASE WHEN OVER pattern)</pre><pre>SELECT *,<br>    declineCount1M * 1.0 / NULLIF(txnCount1M, 0) AS declineRate1M,<br>    declineCount6M * 1.0 / NULLIF(txnCount6M, 0) AS declineRate6M,<br>    (declineCount1M * 1.0 / NULLIF(txnCount1M, 0)) / NULLIF((declineCount6M * 1.0 / NULLIF(txnCount6M, 0)), 0) AS ratio_1M_to_6M<br>FROM (-- the multi-window query --) t</pre><p>A ratio_1M_to_6M close to 1.0 means the account&#39;s most recent month is consistent with its own trailing 6-month pattern, normal, stable behavior. A ratio far from 1.0 signals a recent, sudden shift, distinct from a flat &quot;decline rate &gt; threshold&quot; rule, which wouldn&#39;t distinguish a consistently elevated but stable account from one that just started deteriorating.</p><p><strong>This is a strong candidate for Jinja macros in a dbt context</strong>, since the same window shape repeats for every interval, only the label and interval value change:</p><pre>{% set windows = [<br>    {&#39;label&#39;: &#39;1M&#39;, &#39;interval&#39;: &quot;INTERVAL &#39;1&#39; MONTH&quot;},<br>    {&#39;label&#39;: &#39;6M&#39;, &#39;interval&#39;: &quot;INTERVAL &#39;6&#39; MONTH&quot;},<br>    {&#39;label&#39;: &#39;1Y&#39;, &#39;interval&#39;: &quot;INTERVAL &#39;1&#39; YEAR&quot;},<br>    {&#39;label&#39;: &#39;2Y&#39;, &#39;interval&#39;: &quot;INTERVAL &#39;2&#39; YEAR&quot;}<br>] %}</pre><pre>SELECT accountId, transactionId, transactionDate,<br>    {% for w in windows %}<br>    COUNT(*) OVER (PARTITION BY accountId ORDER BY transactionDate RANGE BETWEEN {{ w.interval }} PRECEDING AND CURRENT ROW) AS txnCount{{ w.label }},<br>    SUM(CASE WHEN isDeclined THEN 1 ELSE 0 END) OVER (PARTITION BY accountId ORDER BY transactionDate RANGE BETWEEN {{ w.interval }} PRECEDING AND CURRENT ROW) AS declineCount{{ w.label }}{{ &#39;,&#39; if not loop.last }}<br>    {% endfor %}<br>FROM {{ ref(&#39;stg_transactions&#39;) }}</pre><p>Changing a window, or adding a new one, then only requires editing the windows list in one place instead of duplicating and hand-editing several near-identical blocks. For a genuinely reusable, tested, documented version, this logic works even better as a parameterized macro (trailing_decline_metrics(interval_label, interval_sql)) called once per window from the model, rather than one large templated block. Note this is specifically a dbt-project technique, most plain SQL editors don&#39;t compile Jinja, so a one-off analytical query outside of dbt would need the repetitive blocks hand-written or generated in Python beforehand.</p><h3>3. Complex Joins for Relationship Detection</h3><h3>Why this differs from window functions</h3><p>Window functions compare a row against its own account’s history over time. A large class of fraud signals instead require comparing <strong>different rows to each other directly</strong>, two transactions sharing a card but belonging to different accounts, which is a self-join problem: joining a table to itself to find relationships between distinct rows.</p><h3>Pattern 1: Self-join on a shared attribute</h3><pre>SELECT<br>    t1.accountId AS accountA,<br>    t2.accountId AS accountB,<br>    t1.cardNumberHash,<br>    COUNT(DISTINCT t1.transactionId) AS sharedCardTxnCount<br>FROM transactions t1<br>JOIN transactions t2<br>    ON t1.cardNumberHash = t2.cardNumberHash<br>    AND t1.accountId &lt; t2.accountId<br>GROUP BY t1.accountId, t2.accountId, t1.cardNumberHash<br>HAVING COUNT(DISTINCT t1.transactionId) &gt; 1</pre><p><strong>Why </strong><strong>t1.accountId &lt; t2.accountId matters:</strong> without it, the join produces every pair twice (once from each direction) and matches every account to itself trivially, since a card obviously matches itself. The inequality guard eliminates both problems at once, a detail specific to self-joins looking for relationships between distinct entities, as opposed to an inherently directional relationship like employee-to-manager, which doesn&#39;t carry this duplicate-pairing risk.</p><h3>Pattern 2: Multi-condition self-joins for higher confidence</h3><pre>SELECT<br>    t1.transactionId AS txn1,<br>    t2.transactionId AS txn2,<br>    t1.deviceId,<br>    t1.cardNumberHash AS card1,<br>    t2.cardNumberHash AS card2<br>FROM transactions t1<br>JOIN transactions t2<br>    ON t1.deviceId = t2.deviceId<br>    AND t1.cardNumberHash != t2.cardNumberHash<br>    AND t1.transactionId &lt; t2.transactionId<br>    AND ABS(DATEDIFF(second, t1.transactionTime, t2.transactionTime)) &lt; 300</pre><p>Each condition earns its place: shared device establishes the connection, different cards specifically targets card-testing (the same card on the same device isn’t unusual), and the tight time window adds the “this happened suspiciously fast” dimension. Stacking multiple conditions raises confidence well beyond what any single shared attribute alone would justify.</p><p><strong>A related structural point worth knowing:</strong> sometimes the question is better answered with a plain aggregation than a join, if you only need a count of how many entities share an attribute, rather than the individual pairwise relationships:</p><pre>SELECT cardNumberHash, COUNT(DISTINCT accountId) AS accountsUsingThisCard<br>FROM transactions<br>GROUP BY cardNumberHash<br>HAVING COUNT(DISTINCT accountId) &gt; 1</pre><p>Defaulting to a self-join when a simple GROUP BY/HAVING would answer the question adds unnecessary join cost.</p><h3>Pattern 3: Sequence-based self-joins for conditional patterns</h3><pre>SELECT<br>    t1.accountId,<br>    t1.transactionId AS declinedTxn,<br>    t1.amount AS declinedAmount,<br>    t2.transactionId AS approvedTxn,<br>    t2.amount AS approvedAmount,<br>    DATEDIFF(second, t1.transactionTime, t2.transactionTime) AS secondsBetween<br>FROM transactions t1<br>JOIN transactions t2<br>    ON t1.accountId = t2.accountId<br>    AND t1.isDeclined = TRUE<br>    AND t2.isDeclined = FALSE<br>    AND t2.transactionTime &gt; t1.transactionTime<br>    AND t2.amount &lt; t1.amount<br>WHERE DATEDIFF(second, t1.transactionTime, t2.transactionTime) &lt; 120</pre><p>A large declined attempt followed quickly by a smaller successful one is a known probing/card-testing behavior. This uses t2.transactionTime &gt; t1.transactionTime to enforce ordering, functionally similar to what LAG() provides, but expressed as a join specifically because both rows&#39; full details are needed together in one output row, not just one lagged column tacked onto the current row.</p><p><strong>When to reach for this over </strong><strong>LAG():</strong> LAG() is right for comparing a row to the single immediately preceding row when only one or two lagged values are needed. A self-join is right when the relationship is conditional (only declined-then-approved pairs, not every consecutive pair) and both matching rows&#39; full details are needed simultaneously in the output.</p><h3>4. Sessionization</h3><h3>The core mechanic</h3><p>Grouping a stream of timestamped events into discrete sessions, contiguous bursts of activity separated by a meaningful gap of inactivity, combines two techniques already covered: LAG() to detect a gap, and a cumulative SUM() window to convert gap flags into an actual session ID.</p><p><strong>Step 1: flag whether each event starts a new session</strong></p><pre>SELECT<br>    accountId,<br>    transactionId,<br>    transactionTime,<br>    CASE<br>        WHEN LAG(transactionTime) OVER (PARTITION BY accountId ORDER BY transactionTime) IS NULL THEN 1<br>        WHEN DATEDIFF(minute, LAG(transactionTime) OVER (PARTITION BY accountId ORDER BY transactionTime), transactionTime) &gt; 5 THEN 1<br>        ELSE 0<br>    END AS isNewSession<br>FROM transactions</pre><p><strong>Step 2: turn the flags into a session ID with a running total</strong></p><pre>SELECT<br>    accountId, transactionId, transactionTime, isNewSession,<br>    SUM(isNewSession) OVER (<br>        PARTITION BY accountId ORDER BY transactionTime<br>        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<br>    ) AS sessionId<br>FROM (-- query above --) t</pre><p>Every time isNewSession = 1, the cumulative sum increments by one, permanently shifting all subsequent events (until the next new-session flag) into that new session number, the same running-total window frame used for a cumulative dollar total, applied instead to a column of 0s and 1s.</p><h3>Session-level aggregation for card-testing bursts</h3><pre>WITH sessioned AS (<br>    SELECT accountId, transactionId, transactionTime, isDeclined,<br>        SUM(CASE WHEN DATEDIFF(minute,<br>                LAG(transactionTime) OVER (PARTITION BY accountId ORDER BY transactionTime),<br>                transactionTime) &gt; 5<br>            OR LAG(transactionTime) OVER (PARTITION BY accountId ORDER BY transactionTime) IS NULL<br>            THEN 1 ELSE 0 END<br>        ) OVER (PARTITION BY accountId ORDER BY transactionTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sessionId<br>    FROM transactions<br>)<br>SELECT<br>    accountId, sessionId,<br>    COUNT(*) AS attemptsInSession,<br>    SUM(CASE WHEN isDeclined THEN 1 ELSE 0 END) AS declinesInSession,<br>    DATEDIFF(second, MIN(transactionTime), MAX(transactionTime)) AS sessionSpanSeconds<br>FROM sessioned<br>GROUP BY accountId, sessionId<br>HAVING COUNT(*) &gt; 5 AND SUM(CASE WHEN isDeclined THEN 1 ELSE 0 END) * 1.0 / COUNT(*) &gt; 0.7</pre><p>This flags <strong>sessions</strong>, not individual accounts or transactions, with more than 5 attempts and a decline rate over 70%, a burst of mostly-failing attempts clustered tightly in time. Note the tighter 5-minute gap here versus a general-purpose 30-minute browsing threshold, a deliberate, domain-specific choice: card-testing bursts happen fast, so a much tighter gap correctly captures a rapid-fire attempt cluster as one session, whereas a looser threshold would merge genuinely separate bursts together.</p><h3>The gap threshold is a tunable, domain-specific decision, not a universal constant</h3><p>There’s no single “correct” gap value. Too short a gap fragments genuine continuous behavior into artificial sessions, diluting session-level signals like event count. Too long a gap merges genuinely distinct sittings into one inflated session, which can wash out a real anomaly (a short, intense burst) by averaging it with normal activity around it. In practice, this threshold is often tuned empirically, by plotting the distribution of gap times between consecutive events and looking for a natural split, rather than picked arbitrarily.</p><h3>5. Outlier and Anomaly Detection Patterns</h3><h3>Method 1: Z-score</h3><pre>SELECT<br>    accountId, transactionId, amount,<br>    AVG(amount) OVER (PARTITION BY accountId) AS meanAmount,<br>    STDDEV(amount) OVER (PARTITION BY accountId) AS stddevAmount,<br>    (amount - AVG(amount) OVER (PARTITION BY accountId)) / NULLIF(STDDEV(amount) OVER (PARTITION BY accountId), 0) AS zScore<br>FROM transactions</pre><p>A z-score of 3.2 means a transaction sits 3.2 standard deviations above the account’s average; |z| &gt; 3 is a common threshold, since roughly 99.7% of values fall within 3 standard deviations under a normal distribution.</p><p><strong>The real limitation:</strong> z-scores assume roughly normal (bell-curve) data. Transaction amounts are almost never normally distributed, they’re typically right-skewed, a long tail of a few very large values. Mean and standard deviation are both heavily distorted by outliers themselves, a genuinely circular problem: one huge fraudulent transaction inflates the mean and stddev used to judge whether that same transaction is anomalous, potentially masking real outliers by widening the “normal” range.</p><h3>Method 2: Percentile-based thresholds, a more robust alternative</h3><pre>SELECT<br>    accountId, transactionId, amount,<br>    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY accountId) AS p99Amount<br>FROM transactions</pre><p>A percentile doesn’t care about the shape of the distribution, it simply asks what value sits at a given rank in the sorted data. Flagging “above the 99th percentile” means, by definition, “in the top 1% of this account’s own historical amounts,” true and meaningful regardless of skew.</p><p><strong>PERCENTILE_CONT vs. </strong><strong>PERCENTILE_DISC:</strong> PERCENTILE_CONT (continuous) interpolates between two data points if the target percentile falls between them, the standard choice for most analytical purposes. PERCENTILE_DISC (discrete) returns an actual value that exists in the dataset, no interpolation, useful when the result needs to correspond to a real observed row.</p><p><strong>IQR, the skew-resistant analog to standard deviation:</strong></p><pre>SELECT<br>    accountId, transactionId, amount,<br>    (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY accountId)<br>     - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY accountId)) AS iqr<br>FROM transactions</pre><p>The classic IQR outlier rule, flag anything beyond Q3 + 1.5×IQR (or below Q1 - 1.5×IQR), is what a boxplot&#39;s whiskers represent, and it&#39;s robust to skewed data in exactly the way mean/stddev is not, since the median and quartiles are far less sensitive to a handful of extreme values.</p><h3>Method 3: Population-relative comparison</h3><pre>WITH accountStats AS (<br>    SELECT accountId, AVG(amount) AS avgAmount<br>    FROM transactions<br>    WHERE transactionDate &gt;= CURRENT_DATE - 30<br>    GROUP BY accountId<br>),<br>populationStats AS (<br>    SELECT AVG(avgAmount) AS populationMean, STDDEV(avgAmount) AS populationStddev<br>    FROM accountStats<br>)<br>SELECT<br>    a.accountId, a.avgAmount,<br>    (a.avgAmount - p.populationMean) / NULLIF(p.populationStddev, 0) AS populationZScore<br>FROM accountStats a<br>CROSS JOIN populationStats p</pre><p><strong>The </strong><strong>CROSS JOIN here is intentional, not a mistake:</strong> populationStats produces exactly one row (a single mean and stddev across all accounts). Cross-joining a single-row table against another simply broadcasts that one row&#39;s values to every row of the other table, the standard, idiomatic way to attach a single aggregate value across a whole result set in SQL.</p><p>This catches a different failure mode than a self-comparison: an account whose <em>own average</em> behavior is unusual relative to the <em>entire population</em>, useful for catching consistently high-value or high-frequency accounts even if they’ve never had a single dramatic spike relative to their own personal history, and essential for a brand-new account with no personal history to compare against at all.</p><h3>Choosing between methods</h3><p>Z-score against an account’s own history is fast and simple but assumes roughly normal data and is distorted by the outliers it’s trying to detect, a reasonable cheap first-pass signal. Percentile/IQR is more robust to real-world skewed distributions, generally the preferred choice for financial data, at the cost of requiring a sort. Population-relative comparison answers a genuinely different question, essential for new entities and for catching consistently-anomalous-but-internally-stable behavior. In practice, a real system typically layers several of these together, the same “combine independent signals for higher confidence” principle behind the multi-condition self-join pattern.</p><h3>6. Rolling Aggregates and Moving Averages</h3><h3>Whether to include the current row depends entirely on purpose</h3><pre>-- For a trend line / dashboard smoothing: INCLUDE the current row<br>AVG(dailyAmount) OVER (PARTITION BY accountId ORDER BY transactionDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)</pre><pre>-- For an anomaly baseline: EXCLUDE the current row<br>AVG(dailyAmount) OVER (PARTITION BY accountId ORDER BY transactionDate ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING)</pre><p>Same window shape, opposite inclusion rule. A trend visualization wants to describe today’s position <em>including</em> today; an anomaly baseline wants to judge today against a window that today hasn’t contaminated.</p><h3>Velocity as rate-of-change, not just count-in-a-window</h3><pre>SELECT<br>    accountId, transactionDate,<br>    SUM(amount) OVER (<br>        PARTITION BY accountId ORDER BY transactionDate<br>        RANGE BETWEEN INTERVAL &#39;7&#39; DAY PRECEDING AND CURRENT ROW<br>    ) AS trailing7dTotal,<br>    SUM(amount) OVER (<br>        PARTITION BY accountId ORDER BY transactionDate<br>        RANGE BETWEEN INTERVAL &#39;14&#39; DAY PRECEDING AND INTERVAL &#39;8&#39; DAY PRECEDING<br>    ) AS prior7dTotal<br>FROM transactions</pre><pre>SELECT *,<br>    (trailing7dTotal - prior7dTotal) * 1.0 / NULLIF(prior7dTotal, 0) AS pctChange<br>FROM (-- query above --) t</pre><p>A frame bounded on both sides with an offset (INTERVAL &#39;14&#39; DAY PRECEDING AND INTERVAL &#39;8&#39; DAY PRECEDING) computes a genuinely separate, non-overlapping prior window, distinct from comparing a smaller window nested fully inside a larger one, which answers a related but different question.</p><h3>Sparse data: ROWS vs. RANGE behave very differently</h3><pre>-- ROWS: always looks back exactly 6 rows, however much calendar<br>-- time those rows actually span. On a sparse account, this could<br>-- silently span over a year of stale history.<br>AVG(amount) OVER (PARTITION BY accountId ORDER BY transactionDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)</pre><pre>-- RANGE: correctly bounds by calendar time, but can produce a<br>-- tiny or empty sample if the account hasn&#39;t been active recently.<br>AVG(amount) OVER (PARTITION BY accountId ORDER BY transactionDate RANGE BETWEEN INTERVAL &#39;30&#39; DAY PRECEDING AND CURRENT ROW)</pre><p>ROWS-based windows guarantee a minimum sample size but risk including stale, irrelevant history on sparse data. RANGE-based windows correctly respect recency but risk an unreliable, too-small sample on the same sparse data. Neither is universally correct, this is a genuine design tradeoff dependent on how dense the underlying data actually is, worth actively reasoning about rather than defaulting blindly to one.</p><h3>7. Query Optimization Applied to Fraud Patterns</h3><h3>Self-joins are the highest-risk pattern in this entire topic</h3><p>A self-join on an N-row table is, worst case, an N×N comparison, the nested loop join cost applied to the same massive table on both sides. What actually saves this at real scale: indexing/clustering the join column (cardNumberHash, deviceId) so the optimizer can execute a hash join instead of a brute-force comparison, and filtering to a recent time window <strong>before</strong> the join runs, not after, so the join operates on a much smaller set. If the table is partitioned by date, a date filter also enables partition pruning on both sides of the join before the expensive matching begins.</p><h3>Wide RANGE windows are a genuine spill risk</h3><p>A RANGE-based frame spanning a year or two, for an active account, can mean holding tens of thousands of rows in memory per window function, and a multi-window query (1M/6M/1Y/2Y) computes several such frames at once. This is a legitimate risk of the warehouse spilling to disk. The practical mitigation: pre-aggregate to a daily account-level summary table first, then compute rolling windows on top of that much smaller table instead of raw transaction-level data, reducing the row count the expensive window operation churns through by orders of magnitude.</p><h3>COUNT(DISTINCT ...) at scale favors an approximate version</h3><pre>SELECT accountId, APPROX_COUNT_DISTINCT(deviceId) AS distinctDevices<br>FROM transactions<br>WHERE transactionDate &gt;= CURRENT_DATE - 1<br>GROUP BY accountId</pre><p>Diversity-of-behavior signals are almost always threshold checks (“more than 5 devices”), not a need for an exact count. Approximate distinct counting trades a small amount of precision for dramatically lower cost at scale, and that precision loss essentially never changes whether an account crosses a threshold, making this a very low-risk optimization for this specific use case.</p><h3>Sessionization’s cumulative window is inherently sequential</h3><p>A running-total window function must process rows for a given partition in strict order, it can’t be parallelized within one account’s sequence the way a plain GROUP BY can. A small number of extremely active accounts (skewed data) can become a genuine bottleneck, one worker processing a massively long sequential chain while others handling normal-sized accounts finish quickly and sit idle. Pragmatic mitigations include pre-filtering or separately handling known extreme-outlier accounts, or accepting the skew as a bounded, known cost if it only affects a small, identifiable segment.</p><h3>Percentiles require a sort, a real cost against z-scores</h3><p>Unlike AVG()/STDDEV(), which compute in a single streaming pass, PERCENTILE_CONT/PERCENTILE_DISC fundamentally require sorted data. Computing percentiles per-account across millions of accounts means many small sorts happening under the hood. This is a real, legitimate cost, not a reason to avoid percentiles, they remain the statistically better choice for skewed data, but worth being explicit about as the tradeoff for that robustness.</p><h3>The One Unifying Idea</h3><p>Every pattern in this guide answers one of two questions:</p><blockquote><strong><em>How does this event compare to this specific entity’s own recent history? Or, how does this entity compare to everyone else?</em></strong></blockquote><blockquote>Window functions with a trailing baseline answer the first. Population-relative comparisons and self-joins between different entities answer the second. Sessionization and rolling aggregates are really just different lenses for framing “recent history” precisely enough to compare against. And the query optimization concerns that show up throughout, self-join cost, spill risk, sequential window processing, are simply what happens when these comparison patterns get run at real production scale instead of a small test table.</blockquote><h3>Quick-Reference Summary</h3><ul><li><strong>PARTITION BY resets a window per entity</strong> — omitting it lets one account&#39;s history leak into another&#39;s, or a computation blend across entities that should never be compared.</li><li><strong>RANGE with a time interval measures real elapsed time; </strong><strong>ROWS measures a fixed count of rows</strong> — velocity and frequency checks almost always need RANGE.</li><li><strong>Exclude the current row from an anomaly baseline</strong> (1 PRECEDING, not CURRENT ROW) so a fraudulent event can&#39;t dilute its own detection threshold; include it for trend-smoothing instead.</li><li><strong>Ratios against a per-entity trailing baseline adapt to each account’s own normal behavior</strong> — a fixed global threshold either constantly flags high-activity accounts or misses anomalies on low-activity ones.</li><li><strong>NULLIF guards any division by a rolling aggregate that could legitimately be zero</strong>, and correctly produces NULL, not 0, preserving the distinction between &quot;confirmed normal&quot; and &quot;genuinely unknown.&quot;</li><li><strong>Referencing the same aggregate in both </strong><strong>SELECT and </strong><strong>HAVING computes it once</strong>, not twice — no CTE is needed to &quot;optimize&quot; this.</li><li><strong>Self-joins for relationship detection need an inequality guard</strong> (t1.id &lt; t2.id) to avoid duplicate mirrored pairs and self-matches.</li><li><strong>Stack multiple join conditions together for higher-confidence signals</strong> — a shared attribute alone is rarely enough; add a difference condition and a time-proximity condition.</li><li><strong>Choose a self-join over </strong><strong>LAG() specifically when the relationship is conditional and both matching rows&#39; full details are needed together.</strong></li><li><strong>Sessionization is </strong><strong>LAG() plus a cumulative </strong><strong>SUM() of gap flags</strong> — the same running-total frame used for a cumulative dollar total, applied to a column of 0s and 1s.</li><li><strong>The session gap threshold is a tunable business decision</strong>, not a universal constant, and should ideally be validated against the actual observed distribution of gap times.</li><li><strong>Z-scores assume roughly normal data and are distorted by the very outliers they’re meant to catch</strong>; percentiles and IQR are more robust to the real-world skew common in financial and behavioral data.</li><li><strong>A population-relative comparison catches a different failure mode than a self-comparison</strong> — essential for new entities with no history, and for consistently-anomalous-but-internally-stable behavior.</li><li><strong>Self-joins are the highest cost-risk pattern in this whole topic</strong> — always index the join column and filter to a recent window before joining, never after.</li><li><strong>Pre-aggregate to a daily summary table before computing wide, multi-year rolling windows</strong>, rather than windowing directly over raw transaction-level data, to avoid spilling to disk.</li><li><strong>Approximate distinct counts are a strong fit for diversity-of-behavior signals</strong>, since these are almost always threshold checks, not requests for an exact number.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=82153bcbd2b1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[dbt Fundamentals for Analytics Engineers : A Complete Guide]]></title>
            <link>https://bysuchi.medium.com/dbt-fundamentals-for-analytics-engineers-a-complete-guide-6f10cd2ba6c3?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/6f10cd2ba6c3</guid>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[interview]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Tue, 18 Aug 2026 23:34:24 GMT</pubDate>
            <atom:updated>2026-08-19T13:58:24.777Z</atom:updated>
            <content:encoded><![CDATA[<p>dbt (data build tool) is the third pillar of most modern Analytics Engineer, alongside query optimization and data modeling. This guide walks through dbt’s core mechanics in dependency order: project structure first, then the templating engine (Jinja/macros) everything else is built on, then how models connect into a dependency graph, how they get physically built, and finally the testing, packaging, and environment-management layers on top. The same worked examples (a daily model-score fact table, a customer dimension) carry through the whole guide.</p><p>Curious what the <a href="https://bysuchi.medium.com/query-optimization-concepts-for-an-analytics-engineer-3000f9dfbd4a">first</a> and <a href="https://bysuchi.medium.com/fact-dimension-grain-the-data-modeling-guide-nobody-gives-you-c44857e3d220">second</a> pillars of modern Analytics Engineering are? Click on the links</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KubT4rvKi_fz9OSK6-gvrA.avif" /><figcaption>Image from <a href="https://unsplash.com/photos/grayscale-photography-of-metal-pipes-scUBcasSvbE">Unsplash</a></figcaption></figure><p><strong>What this guide covers:</strong></p><ol><li>Project Structure (staging → intermediate → marts)</li><li>Jinja &amp; Macros Basics</li><li>ref(), source(), and Freshness</li><li>The DAG and Documentation</li><li>Materializations</li><li>Incremental Models — Practical Deep Dive</li><li>Seeds</li><li>Snapshots</li><li>dbt Tests</li><li>dbt Packages (dbt_utils)</li><li>Environments and Targets</li><li>dbt build vs. run vs. test</li><li>Quick Extras: Exposures, Contracts, Semantic Layer, Hooks</li></ol><h3>1. Project Structure (Staging → Intermediate → Marts)</h3><h3>The core idea</h3><p>A dbt project is conventionally organized into three layers, each with exactly one job. This isn’t enforced by dbt itself, it’s a widely adopted community convention, but deviating from it without good reason is a real red flag in code review.</p><pre>models/<br>├── staging/<br>│   ├── stg_customers.sql<br>│   ├── stg_orders.sql<br>│   └── stg_order_items.sql<br>├── intermediate/<br>│   └── int_orders_joined.sql<br>└── marts/<br>    ├── fact_orders.sql<br>    └── dim_customer.sql</pre><h3>Layer 1: Staging — one-to-one with a source table, light cleanup only</h3><pre>-- models/staging/stg_customers.sql<br>SELECT<br>    id AS customer_id,<br>    UPPER(TRIM(name)) AS customer_name,<br>    email,<br>    created_at AS signup_date<br>FROM {{ source(&#39;raw&#39;, &#39;customers&#39;) }}</pre><p>What belongs here: renaming columns, type casting, light cleanup like the UPPER(TRIM()) pattern. What doesn&#39;t: no joins (a staging model touches exactly one source table), no business logic, no aggregation.</p><p><strong>Why this discipline matters:</strong> if every downstream model trusts that stg_customers already has clean, consistently named columns, nobody repeats that cleanup logic in ten different places, the same &quot;apply the function once, reuse it&quot; principle from query optimization&#39;s CTE-reuse discussion, applied at the project-architecture level.</p><h3>Layer 2: Intermediate — business logic, joins, not yet the final output</h3><pre>-- models/intermediate/int_orders_joined.sql<br>SELECT<br>    o.order_id,<br>    o.customer_id,<br>    o.order_date,<br>    SUM(oi.quantity * oi.unit_price) AS order_total<br>FROM {{ ref(&#39;stg_orders&#39;) }} o<br>JOIN {{ ref(&#39;stg_order_items&#39;) }} oi ON o.order_id = oi.order_id<br>GROUP BY o.order_id, o.customer_id, o.order_date</pre><p>Not every project needs this layer for every model, if a mart is simple enough to build directly from staging with one join, skipping straight there is fine. Intermediate models exist to keep marts readable when the logic is genuinely complex enough to deserve its own named step.</p><h3>Layer 3: Marts — the final, business-facing output</h3><pre>-- models/marts/fact_orders.sql<br>SELECT order_id, customer_id, order_date, order_total<br>FROM {{ ref(&#39;int_orders_joined&#39;) }}</pre><p>This is where fact_orders, dim_customer, etc. live, the layer business users and BI tools query directly. Everything before this is internal plumbing.</p><h3>Why this layering matters</h3><p>Data flows in exactly one direction, staging → intermediate → marts. A bug is easy to isolate by tracing backward one layer at a time. Multiple downstream models can reuse the same staging model’s cleanup logic without duplicating it.</p><p><strong>Important clarification:</strong> staging/intermediate/marts are not actual dbt features. dbt has no special knowledge that models/staging/ differs from models/marts/. It&#39;s purely folder organization and team convention, enforced through code review, not the tool.</p><h3>Materialization strategy for this layering</h3><p><strong>Staging and intermediate models are typically </strong><strong>views</strong>, not tables: cheap to recompute (no joins/aggregation), zero storage cost, always instantly reflects source changes. Since they&#39;re internal plumbing not queried directly by end users, the recompute cost is absorbed once at the point a mart actually runs.</p><p><strong>Marts are usually </strong><strong>table or </strong><strong>incremental</strong>, since dashboards/analysts hit them repeatedly and shouldn&#39;t recompute the full join/aggregation chain on every query.</p><p>This connects directly to CTE inlining from query optimization: a chain of views is conceptually similar to a chain of CTEs, most engines inline a view into the final query plan the same way they’d inline an unmaterialized CTE.</p><h3>Ephemeral: a third option, no database object at all</h3><pre>{{ config(materialized=&#39;ephemeral&#39;) }}<br>SELECT id AS customer_id, UPPER(TRIM(name)) AS customer_name FROM {{ source(&#39;raw&#39;, &#39;customers&#39;) }}</pre><p>Instead of creating a view or table, dbt inlines this model’s SQL directly as a CTE into whatever references it, at compile time. No database object is ever created.</p><p><strong>Advantages:</strong> keeps the warehouse schema browser clean (no stg_* clutter), guarantees CTE inlining rather than hoping the optimizer inlines a view.</p><p><strong>Disadvantages, and why </strong><strong>view remains the more common default:</strong></p><ul><li>Cannot be queried directly for debugging (SELECT * FROM stg_customers LIMIT 10 doesn&#39;t work, the object doesn&#39;t exist)</li><li>If referenced by multiple downstream models, its logic gets recomputed inside each one separately, rather than existing once as a shared object, forced into the “recomputed” side of the CTE-reuse tradeoff with no possibility of the engine sharing it</li><li><strong>dbt tests cannot run directly against an ephemeral model</strong>, since there’s no queryable object to test against at test time</li></ul><p><strong>A genuinely effective real-world pattern: switch materialization by environment</strong></p><pre>models:<br>  my_project:<br>    staging:<br>      +materialized: &quot;{{ &#39;ephemeral&#39; if target.name == &#39;prod&#39; else &#39;view&#39; }}&quot;</pre><p>Use view during development (debug freely), switch to ephemeral in production (once logic is verified, gain the schema-cleanliness and guaranteed-inlining benefits). The tradeoff: any staging-level tests only run in dev/CI, not in prod, since the ephemeral object doesn&#39;t exist there to test against, worth being a deliberate choice rather than an accidental side effect.</p><blockquote><strong><em>Q: Are staging tables materialized, and if so as what?</em></strong></blockquote><blockquote><em>Yes, conventionally as views, since they’re cheap to recompute and not queried directly by end users. Marts are usually tables or incremental models instead, since they’re hit repeatedly by BI tools and analysts.</em></blockquote><blockquote><strong><em>Q: Can staging be skipped entirely?</em></strong></blockquote><blockquote><em>Technically yes, but it duplicates cleanup logic across every model that needs clean source data, and any change to that logic then has to be fixed in every duplicate location instead of one place. Reasonable to skip only for very small projects or lookup tables with no real source to clean.</em></blockquote><blockquote><strong><em>Q: Does splitting into staging/intermediate/marts make queries slower than combining everything into one query with CTEs?</em></strong></blockquote><blockquote><em>Not if staging/intermediate stay as views, since the SQL gets inlined at execution time, the same way CTEs get inlined by the optimizer. The cost only shows up if intermediate models get unnecessarily materialized as physical tables, adding real read/write overhead for no benefit unless that intermediate step is genuinely expensive and reused by multiple downstream marts.</em></blockquote><blockquote><strong><em>Q: Can staging tables be left completely unmaterialized?</em></strong></blockquote><blockquote><em>Yes, via </em><em>materialized=&#39;ephemeral&#39;, which inlines the model as a CTE at compile time rather than creating any database object at all.</em></blockquote><h3>2. Jinja &amp; Macros Basics</h3><h3>The core idea</h3><p>dbt models are SQL <strong>templates</strong>, written in Jinja (the same templating language Flask uses for HTML). Before anything runs against the warehouse, dbt <strong>compiles</strong> the Jinja into plain SQL. The warehouse never sees Jinja syntax, only the final generated SQL. This compile step is the mechanism ref(), source(), is_incremental(), and everything else in dbt is built on top of.</p><h3>The two syntaxes</h3><pre>-- {{ }}: outputs a value into the compiled SQL<br>SELECT {{ 1 + 1 }} AS two   -- compiles to: SELECT 2 AS two</pre><pre>-- {% %}: control flow (if/for/set), emits nothing itself<br>{% set my_var = 5 %}<br>SELECT {{ my_var }} AS five  -- compiles to: SELECT 5 AS five</pre><h3>Variables and loops</h3><pre>{% set payment_methods = [&#39;credit_card&#39;, &#39;paypal&#39;, &#39;gift_card&#39;] %}<br>SELECT<br>    order_id,<br>    {% for method in payment_methods %}<br>    SUM(CASE WHEN payment_method = &#39;{{ method }}&#39; THEN amount ELSE 0 END) AS {{ method }}_total{% if not loop.last %},{% endif %}<br>    {% endfor %}<br>FROM orders<br>GROUP BY order_id</pre><p>Compiles to three fully written-out SUM(CASE WHEN ...) lines. This is genuinely important for <strong>generating repetitive SQL programmatically</strong>: with 20 payment methods, write the loop once instead of 20 near-identical lines, and adding a 21st means updating one list, not hunting down every duplicated line.</p><h3>Conditionals</h3><pre>SELECT * FROM orders<br>{% if target.name == &#39;dev&#39; %}<br>WHERE order_date &gt;= DATEADD(day, -7, CURRENT_DATE)<br>{% endif %}</pre><h3>What a macro is</h3><p>A reusable, named block of Jinja, essentially a function, defined once and called from any model.</p><pre>-- macros/cents_to_dollars.sql<br>{% macro cents_to_dollars(column_name) %}<br>    ({{ column_name }} / 100.0)<br>{% endmacro %}</pre><pre>SELECT order_id, {{ cents_to_dollars(&#39;amount_cents&#39;) }} AS amount_dollars<br>FROM {{ ref(&#39;stg_orders&#39;) }}</pre><p>Compiles to (amount_cents / 100.0) AS amount_dollars. If the conversion logic ever changes, fix it once in the macro, every model using it picks up the fix automatically on next compile.</p><p><strong>A macro is a mix of three things</strong>, not everything needs to be wrapped in {% %}: control-flow tags ({% macro %}, {% endmacro %}) that emit nothing, output tags ({{ column_name }}) that emit a value, and plain literal text (/ 100.0) that passes through untouched exactly as written.</p><h3>ref() and source() are themselves built-in macros</h3><pre>SELECT * FROM {{ ref(&#39;stg_orders&#39;) }}</pre><p>Not special syntax, a macro dbt ships with, resolving at compile time to the fully qualified, environment-specific table name (dev_schema.stg_orders in dev, analytics.stg_orders in prod).</p><h3>Compile-time queries: macros that run SQL during compilation</h3><pre>{% macro get_payment_methods() %}<br>    {% set query %}<br>        SELECT DISTINCT payment_method FROM {{ ref(&#39;stg_payments&#39;) }}<br>    {% endset %}<br>    {% set results = run_query(query) %}<br>    {{ return(results.columns[0].values()) }}<br>{% endmacro %}</pre><p>This actually executes a query against the warehouse <strong>while dbt is compiling</strong>, returning the real distinct payment methods rather than a hardcoded list. It runs once, at compile time, to help generate SQL, it is not part of the final SQL sent to the warehouse when the model executes.</p><h3>Compile time vs. run time, in full detail</h3><p><strong>Compile time</strong> = when dbt processes a .sql file&#39;s Jinja and produces plain SQL text. Happens once per dbt run/dbt build invocation, on your machine or the CI/orchestration runner, before any query touches the database. Output is written to target/compiled/, browsable directly.</p><p><strong>Run time</strong> = when that generated plain SQL is actually sent to and executed by the warehouse, real computation over real data happens here.</p><p><strong>Critical point: compilation happens fresh, every single time </strong><strong>dbt run is invoked.</strong> It is not a one-time, permanent event. So get_payment_methods() genuinely re-runs its query and gets fresh results every single day in a production pipeline, since the whole compile phase re-executes from scratch on every invocation.</p><h3>The {{ today }} trap: compile-time vs. run-time evaluation</h3><pre>-- Requires an EXTRA query at compile time, then hardcodes the result as a literal<br>{% set today = run_query(&quot;SELECT CURRENT_DATE&quot;) %}<br>SELECT * FROM orders WHERE order_date = {{ today }}<br>-- Compiles to: SELECT * FROM orders WHERE order_date = &#39;2024-06-15&#39;</pre><pre>-- Correct: no extra compile-time query, evaluated by the warehouse AT EXECUTION TIME<br>SELECT * FROM orders WHERE order_date = CURRENT_DATE</pre><p>Since compilation happens fresh daily, the Jinja version does update day to day, it is not frozen forever. But it does unnecessary extra work (a whole separate query just to fetch a date), and introduces a subtle risk: the value is frozen at the moment of <em>compilation</em>, not execution. If those two moments ever differ (compiled at 11:59pm, executed just after midnight), the baked-in date could be wrong. Anything that should reflect the exact moment of execution belongs in plain SQL, evaluated by the warehouse at run time, not fetched via Jinja beforehand.</p><h3>Row-level conditionals: {% if %} cannot see column values</h3><pre>-- WRONG: Jinja has no concept of a row&#39;s column value, this doesn&#39;t work<br>{% if status == &#39;completed&#39; %}<br>    SELECT * FROM orders WHERE status = &#39;completed&#39;<br>{% endif %}</pre><pre>-- CORRECT: plain SQL, evaluated per row, at run time<br>SELECT order_id, CASE WHEN status = &#39;completed&#39; THEN &#39;Done&#39; ELSE &#39;Pending&#39; END AS status_label<br>FROM orders</pre><p>{% if %} decides what SQL text gets <em>generated</em>. CASE WHEN decides what happens to each <em>row&#39;s data</em> once that generated SQL actually runs, two completely different times.</p><h3>A catalog of control-flow tags</h3><ul><li><strong>{% set %}</strong> — define a variable</li><li><strong>{% if / elif / else / endif %}</strong> — conditional SQL generation</li><li><strong>{% for / endfor %}</strong> — loop, generate repeated SQL; use loop.first, loop.last, loop.index, loop.index0 as helpers</li><li><strong>{% macro / endmacro %}</strong> — define a reusable block</li><li><strong>{% test / endtest %}</strong> — define a custom generic test</li><li><strong>{% snapshot / endsnapshot %}</strong> — define an SCD Type 2 snapshot</li><li><strong>{% call / endcall %}</strong> — pass a whole SQL block into a macro via caller()</li><li><strong>{%- -%}</strong> — whitespace-trimming modifier, purely cosmetic</li><li><strong>{% do %}</strong> — run something for a side effect, discard the result</li><li><strong>{# #}</strong> — comment, fully stripped at compile time (unlike a SQL -- comment, which survives into the compiled output)</li></ul><blockquote><strong><em>Q: Does everything inside a macro need to be wrapped in </em></strong><strong><em>{% %}?</em></strong></blockquote><blockquote><em>No. Only actual Jinja logic (loops, conditionals, the macro definition itself) needs </em><em>{% %}. Anything meant to become literal SQL text passes through as plain text untouched.</em></blockquote><blockquote><strong><em>Q: If a compile-time-query macro is used in a production pipeline that runs daily, when does it actually execute?</em></strong></blockquote><blockquote><em>During the compile phase, fresh on every single </em><em>dbt run, not once and cached forever. Compilation is not a one-time event, it happens from scratch every invocation.</em></blockquote><h3>3. ref(), source(), and Freshness</h3><h3>ref(): referencing another dbt model</h3><pre>SELECT * FROM {{ ref(&#39;stg_orders&#39;) }}</pre><p>Resolves at compile time into the fully qualified, environment-specific object name. Beyond convenience, this is how dbt <strong>builds the DAG</strong>: every ref() call registers a dependency, which is how dbt determines correct build order, generates the lineage graph, and enables dbt run --select model+ to work at all. A raw hardcoded table name instead of ref() breaks all of this silently, dbt has no idea the two models are related.</p><h3>source(): referencing a raw table dbt did not create</h3><pre># models/staging/_sources.yml<br>sources:<br>  - name: raw<br>    database: raw_db<br>    schema: raw_schema<br>    tables:<br>      - name: customers<br>        identifier: CUST_RAW_TBL_V2   # optional: alias if the real table name is different/ugly</pre><pre>SELECT * FROM {{ source(&#39;raw&#39;, &#39;customers&#39;) }}</pre><p>name: is the friendly name used in code; identifier: (optional) is the actual physical table name in the warehouse, useful when the real name is cryptic or inconsistent, e.g. a vendor-provided table.</p><p>source() is ref()&#39;s counterpart for the boundary of the DAG: tables loaded outside dbt entirely (Fivetran, Airbyte, a Snowpipe, a custom Athena job). Declaring sources centralizes the raw database/schema name in one place, and enables freshness checks, which a hardcoded reference cannot support.</p><h3>Source Freshness</h3><pre>tables:<br>  - name: orders<br>    loaded_at_field: _loaded_at<br>    freshness:<br>      warn_after: {count: 12, period: hour}<br>      error_after: {count: 24, period: hour}</pre><pre>dbt source freshness</pre><p>Conceptually runs SELECT MAX(_loaded_at) FROM orders and compares the gap to now. <strong>Not automatically part of </strong><strong>dbt run/</strong><strong>dbt build</strong>, it&#39;s a separate, standalone command, sequenced explicitly ahead of the main build in a real pipeline: dbt source freshness &amp;&amp; dbt build.</p><p><strong>Built-in three-tier severity, without extra scripting:</strong> fresh → no issue. Past warn_after but within error_after → logged as a warning, command still exits successfully (0), a &amp;&amp;-chained pipeline continues normally. Past error_after → command exits non-zero, a &amp;&amp;-chained pipeline stops there. This means freshness monitoring without a hard pipeline stop is a built-in feature (warn_after), not something requiring custom scripting.</p><p><strong>How it’s actually captured:</strong> based on a real column in the table (loaded_at_field) that the ingestion process must populate, e.g. _loaded_at, _fivetran_synced, not on the warehouse&#39;s own table metadata (like Snowflake&#39;s LAST_ALTERED). Table metadata reflects <em>any</em> write (backfills, schema changes, maintenance), not specifically &quot;new business data arrived,&quot; which is why a dedicated, deliberately populated timestamp column is the trustworthy signal instead.</p><p><strong>Notifications:</strong> dbt source freshness has no built-in alerting itself, only console output and a sources.json artifact. dbt Cloud has native job notifications built in. With dbt Core run through an external orchestrator (Jenkins, Control-M, Airflow), the freshness command&#39;s exit code is what the orchestrator&#39;s own native failure-alerting reacts to, that&#39;s typically where the actual notification comes from in practice, not from dbt itself.</p><p><strong>If no timestamp column exists at all, freshness genuinely cannot be configured for that table.</strong> The real fix is getting the ingestion process to add one (most managed tools like Fivetran already do this by default). A weaker fallback is a business date column that reasonably approximates ingestion time. Absent either, a cruder proxy like a row-count check is sometimes used instead, though it’s far less precise.</p><h3>The critical distinction: ref()/source() vs. a hardcoded table name</h3><pre>-- BAD: still runs fine, but silently breaks every dbt-specific benefit<br>SELECT * FROM raw_db.raw_schema.customers</pre><pre>-- GOOD: DAG tracking, environment portability, freshness checks all work<br>SELECT * FROM {{ source(&#39;raw&#39;, &#39;customers&#39;) }}</pre><p>A hardcoded reference is a completely invisible failure mode: nothing errors, it just quietly loses the DAG entry, the environment portability, and the freshness capability, all at once.</p><blockquote><strong><em>Q: Does </em></strong><strong><em>name: under a source&#39;s </em></strong><strong><em>tables: have to match the actual warehouse table name?</em></strong></blockquote><blockquote><em>By default yes, but the optional </em><em>identifier: property lets you alias it, useful when the real table name is cryptic or inconsistent.</em></blockquote><blockquote><strong><em>Q: Do you have to run </em></strong><strong><em>dbt source freshness separately, or is it part of a normal </em></strong><strong><em>dbt run?</em></strong></blockquote><blockquote><em>It&#39;s a fully separate, standalone command, not included in </em><em>dbt run or </em><em>dbt build at all.</em></blockquote><blockquote><strong><em>Q: If a source is stale, does the whole downstream pipeline fail, or can you set up freshness without a hard stop?</em></strong></blockquote><blockquote><em>Built-in via severity tiers: </em><em>warn_after logs a warning without failing the command (pipeline continues), </em><em>error_after causes a non-zero exit (a </em><em>&amp;&amp;-chained pipeline stops there). No custom scripting needed for the soft-warning case.</em></blockquote><blockquote><strong><em>Q: Can freshness failures trigger an email, or only logs?</em></strong></blockquote><blockquote><em>No built-in alerting in dbt Core itself, that comes from the orchestrator reacting to the command’s exit code, or from dbt Cloud’s native job notifications if using that platform.</em></blockquote><blockquote><strong><em>Q: Is freshness based on the last-updated record’s column, or the table’s own metadata?</em></strong></blockquote><blockquote><em>A real column (</em><em>loaded_at_field) that the ingestion process populates, deliberately, not the warehouse&#39;s generic table-modification metadata, which reflects any write, not specifically new business data.</em></blockquote><blockquote><strong><em>Q: If there’s no loaded_at/updated_at column at all, can freshness still be applied?</em></strong></blockquote><blockquote><em>No, the mechanism fundamentally requires a </em><em>MAX(column) comparison against the current time. Without a reliable timestamp column, freshness simply cannot be configured for that source.</em></blockquote><h3>4. The DAG and Documentation</h3><h3>What the DAG is</h3><p><strong>D</strong>irected <strong>A</strong>cyclic <strong>G</strong>raph: the dependency graph built automatically from every ref() and source() call across the whole project. Directed means dependencies point one way; acyclic means no loops (a circular ref() chain throws a compile error). Nothing about it is manually configured, it&#39;s derived entirely from the ref()/source() calls in model files.</p><h3>What the DAG is actually used for</h3><p><strong>Build order:</strong> dbt topologically sorts the graph so nothing builds before its dependencies exist.</p><p><strong>Parallelization:</strong> models with no relationship to each other can build simultaneously, up to the configured threads count. threads doesn&#39;t override the DAG, dependent models still build in correct order regardless of thread count, it only controls how many <em>independent</em> models can build at once. If never explicitly set, most setups (dbt Cloud especially) still apply a sensible default; check the actual active value with dbt debug.</p><p><strong>Selective runs with </strong><strong>--select:</strong></p><pre>dbt run --select fact_orders     # ONLY fact_orders, nothing else<br>dbt run --select fact_orders+    # fact_orders AND everything downstream<br>dbt run --select +fact_orders    # fact_orders AND everything upstream (its dependencies)<br>dbt run --select +fact_orders+   # the full chain, both directions<br>dbt run --select staging.*       # an entire folder<br>dbt run --select tag:finance     # models tagged a certain way</pre><p><strong>Important:</strong> selecting a single model without + does not automatically include its dependencies. If int_orders_joined hasn&#39;t been built yet, dbt run --select fact_orders alone will fail, since the table it reads from doesn&#39;t exist. +fact_orders explicitly tells dbt to also build everything upstream first.</p><h3>Documentation: dbt docs generate</h3><pre>dbt docs generate   # writes target/manifest.json, target/catalog.json, target/index.html<br>dbt docs serve       # serves it locally in a browser</pre><p><strong>manifest.json</strong> — the DAG itself as structured JSON: every model, its ref()/source() dependencies, config, columns, tests, descriptions. Generated purely by parsing project files, no database connection needed.</p><p><strong>catalog.json</strong> — real warehouse metadata: actual column types, whether a model currently exists as a table or view, row counts. This part <em>does</em> require a live warehouse connection.</p><p><strong>index.html</strong> — a static single-page app that reads the two JSON files and renders the interactive lineage graph and everything else in-browser.</p><p><strong>Not a live view — must be regenerated manually (or via a pipeline step) every time the project changes.</strong> In a real production setup, dbt docs generate is typically a step immediately after dbt build in the scheduled pipeline itself, so the hosted docs site stays current without anyone remembering to run it by hand. On dbt Core, the resulting static files are then commonly deployed somewhere shared (an S3 bucket configured for static hosting, an internal server), since dbt docs serve alone only serves locally on one machine.</p><h3>Documenting a model, including its grain</h3><pre>models:<br>  - name: fact_orders<br>    description: &quot;One row per order. Grain: order_id.&quot;<br>    columns:<br>      - name: order_id<br>        description: &quot;Primary key. Unique identifier for each order.&quot;<br>        tests: [unique, not_null]<br>      - name: customer_id<br>        tests:<br>          - relationships:<br>              to: ref(&#39;dim_customer&#39;)<br>              field: customer_id</pre><p>This is precisely where a fact table’s grain statement becomes formally, visibly documented, with the uniqueness test that enforces it attached right alongside it, so anyone browsing the docs sees both the claim and the automated proof it’s being enforced, rather than the grain living only in someone’s memory or a Slack message.</p><h3>Clicking into a node in the lineage graph shows</h3><p>Model description, full column list with individual descriptions, which tests are attached to each column, materialization type, compiled SQL (viewable directly, useful for checking what a macro/ref() actually resolved to without digging through target/compiled/ manually), raw pre-compiled SQL, tags, and source freshness status if the node is a source.</p><h3>{{ doc() }}: reusable documentation blocks</h3><pre>{% docs customer_id_doc %}<br>The unique identifier for a customer, sourced from the `id` column<br>in the raw CRM export.<br>{% enddocs %}</pre><pre>columns:<br>  - name: customer_id<br>    description: &quot;{{ doc(&#39;customer_id_doc&#39;) }}&quot;</pre><p>The same “write it once, reuse everywhere” principle, applied to documentation text.</p><blockquote><strong><em>Q: Is the </em></strong><strong><em>threads count required to be defined for parallelization to happen at all?</em></strong></blockquote><blockquote><em>No, most setups have a default even if never explicitly configured. </em><em>threads controls how many independent models can build simultaneously; it never overrides the DAG&#39;s required build order.</em></blockquote><blockquote><strong><em>Q: Does plain </em></strong><strong><em>dbt run build the whole project, and does </em></strong><strong><em>--select only run that model plus dependencies?</em></strong></blockquote><blockquote><em>Plain </em><em>dbt run builds everything. </em><em>--select model_name alone runs </em>only<em> that model, nothing upstream or downstream, unless </em><em>+ is explicitly added on either side.</em></blockquote><blockquote><strong><em>Q: Does the docs site auto-update, or does it need to be regenerated every time?</em></strong></blockquote><blockquote><em>Must be manually regenerated (</em><em>dbt docs generate) every time the project changes; it&#39;s a static snapshot, not a live view. Typically wired into the pipeline as an explicit step so it stays current automatically.</em></blockquote><blockquote><strong><em>Q: What does clicking into the lineage graph actually show, just table names?</em></strong></blockquote><blockquote><em>Far more: descriptions, full column lists, attached tests, materialization type, compiled and raw SQL, tags, and freshness status for sources.</em></blockquote><h3>5. Materializations</h3><h3>The core idea</h3><p>A materialization is the strategy dbt uses to physically build a model. dbt wraps the compiled SELECT in different DDL/DML depending on what&#39;s configured.</p><p><strong>DDL (Data Definition Language)</strong> defines/modifies the <em>structure</em> of objects: CREATE TABLE, CREATE VIEW, ALTER TABLE, DROP TABLE. <strong>DML (Data Manipulation Language)</strong> operates on the <em>data</em> inside existing structures: INSERT, UPDATE, DELETE, MERGE. This distinction is the precise, technical reason table and incremental behave so differently in cost: table reruns DDL (rebuild the whole object) every time, incremental runs DML (modify existing rows) after the first run.</p><h3>view (default if unspecified)</h3><pre>CREATE OR REPLACE VIEW analytics.stg_orders AS (SELECT * FROM raw.orders);</pre><p>No data stored, always current, recomputed on every query. Cheapest to “build,” but every downstream query pays the compute cost every time.</p><h3>table</h3><pre>CREATE OR REPLACE TABLE analytics.fact_orders AS (SELECT * FROM analytics.int_orders_joined);</pre><p>Computed once, stored, fast to query afterward. <strong>Fully rebuilt from scratch every single run</strong>, even if only a handful of new rows exist. Does not scale to genuinely large fact tables where most data is unchanged day to day.</p><h3>incremental</h3><pre>{{ config(materialized=&#39;incremental&#39;, unique_key=&#39;order_id&#39;) }}</pre><pre>SELECT * FROM {{ ref(&#39;stg_orders&#39;) }}<br>{% if is_incremental() %}<br>WHERE order_date &gt; (SELECT MAX(order_date) FROM {{ this }})<br>{% endif %}</pre><p>First run: behaves like table, full build. Every subsequent run: only new/changed rows get processed and merged/appended/inserted into the existing table.</p><p><strong>is_incremental():</strong> returns True only when the model is configured as incremental, the target table already exists, and the run is not --full-refresh. On first run or a full refresh, it&#39;s False, the filter is skipped, the full dataset builds.</p><p><strong>{{ this }}:</strong> a built-in Jinja variable referring to the model&#39;s own fully-qualified table name, used here to query the table&#39;s own current max date.</p><h3>Incremental strategies</h3><p><strong>append</strong> — simplest: new rows just get inserted, no update logic, no duplicate protection at all. Right only for genuinely immutable, append-only data (event logs). Wrong for anything a row&#39;s value could later be corrected.</p><p><strong>merge</strong> — the standard default, uses the warehouse&#39;s native MERGE. For each incoming row: matching unique_key found → existing row <strong>updated</strong>; no match → new row <strong>inserted</strong>. This is precisely why unique_key needs to genuinely represent the table&#39;s grain, since it&#39;s what makes reruns after a failure or backfill safe and idempotent.</p><pre>MERGE INTO fact_scores_daily AS target<br>USING (SELECT * FROM staging_temp) AS source<br>ON target.fact_score_key = source.fact_score_key<br>WHEN MATCHED THEN UPDATE SET ...<br>WHEN NOT MATCHED THEN INSERT ...</pre><p><strong>delete+insert</strong> — deletes all existing rows matching the incoming batch&#39;s key values, then inserts the new rows fresh. Used when unique_key is a coarser grain than individual rows (e.g. unique_key=&#39;order_date&#39; when the real grain is per-order), since merge can&#39;t cleanly reconcile &quot;10 old rows for this date&quot; against &quot;12 new rows&quot; at row grain. Also used on engines where native MERGE isn&#39;t well supported.</p><h3>ephemeral</h3><p>Already covered in Section 1: no database object, purely a compile-time CTE inlined into whatever references it.</p><h3>--full-refresh</h3><pre>dbt run --select fact_scores_daily --full-refresh</pre><p>Ignores the existing table, drops/recreates it, rebuilds full history from scratch (is_incremental() returns False). Needed when the incremental logic itself changed (e.g. a bug fix to how the key is generated), since normal incremental runs only ever touch new rows going forward, never revisiting history unless explicitly told to.</p><blockquote><strong><em>Q: What is DDL/DML?</em></strong></blockquote><blockquote><em>DDL defines structure (CREATE, ALTER, DROP). DML manipulates data within existing structures (INSERT, UPDATE, DELETE, MERGE). It’s the precise reason </em><em>table (DDL, full rebuild every run) is so much more expensive than </em><em>incremental (DML, only modifies changed rows after the first run).</em></blockquote><h3>6. Incremental Models — Practical Deep Dive</h3><h3>Backfilling historical data</h3><p><strong>The problem:</strong> a bug in the score calculation logic affected a two-week window months ago. A full --full-refresh is often the wrong fix, it rebuilds the <em>entire</em> table, and if raw source data has limited retention, it may not even be able to reconstruct that old window at all, or it wastefully recomputes years of history to fix two weeks.</p><p><strong>The pattern: override the incremental filter manually, for one targeted run, using a variable</strong></p><pre>dbt run --select fact_scores_daily --vars &#39;{&quot;backfill_start&quot;: &quot;2024-03-01&quot;, &quot;backfill_end&quot;: &quot;2024-03-14&quot;}&#39;</pre><pre>{% if is_incremental() %}<br>    {% if var(&#39;backfill_start&#39;, none) is not none %}<br>    WHERE scoreDate BETWEEN &#39;{{ var(&quot;backfill_start&quot;) }}&#39; AND &#39;{{ var(&quot;backfill_end&quot;) }}&#39;<br>    {% else %}<br>    WHERE scoreDate &gt; (SELECT MAX(scoreDate) FROM {{ this }})<br>    {% endif %}<br>{% endif %}</pre><p>var() reads a value passed via --vars, resolved at compile time. When backfill_start is passed, the filter switches to a fixed range instead of the normal max-date logic. Since the strategy is merge, reprocessing that range safely <strong>updates</strong> existing rows rather than duplicating them, as long as the key genuinely represents the grain. <strong>This only works safely because of </strong><strong>merge</strong> — with append, the same backfill would create true duplicate rows, since append has no matching logic at all.</p><h3>Late-arriving data: a genuinely subtle, silent bug</h3><p><strong>The problem, walked through day by day.</strong> Filter: WHERE scoreDate &gt; (SELECT MAX(scoreDate) FROM {{ this }}).</p><pre>Day 1: table&#39;s max scoreDate = 2024-06-10. Today&#39;s run picks up 2024-06-11. Correct.<br>       New max is now 2024-06-11.<br>Day 1, later: a row for 2024-06-09 arrives LATE upstream, not yet loaded.<br>Day 2: filter checks &gt; &#39;2024-06-11&#39;. Is 2024-06-09 &gt; 2024-06-11? No.<br>       The late row is EXCLUDED. Nothing errors. It&#39;s simply never picked up.<br>Day 3, 4, 5...: the filter&#39;s threshold only ever moves FORWARD. It never<br>       looks back at 2024-06-09 again. That row is now permanently lost.</pre><p>Nothing fails, dbt run succeeds daily, row counts look roughly normal. The only symptom is a missing or stale row for that specific date, discoverable only if someone happens to query it directly.</p><p><strong>The fix: a lookback window, re-checking a bit of recent history on every run</strong></p><pre>WHERE scoreDate &gt; (SELECT DATEADD(day, -3, MAX(scoreDate)) FROM {{ this }})</pre><p>Now Day 2’s filter threshold is 2024-06-08, not 2024-06-11. The late 2024-06-09 row is correctly re-included this time. <strong>This re-processing is only safe because the strategy is </strong><strong>merge, an idempotent operation</strong>: reprocessing already-correct rows for 2024-06-10/2024-06-11 just re-matches and re-updates them with identical values, a harmless no-op. If the strategy were append, this same lookback pattern would create real, growing duplicate rows on every single run, since append has no way to recognize &quot;I&#39;ve already inserted this.&quot;</p><p>The size of the window is a genuine tuning decision, based on how late data has actually been observed to arrive in the specific pipeline, the same kind of judgment call as sizing warn_after/error_after for source freshness.</p><p><strong>An alternative, more explicit fix:</strong> filter on a genuine ingestion timestamp (_loaded_at) rather than the business date, so a late-arriving row for an old business date still gets picked up correctly by whichever run it actually lands in, since its _loaded_at reflects today, regardless of which scoreDate it belongs to.</p><h3>Performance tuning for incremental models</h3><p><strong>Sargability of the incremental filter.</strong> Since this filter runs on <em>every single scheduled run, forever</em>, an unprunable filter (a function wrapped around the filtered column) here is a permanent, compounding cost, not a one-time issue.</p><p><strong>incremental_predicates:</strong> limits how much of the <em>target</em> table a MERGE has to scan when looking for matches, directly leveraging partition pruning during the merge itself.</p><pre>{{<br>    config(<br>        materialized=&#39;incremental&#39;,<br>        unique_key=&#39;fact_score_key&#39;,<br>        incremental_strategy=&#39;merge&#39;,<br>        incremental_predicates=[&quot;target.scoreDate &gt;= dateadd(&#39;day&#39;, -7, current_date)&quot;]<br>    )<br>}}</pre><p><strong>merge vs. </strong><strong>append cost, honestly weighed:</strong> merge is safer (idempotent, handles reruns/backfills/late data correctly) but inherently more expensive, since it must check for existing matches first. For a genuinely append-only, never-updated table (raw clickstream), append can be a deliberate performance choice, accepting that reruns/backfills then require manual care (explicit deletes before re-appending) rather than being automatically safe.</p><p><strong>Clustering:</strong> applying cluster_by on the grain&#39;s date column compounds well with incremental loading, since new data naturally arrives roughly chronologically, though an explicit cluster key still helps if backfills or late data disrupt that natural ordering over time.</p><blockquote><strong><em>Q: Doesn’t looking back 3 extra days on every run add unnecessary reprocessing cost, and isn’t that risky if something goes wrong during a rerun?</em></strong></blockquote><blockquote><em>The extra cost is real but small, bounded, and predictable, a deliberate tradeoff against the alternative (silent, undetectable data loss). The safety of repeatedly reprocessing already-correct data depends entirely on the strategy being </em><em>merge (idempotent): reprocessing the same days repeatedly always produces the same correct result. With </em><em>append, the same pattern would be genuinely dangerous, since it has no mechanism to prevent duplicates. If upstream data for an already-processed day were itself corrupted at the moment of a rerun, the lookback window would re-pull and overwrite with that bad data, but that risk exists for any rerun of any incremental pipeline regardless of the lookback window, it isn&#39;t something the lookback introduces.</em></blockquote><h3>7. Seeds</h3><h3>The core idea</h3><p>A seed is a <strong>CSV file, checked into version control</strong>, that dbt seed loads directly into the warehouse as a table.</p><pre>country_code,country_name,region<br>US,United States,North America<br>CA,Canada,North America</pre><pre>dbt seed</pre><p>Once loaded, referenced with ref(), not source(), since dbt itself created the table:</p><pre>SELECT o.orderId, c.region<br>FROM {{ ref(&#39;stg_orders&#39;) }} o<br>JOIN {{ ref(&#39;country_region_mapping&#39;) }} c ON o.countryCode = c.country_code</pre><h3>What seeds are for, and explicitly not for</h3><p><strong>Good use cases:</strong> small, static, rarely-changing reference data with no real operational source system, a country-region mapping, a manually curated holiday list, status code labels.</p><p><strong>Not for:</strong> large datasets (a huge CSV bloats git permanently, and every dbt seed reloads the <em>entire</em> file, no incremental loading exists for seeds); frequently-changing data (updating means editing a file, committing, and rerunning, a manual, code-review-gated process, wrong for anything needing daily or near-real-time updates); or anything with an actual source system behind it, that belongs as a source() through a proper ingestion pipeline, not a seed.</p><h3>A sharp, genuinely common gotcha: type inference</h3><pre>zip_code,region<br>02134,Northeast</pre><p>Without explicit column types, dbt may infer zip_code as an integer, silently dropping the leading zero (02134 → 2134). Fix with explicit typing:</p><pre>seeds:<br>  my_project:<br>    country_region_mapping:<br>      +column_types:<br>        country_code: varchar(2)</pre><h3>Seeds get full DAG, testing, and documentation support</h3><pre>seeds:<br>  - name: country_region_mapping<br>    description: &quot;Static mapping of country codes to regions, maintained manually.&quot;<br>    columns:<br>      - name: country_code<br>        tests: [unique, not_null]</pre><p>Since seeds are referenced with ref(), they&#39;re proper DAG nodes with the same testing/documentation capabilities as any model. The only real difference is <em>how the data gets in</em> (a CSV load via dbt seed) rather than a SELECT query via dbt run.</p><h3>Where a seed actually gets built</h3><p>The schema/database it lands in comes from the active profile/target’s configuration, not anything special about seeds. If a model’s schema config is set, note that by default dbt <strong>appends</strong> it as a suffix to the profile&#39;s base schema (e.g. profile schema analytics + model config schema: reference_data → actual schema analytics_reference_data), unless a custom generate_schema_name macro overrides that default behavior.</p><h3>8. Snapshots</h3><h3>The core problem this solves</h3><p>A source system almost always only shows <strong>current state</strong>. If a customer’s state updates from Texas to California, a plain SELECT the next day just shows California, the fact it was ever Texas is gone, overwritten. dbt snapshots automate capturing that history, implementing SCD Type 2 without writing manual UPDATE/INSERT logic.</p><h3>Basic anatomy</h3><pre>{% snapshot dim_customer_snapshot %}<br>{{<br>    config(<br>      target_schema=&#39;snapshots&#39;,<br>      unique_key=&#39;customerId&#39;,<br>      strategy=&#39;timestamp&#39;,<br>      updated_at=&#39;updatedAt&#39;,<br>    )<br>}}<br>SELECT * FROM {{ source(&#39;raw&#39;, &#39;customers&#39;) }}<br>{% endsnapshot %}</pre><pre>dbt snapshot</pre><p>First run:</p><pre>customerId=101, name=Alice, state=Texas, dbt_valid_from=2024-01-01, dbt_valid_to=NULL, dbt_scd_id=abc123</pre><p>After Alice moves, next run:</p><pre>customerId=101, name=Alice, state=Texas,      dbt_valid_from=2024-01-01, dbt_valid_to=2024-03-15, dbt_scd_id=abc123<br>customerId=101, name=Alice, state=California, dbt_valid_from=2024-03-15, dbt_valid_to=NULL,       dbt_scd_id=def456</pre><p>dbt_valid_from, dbt_valid_to, dbt_scd_id are dbt-managed columns, never written manually. dbt_valid_to = NULL marks the current version.</p><h3>Two change-detection strategies</h3><p><strong>strategy=&#39;timestamp&#39;</strong> — compares an updated_at column&#39;s current value against what was recorded last time; newer means changed. Requires the source system to reliably bump this on every attribute change, same reliability requirement as loaded_at_field for source freshness. Cheapest to check, the common default when a trustworthy column exists.</p><p><strong>strategy=&#39;check&#39;</strong> — explicitly compares listed column values:</p><pre>{{ config(unique_key=&#39;customerId&#39;, strategy=&#39;check&#39;, check_cols=[&#39;state&#39;, &#39;customerName&#39;, &#39;email&#39;]) }}</pre><p>Used when no reliable updated_at exists, or to track changes only to specific columns while ignoring noise from irrelevant ones changing. check_cols=&#39;all&#39; compares every column, simplest but most expensive and most sensitive, any change to any column (even ones you don&#39;t care about) triggers new history.</p><p><strong>Decision framework:</strong> trustworthy updated_at + want all attributes tracked → timestamp. No reliable timestamp → check is the only option. Want to track only specific columns → check with explicit check_cols, even if updated_at exists.</p><h3>unique_key, revisited</h3><p>Must be the <strong>natural key</strong> (customerId), not a surrogate key, exactly the grain discussion from data modeling. This is what lets dbt recognize &quot;this incoming row is the same real-world entity as one already snapshotted&quot; so it can correctly compare and version it.</p><h3>target_schema: a genuine gotcha</h3><p>Unlike regular models, a snapshot’s schema is <strong>hardcoded directly in its config</strong>, not following normal dev/prod schema switching automatically. Without explicit environment-awareness, dev and prod runs could both write to the same schema:</p><pre>{{ config(target_schema=&#39;snapshots_dev&#39; if target.name == &#39;dev&#39; else &#39;snapshots&#39;, ...) }}</pre><h3>Running snapshots in a pipeline</h3><p>dbt snapshot is a separate command, not part of dbt run/dbt build. Typically sequenced <strong>before</strong> the main build, so downstream models see today&#39;s captured state, not yesterday&#39;s.</p><pre>dbt source freshness<br>dbt snapshot<br>dbt build</pre><p><strong>Frequency matters:</strong> if a customer’s state changes twice within one day and the snapshot only runs once daily, only the <em>last</em> state as of the run gets captured, intermediate changes within that window are permanently lost. A snapshot’s history is only as granular as how often it runs.</p><h3>Querying a snapshot</h3><pre>-- Current state only<br>SELECT * FROM snapshots.dim_customer_snapshot WHERE dbt_valid_to IS NULL</pre><pre>-- What was true on a specific historical date<br>SELECT * FROM snapshots.dim_customer_snapshot<br>WHERE &#39;2024-02-01&#39; BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, &#39;9999-12-31&#39;)</pre><p>In practice, a snapshot is rarely queried directly, a clean mart model is usually built on top of it, mirroring the staging → marts layering principle.</p><h3>How widely this is actually used in practice</h3><p>The underlying need (tracking historical dimension changes) is close to universal in mature analytics functions. Whether teams reach for dbt’s specific {% snapshot %} feature varies: some source systems already provide history via CDC upstream, making a dbt snapshot redundant; some teams hand-roll the same SCD Type 2 logic inside a regular incremental merge model instead of using the dedicated feature; and SCD Type 1 (simple overwrite) is used far more often than Type 2 in practice, simply because Type 2 is more complex to build and most day-to-day reporting doesn&#39;t actually require point-in-time accuracy.</p><blockquote><strong><em>Q: How is a snapshot different from a regular model in terms of where it gets built?</em></strong></blockquote><blockquote><em>target_schema is hardcoded in the snapshot&#39;s own config rather than following the profile&#39;s normal dev/prod schema switching, so it needs to be made explicitly environment-aware to avoid dev and prod colliding.</em></blockquote><h3>9. dbt Tests</h3><h3>The core idea</h3><p>Every dbt test is fundamentally a query that should return <strong>zero rows</strong> if everything is correct. One or more rows returned means the test fails.</p><pre>SELECT * FROM some_model WHERE &lt;condition that should never be true&gt;</pre><h3>The four built-in generic tests</h3><pre>models:<br>  - name: fact_orders<br>    columns:<br>      - name: order_id<br>        tests: [unique, not_null]<br>      - name: customer_id<br>        tests:<br>          - not_null<br>          - relationships:<br>              to: ref(&#39;dim_customer&#39;)<br>              field: customer_id<br>      - name: status<br>        tests:<br>          - accepted_values:<br>              values: [&#39;pending&#39;, &#39;shipped&#39;, &#39;delivered&#39;, &#39;cancelled&#39;]</pre><p><strong>unique</strong> — same mechanism as the grain-enforcement test from data modeling, applied to a single column. <strong>not_null</strong>. <strong>accepted_values</strong> — catches an unrecognized new status code silently flowing through from upstream. <strong>relationships</strong> — a referential integrity / foreign key check most cloud warehouses don&#39;t enforce natively:</p><pre>SELECT f.customer_id<br>FROM fact_orders f<br>LEFT JOIN dim_customer d ON f.customer_id = d.customer_id<br>WHERE d.customer_id IS NULL AND f.customer_id IS NOT NULL</pre><p>Catches orphaned foreign keys, a fact row referencing a customer that doesn’t exist in the dimension, which silently drops (INNER JOIN) or nulls out (LEFT JOIN) in any downstream join otherwise.</p><p><strong>A concrete scenario </strong><strong>relationships catches:</strong> if a snapshot-based dimension table&#39;s build gets sequenced <em>after</em> the fact table&#39;s build due to a pipeline ordering issue, a brand-new customer&#39;s score could be computed before their row exists in the dimension. No error, just a quietly incomplete report downstream, until a relationships test surfaces exactly this ordering bug.</p><p><strong>Why it’s genuinely underused in practice:</strong> more expensive to run than single-column tests (requires a join across two tables, sometimes large ones); only works cleanly when the dimension is fully built before the test runs, an ordering issue can produce a false-looking failure that’s actually a pipeline sequencing bug, not a data quality bug; and cross-table checks are less intuitive to think of than single-column ones. Can be scoped to reduce cost on huge tables:</p><pre>config:<br>  where: &quot;order_date &gt;= dateadd(&#39;day&#39;, -30, current_date)&quot;</pre><h3>Multi-column uniqueness, revisited precisely</h3><pre>tests:<br>  - dbt_utils.unique_combination_of_columns:<br>      combination_of_columns: [customerId, scoreDate]</pre><p>This is the literal grain-enforcement test from data modeling, not a built-in test, it comes from the dbt_utils package (Section 10).</p><h3>Custom generic tests</h3><pre>-- tests/generic/test_is_positive.sql<br>{% test is_positive(model, column_name) %}<br>    SELECT * FROM {{ model }} WHERE {{ column_name }} &lt; 0<br>{% endtest %}</pre><p>Written once, reusable across any column on any model, model and column_name are parameters resolved via Jinja, the same mechanism as any macro.</p><h3>Singular tests</h3><pre>-- tests/assert_no_future_order_dates.sql<br>SELECT * FROM {{ ref(&#39;fact_orders&#39;) }} WHERE order_date &gt; CURRENT_DATE</pre><p>A raw .sql file directly in tests/, no {% test %} wrapper, no registration needed, automatically treated as a one-off, model-specific test. Use singular for a check unlikely to generalize elsewhere; generic when the same logic could apply to many models/columns.</p><h3>Severity: warn vs. error</h3><pre>tests:<br>  - not_null:<br>      config:<br>        severity: warn</pre><p>The same graduated-severity pattern as source freshness’s warn_after/error_after. A warn-level failure is logged but does not cause dbt test/dbt build to exit non-zero, so it won&#39;t block a pipeline the way error would.</p><h3>Why this matters for pipeline safety, tying to dbt build</h3><p>Tests are the enforcement mechanism that stops bad data from silently propagating downstream, directly operationalizing the “grain violations don’t throw errors” concern from data modeling and the “missing row is a signal something broke” idea from periodic snapshot fact tables. How exactly this stops a pipeline is covered fully in Section 12.</p><blockquote><strong><em>Q: What does the </em></strong><strong><em>relationships test actually check, and why is it useful?</em></strong></blockquote><blockquote><em>A referential integrity check: every non-null foreign key value in one model must exist in the referenced model&#39;s key column. It catches orphaned foreign keys, e.g. a fact row for a customer that hasn&#39;t landed in the dimension table yet due to a pipeline sequencing issue, silently invisible otherwise since it just produces a dropped or null row downstream.</em></blockquote><h3>10. dbt Packages (dbt_utils)</h3><h3>The core idea</h3><p>A dbt package is a bundle of pre-written macros, tests, and sometimes models, published by dbt Labs or the community, installed and used directly in a project, the “write once, reuse everywhere” principle applied at community scale.</p><pre># packages.yml<br>packages:<br>  - package: dbt-labs/dbt_utils<br>    version: [&quot;&gt;=1.0.0&quot;, &quot;&lt;2.0.0&quot;]</pre><pre>dbt deps</pre><h3>dbt_utils, the close-to-universal dependency</h3><p><strong>dbt_utils.generate_surrogate_key([&#39;customerId&#39;, &#39;scoreDate&#39;])</strong> — already used earlier for building deterministic grain-based keys. Concatenates given columns with a delimiter, <strong>handles NULLs safely</strong> (a NULL in raw string concatenation would otherwise make the entire result NULL, silently breaking the key), then hashes the result (MD5 by default).</p><p><strong>dbt_utils.unique_combination_of_columns</strong> — the multi-column uniqueness/grain test, not a dbt-core built-in.</p><p><strong>dbt_utils.date_spine()</strong> — generates a continuous, gap-free calendar of dates to LEFT JOIN real data onto, directly solving the &quot;missing vs. zero&quot; ambiguity problem from periodic snapshot fact tables, days with no activity show as 0 instead of silently not appearing at all.</p><p><strong>dbt_utils.pivot()</strong> — generates a pivot table&#39;s CASE WHEN columns automatically, a ready-made version of the hand-rolled {% for %} loop pattern.</p><p><strong>dbt_utils.star()</strong> — selects all columns except explicitly excluded ones, without hardcoding a full column list.</p><p><strong>dbt_utils.get_column_values()</strong> — runs SELECT DISTINCT at compile time and returns the results as a list, the ready-made, package version of a hand-written compile-time-query macro.</p><p><strong>Additional tests:</strong> dbt_utils.not_accepted_values (inverse of the built-in), dbt_utils.expression_is_true (flexible arbitrary boolean row-level check without writing a custom test macro), dbt_utils.recency (a lightweight &quot;at least one row within X time&quot; check, runnable as a normal test rather than the separate freshness command).</p><h3>Other notable packages, briefly</h3><p><strong>dbt-labs/codegen</strong> — auto-generates boilerplate YAML (source definitions, column lists) from existing tables. <strong>dbt-labs/dbt_expectations</strong> — a much larger, statistically-oriented test library. <strong>calogica/dbt_date</strong> — calendar-specific helpers (fiscal years, business days) beyond date_spine alone.</p><blockquote><strong><em>Q: Why does </em></strong><strong><em>dbt_utils matter so much if dbt already has built-in tests and macros?</em></strong></blockquote><blockquote><em>The built-in set is intentionally minimal. </em><em>dbt_utils fills genuinely common gaps, like multi-column uniqueness testing (essential for grain enforcement) and NULL-safe surrogate key generation, that nearly every real dimensional-modeling-heavy project needs but dbt core doesn&#39;t ship by default.</em></blockquote><h3>11. Environments and Targets</h3><h3>The core problem this solves</h3><p>Every developer needs to build and test without touching production data or the shared prod schema real dashboards depend on, while the same model code must eventually run correctly against production too. Environments and targets make one codebase behave correctly in both.</p><h3>profiles.yml</h3><pre># ~/.dbt/profiles.yml -- lives OUTSIDE the project, never in git (contains credentials)<br>my_project_profile:<br>  target: dev<br>  outputs:<br>    dev:<br>      type: snowflake<br>      user: jsmith<br>      password: &quot;{{ env_var(&#39;DBT_PASSWORD&#39;) }}&quot;<br>      database: my_database<br>      schema: dev_jsmith<br>      threads: 4<br>    prod:<br>      type: snowflake<br>      user: svc_dbt_prod<br>      password: &quot;{{ env_var(&#39;DBT_PROD_PASSWORD&#39;) }}&quot;<br>      database: my_database<br>      schema: analytics<br>      threads: 8</pre><p>profiles.yml lives outside the versioned project specifically because it holds credentials (or references to them via env_var(), another compile-time Jinja resolution). The dbt <em>project</em> is shared/versioned in git; profiles.yml is local, per-machine or per-CI-runner configuration.</p><h3>target.name: the mechanism behind every conditional used throughout this guide</h3><pre>dbt run --target dev<br>dbt run --target prod<br>dbt run                  # uses whichever output the `target:` default points to</pre><p>Every {% if target.name == &#39;prod&#39; %} used throughout this guide, switching staging materialization between view/ephemeral, scoping a dev query to 7 days, making a snapshot&#39;s target_schema environment-aware, is this same single mechanism applied to different problems.</p><h3>How ref() resolves differently per target</h3><pre>Compiling with target=dev:  SELECT * FROM my_database.dev_jsmith.stg_orders<br>Compiling with target=prod: SELECT * FROM my_database.analytics.stg_orders</pre><p>ref() reads the active target&#39;s database/schema and substitutes them at compile time, nothing about the model file itself changes.</p><h3>Personal dev schemas</h3><p>Each developer typically gets their own schema (dev_jsmith, dev_asmith), often auto-derived via a custom generate_schema_name macro. This ensures simultaneous developers never collide, nobody&#39;s in-progress broken work is visible in a shared space, and testing something risky like --full-refresh only ever touches one person&#39;s isolated schema, the same production-isolation principle from query optimization, applied developer-to-developer instead of analytics-to-production.</p><h3>How this actually triggers in a real CI/CD or orchestrated pipeline</h3><p>A human runs dbt run locally against their personal dev target while building. Once merged, the orchestrator (Jenkins, or Control-M triggering it) runs dbt run --target prod using a dedicated <strong>service account</strong> (svc_dbt_prod, not any individual&#39;s login), so production runs aren&#39;t tied to any one person&#39;s access.</p><h3>dbt_project.yml vs. profiles.yml</h3><p>dbt_project.yml lives inside the project, checked into git, defines project-level config (materializations per folder, variables, packages), same for everyone. profiles.yml lives outside, never in git, defines connection credentials and per-environment settings, different per machine/person/environment. (dbt Cloud manages this distinction through its UI&#39;s &quot;Environments&quot; concept instead of a local file.)</p><blockquote><strong><em>Q: How does one codebase safely support both individual developer work and shared production runs?</em></strong></blockquote><blockquote><em>Through named targets in the connection profile, each pointing at a different schema/database, with </em><em>target.name available in Jinja to conditionally change compiled behavior. Developers typically get isolated personal schemas so simultaneous work never collides, and production runs use a dedicated service account rather than any individual&#39;s credentials.</em></blockquote><h3>12. dbt build vs. run vs. test</h3><h3>The three core commands</h3><pre>dbt run       # builds models only, in DAG order. No tests, seeds, or snapshots.<br>dbt test      # runs tests against whatever already exists. Builds nothing.<br>dbt seed      # loads seed CSVs only.<br>dbt snapshot  # runs snapshots only.<br>dbt build     # seeds, snapshots, models, AND tests together, in DAG order, with failure propagation.</pre><h3>The critical distinction: failure propagation</h3><p>Running dbt run then dbt test separately means every model finishes building <em>before</em> any test runs, so a test failure on an early model can&#39;t stop later models from building on top of already-bad data:</p><pre>dbt run:  stg_orders builds (bad data, untested) -&gt; int_orders_joined builds on top<br>          -&gt; fact_orders builds on top -&gt; ALL THREE NOW BUILT ON BAD DATA<br>dbt test: NOW the not_null test on stg_orders finally fails, too late</pre><p>With dbt build instead: stg_orders builds, its test runs <em>immediately</em> and fails, dbt automatically <strong>skips</strong> building int_orders_joined and fact_orders since they depend on a model that just failed, while unrelated models still build and test normally.</p><p><strong>This skip behavior is the single most important reason </strong><strong>dbt build is the standard for a production pipeline.</strong> It&#39;s the actual mechanical enforcement of &quot;bad data shouldn&#39;t silently propagate downstream,&quot; the exact principle behind grain-violation tests and periodic-snapshot pipeline health checks from data modeling.</p><h3>When to still use the individual commands</h3><p>dbt run alone: fast iteration on one model&#39;s logic during active development, without waiting on the full test suite every tweak. dbt test alone: re-verify data quality on already-built, current models without spending compute rebuilding anything. dbt seed/dbt snapshot alone: rare in production (normally part of build), but useful in isolation while specifically debugging a seed file or snapshot change-detection logic.</p><p>--select works identically across all commands, the same DAG-based selection syntax throughout.</p><h3>dbt build still doesn&#39;t include everything</h3><p>Even build does not include dbt source freshness or dbt docs generate, both remain genuinely separate steps in a full production pipeline:</p><pre>dbt source freshness   # check raw source staleness first<br>dbt build                # seeds, snapshots, models, tests together<br>dbt docs generate         # regenerate the documentation site</pre><blockquote><strong><em>Q: Why prefer </em></strong><strong><em>dbt build over separately running </em></strong><strong><em>dbt run and </em></strong><strong><em>dbt test?</em></strong></blockquote><blockquote><em>dbt build interleaves testing with model building in DAG order and automatically skips downstream models when an upstream test fails, preventing bad data from propagating through the rest of the pipeline before anyone notices. Separate </em><em>run/</em><em>test lets everything finish building before any test result is known.</em></blockquote><h3>13. Quick Extras: Exposures, Contracts, Semantic Layer, Hooks</h3><p><strong>Exposures</strong> — declare that a downstream asset (dashboard, report, an ML model) depends on specific dbt models, even though that asset lives entirely outside dbt.</p><pre>exposures:<br>  - name: executive_revenue_dashboard<br>    type: dashboard<br>    depends_on: [ref(&#39;fact_orders&#39;), ref(&#39;dim_customer&#39;)]<br>    owner: {name: Data Team, email: data@company.com}</pre><p>Extends lineage past the warehouse, so a proposed change to fact_orders visibly shows &quot;this will affect the executive dashboard&quot; before the change is made, not after something breaks.</p><p><strong>Contracts</strong> — explicitly declare and <em>enforce</em> a model’s column names/types before it builds.</p><pre>models:<br>  - name: fact_orders<br>    config:<br>      contract: {enforced: true}<br>    columns:<br>      - name: order_id<br>        data_type: int</pre><p>If the actual SELECT would produce a different shape, the build fails immediately, before ever writing mismatched data. Stronger than a post-build data test, since it checks the <em>shape</em> of the output before it&#39;s committed, protecting downstream consumers from a silent schema change.</p><p><strong>Semantic Layer / MetricFlow</strong> — define a business metric’s calculation logic once, centrally, so “revenue” means the same thing across every BI tool and team, rather than each analyst’s dashboard quietly defining it slightly differently (with or without tax, refunds, a specific date field).</p><pre>metrics:<br>  - name: total_revenue<br>    type: simple<br>    type_params: {measure: order_amount}</pre><p><strong>Hooks</strong> — SQL that runs automatically around a model’s execution, or an entire run, for side effects unrelated to the model’s actual data logic.</p><pre>{{ config(post_hook=&quot;GRANT SELECT ON {{ this }} TO ROLE analyst_role&quot;) }}</pre><p>pre-hook/post-hook run around a single model (common use: re-granting permissions, since CREATE OR REPLACE TABLE wipes prior grants each time). on-run-start/on-run-end, set in dbt_project.yml, fire once for the whole invocation, not per-model, useful for a single logging statement or a schema-wide permission grant.</p><h3>The One Unifying Idea</h3><p>Nearly everything in dbt is one principle, applied at increasing scope:</p><blockquote><strong><em>Write correct logic exactly once, and let every downstream consumer reuse it, rather than duplicating it and letting copies drift out of sync.</em></strong></blockquote><p>Staging models apply cleanup once instead of in every mart. Macros generate SQL once instead of hand-typing it repeatedly. ref()/source() centralize schema names once instead of hardcoding them everywhere. dbt_utils applies this at community scale, thousands of engineers&#39; collectively debugged solutions, installed rather than reinvented. Even target.name conditionals are this same idea, one model file correctly serving every environment instead of maintaining separate copies per environment.</p><h3>Quick-Reference Summary</h3><ul><li><strong>Project structure (staging → intermediate → marts) is a convention, not a dbt feature</strong> — staging does light cleanup only, no joins; intermediate handles joins/business logic; marts are the final fact/dimension tables.</li><li><strong>Staging/intermediate are conventionally views</strong>; <strong>ephemeral inlines a model as a compile-time CTE with no database object at all</strong>, but can’t be queried or tested directly, and forces “recomputed” cost if referenced by multiple downstream models.</li><li><strong>Jinja compiles to plain SQL before anything touches the warehouse</strong>; the warehouse never sees Jinja syntax.</li><li><strong>Macros are reusable Jinja functions</strong>; ref() and source() are themselves built-in macros.</li><li><strong>Compilation happens fresh on every single </strong><strong>dbt run</strong>, not once and cached forever, but anything that must reflect the exact moment of <em>execution</em> (like a date filter) belongs in plain SQL, not Jinja fetched at compile time.</li><li><strong>{% if %} cannot see row/column values</strong> — it only sees compile-time information (variables, target, macro results); row-level logic is plain SQL CASE WHEN.</li><li><strong>The DAG is built entirely from </strong><strong>ref()/</strong><strong>source() calls</strong> — a hardcoded table name silently breaks build ordering, lineage, and selective runs with no error.</li><li><strong>--select model+ includes downstream; </strong><strong>+model includes upstream; a bare </strong><strong>--select model includes neither.</strong></li><li><strong>dbt docs generate is a manual snapshot, not a live view</strong> — needs to be regenerated (and typically re-deployed) as an explicit pipeline step to stay current.</li><li><strong>table = full DDL rebuild every run; </strong><strong>incremental = DML on just the new/changed rows</strong> — the entire reason incremental scales and table doesn&#39;t for large fact tables.</li><li><strong>merge is the safe default incremental strategy</strong>, matching on a unique_key that must genuinely represent the grain; append has zero duplicate protection; delete+insert operates at a coarser batch grain.</li><li><strong>Backfilling should override the incremental filter for one targeted run</strong>, not trigger a full refresh, and is only safe under an idempotent strategy like merge.</li><li><strong>A pure “greater than max date” incremental filter can permanently and silently miss late-arriving data</strong> — fix with a lookback window or a genuine ingestion-timestamp filter, both only safe under merge.</li><li><strong>Seeds are for small, static, source-less reference data checked into git</strong> — not for large or frequently-changing datasets, and watch for silent type inference issues (leading zeros).</li><li><strong>Snapshots automate SCD Type 2</strong>, using timestamp or check strategy for change detection; target_schema is hardcoded in config and needs explicit environment-awareness; a snapshot&#39;s history is only as granular as how often it&#39;s run.</li><li><strong>Every dbt test is a query that should return zero rows.</strong> relationships is an underused but valuable foreign-key check that catches orphaned rows from pipeline sequencing issues.</li><li><strong>dbt_utils fills gaps dbt core intentionally leaves minimal</strong> — multi-column uniqueness testing and NULL-safe surrogate keys are the two most load-bearing examples.</li><li><strong>profiles.yml (credentials, per-environment) stays out of git; </strong><strong>dbt_project.yml (shared project config) stays in git.</strong></li><li><strong>target.name is the single mechanism behind every dev/prod conditional</strong>, and production runs should use a dedicated service account, not an individual&#39;s credentials.</li><li><strong>Prefer </strong><strong>dbt build over separate </strong><strong>run/</strong><strong>test</strong> — it skips downstream models when an upstream test fails, stopping bad data from propagating, which separate commands cannot do.</li><li><strong>dbt build still excludes </strong><strong>dbt source freshness and </strong><strong>dbt docs generate</strong> — a complete pipeline sequences all three explicitly.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6f10cd2ba6c3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Fact, Dimension, Grain: The Data Modeling Guide Nobody Gives You]]></title>
            <link>https://bysuchi.medium.com/fact-dimension-grain-the-data-modeling-guide-nobody-gives-you-c44857e3d220?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/c44857e3d220</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[interview]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Mon, 17 Aug 2026 02:23:44 GMT</pubDate>
            <atom:updated>2026-08-19T14:01:53.150Z</atom:updated>
            <content:encoded><![CDATA[<p>Fact tables, dimension tables, star schemas, and slowly changing dimensions, explained the way I wish someone had explained them to me when I started</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4GxEf7yPs0qHRE4tOpWfig.avif" /><figcaption>Photo from <a href="https://unsplash.com/photos/a-black-and-white-photo-of-a-bunch-of-cubes-gC_aoAjQl2Q">Unsplash</a></figcaption></figure><p>“How would you model this data” is the second most common category of question to an Analytics Engineer, right behind query optimization. This guide walks through every core data modeling concept you need, built up in dependency order, fact and dimension tables first, then grain, then normalization, then the schema patterns and change-tracking strategies that sit on top of them, with the same worked examples carried through so the ideas connect to each other instead of living in isolation.</p><p>Curious what the <a href="https://bysuchi.medium.com/query-optimization-concepts-for-an-analytics-engineer-3000f9dfbd4a">first</a> and <a href="https://bysuchi.medium.com/dbt-fundamentals-for-analytics-engineers-a-complete-guide-6f10cd2ba6c3">third</a> most common categories are? Click on the links</p><p><strong>What this guide covers:</strong></p><ol><li>Fact Tables vs. Dimension Tables (and their specialized variants)</li><li>Grain of a Fact Table</li><li>Normalization vs. Denormalization</li><li>Star Schema vs. Snowflake Schema</li><li>Slowly Changing Dimensions (SCD Type 1, 2, 3)</li><li>Extra Concepts: Kimball vs. Inmon, Surrogate Keys, Conformed Dimensions, One Big Table, and SCD 4/6/7</li></ol><h3>1. Fact Tables vs. Dimension Tables</h3><h3>The core idea</h3><p>Every table in a dimensional data warehouse plays one of two roles: it either records something that <strong>happened</strong> (a fact), or it describes <strong>who/what/where/when</strong> that thing happened to (a dimension).</p><h3>Fact tables: the “what happened” data</h3><p>A fact table stores <strong>measurable events</strong>, things you can count, sum, or average. Each row is typically a transaction, an event, or an occurrence.</p><pre>CREATE TABLE fact_orders (<br>    orderId INT,<br>    customerId INT,      -- foreign key to dim_customer<br>    productId INT,        -- foreign key to dim_product<br>    storeId INT,           -- foreign key to dim_store<br>    orderDate DATE,         -- foreign key to dim_date (or just a date)<br>    quantity INT,             -- a MEASURE: can be summed<br>    amount DECIMAL             -- a MEASURE: can be summed<br>);</pre><p><strong>Key characteristics:</strong></p><ul><li>Contains numeric, <strong>additive measures</strong> (quantity, amount, revenue) aggregated with SUM, AVG, COUNT</li><li>Contains <strong>foreign keys</strong> pointing out to dimension tables, rarely stores descriptive text directly</li><li>Tends to be the <strong>largest, tallest table</strong> in the warehouse (millions to billions of rows)</li><li>Grows continuously over time as new events happen</li></ul><h3>Dimension tables: the “who/what/where/when” data</h3><p>A dimension table stores <strong>descriptive attributes</strong> that give context to the facts. Each row is typically an entity, like a specific customer, product, or store.</p><pre>CREATE TABLE dim_customer (<br>    customerId INT,<br>    customerName VARCHAR,<br>    city VARCHAR,<br>    state VARCHAR,<br>    region VARCHAR,<br>    signupDate DATE<br>);</pre><p><strong>Key characteristics:</strong></p><ul><li>Contains <strong>descriptive, textual attributes</strong>, not numbers meant for summing</li><li>Tends to be <strong>much smaller</strong> than fact tables</li><li>Changes relatively rarely compared to the fact table</li></ul><h3>Putting them together</h3><pre>SELECT<br>    c.region,<br>    p.category,<br>    SUM(f.amount) AS totalRevenue<br>FROM fact_orders f<br>JOIN dim_customer c ON f.customerId = c.customerId<br>JOIN dim_product p ON f.productId = p.productId<br>GROUP BY c.region, p.category;</pre><p>The fact table sits at the center, holding the numbers. The dimension tables radiate outward, holding the descriptive context used to slice and filter those numbers.</p><blockquote>A simple test to tell them apart</blockquote><blockquote>Ask: <strong>“Can I meaningfully SUM or AVG this column?”</strong></blockquote><blockquote>If yes (amount, quantity, revenue), it belongs in a fact table.</blockquote><blockquote>If no (customerName, region, productCategory), it belongs in a dimension table. Foreign keys live in the fact table, but a raw fact row on its own (orderId=501, customerId=12, productId=88, amount=45.00) tells you almost nothing readable, you need the dimension tables joined in for context.</blockquote><h3>Other specialized table types worth knowing</h3><p><strong>Bridge tables</strong> — resolve many-to-many relationships that don’t fit a clean fact-to-dimension join. Example: one order can involve multiple sales reps sharing commission.</p><pre>bridge_order_rep (orderId, repId, creditPercent)</pre><pre>SELECT o.orderId, r.repName, o.amount * b.creditPercent AS repCredit<br>FROM fact_orders o<br>JOIN bridge_order_rep b ON o.orderId = b.orderId<br>JOIN dim_rep r ON b.repId = r.repId;</pre><p>Without the bridge table, joining a multi-valued relationship directly into a fact table causes a <strong>fan-out</strong>: the fact row gets duplicated once per matching rep, silently inflating any downstream SUM.</p><p><strong>Junk dimensions</strong> — a grab-bag dimension table grouping several small, low-cardinality flags that don’t deserve their own separate tables.</p><pre>dim_order_flags (flagId, isGift, isRush, paymentMethod, channelType)</pre><p><strong>Degenerate dimensions</strong> — an identifier that lives directly in the fact table with no separate dimension table, because it has no descriptive attributes worth breaking out.</p><pre>fact_orders (<br>    orderId,        -- degenerate dimension: an identifier with no<br>                     -- attributes of its own, just lives in the fact table<br>    customerId,<br>    amount<br>)</pre><p><strong>Factless fact tables</strong> — a fact table with no numeric measures at all; it just records that an event or relationship occurred.</p><pre>fact_attendance (studentId, classId, attendanceDate)</pre><p>You derive a measure at query time with COUNT(*) rather than storing a pre-computed one. Common for tracking attendance, eligibility, or coverage, where the interesting question is &quot;did this happen,&quot; not &quot;how much.&quot;</p><p><strong>Aggregate tables</strong> — a pre-computed, rolled-up version of a more granular fact table, built specifically for performance.</p><pre>-- Granular: one row per transaction<br>fact_orders (orderId, customerId, orderDate, amount)</pre><pre>-- Aggregate: pre-summed to monthly grain, much smaller, much faster to query<br>fact_orders_monthly_agg (customerId, orderMonth, totalAmount, orderCount)</pre><p>This is the same idea as the pre-aggregation strategy commonly used before loading raw event/log data into a warehouse, and conceptually similar to a materialized view, except an aggregate table is usually a deliberately modeled, persisted table (often its own dbt model) rather than a database-managed materialized view.</p><p><strong>Staging / intermediate tables</strong> — not part of the star schema itself, but standard dbt project vocabulary worth knowing:</p><ul><li><strong>Staging models:</strong> one-to-one with a source table, light cleanup only (renaming, type casting)</li><li><strong>Intermediate models:</strong> business logic applied, joins, not yet final</li><li><strong>Marts (fact/dimension tables):</strong> the final, business-facing star schema output</li></ul><h3>Quick summary</h3><ul><li><strong>Fact table:</strong> measurable events, additive numbers, foreign keys, huge row counts</li><li><strong>Dimension table:</strong> descriptive attributes, no numbers to sum, much smaller</li><li><strong>Bridge table:</strong> resolves many-to-many relationships without fan-out</li><li><strong>Junk dimension:</strong> groups small flags together</li><li><strong>Degenerate dimension:</strong> an identifier with no attributes, lives in the fact table</li><li><strong>Factless fact table:</strong> records that something happened, no measure at all</li><li><strong>Aggregate table:</strong> a pre-rolled-up copy of a finer-grain fact table, for performance</li><li><strong>Staging/intermediate:</strong> pipeline layers before the final fact/dimension output</li></ul><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“Can a table be both a fact and a dimension?”<br><em>Not really, but a degenerate dimension is the closest thing: an identifier that lives inside the fact table without a full corresponding dimension table, because it carries no additional descriptive attributes worth breaking out separately.</em></blockquote><h3>2. Grain of a Fact Table</h3><h3>The core idea</h3><p><strong>Grain</strong> is the single most important decision in designing a fact table. It answers one question: <strong>“What does one row in this table represent?”</strong> Every join, every aggregation, every downstream number depends on getting this answer right and explicit before building anything.</p><h3>Stating grain precisely</h3><pre>Vague (bad): &quot;This table has order data&quot;</pre><pre>Precise (good): &quot;One row per order line item&quot;<br>Precise (good): &quot;One row per customer, per day&quot;<br>Precise (good): &quot;One row per order, per payment attempt&quot;</pre><h3>Worked example: a daily model-score table</h3><p>This is a genuinely common real-world pattern: a model produces a row-level score per key, and the score can change from one day to the next, or stay the same.</p><p><strong>Grain option A — transaction grain: only a row when the score actually changes</strong></p><pre>fact_scores (customerId, scoreDate, score)</pre><pre>101, 2024-01-01, 0.8<br>101, 2024-01-08, 0.6   &lt;- only appears because it changed</pre><p>Grain statement: <em>“One row per customerId, per date the score changed.”</em></p><p><strong>Grain option B — periodic snapshot grain: a row every single day, always</strong></p><pre>fact_scores_daily (customerId, scoreDate, score)</pre><pre>101, 2024-01-01, 0.8<br>101, 2024-01-02, 0.8<br>101, 2024-01-03, 0.8<br>...<br>101, 2024-01-08, 0.6</pre><p>Grain statement: <em>“One row per customerId, per calendar day, regardless of whether the score changed.”</em></p><p>Same underlying business concept, two completely different grains. Neither is inherently “more correct” — the right one depends entirely on how the table will be consumed.</p><h3>Why a daily snapshot is often the right choice for operational consumers</h3><p>If the downstream consumer is an operational team, choosing the periodic snapshot grain (option B) over the transaction grain (option A) is usually the right call, for several concrete reasons:</p><ol><li><strong>Simplicity of consumption.</strong> “What is the score right now” is a trivial WHERE scoreDate = X filter on a snapshot table. On a transaction-only table, it requires finding the most recent change on or before a given date (ORDER BY scoreDate DESC LIMIT 1), logic that every downstream consumer would otherwise have to reimplement correctly, forever.</li><li><strong>Pipeline health monitoring becomes possible.</strong> If a row is <em>supposed</em> to exist every day for every active key, a missing row is an immediate, unambiguous signal something broke upstream. With a transaction-only table, “missing” and “unchanged” look identical, so a silent pipeline failure is indistinguishable from a genuinely stable score.</li><li><strong>Day-over-day analysis (like </strong><strong>LAG()) works correctly.</strong> A window function like LAG(score) OVER (PARTITION BY customerId ORDER BY scoreDate) only returns the true <em>previous day&#39;s</em> value if the grain guarantees no gaps. With gaps, LAG() silently returns a much older value while looking like &quot;yesterday.&quot;</li><li><strong>It matches how operational teams typically ask questions</strong>: point-in-time (“what is true as of X”), not change-history (“show me every time this changed”).</li></ol><p><strong>The honest tradeoff:</strong> a daily snapshot means storing the same unchanged value over and over for keys that rarely change, real, linear storage growth. Since storage is cheap in a cloud warehouse and the operational cost of ambiguous or broken downstream logic is high, this tradeoff is usually worth making.</p><h3>Why getting grain wrong is dangerous</h3><p>If you assume a coarser grain than what actually exists, joins silently duplicate rows and every SUM/COUNT downstream is quietly inflated. No error is thrown. This is the most dangerous kind of bug, because it looks correct.</p><h3>How you enforce grain in practice: uniqueness tests</h3><pre># dbt schema.yml<br>models:<br>  - name: fact_scores_daily<br>    tests:<br>      - dbt_utils.unique_combination_of_columns:<br>          combination_of_columns:<br>            - customerId<br>            - scoreDate</pre><p>If a bug ever produces two rows for the same customer and date, this test fails loudly on the next dbt run, catching the violation before it corrupts every downstream aggregation. This is arguably the single most important test on any fact table, since grain violations are silent and compounding.</p><h3>Grain and the fact table’s key</h3><p>The grain <strong>is</strong> effectively the definition of the fact table’s primary key. Once the grain is known, the key is the combination of columns that grain describes.</p><p><strong>Composite key (use the natural grain columns directly):</strong></p><pre>PRIMARY KEY (customerId, scoreDate)</pre><p><strong>Surrogate key (hash the grain columns into a single generated column):</strong></p><pre>SELECT<br>    {{ dbt_utils.generate_surrogate_key([&#39;customerId&#39;, &#39;scoreDate&#39;]) }} AS fact_score_key,<br>    customerId,<br>    scoreDate,<br>    score<br>FROM ...</pre><p>A hash-based surrogate key like this is <strong>deterministic</strong>: the same (customerId, scoreDate) always produces the same key, every time it&#39;s recomputed. This is what makes it usable for incremental MERGE logic without a separate lookup:</p><pre>{{<br>    config(<br>        materialized=&#39;incremental&#39;,<br>        unique_key=&#39;fact_score_key&#39;,<br>        incremental_strategy=&#39;merge&#39;<br>    )<br>}}</pre><p><strong>An important distinction: a hash-based key is not the same thing as a sequential (auto-incrementing) key.</strong></p><ul><li><strong>Hash-based key:</strong> deterministic, directly encodes the grain, works with zero coordination in distributed/parallel loads (each worker can independently compute the correct key for its own rows), ideal for MERGE matching.</li><li><strong>Sequential key:</strong> depends on insertion order, not row content; does not encode the grain at all (FACT_KEY = 4521 tells you nothing about which customer or date it represents); cannot on its own catch a grain violation, since two rows for the same (customerId, scoreDate) would simply get two different sequence values and both look &quot;unique.&quot;</li></ul><p><strong>Verifying which one you actually have, and whether it protects your grain:</strong></p><pre>-- If the key faithfully represents the grain, this returns ZERO rows<br>SELECT customerId, scoreDate, COUNT(DISTINCT FACT_KEY)<br>FROM fact_scores_daily<br>GROUP BY customerId, scoreDate<br>HAVING COUNT(DISTINCT FACT_KEY) &gt; 1;</pre><p>If your key is sequential rather than hash-based, this check tells you nothing about grain violations, the grain must be separately enforced with a uniqueness test directly on the grain columns, (customerId, scoreDate), not on the key itself.</p><p><strong>When to prefer each type of key:</strong></p><ul><li><strong>Sequential keys are advantageous for:</strong> smaller storage footprint and faster comparisons in downstream joins (integers beat long hash strings at the CPU level), natural readable insertion ordering, zero collision risk, and a simpler mental model for non-technical consumers.</li><li><strong>Hash-based keys are advantageous for:</strong> deterministic recomputation during incremental merges (no lookup needed), self-documenting the grain directly in the key’s definition, and working cleanly across distributed/parallel loads with no need for a centrally coordinated counter.</li></ul><p><strong>The honest framing:</strong> hash-based keys are chosen for <em>load-time correctness and simplicity</em>, not for downstream query speed. If anything, sequential integer keys are the more compute-efficient choice for downstream joins specifically. Many real dbt projects use both: a hash key drives the incremental MERGE logic internally, while a simpler sequential or natural key gets exposed to downstream consumers and BI tools.</p><h3>Finer grain vs. coarser grain</h3><pre>Finer:    one row per order LINE ITEM<br>Coarser:  one row per ORDER<br>Coarser:  one row per CUSTOMER PER DAY<br>Coarser:  one row per CUSTOMER PER MONTH</pre><p><strong>Rule of thumb:</strong> model fact tables at the finest reasonable grain, and aggregate up when needed. You can always aggregate up from fine detail; you can never recover detail that was never captured because the table was modeled too coarse to begin with. This is also exactly why aggregate tables exist as a separate layer, keep the finest-grain fact table as the source of truth, and build coarser rollups on top of it for performance, rather than only ever storing the coarse version.</p><h3>Summary</h3><ul><li><strong>Grain = what one row represents</strong>, and it must be stated precisely enough that any two rows are distinguishable by definition.</li><li><strong>A coarser assumed grain than reality causes silent, un-thrown-error aggregation bugs.</strong></li><li><strong>Enforce grain with a uniqueness test</strong> on the exact combination of columns that define it.</li><li><strong>Hash-based surrogate keys encode the grain</strong>; sequential keys do not, and cannot catch a grain violation on their own.</li><li><strong>Model at the finest reasonable grain</strong>; aggregate up, never try to recover detail that was never captured.</li></ul><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“Does a hash-based surrogate key make downstream queries faster?”</blockquote><blockquote><em>No, generally the opposite for pure join performance, since a hash is a longer string than a small integer, and integer comparisons are cheaper. The value of a hash key is at load time (deterministic matching for incremental merges, no coordination needed across parallel workers), not at query time.</em></blockquote><h3>3. Normalization vs. Denormalization</h3><h3>The core idea</h3><p>This is fundamentally about <strong>where redundant data is allowed to live</strong>.</p><ul><li><strong>Normalization:</strong> organize data so each fact is stored in exactly one place, achieved by splitting data into multiple related tables linked by foreign keys.</li><li><strong>Denormalization:</strong> deliberately allow redundant/repeated data to live together in fewer, wider tables, to avoid needing joins to reconstruct it.</li></ul><h3>A worked example</h3><p><strong>Normalized version:</strong></p><pre>CREATE TABLE customers (<br>    customerId INT,<br>    customerName VARCHAR,<br>    cityId INT          -- foreign key, not the raw city name<br>);</pre><pre>CREATE TABLE cities (<br>    cityId INT,<br>    cityName VARCHAR,<br>    stateId INT<br>);</pre><pre>CREATE TABLE states (<br>    stateId INT,<br>    stateName VARCHAR,<br>    region VARCHAR<br>);</pre><p>The word “California” and the word “West” exist in exactly <strong>one row</strong>, in the states table, no matter how many thousands of customers live there. Getting a customer&#39;s region requires a join across three tables.</p><p><strong>Denormalized version:</strong></p><pre>CREATE TABLE customers (<br>    customerId INT,<br>    customerName VARCHAR,<br>    city VARCHAR,<br>    state VARCHAR,<br>    region VARCHAR<br>);</pre><p>“California” and “West” are now repeated in every single customer row for every Californian customer, thousands of times over. Getting a customer’s region requires zero joins.</p><h3>Why normalization exists: it prevents update anomalies</h3><pre>-- Denormalized: if California&#39;s region gets reclassified from<br>-- &quot;West&quot; to &quot;Pacific&quot;, EVERY row where state = &#39;California&#39; must be found and updated<br>UPDATE customers SET region = &#39;Pacific&#39; WHERE state = &#39;California&#39;;<br>-- If even one row is missed, the data becomes self-contradictory</pre><pre>-- Normalized: change it in exactly one place<br>UPDATE states SET region = &#39;Pacific&#39; WHERE stateName = &#39;California&#39;;<br>-- Every customer&#39;s effective region is correct instantly</pre><p>This is called an <strong>update anomaly</strong>: the risk that the same fact, duplicated in multiple places, gets updated in some but not all of them. Normalization structurally prevents this, since each fact has exactly one authoritative location.</p><p>Two related, occasionally-asked anomalies:</p><ul><li><strong>Insertion anomaly:</strong> a new fact can’t be added without also having unrelated data present (e.g., a new course can’t be recorded until at least one student is enrolled in it, in a poorly denormalized student/course table).</li><li><strong>Deletion anomaly:</strong> deleting one fact accidentally deletes another, unrelated fact (e.g., the last student dropping a course accidentally erases all record the course ever existed).</li></ul><h3>Normal forms, briefly</h3><ul><li><strong>1NF:</strong> every column holds a single, atomic value, no comma-separated lists crammed into one field.</li><li><strong>2NF:</strong> builds on 1NF; every non-key column depends on the <em>entire</em> primary key, not just part of it (relevant with composite keys).</li><li><strong>3NF:</strong> builds on 2NF; no non-key column depends on another non-key column. (region depending on state rather than directly on customerId is exactly this violation, if region is stored directly on the customer row.)</li></ul><p>Most transactional (OLTP) production databases are designed close to 3NF. Most analytics warehouse fact/dimension tables deliberately are not.</p><h3>Why analytics warehouses deliberately denormalize</h3><ul><li><strong>OLTP (production app database):</strong> frequent small writes, single-row lookups; high update-anomaly risk since data changes constantly, live; joins are cheap on small transactional queries; priority is data integrity on every single write.</li><li><strong>OLAP (analytics warehouse):</strong> infrequent large writes (batch loads), large scans/aggregations; low update-anomaly risk since most tables are append-only or refreshed in controlled batches; joins are expensive when aggregating millions/billions of rows; priority is query speed and simplicity for analysts.</li></ul><p>Because warehouse data typically arrives through a controlled ETL/ELT pipeline rather than thousands of individual live users editing rows directly, the update-anomaly risk that justified normalization in OLTP is far less of a concern in OLAP. A dbt model rebuilding dim_customer nightly from source doesn&#39;t carry the same &quot;someone forgot to update one row&quot; risk a live production app does. Given that, the tradeoff shifts: fewer joins and faster analyst queries becomes more valuable than eliminating redundant storage, since storage is cheap and joins are the expensive, slow part of the equation in a warehouse.</p><h3>Summary</h3><ul><li><strong>Normalization = one authoritative copy of each fact</strong>, prevents update/insertion/deletion anomalies, standard for OLTP.</li><li><strong>Denormalization = deliberate redundancy</strong> to avoid joins, standard for OLAP/analytics warehouses.</li><li><strong>The shift from OLTP to OLAP thinking is the entire justification</strong>: controlled batch pipelines carry far less anomaly risk than live production writes, so the warehouse can afford to trade redundancy for query simplicity and speed.</li></ul><h3>4. Star Schema vs. Snowflake Schema</h3><h3>The core idea</h3><p>Now that fact tables, dimension tables, grain, and normalization/denormalization are established, this concept is a direct application: <strong>a design choice about how much you normalize the dimension tables in a warehouse.</strong></p><h3>Star Schema: dimension tables stay flat (denormalized)</h3><pre>dim_customer<br>                         |<br>dim_product -- fact_orders -- dim_date<br>                         |<br>                    dim_store</pre><pre>-- dim_customer is flat: every attribute lives in ONE table,<br>-- including city/state/region, which could theoretically be<br>-- broken out into their own lookup tables but aren&#39;t<br>CREATE TABLE dim_customer (<br>    customerId INT,<br>    customerName VARCHAR,<br>    city VARCHAR,<br>    state VARCHAR,<br>    region VARCHAR,<br>    country VARCHAR<br>);</pre><p>The diagram looks like a star: one fact table at the center, dimension tables radiating out in a single flat layer, no further branching.</p><pre>-- One join to get everything about the customer<br>SELECT c.region, SUM(f.amount)<br>FROM fact_orders f<br>JOIN dim_customer c ON f.customerId = c.customerId<br>GROUP BY c.region;</pre><h3>Snowflake Schema: dimension tables get further normalized into sub-dimensions</h3><pre>dim_customer -- dim_city -- dim_state -- dim_region<br>                     |<br>dim_product -- fact_orders -- dim_date<br>                     |<br>                dim_store</pre><pre>CREATE TABLE dim_customer (<br>    customerId INT,<br>    customerName VARCHAR,<br>    cityId INT              -- foreign key, not the raw city name<br>);</pre><pre>CREATE TABLE dim_city (<br>    cityId INT,<br>    cityName VARCHAR,<br>    stateId INT<br>);</pre><pre>CREATE TABLE dim_state (<br>    stateId INT,<br>    stateName VARCHAR,<br>    regionId INT<br>);</pre><p>The diagram branches outward from each dimension, like the arms of a snowflake.</p><pre>-- Multiple joins needed to get the same region-level answer<br>SELECT r.regionName, SUM(f.amount)<br>FROM fact_orders f<br>JOIN dim_customer c ON f.customerId = c.customerId<br>JOIN dim_city ci ON c.cityId = ci.cityId<br>JOIN dim_state s ON ci.stateId = s.stateId<br>JOIN dim_region r ON s.regionId = r.regionId<br>GROUP BY r.regionName;</pre><h3>The tradeoff</h3><ul><li><strong>Star schema:</strong> fewer joins, more storage redundancy (region name repeated across many customer rows), easier for business users and analysts to navigate, generally faster to query.</li><li><strong>Snowflake schema:</strong> more joins, less storage redundancy (region name stored once), a name change to a region propagates automatically everywhere, harder to navigate, generally slower to query due to extra joins.</li></ul><p>Storage is cheap in a cloud warehouse; compute, the cost of running joins, is the resource that actually matters more. Star schema trades storage redundancy for dramatically fewer joins, which fits that cost structure. Snowflake schemas are more associated with older, on-premise, storage-constrained warehouse designs, from an era when storage genuinely was the expensive resource.</p><h3>Directly connecting back to normalization/denormalization</h3><p>A star schema <strong>is</strong> a denormalized dimension design, dim_customer repeats &quot;California&quot; and &quot;West&quot; across every Californian customer row, exactly the denormalization tradeoff, applied on purpose to avoid joins. A snowflake schema <strong>is</strong> a normalized dimension design, applying the same 3NF-style thinking (splitting state → region into their own table) inside a warehouse. Nothing new happens here conceptually, it&#39;s the same normalization/denormalization tradeoff wearing warehouse-specific vocabulary.</p><h3>How grain connects to this choice</h3><p>The grain of dim_customer in a star schema is &quot;one row per customer,&quot; full stop, everything about that customer lives at that grain, in one place. In a snowflake schema, that single grain is deliberately split across multiple tables at different grains: dim_customer at &quot;one row per customer,&quot; dim_city at &quot;one row per city,&quot; dim_state at &quot;one row per state.&quot;</p><h3>Can a dimension join to more than one fact table?</h3><p>Yes — and this is a deliberate, named pattern called a <strong>conformed dimension</strong>.</p><pre>fact_orders ----\<br>                  &gt;---- dim_customer<br>fact_support_tickets ----/</pre><p>If dim_customer&#39;s region column means exactly the same thing whether you&#39;re looking at orders or support tickets, questions like &quot;which region has high order value but also high support ticket volume&quot; become trustworthy, since &quot;region&quot; is defined identically in both contexts. Building a shared library of conformed dimensions once, and reusing them across every fact table in the warehouse, is a core idea in Kimball-style dimensional modeling.</p><h3>Can a fact table join directly to another fact table?</h3><p>Technically yes in SQL, but it’s generally bad practice, because fact tables usually have <strong>different grains</strong>, and a direct join causes a fan-out.</p><pre>-- fact_orders grain: one row per order<br>-- fact_support_tickets grain: one row per ticket<br>-- If customer 101 has 3 orders and 2 tickets, this join produces<br>-- 3 x 2 = 6 rows for that customer, not 3, not 2, not 5<br>SELECT o.customerId, o.amount, t.ticketId<br>FROM fact_orders o<br>JOIN fact_support_tickets t ON o.customerId = t.customerId</pre><p>Every order amount now appears duplicated once per ticket, and vice versa — any SUM() afterward is silently, badly wrong.</p><p><strong>The correct pattern: aggregate each fact table down to the shared dimension’s grain first, then join through the dimension.</strong></p><pre>WITH orderSummary AS (<br>    SELECT customerId, SUM(amount) AS totalOrderAmount<br>    FROM fact_orders<br>    GROUP BY customerId<br>),<br>ticketSummary AS (<br>    SELECT customerId, COUNT(*) AS ticketCount<br>    FROM fact_support_tickets<br>    GROUP BY customerId<br>)<br>SELECT<br>    c.customerId,<br>    c.region,<br>    o.totalOrderAmount,<br>    t.ticketCount<br>FROM dim_customer c<br>LEFT JOIN orderSummary o ON c.customerId = o.customerId<br>LEFT JOIN ticketSummary t ON c.customerId = t.customerId;</pre><p>Both fact tables get pre-aggregated to the same grain (one row per customer) before joining, and they’re joined <em>through</em> the shared conformed dimension, not directly to each other.</p><p><strong>The general rule:</strong> facts join to dimensions freely. Facts join to other facts only through a shared, matching-grain dimension, and only after each fact has been aggregated down to that dimension’s grain first.</p><h3>Summary</h3><ul><li><strong>Star schema = denormalized dimensions</strong>, fewer joins, more storage redundancy, the default choice in modern cloud warehouses.</li><li><strong>Snowflake schema = normalized dimensions</strong>, more joins, less redundancy, associated with older storage-constrained designs.</li><li><strong>Conformed dimensions let multiple fact tables share one trustworthy definition</strong> of a dimension, enabling cross-process comparisons.</li><li><strong>Never join two fact tables directly</strong> — aggregate each to a shared dimension’s grain first, to avoid a fan-out that silently inflates every downstream aggregation.</li></ul><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“Can I join two fact tables directly if I really need to?”</blockquote><blockquote><em>Technically yes, but avoid it. Different grains between fact tables cause a fan-out that silently corrupts any aggregation. Pre-aggregate each fact table to the grain of a shared conformed dimension, and join through that dimension instead.</em></blockquote><h3>5. Slowly Changing Dimensions (SCD Type 1, 2, 3)</h3><h3>The core problem this solves</h3><p>Dimension tables describe entities that <strong>change over time</strong>: a customer moves cities, a product gets reassigned to a new category. SCD strategies answer: <strong>when a dimension attribute changes, what happens to the historical fact rows that were tied to the old value?</strong></p><p>This is grain applied to a dimension table instead of a fact table: just as a fact table’s grain defines what one row means, an SCD strategy defines what happens to that meaning over time as the real-world entity changes underneath it.</p><h3>Starting point</h3><pre>dim_customer (customerId, customerName, state)</pre><pre>101, &quot;Alice&quot;, &quot;Texas&quot;</pre><p>Alice moves to California. What should happen to this row?</p><h3>SCD Type 1: Overwrite (no history kept)</h3><pre>UPDATE dim_customer<br>SET state = &#39;California&#39;<br>WHERE customerId = 101;</pre><p><strong>Result:</strong></p><pre>101, &quot;Alice&quot;, &quot;California&quot;</pre><p>The old value is gone completely. Every fact row ever joined to customerId = 101, including orders placed years ago while she genuinely lived in Texas, will now show &quot;California.&quot;</p><p><strong>When to use it:</strong> when the historical value genuinely doesn’t matter, or when the change is a correction rather than a real business event (e.g., fixing a typo in a name — you want that fixed everywhere, retroactively).</p><p><strong>Tradeoff:</strong> simplicity (one row per customer, always) at the cost of losing the ability to answer “what was true at the time this order was placed.”</p><h3>SCD Type 2: Add a new row, preserve history</h3><pre>dim_customer (<br>    customerKey,       -- surrogate key, NOT the same as customerId<br>    customerId,        -- the natural/business key, stays constant<br>    customerName,<br>    state,<br>    effectiveDate,<br>    endDate,<br>    isCurrent<br>)</pre><p><strong>Before the move:</strong></p><pre>customerKey=1, customerId=101, &quot;Alice&quot;, &quot;Texas&quot;, 2020-01-01, 2024-03-14, FALSE</pre><p><strong>After the move, a new row is inserted, the old one is closed out:</strong></p><pre>customerKey=1, customerId=101, &quot;Alice&quot;, &quot;Texas&quot;,      2020-01-01, 2024-03-14, FALSE<br>customerKey=2, customerId=101, &quot;Alice&quot;, &quot;California&quot;, 2024-03-15, NULL,       TRUE</pre><p>customerId stays the same (it&#39;s still Alice), but customerKey is different for each version of her record — the same surrogate key concept from Section 2, applied here to distinguish between two different <em>versions</em> of the same real-world entity.</p><p><strong>Joining a fact table for the historically accurate picture:</strong></p><pre>SELECT o.orderId, o.orderDate, d.state<br>FROM fact_orders o<br>JOIN dim_customer d<br>    ON o.customerId = d.customerId<br>    AND o.orderDate BETWEEN d.effectiveDate AND COALESCE(d.endDate, &#39;9999-12-31&#39;);</pre><p>An order placed on 2022-06-01 joins to the customerKey=1 row (Texas); an order on 2024-06-01 joins to customerKey=2 (California) — the historically accurate answer, impossible under Type 1.</p><p><strong>Getting the current picture:</strong></p><pre>SELECT * FROM dim_customer WHERE isCurrent = TRUE;</pre><p><strong>When to use it:</strong> when historical accuracy genuinely matters for the business question — this is the most common real-world SCD pattern, and what people usually mean by default when they say “SCD” without specifying a type. Example: sales territory assignment — revenue should be attributed to the region actually responsible for the sale at the time, not retroactively reassigned.</p><p><strong>Tradeoff:</strong> full history, at the cost of a more complex dimension table (multiple rows per entity, date ranges to manage, more storage) and a more complex join (date-range join instead of simple equality).</p><h3>SCD Type 3: Add a new column, keep limited history</h3><pre>dim_customer (<br>    customerId,<br>    customerName,<br>    currentState,<br>    previousState<br>)</pre><p><strong>Before:</strong></p><pre>101, &quot;Alice&quot;, &quot;Texas&quot;, NULL</pre><p><strong>After the move:</strong></p><pre>101, &quot;Alice&quot;, &quot;California&quot;, &quot;Texas&quot;</pre><p>Only one prior value is retained, in a dedicated column, not a new row. A second move overwrites previousState, permanently losing &quot;Texas.&quot;</p><p><strong>When to use it:</strong> rare, genuinely useful only for comparing “before vs. after” a single specific known change (e.g., a one-time sales-territory reorganization), when there’s no need to track ongoing, repeated changes.</p><p><strong>Tradeoff:</strong> much simpler than Type 2 (no date ranges, one row per customer), but only supports exactly one level of history, and cannot represent “what was true on any arbitrary past date.”</p><h3>Side-by-side, same underlying change</h3><p><strong>Type 1</strong> — overwrite: 101, &quot;Alice&quot;, &quot;California&quot; <em>(Texas is gone entirely)</em></p><p><strong>Type 2</strong> — new row per change:</p><pre>1, 101, &quot;Alice&quot;, &quot;Texas&quot;,      2020-01-01, 2024-03-14, FALSE<br>2, 101, &quot;Alice&quot;, &quot;California&quot;, 2024-03-15, NULL,       TRUE</pre><p><em>(full history preserved, correct point-in-time joins possible)</em></p><p><strong>Type 3</strong> — new column per change: 101, &quot;Alice&quot;, &quot;California&quot;, &quot;Texas&quot; <em>(one level of history, no date tracking)</em></p><h3>Quick decision framework</h3><ul><li><strong>Type 1:</strong> the old value is wrong or irrelevant to keep → overwrite.</li><li><strong>Type 2:</strong> the old value was true at the time and that historical truth matters for analysis → preserve as a new row with effective dates.</li><li><strong>Type 3:</strong> exactly one “before vs. after” comparison is needed and nothing more granular → add a column.</li></ul><h3>dbt’s built-in tool for this: snapshots</h3><p>dbt has a dedicated feature, snapshots, purpose-built to implement SCD Type 2 automatically against a source table that only ever shows the current state.</p><pre>{% snapshot dim_customer_snapshot %}</pre><pre>{{<br>    config(<br>      target_schema=&#39;snapshots&#39;,<br>      unique_key=&#39;customerId&#39;,<br>      strategy=&#39;timestamp&#39;,<br>      updated_at=&#39;updatedAt&#39;,<br>    )<br>}}</pre><pre>SELECT * FROM {{ source(&#39;raw&#39;, &#39;customers&#39;) }}</pre><pre>{% endsnapshot %}</pre><p>Every time this runs, dbt compares the incoming row to what it already has, and if updatedAt (or the row&#39;s content, with strategy=&#39;check&#39;) has changed, it closes out the old row and inserts a new one, automatically managing dbt_valid_from / dbt_valid_to columns. This is exactly the Type 2 mechanism above, automated.</p><h3>Summary</h3><ul><li><strong>SCD = grain discipline applied to dimension tables over time</strong>, defining what happens when a described attribute changes.</li><li><strong>Type 1 overwrites</strong> — simplest, no history, right for corrections.</li><li><strong>Type 2 preserves full history as new rows with date ranges</strong> — the default choice when historical accuracy matters, requires a surrogate key distinct from the natural key.</li><li><strong>Type 3 keeps exactly one prior value in a column</strong> — narrow use case, single before/after comparisons only.</li><li><strong>dbt snapshots automate Type 2</strong> against a source that only exposes current state.</li></ul><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“Why can’t the natural key (customerId) be the dimension table&#39;s primary key under Type 2?&quot;</blockquote><blockquote><em>Because under Type 2, the same real-world entity legitimately has multiple rows (multiple versions over time), so </em><em>customerId alone is no longer unique. A separate surrogate key (</em><em>customerKey) is needed to uniquely identify each </em>version<em> of the customer, while </em><em>customerId is kept around specifically to group all versions of the same person together.</em></blockquote><h3>6. Extra Concepts Worth Knowing</h3><p>These come up less often than the five core topics above, but each is a genuinely distinct concept worth a quick, confident answer if raised.</p><h3>Kimball vs. Inmon Methodology</h3><p>Two competing philosophies for building a data warehouse.</p><ul><li><strong>Kimball (bottom-up):</strong> build small, business-process-specific star schemas first (e.g., a sales mart, a support mart), then let them share conformed dimensions over time. Faster to deliver value, and closer to how modern dbt projects are actually built.</li><li><strong>Inmon (top-down):</strong> build one large, fully normalized (3NF) central enterprise data warehouse first, then derive smaller denormalized data marts from it for specific teams. Slower upfront, but produces a single, tightly governed source of truth.</li></ul><p><strong>In practice:</strong> most modern cloud/dbt-based teams lean Kimball-style — star schemas and marts — since it delivers value incrementally rather than requiring a huge upfront modeling effort.</p><h3>Surrogate Keys vs. Natural Keys</h3><ul><li><strong>Natural key:</strong> an identifier that already exists in the source data and has business meaning (e.g., customerId, SSN, email).</li><li><strong>Surrogate key:</strong> a generated identifier with no business meaning, created purely for warehouse use (e.g., customerKey, a hash or sequence).</li></ul><p>The real-world reason surrogate keys matter is covered in depth in Sections 2 and 5: a natural key like customerId can&#39;t represent &quot;two different versions of the same customer over time&quot; under SCD Type 2, but a surrogate key (customerKey) can, since a new one is generated for each version.</p><h3>Conformed Dimensions / Bus Matrix</h3><p>A <strong>conformed dimension</strong> is a single shared dimension table (like dim_customer) reused consistently across multiple fact tables, so metrics from different business processes (orders, support tickets) can be compared reliably. Covered concretely in Section 4&#39;s fact-to-fact join discussion.</p><p>The <strong>Bus Matrix</strong> is the planning document for this: a grid with business processes (fact tables) as rows and shared dimensions as columns, marking which dimensions each process uses, used to plan conformed dimensions across an entire warehouse before building it.</p><h3>One Big Table (OBT) vs. Star Schema</h3><p><strong>OBT:</strong> instead of a fact table joined to several dimension tables, pre-join everything into a single wide, flat table, so downstream queries need zero joins at all.</p><pre>-- Star schema: fact + dimensions, joined at query time<br>-- OBT: already joined and stored as one denormalized table<br>SELECT * FROM obt_orders<br>-- includes order amount, customer name, region, product category,<br>-- all already flattened into one row, no joins needed</pre><p><strong>Why it’s trending now:</strong> cloud compute makes storage and redundant joins-at-write-time cheap, and BI tools/analysts often prefer querying one simple table over navigating a schema with many joins.</p><p><strong>The tradeoff:</strong> massive storage redundancy, and any change to a dimension attribute requires rebuilding the entire wide table rather than updating one small dimension row. It’s essentially “extreme denormalization” — the same axis as star vs. snowflake, pushed even further.</p><h3>SCD Type 4, 6, 7 (did you know they exist)</h3><ul><li><strong>Type 4:</strong> keep the current value in the main dimension table, but move all historical versions into a separate “history” table.</li><li><strong>Type 6:</strong> a hybrid combining Type 1 + 2 + 3 — keep full history as new rows (Type 2), but also add a “current value” column on every historical row (Type 1-ish), so current value can be queried without a separate lookup.</li><li><strong>Type 7:</strong> a similar hybrid, using both a surrogate key (for point-in-time joins) and the natural key with a durable “current” flag on the same row.</li></ul><h3>The One Unifying Idea</h3><p>If there’s a single thread connecting every topic in this guide, it’s this:</p><blockquote><strong><em>Every data modeling decision is ultimately about deciding what one row means (grain), whether that meaning should be repeated or centralized (normalization/denormalization), and what should happen when the real-world thing that row describes changes (SCD).</em></strong></blockquote><p>Fact and dimension tables give you the vocabulary. Grain makes the row’s meaning precise. Normalization/denormalization decides where redundancy lives. Star vs. snowflake schema is that decision applied to dimensions specifically. SCD is that same grain discipline applied across time.</p><h3>Quick-Reference Summary</h3><ul><li><strong>Fact tables hold measurable, additive events</strong> (SUM-able numbers plus foreign keys); <strong>dimension tables hold descriptive attributes</strong> that give those events context.</li><li><strong>A degenerate dimension</strong> is an identifier with no attributes of its own, living directly in the fact table (e.g., orderId).</li><li><strong>A bridge table resolves many-to-many relationships</strong> between facts and dimensions without causing a fan-out.</li><li><strong>A factless fact table records that something happened</strong>, with no numeric measure — use COUNT(*) to derive a measure at query time.</li><li><strong>Grain is the single most important fact-table decision</strong> — state precisely what one row represents, before building anything.</li><li><strong>A grain violation doesn’t throw an error</strong> — it silently inflates every downstream SUM/COUNT. Enforce grain with a uniqueness test on the exact grain columns.</li><li><strong>Hash-based surrogate keys encode the grain and are deterministic</strong> (ideal for incremental MERGE); <strong>sequential keys do not encode the grain</strong> and can&#39;t catch a grain violation on their own.</li><li><strong>Model fact tables at the finest reasonable grain</strong> — you can always aggregate up, but you can never recover detail that was never captured.</li><li><strong>Normalization stores each fact once</strong>, preventing update/insertion/deletion anomalies — the OLTP default.</li><li><strong>Denormalization deliberately repeats data</strong> to avoid joins — the OLAP/warehouse default, since batch pipelines carry far less anomaly risk than live production writes.</li><li><strong>A star schema is a denormalized dimension design; a snowflake schema is a normalized one</strong> — the same normalization/denormalization tradeoff, just applied to dimension tables.</li><li><strong>Storage is cheap, joins are expensive</strong> — this is why star schema (and OBT, its more extreme cousin) dominates modern cloud warehouse design.</li><li><strong>A conformed dimension is shared across multiple fact tables</strong>, enabling trustworthy cross-process comparisons (e.g., orders vs. support tickets by region).</li><li><strong>Never join two fact tables directly</strong> — different grains cause a silent fan-out. Aggregate each to a shared dimension’s grain first, then join through that dimension.</li><li><strong>SCD strategies define what happens to historical fact rows when a dimension attribute changes.</strong></li><li><strong>SCD Type 1 overwrites</strong> (no history, right for corrections); <strong>Type 2 preserves full history as new dated rows</strong> (the default choice, requires a surrogate key distinct from the natural key); <strong>Type 3 keeps exactly one prior value in a column</strong> (narrow, single before/after use case).</li><li><strong>dbt snapshots automate SCD Type 2</strong> against a source that only ever shows current state.</li><li><strong>Kimball (bottom-up, star schemas first) is the dominant modern approach</strong>; Inmon (top-down, normalized enterprise warehouse first) is more associated with legacy, tightly governed environments.</li><li><strong>One Big Table (OBT) is denormalization taken to its extreme</strong> — zero joins at query time, at the cost of significant storage redundancy and expensive rebuilds on dimension changes.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c44857e3d220" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Your Query Is Slow: A Complete SQL Optimization Guide]]></title>
            <link>https://bysuchi.medium.com/query-optimization-concepts-for-an-analytics-engineer-3000f9dfbd4a?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/3000f9dfbd4a</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[interview]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Sun, 16 Aug 2026 18:48:41 GMT</pubDate>
            <atom:updated>2026-08-19T14:05:19.146Z</atom:updated>
            <content:encoded><![CDATA[<p>Sargability, indexing, joins, correlated subqueries, and everything else that separates a query that runs in seconds from one that runs in minutes</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*K-oD9le-lfde60vPcSNo3w.avif" /><figcaption>Photo from <a href="https://unsplash.com/photos/server-and-online-database-cloud-and-colorful-gears-data-center-technology-and-computer-science-big-data-processing-and-networking-3d-rendering-ONQeqIIF4nY">Unsplash</a></figcaption></figure><p>If you are starting or prepping to be an Analytics Engineer (or Data Engineer, or SQL-heavy Data Analyst) , “how would you optimize this query” is one of the most common questions you will face. This guide walks through every concept you actually need, in the order it usually comes up in conversation, with the same worked examples throughout so the ideas build on each other instead of living in isolation.</p><p>Curious what you need to learn next? Click on the <a href="https://bysuchi.medium.com/fact-dimension-grain-the-data-modeling-guide-nobody-gives-you-c44857e3d220">second</a> and <a href="https://bysuchi.medium.com/dbt-fundamentals-for-analytics-engineers-a-complete-guide-6f10cd2ba6c3">third</a> most common topics for an Analytics Engineer</p><p>This is not a syntax reference. It is a mental model for how a query actually gets executed, so that when someone hands you a slow query on a whiteboard, you know exactly what to look for.</p><p><strong>What this guide covers:</strong></p><ol><li>Sargability</li><li>Index Awareness</li><li>Partition Pruning</li><li>CTE vs Subquery Performance</li><li>Correlated vs Non-Correlated Subqueries</li><li>Join Types and Join Order</li><li>Reading Execution Plans (EXPLAIN)</li><li>Result Caching</li><li>Avoiding Unnecessary Operations</li><li>UNION vs UNION ALL</li><li>Data Skew and Approximate Aggregates</li><li>Materialized Views and Wildcard Sargability</li></ol><p>Throughout, we will reference Snowflake, Presto/Athena, and Spark SQL, since most modern analytics engineering roles touch more than one engine.</p><h3>1. Sargability</h3><p><strong>Sargability</strong> (a contraction of “Search ARGument ABLE”) is whether a condition in your WHERE clause lets the database use an index or metadata to skip data, versus forcing it to touch every row.</p><p><strong>The core rule: keep the filtered column bare. Never wrap it in a function.</strong></p><h3>Example 1: Date functions</h3><pre>-- Bad: forces a full scan, YEAR() must be computed on every row first<br>WHERE YEAR(transactionDate) = 2023</pre><pre>-- Good: the raw column stays bare, index/partition metadata can be used<br>WHERE transactionDate BETWEEN &#39;2023-01-01&#39; AND &#39;2023-12-31&#39;</pre><p><strong>Why this happens:</strong> an index (or Snowflake’s micro-partition metadata) stores the raw column values in a structure the engine can jump around in. YEAR(transactionDate) does not exist anywhere in that structure, only transactionDate itself does. To evaluate YEAR(transactionDate) = 2023, the engine has no choice but to pull every row, compute YEAR() on it, and then check the condition. That is a full scan by definition.</p><h3>Example 2: String functions</h3><pre>-- Bad<br>WHERE UPPER(customerName) = &#39;JOHN SMITH&#39;</pre><pre>-- Good: normalize data upstream so you never need this at query time<br>WHERE customerName = &#39;John Smith&#39;</pre><h3>Example 3: Arithmetic on a column</h3><pre>-- Bad: math applied to the column itself<br>WHERE salary * 1.1 &gt; 100000</pre><pre>-- Good: move the math to the constant side instead<br>WHERE salary &gt; 100000 / 1.1</pre><h3>Example 4: Implicit type casting</h3><pre>-- Bad: customerId is INT, &#39;123&#39; is a string, forces a cast on every row<br>WHERE customerId = &#39;123&#39;</pre><pre>-- Good: match the data type exactly<br>WHERE customerId = 123</pre><h3>Edge case: when you are forced to use UPPER/TRIM</h3><p>Sometimes the data is genuinely inconsistent and you cannot avoid a cleanup function. Two honest options exist:</p><p><strong>Option A (best): fix it upstream, once, in your staging layer</strong></p><pre>-- In a dbt staging model, clean the data once<br>SELECT<br>    UPPER(TRIM(customerName)) AS customerName<br>FROM raw.customers</pre><p>Every downstream query then filters on a clean, bare column with no function needed.</p><p><strong>Option B: if you cannot control the source, wrapping the column in a CTE does NOT avoid the full scan</strong></p><pre>WITH cleaned AS (<br>    SELECT id, UPPER(TRIM(customerName)) AS customerName<br>    FROM customers<br>)<br>SELECT * FROM cleaned WHERE customerName = &#39;JOHN SMITH&#39;</pre><p>This is <strong>not</strong> more optimized than writing WHERE UPPER(TRIM(customerName)) = &#39;JOHN SMITH&#39; directly. Both scan every row and apply the function to every row before filtering. The CTE version is only a readability improvement, not a performance improvement, <strong>when used once</strong>.</p><p>The CTE version genuinely starts to save compute the moment the cleaned value is reused multiple times downstream, since the function is applied once instead of being reapplied at every reference:</p><pre>-- Applies UPPER(TRIM()) 3 separate times, once per query<br>SELECT * FROM customers WHERE UPPER(TRIM(customerName)) = &#39;JOHN SMITH&#39;<br>UNION ALL<br>SELECT * FROM customers WHERE UPPER(TRIM(customerName)) = &#39;JANE DOE&#39;<br>UNION ALL<br>SELECT * FROM customers WHERE UPPER(TRIM(customerName)) = &#39;BOB JONES&#39;</pre><pre>-- vs: applies UPPER(TRIM()) once, reuses the cleaned result 3 times<br>WITH cleaned AS (<br>    SELECT id, UPPER(TRIM(customerName)) AS customerName FROM customers<br>)<br>SELECT * FROM cleaned WHERE customerName = &#39;JOHN SMITH&#39;<br>UNION ALL<br>SELECT * FROM cleaned WHERE customerName = &#39;JANE DOE&#39;<br>UNION ALL<br>SELECT * FROM cleaned WHERE customerName = &#39;BOB JONES&#39;</pre><p><strong>Rule of thumb:</strong> used once, a CTE around a function is purely readability. Used multiple times, it is a genuine performance win.</p><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“Are these two queries different in performance at all?”<em> — </em><em>SELECT * FROM customers WHERE UPPER(TRIM(customerName)) = &#39;X&#39; vs the CTE-wrapped version, both used once. <br>Answer: no, they are identical in cost. The database still scans every row and applies the function to every row either way; the CTE only changes how the code reads, not what work gets done.</em></blockquote><h3>2. Index Awareness</h3><p>An <strong>index</strong> is a separate data structure the database maintains alongside a table, so it can find rows without scanning the whole thing. Think of the index at the back of a textbook: instead of reading every page for “salary,” you jump straight to the referenced pages.</p><h3>Types of indexes</h3><pre>-- Primary key index (automatic)<br>CREATE TABLE orders (<br>    id INT PRIMARY KEY,        -- automatically indexed<br>    customerId INT,<br>    orderDate DATE,<br>    amount DECIMAL<br>);</pre><pre>-- Single column index (manual)<br>CREATE INDEX idx_orderDate ON orders(orderDate);</pre><pre>-- Composite index (multiple columns)<br>CREATE INDEX idx_customer_date ON orders(customerId, orderDate);</pre><h3>Composite index column order matters</h3><p>A composite index is sorted like a phone book: by last name, then first name within each last name. You can search by last name alone, or last name + first name together — but never by first name alone.</p><pre>-- Index defined on (customerId, orderDate)</pre><pre>-- Good: uses the index, customerId is the leading column<br>WHERE customerId = 123</pre><pre>-- Good: uses the index fully, both columns match the leading order<br>WHERE customerId = 123 AND orderDate = &#39;2023-01-15&#39;</pre><pre>-- Bad: skips the leading column, index cannot be used at all<br>WHERE orderDate = &#39;2023-01-15&#39;</pre><h3>Indexes are not free</h3><p>ActionEffect on indexesSELECTIndexes helpINSERTEvery index on the table must also be updatedUPDATEAffected indexes must be updatedDELETEEvery index on the table must be updated</p><p>A table with 10 indexes means every single row inserted has to update 10 separate structures. This is why you do not index every column reflexively — over-indexing slows down data loads significantly.</p><h3>Indexes can be added after the table already has data</h3><p>A common misconception is that indexing must be decided at table creation. It does not.</p><pre>-- Add an index to an existing table, anytime<br>CREATE INDEX idx_orderDate ON orders(orderDate);</pre><pre>-- Remove it later if it&#39;s not helping<br>DROP INDEX idx_orderDate ON orders;</pre><p>When you add an index to a table that already has millions of rows, the database does a one-time scan to build the structure, which takes real time and resources. Going forward, every write keeps it updated automatically. In practice, the most common workflow is <strong>reactive</strong>, not upfront: a table gets used normally, someone notices a specific column shows up constantly in WHERE/JOIN clauses, and an index gets added based on actual observed query patterns rather than guessed in advance.</p><h3>Indexing a downstream analytics table does not touch the source system</h3><p>If your analytics table in Snowflake is a physical copy of data that originated in a production OLTP database, indexing (or clustering) that Snowflake copy has <strong>zero effect</strong> on the original source table. They are two entirely separate physical objects. This separation is one of the core reasons data warehouses exist: it lets you optimize for heavy analytical scans (OLAP) completely independently of the fast single-row lookups a production application needs (OLTP), without either workload competing with or slowing down the other.</p><h3>Snowflake, Presto, and Spark do not use traditional indexes</h3><p>Traditional Index ConceptSnowflake EquivalentPresto / Spark EquivalentB-tree indexMicro-partition min/max metadataColumn statistics in Parquet/ORC filesComposite indexCluster key (CLUSTER BY)Physical partitioning (folder structure)Manual index creationALTER TABLE ... CLUSTER BY (...)Partitioning + bucketing at write timeFull-text / substring indexSearch Optimization ServiceN/A by default</p><pre>-- Snowflake: cluster key, can be added after table creation<br>ALTER TABLE orders CLUSTER BY (orderDate, customerId);</pre><pre>-- Check how well-clustered a table currently is<br>SELECT SYSTEM$CLUSTERING_INFORMATION(&#39;orders&#39;, &#39;(orderDate)&#39;);</pre><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“Is manual clustering something you use often?”<br><em>Honestly, most analytics engineers rarely touch this directly. Snowflake’s automatic micro-partitioning handles the vast majority of tables fine without manual intervention. Manual </em><em>CLUSTER BY typically only becomes necessary once tables reach hundreds of millions to billions of rows and query performance visibly degrades, or Snowflake compute costs spike due to excessive partition scanning. At many companies this is owned by a dedicated data platform team rather than individual analytics engineers — and it is a completely honest, normal answer to say you understand the concept without having needed to apply it directly.</em></blockquote><h3>3. Partition Pruning</h3><p><strong>Partition pruning</strong> is the practice of skipping entire chunks of data based on metadata alone, without ever reading the actual rows inside those chunks.</p><h3>Snowflake: automatic, via micro-partitions</h3><p>Snowflake automatically divides every table into micro-partitions (roughly 50–500MB of uncompressed data each) and stores the min/max value of every column, for every micro-partition, as metadata.</p><pre>-- Prunes: Snowflake checks metadata first, skips any partition<br>-- whose max(order_date) is below &#39;2023-01-01&#39;<br>SELECT * FROM orders<br>WHERE order_date &gt;= &#39;2023-01-01&#39;</pre><pre>-- Does NOT prune: wrapping the column in a function blinds<br>-- the optimizer to the min/max metadata entirely<br>SELECT * FROM orders<br>WHERE YEAR(order_date) = 2023</pre><p>This is the exact same sargability principle from Section 1, just applied at the partition level instead of the index level. You can verify pruning worked by checking the Query Profile in Snowsight, which shows “Partitions scanned” vs. “Partitions total.”</p><p>Micro-partitioning itself is always on, no setup required. What is <em>not</em> automatic is how well-<strong>clustered</strong> the data is — that depends on the order data was originally loaded in, and can degrade over time as more data is inserted out of order, which is when an explicit CLUSTER BY (Section 2) becomes useful.</p><h3>Presto/Athena and Spark: manual, physical folder-based partitioning</h3><p>Here, nothing happens automatically. Someone has to physically organize files into folders named after a partition key:</p><pre>s3://bucket/orders/year=2023/month=01/day=15/file1.parquet<br>s3://bucket/orders/year=2023/month=01/day=16/file2.parquet<br>s3://bucket/orders/year=2023/month=02/day=01/file3.parquet</pre><pre>-- Prunes: only reads the year=2023/month=01 folders<br>SELECT * FROM orders<br>WHERE year = 2023 AND month = 1</pre><pre>-- Does NOT prune: order_timestamp is a column INSIDE the files,<br>-- not a folder-level partition key, so every folder still gets read<br>SELECT * FROM orders<br>WHERE order_timestamp &gt;= &#39;2023-01-01&#39;</pre><p><strong>The critical trap here:</strong> the partition key (year, month, day as folder names) is often a separate, derived column from the actual timestamp inside the file. Filtering on the raw timestamp instead of the partition columns loses pruning entirely, even though semantically it looks like &quot;the same&quot; filter.</p><h3>The small files problem</h3><p>Over-partitioning creates a different, distinct performance problem:</p><pre>-- Partitioned down to the minute — too granular<br>s3://bucket/events/year=2023/month=01/day=15/hour=09/minute=15/<br>s3://bucket/events/year=2023/month=01/day=15/hour=09/minute=16/<br>...thousands of tiny files...</pre><p>Every file carries fixed overhead (open/close, metadata reads). Thousands of tiny files can be slower to query than fewer, well-sized files, even with pruning working perfectly. This is a well-known operational headache in Spark/Presto environments.</p><p><strong>Rule of thumb:</strong> partition by a column with reasonable cardinality (day is usually fine, minute is usually too granular), and aim for individual files in the 100MB–1GB range.</p><h3>Predicate pushdown: pruning’s companion optimization</h3><p>Columnar file formats (Parquet, ORC) store min/max statistics <em>within</em> each file, at the column level, not just at the folder/partition level. So even after partition pruning narrows down to the right folder, the engine can skip entire row groups inside a file if that file’s own stats show no possible match. This is called <strong>predicate pushdown</strong>.</p><pre>-- Even within the year=2023/month=01 partition, if one file&#39;s<br>-- amount column has a max of 500, this skips that file&#39;s row<br>-- groups entirely without reading them<br>WHERE year = 2023 AND month = 1 AND amount &gt; 10000</pre><h3>Summary table</h3><p><strong>Setup required</strong></p><ul><li>Snowflake: None, automatic</li><li>Presto / Athena / Spark: Manual, folder-based partitioning</li></ul><p><strong>What breaks pruning</strong></p><ul><li>Snowflake: Functions on the filtered column</li><li>Presto / Athena / Spark: Filtering on non-partition columns</li></ul><p><strong>Extra failure mode</strong></p><ul><li>Snowflake: N/A</li><li>Presto / Athena / Spark: Small files problem from over-partitioning</li></ul><p><strong>Complementary optimization</strong></p><ul><li>Snowflake: Clustering (CLUSTER BY)</li><li>Presto / Athena / Spark: Predicate pushdown via columnar file stats</li></ul><h3>4. CTE vs Subquery Performance</h3><p><strong>The misconception to clear up first:</strong> many people assume a CTE is always “materialized” separately, or is inherently faster than an equivalent subquery. In most modern engines, this is false by default.</p><h3>CTEs are typically inlined, not materialized</h3><p>In Snowflake, PostgreSQL 12+, and SQL Server, the optimizer generally treats a CTE like a subquery: it substitutes the CTE’s definition directly into the surrounding query and optimizes the whole thing as a single unit.</p><pre>-- These two are usually functionally identical in performance<br>WITH highEarners AS (<br>    SELECT * FROM employees WHERE salary &gt; 100000<br>)<br>SELECT * FROM highEarners WHERE department = &#39;Engineering&#39;;</pre><pre>-- vs</pre><pre>SELECT * FROM (<br>    SELECT * FROM employees WHERE salary &gt; 100000<br>) t<br>WHERE department = &#39;Engineering&#39;;</pre><p>Both typically get compiled into one combined filter (salary &gt; 100000 AND department = &#39;Engineering&#39;), not two sequential steps.</p><h3>Where CTEs genuinely behave differently: reuse</h3><pre>WITH cleaned AS (<br>    SELECT id, UPPER(TRIM(name)) AS name FROM customers<br>)<br>SELECT * FROM cleaned WHERE name = &#39;JOHN&#39;<br>UNION ALL<br>SELECT * FROM cleaned WHERE name = &#39;JANE&#39;</pre><p>Some engines will compute cleaned once and reuse the result. Others may still inline and recompute it per reference — this is engine- and version-specific. In Snowflake specifically, the optimizer can choose to materialize a CTE referenced multiple times if doing so is estimated to be cheaper, but it is a cost-based decision, not a guarantee.</p><h3>Forcing materialization, by platform</h3><p>There is no universal “force materialize” keyword. Each platform has its own mechanism:</p><p>PlatformHow to force materializationSnowflakeNo explicit hint — use a temp table, regular table, or Dynamic Table insteadPostgreSQLWITH x AS MATERIALIZED (...) (and NOT MATERIALIZED to force the opposite)Presto / TrinoCTAS into an actual tableSpark SQL.cache() / .persist()</p><pre>-- Snowflake: force it via a temp table<br>CREATE TEMPORARY TABLE highEarners AS<br>SELECT * FROM employees WHERE salary &gt; 100000;</pre><pre>SELECT * FROM highEarners WHERE department = &#39;Engineering&#39;<br>UNION ALL<br>SELECT * FROM highEarners WHERE department = &#39;Sales&#39;;</pre><pre>-- PostgreSQL 12+: explicit keyword<br>WITH highEarners AS MATERIALIZED (<br>    SELECT * FROM employees WHERE salary &gt; 100000<br>)<br>SELECT * FROM highEarners WHERE department = &#39;Engineering&#39;<br>UNION ALL<br>SELECT * FROM highEarners WHERE department = &#39;Sales&#39;;</pre><pre># Spark SQL: explicit caching<br>highEarners = spark.sql(&quot;SELECT * FROM employees WHERE salary &gt; 100000&quot;)<br>highEarners.createOrReplaceTempView(&quot;highEarners&quot;)<br>highEarners.cache()  # computed once, reused for every subsequent reference</pre><p><strong>Important judgment call:</strong> forcing materialization has a real cost too (writing data out, or holding it in memory). Only do it once you have confirmed, via EXPLAIN or a query profile, that the engine is genuinely recomputing something expensive multiple times.</p><h3>How to verify whether an engine reused a CTE or recomputed it</h3><p>Do not assume — check the execution plan:</p><ul><li><strong>Snowflake:</strong> run the query and open its Query Profile in Snowsight. If the CTE’s underlying table scan appears once in the graph even though the CTE is referenced multiple times, it was reused. If the same scan appears multiple times, it was recomputed.</li><li><strong>Presto/Trino:</strong> EXPLAIN ANALYZE shows actual runtime stats per operator; look for whether the base table scan appears once or multiple times in the plan tree.</li><li><strong>Spark:</strong> compare the “Analyzed Logical Plan” and “Optimized Logical Plan” from EXPLAIN EXTENDED, or check the Spark UI&#39;s SQL tab for ReusedExchange nodes, which explicitly indicate a repeated computation was detected and reused.</li></ul><p><strong>The most reliable signal across all three platforms:</strong> look at how many times the <em>base table</em> underlying the CTE actually gets scanned in the execution stats, not just at the plan’s visual structure.</p><h3>CTE chains</h3><pre>WITH step1 AS (<br>    SELECT * FROM orders WHERE orderDate &gt;= &#39;2023-01-01&#39;<br>),<br>step2 AS (<br>    SELECT customerId, SUM(amount) AS total FROM step1 GROUP BY customerId<br>),<br>step3 AS (<br>    SELECT * FROM step2 WHERE total &gt; 10000<br>)<br>SELECT * FROM step3</pre><p>Even though this looks like three separately materialized steps, most optimizers try to inline and fuse everything into one plan where possible. This CTE syntax is primarily a <strong>readability tool</strong> for the developer, not a guarantee of separately materialized steps for the database.</p><h3>Practical rule of thumb</h3><p>ScenarioRecommendationCTE referenced onceNo performance difference vs. a subquery — use it for readabilityCTE referenced multiple timesMay help if the engine chooses to materialize it — verify with the query planCorrelated subquery doing a row-by-row comparisonPrefer a JOIN — see Section 5Long CTE chainsFine for readability; trust the optimizer to fuse steps, but verify with EXPLAIN if performance is in question</p><h3>5. Correlated vs. Non-Correlated Subqueries</h3><h3>The core distinction</h3><p>A <strong>non-correlated subquery</strong> runs independently of the outer query — it could execute standalone and return the same result no matter what row the outer query is currently on.</p><p>A <strong>correlated subquery</strong> references a column from the outer query inside itself. It cannot run standalone; its result depends on whatever row the outer query is currently processing.</p><h3>Worked example: employees who earn more than their manager</h3><p>This is a classic problem, and a perfect way to see the difference directly.</p><p><strong>Table:</strong></p><ul><li><strong>id 1</strong> — name: Joe, salary: 70000, managerId: 3</li><li><strong>id 2</strong> — name: Henry, salary: 80000, managerId: 4</li><li><strong>id 3</strong> — name: Sam, salary: 60000, managerId: NULL</li><li><strong>id 4</strong> — name: Max, salary: 90000, managerId: NULL</li></ul><p><strong>Correlated subquery solution:</strong></p><pre>SELECT e.name AS Employee<br>FROM Employee e<br>WHERE e.managerId IS NOT NULL<br>  AND e.salary &gt; (<br>      SELECT m.salary FROM Employee m WHERE m.id = e.managerId<br>  );</pre><p>Notice the inner query references e.managerId — a value from the <em>outer</em> row. You cannot run SELECT m.salary FROM Employee m WHERE m.id = e.managerId on its own; e does not exist outside the outer query&#39;s context. Conceptually, this re-runs the lookup once per outer row:</p><pre>Row 1 (Joe, managerId=3):   look up id=3 -&gt; Sam&#39;s salary (60000), compare<br>Row 2 (Henry, managerId=4): look up id=4 -&gt; Max&#39;s salary (90000), compare<br>Row 3 (Sam, managerId=NULL): look up id=NULL -&gt; no match<br>Row 4 (Max, managerId=NULL): look up id=NULL -&gt; no match</pre><p><strong>JOIN solution (equivalent result, set-based execution):</strong></p><pre>SELECT e.name AS Employee<br>FROM Employee e<br>INNER JOIN Employee m ON e.managerId = m.id<br>WHERE e.salary &gt; m.salary;</pre><p>The INNER JOIN itself already eliminates any row where managerId IS NULL, since NULL = anything never evaluates to TRUE in SQL. This makes the explicit WHERE managerId IS NOT NULL filter from the subquery version redundant here — a useful thing to notice and call out if asked to compare the two approaches.</p><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“Would wrapping this in a CTE that pre-filters managerId IS NOT NULL make the JOIN version faster?&quot;</blockquote><blockquote><em>No. A CTE like </em><em>WHERE managerId IS NOT NULL applied before the join is redundant, since the join&#39;s </em><em>ON condition already discards those rows for the exact same reason (</em><em>NULL never matches). Adding it just means scanning the table an extra time for no benefit.</em></blockquote><h3>Why the JOIN scales better: the mechanical reason</h3><p>A correlated subquery’s cost scales with the <strong>number of outer rows</strong>, since (conceptually) the inner lookup re-runs once per row:</p><pre># Correlated subquery, conceptually<br>result = []<br>for e in employees:                       # loop over every outer row<br>    manager_salary = lookup(e.managerId)   # re-run once PER row<br>    if e.salary &gt; manager_salary:<br>        result.append(e)</pre><p>A JOIN processes the relationship <strong>set-based</strong>: it builds a lookup structure (e.g., a hash table) for one side <strong>once</strong>, then does a single pass over the other side, probing that structure:</p><pre># JOIN, conceptually (hash join)<br>manager_lookup = {m.id: m.salary for m in employees}  # built once<br>result = [e for e in employees if e.salary &gt; manager_lookup.get(e.managerId)]</pre><p><strong>This is the actual cost driver — not raw memory volume, but how many times work gets repeated.</strong> A non-correlated subquery (like SELECT AVG(salary) FROM employees) has a fixed cost regardless of outer table size, since it only runs once. A correlated subquery&#39;s cost scales with N, the outer row count. This is why the concern grows specifically as the outer table grows large — on a few hundred rows it is a non-issue; on millions of rows it becomes a real, measurable problem.</p><p><strong>Important caveat:</strong> modern optimizers often automatically rewrite simple correlated subqueries into joins behind the scenes. So the “runs once per row” mental model is a worst-case framing for reasoning purposes, not a guarantee of what physically executes. Still, preferring an explicit JOIN is the safer default, since you are not relying on the optimizer’s rewrite capability.</p><h3>Correlated subquery in a SELECT clause (not just WHERE)</h3><p>This pattern is extremely common and often written instinctively, without realizing it has the same cost profile as the WHERE-clause version:</p><pre>-- Runs once PER ROW returned, computing an output column for each customer<br>SELECT<br>    c.customerName,<br>    (SELECT COUNT(*) FROM orders o WHERE o.customerId = c.id) AS orderCount<br>FROM customers c;</pre><p><strong>JOIN + GROUP BY rewrite:</strong></p><pre>SELECT<br>    c.customerName,<br>    COUNT(o.id) AS orderCount<br>FROM customers c<br>LEFT JOIN orders o ON o.customerId = c.id<br>GROUP BY c.customerName;</pre><h3>EXISTS: a correlated subquery that is often genuinely efficient</h3><p>EXISTS is technically still correlated (it references the outer row), but it behaves very differently from a scalar comparison:</p><pre>-- Find customers who have placed at least one order<br>SELECT customerName<br>FROM customers c<br>WHERE EXISTS (<br>    SELECT 1 FROM orders o WHERE o.customerId = c.id<br>);</pre><p>The engine only needs to know <strong>whether at least one match exists</strong> — it does not need to fetch or compare an actual value, and most optimizers short-circuit as soon as one match is found. This is often more efficient than the alternative below, not a performance problem.</p><pre>-- Can be less efficient with a large subquery result set<br>SELECT customerName FROM customers<br>WHERE id IN (SELECT customerId FROM orders);</pre><pre>-- Often more efficient, especially when orders is very large<br>SELECT customerName FROM customers c<br>WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customerId = c.id);</pre><h3>NOT IN vs. NOT EXISTS: a correctness trap, not just a performance one</h3><pre>-- DANGEROUS: if orders.customerId contains even one NULL,<br>-- this silently returns ZERO rows, with no error thrown<br>SELECT customerName FROM customers c<br>WHERE c.id NOT IN (SELECT customerId FROM orders);</pre><pre>-- SAFE: NOT EXISTS handles NULLs correctly<br>SELECT customerName FROM customers c<br>WHERE NOT EXISTS (<br>    SELECT 1 FROM orders o WHERE o.customerId = c.id<br>);</pre><p><strong>Why:</strong> SQL’s three-valued logic means x NOT IN (1, 2, NULL) evaluates to UNKNOWN for every row, because comparing anything to NULL with &lt;&gt; returns UNKNOWN, not TRUE — and UNKNOWN fails the WHERE filter. NOT EXISTS works off row existence rather than value comparison, so it does not have this failure mode.</p><h3>Summary table</h3><p><strong>Non-correlated subquery</strong></p><ul><li>Behavior: Runs once, independent of outer rows</li><li>Recommendation: Generally fine as-is</li></ul><p><strong>Correlated subquery (scalar, WHERE)</strong></p><ul><li>Behavior: Conceptually re-runs per outer row</li><li>Recommendation: Prefer a JOIN when possible</li></ul><p><strong>Correlated subquery (scalar, SELECT)</strong></p><ul><li>Behavior: Re-runs per output row</li><li>Recommendation: Prefer JOIN + GROUP BY</li></ul><p><strong>Correlated EXISTS</strong></p><ul><li>Behavior: Short-circuits on first match</li><li>Recommendation: Often efficient, keep as EXISTS</li></ul><p><strong>NOT IN with a nullable column</strong></p><ul><li>Behavior: Correctness risk, not just performance</li><li>Recommendation: Prefer NOT EXISTS</li></ul><h3>6. Join Types and Join Order</h3><p><strong>Important starting point:</strong> your SQL never changes based on which join algorithm runs underneath it. You write JOIN ... ON ... once; the optimizer decides <em>how</em> to physically execute it, based on table sizes, available indexes, and statistics. This is directly analogous to telling someone &quot;sort this deck of cards&quot; without specifying whether they use bubble sort or quicksort internally.</p><h3>Worked example: tracing all three algorithms by hand</h3><p><strong>Customers (small, 4 rows):</strong></p><ul><li><strong>1:</strong> Alice</li><li><strong>2:</strong> Bob</li><li><strong>3:</strong> Carol</li><li><strong>4:</strong> Dave</li></ul><p><strong>Orders (larger, 6 rows):</strong></p><ul><li><strong>orderId 101</strong> — customerId: 2, amount: 50</li><li><strong>orderId 102</strong> — customerId: 1, amount: 30</li><li><strong>orderId 103</strong> — customerId: 4, amount: 20</li><li><strong>orderId 104</strong> — customerId: 2, amount: 15</li><li><strong>orderId 105</strong> — customerId: 3, amount: 40</li><li><strong>orderId 106</strong> — customerId: 1, amount: 60</li></ul><p><strong>The query — identical for all three algorithms below:</strong></p><pre>SELECT c.name, o.amount<br>FROM customers c<br>JOIN orders o ON c.id = o.customerId</pre><p>All three produce the exact same 7-row result. What differs is how much work each one does to get there.</p><h4>Nested Loop Join</h4><p>For every row in the outer table, scan the entire inner table looking for matches.</p><pre>Row 1 (Alice, id=1): check all 6 orders rows -&gt; 6 comparisons, 2 matches<br>Row 2 (Bob, id=2):   check all 6 orders rows -&gt; 6 comparisons, 2 matches<br>Row 3 (Carol, id=3): check all 6 orders rows -&gt; 6 comparisons, 1 match<br>Row 4 (Dave, id=4):  check all 6 orders rows -&gt; 6 comparisons, 1 match</pre><pre>TOTAL: 4 customers x 6 orders = 24 comparisons for 7 actual matches</pre><p><strong>When the optimizer picks this:</strong> one table is very small, or there is a usable index on the inner table’s join column (which turns “scan all 6 rows” into a direct lookup, making this efficient rather than wasteful). Cost is roughly O(N x M) without an index, or closer to O(N log M) with one.</p><h4>Hash Join</h4><p>Build a hash table from the smaller side once, then scan the larger side once, probing the hash table.</p><pre>BUILD PHASE (customers, 4 rows, touched once):<br>    HashTable = { 1: &quot;Alice&quot;, 2: &quot;Bob&quot;, 3: &quot;Carol&quot;, 4: &quot;Dave&quot; }</pre><pre>PROBE PHASE (orders, 6 rows, touched once):<br>    101 (cust=2) -&gt; hash lookup &quot;2&quot; -&gt; Bob -&gt; match<br>    102 (cust=1) -&gt; hash lookup &quot;1&quot; -&gt; Alice -&gt; match<br>    103 (cust=4) -&gt; hash lookup &quot;4&quot; -&gt; Dave -&gt; match<br>    104 (cust=2) -&gt; hash lookup &quot;2&quot; -&gt; Bob -&gt; match<br>    105 (cust=3) -&gt; hash lookup &quot;3&quot; -&gt; Carol -&gt; match<br>    106 (cust=1) -&gt; hash lookup &quot;1&quot; -&gt; Alice -&gt; match</pre><pre>TOTAL: 4 (build) + 6 (probe) = 10 operations</pre><p>Compare to nested loop’s 24 comparisons — hash join touches each table exactly once, with cost roughly proportional to N + M rather than N x M. This gap grows enormously with scale: for a 10,000-row table joined to a 1,000,000-row table, nested loop without an index is ~10 billion comparisons, while hash join is ~1.01 million operations. This is why hash join is the default choice for large, unindexed joins in Snowflake, Spark, and Presto.</p><h4>Merge (Sort-Merge) Join</h4><p>Sort both sides by the join key, then walk both sorted lists together with two pointers, like merging two sorted decks of cards.</p><pre>Sorted customers (already sorted by id): 1-Alice, 2-Bob, 3-Carol, 4-Dave<br>Sorted orders (sorted by customerId):    102(1), 106(1), 101(2), 104(2), 105(3), 103(4)</pre><pre>Walk both pointers forward, never backward:<br>customers=1, orders=1 -&gt; match (Alice, 30), advance orders<br>customers=1, orders=1 -&gt; match (Alice, 60), advance orders<br>customers=1, orders=2 -&gt; no match, advance customers<br>customers=2, orders=2 -&gt; match (Bob, 50), advance orders<br>customers=2, orders=2 -&gt; match (Bob, 15), advance orders<br>customers=2, orders=3 -&gt; no match, advance customers<br>customers=3, orders=3 -&gt; match (Carol, 40), advance orders<br>customers=3, orders=4 -&gt; no match, advance customers<br>customers=4, orders=4 -&gt; match (Dave, 20)</pre><p><strong>The critical caveat:</strong> merge join is only cheap when at least one side is <em>already</em> sorted (typically due to an existing index). If both sides need sorting from scratch, that sort costs roughly O(M log M), and merge join can lose to hash join.</p><h4>Side-by-side cost comparison (this example)</h4><p>AlgorithmWork performedTotal operationsNested Loop (no index)Re-scan the full inner table for every outer row4 x 6 = 24Hash JoinTouch each table once (build once, probe once)4 + 6 = 10Merge JoinSort both sides once, then a single forward pass4 + 6 = 10, plus sort cost if unsorted</p><p><strong>One-sentence mental model for each:</strong></p><ul><li><strong>Nested Loop:</strong> for every row on one side, look through the other side — cheap only if the inner side is small or indexed.</li><li><strong>Hash Join:</strong> build a lookup dictionary once, then check every row against it — cheap for large, unindexed tables.</li><li><strong>Merge Join:</strong> line up two sorted lists and walk them together like a zipper — cheap only if both sides are already sorted.</li></ul><blockquote><strong><em>Follow-up question worth knowing:</em></strong><em> </em>“If I don’t control the join algorithm through SQL, how do I actually change it?”</blockquote><blockquote><em>You don’t force it directly in most cases; you influence the conditions the optimizer bases its decision on:<br>- </em><strong>Add or remove an index</strong> on the join column — the most common, most legitimate lever.<br>- <strong>Refresh statistics</strong> (ANALYZE in Postgres/MySQL; automatic in Snowflake) so the optimizer&#39;s row-count estimates are accurate.<br>- <strong>Restructure the query</strong> to filter down to a smaller intermediate result before the join, which can change which algorithm looks cheapest.<br>- <strong>Explicit hints</strong> exist in some engines (MySQL, SQL Server, Spark’s /*+ BROADCAST(table) */) but are a last resort, not standard practice and <strong>Snowflake exposes no join hints at all</strong>, by design, trusting its cost-based optimizer over manual overrides.</blockquote><h3>Why join order matters</h3><pre>SELECT *<br>FROM orders o<br>JOIN customers c ON o.customerId = c.id<br>JOIN products p ON o.productId = p.id<br>WHERE c.region = &#39;US&#39;</pre><p>The order tables are <em>written</em> in SQL does not dictate execution order — the optimizer decides that. But the underlying principle is worth internalizing: the optimizer tries to minimize the size of intermediate results at every step, ideally filtering down to the smallest possible row count as early as possible, then joining progressively larger things afterward.</p><p><strong>Bad conceptual order:</strong> join orders to products first (both potentially huge, producing a massive intermediate result), then join that huge result to customers last — meaning the cheap region = &#39;US&#39; filter only helps after the expensive work is already done.</p><p><strong>Good conceptual order:</strong> filter customers down to region = &#39;US&#39; first (small result), then join that small filtered set to orders, keeping every intermediate result as small as possible.</p><h3>Why join order decisions can go wrong: stale statistics</h3><p>Databases use a <strong>cost-based optimizer (CBO)</strong>, which relies on table statistics (row counts, distinct value counts, data distribution) to estimate the cost of different join orders and algorithms, then picks the cheapest estimated plan. If those statistics are stale — for example, right after a huge data load — the optimizer might think a table has 100 rows when it actually has 100 million, and choose a nested loop join when a hash join would have been far better. This is a common real-world cause of a query that “used to be fast” suddenly becoming slow.</p><ul><li><strong>Snowflake:</strong> statistics are automatically maintained as part of micro-partition metadata — no manual ANALYZE needed.</li><li><strong>Postgres/MySQL:</strong> you may need to explicitly run ANALYZE after large data changes.</li></ul><h3>The unifying theme across indexing, partitioning, and joins</h3><p>Nearly everything in query optimization comes down to one question: <strong>can the database avoid touching data it doesn’t need?</strong> There are exactly two families of tools for this:</p><ol><li><strong>Skip data entirely</strong> via indexing, clustering, or partition pruning (sargability, index awareness, partition pruning, nested loop + index, merge join).</li><li><strong>Avoid <em>repeated</em> scanning</strong> via hashing, when skipping isn’t possible (hash joins specifically — the technique the optimizer reaches for precisely when no useful index exists).</li></ol><p>These are genuinely two different mechanisms solving the same underlying problem, not the same idea wearing different names.</p><h3>7. Reading Execution Plans (EXPLAIN)</h3><p>This is where you go to actually verify everything above: sargability, index usage, partition pruning, join algorithm choice, CTE materialization.</p><h3>EXPLAIN vs. EXPLAIN ANALYZE</h3><ul><li><strong>EXPLAIN alone:</strong> shows the estimated plan, based on statistics, without running the query. Safe to use on expensive queries you don&#39;t want to actually execute.</li><li><strong>EXPLAIN ANALYZE:</strong> actually runs the query and shows real numbers alongside the estimates. More reliable, but it executes — use with care on expensive production queries.</li></ul><h3>Worked example (Postgres-style output, the clearest for teaching)</h3><pre>EXPLAIN ANALYZE<br>SELECT c.name, o.amount<br>FROM customers c<br>JOIN orders o ON c.id = o.customerId<br>WHERE c.region = &#39;US&#39;;</pre><pre>Hash Join  (cost=1.23..15.67 rows=120 width=64) (actual time=0.045..0.312 rows=118 loops=1)<br>  Hash Cond: (o.customerId = c.id)<br>  -&gt; Seq Scan on orders o  (cost=0.00..10.00 rows=1000 width=8) (actual time=0.010..0.150 rows=1000 loops=1)<br>  -&gt; Hash  (cost=1.10..1.10 rows=10 width=56) (actual time=0.020..0.020 rows=10 loops=1)<br>     -&gt; Seq Scan on customers c  (cost=0.00..1.10 rows=10 width=56) (actual time=0.005..0.012 rows=10 loops=1)<br>        Filter: (region = &#39;US&#39;::text)<br>        Rows Removed by Filter: 90</pre><p><strong>How to read this: bottom-up, inside-out.</strong> The deepest/innermost operations run first, feeding upward.</p><ol><li>Seq Scan on customers c ... Filter: (region = &#39;US&#39;) runs first — scans all 100 customers, filters to 10 matching US customers. Rows Removed by Filter: 90 confirms this filter is doing real, effective work.</li><li>Hash — those 10 filtered rows get built into a hash table.</li><li>Seq Scan on orders o — a full scan of all 1000 orders rows, no filter, no index (Seq Scan means full scan, not Index Scan).</li><li>Hash Join at the top — the two results combine.</li></ol><p><strong>The critical numbers to compare: estimated vs. actual.</strong> Here, estimated rows=120 is close to actual rows=118 — the optimizer&#39;s statistics are accurate, and it likely made a good decision. <strong>Red flag:</strong> if estimated and actual rows are wildly different (say, estimated 10, actual 500,000), that is a strong signal of stale statistics, and the optimizer likely picked a bad plan based on wrong assumptions — a very common real-world cause of &quot;this query used to be fast and now it&#39;s slow.&quot;</p><h3>Key operators to recognize across engines</h3><p>Term (varies by engine)What it meansWhat you want to seeSeq Scan / Full Table ScanReading every row in the tableFine for small tables; a red flag on large onesIndex Scan / Index SeekUsing an index to jump to matching rowsGenerally good — confirms sargability is workingHash Join / Nested Loop / Merge JoinWhich join algorithm was chosenSanity-check against table sizes, per Section 6FilterA WHERE condition applied at this stepCheck Rows Removed by Filter — high removal is a good signRows (estimated vs. actual)Optimizer&#39;s guess vs. realityA large mismatch = stale statistics, unreliable planSortAn explicit sort operationCan be expensive at scale — check if an existing index could avoid it</p><h3>Snowflake specifics: the Query Profile</h3><p>In Snowsight, the visual Query Profile is what you would actually use day to day. Key things to check:</p><ul><li><strong>Partitions scanned vs. Partitions total</strong> — your direct confirmation of whether partition pruning worked. “100 of 100 partitions scanned” after filtering on a date usually points to a sargability issue.</li><li><strong>The most expensive node</strong>, visually highlighted, letting you spot the bottleneck immediately without reading raw plan text.</li><li><strong>Bytes spilled to local/remote storage</strong> — indicates the query ran out of memory and had to spill to disk, a strong signal the warehouse may be undersized for the query’s working data volume, or that the query needs restructuring.</li></ul><h3>A practical debugging workflow</h3><pre>1. Run EXPLAIN (or EXPLAIN ANALYZE if safe to execute)<br>2. Read from the bottom up: what runs first?<br>3. Look for Seq Scan / Full Table Scan on large tables<br>   -&gt; possible missing index or broken sargability<br>4. Compare estimated vs actual row counts<br>   -&gt; large gaps mean stale statistics<br>5. Check which join algorithm was chosen<br>   -&gt; does it make sense given table sizes?<br>6. Check for unnecessary Sort operations<br>   -&gt; could an existing index avoid them?<br>7. In Snowflake: check partition pruning ratio and any spillage</pre><h3>8. Result Caching</h3><h3>Snowflake’s result cache</h3><p>Snowflake automatically caches the exact result of a query for 24 hours (extended up to 31 days each time it’s reused). If the identical query text runs again and the underlying data hasn’t changed, Snowflake returns the cached result instantly, with <strong>zero warehouse compute spun up</strong>.</p><pre>-- First run: actually executes, uses warehouse compute<br>SELECT department, AVG(salary) FROM employees GROUP BY department;</pre><pre>-- Second run, identical text, data unchanged:<br>-- returns instantly from result cache, no compute cost at all<br>SELECT department, AVG(salary) FROM employees GROUP BY department;</pre><p><strong>Conditions for the cache to apply:</strong> the exact same SQL text, the underlying table data unchanged since the cached result was produced, and within the (extendable) 24-hour window.</p><h3>Why this can mislead performance testing</h3><p>If you optimize a slow query and then run it twice in a row to “test if it worked,” the second run will look blazing fast — but that speed came from the result cache, not your optimization. To test honestly:</p><pre>-- Disable result caching for the duration of a benchmarking session<br>ALTER SESSION SET USE_CACHED_RESULT = FALSE;</pre><p><strong>Practical tip:</strong> always disable result caching before benchmarking query performance changes, or you cannot trust your before/after comparison.</p><h3>Warehouse-level (local disk) caching — a separate mechanism</h3><p>Easy to conflate with result caching, but mechanically different:</p><ul><li><strong>Result cache:</strong> stores the final output of a specific query, independent of any warehouse.</li><li><strong>Warehouse local disk cache:</strong> each running virtual warehouse caches raw data pages it has recently read, on local SSD. A <em>different</em> query touching the same underlying data can reuse those cached pages to speed up its scan, without needing an exact query match.</li></ul><p>A freshly resumed (previously suspended) warehouse has a cold, empty local cache — so the first few queries after a resume will be slower than subsequent ones, purely due to this, not because anything is wrong with the query.</p><h3>How other engines compare</h3><p>EngineCaching mechanismSnowflakeResult cache (24hr, exact match) + warehouse local disk cachePresto / TrinoNo built-in persistent result cache by default (some managed platforms add one)Spark.cache() / .persist() — explicit and manual, not automaticPostgreSQL / MySQLNo query result cache by default (MySQL removed its old query cache entirely in 8.0, since a single write invalidated the entire cache for that table under high write concurrency)</p><h3>9. Avoiding Unnecessary Operations</h3><h3>SELECT * vs. explicit columns</h3><pre>-- Wasteful<br>SELECT * FROM orders WHERE customerId = 123;</pre><pre>-- Better<br>SELECT orderId, orderDate, amount FROM orders WHERE customerId = 123;</pre><p>Beyond readability, this matters for real reasons: columnar engines (Snowflake, Parquet-based Spark/Presto) only read the columns you actually select — SELECT * forces reading every column&#39;s data even when most get discarded, a genuinely significant I/O cost in columnar systems specifically. It also costs more in network/serialization, and can break silently on schema changes (a new column added upstream can suddenly appear in output that wasn&#39;t expecting it).</p><h3>Reflexive DISTINCT</h3><pre>-- Often added to &quot;fix&quot; an unintended join fan-out, instead of the real cause<br>SELECT DISTINCT c.name, c.email<br>FROM customers c<br>JOIN orders o ON c.id = o.customerId  -- duplicates customers with multiple orders</pre><p>DISTINCT requires sorting or hashing the entire result set to remove duplicates — real computational cost. If it&#39;s masking a fan-out bug, fix the actual cause instead:</p><pre>-- Fixes the cause instead of masking it with DISTINCT<br>SELECT c.name, c.email<br>FROM customers c<br>WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customerId = c.id);</pre><h3>Unnecessary ORDER BY inside intermediate steps</h3><pre>-- Pointless: sorting inside a CTE that just gets aggregated afterward<br>WITH sortedOrders AS (<br>    SELECT * FROM orders ORDER BY orderDate<br>)<br>SELECT customerId, SUM(amount) FROM sortedOrders GROUP BY customerId</pre><p>The ORDER BY here accomplishes nothing — GROUP BY does not care about input order, and most optimizers won&#39;t even preserve that ordering through to the aggregation. <strong>Rule of thumb:</strong> only sort the final, outermost result set that actually needs to be sorted for the end consumer. Remove it from intermediate CTEs/subqueries. (Exception: ORDER BY inside a window function&#39;s OVER() clause is a different, necessary usage.)</p><h3>COUNT(*) vs. COUNT(column) vs. COUNT(DISTINCT column)</h3><pre>COUNT(*)                    -- counts all rows, including NULLs, generally fastest<br>COUNT(columnName)           -- counts only non-NULL rows for that column<br>COUNT(DISTINCT columnName)  -- counts distinct non-null values, most expensive, requires dedup</pre><p>Reaching for COUNT(DISTINCT x) out of habit when COUNT(*) or COUNT(x) would do pays an unnecessary deduplication cost.</p><h3>HAVING vs. WHERE</h3><pre>-- Wasteful: filters AFTER aggregating everything<br>SELECT customerId, SUM(amount) AS total<br>FROM orders<br>GROUP BY customerId<br>HAVING customerId IN (SELECT id FROM customers WHERE region = &#39;US&#39;)</pre><pre>-- Better: filters BEFORE aggregating, so less data gets aggregated at all<br>SELECT o.customerId, SUM(o.amount) AS total<br>FROM orders o<br>WHERE o.customerId IN (SELECT id FROM customers WHERE region = &#39;US&#39;)<br>GROUP BY o.customerId</pre><p><strong>The rule:</strong> HAVING should only filter on the result of an aggregate function itself (e.g., HAVING SUM(amount) &gt; 1000), since that value doesn&#39;t exist until after aggregation. Any condition that doesn&#39;t depend on an aggregate belongs in WHERE, so it runs before the expensive aggregation step.</p><h3>10. UNION vs. UNION ALL</h3><pre>-- UNION: combines results AND removes duplicate rows<br>SELECT customerId FROM ordersUS<br>UNION<br>SELECT customerId FROM ordersEU;</pre><pre>-- UNION ALL: combines results, keeps duplicates, no dedup work at all<br>SELECT customerId FROM ordersUS<br>UNION ALL<br>SELECT customerId FROM ordersEU;</pre><p>UNION is functionally UNION ALL followed by an implicit DISTINCT — it has to sort or hash the entire combined result set to identify and drop duplicates, genuinely expensive at scale. UNION ALL just concatenates, with no comparison work at all.</p><p><strong>Default to </strong><strong>UNION ALL unless you specifically know duplicates are possible and unwanted.</strong> A common mistake is writing UNION reflexively even when the two source queries could never logically overlap (e.g., pulling from two mutually exclusive regional tables) — paying a real dedup cost for zero benefit.</p><pre>-- If ordersUS and ordersEU can never share a row, UNION ALL is<br>-- strictly better: same result, less work<br>SELECT customerId FROM ordersUS<br>UNION ALL<br>SELECT customerId FROM ordersEU;</pre><p>UNION genuinely earns its cost when overlap is real and unwanted — for example, two queries against the same table that could legitimately return the same row (a customer who is both a high spender and flagged, if you only want them listed once).</p><h3>11. Data Skew and Approximate Aggregates</h3><p><strong>Data skew:</strong> when a join or GROUP BY key is unevenly distributed — for example, one customerId has 10 million orders while everyone else has a few hundred — distributed engines like Spark or Presto can bottleneck badly, since one worker node gets stuck processing the oversized partition while every other node finishes quickly and sits idle. This is a distinctly different problem from the join algorithm or partitioning choices above; it is about the <em>shape</em> of the data itself, not the query structure.</p><p><strong>Approximate aggregate functions:</strong> functions like APPROX_COUNT_DISTINCT (Snowflake) or APPROX_DISTINCT (Presto) trade a small amount of precision for dramatically lower compute cost on huge datasets — useful when a &quot;close enough&quot; distinct count on billions of rows is acceptable, rather than an exact one.</p><h3>12. Materialized Views and Wildcard Sargability</h3><h3>Materialized Views</h3><p>A regular view is just saved SQL text — every query against it re-runs the underlying logic from scratch:</p><pre>CREATE VIEW customerOrderSummary AS<br>SELECT customerId, SUM(amount) AS totalSpend<br>FROM orders<br>GROUP BY customerId;</pre><pre>-- Every single call re-aggregates the entire orders table, live<br>SELECT * FROM customerOrderSummary WHERE totalSpend &gt; 10000;</pre><p>A <strong>materialized view</strong> physically stores the computed result, like a table, and refreshes it periodically or incrementally instead of recomputing on every query:</p><pre>-- Snowflake syntax<br>CREATE MATERIALIZED VIEW customerOrderSummary AS<br>SELECT customerId, SUM(amount) AS totalSpend<br>FROM orders<br>GROUP BY customerId;</pre><pre>-- Reads precomputed, stored results — no live aggregation<br>SELECT * FROM customerOrderSummary WHERE totalSpend &gt; 10000;</pre><p>Regular ViewMaterialized ViewRegular TableStorage costNone, just SQL textYes, stores actual dataYesFreshnessAlways liveNear real-time, auto-refreshed by SnowflakeOnly as fresh as the last load/dbt runQuery costFull recompute every timeFast, reads precomputed dataFast, reads stored dataMaintenanceNoneSnowflake manages refresh automaticallyYou manage refresh (e.g., dbt incremental)</p><p><strong>Use a materialized view when:</strong> the underlying query is expensive (large aggregations, complex joins), the result is queried frequently, and you want it kept reasonably fresh automatically without writing your own refresh job.</p><p><strong>Real limitations worth knowing:</strong> Snowflake materialized views have restrictions (historically single-table only; this has expanded over time but constraints remain), and they cost ongoing background compute to maintain, whether or not anyone actually queries them — a genuine tradeoff, not a free lunch.</p><p><strong>Dynamic Tables — the more flexible modern alternative:</strong></p><pre>CREATE DYNAMIC TABLE customerOrderSummary<br>    TARGET_LAG = &#39;1 hour&#39;<br>    WAREHOUSE = my_warehouse<br>AS<br>SELECT customerId, SUM(amount) AS totalSpend<br>FROM orders<br>GROUP BY customerId;</pre><p>Dynamic Tables support a much wider range of SQL (multi-table joins, more complex transformations) and give explicit control over freshness via TARGET_LAG, rather than Snowflake deciding refresh timing automatically as it does for materialized views.</p><h3>Wildcard LIKE Patterns and Sargability</h3><p>This is a very common one-liner “gotcha” question, and a direct extension of Section 1’s sargability rule.</p><pre>-- CANNOT use an index: leading wildcard<br>WHERE name LIKE &#39;%smith%&#39;<br>WHERE name LIKE &#39;%smith&#39;</pre><pre>-- CAN use an index: wildcard only at the end<br>WHERE name LIKE &#39;smith%&#39;</pre><p><strong>Why, mechanically:</strong> an index on name is sorted alphabetically, like a phone book.</p><ul><li>LIKE &#39;smith%&#39; (&quot;starts with smith&quot;) is exactly like looking up a name in a phone book — jump straight to the &quot;Sm&quot; section, read forward until entries stop matching. <strong>Fully sargable.</strong></li><li>LIKE &#39;%smith%&#39; (&quot;contains smith anywhere&quot;) has no alphabetical shortcut — &quot;smith&quot; could be buried inside &quot;Blacksmithson,&quot; &quot;Goldsmith,&quot; or &quot;Smithville,&quot; which are scattered all over the sorted index, not grouped together. The database must check every row&#39;s full value. <strong>Full table scan, the index cannot help at all.</strong></li></ul><p><strong>Real fixes when substring search is a genuine requirement:</strong></p><pre>-- Snowflake: Search Optimization Service, purpose-built for this<br>ALTER TABLE customers ADD SEARCH OPTIMIZATION ON EQUALITY(name), SUBSTRING(name);</pre><pre>-- PostgreSQL: trigram index, breaks strings into overlapping<br>-- chunks so substring search can actually use an index<br>CREATE EXTENSION pg_trgm;<br>CREATE INDEX idx_name_trgm ON customers USING gin (name gin_trgm_ops);</pre><p>Sometimes it’s also worth simply clarifying the actual business requirement — “starts with” is often sufficient where “contains” was assumed, and avoids the expensive path entirely.</p><h3>The One Unifying Idea</h3><p>If there is a single thread connecting every topic in this guide, it’s this:</p><blockquote><strong><em>Query optimization is almost entirely about minimizing how much data the engine has to touch — and there are only two ways to do that: skip data you don’t need (indexes, clustering, partition pruning), or avoid re-scanning the same data repeatedly (hash joins, avoiding correlated subqueries, avoiding redundant CTE computation).</em></strong></blockquote><p>Every technique in this guide is one of those two strategies, applied to a different part of the query.</p><h3>Quick-Reference Summary</h3><ul><li><strong>Sargability:</strong> never wrap a filtered column in a function (YEAR(), UPPER(), arithmetic) — it disables index/partition use and forces a full scan.</li><li><strong>Indexes:</strong> speed up reads, slow down writes; can be added or dropped after a table already has data; indexing a downstream analytics copy never affects the source system.</li><li><strong>Snowflake has no traditional indexes</strong> — it uses automatic micro-partition metadata plus optional CLUSTER BY for manual tuning.</li><li><strong>Partition pruning</strong> is automatic in Snowflake, but manual (folder-based) in Presto/Spark — and filtering on a raw column instead of the partition key silently disables it.</li><li><strong>Over-partitioning causes the “small files problem”</strong> in Spark/Presto — aim for 100MB–1GB files, not one file per minute.</li><li><strong>A CTE used once is just readability</strong> — it performs identically to an equivalent subquery in most modern optimizers.</li><li><strong>A CTE reused multiple times can genuinely save compute</strong>, but only verify this by checking the execution plan, not by assuming it.</li><li><strong>Correlated subqueries scale with the outer row count</strong>; non-correlated subqueries have a fixed, one-time cost — prefer JOINs for row-by-row comparisons at scale.</li><li><strong>EXISTS is efficient and fine to keep</strong> — it short-circuits on the first match, unlike a scalar correlated subquery.</li><li><strong>NOT IN with a nullable column is a correctness bug</strong>, not just a performance issue — use NOT EXISTS instead.</li><li><strong>You never choose the join algorithm directly</strong> — you influence it via indexes, statistics, and query structure; Snowflake exposes no join hints at all, by design.</li><li><strong>Hash joins are the default for large, unindexed joins</strong> — cost scales with N + M instead of N x M.</li><li><strong>Merge joins are only cheap when a side is already sorted</strong> — otherwise the sort cost can make them lose to a hash join.</li><li><strong>Stale statistics are a common, real cause of a query “suddenly” getting slow</strong> — refresh them (ANALYZE) outside Snowflake, where it&#39;s automatic.</li><li><strong>Read execution plans bottom-up</strong> — and always compare estimated vs. actual row counts to catch bad statistics.</li><li><strong>In Snowflake, check partition-scan ratio and spillage in the Query Profile</strong>, not just the raw plan text.</li><li><strong>Result caching can make a bad query look fast on the second run</strong> — disable it (USE_CACHED_RESULT = FALSE) before benchmarking.</li><li><strong>Avoid </strong><strong>SELECT *</strong>, especially in columnar engines, where it forces reading unused columns from disk.</li><li><strong>Reflexive </strong><strong>DISTINCT often masks an unintended join fan-out</strong> — fix the join, don&#39;t paper over it.</li><li><strong>Only sort the final output</strong>, not intermediate CTEs whose order gets discarded anyway.</li><li><strong>Filter in </strong><strong>WHERE, not </strong><strong>HAVING, unless the condition depends on an aggregate result.</strong></li><li><strong>Default to </strong><strong>UNION ALL</strong> — only pay for UNION&#39;s deduplication when overlap is real and unwanted.</li><li><strong>Data skew is a distributed-systems problem</strong>, not a query-structure one — one oversized key can bottleneck an entire Spark/Presto job.</li><li><strong>Materialized views store results physically and refresh automatically</strong>; Dynamic Tables are Snowflake’s more flexible, freshness-configurable alternative.</li><li><strong>A leading wildcard (</strong><strong>LIKE &#39;%x%&#39;) can never use a standard index</strong> — a trailing wildcard (LIKE &#39;x%&#39;) can; substring search at scale needs a purpose-built solution like trigram indexes or Snowflake&#39;s Search Optimization Service.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3000f9dfbd4a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Irony of Being a Data Scientist Who Does Not Share Their Data]]></title>
            <link>https://bysuchi.medium.com/the-irony-of-being-a-data-scientist-who-does-not-share-their-data-012144181b8a?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/012144181b8a</guid>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-privacy]]></category>
            <category><![CDATA[privacy]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Tue, 23 Jun 2026 14:30:37 GMT</pubDate>
            <atom:updated>2026-06-23T14:30:37.401Z</atom:updated>
            <content:encoded><![CDATA[<p><em>When the people who understand data the most are also the most suspicious of it</em></p><p>I am a data scientist. But I do not share my data.</p><p>If that sounds ironic, it is. Very much intentionally so.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*abUfk8aikB7O7_ON.gif" /><figcaption><a href="https://media2.giphy.com/media/v1.Y2lkPTc5MGI3NjExeHloOWhucTh3eHB4NG9leWRpZG1qbThldWVwbXhyNmR2cGF1NmRhcSZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/xT9C25UNTwfZuk85WP/giphy.gif">Data, Data Everywhere from GIPHY</a></figcaption></figure><p>Data science, at its core, is the art of using large, complex data and reducing it into patterns to predict. The prediction can be about the future, the best possible outcome, or the direction of change.</p><p>One would think that the general intent of data science and AI, is to better humanity. But that is not always the case. Data science serves the person who owns the outcome.</p><p>Take the weather app. It tells you if it is expected to rain so that you can go better prepared, not get wet and eventually maybe not get sick. Or image recognition that looks at brain scans and tells you whether there is a tumor. If there is, it helps detect it early and potentially save a life. These are the cases where data science is genuinely on your side.</p><p>But then there are cases where the outcome is built to serve the owner of the model. Or worse, where the data is being collected specifically to be used in a model that is designed to influence the user.</p><p><strong>The gray area</strong></p><p>The most common example is the ads and marketing industry.</p><p>The ads you see on social media platforms pick up on your activities. There are data science models in the background that identify your potential interests, things you would potentially spend money on. They make recommendations, show up on your feed, attract you to click, and eventually nudge you to purchase.</p><p>But the real question is: did you need that product in the first place? Did the algorithm trick you into spending money on something you do not need? Or did the product genuinely help make your life better?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*ibbO0qfYOMPACFiu.gif" /><figcaption><a href="https://media1.giphy.com/media/v1.Y2lkPTc5MGI3NjExc2I5Z2J2ZGVzN2swZm03MDR6bTk1bG40bDc0Nm9sN3Y4M2RhNDViciZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/Vh3cvmaCRW05KwMwoC/giphy.gif">Watching you from GIPHY</a></figcaption></figure><p>That is the gray area. You do not always know if data science is built to serve you or the business behind it.</p><p>Here is a personal example of that gray area in action. On a recent trip to Hawaii, I had my location turned on. Almost immediately, the algorithm picked up on it and started suggesting activities. Zip lining suggestions kept coming, one after another. Honestly, the photos were amazing. It was very tempting.</p><p>But we already had a well planned itinerary. After thinking it through, we realized that driving there and spending the time would disrupt everything we had already planned. More importantly, zip lining is something I could do anywhere. The experiences we had planned were specific to that island.</p><p>The algorithm was not necessarily wrong. But it was nudging me toward something that worked better for the platform than for my actual trip.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*hiOZxmET5AE9Cg0S.gif" /><figcaption><a href="https://media1.giphy.com/media/v1.Y2lkPTc5MGI3NjExeTM2ZXd6aW5waWJkYWl3YTZwZXJyNTB2aGp1ZWYzNzMxZmM2dzY5dyZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/5gw0VWGbgNm8w/giphy.gif">I see what you did there from GIPHY</a></figcaption></figure><p><strong>The irony</strong></p><p>As a data scientist, I have worked on models for fraud prevention, where the goal is to stop something bad from happening. I have also worked on customer retention models, where the goal is to help the company not lose any money.</p><p>I know firsthand that data can be used to make things better for you. But I am also very aware that it can be used against you, or to influence you in ways you never intended.</p><p>Which is why I always try to keep my location turned off unless it is absolutely necessary. When I sign up for a new social media platform, you know those questions they ask, your age, your date of birth, where you are from? If it is not legally required, I skip them. If I cannot skip them, I do not always give the most accurate answer.</p><p>Because I want to make the algorithm make mistakes.</p><p>I want to save myself by making the algorithm work imperfectly on me. I say this as someone who builds these models for a living.</p><p>You cannot always escape from sharing your data, whether it is on social media or other platforms. Algorithms will always try to influence you in some way. But maybe we can make conscious choices. Or at the very least, be aware of what might be happening behind the scenes, so that we can make better judgments and better decisions.</p><p>So the next time an algorithm surprises you with a recommendation, pause for a second. Has there ever been a moment where you were influenced to buy something you never would have bought if it was not for an algorithm recommendation? Drop it in the comments. I would genuinely love to know.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=012144181b8a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What Is Data Science? You Do Not Need to Be a Data Scientist to Understand It]]></title>
            <link>https://bysuchi.medium.com/what-is-data-science-you-do-not-need-to-be-a-data-scientist-to-understand-it-b1eb31c9d202?source=rss-497698dcd858------2</link>
            <guid isPermaLink="false">https://medium.com/p/b1eb31c9d202</guid>
            <category><![CDATA[beginners-guide]]></category>
            <category><![CDATA[careers]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Suchithra Balakrishnan]]></dc:creator>
            <pubDate>Tue, 16 Jun 2026 20:47:34 GMT</pubDate>
            <atom:updated>2026-06-17T23:01:29.902Z</atom:updated>
            <content:encoded><![CDATA[<p>Data science sounds like a big, serious term. The kind that belongs in a boardroom or a research lab. But strip away the jargon and it is actually something you encounter before you even have your morning coffee. Lets simplify it today.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*LnTnX1gPTHNdDD2A.gif" /><figcaption><a href="https://media3.giphy.com/media/v1.Y2lkPTc5MGI3NjExN2Z6bzhrajZlazR6d2I3cDFvaDU2Z2NzMWl2NnRkejRiYjI3aGV3cCZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/JWuBH9rCO2uZuHBFpm/giphy.gif">I eat data for breakfast from GIPHY</a></figcaption></figure><p>At its core, data science is about finding patterns. That is it. If everything in the world happened completely randomly, there would be nothing to study, nothing to predict, and honestly, no need for data science at all. But the world is not random. People tend to behave in consistent ways. Events follow patterns. History has a habit of rhyming.</p><p>Data science takes those patterns and asks: what can we learn from them? And more importantly, what can we do with what we learn?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/240/0*XZykFBcEkRv8BmNB.gif" /><figcaption><a href="https://media0.giphy.com/media/v1.Y2lkPTc5MGI3NjExd3hpZmhvemVyd216cDdhY3NtZDVvN2ZwYWI0a2E5NG5xaG1kY3VhbyZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/0NwSQpGY6ipgOSt8LL/giphy.gif">Mind blown from GIPHY</a></figcaption></figure><p><strong>A quick trip to the grocery store</strong></p><p>Here is an example I learned early on in my data science journey, and one I have never been able to “unsee” since.</p><p>Have you ever noticed that bread and spreads, like butter or jam, tend to live in the same aisle? Or that chips always seem to be right next to the dips? That is not a coincidence, and it is definitely not just common sense on the part of a store manager.</p><p>Retailers use statistics and sales data to figure out exactly which layout will make customers spend the most. They look at what people tend to buy together, and then they arrange the store to reflect those patterns. Sometimes it is about convenience for you. Often, it is also about nudging you toward buying more. Either way, data is the thing making that decision, not guesswork.</p><p>Once I learned this, I started noticing it everywhere. The placement of products at eye level. The way essentials like milk and eggs are always at the back of the store, so you have to walk past everything else to get to them. It is all deliberate. All data-driven.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/240/0*uGubUr8G307AMmXc.gif" /><figcaption><a href="https://media0.giphy.com/media/v1.Y2lkPTc5MGI3NjExMTQ3eDNxOXBxeXA0ZmtlNm56dzY5eHdlbGxycHdoODhrZ3QwZWswdCZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/dXFKDUolyLLi8gq6Cl/giphy.gif">Its true from GIPHY</a></figcaption></figure><p><strong>So what does data science actually do for you?</strong></p><p>At its heart, data science helps reduce uncertainty. And that matters because uncertainty is uncomfortable. We like knowing what to expect.</p><p>Think about your weather app. When it tells you there is a 70% chance of rain tomorrow, that number did not come from someone looking out the window and making a guess. It came from years of weather data, patterns of temperature, humidity, and wind, all analyzed to give you the most informed prediction possible. You grab your umbrella. You wear a thicker jacket. A small insight changed how your day went.</p><p>Now think about something slightly higher stakes. When your bank sends you a text asking “did you make this purchase?” after an unusual transaction, that is data science at work. Fraud detection works by learning what your normal spending looks like. When something does not fit that pattern, it raises a flag. That quiet, invisible protection running in the background? Data science.</p><p><strong>It is more woven in than you think</strong></p><p>As someone who works in data science, I can tell you that even I am occasionally surprised by where it shows up. The more I learned, the more I started seeing it everywhere: in the recommendations your music app makes, in the route your navigation app suggests, in the way an e-commerce site seems to know exactly what you were thinking of buying.</p><p>Some of it is designed to serve you. Some of it is designed to serve a business. But all of it is built on the same foundation: finding patterns in data, learning from them, and using that knowledge to make a prediction or a decision.</p><p>That is data science. Not a server room. Not a mysterious black box. Just a very deliberate way of paying attention to the world.</p><p>I am curious about you, though. Now that you know a little more about what is happening behind the scenes, is there something you use every day that you are surprised has data science behind it? Drop it in the comments. I would genuinely love to know.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b1eb31c9d202" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>