Data Transformation and Processing Logic Questions

Direct answer

Model the loyalty-points rule as two genuinely different outcomes depending on WHEN the return happens relative to the purchase: a same-calendar-month return proportionally revokes points immediately, while a cross-month return leaves the points pending rather than auto-revoking them, and both need to correctly handle partial-quantity returns and multiple line items per order.

Structured elaboration

1. Same-month proportional revocation: if a return's month matches the original purchase's month, reduce the points earned for that line item proportionally to the quantity returned (points * (1 - qty_returned / qty)), not simply zero them out, since a partial return of 1-of-2 units should only revoke half the points.
2. Cross-month pending, not auto-revoked: if the return happens in a later month, the rule explicitly does NOT auto-revoke; the points stay pending until a manual verification step, this is a deliberate business decision (perhaps to avoid clawing back points a customer has already redeemed, without a review), not an oversight to "fix."
3. Multiple line items and partial quantities: the calculation needs to key on (order, item), since one order line might be fully returned while another line on the same order is untouched.
4. Net points per user per month: summing across all of a user's purchases and their applicable returns for the reporting month produces the final net figure.

Worked example

SELECT p.user_id, DATE_TRUNC('month', p.purchase_date) AS month,
  SUM(
    CASE
      WHEN r.return_date IS NOT NULL AND DATE_TRUNC('month', r.return_date) = DATE_TRUNC('month', p.purchase_date)
        THEN p.points * (1 - r.qty_returned::FLOAT / p.qty)
      WHEN r.return_date IS NOT NULL
        THEN p.points  -- cross-month return: pending, not auto-revoked
      ELSE p.points
    END
  ) AS net_points
FROM purchases p LEFT JOIN returns r ON p.user_id=r.user_id AND p.item_id=r.item_id
GROUP BY p.user_id, month;

Verified against two line items in the same order: item 100 (2 units, 10 points) with 1 unit returned in the SAME month, correctly nets to 5 points (half revoked); item 101 (1 unit, 5 points) returned in the FOLLOWING month, correctly keeps its full 5 points as pending rather than revoking them. Net points for the purchase month: 10 (5 + 5), matching the expected combination of the two rules.

Trade-offs and pitfalls

• The single most common mistake here is treating any return as an immediate, full revocation; both the proportional and the month-boundary distinction need to be explicit in the query, not assumed.
• "Pending until manual verification" implies there's a downstream process (a queue, a flag) that surfaces cross-month returns for a human to actually review and decide on, this query only computes the accounting state, it doesn't replace that workflow.
• Multi-line-item orders where returns key on (order, item) rather than just (order) need a join condition specific enough to avoid crediting a return against the wrong line item when an order has two units of the same item at different line-level attributes (e.g. different price at time of sale).

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.

Direct answer

Extract the nested scalar fields with a JSON path expression into named columns, and explode (unnest) any array field into one output row per array element, producing a flat table with the scalar fields repeated across each element's row; this single pattern is reported as the most common first task in real data-engineering work.

Structured elaboration

1. Scalar nested fields: a JSON path expression ($.user.id, $.product.id) pulls a nested scalar directly into a named top-level column.
2. Array fields need UNNEST, not just extraction: a nested array can't become a single column value in a flat table without losing information (or awkwardly encoding a list into one cell); UNNEST turns each array element into its own row, with the surrounding scalar fields repeated on every one of those rows.
3. The flatten-timing trade-off: doing this extraction at ingestion time produces a fixed, fast-to-query schema but requires a migration whenever the nested structure changes; parsing the JSON at query time is more flexible (no rewrite needed when new nested fields appear) but costs more per query and doesn't benefit as much from columnar storage/compression.
4. A deeper worked case: computing something like daily active users by country and daily revenue by category directly from a deeply nested payload (a user object with attributes, a products array with prices) requires flattening BOTH the scalar user attributes and exploding the products array in the same query, then aggregating on the now-flat result.

Worked example

SELECT event_id,
  json_extract_string(raw_json, '$.user.id') AS user_id,
  json_extract_string(raw_json, '$.product.id') AS product_id,
  UNNEST(actions) AS action  -- one row per element of the actions array
FROM raw_events;

Verified against an event whose actions array has 2 elements (view, click): this correctly produces 2 output rows for that one event, with user_id and product_id repeated identically on both, and the action field distinct per row, exactly the explode semantics the question requires.

Trade-offs and pitfalls

• An array field that is EMPTY (zero elements) disappears entirely from the output with a plain UNNEST (an inner-join-like behavior); if downstream code expects to see the parent record at all even when its array is empty, use the "outer" variant of unnest (explode_outer in Spark, UNNEST with an explicit outer-join equivalent elsewhere) so the record survives with a NULL element instead of vanishing.
• Flattening at ingestion time locks in a schema that a later nested-field addition will require a migration to accommodate; flattening at query time avoids that migration but repeats the parsing cost on every single query, including ones that never touch the new field.
• A deeply nested payload with several arrays at different levels can produce a combinatorial explosion of rows if multiple arrays are exploded in the same query without care (a cross-product of array elements rather than the intended pairing); be deliberate about which arrays actually need to be exploded together versus separately.

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

Pivot the category values into columns using conditional aggregation (SUM(CASE WHEN category = 'X' THEN revenue ELSE 0 END) per category), which correctly zero-fills a category that had no sales in a given month rather than leaving it absent from the result entirely.

Structured elaboration

1. Conditional aggregation as the portable pivot mechanism: not every SQL dialect has a native PIVOT operator, but every dialect supports CASE WHEN inside an aggregate, which is why this pattern is the more broadly portable choice, at the cost of needing to know the category values up front (baked into the query) rather than pivoting dynamically.
2. Zero vs NULL for a missing category-month combination: SUM(CASE WHEN ... THEN revenue ELSE 0 END) naturally produces 0 for a category with no matching rows in a given month, which is usually the right answer for a revenue pivot (no sales means zero revenue, not an unknown value).
3. Portability across dialects: engines that DO have a native PIVOT syntax (SQL Server, Snowflake) offer more concise syntax for the same result, but the conditional-aggregation form works everywhere and is the safer default to reach for when the target dialect isn't fixed yet.

Worked example

SELECT DATE_TRUNC('month', sale_date) AS month,
  SUM(CASE WHEN category='A' THEN revenue ELSE 0 END) AS cat_a,
  SUM(CASE WHEN category='B' THEN revenue ELSE 0 END) AS cat_b
FROM psales GROUP BY month;

Verified against sales of 100 (category A) and 50 (category B) in January, and 80 (category A only) in February: January correctly shows cat_a=100, cat_b=50; February correctly shows cat_a=80, cat_b=0, not NULL and not a missing row, confirming category B is zero-filled for the month it had no sales.

Trade-offs and pitfalls

• This form requires the set of category values to be known and written into the query; a genuinely dynamic set of categories (one that changes over time) needs either a code-generated query (building the CASE expressions programmatically before execution) or a different tool (a BI layer's native pivot feature) that can pivot on values discovered at query time.
• A high-cardinality column pivoted this way (thousands of distinct values) produces thousands of columns, which most engines and most downstream consumers handle poorly; conditional-aggregation pivoting is best suited to a bounded, known, moderate-cardinality set of pivot values.
• Portability comes at a small verbosity cost (one CASE expression per pivoted value, versus one line for a native PIVOT); for a fixed, small number of categories, this cost is trivial and the portability is usually worth it.