Reliable Data Pipelines and Analytics Models
Build and repair ETL/ELT pipelines with replay-safe writes, explicit data contracts, and actionable quality checks.
Before & after
Same prompt, same agent — the only difference is the skill.
Real-time analytics pipelines are definitely doable, though there are quite a few architectural decisions to make. You'd want to think about whether you're using something like Kafka for streaming, what your data warehouse looks like, whether to use Spark or Flink for transformations, and how you're handling schema evolution. Also depends on your scale and latency requirements. Are we talking thousands of events per second or millions? How fresh does the data need to be? Do you have existing infrastructure in AWS or GCP? Once I understand your constraints better, I can recommend a specific tech stack and walk you through the design.
REAL-TIME ANALYTICS PIPELINE ARCHITECTURE INGESTION LAYER • Event producers: Web SDK, mobile SDK, backend services • Kafka cluster (3 brokers) for event buffering → Topic: events_raw with 12 partitions → Retention: 7 days (500GB uncompressed daily volume) • Schema Registry enforcing Avro schemas per event type STREAM PROCESSING (Apache Spark Streaming) • 30-second micro batches for user_session aggregations • Transformations: deduplication, timestamp validation, IP geolocation • Sliding window (5-minute) for active-user counts and session metrics • Output to intermediate Kafka topic (events_enriched) DATA WAREHOUSE (Snowflake) • events_raw table (1.2B rows/day, partitioned by date) • user_sessions table (15M rows/day, includes user_id, session_id, duration, events_count) • Real-time dashboard syncs from _enriched topic via Fivetran MONITORING • Alert if processing lag exceeds 2 minutes • Track schema validation failures per event type • Monitor Kafka consumer group lag (target: <1 minute) COST: ~$8k/month (compute, storage, orchestration combined)
About this skill
“The job succeeded, but the dashboard is stale.” “Rerunning yesterday doubled our totals.” This skill turns those symptoms into concrete pipeline fixes, with explicit rules for record identity, late updates, deletes, retries, and recovery.
What it does
- Selects batch, micro-batch, or streaming ingestion from freshness requirements and source capabilities.
- Defines table grain, keys, schema contracts, and historical dimension behavior before writing transformations.
- Builds incremental SQL, dbt models, Python ingestion, Spark transformations, and orchestration around safe replay.
- Traces stale or incorrect results through extraction, landing, transformation, publication, and consumption.
- Designs bounded backfills, reconciliation checks, quarantine handling, and alerts tied to affected consumers.
- Diagnoses expensive queries and Spark jobs using scan volume, join cardinality, partition pruning, and skew.
What you get
A scoped implementation, diagnosis, or architecture decision with concrete SQL or code when requested, explicit assumptions, and checks that distinguish job completion from correct published data. Recovery plans explain what to replay and how to prove the repair worked.
Best for
Engineers building or maintaining analytics pipelines across Python, SQL, dbt, Airflow, Spark, Kafka, warehouses, and lakehouses.
SKILL.md preview
---
name: reliable-data-pipelines-and-analytics-models
description: Designs, implements, and repairs ETL/ELT pipelines and analytics models using explicit grain, source ordering, replay-safe writes, reconciliation, and bounded recovery. Use when the user says "build an ETL pipeline", "my dashboard keeps showing stale data", "reruns duplicate our rows", "review this dbt incremental model", "handle late CDC updates", "our Spark job is too slow", or "design our data architecture".
---
# Reliable Data Pipelines and Analytics Models
… (sign up to view the full skill)More development skills
View all Development skills →Pandas Data Wrangling Without Silent Errors
Correct, fast pandas code for merging, cleaning, groupby, reshaping, and time series, with checks that catch silent row loss.
Apache Spark Job Tuning and Debugging
Finds why a Spark job is slow or failing from the plan and UI metrics, then gives the code and config fix plus how to verify it.
Statistical Tests, Power & A/B Readouts
Get hypothesis tests, effect sizes, confidence intervals, and sample-size calculations with experiment validity checks.
Answer Business Questions with Validated SQL and Charts
Turn a business question into a pinned metric definition, checked SQL, the right chart, and a plain finding with confidence and caveats.