Cloud Data Warehousing
Context: FIT3003_MOC · the same Data Warehouse and Star Schema hosted on provider-managed infrastructure · sibling of Data Lakehouse · (W12 emerging-topics deck — not a book chapter) Parent Framework: Data Warehouse
Quick Revision
- 🎯 Objective: a warehouse hosted and operated on cloud infrastructure ➔ analytical SQL, reporting and BI with managed backups, monitoring and maintenance, and resources that follow the workload.
- 📦 Core Components: service model (IaaS ➔ managed ➔ serverless) | storage compute | platforms Snowflake · Redshift · BigQuery | Oracle ADW.
- ⚡ Key Constraint: the cloud changes how infrastructure is provisioned and managed — it does not automatically guarantee lower cost or better performance, and automation never removes the need for modelling, SQL and governance expertise.
📝 How It Works
1. On-premises vs cloud
| Aspect | On-premises | Cloud |
|---|---|---|
| Infrastructure | organisation-managed | provider-managed |
| Capacity | planned and provisioned in advance | provisioned or scaled on demand |
| Upgrades | significant planning | resources adjusted more quickly |
| Operational burden | organisation runs hardware and most infrastructure | provider assumes some infrastructure and operational duties |
| Cost shape | capacity sits underused in quiet periods | usage-based pricing can align cost with demand |
- Service models ➔ IaaS — the organisation still runs much of the database software and configuration · managed warehouse — the provider runs more of the database operations · serverless analytics — provisioning is abstracted away from the user entirely.
2. Separation of storage and compute
- Storage ➔ persistent tables and history; sized by data volume and retention; cloud object storage or a managed storage layer.
- Compute ➔ runs queries, joins, aggregations, transformations; sized by query performance and concurrency; scales with analytical demand.
- Payoff ➔ each scales on its own ➔ flexible allocation · workload isolation (where supported) · potentially better cost control — keep five years of sales data, add compute only for peak reporting periods.
3. Platforms
| Platform | Architecture | Scaling | Administration | Pricing | Known for |
|---|---|---|---|---|---|
| Snowflake (SaaS) | multi-cluster shared-data; compute and storage fully decoupled | virtual warehouses scale independently and automatically — “infinite” | predictable | pay-as-you-go per second, or pre-purchased | ANSI SQL over structured and semi-structured data · multi-cloud (AWS, Azure, GCP) · data sharing · many concurrent users |
| Amazon Redshift | MPP clusters built on PostgreSQL | planned (choose compute- vs storage-optimised node types) | moderate | (not stated) | AWS-native integration · configurable distribution styles · Redshift Spectrum queries S3 directly · AWS Lake Formation |
| Google BigQuery | fully serverless | automatic, petabyte-scale | none | per data scanned, or flat-rate slot capacity | ANSI SQL over billions of rows · streaming inserts for real-time · Google Cloud AI/ML integration |
When It Flips: Snowflake when compute must scale apart from storage across clouds or for sharing · Redshift when the estate is already AWS and capacity can be planned · BigQuery when nobody should manage infrastructure and cost should follow bytes scanned (real-time analytics).
4. Oracle Autonomous Data Warehouse (ADW)
- What ➔ a managed Oracle Cloud database service for analytical workloads.
- Automated ➔ provisioning and maintenance · backups and software updates · performance optimisation · elastic compute scaling · integration with SQL tools, data integration and analytics services.
- Still yours ➔ data modelling and schema design · SQL and query optimisation · data quality and governance · security, workload management and cost control.
- Oracle modern DW architecture ➔ sources (enterprise apps, devices, end users, events, sensors) ➔ Data Integration / Oracle TEQ ➔ ADW Object Storage Autonomous Database ➔ OML (in-database ML), Oracle Analytics Cloud, analytics/ML apps, Data Catalog.
⚠️ Common Mistakes
- 💡 “Cloud = cheaper and faster” ➔ the deck states it guarantees neither; usage pricing helps only when demand actually varies.
- 💡 “Autonomous = no DBA skills needed” ➔ automation reduces operational effort; the star schema, the SQL and data quality are still designed by you.
🧠 Active Recall
A retailer keeps five years of sales history but only runs heavy reports at quarter-end. Which cloud property matters, and why does on-premises handle this badly?
Answer
- Short answer: separation of storage and compute — retain all the data cheaply, scale compute up only at quarter-end.
- Why: Independent sizing ➔ storage follows volume and retention, compute follows query load; when they are coupled, the machine must be provisioned for the peak and sits underused the rest of the quarter. Usage-based pricing ➔ cost then tracks the quarter-end spike rather than the year-round maximum.
Oracle ADW automates tuning, backups and scaling. Name what it leaves to the warehouse designer.
Answer
- Short answer: schema design, SQL, data quality/governance, and security/workload/cost control.
- Why: Automation is operational, not semantic ➔ it can patch and scale the engine, but it cannot decide the fact’s grain, which dimensions qualify it, or whether the loaded data is clean — the whole of Chapters 2–18 still applies.