Data Platform Cost and Performance Optimization

Data Engineering & Modern Data Platforms · Lesson 58 · Modern Data Platforms and Cloud

Lesson Overview

The Monday board is correct and the invoice is not. Forty people opened a dashboard that scans raw progress as JSON. A warehouse cluster that serves those forty people from 06:00 to 10:00 has been running since it was created. A second engine, stood up during a cloud comparison, still refreshes its own copy of completion-v4. The data is partitioned by the hour because a template said finer partitions are faster. Most hours are empty. The bill is a design, and it is reviewable the same way the merge is reviewable.

Lessons 54 through 56 chose a serving engine and left the others off, with reopen conditions. Data Platform Security and Governance chose identities and audit scope, which have prices of their own. This lesson prices storage, scans, idle capacity, and the layout choices that move those numbers: partitions, file size, and the knobs that make a query read less. It does not crown a cloud. Unit prices change and differ by region. The worked arithmetic uses illustrative rates, labeled as such, so the ratios survive a price change. Replace the rates with the current price card before anyone calls the result a budget.

The teaching volumes stay fixed. About 40 GB of compacted Parquet progress a day. A raw JSON landing, if you still keep one, on the order of a couple of terabytes a day uncompressed or lightly compressed text. A completion mart that is tens of gigabytes, not terabytes, once it is columnar and holds a grain instead of every event. About forty board readers on Monday morning, a handful mid-week. Those shapes are enough to see which line will dominate.

Module 6 will operate the platform and will set the freshness SLO that tells you which knob you may turn. A cheaper plan that misses 06:00 is not a saving. Data Reliability and SLA/SLO Design owns that promise. This lesson shows the cost of keeping it, and the cost of designs that miss it while spending more.

Learning Objectives

By the end of this lesson you will be able to:

1. Separate the meters that matter here: storage, operations, bytes scanned, capacity hours, and data movement.
2. Explain why a smaller table can cost more to query than a larger one when the layout forces a full read.
3. Choose a partition grain for progress and for the mart, and reject hourly partitions that do not match a filter.
4. Set a Parquet file-size target and connect small files to listing, task overhead, and extra scans.
5. Compare a scan-priced lake query with a capacity-priced warehouse using Monday's spike and Wednesday's quiet, without double-counting a second mart.
6. Use dry-run bytes, query history, and warehouse plans as the evidence, not the console's estimated monthly total alone.
7. Say when a result cache or a materialized view will miss, including a dashboard that changes the date.
8. Isolate ELT from the board so a load cannot spend the interactive budget, and so idle baseline is a choice.
9. Treat inter-region copies and full data-access logs as line items with owners, not as free safety.
10. Hand Module 6 a cost sheet that names the SLO you will not trade away to shrink the bill.

Prerequisites

• Lessons 54, 55, and 56 for the engines and the rule of one serving mart. This lesson prices that choice.
• Lesson 57 for audit scope and keys. KMS request volume and data-access logs are meters, not only controls.
• Spark Performance Optimization for shuffle and file size on Spark. The file-size target agrees with that lesson. The scan price is the cloud meter's version of the same layout.
• Data Formats and Serialization for Parquet row groups and compression.
• The lake and Iceberg or Delta lessons for compaction and table commits. Compaction is a cost control only after the commit points at the new files.
• Lesson 62's place in the course, so a performance knob that breaks freshness is refused here and specified there.

Detailed Core Concepts

Five meters, and the ones that will not be your problem

Storage is bytes retained times the class, per month, plus the minimum duration of a colder class. Forty gigabytes a day of Parquet is about 1.2 TB a month and about 15 TB a year if you keep every day. At an illustrative hot-storage rate of $0.02 per GB-month, a year of hot Parquet is on the order of a few hundred dollars a month once the year has accumulated, not the dominant line. The same year's raw JSON, if it is fifty times larger, starts to matter, and a second region doubles it. Storage dominates when you keep everything, in the hot class, in two places. It rarely dominates a bad Monday query.

Operations are requests: puts, lists, gets. A producer that writes one object per progress event at eighty million events a day will pay request charges and will create the small-file problem below. Compacted daily objects, hundreds of them rather than millions, make this meter quiet. Lifecycle transitions and inventories have their own request and storage charges. They are still usually smaller than a full scan of JSON.

Bytes scanned are the on-demand lake and serverless warehouse meter: Athena, BigQuery on-demand, Synapse serverless SQL. You pay for what the query processes, with a minimum per query that makes a tiny lookup less cheap than zero. Columnar format, column projection, and partition pruning change this number by orders of magnitude. This is the meter a single bad dashboard can blow up.

Capacity hours are the warehouse and the reservation: Redshift nodes or RPUs, dedicated SQL pool DWUs, BigQuery slot-hours, a Fabric capacity, a Spark pool that forgot to pause, a Composer or MWAA environment, a self-hosted integration runtime. You pay while the capacity exists or while it is scaled to a baseline, including hours when nobody queries. This is the meter a cluster left running will blow up, quietly, every night.

Movement is egress and cross-region replication. A dashboard in one region reading a mart in another pays to move the result, and sometimes the scan, across regions. Replication of the raw bucket to a second region is storage plus transfer. It is a restore strategy only after a restore test. Until then it is a line item with a hopeful name.

KMS calls and data-access audit logs are smaller meters that become visible when every object is encrypted with a customer-managed key without bucket keys or the cloud's equivalent, or when every progress GET is logged because lesson 57's gap was never written down. Fix those as design, then return to scans and idle capacity. They are the two meters that decide this platform's shape.

Illustrative rates used only for ratios in this lesson: $0.02 per GB-month hot storage, $5 per TB scanned on demand. Slot-hours and node-hours stay symbolic, C per hour, because those price cards move and a fake precision would be worse than a variable. Substitute the real C from the current card. If a conclusion flips when C changes by a factor of two, the conclusion was not a design, it was a quote.

A smaller table can cost more

The mart is tens of gigabytes, columnar, partitioned by event_date. A week of it, projected to the columns the board needs, might be a few gigabytes scanned. The raw JSON landing for one day might be two terabytes scanned for the same business question, because the engine reads text, because every column is the only column, and because a missing partition filter reads the year. The mart is smaller and cheaper. The raw table is larger and, if you query it carelessly, much more expensive. Size on disk is not the bill. Bytes processed, or capacity held while a scan runs, is the bill.

That is why lessons 54 through 56 put the board on the published grain and left raw to a single-day investigation with a cutoff. The cutoff is a cost control and a safety control. It does not replace the layout.

Partition grain follows the filter

Partition on a column that almost every selective query constrains, at a cardinality you can afford.

Daily event_date on progress and on the mart matches the board, the backfill, and the publish. Two years of days is on the order of seven hundred partitions. Planners handle that. Hourly partitions for the same table, without a query that filters to an hour, create thousands of partitions and tiny files. The planner spends time, the listing spends time, and pruning an hour you never needed does not repay it. Partition by hour only when a real consumer filters by hour and each hour is large enough to be a partition of its own.

Do not partition by learner_id. Cardinality explodes. Cluster, sort, or distribute on learner_id inside the day if a selective lookup needs it. Those are different mechanisms:

• A Hive-style or Iceberg partition skips directories or data files before the scan.
• A BigQuery cluster skips blocks inside the partition.
• A Redshift sort key and zone map skip blocks inside the table. Redshift does not need a directory per day for that skip to work. A date predicate on a sort key of event_date is the prune. People who only know Hive go looking for PARTITIONED BY in a Redshift table and, failing to find it, assume the date filter does nothing. Read the sort key and the EXPLAIN.
• A dedicated SQL pool hash distribution does not prune by date. A range partition on event_date does, if you created it. The hash co-locates joins. It is not a partition.

Iceberg hidden partitions, from the Iceberg lesson, can transform event_time into days so readers prune without a hand-built directory name. The cost rule is the same. The metadata is just less brittle. Over-partitioning an Iceberg table still creates too many small files and a heavy metadata tree.

require_partition_filter on BigQuery, and a bytes cutoff on Athena, turn a missing predicate into an error. They do not repair a predicate wrapped in a function that cannot prune. Dry-run bytes are the check.

File size is a cost and a runtime

Target compacted Parquet around 128 MB to 512 MB per object for this platform's analytical reads. The number is a range, not a moral. Far below it, a day of progress becomes tens of thousands of files. The engine lists them, opens a footer per file, and schedules a task per file. You pay the list and you pay the latency, and on a scan-priced engine you can also pay for inefficient reads of tiny row groups. Far above it, a single multi-gigabyte object serializes readers and makes a retry expensive.

Landing tools write small files on purpose: Firehose buffers, Event Hubs Capture windows, a Connect sink flushing on a time interval, a streaming job committing every minute. Compaction is part of the landing, before repeated queries and before COPY. Rewriting a day's partition into fewer objects is the idempotent form already used in the cloud lessons. Compaction has its own compute cost. Run it when the files are small, not on a cron that rewrites a day that was already healthy. A metadata check of file count and average size is the trigger.

Compression changes bytes scanned. Snappy is a common default. Zstd often stores fewer bytes and spends more CPU. On a scan-priced engine, fewer bytes usually win if the CPU time still fits the SLO. Measure one day. Do not adopt Zstd for the mart and leave the raw landing as JSON, then celebrate the codec.

Warehouse capacity versus lake scans

Use a scan-priced query when the work is occasional, the bytes are pruned, and nobody is waiting in a queue of forty. Use capacity when the same pruned query runs concurrently, when you need isolation from a load, or when the engine's features, the row-level policy, the materialized view, are the product. The expensive mistakes are specific.

Idle capacity. A provisioned cluster, a dedicated SQL pool, a high reservation baseline, or a Fabric capacity sized for Monday and left there on Saturday. If resume time fits before 06:00, pause or drop the baseline outside the window. If resume does not fit, the idle hours are the price of the SLO, and they should be named that way. They should not be discovered in the invoice as "the cluster we forgot."

Double serving. Redshift and BigQuery both certified, or a dedicated pool and a Fabric warehouse, from lessons 54 through 56. You pay two capacities or a capacity plus a scan, and you pay the engineering cost when the definitions drift. One mart. The lake holds raw and the published files. The second engine is a reopen condition, not a hot standby that also takes traffic.

A scan that pretends to be cheap. Forty Monday users each scanning 2 TB of raw JSON is 80 TB. At the illustrative $5 per TB, that is $400 for one meeting, and it gets worse if the dashboard refreshes again before lunch. The same forty users scanning 5 GB each of the mart is 0.2 TB, about a dollar at the same illustrative rate. The ratio, roughly a hundred to one in this sketch, is the design lesson. Swap in the real price per TB and the ratio stays if the byte counts stay. Fix the bytes.

A warehouse that is busy only because the SQL reads raw. Capacity will scale up to read the same bad layout, and you will pay C times hours for a scan the mart would have finished on a small reservation. Layout first, then size the capacity to the pruned query.

Break-even in one sentence: if the monthly slot or node bill for the board is larger than the on-demand scans of the pruned mart at real Monday concurrency, and the SLO still holds on-demand, the reservation baseline is too high or the warehouse is idle. If on-demand latency or queuing breaks 06:00, the capacity is the product, and shrinking it to win the spreadsheet breaks the promise lesson 62 will write down.

Caches and materialized views miss more often than the demo

A result cache hit needs the same statement and underlying data that has not changed, within the engine's cache window. Monday's query with today's date is a different statement from last Monday's. It misses. A dashboard that embeds CURRENT_DATE() can miss on every refresh if the engine treats it as non-deterministic. Do not put a cache hit rate of ninety percent into the budget for a parameterized date. Budget the dry-run bytes of a miss. Treat cache hits as a bonus on the repeated refresh of the same day.

A materialized view pays at refresh and saves at read. Six board tiles that each join the fact before standup can be one refresh plus six cheap reads. If refresh takes longer than the margin before 06:00, the view is a late board, not a saving. Refresh on the ELT reservation, read on the board reservation. A view that computes its own completion rule is a second mart. It must read the published grain.

BI extracts, BI Engine, and dashboard caches are the same idea one tier up. Invalidate them when definition_version changes. A cached extract of completion-v3 served after the cutover to v4 is a cheap wrong number. Wrong is more expensive than the scan.

Isolation is a cost control

ELT and the board should not share an unconstrained pool. A merge at 05:40 that can take every slot will make the board late and will tempt you to raise the maximum, which raises the possible bill. Give the merge a ceiling and a start time that finishes with margin. Give the board a baseline that covers forty readers of the pruned query. Exploration goes to on-demand or to a cutoff workgroup so a curious SELECT * cannot scale the board's reservation.

Spark and Dataflow have the same shape. A maximum number of workers is a cost ceiling and a skew alarm. An unbounded maximum is a bet that the data has no hot key. The progress topic's worst hour is that bet losing.

Movement and safety copies

Cross-region replication of raw "for safety" stores a second copy and charges transfer. It is justified after a restore game day has a person, a timestamp, and a success. Until then, versioning and a lifecycle in the primary region may be the honest control, and lesson 57's key policy is what stops a delete. Replication does not fix a job that writes the wrong definition. It copies it.

Audit logs from lesson 57 belong on this sheet as a number. Vault data-access logs are worth their price. Progress GET logs for every object are a line you either accept or decline in writing. Leaving them on by accident is how a safety control becomes the third-largest invoice line.

Architecture and Design Diagrams

Money follows the read path. The dashed path is the one the board used to take, and the one the cutoff should now reject.

mermaidCopy
flowchart LR
  RAW["Raw JSON or tiny files"] -->|"wide scan, many readers"| BILL["Scan or slot spike"]
  RAW --> COMPACT["Compact Parquet by event_date"]
  COMPACT --> MART["Native mart or warehouse table"]
  MART -->|"pruned aggregate"| BOARD["Monday board"]
  MART --> ELT["ELT reservation or pool"]
  BOARD --> SLOTS["Board reservation or pool"]
  ELT --> SLOTS
  EXP["Exploration cutoff"] --> COMPACT

The publish clock and the idle clock are different. Capacity exists only inside the window the SLO requires, plus the resume margin.

mermaidCopy
flowchart LR
  subgraph monday [Board window]
    RESUME["Resume or raise baseline"]
    LOAD["Merge finishes with margin"]
    READ["Forty readers"]
  end
  subgraph quiet [Rest of the week]
    LOW["Baseline at the quiet floor or paused"]
  end
  RESUME --> LOAD
  LOAD --> READ
  READ --> LOW

If pause cannot finish resume before 06:00, the quiet box stays at the minimum that still meets the SLO, and the cost sheet calls that minimum the price of freshness rather than waste.

Technical Explanation

How a pruned scan actually shrinks

A Parquet file stores statistics and column chunks. A query that names five columns reads those chunks, not the rest of the file. A partition predicate skips files whose paths or metadata lie outside the date range. A cluster or a sort key skips blocks inside the remaining files when the predicate is selective. Each of those skips has to be visible to the planner. A filter on DATE(event_time) when the partition column is event_time can hide the column from pruning. A filter applied in a BI tool after it has selected every column of every day does the prune too late. The bytes have already been processed.

Dry runs and history are the instruments. Athena records data scanned in the query execution. BigQuery's dry run returns totalBytesProcessed without running the query. Synapse serverless shows data processed. Redshift EXPLAIN and the query history show scanned rows and blocks, which you read against the sort key, not against a directory listing. Keep one week's board query in the review with its byte count. A jump of ten times is a layout or a SQL regression, and it is cheaper to catch in review than in the invoice.

What small files do to the bill and to the clock

Listing ten thousand objects is an API cost and a scheduler cost. Each file's footer is a read. Each file may become a task, and tasks have startup time that dominates when the file holds a few megabytes. The scan price can rise because compression and row-group pruning work better on larger row groups than on a stream of tiny ones. COPY into a warehouse spends the same overhead: too many files, idle slices, a load that misses its window, and then a warehouse that stays scaled up while you stare at it.

The repair is compaction to the size range, then a publish that points at the compacted files only. Deleting small files first creates a window where the day is short. The cloud lessons already required the new files to be the published ones before the old ones go. That order is a cost lesson too, because a restore of a broken day is the most expensive compute you will run.

Capacity math without fake precision

Let C be the hourly price of the board's capacity unit, taken from the current card. Let H be the hours it must exist to hit 06:00 including resume and the morning read. Let H_idle be the other hours in the month if you leave it up. The idle waste is C * H_idle when those hours serve no SLO. The useful spend is C * H. A design that cannot pause still owes C * H_idle only if the SLO truly requires the warm capacity. Write that sentence next to the number. Teams cut H and miss the board, or they leave H_idle and call it reliability. Lesson 62 is where the sentence becomes an SLO. This lesson is where the sentence becomes visible.

On-demand comparison uses measured bytes, not a guess. bytes_per_refresh * refreshes * readers * price_per_byte. Cache hits may reduce refreshes. Assume zero hits for the first refresh of a new date. If that product is far below C * H and latency is inside the SLO, the capacity is a convenience you should justify. If the product is similar and the queueing is not, keep the capacity and cut the baseline outside H.

Spark DPU-hours, Dataflow worker-hours, and mapping data flow debug sessions are the same formula with a different C. A debug session left warm overnight is H_idle with no board attached. It is the clearest waste in the module, because nothing depends on it.

Knobs worth turning, and knobs that lie

Turn these first, in order, because they change bytes or hours rather than hiding them.

1. Publish the board against the mart, with a date predicate the planner can see.
2. Compact files into the size range before repeated reads and before loads.
3. Project columns. Ban SELECT * in the certified query.
4. Isolate reservations or workload groups. Cap workers.
5. Pause or lower baselines outside the SLO window, after you time the resume.
6. Refresh one materialized view for the repeated tiles, reading the published definition.
7. Expire results and drop the second mart.

Knobs that lie, or that trade the wrong thing:

• Raising cluster size to finish a JSON scan, instead of stopping the scan.
• Partitioning by hour to look sophisticated, then listing thousands of files.
• Caching a dated dashboard and booking the cache as the steady state.
• Turning off a grain check or a forbidden-column check to save a minute. The minute returns as a wrong publish, which is a rerun at the worst hour.
• Coalescing an entire day to one file to "save storage." Storage was not the meter. The next job now runs single-threaded.
• Compressing with a heavier codec that pushes the merge past 06:00. Bytes went down. The SLO went with them. Measure both.

Freshness is a constraint on the bill

The mart's promise is 06:00 with definition_version pinned. Any saving that moves the publish later, serves a stale extract after a definition change, or drops the duplicate check, is refused. The tile's five-minute provisional promise is a different budget. It may use a smaller maximum and a landing table. It may not write the certified grain to save a merge. Lesson 47 separated the cursors. This lesson separates the budgets. Lesson 62 will name the error budget. You should arrive there knowing which line on the invoice is the one you are willing to defend in a review.

Practical Implementation or Design Walkthrough

Problem

TechLearningPro's last month: the board SQL reads raw JSON for seven days, forty readers, one refresh each, no cache assumed. A warehouse used by almost nobody outside the morning has been up all month. Compacted Parquet for the mart exists and was not the source of the board. Build the cost sheet, point the board at the mart, and state what you will not turn off.

Illustrative scan price: $5 per TB. Illustrative hot storage: $0.02 per GB-month. Capacity price C per hour, from the live card, not invented here.

Measure the bad query before you change it

On BigQuery, dry-run the board statement and record totalBytesProcessed. On Athena, read data scanned from the completed query in a workgroup you are allowed to use for measurement, with a cutoff so the measurement itself cannot scan the year. On a warehouse, capture EXPLAIN and the bytes or rows scanned for the same SQL. The number you need is bytes or blocks of raw JSON versus bytes or blocks of the mart for the same business question.

A sketch you can drop measured numbers into:

pythonCopy
"""Illustrative only. Replace price_per_tb and the byte counts with measurements."""

def scan_cost_usd(terabytes: float, readers: int, refreshes: int, price_per_tb: float) -> float:
    return terabytes * readers * refreshes * price_per_tb


def capacity_cost(hourly: float, hours: float) -> float:
    return hourly * hours


RAW_TB = 2.0          # one reader's raw JSON scan, measured
MART_TB = 0.005       # one reader's pruned mart scan, measured
READERS = 40
REFRESHES = 1
PRICE = 5.0           # illustrative USD per TB

raw_monday = scan_cost_usd(RAW_TB, READERS, REFRESHES, PRICE)
mart_monday = scan_cost_usd(MART_TB, READERS, REFRESHES, PRICE)
print(f"raw_monday={raw_monday:.2f} mart_monday={mart_monday:.2f}")

With those inputs the raw Monday is $400 and the mart Monday is $1. The inputs are a teaching sketch. Your dry run replaces RAW_TB and MART_TB. If your mart scan is 50 GB because the table is young and wide, the mart Monday is still far under the raw scan, and you write the measured number, not this one.

Point the board at the pruned grain

The certified statement names columns and filters event_date directly.

sqlCopy
SELECT lesson_id, COUNT(*) AS attempts
FROM published.completion_attempt
WHERE event_date BETWEEN DATE '2026-11-03' AND DATE '2026-11-09'
  AND definition_version = 'completion-v4'
GROUP BY lesson_id;

Run the dry run again after the change. The review artifact is the before and after byte counts, not a screenshot of a faster-looking chart. If the after count does not drop, the view published.completion_attempt is not the partitioned table, or the filter is wrapped. Fix that before you talk about reservations.

Capacity sheet

Fill C and H from the account. A sheet that says "optimize the warehouse" and leaves H_idle blank has not started.

File layout check

For the published day, record object count and average size. If the average is well under 128 MB or the count is in the thousands, schedule compaction before the next board, and rerun the dry run. If the average is multi-gigabyte, split so a retry does not reread one huge object. The Spark lesson's guidance on file size is the same target. One number, used by the lake and by the warehouse load.

What you will not trade

Write these refusals on the sheet:

• The grain check and the prohibited-column check stay, even if they add minutes.
• definition_version stays in the filter. A cache of an older version is a defect.
• The board does not regain access to raw JSON to "save the warehouse bill."
• Resume time is measured. If it threatens 06:00, you do not pause that capacity to win H_idle.

Enterprise Real-World Scenarios

TechLearningPro's Monday is a scan, and the nights are a cluster

The invoice has two surprises. On-demand scans spike on Mondays, and a provisioned warehouse or a high slot baseline is almost flat across the month. The scans are the raw JSON dashboard. The flat line is capacity that nobody uses after 10:00. Investigators already have a cutoff workgroup, which they bypass by querying in the production project.

The repair order is the walkthrough order. Move the board SQL to the mart and keep the before-and-after bytes. Move exploration out of the production reservation. Time a pause and a resume on a Tuesday. If resume fits, pause outside a window that still covers 06:00 with margin, and leave the ELT job on its own ceiling in the early morning. If resume does not fit, lower the baseline to the measured Monday need and stop calling the overnight hours a mystery. Compact the landing that still feeds the mart so the merge's hours fall. Drop the second engine's scheduled refresh if a pilot from lesson 54, 55, or 56 left one running. The reopen condition can stay in the decision record without a nightly job.

The first Monday after the change should show a scan line near the mart estimate and a capacity line that matches H or the reduced baseline. If the scan line does not move, the dashboard is still pinned to the old SQL, probably inside a BI extract. That extract is the next deletion, after you confirm definition_version.

Northline pays for two closes

Northline's portal is on a dedicated SQL pool or a BigQuery reservation with row-level security, which they need. A second warehouse was loaded "so finance can close without disturbing the portal." Both run all month. Both contain order_line. Refund logic has already drifted by one status code.

Finance does not need a second engine. Finance needs a published monthly table, a definition version, and a workload group or a reservation that cannot be starved by the portal and cannot starve it. The close query runs once. It can even be scan-priced if it is a single pruned monthly aggregate. The portal needs the interactive capacity. Turning the second warehouse off removes C * hours and removes the drift. The row-level test from lesson 57 still runs on the one table that remains. A cost cut that drops the security policy is not a cost cut this course will sign.

Separately, Northline replicates raw orders to another region because a postmortem slide said "multi-region." Nobody has restored it. The sheet either gains a quarterly restore drill with an owner, or the replication stops and versioning plus the key policy from lesson 57 cover the delete scenario they actually fear. Copying a wrong refund rule to a second region would have replicated the bug faster. It would not have found it.

Best Practices

• Measure bytes or blocks for the certified query before you resize anything. Dry runs are cheaper than heroics.
• Put the board on the partitioned mart. Leave raw behind a cutoff and a date predicate.
• Partition by the filter you have, usually the day. Cluster or sort inside it. Do not partition by learner_id.
• Compact toward a few hundred megabytes per Parquet object before repeated reads and before COPY.
• Price capacity as C * H plus C * H_idle, and name which hours the SLO truly needs.
• Isolate ELT, board, and exploration. Cap workers and reservation maximums.
• Assume the first query of a new date misses the result cache. Budget the miss.
• Refresh materialized views from the published definition, inside the morning margin.
• Put second regions, second marts, debug sessions, and full GET audit logs on the sheet with owners, or turn them off.
• Refuse savings that skip the grain check, the column gate, or the 06:00 margin. Take those constraints to lesson 62 as inputs, not as leftovers.

Common Mistakes and Anti-Patterns

Resizing the warehouse to survive a raw JSON scan. You pay C to read text forty times. Point the query at the mart. Then size capacity to the pruned plan.

Hourly partitions on a daily board. Thousands of small prefixes, a slower planner, and no query that needed the hour. Partition to the day. Cluster or sort if a lesson lookup inside the day is selective.

Tiny landing files left for the board to read. List and footer overhead, slow COPY, a merge that misses 06:00, and a cluster you then leave up. Compact first.

Booking cache hit rates for a query whose date literal changes. Monday misses. Budget the miss. Let same-day refreshes be the bonus they are.

Two certified marts after a cloud bake-off. Double capacity and drifted definitions. One owner, one nightly job. The loser of the bake-off is a document, not a schedule.

A baseline sized for Monday, left there on Saturday. H_idle dominates. Pause or lower it when resume fits. If it does not fit, say so next to the SLO.

One file per day to save pennies of storage. Storage was the small meter. You serialized the reader. Stay inside the size range.

A heavier codec that pushes publish past 06:00. Bytes fell and the board is late. The late board is not a saving. Measure the clock and the bytes together.

Turning off checks to make the window. The grain check and the prohibited-column gate are cheaper than a wrong publish and a rerun during the meeting.

Cross-region replication with no restore drill. You pay transfer and a second copy and you still do not know you can come back. Test a restore or stop replicating.

Full object-level audit on the progress prefix, inherited from a security ticket that did not name a dataset. Lesson 57 scoped the vault on purpose. Relitigating that by enabling every GET is a cost incident. Change the sheet explicitly.

Troubleshooting and Design Review

The scan bill moved and nobody changed the cluster size. Look at query history for the board identity. You will usually find SELECT * on raw, a missing date predicate, or a BI tool issuing one query per tile per user. The dry-run bytes of each statement are the ranking. Fix the top statement. Do not average it away.

The mart query is still expensive. Check partition pruning first, then columns, then file sizes, then a join back to raw that pulls the big side into the scan. A view that the board trusts might embed the join. Read the expanded SQL.

COPY or the merge misses the window. Count files and look at warehouse queue time. Tiny files and a shared pool with the board are the usual pair. Compact, and run the load in its own queue or reservation earlier.

The reservation or the pool is busy at midnight. A schedule you forgot, a debug session, a second mart, or a baseline that never scaled down. The midnight query text tells you which. Pause the one that has no SLO.

Dashboard latency improved and the number is wrong. A cache or an extract is serving an old definition_version. Invalidate on version, not only on a timer. Speed without the version pin is a defect.

Storage grew faster than 40 GB a day. Small-file debris, a results prefix, a replication destination, or a landing that is never compacted and never expired. Lifecycle and the second region belong on the sheet. The mart itself rarely explains a surprise storage line at this volume.

Costs fell and 06:00 slipped. You cut H below resume plus merge plus read. Put the hours back. Then cut H_idle only. A reliability review that starts from the invoice will make this mistake. Start from the SLO window, then delete what is outside it.

Design review:

• What are the measured bytes of the certified query, before and after layout?
• What is H versus H_idle, and which SLO sentence justifies H?
• How many engines publish completion-v4 tonight?
• What is the average Parquet object size of the published day?
• Does the first Monday refresh assume a cache hit?
• Which safety copy has a restore test?
• Which check would you refuse to remove even if it saved a capacity hour?

Interview Questions and Answers

Q1. Why can the smaller table cost more to query?

Because the bill follows bytes processed or capacity held, not bytes stored. A compact columnar mart, partitioned by date, with the query naming its columns, processes a thin slice. A smaller-looking extract that is actually JSON, or a mart scanned with SELECT * and no date filter, can process orders of magnitude more. I compare measured bytes for the same business question. I do not compare table size in the catalog and call the smaller one cheap.

Q2. When would you partition progress by hour instead of by day?

When a real consumer filters to an hour and the hour is large enough that a daily partition would still scan too much. The Monday board filters by day. Hourly partitions would add thousands of small files over a year and would not change that query's predicate. I partition by event_date. If a lesson lookup inside the day is selective, I cluster or sort. I do not partition by learner_id.

Q3. How do you compare on-demand scans with a reservation for a Monday spike and a quiet week?

I measure bytes for one pruned board query and multiply by readers and by refreshes, assuming the first refresh of a new date misses the cache. That is the on-demand side, at the real price per TB. I multiply capacity price C by the hours the SLO needs, including resume, and I look at C times the other hours separately as idle. If on-demand meets latency, the baseline is a choice. If forty concurrent readers queue past 06:00, the capacity is required and I still delete H_idle. I do not compare a raw JSON scan with a reservation and conclude I need a larger warehouse.

Q4. What does a result cache miss when the dashboard changes the date?

The statement changed, or a non-deterministic clock function changed the key. Engines cache identical work against unchanged inputs. Next Monday is not identical to this Monday. I budget bytes for a miss. Same-day refreshes may hit and that is upside. A plan that needs a ninety percent hit rate to look affordable is a plan that fails on the first refresh, which is the one in front of the board.

Q5. How do small files become money, not just a slogan?

Each object is listed, opened, and often scheduled as a task. Footers and tiny row groups add reads and startup time. The merge misses its window, and the warehouse stays up while people debug. On a scan-priced engine the layout also reads worse than a few hundred megabyte files with real row groups. Landing tools emit small files because they flush on a timer. Compaction before repeated queries is the control. I watch file count and average size for the published day.

Q6. Why is a second warehouse for the same mart a correctness cost as well as a compute cost?

You pay two capacities or a capacity and a scan, and you now have two places where completion-v4 can drift, the way Northline's refund status drifted. Incident response will not know which number is certified. One publisher, one serving engine. The other engine can remain a written reopen condition. A nightly job on both is how the bake-off never ended.

Q7. How do you explain Redshift sort keys to someone who has only used Hive partitions?

Hive-style partitions skip directories whose names fail the predicate. Redshift stores the table in distributed blocks and uses a sort key and zone maps to skip blocks. A compound sort on event_date lets a date filter skip blocks without a directory per day. DISTKEY is about join locality, not about date pruning. I show EXPLAIN rather than a folder tree. If the table is an external Spectrum table, then the Glue partitions behave more like Hive, and that is a different object from the native mart.

Q8. When is inter-region replication not yet a backup?

When nobody has restored it. You are paying storage and transfer for a copy you have not proven you can use. Replication also copies bad data faithfully. I keep it when a named owner runs a restore drill and the drill meets the recovery objective. Otherwise versioning, lifecycle, and the key policy cover the failures we have actually rehearsed. Hope is not a line item.

Q9. When does a materialized view pay for itself on this board?

When several tiles repeat the same heavy aggregate, the view reads the published grain and definition version, and the refresh finishes inside the margin before 06:00. One refresh replaces several scans. If refresh is late, the view hurts the SLO. If the view encodes its own completion rule, it is a second mart. I would rather change the certified SQL than add a view that cannot be refreshed in time.

Q10. Which performance knob do you refuse to turn because of the freshness promise?

I refuse to drop the grain check, the prohibited-column gate, or the partition filter to buy minutes. I refuse to serve a cached extract across a definition_version change. I refuse to pause capacity when the measured resume misses 06:00. I refuse to let the provisional tile write the certified table so we can skip the merge. Those are correctness and freshness, not stinginess. Lesson 62 can tighten the SLO. It should not inherit a platform that already traded the publish away.

Q11. The storage line is growing and someone wants lifecycle rules the same day. What do you check first?

Whether the growth is the mart, which at 40 GB a day of Parquet should be predictable, or debris: uncompacted landings, query results, debug outputs, or a replica. Lifecycle on the mart can delete history the product still needs. Lifecycle on results and on abandoned landings is usually the right same-day change. I put the replica's restore test on the sheet before I defend its growth as safety.

Hands-On Exercises

Exercise: A one-month cost sheet for the completion board

Problem statement. You are given query history, or a sample you construct, for a board statement that scans raw data and a mart statement that answers the same question with an event_date filter. You are also given a capacity that runs 730 hours. Produce the cost sheet and the revised board SQL. Use illustrative prices only if you label them, and leave a blank where the live price card belongs. Do not recommend a second mart.

Tasks.

1. Compute the Monday scan cost before and after the SQL move, showing readers, refreshes, and bytes. State the cache assumption.
2. Split capacity hours into H and H_idle. Say whether you would pause, and what resume measurement you need first.
3. Inspect a day's file sizes, real or synthetic. Recommend compact, split, or leave, against the 128 MB to 512 MB range.
4. Identify one safety copy or audit log line and either assign a restore or review owner, or turn it off in the proposal.
5. List the checks and the freshness window you will not trade for a lower C.
6. Write the single serving engine and the reopen condition you inherited from lessons 54 through 56.

Expected deliverables. Before-and-after bytes, capacity split, file-size note, safety-copy decision, refusal list, engine choice.

Evaluation rubric.

Lesson Summary

Storage at this volume is usually the quiet meter once you stop keeping two hot copies of everything. Scans and idle capacity are the loud ones. A raw JSON read for forty people dominates a Monday. A pruned, columnar, daily partitioned mart makes that scan small. A cluster or a reservation left at Monday's size all month dominates the nights. Partition for the filter you run, compact files into a few hundred megabytes, project columns, and isolate the merge from the board. Budget a cache miss when the date changes. Price a second region and a second mart as choices with owners, not as leftovers of a pilot.

The ratios in this lesson used illustrative storage and scan rates so the design would survive a price-card update. The hours and the byte counts are what you measure in the account. Replace C before you call it a budget.

DataOps and Data Platform Engineering is the next module's start: the decision records, the control sheet, and this cost sheet become a platform you can change without a hero. Data Reliability and SLA/SLO Design writes the 06:00 promise that tells you which of these knobs are available. The capstone has to pick one cloud and one serving engine. It already has the reopen conditions, the identity sheet, and the line items. The remaining work is to keep a single publisher when the invoice gets loud.

Further Reading

• Amazon Athena pricing and workgroups for bytes scanned and per-query cutoffs, and the S3 storage class pages for lifecycle.
• Amazon Redshift sort keys, distribution, WLM, and Serverless RPU billing in the current price card.
• Azure Synapse dedicated SQL pool distribution, workload groups, and pause, plus serverless SQL data-processed billing.
• BigQuery partitioned and clustered tables and reservations, plus dry runs that return totalBytesProcessed.
• Apache Parquet documentation for row groups and compression, with the Spark performance lesson for how file size meets execution.
• The current public price cards for the cloud you are on. This lesson's dollar figures are illustrative ratios, not quotes.
• Lessons 54 through 57 for the single-engine decision, the audit gap, and the freshness cursors this sheet is not allowed to spend.