Data Warehouse Development | Snowflake BigQuery

Platform assessment based on your existing cloud infrastructure, data volume, query patterns, team SQL proficiency, and cost constraints -- with the recommendation documented and justified before procurement decisions are made. Snowflake: separation of storage from compute means query costs scale with usage rather than provisioned capacity; virtual warehouses scale independently per team or workload; Data Sharing enables cross-organisation data collaboration without copying data; ideal for organisations with variable query concurrency or multi-cloud infrastructure. BigQuery: serverless, no cluster management, tightly integrated with Google Cloud services (Vertex AI, Looker, Pub/Sub), pricing at on-demand per-TB or flat-rate slot reservations depending on workload predictability; optimal for GCP-native organisations. Redshift: columnar storage with mature RA3 node type for storage-compute separation, Redshift Spectrum for querying S3 data directly, strong performance for large predictable SQL workloads on AWS. Databricks: Delta Lake format with ACID transactions, Unity Catalog for unified governance across SQL and ML workloads, ideal when data science teams need the same data as analysts. Initial setup: VPC configuration for private connectivity between source systems and warehouse, IAM roles and policies for least-privilege access, resource monitors and cost alerts to prevent runaway query costs, and environment separation (dev/staging/production schemas or databases).

Dimensional data modelling using star schema patterns for BI-optimised query performance: fact tables (orders, sessions, events, transactions) at the grain of the most granular business event, surrounded by dimension tables (customer, product, date, geography, salesperson) that enable any combination of grouping and filtering without schema changes. Snowflake schema used where dimension tables have significant sub-dimensions that would create excessive column count in a star schema. The data model is designed around your actual business questions -- "revenue by product line by region by month" defines the dimensions needed, not the other way around. Naming conventions and grain documentation for every fact table: the grain (one row = one order line item, not one row = one order) documented explicitly so downstream analysts know what a COUNT(*) means. Three-layer architecture: raw_ staging tables preserving source data exactly as received (audit trail, re-processing capability), stg_ staging models applying cleaning and standardisation (type casting, null handling, deduplication), and mart_ business entities applying the business logic that definitions dictate (a mart_orders model applies the agreed revenue recognition rule, not each analyst's interpretation). Schema documentation generated from dbt model descriptions and column-level descriptions -- a browsable data dictionary that analysts reference before writing a query, reducing the "what does this field mean" questions to data engineering.

dbt (data build tool) transformation layer with every business-defined transformation written as a modular SQL SELECT statement committed to a git repository -- version-controlled, code-reviewed, and deployable via CI/CD pipeline rather than applied manually in a warehouse console. Materialisation strategies selected per model based on data volume and refresh frequency: table for small lookup dimensions refreshed fully each run, incremental for large fact tables where only new or changed records are processed (using updated_at watermarks or stream CDC change detection), and view for lightweight transformations where compute cost at query time is acceptable. dbt tests configured at the model level: not_null and unique tests on primary keys catch upstream data quality issues before they corrupt downstream reports; relationships tests verify foreign key integrity between fact and dimension tables; accepted_values tests validate categorical fields against the agreed value set. Custom schema tests for business logic assertions: total revenue per order must equal the sum of line item revenues; customer counts must be non-decreasing; order status must follow a valid state transition sequence. dbt CI integration: the test suite runs automatically on every pull request against a development schema before merging, catching model errors without deploying to production. Generated dbt documentation published as a browsable web UI showing the DAG of model dependencies, test results, and column descriptions -- the living documentation that stays accurate because it generates from the code rather than being maintained separately.

Centralised metric definitions implemented in a semantic layer so every business metric has one definition, one calculation, and one number -- regardless of which BI tool, analyst, or dashboard is asking the question. Options based on your BI tooling: dbt Metrics (dbt's native semantic layer, now dbt Semantic Layer with MetricFlow), Looker LookML (semantic definitions in Looker that generate SQL dynamically), Cube.js (headless semantic layer serving metrics to any BI tool or API), or Power BI semantic model (DAX measures centralised in a shared dataset). Metric definitions cover the business concepts that produce disagreements: revenue (invoiced vs cash vs recognised, including returns and refunds), churn rate (the definition of an active customer, the numerator, the denominator, the period boundary rules), customer acquisition cost (which costs are included, over what period, for which customer segments), lifetime value (prediction horizon, discount rate if NPV-based, segment breakdowns). Each metric definition documented with the business name, the calculation in plain language, the SQL formula, the edge cases explicitly excluded, and the data steward responsible for changes. The process for changing a metric definition: the change is proposed, reviewed with the business stakeholders affected, merged into the semantic layer via a pull request with a dbt or LookML diff showing the downstream impact, and communicated to report owners before deployment -- so metric changes are deliberate and traceable, not discovered when a dashboard number changes unexpectedly.

Migration of historical records from legacy databases, spreadsheets, CSV archives, and deprecated data stores into the new warehouse. Data cleaning and standardisation applied during migration so historical records conform to the same schema and business logic as current records. Reconciliation reports confirming that migrated totals -- revenue, order counts, customer counts -- match the source systems within an agreed tolerance. Historical data is a business asset; migration preserves it in a queryable form rather than archiving it somewhere inaccessible.

Connection setup for Looker, Tableau, Metabase, Power BI, or the BI tool your team already uses. Semantic layer exposure to the BI tool so analysts build dashboards from business-defined metrics rather than raw warehouse tables. Query pattern guidance for analysts so reports run efficiently at warehouse scale without triggering expensive full-table scans. Documentation of the data model, mart layer, and metric definitions delivered to the team that maintains the warehouse after the engagement ends.