Cloud Fundamentals

The reasoning

A data lake has to hold years of files for many engines — Spark, a warehouse, a query service — at a low price. Object storage gives exactly that: you pay per gigabyte stored and per request, it grows without capacity planning, and the providers design it for very high durability by keeping copies across devices and zones. Because the files are separate from any cluster, you can switch the cluster off and the data stays.

What it does not give you is a file system. An object is written whole and replaced whole; you cannot append a line to it or change a byte in the middle. "Folders" are only key prefixes, so listing a large prefix is slow and costs requests. On S3, and on Cloud Storage buckets without the hierarchical namespace option, renaming a "folder" of 10,000 files is 10,000 copies and 10,000 deletes, not one quick step. ADLS Gen2 with the hierarchical namespace turned on is the exception: it has real directories with atomic renames, which is one reason Azure lakes use it.

For a pipeline this means: write each run to a new, unique path (one folder per run or per date partition), check it, then switch readers to it in one step — through a table format such as Delta Lake or Iceberg, a catalog update, or a success marker file. Never overwrite files that readers are reading. S3 has had strong read-after-write consistency since December 2020, so the old "the listing is out of date" problem is gone; the lack of atomic changes across many files is not.

The reasoning

Partitioning puts rows into separate folders by a column value, for example orders/event_date=2026-09-01/. Athena, BigQuery external tables, Spark and Synapse serverless read the folder names and skip the folders the filter rules out. Choose the column almost every query filters on — usually a date — and keep the number of partitions sensible: partitioning by customer_id with millions of values creates millions of tiny folders and makes everything slower, not faster.

The file format matters as much. Parquet and ORC store data by column, compress it well and keep small statistics per block, so a query that needs 3 of 40 columns reads roughly those 3, and can skip blocks whose minimum and maximum cannot match the filter. CSV and JSON have to be read in full.

Then file size. Thousands of files of a few kilobytes each mean thousands of requests and a fixed overhead per file — the small-files problem — and streaming or very frequent loads cause it. Write bigger files, or compact the small ones on a schedule: OPTIMIZE in Delta Lake, or a job that rewrites a partition into fewer files. Files of roughly 100 MB to 1 GB are a good target for most engines.

The reasoning

Each cloud has the same ladder under different names. AWS S3 has Standard, Standard-Infrequent Access and the Glacier classes; Google Cloud Storage has Standard, Nearline, Coldline and Archive; Azure Blob Storage has the Hot, Cool, Cold and Archive tiers. Going down the ladder, the price to store a gigabyte falls while the price to read it — and sometimes the time to get it back — rises.

Two catches decide the design. First, minimum storage durations: Cloud Storage Nearline is charged for at least 30 days, Coldline 90 and Archive 365, and S3 Standard-IA for 30, so moving data you will delete next week into a cold class costs more, not less. Second, archive classes can take hours to restore, so they suit data you must keep for law or audit, not data an analyst may query tomorrow.

A lifecycle rule is a policy on the bucket: "after 30 days move to the cool class, after 90 to cold, after 365 to archive, delete after 7 years". For a lake read heavily for a week, sometimes for a quarter and almost never after a year, that cuts the storage bill a great deal without any code. Apply it to raw and log data; keep the curated tables that dashboards read in the standard class.

The reasoning

When you load data into a warehouse, it rewrites the data into its own storage format and keeps statistics, so it can prune, cache and spread the work well. BigQuery native tables, Redshift tables and Synapse dedicated SQL pool tables work this way. You pay for that storage and for the load, but repeated queries are fast and predictable.

An external table is only a definition — a schema and a location — over files in S3, Cloud Storage or ADLS. Athena, Redshift Spectrum, BigQuery external tables and Synapse serverless SQL pools read those files when the query runs. Nothing is copied, so the lake stays the single source and there is no load to keep in sync, but speed depends on the file layout and some features (caching, constraints, fast updates) are missing or limited.

A common pattern uses both: raw files and long history stay in the lake as external tables, while the cleaned and modelled tables that dashboards hit all day are loaded into native tables. The deciding questions are how often the data is queried, how fast the answer must be, and whether a second copy is worth its storage.

The reasoning

In an older warehouse the disks sat inside the nodes, so more data meant more nodes even if nobody queried it, and more users meant fighting over the same machines. With storage and compute separated, the data sits in a central storage service, and compute — clusters, warehouses or "slots" — reads from it.

Two things follow. Cost: storage is billed per gigabyte per month and compute per second or per query, and compute can be scaled down or paused at night. Concurrency: the BI team and the data science team can get separate compute on the same data, or the warehouse can add compute when many queries arrive at once — Redshift calls this concurrency scaling, BigQuery gives a query more slots, Snowflake starts extra clusters in a multi-cluster warehouse.

The trade-off is that data has to travel from storage to compute, so warehouses cache data near the compute and depend on columnar storage and pruning. Good layout — partitions, clustering, sort keys — still decides how much has to be read.

The reasoning

Athena charges per terabyte scanned, with a 10 MB minimum per query, and BigQuery on-demand charges per tebibyte processed. A LIMIT 10 at the end does not reduce the scan: the engine still reads everything the query needs before it can return ten rows.

Three things cut the scan. Partition pruning: if the table is split into date folders and the query filters on that date column, only those folders are read. Column pruning: in Parquet or ORC, selecting 3 columns reads roughly those 3, while SELECT * reads them all — and in CSV every query reads every byte of every row. Compression: columnar files compress well, and the engine bills the bytes it actually reads.

Watch the trap: the filter must be on the partition column itself. A BigQuery table partitioned on event_date but queried with WHERE DATE(event_ts) = '2026-09-01' can read every partition. For predictable spend, teams also set limits — a maximum bytes billed per query in BigQuery, workgroup limits in Athena — or move to reserved capacity when usage is steady.

The reasoning

A serverless function starts on an event (a file lands, a message arrives), runs your code and stops. You pay per call and per second of memory, there is nothing to manage, and it starts quickly. That makes it good for small parsing, validation, moving and routing work. The limits are real: each function has a capped run time and memory (on AWS Lambda, 15 minutes and up to 10,240 MB), runs on one machine, and at high volume many small calls can cost more than one steady job.

Spark, through Glue, EMR, Dataproc or Databricks, splits the work across many machines and moves data between them, which a join of two large datasets needs. Starting compute takes time and has a minimum charge, so it is wasteful for a 50 MB file but right for terabytes.

So for the example: the 50 MB JSON every 10 minutes goes to a function (or a small serverless job) that validates it and writes Parquet to the lake; the nightly 2 TB join runs as a Spark job, ideally on serverless Spark or on a cluster created for the run and removed afterwards.

The reasoning

A cluster that runs 24 hours for a 2-hour nightly job bills 22 idle hours. The standard fix is an ephemeral cluster: the orchestrator creates the cluster, runs the job and deletes the cluster — also when the job fails. On Databricks the same idea is a job cluster instead of an all-purpose cluster, which is also cheaper per unit. Clusters people use interactively should shut down by themselves after a period with no activity.

Within a run, autoscaling adds workers when there is work and removes them when there is none, and spot or preemptible machines lower the price of workers whose loss Spark can retry. Keep the driver on a normal machine, because losing it fails the job.

Serverless Spark goes one step further: you submit the job and the service provides and removes the machines. It suits scheduled jobs that do not need a tuned, long-lived cluster. A cluster you manage still makes sense when you need special libraries, steady performance or many jobs back to back.

The reasoning

In the cloud, people and programs are both identities, and permissions are attached to identities through policies or role assignments. A pipeline should not borrow a person's account or a static access key. Instead, a Glue job or an EMR cluster assumes an IAM role, a Dataflow or Dataproc job runs as a service account, and Data Factory or a Databricks workspace uses a managed identity. The platform gives the job temporary credentials that expire and rotate on their own.

Least privilege means the policy allows only the actions and resources the job needs — "read objects under s3://lake/raw/orders/" rather than "full access to S3". A separate identity per pipeline keeps a bug or a leak in one job from reaching everything, and the audit logs then show which job did what.

Two details often come next. On Azure there can be two layers: RBAC roles control access to the storage account, and ACLs on ADLS Gen2 folders control access inside it. And encryption keys are resources too: a job that reads data encrypted with your own key also needs permission to use that key.

The reasoning

A secret store keeps secrets encrypted, controls who may read each one, logs every access and can rotate some secrets automatically. The job's identity (role, service account or managed identity) gets permission to read only the secrets it needs. Airflow secrets backends, Data Factory linked services and Databricks secret scopes can all point at the store instead of holding the value.

Moving out is a small project: list every place a secret appears (configs, notebooks, environment variables, the repo history), create each one in the store, change the code to fetch it, and give each job identity read access to only its secrets. Then rotate every secret that was ever written in code, because old commits and logs still contain the value.

Where you can, remove the secret altogether: connecting to cloud storage or a cloud database with the job's own identity needs no password at all. And secrets must never be printed in logs — mask them in the orchestrator.

The reasoning

Encryption at rest means the stored bytes are encrypted, so a lost disk or a copied file is useless without the key. S3, Cloud Storage, Azure Storage and the cloud warehouses encrypt data at rest by default. Encryption in transit means connections use TLS, so the data cannot be read on the network; you make sure every client — database drivers, APIs, JDBC connections to the warehouse — uses it.

With provider-managed keys you get encryption with no work, but you do not control the key. A customer-managed key lives in a key management service in your account: you decide who may use it, every use is logged, you set the rotation, and disabling it makes the data unreadable. Regulated companies often require this for sensitive data — and jobs then also need permission to use the key.

Encryption does not replace access control. If a user or a job may read the table, the platform decrypts the data for them. Hiding sensitive columns from people who can read the table needs masking, column-level permissions or tokenisation, which is a separate question.

The reasoning

Copying the table into one version per team works for a week and then drifts: the copies go stale, and someone forgets to filter a new column. Fine-grained access control keeps one table and decides what each reader sees.

For columns, you either deny the column (the risk team sees it, others get an error or do not see it) or mask it (others see the last four digits or a hash). AWS Lake Formation grants columns and rows on Glue catalog tables for Athena, Redshift Spectrum and EMR; BigQuery uses policy tags on columns and row access policies on rows; Databricks Unity Catalog has column masks and row filters; Synapse dedicated SQL pools support column-level and row-level security.

For rows, a row filter keeps only the rows where country matches what the reader is allowed. Grant both rules to groups (risk_team, india_analysts), not to named people, so joining or leaving a team is only a change of group. Test by querying as a member of each group, and keep the audit logs.

The reasoning

A Kinesis shard accepts up to 1 MB or 1,000 records a second for writes. 15,000 events a second at 2 KB each is about 30 MB a second, so provisioned mode needs at least 30 shards — or on-demand mode, which adds shards for you. Event Hubs partitions work the same way, and each consumer reads a partition in order and records its position (an offset or sequence number).

The partition key is hashed to choose the partition. Use a key whose events must stay in order, such as device_id or order_id: all events for that key land in one partition, in order. A poor key — one value for most events — sends everything to one partition, which then throttles while the others sit idle. That is a hot partition.

Pub/Sub has no partitions to manage: it scales by itself and does not keep order by default. With an ordering key, messages that share the key are delivered to a subscriber in order. In every one of these services, order holds only within one key or partition, never across the whole stream.

The reasoning

A consumer usually writes the event and then acknowledges it, or checkpoints its position. If it crashes between the two, the service believes the message was never handled and delivers it again, so it is written twice. Producer retries can create duplicates too. This is normal behaviour, not a bug in the service.

The fix is on the writing side. Each event needs a stable unique id from the producer (a payment_id or event_id, not the arrival time). Then either write with an upsert keyed on that id (MERGE into the table), or land everything in a raw table and build the clean table with a step that keeps one row per id — for example ROW_NUMBER() over the id. Some services offer exactly-once inside their own pipeline, such as Pub/Sub exactly-once delivery or Dataflow's exactly-once processing, but once data crosses into another system you still need the idempotent write.

To clean up what is already there, run the same deduplication over the affected hour and replace that hour's partition. Then add a check that counts duplicate ids in each load, so the next time is caught at once.

The reasoning

A full load copies every row once. For big tables it runs in parallel and can take hours while the database keeps changing, so CDC starts from the point in the log where the full load began; the changes made during the load are applied on top, and nothing is lost.

Log-based CDC reads the database's own transaction log instead of querying the tables, so it catches deletes and every change without loading the source. It needs settings on the source: binary logging in ROW format on MySQL, logical replication on PostgreSQL, supplemental logging on Oracle, and enough log retention for the service to catch up after a pause. The query-based alternative — select rows whose updated_at is newer than the last run — is simpler, but it misses deletes and depends on that column always being set.

On the target, changes usually land in a raw table with the operation type and a sequence number, and a MERGE applies them to the current table: insert new keys, update changed ones, delete removed ones, always applying the latest change per key. Watch the replication lag, so you know how far behind the warehouse is.

The reasoning

Bulk load commands read files straight from S3, Cloud Storage or ADLS and spread them across the warehouse's compute. They work best with many medium-sized, compressed or columnar files rather than one huge CSV, because each file can be read in parallel. In BigQuery, batch load jobs from Cloud Storage are free — you pay for the stored data — while streaming inserts are charged.

Streaming inserts, or a streaming write API, make rows available within seconds and are for real-time needs. For a daily 5 GB file they cost more and add nothing.

The load must be safe to run twice. Load the day's files into a staging table, check them (row count against the source, no nulls in keys, no duplicates), then publish: replace that day's partition in the target, or MERGE on the business key. If the job fails halfway, you rerun it and get the same result, not double rows.

The reasoning

With cron, each script stands alone: if the extract fails, the transform still starts at 03:00 on yesterday's data, nobody is told, and there is no record of what ran. Rerunning last Tuesday means editing dates by hand.

An orchestrator models the pipeline as a graph — a DAG in Airflow, a pipeline in Data Factory, a state machine in Step Functions. Tasks run in order, a failure stops the tasks that depend on it, retries with a delay ride out short outages, sensors or triggers wait for a file or an upstream job, and every run is logged with its inputs. Backfilling or rerunning a date becomes a normal operation — as long as each task is idempotent.

The managed services — MWAA and Cloud Composer run Apache Airflow; Data Factory and Step Functions are the clouds' own — look after the servers, upgrades and scaling of the orchestrator. You still own the pipeline code, the alerts and the cost of what the tasks run.

The reasoning

First, failures: every job and task reports success or failure to one place, and a failure sends an alert, with a link to the logs, to a channel someone watches. Retries absorb short glitches so nobody is paged for them.

Second, lateness: a job that hangs never fails. Set a deadline alert — "if the orders table is not updated by 05:30, alert" — based on the table's last update time or the expected end of the run.

Third, the data itself: a run can succeed and load zero rows, or half. Add checks after each load — row count against the source or against yesterday, no duplicate keys, no nulls in required columns, values in range — and alert, or stop publishing, when one fails. A small dashboard of last run, duration and rows loaded per job shows slow drift long before it becomes a missed deadline.

The reasoning

Start with the provider's cost tools — Cost Explorer on AWS, billing reports and exports on GCP, Cost Management on Azure — and group the increase by service and by tag or project. Good tags (team, pipeline, environment) are what make this possible; if they are missing, adding them is the first fix.

Then go one level down. For query engines, list the most expensive queries and users from the query history (the INFORMATION_SCHEMA jobs views in BigQuery, the query history in Athena): a new dashboard that refreshes every 5 minutes with no partition filter is a classic. For compute, look for clusters running 24 hours, autoscaling that never scales down, or a job retrying all night. For storage, look for raw data with no lifecycle rule, many object versions, or a full copy written every day instead of an increment. Data transfer between regions, or out of the cloud, is another hidden cost.

Fix the cause and add a guard: budgets with alerts, per-query or workgroup byte limits, auto-termination on clusters, and lifecycle rules on buckets.

The reasoning

Separate environments mean a test run cannot overwrite production tables or read production secrets. The cleanest split is a separate cloud account (AWS), project (GCP), or subscription or resource group (Azure) per environment, each with its own buckets, warehouse schemas, identities and secret store.

The code is the same everywhere; only configuration changes — bucket names, connection references, cluster sizes — passed in as parameters or environment settings. Everything lives in Git: Airflow DAGs and Databricks code as files, and Data Factory as the ARM templates a Git-connected factory publishes. A pull request is reviewed, tests run (unit tests on transforms, a check that the DAGs load), and a CI/CD pipeline deploys to dev, then to prod after approval.

Infrastructure — buckets, roles, clusters — is best created with infrastructure as code such as Terraform, so production can be rebuilt and every change is reviewed. Nobody edits production by hand; if someone must in an emergency, the change goes back into Git the same day.