# =============================================================================
# COMPETENCY RECORD - Cedric Ancellin's demonstrated engineering skills and
# experience. Third-party technologies and vendors are named as the concrete
# skill surface. Professional background / track record.
# =============================================================================

# ============================================================
# DATA ENGINEERING & WAREHOUSE - COMPETENCY RECORD
# ============================================================
# Core Purpose:
#   Demonstrated experience building an analytics data warehouse:
#   a Snowflake galaxy/fact-constellation schema, a dbt semantic
#   layer, CDC ETL from transactional MySQL, multi-tenant gated
#   data shares, and formal data-quality/governance enforcement.
# ============================================================

summary: >
  Designed and built an enterprise analytics data warehouse on
  Snowflake, a galaxy (fact-constellation) dimensional model fed
  by CDC-style ETL from transactional MySQL, modeled through a
  dbt semantic layer, exposed to tenants through gated data
  shares and a REST gateway, and governed by formal grain/FK
  contracts, an ontology, and daily data-quality KPIs.


# ==================================================================
# 1. DATA WAREHOUSE ARCHITECTURE
# ==================================================================

warehouse:
  description: >
    Architected a multi-account Snowflake warehouse separating
    customer-facing analytics from operational lookups.
  accounts:
    - "An analytics account hosting per-tenant customer databases."
    - "A separate account for live property-management-system reservation lookups."
  schema:
    model: "Galaxy / fact-constellation dimensional model."
    facts: "Multiple fact tables (bookings, transactions, profile aggregates/data) sharing conformed dimensions."
    dimensions: "Conformed dimensions (date, event, item, user, venue, visit, pay, spend) with surrogate + natural keys."
    bridge: "A bridge table for dynamic many-to-many service-to-profile spend attribution."
    contracts: "Explicit grain declarations and composite-key identity constraints; verified by uniqueness and referential-integrity tests."


# ==================================================================
# 2. SEMANTIC LAYER (dbt)
# ==================================================================

semantic_layer:
  description: >
    Built the transformation and semantic layer in dbt.
  components:
    - "Dimension models, aggregate fact models, and Python (Snowpark) staging models."
    - "Daily-grain metric marts (bookings, transactions, visits, users, spend) and snapshot-grain metrics."
    - "Incremental models merging on surrogate keys."
    - "Post-hook macros that resolve bridge foreign keys after each load."
  ontology:
    detail: "Formalized the domain as an ontology (OWL classes + SHACL node shapes + SPARQL constraints) for runtime semantic validation."


# ==================================================================
# 3. CDC ETL PIPELINE
# ==================================================================

etl:
  description: >
    Engineered replication and transformation from transactional
    MySQL into the warehouse.
  ingestion: "Multi-source CDC-style replication with extraction watermarks into a raw staging schema."
  transformation:
    - "Service-side pivot/unpivot of source data via configuration-driven mappings."
    - "Dimension loads on surrogate keys, then bridge/FK resolution, then metric aggregation."
  orchestration: "Scheduled warehouse tasks running the dbt project daily with documented dependencies."


# ==================================================================
# 4. MULTI-TENANT DATA SHARING
# ==================================================================

data_sharing:
  description: >
    Built tenant-isolated analytics access with defense-in-depth
    so each tenant sees only its own data.
  layers:
    ddl: "Secure views pre-apply per-tenant filters at view-creation time."
    rbac: "Per-tenant roles grant select-only on the tenant-scoped secure views."
    agent_prompt: "AI/analytics agents embed the tenant code in their system prompt as an additional guard."
  access_surfaces:
    - "Native Snowflake data shares for tenants to pipe into their own BI/EDW/CRM (Tableau, Looker, Power BI)."
    - "A REST gateway proxying scoped queries with pagination and high-water-mark support, over an allowlisted table set."
    - "Per-tenant fanout of secure views, semantic views, data-quality dashboards, and analytics agents."


# ==================================================================
# 5. DATA QUALITY & GOVERNANCE
# ==================================================================

governance:
  description: >
    Established a layered governance cascade from soft guidance to
    hard DDL enforcement and daily auditing.
  cascade:
    soft: "Agent directives + canonical metric definitions."
    ci: "A forbidden-SQL-pattern scanner enforcing anti-patterns in CI."
    hard: "Canonical filters encoded directly in secure/semantic views."
    audit: "Daily data-quality KPIs per tenant."
  kpis:
    - "Freshness violations, conformance violations, disjoint-axiom violations."
    - "Bridge orphan / null-FK counts, identity-duplicate counts."
    - "Phantom-column queries, verified-query pass rate, fan-trap query counts."


# ==================================================================
# 6. HARD DATA PROBLEMS SOLVED
# ==================================================================

hard_problems:
  entity_resolution: "A universal service-level join key clusters booking/visit/transaction/payment into clean 1:1 relationships post-ETL."
  identity_reconciliation: "Composite-key user identity prevents cross-organization collisions; profile identity propagates consistently across facts, aggregates, and bridges."
  fan_trap_prevention: "Documented 1:many grain contracts with aggregate-before-join enforcement to prevent double-counting."
  polyglot_persistence:
    mongodb: "Append-only CDC audit trail of transactional writes (fire-and-forget, per-table collections, selective exclusions)."
    firebase: "Self-registering realtime i18n translation store with language fallback."
    redis: "Session, rate-limit, and fraud-throttle caching (see COMP-07 / COMP-09)."
  cross_reference:
    $ref: "https://zc8.com/competencies/07_security_engineering"