The pipeline
ran at 3 a.m.
nobody noticed. — that's the goal.

The Challenge

Customer Success was spending two full days every week assembling a recurring KPI report — pulling SQL exports, copying values into Excel templates, refreshing charts manually, then re-typing summaries into a Word doc and exporting to PDF. The same humans were doing the same clicks every Monday.

The Approach

Designed a parameterized Python pipeline that reads customer config from a control table, runs templated Snowflake queries, populates an Excel workbook (preserving formatting and pivot logic), and injects the results plus auto-generated narrative into a Word template via python-docx. Final step exports to PDF for leadership distribution.

Technical Details

• Parameterized SQL with config-driven date windows and tenant filters
• openpyxl for chart-preserving Excel population
• python-docx with bookmarked placeholder injection
• Reusable across 30+ customers with no code changes
• Scheduled via cron with email distribution on success

The Challenge

The Sales and Customer Success teams couldn't quantify product value at renewal conversations. Each rep was inventing their own ROI math, leading to inconsistent numbers across customers — and no defensible framework when leadership asked "what's the upsell story?"

The Approach

Built a unified Snowflake-backed ROI model that quantifies four pillars: intake completion lift, scheduling adoption, reminder effectiveness (no-show reduction), and engagement uplift. Each pillar maps to specific time and revenue assumptions configurable per customer segment.

Technical Details

• Per-customer baseline measurement (pre-product window)
• Configurable assumption layer for time/cost values
• Versioned metric definitions in a governance table
• Excel deliverable with executive summary and detail tabs
• Used by Sales, CS, and Product strategy teams

The Challenge

Multiple customers, shared infrastructure, divergent needs — and a warehouse that had grown organically into a tangle of one-off views. Cross-tenant benchmarking was unsafe, performance was inconsistent, and onboarding a new customer required custom code rather than configuration.

The Approach

Redesigned as a layered architecture: staging mirrors source systems, intermediate handles business logic and conformed dimensions, marts expose tenant-isolated semantic models. SCD Type 2 captures historical attribution. Tenant ID propagates through every fact for safe row-level isolation.

Technical Details

• Three-layer architecture with clear contracts between layers
• SCD Type 2 with effective_from / effective_to / is_current
• Conformed dimensions shared across tenants
• Row-access policies for tenant isolation
• Foundation for 60+ multi-tenant dashboards

The Challenge

Daily pipelines occasionally failed mid-run or produced duplicates after partial recovery. Engineers had to manually clean state before reruns, and late-arriving source records silently corrupted aggregates. Trust in the data was eroding.

The Approach

Designed an incremental framework around three principles: deterministic watermark tracking, merge-based upserts (never plain inserts), and explicit validation gates. Every run logs to a control table; every failure can be safely retried without manual cleanup.

Technical Details

• Watermark column tracked per source-target pair
• MERGE statements with deterministic match keys
• Validation queries for duplicates and gaps
• Late-arriving record detection & backfill logic
• Control tables track state for surgical recovery

The Challenge

CRM said one number of active contracts. Billing said another. The operational system said a third. Finance was reconciling these by hand each month-end, and discrepancies often weren't surfaced until they had compounded across periods.

The Approach

Built a reconciliation layer that joins all three systems on canonical entity keys and applies deterministic mismatch rules. Every mismatch is logged with a category, severity, and recommended action. Daily snapshots track drift over time, with exception reports surfaced to operations.

Technical Details

• Canonical entity resolution across three systems
• Deterministic mismatch classification rules
• Daily snapshot fact for drift monitoring
• Severity scoring and routing logic
• Structured exception views for operations

The Challenge

Snowflake credit consumption was growing faster than usage justified. A single oversized warehouse was running every workload, ad-hoc queries were doing full table scans, and no one had visibility into which queries were the actual cost drivers.

The Approach

Mined query history and warehouse metering to attribute cost by workload. Split into purpose-sized warehouses (small for BI, medium for ETL, X-small for ad hoc), enforced auto-suspend, and rewrote the top expensive queries to use clustering and pruning. Built an internal cost dashboard for ongoing visibility.

Technical Details

• Query history mining & cost attribution
• Workload segmentation across multiple warehouses
• Auto-suspend tuning per workload pattern
• Clustering keys and pruning for top queries
• Ongoing cost monitoring dashboard

The Challenge

Visit lifecycle data lived in a noisy event stream from the UrgentIQ API. Some statuses were skipped, some were emitted out of order, and timestamps were UTC while reporting expected EST. Operations couldn't answer basic questions like "how long are visits stuck in checked-in status?"

The Approach

Built an incremental ingestion pipeline that normalizes status timelines (1 → N), gap-fills missing transitions with deterministic rules, standardizes timezone, and computes transition durations. Output is a clean lifecycle fact ready for funnel and bottleneck analysis.

Technical Details

• API pagination & incremental ingestion
• Status timeline gap-filling logic
• UTC → EST timestamp normalization
• Transition duration computation per status pair
• Deduplication & configurable schema mapping

The Challenge

Customers were billed based on provider counts, but "provider" meant different things in different systems. Some part-time providers were counted as full FTEs, some full-time providers were missed entirely, and contract terms specified thresholds nobody was actively checking.

The Approach

Designed a reconciliation workflow that joins operational provider activity with CRM contract terms and QuickBooks billing records. Applied FTE thresholds (active hours, encounter volume) to classify each provider into a billing category. Standardized monthly reconciliation checkpoints.

Technical Details

• Three-system join: ops, CRM, billing
• FTE threshold logic with multiple inputs
• Provider classification into billing tiers
• Monthly reconciliation checkpoint cadence
• Exception reports for review by finance

The Challenge

SMS costs were inflated by two avoidable issues: messages tipping over the 160-character single-segment threshold (doubling per-message cost) and a fleet of 300+ phone numbers that were billed monthly but no longer in use. Nobody had audited either in years.

The Approach

Audited message content across templates, identified those tipping over the segment boundary, and rewrote them under a 150-character target with a margin for personalization variables. Cross-referenced active phone numbers against actual outbound traffic and decommissioned the inactive ones.

Technical Details

• Segment count analysis across all templates
• ~150-character optimization threshold
• Number-to-traffic cross reference
• 300+ inactive numbers identified & disabled
• Standardized template review process

The Challenge

EMR vendor API costs were rising without a clear understanding of what was driving usage. Were certain endpoints inefficient? Were features double-fetching data? Engineering needed evidence before making architectural changes.

The Approach

Mined six months of API logs to surface usage patterns: top endpoints by volume, redundant call chains, customer-level cost attribution, and timing correlations with downstream features. Output was a structured report engineering used to prioritize caching and batching work.

Technical Details

• Six months of log analysis
• Endpoint volume and cost ranking
• Redundant call chain detection
• Customer-level cost attribution
• Optimization recommendations to engineering

The Challenge

Hundreds of business-critical views had grown over years with no tests, no documentation, and no consistent style. Logic was duplicated across views, dependencies were implicit, and a change in one place could quietly break a dashboard halfway across the warehouse.

The Approach

Migrated transformations to dbt with a layered staging → intermediate → mart structure. Added schema tests for uniqueness, referential integrity, and accepted values. Generated docs auto-publish on every merge. CI runs the full test suite before any change reaches production.

Technical Details

• Layered model architecture with explicit DAG
• Schema tests, custom tests, and freshness checks
• Macros for reused logic (tenant filters, SCD merges)
• Auto-generated docs with column-level lineage
• GitHub Actions CI for slim builds on PRs

The Challenge

Operational teams needed visibility into events as they happened — not 24 hours later. The existing batch pipeline ran nightly, which meant by the time an issue showed up on the dashboard, the day was over. Switching to streaming meant rethinking watermarks, ordering, and failure recovery.

The Approach

Built a Kafka-to-Snowflake pipeline using Snowpipe Streaming for sub-minute landing, then Snowflake Streams + Tasks for incremental transformation. Out-of-order events handled via watermark tolerance windows. Quality metrics (lag, dropped events, schema drift) emitted to a monitoring dashboard.

Technical Details

• Snowpipe Streaming with row-level latency under 60s
• Streams + Tasks for incremental aggregation
• Watermark tolerance for out-of-order events
• Schema evolution handling without pipeline restart
• Lag, drop, and drift metrics surfaced to ops

The Challenge

Case analysis and note generation are only as trustworthy as the records they retrieve from. Stale case history, duplicate or contradictory CRM entries, and telemetry that disagrees with what a case note says all silently corrupt LLM output downstream — with no easy way to detect the failure after the fact.

The Approach

Built the retrieval layer on Databricks over CRM case history and telemetry data, powering case analysis and a case-note generator. A quality framework runs on the source records before embedding: freshness checks (last-updated thresholds), deduplication (semantic similarity clustering), contradiction detection across records, and provenance tagging. Records that fail are quarantined; the rest enter the vector index with quality metadata attached.

Technical Details

• Record freshness scoring with configurable thresholds
• Semantic dedup via embedding similarity clusters
• Cross-record contradiction detection (case history vs. telemetry)
• Provenance metadata propagated to retrieval layer
• Retrieval feeds case analysis and case-note generation
• Quarantine queue for human review

The Challenge

The Siebel CRM reporting view Commercial Ops relied on lagged live case activity by several hours. New cases, customer replies, and assignments all sat invisible until someone happened to refresh a dashboard — there was no push notification layer on top of a CRM never built for one.

The Approach

Built an hourly polling job against the lagging view that diffs against a last-seen cursor to detect genuinely new activity by type — new case, customer reply, or assignment — then posts formatted alerts to the right Slack channel. The job is designed around the view's inherent staleness rather than fighting it.

Technical Details

• Last-seen cursor prevents duplicate alerts across runs
• Classifies activity into new case / reply / assignment before routing
• Slack webhook integration with channel-specific formatting
• Tolerant of the CRM's multi-hour reporting lag by design
• Runs hourly on a schedule, no manual trigger required

The Challenge

Support analysts needed to diagnose why a vehicle wasn't reporting telemetry, but root causes ranged from a simple connectivity gap to a multi-system fault. Running an LLM against every case was slow, costly, and unnecessary for the majority that a straightforward rule check could already explain.

The Approach

Structured the notebook as three escalating tiers: Tier 1 runs fast, deterministic SQL checks against the most common known failure patterns; Tier 2 adds cross-table correlation for less obvious cases; Tier 3 — AI-generated case notes — only fires for cases that survive both earlier tiers unresolved, summarizing the diagnostic trail for the analyst instead of leaving them to piece it together.

Technical Details

• Tier 1: deterministic SQL rule checks for common failure signatures
• Tier 2: cross-table correlation for subtler fault patterns
• Tier 3: LLM-generated case notes, gated to unresolved cases only
• Diagnostic trail logged per case for auditability
• AI cost scoped to the minority of cases that actually need it

The Challenge

Analysts had no consolidated, real-time answer to three questions: what needs a response now, what's gone stale without follow-up, and what's the full state of everything still open. The data was all in the CRM — it just wasn't shaped for triage, and auto-acknowledgment emails made "needs response" counts noisy.

The Approach

A pure-SQL notebook driven by a single :analyst_name parameter, cloned per analyst for isolated execution state. A UNION bridges current case status with full historical activity, and ROW_NUMBER / QUALIFY isolate the most recent activity per case without subquery nesting. Auto-acks are filtered by treating a short-window inbound right after an outbound as system-generated, and pure-SQL weekday arithmetic powers business-day stale-follow-up flags with no UDF or calendar table.

Technical Details

• Single string parameter makes the notebook portable across analysts
• QUALIFY replaces nested subqueries for "most recent per case"
• Auto-acknowledgment filtering keeps "needs response" counts honest
• Pure-SQL business-day arithmetic, no UDFs or calendar table
• Inline CASE expressions derive urgency tiers automatically