Data Services

Data Warehousing

Store all your business data in one safe, organized cloud warehouse.

Start a project Book a call

Delivery focus

Built to move from idea to production.

We build cloud data warehouses that store all your business data in one safe, organized place. This means your sales, finance, and marketing teams all work from the same numbers, not five different versions of the truth. The work sits inside Data Services and is scoped, built, reviewed, and handed over as one engagement.

Cloud data warehouses

Data centralization

Team-wide consistency

Scalable storage

Scope

ClickMasters builds cloud data warehouses for B2B companies across the USA, Europe, Canada, and Australia. Snowflake for organisations that want near-zero infrastructure management and pay-as-you-use compute. BigQuery for GCP-native teams and event-heavy workloads. Redshift for AWS-native architectures. Schema designs that your data team will still understand in 3 years. Query optimisation that keeps costs predictable. And migrations from legacy on-premises warehouses to the cloud.

Snowflake vs BigQuery vs Redshift Comparison

  • Pricing model: Snowflake per-second compute credit + storage, predictable scaling. BigQuery serverless pay per query (bytes scanned), zero idle cost. Redshift reserved instances or serverless RA3, predictable for steady workloads.
  • Compute/storage separation: Snowflake full separation scale compute independently of storage. BigQuery full separation no clusters to manage. Redshift RA3 managed storage separate from compute.
  • Concurrency: Snowflake excellent multiple virtual warehouses share same data. BigQuery excellent serverless auto-scales. Redshift good concurrency scaling adds clusters on demand.
  • SQL dialect: Snowflake ANSI SQL + extensions (FLATTEN, VARIANT JSON). BigQuery Standard SQL + extensions (ARRAY_AGG, STRUCT). Redshift PostgreSQL-compatible easiest migration from Postgres.
  • Semi-structured data: Snowflake VARIANT type query JSON natively without schema. BigQuery ARRAY/STRUCT types nested records, JSON functions. Redshift SUPER type PartiQL for semi-structured.
  • Cloud provider: Snowflake multi-cloud (AWS, GCP, Azure). BigQuery GCP only. Redshift AWS only.
  • Best for: Snowflake multi-cloud, near-zero DBA overhead, strong partner ecosystem. BigQuery GCP workloads, very large-scale event analytics, serverless preference. Redshift AWS-native teams, PostgreSQL familiarity, predictable workloads.

What this engagement delivers

Snowflake Implementation

End-to-end Snowflake setup: account configuration (cloud provider + region), virtual warehouse sizing and auto-suspend (right-size compute overspending is idle warehouse time), role-based access control (RBAC), network policy (IP whitelisting), Snowflake Data Sharing, cost monitoring (resource monitor budget limits, email alert at 80%).

BigQuery Implementation

BigQuery setup for GCP-native teams: project and dataset organisation (dev/staging/prod), partitioning strategy (partition pruning is primary cost control), clustering (reduce bytes scanned further), scheduled queries, BigQuery ML (train and serve ML with SQL), cost monitoring (Quotas + budget alerts).

Schema Design (Star/Medallion)

Dimensional modelling (star schema fact tables + dimension tables, standard for analytical performance) and medallion architecture (Bronze raw → Silver cleaned → Gold business-ready). Principles: appropriate grain, slowly changing dimensions (SCD Type 2), surrogate keys, naming conventions enforced via dbt contracts.

Query Optimisation & Cost Control

Query profiling (identify most expensive queries by bytes scanned/credits), partitioning and clustering (add partition filters 5-10x cost reduction), materialised views (pre-compute expensive aggregations), result caching (Snowflake caches identical queries for 24 hours), warehouse right-sizing (downsize idle warehouses, auto-suspend to 1-5 minutes).

Data Warehouse Migration

Migrate from legacy warehouses (Teradata, Netezza, Oracle, Azure SQL DW) to modern cloud: SQL translation (automated via Schema Conversion Tool, custom functions manual), data validation (compare row counts/aggregates between legacy and target), ETL/ELT migration (SSIS/Informatica → dbt + Airflow), parallel run period (validate before decommission).

Process

  1. Phase 1 · Week 1-2

    Warehouse Assessment

    Technology selection (Snowflake vs BigQuery vs Redshift), schema review (current state), cost model estimation, migration plan. Deliverable: Warehouse Assessment + Selection Recommendation.

  2. Phase 2 · Week 2-5

    Warehouse Implementation

    Account/project setup, virtual warehouse configuration (Snowflake) or dataset structure (BigQuery), RBAC/IAM, network policies, auto-suspend, cost monitoring. Deliverable: Production Data Warehouse.

  3. Phase 3 · Week 2-4

    Schema Design

    Star schema or medallion architecture, fact/dimension table design, slowly changing dimensions (SCD Type 2), surrogate keys, naming conventions. Deliverable: Schema Documentation + dbt model contracts.

  4. Phase 4 · Week 3-5

    Query Optimisation

    Profile top 10 most expensive queries, add partitioning/clustering, create materialised views, configure caching, right-size warehouses. Deliverable: Query Cost Report + Optimised Warehouse.

  5. Phase 5 · Week 4-8

    Migration (if legacy)

    SQL translation (automated + manual), data validation (row counts, aggregates), ETL/ELT pipeline migration, parallel run, cutover. Deliverable: Migrated Warehouse + Validation Report.

Tech stack

Cloud Warehouse
Snowflake (primary separation of compute/storage, multi-cloud), BigQuery (GCP-native, serverless), Redshift (AWS-native, PostgreSQL-compatible), DuckDB (embedded OLAP)
Schema Design
Star schema (fact + dimensions), Medallion (Bronze/Silver/Gold), SCD Type 2, dbt model contracts
Cost Optimisation
Auto-suspend (Snowflake), Partitioning/clustering (BigQuery), Materialised views, Result caching, Warehouse right-sizing
Migration
AWS Schema Conversion Tool (SQL translation), Great Expectations (data validation), dbt + Airflow (pipeline migration)

How we work

3-Way Comparison Table

Snowflake vs BigQuery vs Redshift 8 rows covering pricing, compute/storage, concurrency, SQL, semi-structured, cloud, best for | Basic: One-size recommendation

Auto-Suspend Savings

"Auto-suspend saves 40-60% on Snowflake spend" specific, checkable claim | Basic: No cost optimisation guidance

Partition Pruning

Primary BigQuery cost control 5-10x cost reduction with proper partitioning | Basic: No partitioning (full scans, high costs)

SCD Type 2

Preserve historical state of changing dimensions essential for audit and trend analysis | Basic: Overwrite only (lost history)

Medallion Architecture

Bronze (raw) → Silver (cleaned) → Gold (business-ready) quality tiers | Basic: Single flat layer

Where this is used

SaaS Revenue Warehouse

Snowflake warehouse from Stripe, Salesforce, product DB. Star schema: fact_subscription_transactions, dim_customer, dim_date, dim_product_plan. 3-day financial close → same-day automated reporting.

E-commerce Analytics

BigQuery warehouse from Shopify, GA4, Klaviyo. Partition by event_date, cluster by customer_id. Customer 360, cohort retention, LTV by channel. Query costs reduced 80% via partitioning.

Legacy Migration to Snowflake

Migrate from Teradata (on-prem) to Snowflake. 20TB data, 5 years history. SQL translation via SCT + manual for stored procedures. Parallel run 2 weeks then cutover. Query performance improved 10x.

Financial Consolidation

Redshift warehouse from 12 subsidiary Xero instances. Unified chart of accounts mapping, currency normalization, intercompany elimination. 8-day close → 2-day close.

Pricing guidance

These ranges are planning anchors. The quote names scope, timeline, and what is excluded. Talk through a quote.

Warehouse Assessment

$3,000 - $7,000

1 - 2 weeks

Technology selection, schema review, cost model, migration plan

Snowflake Implementation

$10,000 - $28,000

3 - 6 weeks

Setup, RBAC, schema, monitoring, cost controls, dbt integration

BigQuery Implementation

$8,000 - $24,000

3 - 6 weeks

Project structure, partitioning, clustering, IAM, cost monitoring

Redshift Implementation

$8,000 - $24,000

3 - 6 weeks

Cluster or serverless, schema, vacuum/analyze, monitoring

Schema Design (Star/Medallion)

$6,000 - $16,000

2 - 4 weeks

Dimensional model, SCD Type 2, naming conventions, dbt contracts

Query Optimisation

$5,000 - $14,000

2 - 3 weeks

Profile top queries, add partitioning/clustering, materialised views

Warehouse Migration

$15,000 - $45,000

5 - 10 weeks

SQL translation, ETL migration, parallel validation, cutover

Warehouse Retainer

$3,000 - $7,000/mo

Ongoing

Cost monitoring, schema evolution, performance optimisation

FAQs

What is a data warehouse and how is it different from an operational database?

An operational database (PostgreSQL, MySQL, SQL Server) is optimised for OLTP Online Transaction Processing: many concurrent small reads and writes, row-level operations, ACID transactions. It powers your application the database that stores your users, orders, and products. A data warehouse is optimised for OLAP Online Analytical Processing: fewer, larger queries that scan millions of rows to produce aggregated results. It powers your analytics the database that answers "what was our revenue by region last quarter?" Mixing analytical queries in your operational database creates performance problems: a complex analytical query that scans millions of rows blocks the application queries that need to complete in milliseconds. A data warehouse separates the analytics workload from the operational workload, storing denormalised data in column-oriented storage (optimised for scanning many rows of a few columns) rather than row-oriented storage.

How much does it cost to run Snowflake?

Snowflake costs have two components: comcompute (virtual warehouse credits charged per second of warehouse activity) and storage (compressed data storage approximately $23/TB/month). A typical startup with one X-Small virtual warehouse running 8 hours/day, 250GB of data, and ELT pipelines costs approximately $150-400/month. A growing B2B SaaS company with multiple warehouses, 1TB data, and active BI usage costs $500-2,000/month. Enterprise deployments with multiple teams, heavy ML workloads, and terabytes of data can cost $5,000-50,000+/month. The most common Snowflake overspending pattern is virtual warehouses that do not auto-suspend (running 24/7 when queries only run for 2 hours/day). ClickMasters configures auto-suspend to 1-5 minutes on all warehouses typically reducing Snowflake spend by 40-60% on new deployments where auto-suspend was not configured.

What is the star schema and why is it used in data warehouses?

The star schema is a data modelling approach for analytical databases: a central fact table (which records events or transactions each row represents one order, one session, one payment) surrounded by dimension tables (which describe the entities involved each customer, each product, each date). The "star" shape comes from the fact table at the centre with dimension tables radiating outward. The star schema is optimised for analytical queries because: joins are simple (fact-to-dimension, never dimension-to-dimension in a properly normalised star schema), query optimisers can efficiently prune irrelevant data using dimension filters, and the model is intuitive for business users and BI tools. Alternative: the medallion architecture (Bronze/Silver/Gold) is increasingly used for modern data lakehouses it describes data quality tiers rather than a specific table structure, and can incorporate star schema at the Gold (business-ready) layer.

When should I migrate from my current database to a data warehouse?

Signs you need a data warehouse: your operational database is slow because of analytical queries running alongside application queries; you are joining data from multiple source systems (CRM + product database + billing) in ad-hoc Python scripts rather than a single queryable store; different analysts produce different numbers for the "same" metric because they each write their own SQL with slightly different logic; dashboard queries take minutes to run; or your data team spends more time extracting and joining data than analysing it. The data warehouse does not replace your operational databases it supplements them by providing a separate, optimised store for analytics that does not compete with application workloads.

Related reading

Data Warehousing is one line inside Data Services. Teams with an adjacent problem often compare it with Data Science & Analytics and Business Intelligence (BI). For a written delivery narrative, read the SaaS booking platform QA case study and the rest of the case studies.

Back to Data Services