SQL Dialects and Built-in Functions Questions

Clarify requirements:

• Target: ISO 8601 UTC timestamps (YYYY-MM-DDTHH:MM:SSZ)
• Preserve original value and source metadata
• Automated, auditable, configurable per-source overrides

Pipeline overview:

1. Ingest raw rows with columns: source_id, field_name, raw_value, received_at, source_timezone (if provided).
2. Apply detection + parsing module (see below).
3. Validate parsed timestamp against reasonable bounds (e.g., 1900–now+1yr).
4. Emit normalized timestamp, confidence score, original value, parsing method, and audit log entry.
5. Store failures/ambiguous in a quarantine table and a sampling dashboard for human review.

Detection & parsing heuristics (in order):

• If source provides explicit locale/format metadata → use that format (highest priority).
• If value is integer and within plausible epoch ranges → try seconds, ms, micros (based on magnitude).
• If ISO-like (contains T or starts with YYYY-) → parse as ISO.
• Pattern-match delimiters: if contains '-' assume Y-M-D or D-M-Y based on first token length; if contains '/' or '.' apply locale disambiguation heuristics:
  • Heuristic A (population): Look up source country/locale mapping.
  • Heuristic B (column-wide distribution): sample non-null values for that source+column. If >90% of values have month>12 only possible in D/M? Use frequency to infer ordering.
  • Heuristic C (business rules): if business domain implies month ranges (e.g., fiscal months), prefer that.
• If ambiguous after heuristics (e.g., many values could be both) mark as ambiguous and:
  • Apply default rule (configurable per environment, e.g., treat as MM/DD/YYYY for US-based sources).
  • Assign low confidence and route to quarantine for periodic manual review.

Fallback rules:

• If parsing fails, try alternate common formats (dmy, mdy, ymd) with increasing error tolerance.
• If timezone absent, apply source_timezone; if unknown, use organization default timezone then convert to UTC.
• For epoch-like floats/ints ambiguous between seconds/ms: choose ms if magnitude >1e10; else seconds. Log chosen assumption.

Logging & observability:

• For each row produce structured log: source_id, raw_value, parsed_iso (or null), method_used, confidence (0-1), error/reason, timestamp.
• Aggregate metrics: parse success rate by source/column, ambiguity rate, top failure reasons; alert if success rate drops below threshold.
• Store sampled ambiguous/failure records in a review UI (with ability to set per-source override).

Handling ambiguous values in production:

• Default automatic normalization with low confidence + quarantine flag.
• Downstream consumers receive normalized value plus confidence and original raw_value; dashboards show confidence filters.
• Implement feedback loop: when human resolves ambiguous pattern, update format mapping for that source and reprocess historical quarantined records.

Example parsing snippet (Python):

from dateutil import parser
def try_parse(val, fmt=None, tzinfo=None):
    try:
        if fmt:
            dt = datetime.strptime(val, fmt)
        else:
            dt = parser.parse(val, dayfirst=heuristic_dayfirst(val))
        if tzinfo:
            dt = dt.replace(tzinfo=tzinfo)
        return dt.astimezone(timezone.utc).isoformat()
    except Exception as e:
        return None

Key trade-offs:

• Conservative defaults + quarantine reduce silent corruption but require human QA.
• Heuristics using column-wide statistics scale well; keep ability to override per-source for accuracy.

Requirements:

• Detect missing or stale daily partitions for date-partitioned ETL (yesterday’s partition not present or older than SLA).
• Trigger automated retries, escalate to stakeholders if unresolved.
• Provide observability: freshness, runtime, success rate; minimize alert fatigue.

High-level design:

• Orchestrator (Airflow / Prefect) runs ETLs and exposes task metadata.
• Partition registry service: small metadata store (Postgres / BigQuery table) that records expected partitions, load_time, source_timestamp, job_run_id, status.
• Monitoring job (cron or DAG) runs hourly to evaluate freshness and metrics, writes to observability DB and time-series (Prometheus / Datadog).
• Alerting rules in Datadog/Prometheus -> incident manager (PagerDuty / Opsgenie) -> notification channels (email, Slack, ticketing).
• Retry automation: on detection, monitoring job triggers orchestrator API to re-run ETL for missing partition with backoff and max attempts; logs attempts in registry.

Metrics to track:

• Data freshness: max(source_timestamp, load_time) per partition; freshness_lag = now() - source_timestamp.
• Partition presence: boolean per expected date.
• Job runtime: histogram of run durations.
• Success rate: rolling 7/30-day success % per job.
• Retry attempts and failure escalation count.

Recommended thresholds:

• Freshness warning: lag > 1 hour (for near-real-time) or >24 hours for daily SLA.
• Freshness critical: lag > 6 hours (near-real-time) or >48 hours (daily).
• Missing partition warning: expected partition absent by 2 hours after expected window.
• Missing partition critical: still absent after 6 hours or after 3 failed retries.
• Success rate alert: < 95% over 7 days.

Avoiding alert fatigue:

• Use multi-tier alerts (warning -> critical) with clear runbooks.
• Suppress duplicates by deduping alerts per partition/job and grouping related alerts.
• Implement automated retries and only escalate after retries exhausted.
• Use rate limits and silence windows for planned maintenance; notify stakeholders proactively.
• Provide actionable alert content: job name, partition date, last load_time, retry count, link to run logs and runbook.
• Review alert thresholds quarterly based on historical metrics and business impact.

Operational best practices:

• Track SLA per data product and map stakeholders with escalation matrix.
• Provide dashboard for freshness, SLA attainment, and recent failures.
• Regular postmortems for recurring alerts; tune thresholds and automation accordingly.

Approach: Use a simplified, practical regex that (1) restricts local-part and domain to common safe characters, (2) enforces a single @, and (3) matches only domains ending in .co.uk or .com. This is not full RFC 5322 validation but is appropriate for analytics filtering.

PostgreSQL query (case-insensitive):

SELECT email
FROM customers
WHERE email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.-]+\.(co\.uk|com)$';

Explanation / key points:

• ^...$ anchors ensure full-string match.
• Local-part: [A-Za-z0-9._%+-]+ allows letters, digits, dot, underscore, percent, plus, hyphen (common safe set).
• Domain: [A-Za-z0-9.-]+ allows labels and subdomains; this simplified pattern does not enforce label-length or forbids leading/trailing hyphens/dots (trade-off for readability).
• .(co.uk|com)$ ensures only .co.uk or .com TLDs.
• ~* is PostgreSQL’s case-insensitive regex operator.

Portability:

• MySQL: Use REGEXP (MySQL 8+ supports ICU regex; older versions use POSIX). Example:
  WHERE email REGEXP '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.-]+\.(co\.uk|com)$'
  Note: In SQL strings you may need to double-escape backslashes.
• SQL Server: T-SQL has no built-in full regex match. Options:
  • Use CLR function exposing .NET Regex.IsMatch and call it from SQL Server.
  • Use LIKE for very simple patterns (cannot express the above precisely).
  • Use external processing (ETL) or apply regex in the application layer.

Trade-offs & recommendations:

• For analytics, this simplified regex is fast and filters most invalids; for strict validation (signup flows), validate with a dedicated library or send verification emails.
• Test against real data to avoid false negatives (e.g., internationalized addresses) and adjust character set as needed.

Use a year-difference with a month/day check so the age only increments after the birthday in the current year. Simple /365 is wrong because it ignores leap days and varying month lengths (people born just before/after Feb 29 will be off by 1 when leap years are involved).

Postgres (recommended — uses AGE):

-- returns integer years
SELECT EXTRACT(YEAR FROM AGE(current_date, birth_date))::INT AS age
FROM people;

BigQuery (explicit approach that works across engines):

SELECT
  DATE_DIFF(CURRENT_DATE(), birth_date, YEAR)
  - CASE
      WHEN FORMAT_DATE('%m%d', CURRENT_DATE()) < FORMAT_DATE('%m%d', birth_date) THEN 1
      ELSE 0
    END AS age
FROM dataset.people;

Handling Feb 29 edge case (common convention: treat Feb 29 birthdays as Feb 28 in non-leap years):
Postgres:

SELECT
  EXTRACT(YEAR FROM AGE(current_date,
    CASE WHEN birth_date::text LIKE '%-02-29' AND NOT (EXTRACT(ISODOW FROM (DATE_TRUNC('year', current_date) + INTERVAL '59 days')) IS NULL)
         THEN birth_date - INTERVAL '1 day'
         ELSE birth_date END
  ))::INT AS age
FROM people;

BigQuery (simpler):

SELECT
  DATE_DIFF(CURRENT_DATE(), birth_date, YEAR)
  - CASE
      WHEN FORMAT_DATE('%m%d', CURRENT_DATE()) < 
           IF(FORMAT_DATE('%m%d', birth_date) = '0229' AND NOT (EXTRACT(IS_LEAP_YEAR FROM CURRENT_DATE())), '0228', FORMAT_DATE('%m%d', birth_date))
      THEN 1 ELSE 0 END AS age
FROM dataset.people;

Example edge case: birth_date = 2000-02-29. On 2021-02-28 the expression that uses raw month/day would treat the birthday as not passed -> age = 20; if you want to consider Feb 28 as the anniversary in non-leap years, apply the Feb-29 to Feb-28 adjustment so age becomes 21 on 2021-02-28.

Situation: You need a 14-day moving average of a KPI in Tableau and must ensure it respects the viz dimensions and filters while understanding interactions with LODs.

Approach (table calculation):

1. Create the base measure if needed: SUM([KPI]).
2. Create a table-calculation:

// 14-day moving average including current day + previous 13
WINDOW_AVG(SUM([KPI]), -13, 0)

3. Put Date (continuous, DAY) on Columns and KPI/table calc on Rows. Right‑click the calc → Compute Using → choose the date dimension (or Edit Table Calculation → Specific Dimensions) and ensure dates sort ascending. This defines addressing (date) and partitioning (other dimensions like Region).

LOD interaction:

• LODs ({FIXED ...}, {INCLUDE ...}) are computed before table calculations (but after dimension filters unless filter is context).
• If you need the moving average across raw rows regardless of viz-level dimensions, pre-aggregate with an LOD, e.g.:

{FIXED [Date]: SUM([KPI])}

then run WINDOW_AVG on that LOD measure:

WINDOW_AVG([KPI by Date LOD], -13, 0)

Common pitfalls mixing LOD and table calcs:

• Order-of-operations confusion: LODs are computed before table calcs but after non-context filters. Regular filters remove rows before LODs; use Context Filter for filters that should affect LOD.
• Applying dimension filters: filtering a dimension on the view will change table calc partitioning; filtering a dimension used in a FIXED LOD will remove data unless the filter is in Context.
• Aggregation mismatch: table calcs operate on row-level results of aggregations. Using an LOD that returns aggregated values and then aggregating again can double-aggregate.
• Missing dates / data density: WINDOW_AVG over dates requires contiguous dates; gaps cause incorrect windows. Use data densification (show missing values) or scaffold dates.

Steps to ensure moving average respects viz dimensions and filters:

• Convert date to continuous and use it as addressing dimension.
• Decide whether LOD should ignore or respect certain filters. If LOD must ignore quick filters, put those filters into Context (right-click filter → Add to Context).
• If you want the MA computed at the level of specific dims (e.g., per Region), include those dims in the FIXED or partition by them in the table calc (Edit Table Calc → Partitioning).
• Validate by building a table: show Date, SUM(KPI), LOD value (if used), and the WINDOW_AVG; inspect first/last windows and edge behavior.
• Handle edge cases: for earliest 13 days you may get NULLs—use IFNULL or ZN to display appropriately.

Result: Using WINDOW_AVG with explicit Compute Using settings, combined with properly scoped LODs and context filters, yields a correct 14-day moving average that respects both visualization dimensions and intended filter behavior.