Data warehouses and analytical storage

A data warehouse brings data together for reporting and analysis. Consider one when reports need several sources, substantial history or repeated scans that are hard to serve alongside an application’s transactions. A small, well-bounded report may work from an existing database or reporting replica. The choice depends on the workload, not a universal data-size threshold.

Start with the reporting work

List the reports and investigations the organisation needs. For each one, record its sources, historical period, required update time, usual filters, readers and busiest reporting period. Include scheduled preparation jobs: joins and transformations may run before anyone opens a dashboard.

Look at overlapping work as well as individual queries. A monthly close may bring data loads, finance queries and management reports together. Compare candidate approaches with representative data and queries at a realistic busy time; product descriptions cannot establish performance for your workload.

Compare storage patterns

Microsoft Fabric SQL databases support structured data and both transactional and analytical workloads. Check the capabilities and boundaries of the specific service being considered; products in one category do not necessarily behave alike.

Analytical Storage Patterns: When to Use Each

Make figures traceable

Record where each reported figure came from, which source owns a field when systems disagree, and the cutoff for each load. Separate source inputs from prepared reporting data where that makes investigation easier. Historical data is useful only when readers can identify the period and definition behind a figure.

Arrange data around observed queries. In BigQuery, a qualifying filter on a partition column can skip other partitions. Creating partitions alone does not ensure that a query will use them. Check the behaviour of the selected engine with the intended queries.

Storage also has to support the agreed measure definitions and checks for missing or duplicated source records. A successful load, by itself, does not establish that a report is correct.

Partitioning can also support routine data management, not just query scans. BigQuery allows a partition to have an expiry time and supports loading data into, or deleting, a specific partition without affecting or scanning the whole table. These options are useful to assess where reporting history has clear retention periods or is managed in separate time slices.

Decide whether reporting needs separate resources

Operational databases handle business transactions and application queries. Analytical work often asks for totals across many records, long-period comparisons and joins between systems. Large aggregations can strain a transactional system, although a modest report or a suitable mixed-workload design may work well there.

If reporting moves to a warehouse, decide how updates and late corrections arrive and what readers see during an incomplete load. A daily management report and a live service queue can reasonably need different paths. Test whether separate analytical resources, history or combined data solve a specific reporting problem.

Pros and Cons of Using a Data Warehouse vs. Operational Database

Account for the operating cost

Count retained source data, prepared tables, recurring transformations, dashboard refreshes and exploratory queries. Billing depends on the service.

BigQuery separates storage from query compute and offers on-demand and capacity pricing for queries. Snowflake documentation describes compute costs for running warehouses and other applicable services. A storage rate alone is therefore an incomplete reporting-cost estimate.

Keep growth, peak use and account-specific billing assumptions visible. Compare a forecast with billing and job records if a limited workload is later run.

Key Cost and Performance Considerations for Analytical Storage

Check what the platform provides

Microsoft Fabric’s decision guide identifies data type, compute engine, ingestion and transformation patterns, query needs, access controls, and integration with OneLake and other Fabric components as factors to assess. Use these as prompts to check that a candidate store fits the reporting work and the surrounding platform.

Fabric identifies SQL databases, warehouses, lakehouses and eventhouses as its primary analytical data stores. It also offers mirrored databases that continuously replicate data and metadata for analytics, but these provide read access to mirrored data and are not general-purpose data stores. That boundary matters if readers or processes need to update the analytical copy.

Keep product-specific size guidance in context

Microsoft describes Fabric SQL databases as suited to structured data and moderate volumes, typically several gigabytes to a few terabytes. Treat that as guidance for this particular Fabric option, not a universal point at which reporting should move to a warehouse.

Microsoft Fabric SQL Database: Size Guidance and Use Cases

Make a bounded first decision

Choose one maintained report with a known source, a fixed historical period and a figure its owner can check. Capture its update time, access needs and expected growth. Compare viable storage approaches against that work, including the effort to operate them. If a separate store is justified, begin with the data needed for that reporting slice and check both the figure and query behaviour before expanding.

Steps to Make a Bounded First Decision