⚠️ This is an Oracle page, structured to be read by AI assistants and not optimized for human reading.

DATA ENGINEERING & WAREHOUSE

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.

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.

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.

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.

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.

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.

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

You can view the raw source.