references/dataops_best_practices.md · 24000
iguration
# dbt_project.yml
name: 'analytics'
version: '1.0.0'
config-version: 2
profile: 'analytics'
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
target-path: "target"
clean-targets: ["target", "dbt_packages"]
# Slim CI configuration
on-run-start:
- "{{ dbt_utils.log_info('Starting dbt run') }}"
on-run-end:
- "{{ dbt_utils.log_info('dbt run complete') }}"
vars:
# CI testing with limited data
ci_limit: "{{ 1000 if target.name == 'ci' else none }}"
# Model configurations
models:
analytics:
staging:
+materialized: view
+schema: staging
intermediate:
+materialized: ephemeral
marts:
+materialized: table
+schema: marts
core:
+tags: ['core', 'daily']
marketing:
+tags: ['marketing', 'daily']
Slim CI with State Comparison
# scripts/slim_ci.sh
#!/bin/bash
set -e
# Download production manifest for state comparison
aws s3 cp s3://dbt-artifacts/prod/manifest.json ./target/prod_manifest.json
# Run only modified models and their downstream dependencies
dbt run \
--target ci \
--select state:modified+ \
--state ./target/prod_manifest.json
# Test only affected models
dbt test \
--target ci \
--select state:modified+ \
--state ./target/prod_manifest.json
# Upload CI artifacts
dbt docs generate
aws s3 sync ./target s3://dbt-artifacts/ci/${GITHUB_SHA}/
Observability and Lineage
Data Lineage with OpenLineage
# lineage/openlineage_emitter.py
from openlineage.client import OpenLineageClient
from openlineage.client.run import Run, RunEvent, RunState, Job, Dataset
from openlineage.client.facet import (
SchemaDatasetFacet,
SchemaField,
SqlJobFacet,
DataQualityMetricsInputDatasetFacet
)
from datetime import datetime
import uuid
class DataLineageEmitter:
"""Emit data lineage events to OpenLineage."""
def __init__(self, api_url: str, namespace: str = "data-platform"):
self.client = OpenLineageClient(url=api_url)
self.namespace = namespace
def emit_job_start(self, job_name: str, inputs: list, outputs: list,
sql: str = None) -> str:
"""Emit job start event."""
run_id = str(uuid.uuid4())
# Build input datasets
input_datasets = [
Dataset(
namespace=self.namespace,
name=inp['name'],
facets={
"schema": SchemaDatasetFacet(
fields=[
SchemaField(name=f['name'], type=f['type'])
for f in inp.get('schema', [])
]
)
}
)
for inp in inputs
]
# Build output datasets
output_datasets = [
Dataset(
namespace=self.namespace,
name=out['name'],
facets={
"schema": SchemaDatasetFacet(
fields=[
SchemaField(name=f['name'], type=f['type'])
for f in out.get('schema', [])
]
)
}
)
for out in outputs
]
# Build job facets
job_facets = {}
if sql:
job_facets["sql"] = SqlJobFacet(query=sql)
# Create and emit event
event = RunEvent(
eventType=RunState.START,
eventTime=datetime.utcnow().isoformat() + "Z",
run=Run(runId=run_id),
job=Job(namespace=self.namespace, name=job_name, facets=job_facets),
inputs=input_datasets,
outputs=output_datasets
)
self.client.emit(event)
return run_id
def emit_job_complete(self, job_name: str, run_id: str,
output_metrics: dict = None):
"""Emit job completion event."""
output_facets = {}
if output_metrics:
output_facets["dataQuality"] = DataQualityMetricsInputDatasetFacet(
rowCount=output_metrics.get('row_count'),
bytes=output_metrics.get('bytes')
)
event = RunEvent(
eventType=RunState.COMPLETE,
eventTime=datetime.utcnow().isoformat() + "Z",
run=Run(runId=run_id),
job=Job(namespace=self.namespace, name=job_name),
inputs=[],
outputs=[]
)
self.client.emit(event)
def emit_job_fail(self, job_name: str, run_id: str, error_message: str):
"""Emit job failure event."""
event = RunEvent(
eventType=RunState.FAIL,
eventTime=datetime.utcnow().isoformat() + "Z",
run=Run(runId=run_id, facets={
"errorMessage": {"message": error_message}
}),
job=Job(namespace=self.namespace, name=job_name),
inputs=[],
outputs=[]
)
self.client.emit(event)
# Usage example
emitter = DataLineageEmitter("http://marquez:5000/api/v1/lineage")
run_id = emitter.emit_job_start(
job_name="transform_orders",
inputs=[
{"name": "raw.orders", "schema": [
{"name": "id", "type": "string"},
{"name": "amount", "type": "decimal"}
]}
],
outputs=[
{"name": "analytics.fct_orders", "schema": [
{"name": "order_id", "type": "string"},
{"name": "net_amount", "type": "decimal"}
]}
],
sql="SELECT id as order_id, amount as net_amount FROM raw.orders"
)
# After job completes
emitter.emit_job_complete(
job_name="transform_orders",
run_id=run_id,
output_metrics={"row_count": 1500000, "bytes": 125000000}
)
Pipeline Monitoring with Prometheus
# monitoring/metrics.py
from prometheus_client import Counter, Gauge, Histogram, start_http_server
from functools import wraps
import time
# Define metrics
PIPELINE_RUNS = Counter(
'pipeline_runs_total',
'Total number of pipeline runs',
['pipeline_name', 'status']
)
PIPELINE_DURATION = Histogram(
'pipeline_duration_seconds',
'Pipeline execution duration',
['pipeline_name'],
buckets=[60, 300, 600, 1800, 3600, 7200]
)
ROWS_PROCESSED = Counter(
'rows_processed_total',
'Total rows processed by pipeline',
['pipeline_name', 'table_name']
)
DATA_FRESHNESS = Gauge(
'data_freshness_hours',
'Hours since last data update',
['table_name']
)
DATA_QUALITY_SCORE = Gauge(
'data_quality_score',
'Data quality score (0-1)',
['table_name', 'check_type']
)
def track_pipeline(pipeline_name: str):
"""Decorator to track pipeline execution."""
def decorator(func):
@wraps(func)
def wrapper(*args, **kwargs):
start_time = time.time()
try:
result = func(*args, **kwargs)
PIPELINE_RUNS.labels(pipeline_name=pipeline_name, status='success').inc()
return result
except Exception as e:
PIPELINE_RUNS.labels(pipeline_name=pipeline_name, status='failure').inc()
raise
finally:
duration = time.time() - start_time
PIPELINE_DURATION.labels(pipeline_name=pipeline_name).observe(duration)
return wrapper
return decorator
def record_rows_processed(pipeline_name: str, table_name: str, row_count: int):
"""Record number of rows processed."""
ROWS_PROCESSED.labels(pipeline_name=pipeline_name, table_name=table_name).inc(row_count)
def update_freshness(table_name: str, hours_since_update: float):
"""Update data freshness metric."""
DATA_FRESHNESS.labels(table_name=table_name).set(hours_since_update)
def update_quality_score(table_name: str, check_type: str, score: float):
"""Update data quality score."""
DATA_QUALITY_SCORE.labels(table_name=table_name, check_type=check_type).set(score)
# Start metrics server
if __name__ == '__main__':
start_http_server(8000)
Alerting Configuration
# alerting/prometheus_rules.yml
groups:
- name: data_quality_alerts
rules:
- alert: DataFreshnessAlert
expr: data_freshness_hours > 24
for: 15m
labels:
severity: critical
team: data-platform
annotations:
summary: "Data freshness SLA violated"
description: "Table {{ $labels.table_name }} has not been updated for {{ $value }} hours"
- alert: DataQualityDegraded
expr: data_quality_score < 0.95
for: 10m
labels:
severity: warning
team: data-platform
annotations:
summary: "Data quality below threshold"
description: "Table {{ $labels.table_name }} quality score is {{ $value }}"
- alert: PipelineFailure
expr: increase(pipeline_runs_total{status="failure"}[1h]) > 0
for: 5m
labels:
severity: critical
team: data-platform
annotations:
summary: "Pipeline failure detected"
description: "Pipeline {{ $labels.pipeline_name }} has failed"
- alert: PipelineSlowdown
expr: histogram_quantile(0.95, rate(pipeline_duration_seconds_bucket[1h])) > 3600
for: 30m
labels:
severity: warning
team: data-platform
annotations:
summary: "Pipeline execution time degraded"
description: "Pipeline {{ $labels.pipeline_name }} p95 duration is {{ $value }} seconds"
- alert: LowRowCount
expr: increase(rows_processed_total[24h]) < 1000
for: 1h
labels:
severity: warning
team: data-platform
annotations:
summary: "Unusually low row count"
description: "Pipeline {{ $labels.pipeline_name }} processed only {{ $value }} rows in 24h"
Incident Response
Runbook Template
# Incident Runbook: Data Pipeline Failure
## Overview
This runbook covers procedures for handling data pipeline failures.
## Severity Levels
- **P1 (Critical)**: Data older than 24 hours, revenue-impacting
- **P2 (High)**: Data older than 4 hours, customer-facing dashboards affected
- **P3 (Medium)**: Data older than 1 hour, internal reports delayed
- **P4 (Low)**: Non-critical pipeline, no business impact
## Initial Response (First 15 minutes)
### 1. Acknowledge the Alert
```bash
# Acknowledge in PagerDuty
curl -X POST https://api.pagerduty.com/incidents/{incident_id}/acknowledge
# Post in #data-incidents Slack channel
2. Assess Impact
- Which tables are affected?
- Which downstream consumers are impacted?
- What is the data freshness currently?
-- Check data freshness
SELECT
table_name,
MAX(updated_at) as last_update,
DATEDIFF(hour, MAX(updated_at), CURRENT_TIMESTAMP) as hours_stale
FROM information_schema.tables
WHERE table_schema = 'analytics'
GROUP BY table_name
ORDER BY hours_stale DESC;
3. Identify Root Cause
Check Pipeline Status
# Airflow
airflow dags list-runs -d <dag_id> --state failed
# dbt
dbt debug
dbt run --select state:failed
# Spark
spark-submit --status <application_id>
Common Failure Modes
| Symptom | Likely Cause | Fix | |---------|--------------|-----| | OOM errors | Data volume spike | Increase memory, add partitioning | | Timeout | Slow query | Optimize query, check locks | | Connection refused | Network/auth | Check credentials, VPC rules | | Schema mismatch | Source change | Update schema, add contract | | Duplicate key | Upstream bug | Deduplicate, fix source |
Resolution Procedures
Restart Failed Pipeline
# Clear failed Airflow task
airflow tasks clear <dag_id> -t <task_id> -s <start_date> -e <end_date>
# Rerun dbt model
dbt run --select <model_name>+
# Resubmit Spark job
spark-submit --deploy-mode cluster <job.py>
Backfill Missing Data
# Airflow backfill
airflow dags backfill -s 2024-01-01 -e 2024-01-02 <dag_id>
# dbt incremental refresh
dbt run --full-refresh --select <model_name>
Rollback Procedure
# dbt rollback (use previous version)
git checkout <previous_sha> -- models/<model>.sql
dbt run --select <model_name>
# Delta Lake time travel
spark.sql("""
RESTORE TABLE analytics.orders TO VERSION AS OF 10
""")
Post-Incident
1. Write Incident Report
- Timeline of events
- Root cause analysis
- Impact assessment
- Remediation steps taken
- Follow-up action items
2. Update Monitoring
- Add missing alerts
- Adjust thresholds
- Improve documentation
3. Share Learnings
- Post in #data-engineering
- Update runbooks
- Schedule blameless postmortem if P1/P2
---
## Cost Optimization
### Query Cost Analysis
```sql
-- Snowflake query cost analysis
SELECT
query_id,
user_name,
warehouse_name,
execution_time / 1000 as execution_seconds,
bytes_scanned / 1e9 as gb_scanned,
credits_used_cloud_services,
query_text
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP)
ORDER BY credits_used_cloud_services DESC
LIMIT 20;
-- BigQuery cost analysis
SELECT
user_email,
query,
total_bytes_processed / 1e12 as tb_processed,
total_bytes_processed / 1e12 * 5 as estimated_cost_usd, -- $5/TB
creation_time
FROM `project.region-us.INFORMATION_SCHEMA.JOBS_BY_USER`
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY total_bytes_processed DESC
LIMIT 20;
Cost Optimization Strategies
# cost/optimizer.py
from dataclasses import dataclass
from typing import List, Dict
import pandas as pd
@dataclass
class CostRecommendation:
category: str
current_cost: float
potential_savings: float
recommendation: str
priority: str
class CostOptimizer:
"""Analyze and optimize data platform costs."""
def __init__(self, connection):
self.conn = connection
def analyze_query_costs(self) -> List[CostRecommendation]:
"""Identify expensive queries and optimization opportunities."""
recommendations = []
# Find queries scanning full tables
full_scans = self.conn.execute("""
SELECT
query_text,
COUNT(*) as execution_count,
AVG(bytes_scanned) as avg_bytes,
SUM(credits_used) as total_credits
FROM query_history
WHERE bytes_scanned > 1e10 -- > 10GB
AND start_time > DATEADD(day, -7, CURRENT_TIMESTAMP)
GROUP BY query_text
HAVING COUNT(*) > 10
ORDER BY total_credits DESC
""").fetchall()
for query, count, avg_bytes, credits in full_scans:
recommendations.append(CostRecommendation(
category="Query Optimization",
current_cost=credits,
potential_savings=credits * 0.7, # Estimate 70% savings
recommendation=f"Add WHERE clause or partitioning to reduce scan. Query runs {count}x/week, scans {avg_bytes/1e9:.1f}GB each time.",
priority="high"
))
return recommendations
def analyze_storage_costs(self) -> List[CostRecommendation]:
"""Identify storage optimization opportunities."""
recommendations = []
# Find large unused tables
unused_tables = self.conn.execute("""
SELECT
table_name,
bytes / 1e9 as size_gb,
last_accessed
FROM table_metadata
WHERE last_accessed < DATEADD(day, -90, CURRENT_TIMESTAMP)
AND bytes > 1e9 -- > 1GB
ORDER BY bytes DESC
""").fetchall()
for table, size, last_accessed in unused_tables:
monthly_cost = size * 0.023 # $0.023/GB/month for S3
recommendations.append(CostRecommendation(
category="Storage",
current_cost=monthly_cost,
potential_savings=monthly_cost,
recommendation=f"Table {table} ({size:.1f}GB) not accessed since {last_accessed}. Consider archiving or deleting.",
priority="medium"
))
# Find tables without partitioning
unpartitioned = self.conn.execute("""
SELECT table_name, bytes / 1e9 as size_gb
FROM table_metadata
WHERE partition_column IS NULL
AND bytes > 10e9 -- > 10GB
""").fetchall()
for table, size in unpartitioned:
recommendations.append(CostRecommendation(
category="Storage",
current_cost=0,
potential_savings=size * 0.1, # Estimate 10% query cost savings
recommendation=f"Table {table} ({size:.1f}GB) is not partitioned. Add partitioning to reduce query costs.",
priority="high"
))
return recommendations
def analyze_compute_costs(self) -> List[CostRecommendation]:
"""Identify compute optimization opportunities."""
recommendations = []
# Find oversized warehouses
warehouse_util = self.conn.execute("""
SELECT
warehouse_name,
warehouse_size,
AVG(avg_running_queries) as avg_queries,
AVG(credits_used) as avg_credits
FROM warehouse_metering_history
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP)
GROUP BY warehouse_name, warehouse_size
""").fetchall()
for wh, size, avg_queries, avg_credits in warehouse_util:
if avg_queries < 1 and size not in ['X-Small', 'Small']:
recommendations.append(CostRecommendation(
category="Compute",
current_cost=avg_credits * 7, # Weekly
potential_savings=avg_credits * 7 * 0.5,
recommendation=f"Warehouse {wh} ({size}) has low utilization ({avg_queries:.1f} avg queries). Consider downsizing.",
priority="high"
))
return recommendations
def generate_report(self) -> Dict:
"""Generate comprehensive cost optimization report."""
all_recommendations = (
self.analyze_query_costs() +
self.analyze_storage_costs() +
self.analyze_compute_costs()
)
total_current = sum(r.current_cost for r in all_recommendations)
total_savings = sum(r.potential_savings for r in all_recommendations)
return {
"total_current_monthly_cost": total_current,
"total_potential_savings": total_savings,
"savings_percentage": total_savings / total_current * 100 if total_current > 0 else 0,
"recommendations": [
{
"category": r.category,
"current_cost": r.current_cost,
"potential_savings": r.potential_savings,
"recommendation": r.recommendation,
"priority": r.priority
}
for r in sorted(all_recommendations, key=lambda x: -x.potential_savings)
]
}