Data Transformation and Processing Logic Questions

Direct answer

An unexpected row-count increase after a join almost always means the join key is not unique on at least one side, so one row on the "one" side is fanning out against multiple matching rows on the other side. The fix is to find which side has the duplicate key values, decide whether that duplication is real data or a bug, and pre-aggregate before joining if it's real.

Structured elaboration

1. Check first: count rows in each input table, then check whether the join key is actually unique on each side (SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1). This single query almost always finds the cause.
2. Sample the fan-out: pull a few of the order_ids that have more than one matching refund row and look at them directly. Is it two legitimate partial refunds on the same order, or a genuine duplicate/bug in the refunds table?
3. Decide the fix: if the duplication is legitimate (partial refunds are a real, expected pattern), pre-aggregate the many-side table (SUM(refund_amount) GROUP BY order_id) before joining, so the join is one-to-one; if it's a bug (accidental double-insert), fix it upstream instead of working around it downstream.
4. Explaining it to a non-technical stakeholder: "each order can have more than one refund, and when we combine the two tables, each of those extra refunds adds an extra row for that order, that's why the count went up even though nothing was duplicated in the original orders."
5. A related but distinct hygiene issue: two tables can share a column name (both have an id or created_at) that means something different in each source; joining them naively can silently produce a nonsensical combined column, or a name collision that overwrites one column with the other depending on the SQL dialect's join-column handling. The fix here is renaming or namespacing columns before the join, and preserving provenance (which source each value came from) rather than letting the join silently pick one.

Worked example

Orders (10,000 rows) LEFT JOIN refunds (200 rows) on order_id, where a few orders have more than one refund row:

SELECT COUNT(*) FROM orders o LEFT JOIN refunds r ON o.order_id = r.order_id;

With orders (1,100),(2,50),(3,75) and refunds (1,20),(1,30),(2,10) (order 1 has two refund rows), the join returns 4 rows, not 3, verified by execution: order 1 fans out into 2 result rows, order 2 into 1, order 3 into 1 (with NULL refund columns) - 2+1+1 = 4. The "missing" row isn't missing at all, it's the extra refund row for order 1 that explains the count increase.

Trade-offs and pitfalls

• Pre-aggregating the many-side table before joining fixes the row-count symptom but can hide a genuine data-quality issue (duplicate refund rows that shouldn't exist); confirm which case you're in before "fixing" it.
• A COUNT(DISTINCT o.order_id) after the join will look correct even when the join is silently fanning out revenue or other summed metrics; row-count sanity checks alone don't catch every symptom of a bad join, check the aggregated business metric too.
• Renaming colliding columns (e.g. o.created_at AS order_created_at, r.created_at AS refund_created_at) before the join is cheap and prevents an entire class of silent-overwrite bugs; do it by convention, not only when a collision is noticed.

Direct answer

Handling nulls and outliers in the same numeric column requires two separate decision processes run side by side, not one: for nulls, decide impute versus drop versus flag based on the missingness mechanism; for outliers, decide flag versus drop versus cap based on statistical detection (IQR or z-score) combined with a business-rule sanity check, and document both decisions so downstream analysts know what happened to their data.

Structured elaboration

1. Statistical outlier detection: IQR-based bounds (values beyond 1.5x the interquartile range from the quartiles) are robust to the outliers themselves distorting the detection; a z-score/standard-deviation-based bound is simpler to compute but can miss outliers when several occur together (the outliers inflate the standard deviation used to judge them).
2. Business-rule thresholds as a complement, not a replacement: a purely statistical bound doesn't know that a negative age or a transaction of $10 million is implausible on its face; domain-specific hard bounds catch cases that a purely distributional method might not flag as statistically unusual in a small or skewed sample.
3. Impute vs. flag vs. drop, for BOTH nulls and outliers: dropping is simplest but costs sample size and can bias results if the dropped values aren't random; flagging (keeping the value but marking it) preserves information for downstream review; imputing/capping substitutes a value, which is only defensible when you can state what you're substituting and why.
4. Documentation and testing: whatever the decision, document it per column (which threshold, which strategy) and add tests so a later pipeline change doesn't silently alter the assumptions a report or model was built on.

Worked example

A revenue column with a few NULLs and a few outlier values: apply the null-handling decision (say, MAR-informed imputation, or an explicit "missing" flag) first, since an outlier check computed over data that still contains unhandled nulls can behave unpredictably depending on how the engine treats NULL in an IQR or standard-deviation calculation. Then apply outlier detection (IQR is the more robust default given the earlier finding that a small number of co-occurring outliers can inflate a standard-deviation-based threshold enough to mask themselves) to the now-null-free column, deciding per business context whether a flagged outlier should be capped, excluded from an aggregate, or kept with a visible flag for a human to review.

Trade-offs and pitfalls

• Running outlier detection BEFORE resolving nulls (rather than after) can produce a statistic (mean, standard deviation, quartile boundaries) computed inconsistently depending on how NULLs happen to be handled by the specific function used, do the null-handling step first.
• A z-score threshold alone can silently miss multiple co-occurring outliers precisely because they inflate the very standard deviation the threshold is based on (a concrete, verified instance of this masking effect is worked out in the window-function outlier-flagging answer); prefer IQR, or at minimum sanity-check a z-score-based result against a robust alternative.
• Capping an outlier to a boundary value (winsorizing) changes the shape of the distribution in a way a simple flag does not; state which one you did, since they have different downstream statistical implications.

Direct answer

Parse each source's timestamps with a tolerant parser that handles multiple formats, attach a timezone as soon as one can be determined (either from the string itself or a known per-source default), convert everything to UTC, and treat any date whose format is genuinely ambiguous (like 02/03/2024) as a case needing an explicit rule or a human decision, not a silent guess.

Structured elaboration

1. Tolerant parsing first: a general date/time parsing library handles the mechanical variety (ISO 8601, MM/DD/YYYY, epoch seconds, named-timezone suffixes) far more robustly than hand-written regexes for every format you've seen so far.
2. Timezone attachment: if a timestamp has no timezone information at all, you must know (from the source system, not the string) what timezone it was recorded in; silently assuming UTC for a naive timestamp that was actually recorded in local time is one of the most common and hardest-to-detect bugs in this space.
3. Ambiguous dates: 02/03/2024 is genuinely ambiguous between February 3rd and March 2nd without knowing the source's locale convention; detect this ambiguity (day and month both <=12) and either apply a known per-source rule or flag it for review, rather than picking one silently.
4. DST (daylight saving time) transitions: converting a naive local timestamp to UTC across a DST boundary can be ambiguous (the "fall back" hour occurs twice) or invalid (the "spring forward" hour doesn't exist); a timezone-aware library surfaces this rather than silently picking an arbitrary interpretation.
5. Detecting clock skew and duplicate timestamps: comparing timestamps across sources for signs of a systematically offset clock (a consistent multi-minute drift) or genuinely duplicated timestamps (device misconfiguration, at-least-once redelivery) is a useful sanity check before trusting the normalized result at all.

Worked example

from dateutil import parser as dtparser
from datetime import timezone

def normalize_ts(s):
    dt = dtparser.parse(s)
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)  # only valid if you KNOW the source is UTC; state the assumption
    return dt.astimezone(timezone.utc)

samples = ['2024-01-02 13:00', '01/02/2024 1pm PST', '2021-05-01T12:00Z']

Verified: a naive string, a PST-labeled string, and a Z-suffixed (UTC) string all correctly normalize to timezone-aware UTC datetimes; an epoch integer normalizes via datetime.fromtimestamp(epoch, tz=timezone.utc). The genuinely hard case, 02/03/2024, is not something a library resolves for you: it requires knowing (from the SOURCE, not the string) whether that source uses day-first or month-first date ordering.

Trade-offs and pitfalls

• Assuming UTC for every naive timestamp is a common shortcut that is wrong whenever a source actually recorded local time; get this wrong once and every downstream time-based computation (session gaps, daily aggregates) is silently offset.
• A tolerant parser can successfully parse a genuinely wrong date (e.g. silently picking month-first for a day-first source) without ever raising an error, this is a correctness bug, not a crash, and is much harder to catch in testing.
• Detecting clock skew (comparing a device's reported timestamp against a trusted server-side receipt time) is worth doing BEFORE normalizing formats, since a systematically skewed clock will produce plausible-looking but wrong UTC timestamps no matter how correctly the format itself was parsed.

A first analytics-engineering dbt project should mirror how data actually flows: raw closer to the source, progressively more business-meaning added as you move toward the marts consumers actually query.

Layering

1. Sources (declared in YAML, not modeled): the raw tables as they land, referenced via source() so dbt can track freshness and lineage back to the actual source system, never referenced directly by table name in downstream models.
2. Staging models (one per source table, thin): light cleanup only, renaming columns to a consistent convention, casting types, maybe basic deduplication. No business logic and no joins across sources here; a staging model's job is to make the raw data trustworthy to build on, not to answer any business question yet.
3. Intermediate models: where joins across staging models and the first layer of real business logic live (a int_orders_joined_customers combining two staging models). These aren't meant to be queried by end users, they're reusable building blocks for the marts layer.
4. Marts: the final, business-facing tables (fct_orders, dim_customers), organized by business domain, that BI tools and analysts actually query. This is the only layer that should be treated as a stable, documented interface.

Incremental vs. full-refresh, per model
Full-refresh (dbt's default table materialization, rebuilt entirely every run) is right for small dimension tables and any model where the transformation logic is still actively changing, since a full rebuild guarantees the output always matches the current model definition with no risk of the incremental logic drifting from a since-changed transform. Incremental materialization is worth the added complexity once a model is both large and stable: a fact table with years of history where reprocessing everything daily would be wasteful, and the transformation logic has settled enough that you're not constantly changing the model in ways that would require a full rebuild anyway to stay correct.

Snapshots: for source tables that don't carry their own history (an operational table that just gets overwritten in place, with no updated_at you can trust to reconstruct past states), a dbt snapshot captures the table's state on every run and builds an SCD (Slowly Changing Dimension) Type 2 style history table automatically, which is the practical way to get historical tracking out of a source that was never designed to provide it.

Example tests for critical metrics

• unique and not_null on the primary key of every mart-layer fact and dimension table, the baseline that catches a broken join before it reaches a dashboard.
• A relationships test asserting every foreign key in a fact table resolves to a real row in its dimension, catching orphaned facts.
• A custom singular test asserting a business invariant directly (e.g. sum(revenue) >= 0, or that daily row counts don't drop more than some threshold day over day), which catches the kind of silent regression a generic schema test can't.

The mistake worth naming: skipping the staging layer and joining raw source tables directly inside marts models. It looks like fewer files, but it means every downstream model has to independently know about (and stay in sync with) the raw source's quirks, and a source schema change now has to be hunted down and fixed in N places instead of one staging model.

Direct answer

NULL means "unknown," not "empty" and not "zero," and it does not behave like an ordinary value in comparisons: any direct comparison involving NULL (NULL = NULL, x = NULL) evaluates to NULL (neither true nor false), which is why WHERE x = NULL never matches anything, even rows where x actually is NULL.

Structured elaboration

1. NULL vs empty string vs zero, by type: in a string column, '' is a real, present value (zero-length text) while NULL means no value was recorded; in a numeric column, 0 is a real, present value while NULL again means unknown; in a date column there is no equivalent "empty" value at all, only NULL, which is a common source of confusion when developers try to use a sentinel date instead.
2. Pitfall 1, filtering: WHERE x = NULL silently returns zero rows; the correct form is WHERE x IS NULL.
3. Pitfall 2, aggregation: COUNT(column) skips NULLs while COUNT(*) counts every row regardless; SUM/AVG over a column with NULLs ignore the NULLs rather than treating them as zero, which changes an average's denominator in a way that is easy to miss.
4. Pitfall 3, joins: a LEFT JOIN with no match produces NULLs for every column from the right-hand table, not zeros or empty strings; code that assumes a numeric join result defaults to 0 will get NULL instead unless it explicitly wraps the column in COALESCE.
5. Handling strategies: COALESCE(x, default) to substitute a value for display or arithmetic; explicit IS NULL / IS NOT NULL checks in filters; and, where the schema allows it, deciding at write time whether "unknown" should even be representable, versus requiring a value.

Worked example

SELECT id FROM users2
WHERE email IS NULL OR TRIM(email) = '';

Against rows (1, NULL), (2, ''), (3, ' '), (4, 'a@x.com'), this correctly returns ids 1, 2, and 3 (verified): NULL, an empty string, and a whitespace-only string are three distinct representations of "no real email," and all three need to be checked explicitly, none of them is caught by checking for only one of the three.

Trade-offs and pitfalls

• COALESCE(amount, 0) is the right fix when "unknown" should be treated as zero for a sum, but the wrong fix when "unknown" should exclude the row from an average entirely (it will pull the average toward zero).
• Comparing a nullable column to another nullable column with plain = silently drops rows where either side is NULL from an equi-join; some engines offer a NULL-safe equality operator (IS NOT DISTINCT FROM in Postgres, <=> in MySQL) for the cases where NULL should be treated as matching NULL.
• Casting a NULL numeric to a string for display purposes can turn it into the literal text "None" or "null" in some client libraries if not explicitly handled, which then fails an IS NULL check silently downstream.