Data Platform Architecture and Technology Selection Questions

When compute and storage costs on a data platform have grown too high, the fix has to separate three distinct cost drivers, wasteful storage tiering, wasteful compute usage patterns, and wasteful query design, because each has a different remedy.

Storage tiering

Move data that is rarely queried (older than, say, 90 days) from hot, expensive storage to a cheaper warm or cold tier, and delete or archive data past its actual retention requirement rather than keeping everything "just in case." This is usually the single largest and least risky lever, because it doesn't touch how anyone queries the data, only where it physically sits.

Materialized views and caching

If ad-hoc queries or dashboards repeatedly recompute the same aggregation from raw data, materializing that aggregation once (refreshed on a schedule matching how fresh the answer actually needs to be) turns many expensive scans into one cheap scan plus many cheap reads of the precomputed result. A result cache in front of the BI layer catches the common case of the exact same query being run many times a day by different dashboard viewers.

Compute autoscaling

For scheduled ETL (extract, transform, load) jobs on transient clusters, right-size the cluster to the actual job (a job that historically finishes with cluster utilization at 30 percent is over-provisioned) and ensure clusters actually shut down between runs rather than idling. For interactive query compute, autoscale down aggressively outside business hours rather than running a fixed-size warehouse around the clock.

Architectural changes

If ad-hoc analysts are scanning raw, unpartitioned files directly, partitioning and clustering the underlying tables on the columns those queries actually filter on (date, region, customer segment) is often the highest-leverage architectural fix, because it reduces bytes scanned per query regardless of who runs it or how often.

Worked example

Say ad-hoc queries against a raw events table currently scan the full table (say, 10 TB) on every query because it isn't partitioned by date, and analysts typically only care about the last 30 days. Partitioning by date so a typical query only scans the relevant 30-day slice (say, roughly 300 GB, if daily volume is even) cuts bytes-scanned-per-query by roughly 97 percent for that common access pattern, which for a byte-scanned pricing model translates almost directly into a proportional cost reduction on that workload, without changing a single line of the analyst-facing query.

Preserving query performance while cutting cost

The order of operations matters: apply storage tiering and partitioning first (these reduce cost without touching latency, and often improve it), then materialize the specific hot aggregations dashboards depend on (this improves latency while cutting cost), and only then look at autoscaling policy (which affects cost directly but needs headroom tuning to avoid introducing cold-start latency during traffic spikes). Cutting compute capacity before fixing the underlying query patterns just makes the same wasteful queries slower without actually reducing waste.

Trade-offs and pitfalls

The most common mistake is reaching for aggressive autoscaling or smaller compute allocations as the first lever, because it's the fastest change to make, without first fixing the partitioning and materialization issues that are the actual source of wasted spend; this trades a cost problem for a latency problem instead of solving either. The second is archiving or deleting data based on age alone without checking whether a specific, infrequently-run but business-critical report (an annual compliance report, say) still depends on it.

When an organization genuinely needs heavy BI, ad-hoc data science, and near-real-time features all on the same data, the honest answer is usually neither a pure warehouse nor a pure lake alone, but a lakehouse serving BI and data science together, plus a narrow, separate low-latency path for the specific real-time feature needs.

Why not standardize on one alone

A pure warehouse struggles with the unstructured or semi-structured data data science often wants, and it usually isn't built for the sub-second, high-throughput reads a real-time feature-serving layer needs. A pure lake without transactional guarantees creates correctness headaches for BI (dashboards need trustworthy, consistent numbers, not "eventually consistent" files) and forces data science to build its own reproducibility discipline from scratch. A lakehouse (an open table format like Iceberg or Delta Lake over lake storage) closes most of that gap for BI and data science on one copy of the data, but it is not, by itself, a low-latency online feature store.

The architecture

Curated lakehouse tables serve both BI (via the warehouse/query-engine layer on top) and data science (direct table access, versioned, ACID-safe [atomicity, consistency, isolation, durability], so an experiment run last month is reproducible today). For the near-real-time feature need specifically, add a narrow streaming path: a stream processor computes the handful of features that genuinely need sub-second freshness and writes them to a low-latency key-value store (the actual online feature-serving layer), separate from the lakehouse. The lakehouse remains the batch/offline source of truth that periodically backfills and reconciles against that online store, so the two never permanently diverge.

Data flow and governance

Raw events land once, in the lakehouse's raw layer. Batch transforms build the curated tables BI and data science both read. A stream processor reads the same raw event stream in parallel (not a copy pulled from the curated layer) to avoid adding batch latency to the real-time path, computes the specific low-latency features, and writes them to the online store. Governance-wise, both paths should derive from the same event schema and the same feature definitions, checked in code, so a BI number and a real-time feature score never quietly diverge on what "active user" or "recent purchase" means.

Trade-offs and pitfalls

The most common mistake is trying to make one system do all three jobs by choosing whichever technology sounds most feature-complete on paper. A lakehouse alone will disappoint the real-time-feature team on latency; a dedicated feature-store product alone will disappoint BI and data science on the breadth of historical, ad-hoc querying they need. The second mistake is letting the batch and streaming paths compute the same business logic independently in two codebases, which reliably drifts apart over time; sharing the feature/metric definitions between the two paths, even if the execution engines differ, is what prevents that.

A Redshift-to-BigQuery migration (or the equivalent move between any two cloud warehouses) is really three separate problems: making the data land correctly, making the queries and dashboards keep working, and doing the cutover without a period where nobody trusts the numbers.

The migration plan

Schema translation: map Redshift's distribution and sort keys to BigQuery's partitioning and clustering columns; these aren't 1-to-1 concepts, so this step needs deliberate redesign, not a mechanical copy. Data types mostly translate cleanly, but SQL dialect differences (window function syntax, date functions) will break existing queries and dashboards that reference them directly.
Cost modeling: model BigQuery's on-demand-per-byte-scanned pricing against your actual query patterns, not just against Redshift's current node-hour cost, since the two pricing models reward different behavior (BigQuery rewards good partitioning and column pruning; Redshift's provisioned-node cost is largely independent of how well queries are written).
Testing and validation: run both systems in parallel for a defined period, and reconcile: row counts, and aggregate sums on the metrics the business actually cares about (revenue, active users), grouped the same way in both systems, for every day of the parallel-run window, not just a spot check.
Cutover: switch BI tools and downstream consumers over in phases (least-critical dashboards first), rather than a single cutover date for everything, so a translation bug surfaces on a low-stakes report before it surfaces on the CEO's dashboard.
Rollback: keep the Redshift cluster running and reachable for a defined grace period (commonly a few weeks) after cutover, with a documented, tested procedure for repointing BI tools back if the new platform surfaces a serious issue.

Three non-obvious trade-offs

First, BigQuery's pricing model means query-writing discipline now directly affects cost in a way Redshift's provisioned model didn't; a team used to writing loose, unoptimized SQL on Redshift will get an unpleasant surprise on the first month's BigQuery bill unless partitioning and clustering are designed in from the start, not bolted on after complaints. Second, Redshift's tight native integration with the rest of the AWS ecosystem (IAM, AWS Identity and Access Management, and other AWS-native services) has to be rebuilt with BigQuery's GCP-native equivalents; if the rest of the company's infrastructure stays on AWS, this migration adds a permanent cross-cloud networking and identity-management cost that a pure warehouse comparison wouldn't surface. Third, distribution-key tuning that Redshift teams have accumulated as tribal knowledge over years (which tables to co-locate, which keys to distribute on) does not transfer to BigQuery's serverless model at all; that expertise becomes sunk cost, and the team effectively restarts the performance-tuning learning curve on the new platform.

Trade-offs and pitfalls

The most common mistake is treating this as a data-movement problem and underestimating the query-rewrite and dashboard-revalidation effort, which is usually the larger share of total migration cost. The second is skipping the parallel-run reconciliation step to save time, which reliably surfaces subtle correctness bugs (a rounding difference, a NULL-handling difference between the two SQL dialects) only after cutover, when they're far more expensive to diagnose.

A cost-allocation model has to answer, defensibly, "how much of this shared bill belongs to your team," which is straightforward for dedicated resources and genuinely hard for shared ones.

Tagging strategy

Require every compute job and every stored dataset to carry a team or product-owner tag at creation time, enforced by policy (a job or table without a valid tag either fails to run or gets flagged), not by asking people to remember. Tag at the finest practical granularity, per query or per job run, not just per dataset, since a shared dataset queried by five teams needs usage-level attribution, not just ownership-level attribution.

Handling shared resources fairly

For genuinely shared infrastructure (a common raw-data lake, a shared orchestration platform), allocate cost using a defensible proxy for actual usage, bytes scanned per team for a shared warehouse, compute-seconds consumed per team for a shared processing cluster, rather than an even split, which systematically overcharges light users and undercharges heavy ones. For genuinely indivisible shared cost (the platform team's own headcount, a base infrastructure license fee that doesn't vary with usage), either allocate it evenly as an acknowledged "platform tax" or pro-rate it by team size or overall usage share, whichever the organization has agreed is fair, but be explicit that this portion is a policy choice, not a measured usage number.

Reporting cadence

Report monthly at a minimum, with a runnable, self-service breakdown available to each team on demand rather than only in a monthly PDF, so a team can investigate a cost spike in the same week it happens rather than a month later when the details are hard to reconstruct. Include trend, not just the current month's number, since a single month's snapshot doesn't show a team whether their cost trajectory is improving or worsening.

Dispute-resolution process

Publish the tagging and allocation methodology itself (not just the resulting numbers) so a team can check the calculation, not just the conclusion. Provide a defined escalation path (a specific person or a lightweight review committee) for a team that believes its allocated cost is wrong, with a documented turnaround time, and track how often disputes are raised and resolved, since a rising dispute rate is itself a signal the methodology needs revisiting, not that teams are being difficult.

Worked example

If a shared raw-data lake costs a fixed amount in storage per month and three teams query it with wildly different frequency, allocate the storage cost by data volume each team's data occupies (a clean, defensible per-team number), and allocate the compute cost of QUERYING that lake by bytes scanned per team (measurable directly from query logs), rather than splitting either cost three ways evenly, which would let the lightest user subsidize the heaviest one indefinitely with no visibility into why.

Trade-offs and pitfalls

The most common mistake is allocating shared cost evenly because it's simple to compute, which quietly subsidizes heavy users at light users' expense and erodes trust in the whole model once someone notices. The second is building a cost model with no visible methodology or dispute path, which turns every allocation disagreement into a political argument instead of a checkable calculation.

A capacity-planning model has to connect three growing inputs (data volume, retention, and query concurrency) to two cost drivers (storage and compute) with enough transparency that someone can challenge your assumptions later.

The model

Storage cost scales with retained data volume, which is a function of daily ingestion volume and retention period:

$$\text{Storage}_{TB} = \text{DailyIngestGB} \times 365 \times \text{RetentionYears} / 1000$$

Compute cost has two components: scheduled ETL/ELT (extract, transform, load / extract, load, transform) jobs, roughly proportional to data volume processed, and interactive query compute (roughly proportional to concurrent users and query complexity, largely independent of total data volume if partitioning is effective):

$$\text{ComputeCost} = (\text{DailyIngestGB} \times \text{ETLCostPerGB}) + (\text{ConcurrentUsers} \times \text{AvgQueriesPerUserPerDay} \times \text{CostPerQuery})$$

Worked example with pinned inputs

Assume: daily ingestion of 500 GB per day, a 2-year retention policy, 50 concurrent analysts averaging 20 queries per day each, and illustrative, platform-specific unit rates of 0.02 cost-units per GB-month for storage, 0.01 cost-units per GB processed for ETL compute, and 0.05 cost-units per query for interactive compute (substitute your own platform's actual billed rates here).

Storage: 500 GB/day times 365 days times 2 years equals 365,000 GB, or 365 TB, retained after two years. At 0.02 cost-units per GB-month, that is roughly 7,300 cost-units per month once fully ramped (this grows steadily over the two years as retained volume accumulates; it is not the month-1 cost).

ETL compute: 500 GB/day times 0.01 cost-units per GB times 30 days equals 150 cost-units per month.

Interactive compute: 50 users times 20 queries/day times 30 days times 0.05 cost-units per query equals 1,500 cost-units per month.

Total steady-state monthly cost at the 2-year retention horizon: roughly 7,300 plus 150 plus 1,500, or about 8,950 cost-units per month, with storage dominating because it compounds over the retention window while compute scales with current usage, not accumulated history.

Forecasting 12-24 months out

Apply a growth rate to daily ingestion and concurrent users (say, a business-projected 20 percent year-over-year data growth and 15 percent analyst-headcount growth) and recompute the same formulas at each future point; the storage term compounds fastest because it's a function of accumulated volume over the growing retention window, not just the current day's ingestion, which is precisely why storage tends to dominate total cost at scale even though its per-unit price is far cheaper than compute.

Validating the model

Compare the model's projected cost for the CURRENT month (using today's actual ingestion volume and concurrency) against the actual current bill; if they diverge by more than a small margin, the unit-cost assumptions are wrong and need recalibrating against real invoices before trusting the 12-24 month projection. A model that isn't first validated against a month you can already observe is not a forecast, it's a guess with a formula attached.

Trade-offs and pitfalls

The most common mistake is projecting compute cost as though it scales with total data volume the way storage does; well-partitioned interactive queries scan a bounded slice of data regardless of how much history is retained, so compute growth is really driven by user count and query complexity, not by retention. Ignoring that distinction produces a model that wildly overestimates future compute cost and underinvests in the partitioning discipline that would keep it flat.