India’s Leading Domain & Web Hosting Provider
Support
Domain & Login
Blog
Cloud Data Warehouse Build on Redshift, Fabric Warehouse or BigQuery
A cloud data warehouse is a managed, SQL-first analytical database (Amazon Redshift, Microsoft Fabric Warehouse, Google BigQuery or Snowflake) that stores modelled, structured data for reporting and analysis at scale. NTSPL designs dimensional models, builds tested ELT pipelines and certified marts, and isolates workloads so finance, sales and operations can trust and share the same numbers.
Delivered on
In scope
Why Extract-Per-Team Reporting Breaks at Month-End
Finance closes the month on numbers that operations dispute, because each team pulls its own extract from the ERP and applies its own rules for returns, credit notes and scheme accruals. Reports that join sales with inventory run for 40 minutes on a shared SQL Server and are killed during the day to protect the ERP. Adding a new principal, product line or state means weeks of rework in stored procedures nobody wants to touch.
NTSPL builds warehouses around conformed dimensions and facts at the grain the business argues about, such as invoice line, claim or policy-month. Transformations live in version-controlled dbt or Dataform models with tests on every key and amount, and BI, data science and finance close each get isolated compute so one heavy query cannot stall the CFO's pack. Finance signs off control totals first.
When a Modelled Cloud Warehouse Is the Answer
Signals you need Cloud Data Warehouse
- Month-end reporting needs more than 3 days of manual reconciliation between finance and operations
- Analytical queries on the ERP or a reporting replica are throttled or killed in business hours
- Structured data is between 1 TB and 100 TB and mostly from ERP, CRM, core or policy systems
- Regulatory returns (IRDAI, RBI, GST) are compiled from spreadsheets with no audit trail back to source
When to choose something else
- Most of your volume is raw logs, images or sensor files: start with a Cloud Data Lake or Lakehouse Architecture.
- You already run a warehouse and want to move it to the cloud with minimal change: Data Modernization & Migration handles code conversion and parallel runs.
Models, Pipelines and Marts in a Warehouse Build
Dimensional modelling
Bus matrix, conformed dimensions (customer, product, geography, calendar) and fact tables at agreed grain, with slowly changing dimension rules.
ELT pipelines as code
dbt or Dataform models in Git with staging, intermediate and mart layers, CI on every pull request and documentation generated from code.
Data tests and certification
Uniqueness, not-null, referential and reconciliation tests against source control totals before a mart is marked certified.
Workload management
Separate compute, queues or reservations for ELT, BI, data science and finance close, with concurrency and cost limits.
Marts and sharing
Subject marts for finance, sales and operations, shared to other teams or entities through zero-copy data sharing.
How Cloud Data Warehouse Works, Step by Step
Layered ELT Warehouse with Conformed Dimensions
Ingested bronze tables are cleaned into staging models, combined into conformed dimensions and facts, then published as tested subject marts. Isolated compute serves ELT, BI and finance workloads separately.
Select any component
Layered ELT Warehouse with Conformed Dimensions: click any of its 8 components to see its role and the design choice NTSPL makes for it, or trace the 6-step primary path.
Warehouse Engines Compared Across Clouds
We default to Redshift Serverless for spiky BI loads and RA3 provisioned clusters with reserved nodes when utilisation stays above roughly 60%.
Fabric Warehouse stores tables in OneLake as Delta, so Power BI can read them in Direct Lake mode without import refreshes.
BigQuery separates storage and slots; we use Dataform for SQL pipelines and separate reservations so ELT never competes with Looker dashboards.
Modelling and Engine Decisions That Shape Cost and Trust
Kimball, Data Vault or wide tables
Use Kimball star schemas for most Indian mid-market warehouses: they are fast to deliver and easy for analysts. Add Data Vault only when you integrate 10 or more volatile sources or need full auditability of every source change, as in some BFSI groups. Expose wide denormalised tables in marts for BI speed, but build them from the star, never instead of it.
Choosing the engine
Pick the warehouse native to the cloud where your ERP and BI already live. Redshift suits AWS-centric estates with steady loads; Fabric Warehouse suits Microsoft 365 and Power BI shops; BigQuery suits spiky, ad-hoc workloads with minimal administration. Snowflake is the strongest choice when you need one warehouse across two clouds.
Grain and history
Store facts at the lowest grain the source provides, such as invoice line rather than invoice, and keep SCD Type 2 history for attributes used in incentive or commission calculations, like dealer tier or territory. Re-statements are then reproducible. Aggregates and snapshots are cheap to rebuild from atomic facts; lost grain cannot be recovered.
Workload isolation
Give ELT, BI and finance close separate compute from day one. On BigQuery that means separate reservations; on Redshift, separate workgroups or WLM queues; on Fabric, separate capacities or workspaces. Set per-workload spend alerts at 80% of budget so a runaway query is caught within hours, not at the monthly invoice.
Mart-by-Mart Warehouse Delivery Plan
Bus matrix and grain agreement
Map business processes to dimensions, agree fact grain and certify control totals with finance.
Platform and CI foundation
Provision the warehouse, Git repository, CI pipeline, environments and workload isolation.
Core dimensions and first fact
Build conformed dimensions and the highest-value fact table with full test coverage.
First certified mart
Publish the finance or sales mart, reconcile with source for two cycles and certify.
Additional marts and sharing
Add marts in priority order and set up data sharing to subsidiaries or partners.
What you receive
Warehouse Accuracy, Speed and Adoption Targets
| KPI | Typical target |
|---|---|
| Certified mart reconciliation to source control totals | 100%, zero unexplained variance |
| Dashboard query p95 on marts | Under 5 seconds |
| Model test coverage on keys and amounts | 100% of fact and dimension keys |
| Month-end reporting cycle | Reduced by 50-70% |
Warehouse Pitfalls and How We Avoid Them
| Risk | How NTSPL prevents it |
|---|---|
| Business logic duplicated in BI tool and warehouse | KPI logic lives in marts; BI semantic models only reference certified columns. |
| Warehouse cost creeps as analysts run unbounded queries | Workload limits, query timeouts and weekly top-20 query reviews with owners. |
| Restatements change historical numbers without explanation | SCD Type 2 history and snapshot tables make every restatement traceable and dated. |
Redshift vs BigQuery vs Fabric Warehouse: which cloud data warehouse should we choose?
Choose by estate, not benchmark. If your ERP, identity and engineering skills are on AWS and workloads are steady, Redshift with reserved RA3 capacity is usually cheapest per query. If you live in Microsoft 365 and Power BI, Fabric Warehouse removes import refreshes through Direct Lake and simplifies licensing on one capacity. If workloads are spiky, ad hoc and you want minimal administration, BigQuery is the easiest to run. All three handle 1-100 TB comfortably; the deciding costs are egress, skills and BI integration.
Key facts: Cloud Data Warehouse
Cloud Data Warehouse in Practice
2 Cloud Data Warehouse scenarios, from FMCG distribution and General insurance, plus related platform case studies.
FMCG distributor settles brand scheme claims in 9 days instead of 47
General insurer files IRDAI returns from a reconciled warehouse, not 140 spreadsheets
Agri-inputs company sees dealer secondary sales daily instead of 45 days late
Dairy cooperative sees milk procurement by collection centre within 15 minutes
Cloud Data Warehouse: Frequently Asked Questions
How long does it take to build a cloud data warehouse?
The first certified mart typically goes live in 8-10 weeks, and a warehouse covering finance, sales and operations in 4-6 months. Build cost for a mid-size company usually falls between INR 35 lakh and 1 crore, depending on source count and the number of marts.
Can the warehouse produce IRDAI, RBI or GST returns?
Yes. We build regulatory marts at the grain each return needs, reconcile them to the general ledger and keep lineage from return line back to source records. Auditors can trace every figure, and spreadsheets drop out of the filing process.
Should we use dbt or Dataform?
Both work well. Dataform is built into BigQuery and is the natural choice on Google Cloud. dbt supports Redshift, Fabric, Snowflake and BigQuery and has a larger community, so we use it on AWS, Azure and multi-cloud estates. The modelling approach is identical either way.
Where is the data stored, and is it in India?
We deploy in Indian regions: Redshift in AWS Mumbai or Hyderabad, Fabric capacity in Azure Central India or South India, and BigQuery datasets in Mumbai or Delhi. Dataset location is fixed at creation and enforced through organisation policies. Backups and snapshot copies stay in-country, using the paired Indian region for DR.
Will our existing Power BI or Tableau reports still work?
Yes, after re-pointing to the marts. We map each existing report to certified columns, rebuild calculations that duplicated warehouse logic and run old and new reports in parallel for one close cycle before switching. Users keep familiar report layouts throughout.
How do you handle late or corrected source data?
Incremental models re-process a rolling window, typically 7-35 days, so late entries and credit notes land in the right period. Larger corrections trigger a dated restatement that finance approves, with history preserved. Each restatement is logged with its reason and approver.