NovaPunk LLC
← All services

04 / Cloud & Data Engineering

Cloud Data Architecture & Engineering

Design the data foundation. Engineer the path to analytics and AI.

NovaPunk designs and implements cloud data architectures that connect operational systems to analytics and AI applications. The service covers source-system analysis, relational and warehouse modelling, migration, change data capture, SQL transformations, workflow orchestration, and validation. GCP implementation can combine Cloud SQL, Datastream, BigQuery, Dataform, Cloud Workflows, and Cloud Scheduler.

Discuss this service

Built for your requirements.

The problem

Operational databases are designed to support transactions, while analytics and AI need joined history, consistent definitions, and reusable features. The service creates a deliberate path between those environments instead of coupling every dashboard and model directly to production tables.

What we can build with you

  • Source-system assessment, architecture diagrams, and data-flow specifications
  • Operational ERDs, warehouse datasets, and migration mappings
  • CDC ingestion and analytics-ready transformation pipelines
  • Feature marts and downstream BI or AI interfaces
  • Workflow dependencies, service-account access design, and run verification
  • Synthetic test datasets and optional knowledge-ingestion/RAG pipelines

Architecture follows the workload.

The architecture work defines what belongs in the operational system, what belongs in the analytical environment, and how the two communicate.

Data discovery and requirements

Survey available sources, identify the data needed for the use case, and specify freshness, history, consumption patterns, and integration boundaries before selecting services.

Operational modelling and migration

Design entity relationships and normalized tables for transactional workloads. The reference project separates customer, address, payment, order header/detail, product, and SKU domains and migrates a local MariaDB dataset into Cloud SQL.

Warehouse and mart design

Separate source replicas from analytical tables. BigQuery datasets organize customer features, predictions, campaigns, vector-related assets, and pipeline logs around their intended consumers.

Access and deployment design

Define service identities, data-location requirements, and service-to-service permissions alongside the architecture. The project encountered region and service-account invocation issues, which informed its implementation checks.

Capture changes. Build reusable data products.

In the implemented pipeline, CDC ingestion is continuous while feature transformation is scheduled. Their freshness and execution semantics are described separately.

CDC ingestion

Datastream captures insert, update, and delete events from the MySQL binary log. Initial backfill and subsequent changes feed the BigQuery source replica. Freshness and source-log retention must be sized for the workload; zero latency is not assumed.

SQL transformations and feature marts

Dataform SQLX defines transformation targets and dependencies. The retail example joins transaction and catalog data to build recency, frequency, spend, discount behavior, and category features in mart.customer_features.

Compile, execute, and schedule

Cloud Workflows creates a Dataform compilation result, then invokes the compiled workflow. Cloud Scheduler triggers the reference job daily at 01:00 UTC. The supplied example returns an invocation identifier; completion checks and failure handling must be designed for the operating workflow.

Trace changes end to end

The pipeline account verifies a source UPDATE in Cloud SQL, its CDC replica in BigQuery, the Dataform job, and the matching update timestamp in a mart join. Run identifiers, timestamps, record checks, and failure visibility are part of the service design.

Extend the foundation when the application needs it.

Synthetic data and knowledge retrieval support the core architecture and engineering work. They are optional components selected for the application.

Synthetic data for development

Create representative test records to exercise relationships, transformations, and edge cases before connecting customer data. Simulation-generated labels, such as the separate MuJoCo harvest dataset, support physical modelling use cases.

Data for retrieval and generation

Prepare a knowledge corpus, metadata, embedding/index ingestion, and retrieval interfaces for downstream applications. The later retail implementation uses Pinecone and LangChain; the early architecture diagram shows BigQuery Vector Search as the original design.

Internal research / prototype

From operational retail data to an analytical feature mart

The Walsh Retail architecture and pipeline documents illustrate this service using a small internal project. The initial architecture separates ingestion, storage/modelling, intelligence, and action. The subsequent pipeline account documents direct Cloud SQL-to-BigQuery CDC, Dataform transformations, and scheduled Cloud Workflows execution.

Static demonstration page. Figures and examples replace the video presentation for now; no live agent, model, or simulator runs on this page.

Inputs and data

  • Source-system entities, relationships, existing schema, and reporting requirements
  • Cloud SQL retail schema with customer, order, product, payment, address, and SKU data
  • Binlog changes and raw BigQuery replica data
  • Transformation definitions, freshness requirements, and workflow dependencies

Example scope

The pipeline report documents operational migration, CDC backfill and change propagation, Dataform customer-feature generation, and scheduled workflow invocation. Its end-to-end UPDATE test traces a source change to a matching timestamp in the analytical join. These are reference-project observations, not a throughput or availability guarantee for a client workload.

01

Assess & model

Survey source data, select domains, define ERDs and mappings.

02

Migrate & capture

Load operational tables and capture changes with Datastream.

03

Transform

Compile SQLX and build analytical features in BigQuery.

04

Orchestrate

Coordinate execution with Workflows and Scheduler.

05

Verify & serve

Trace source changes through the mart and expose BI/AI interfaces.

Operational and analytical boundaries

Normalized transaction tables feed a source replica. Dataform builds purpose-specific marts for BI and AI consumers, keeping their transformations separate from operational writes.

Verification path

Source UPDATE → CDC replica → transformation job → analytical join. Matching timestamps provide a concrete check that the change reached the intended data product.

A reference architecture evolves.

The original design includes Cloud Composer and a GCS landing path. The later pipeline uses direct BigQuery ingestion and Cloud Workflows. The diagrams below preserve both stages and identify their differences.

Four-layer GCP reference architecture showing ingestion, storage and modelling, intelligence, and action
Original architecture diagram from Project Design & Architecture (page 18). It records the early design, including Cloud Composer and BigQuery Vector Search; later implementation uses Cloud Workflows and Pinecone. Select the figure to view it at full size.
Implemented data pipeline from Cloud SQL through Datastream and BigQuery to Dataform feature marts, Workflows and Scheduler
Implemented pipeline diagram from Data Pipeline: CDC, ETL & Orchestration, Figure 8.1. It is the later implementation view of the reference project. Select the figure to view it at full size.

What the study reports.

8Operational tables in the reference project
200Customer feature rows reported
01:00 UTCReference transformation schedule

Metrics are reproduced from the supplied research or original project account. They have not been independently rerun for this website.

Boundaries and next work

  • The retail system is a prototype with synthetic data and a limited demonstration scale.
  • RAG quality depends on corpus quality, retrieval relevance, and output evaluation.
  • Simulation-generated labels inherit the assumptions of the simulator.
  • Cloud access controls, monitoring, cost, and scale must be scoped for each implementation.
  • This page creates no GCP resources, invokes no model APIs, and sends no messages.

Explore the engineering.

Based primarily on the supplied Project Design & Architecture and Data Pipeline documents by Sungwon Son. Later AI/RAG and BI accounts provide downstream application context.

What would you like to build?

Tell us about your workflow, available data, and the decision or task you want to improve.

Contact NovaPunk