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

RAG systems are only as trustworthy as the documents they retrieve from. Stale clinical guidelines, contradictory sources, and ambiguous procedure documentation all silently corrupt LLM answers downstream — with no easy way to detect the failure after the fact.

The Approach

Designed a quality framework that runs on the source corpus before embedding: freshness checks (last-updated thresholds), deduplication (semantic similarity clustering), contradiction detection (cross-document NLI scoring), and provenance tagging. Documents that fail are quarantined; the rest enter the vector index with quality metadata attached.

Technical Details

• Document freshness scoring with configurable thresholds
• Semantic dedup via embedding similarity clusters
• Cross-document contradiction detection
• Provenance metadata propagated to retrieval layer
• Quarantine queue for human review