Technology Evaluation and Vendor Management Questions

Situation: At my previous company I was the BI lead building executive dashboards in Tableau that sourced data from a third‑party customer analytics vendor’s hosted data warehouse. Six weeks after rollout the vendor’s ETL started failing intermittently, causing delayed and incorrect metrics in executive dashboards used for daily ops.

Task: I needed to detect the problem quickly, protect business users and data integrity, restore reliable reporting, and coordinate a remediation path with the vendor and internal stakeholders.

Action:

• Discovery: I noticed diverging KPI trends and timestamp gaps during daily validation checks, and set up a quick SQL health query that showed missing rows and late loads from the vendor table. I escalated to engineering and the vendor with concrete evidence (query results, timestamps, dashboard screenshots).
• Immediate mitigations:
  • Switched affected dashboards to a cached snapshot version and added a clear banner noting data is delayed.
  • Created a temporary backfill script that pulled raw CSV exports from the vendor’s API and loaded them into our internal staging DB to correct critical KPIs for the executive report.
  • Implemented automated alerting (Slack + email) for any ETL delay beyond 30 minutes so ops would know in real time.
• Communication:
  • Sent a concise incident summary to stakeholders within 90 minutes outlining impact, mitigations, and expected timeline.
  • Ran daily status updates until resolution and a postmortem review with product, engineering, and vendor teams.

Result / Long-term decisions:

• The vendor fixed their ETL, but we decided to reduce single‑vendor risk: built a resilient ingestion layer in our cloud (Airflow + S3 intermediate storage) so we could accept both vendor pushes and our pull-based backfills.
• We added SLA clauses and runbook requirements to the vendor contract, and instituted daily automated sanity checks on critical tables.
• Impact: dashboard downtime reduced from multi-hour outages to under 15 minutes in later incidents; stakeholder confidence improved because of transparent communication and faster remediation.

Lessons:

• Accept that vendor tooling accelerates delivery but requires defensive architecture: build intermediate controls, monitoring, and legal SLAs.
• Proactive validation and clear, timely communication are as important as technical fixes when protecting business decisions.

A star schema has a central fact table (measures, foreign keys) directly connected to denormalized dimension tables (flat attributes). A snowflake schema normalizes dimensions into multiple related tables (e.g., city → state → country), so dimension data is split across tables.

When to choose:

• Star: preferred for BI/semantic layers and dashboards because it simplifies SQL, power tools (Tableau/Power BI) can auto-aggregate, and analysts find it intuitive. Use when query performance and ease-of-use matter and when dimensional redundancy is manageable.
• Snowflake: choose when data governance, storage limits, or strict normalization rules matter (avoid duplicated dimension attributes), or when many-to-one hierarchical attributes are frequently maintained separately.

Impacts:

• Query performance: Star usually faster for read-heavy analytical queries because fewer joins; snowflake can be slower due to extra joins but may benefit from smaller, more selective joins and normalization in some engines.
• Data redundancy: Star increases redundancy in dimensions (more storage, possible update anomalies); snowflake reduces duplication.
• Ease of use for analysts: Star is simpler to understand and model in semantic layers—easier for non-technical users to slice/dice. Snowflake adds complexity (more joins, deeper hierarchies).
• Maintenance costs: Star often cheaper to maintain for reporting (simpler ETL, fewer tables), but higher storage and potential data consistency work. Snowflake increases ETL complexity and maintenance effort for joins and schema changes but can reduce upstream duplication and enforce clearer data lineage.

Best practice: build star schemas in the semantic layer for performance and usability; use snowflaking selectively where normalization provides clear governance or avoids large-scale duplication.

Requirements (clarify): 500M rows ≈1TB historical events to cloud warehouse (Snowflake/Redshift). Target: minimal downtime, reliable validation, predictable cost.

High-level approach:

• Export → Stage → Load in parallel → Validate → Cutover.

Staging:

• Export compressed Parquet/CSV files partitioned by date (daily or weekly) and by event_type if available. Store in cloud object storage (S3/GCS/Azure Blob). Use columnar Parquet for smaller transfer and faster load (especially Snowflake).

Batching & parallelization:

• Create batches by date-range (e.g., 7–30 day files) sized ~100–500MB compressed; that gives ~2k–10k files.
• Use multiple parallel workers (AWS Glue/DMS, flink, or custom multi-threaded ETL) to upload files to S3 and invoke COPY/LOAD in parallel per file/partition. For Snowflake, use many small files and multiple concurrent COPY commands with auto-scaling warehouses.
• Throughput estimate: with 1 Gbps network and 8 parallel loaders you can move ~300–800 GB/hr; adjust concurrency based on observed bandwidth and target window.

Cutover window & downtime:

• Bulk historical load runs in advance (no downtime). For cutover, sync incremental changes since export with CDC (DMS or transactional log tailing). Two-phase cutover:
  1. Initial full load (zero downtime).
  2. Near-cutover: enable CDC and backfill delta until lag <1–5 min.
  3. Short maintenance window (15–60 minutes) to pause writes to source or accept dual-write for final delta, run final reconciliation, and switch BI consumers to new warehouse.
• Estimated downtime: ideally 15–60 minutes if source can pause writes; if not, plan for a rolling validation and accept sub-minute switchover with dual-writing.

Validation approach:

• Row counts per partition, checksums/hash of key columns, sample joins between source and target for high-impact partitions.
• Automate reconciliation: compare aggregates (counts, sums) per day/event_type; run BI queries on sample dashboards to compare key KPIs.
• Record and surface discrepancies; backfill missing partitions if needed.

Cost vs throughput trade-offs:

• Higher concurrency reduces migration time but increases compute and storage costs (more loaders, bigger cloud warehouse). Use spot instances/burst compute for staging to save cost.
• Use compression and Parquet to reduce storage/transfer cost.
• For Snowflake: scale up warehouses during load, then scale down. For Redshift: use elastic resize or load into transient cluster then snapshot into production.

Minimizing downtime:

• Full bulk before cutover.
• Use CDC to minimize final delta.
• Use feature flags and DNS/BI connection string swap to flip consumers quickly.
• Pre-warm warehouse and run smoke tests before swap.

Deliverables & timeline (example):

• Prep & export: 1–2 days
• Stage upload & full load: 1–3 days (parallel speed dependent)
• CDC backfill & validation: 1 day
• Cutover & smoke tests: 15–60 minutes

This plan balances BI needs for data accuracy, predictable cutover, and cost-controlled throughput while keeping downtime minimal.

Approach: Use incremental watermarking on source event timestamp + change hash to detect changed rows. Apply micro-batch merges to a staging table, then perform SCD Type 1 (overwrite current attributes) and Type 2 (insert new version rows) using idempotent, ordered upserts. Minimize locks by using small transactions, staging tables, and partitioned/clustered target tables. Handle late-arriving data via backfill windows and reprocessing using watermark lag.

Steps (high-level):

1. Watermarks:
   • last_watermark = max_processed_event_ts
   • fetch new rows where event_ts > last_watermark - allowed_lag
2. Extract & compute change detection:
   • compute row_hash = sha256(concat(cols_for_change))
3. Load into staging table (stg_fact) with source_ts, row_hash, ingested_at
4. Deduplicate staging by (business_key, source_ts) keeping latest
5. Merge into target_fact using ordered merge: first handle Type 2, then Type 1

Pseudocode (SQL-like):

-- 1. insert new rows into staging
INSERT INTO stg_fact (...) SELECT ... FROM source WHERE event_ts > :last_watermark - :lag;

-- 2. dedupe in staging keeping max event_ts per business_key
WITH dedup AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY event_ts DESC, source_id DESC) rn
  FROM stg_fact WHERE batch_id = :batch
)
-- 3. MERGE for SCD Type 2 (versioning)
MERGE INTO fact_dim t
USING (SELECT * FROM dedup WHERE rn=1) s
ON t.business_key = s.business_key AND t.current = TRUE
WHEN MATCHED AND s.row_hash != t.row_hash AND (s.scd2_attr IS DISTINCT FROM t.scd2_attr) THEN
  -- expire old
  UPDATE SET current = FALSE, end_ts = s.event_ts;
WHEN NOT MATCHED AND s.scd2_attr IS NOT NULL THEN
  INSERT (business_key, scd2_attr, scd1_attr, start_ts, end_ts, current, row_hash)
  VALUES (s.business_key, s.scd2_attr, s.scd1_attr, s.event_ts, NULL, TRUE, s.row_hash);

-- 4. MERGE for SCD Type 1 (overwrite non-versioned attributes)
MERGE INTO fact_dim t
USING (SELECT * FROM dedup WHERE rn=1) s
ON t.business_key = s.business_key
WHEN MATCHED AND s.row_hash != t.row_hash THEN
  UPDATE SET scd1_attr = s.scd1_attr, row_hash = s.row_hash, last_updated = s.event_ts;

Ordering guarantees:

• Use event_ts + source_seq (tie-breaker) and WINDOW functions to pick latest per business_key.
• Apply merges in a single transaction per micro-batch to preserve atomicity but keep batches small (e.g., 5–15 minutes or N rows) to minimize locks.

Python orchestration sketch:

last_watermark = get_watermark()
batch = extract(event_ts > last_watermark - lag)
compute_hashes(batch)
write_staging(batch)
run_sql_merges(batch_id)
if success: update_watermark(max(batch.event_ts))

Late-arriving data:

• Keep watermark lag (e.g., 24–72 hours) to capture typical latency.
• Support targeted replays/backfills by running backfill jobs for specific event_ts ranges; mark processed ranges and use reprocessing idempotency via row_hash and batch_id.
• For very late corrections, allow isolated backfill that updates historical SCD2 rows (split/adjust start/end_ts) and triggers downstream metric re-computation where necessary.

Notes / Best practices:

• Partition target by event date for faster targeted updates and smaller locks.
• Use optimistic concurrency (row_version) and short transactions.
• Maintain audit/log tables: batch_id, source_min_ts, source_max_ts, rows_processed, errors.
• Test replays and backfills on a copy to validate SCD transitions and report recomputation impact.

Approach: keep metric SQL definitions in metrics_definitions(name, version, definition_sql, effective_from). Provide a stored function that, given name and version, safely retrieves the definition_sql and executes it (dynamic SQL) returning the metric resultset. For consumers who want always-latest, provide a view that resolves to the highest effective version. For scale/latency, support materialized (backfilled) metric table per metric+version.

Example (Postgres) — stored procedure that executes a metric definition and returns a setof record. Use json/params pattern for safety.

-- returns json resultset for arbitrary metric definition
CREATE OR REPLACE FUNCTION run_metric(p_name text, p_version int DEFAULT NULL)
RETURNS TABLE(result json) AS $$
DECLARE
  def_sql text;
  v_version int;
BEGIN
  -- choose requested version or latest <= today
  IF p_version IS NULL THEN
    SELECT version INTO v_version
    FROM metrics_definitions
    WHERE name = p_name AND effective_from <= current_date
    ORDER BY version DESC LIMIT 1;
  ELSE
    v_version := p_version;
  END IF;

  IF v_version IS NULL THEN
    RAISE EXCEPTION 'No metric definition for %', p_name;
  END IF;

  SELECT definition_sql INTO def_sql
  FROM metrics_definitions
  WHERE name = p_name AND version = v_version;

  -- optional: validate SQL pattern, prevent dangerous statements (DDL)
  IF def_sql IS NULL THEN
    RAISE EXCEPTION 'Definition not found for % v%', p_name, v_version;
  END IF;

  -- execute and return rows as json for flexible BI consumption
  RETURN QUERY EXECUTE format('SELECT to_jsonb(t) FROM (%s) t', def_sql);
END;
$$ LANGUAGE plpgsql;

Latest-version view:

CREATE OR REPLACE VIEW metric_latest AS
SELECT name, version, effective_from
FROM metrics_definitions d
WHERE (name, version) IN (
  SELECT name, max(version) FROM metrics_definitions WHERE effective_from <= current_date GROUP BY name
);

Handling version retirement and backfill:

• Retirement: add columns retired_at/date or active boolean in metrics_definitions; consumers can still request retired versions by explicit version number, but latest view excludes retired ones. Marking retired prevents accidental use in dashboards.
• Backfill: compute historical metric values by running run_metric for each historical partition (date/month) and persist into a materialized table metrics_materialized(name, version, period_start, period_end, payload json, computed_at). Schedule a backfill job (Airflow/cron) that:
  • Locks the metric/version during backfill
  • Runs the metric SQL with WHERE clauses for partitioned time ranges (encourage definition_sql to accept a parameter placeholder like {{period_start}}) or use templating.
• Best practices:
  • Encourage paramized definitions (e.g., WITH params AS (...) SELECT ... WHERE event_date BETWEEN params.start AND params.end)
  • Validate/scan definition_sql for unsafe keywords before storing or executing
  • Use materialized tables for heavy metrics to reduce dashboard latency; refresh policies: incremental daily or on-demand.
• Edge cases: missing version, incompatible schema changes between versions (store schema contract or return json), long-running SQL (use query timeouts), multi-tenant sanitization.

This pattern lets BI teams request metrics by stable version for reproducibility, retire old definitions safely, and run controlled backfills to populate historical metric stores for analysis and dashboards.