English | Español

---

data-engineering-toolkit

Production-grade data engineering patterns and architecture blueprints for Google Cloud Platform (GCP) and Snowflake. Includes idempotent BigQuery pipelines, LLMOps gateways, multimodal OCR extraction, and high-throughput Playwright web scrapers.

---

Architecture Blueprint

graph LR
    subgraph Ingestion [Ingestion & Extraction Layer]
        A[Playwright Scrapers] --> B[GCS Raw Bucket]
        C[REST APIs / Webhooks] --> D[Google Cloud Pub/Sub]
        E[Scanned Invoices / PDFs] --> B
    end

    subgraph Processing [DataOps & Validation]
        B --> F[Cloud Run Jobs]
        D --> F
        F --> G[Pydantic v2 Contract Validation]
        F --> H[Gemini 1.5 Multimodal OCR]
    end

    subgraph Storage [Analytical Lakehouse]
        G --> I[(BigQuery Partitioned & Clustered Tables)]
        I --> J[(Snowflake DWH)]
    end

    subgraph Serving [AI & BI Serving Layer]
        I --> K[LiteLLM API Gateway]
        I --> L[NL-to-SQL Copilot]
        I --> M[Connected Sheets / BI Dashboards]
    end

Loading

---

Core Engineering Patterns

1. Idempotent Data Ingestion & Physical Table Optimization

Physical DDL defining date partitioning, multi-column clustering, and schema contracts to minimize scan costs and guarantee deterministic re-runs.

-- Partitioned by day with multi-column clustering for sub-second query pruning
CREATE OR REPLACE TABLE `enterprise_analytics.fct_ad_intelligence_daily`
(
    ad_fingerprint STRING NOT NULL,
    brand_id STRING NOT NULL,
    creative_url STRING,
    vision_taxonomy STRUCT<
        category STRING,
        sentiment STRING,
        confidence FLOAT64
    >,
    spend_estimate NUMERIC,
    captured_at TIMESTAMP NOT NULL,
    ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY DATE(captured_at)
CLUSTER BY brand_id, ad_fingerprint
OPTIONS (
    description = "Idempotent ad intelligence facts with partition pruning and scan controls",
    require_partition_filter = true
);

Idempotent MERGE Statement

MERGE INTO `enterprise_analytics.fct_ad_intelligence_daily` T
USING `staging.stg_ad_events_batch` S
ON T.captured_at = S.captured_at
   AND T.brand_id = S.brand_id
   AND T.ad_fingerprint = S.ad_fingerprint
WHEN MATCHED THEN
    UPDATE SET 
        vision_taxonomy = S.vision_taxonomy,
        spend_estimate = S.spend_estimate,
        ingested_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (ad_fingerprint, brand_id, creative_url, vision_taxonomy, spend_estimate, captured_at)
    VALUES (S.ad_fingerprint, S.brand_id, S.creative_url, S.vision_taxonomy, S.spend_estimate, S.captured_at);

---

2. Data Contracts & Schema Validation (Pydantic v2)

Strict upstream validation prevents corrupted records from contaminating analytical datasets.

from pydantic import BaseModel, Field, HttpUrl
from datetime import datetime
from typing import Optional

class VisionTaxonomyContract(BaseModel):
    category: str = Field(..., min_length=2, max_length=50)
    sentiment: str = Field(..., pattern="^(positive|neutral|negative)$")
    confidence: float = Field(..., ge=0.0, le=1.0)

class IngestedAdPayload(BaseModel):
    ad_fingerprint: str = Field(..., min_length=16, max_length=64)
    brand_id: str
    creative_url: Optional[HttpUrl] = None
    vision_taxonomy: VisionTaxonomyContract
    spend_estimate: Optional[float] = Field(None, ge=0.0)
    captured_at: datetime

    class Config:
        frozen = True
        extra = "forbid"

---

3. Enterprise LLM API Gateway (LiteLLM + FastAPI)

Centralized proxy managing multi-provider routing (Gemini, Claude, GPT-4o), virtual API keys, team-level token quotas, and asynchronous FinOps telemetry streaming to BigQuery.

# litellm_config.yaml
model_list:
  - model_name: enterprise-fast
    litellm_params:
      model: vertex_ai/gemini-1.5-flash
      budget_duration: 30d
  - model_name: enterprise-reasoning
    litellm_params:
      model: vertex_ai/gemini-1.5-pro
      budget_duration: 30d

---

Technology Stack

• Storage & DWH: Google BigQuery, Snowflake, PostgreSQL, Google Cloud Storage (GCS)
• Processing & Streaming: Python 3.11, Google Cloud Pub/Sub, Eventarc, Cloud Run Jobs, FastAPI, PyArrow
• AI & LLMOps: Vertex AI, Gemini 1.5 Pro/Flash, LiteLLM, Pydantic v2
• Web Scraping & Automation: Playwright (Headless Chromium), Docker

---

data-engineering-toolkit

Patrones de ingeniería de datos y esquemas de arquitectura para Google Cloud Platform (GCP) y Snowflake. Incluye pipelines idempotentes en BigQuery, gateways de LLMOps, extracción OCR multimodal y scrapers con Playwright.

---

Diagrama de Arquitectura

graph LR
    subgraph Ingesta [Capa de Ingesta y Extracción]
        A[Scrapers Playwright] --> B[Bucket GCS Raw]
        C[APIs REST / Webhooks] --> D[Google Cloud Pub/Sub]
        E[Facturas Escaneadas / PDFs] --> B
    end

    subgraph Procesamiento [DataOps y Validación]
        B --> F[Cloud Run Jobs]
        D --> F
        F --> G[Validación de Contratos Pydantic]
        F --> H[OCR Multimodal Gemini 1.5]
    end

    subgraph Almacenamiento [Lakehouse Analítico]
        G --> I[(Tablas Particionadas BigQuery)]
        I --> J[(Data Warehouse Snowflake)]
    end

    subgraph Consumo [Capa de Servido y Analítica]
        I --> K[Gateway LiteLLM]
        I --> L[Copiloto NL-to-SQL]
        I --> M[Connected Sheets / Dashboards]
    end

Loading

---

Patrones de Ingeniería Principales

1. Ingesta Idempotente y Optimización de Tablas Físicas

DDL físico con particionamiento por fecha y clustering multicolumna para minimizar costos de escaneo y garantizar reejecuciones deterministas.

CREATE OR REPLACE TABLE `enterprise_analytics.fct_ad_intelligence_daily`
(
    ad_fingerprint STRING NOT NULL,
    brand_id STRING NOT NULL,
    creative_url STRING,
    vision_taxonomy STRUCT<
        category STRING,
        sentiment STRING,
        confidence FLOAT64
    >,
    spend_estimate NUMERIC,
    captured_at TIMESTAMP NOT NULL,
    ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
PARTITION BY DATE(captured_at)
CLUSTER BY brand_id, ad_fingerprint
OPTIONS (
    description = "Hechos de inteligencia publicitaria con poda de particiones",
    require_partition_filter = true
);

---

2. Contratos de Datos y Validación de Esquemas (Pydantic v2)

La validación previa en la capa de ingesta asegura que registros corruptos nunca lleguen a las tablas analíticas finales.

---

3. Gateway Corporativo de APIs LLM (LiteLLM + FastAPI)

Proxy centralizado para balanceo multi-modelo (Gemini, Claude, OpenAI), gestión de cuotas de tokens por equipo y telemetría de costos en BigQuery.

---

Stack Tecnológico

• Almacenamiento y DWH: Google BigQuery, Snowflake, PostgreSQL, Google Cloud Storage (GCS)
• Procesamiento y Streaming: Python 3.11, Google Cloud Pub/Sub, Eventarc, Cloud Run Jobs, FastAPI, PyArrow
• IA y LLMOps: Vertex AI, Gemini 1.5 Pro/Flash, LiteLLM, Pydantic v2
• Automatización y Web Scraping: Playwright, Docker