Batch processing and data warehouses: files, columns and star schemas

The interviewer says: "The business wants a report of revenue by city and month for the last year. The orders table has 365 million rows. Where do you run the report, and how is the data stored?"

A weak answer runs the query on the main database and hopes. Another weak answer says "use a big data tool" and gives no reason. A strong answer keeps reports away from the database that serves users. It stores the data in a shape that reads few columns, splits the work across machines, and makes a failed job safe to run again. It also says how old the report may be.

At SDE-2 level you should explain why reports do not run on the main database. At SDE-3 level you should explain columns, star schemas and safe reruns with numbers.

Think of a laundry service. Every Sunday it collects the week's clothes, shares them among helpers, washes them, returns them and closes until next Sunday. If one helper spills a bucket, only that helper's pile is washed again. The service does not wash each shirt the moment it arrives.

A batch job works like this. It reads a large input that does not change while it runs, writes an output and stops. Because the input is fixed, a failed piece can run again and gives the same answer.

Split, group, combine

The classic way to share work is called map and reduce. Take the sum of order amounts for each city.

1. Split the input into pieces. The paper that made this idea known says pieces of 16 MB to 64 MB are typical.
2. Map: each worker reads one piece and writes pairs of key and value, such as (Pune, 400).
3. Shuffle: all pairs with the same key go to the same reduce worker. The worker is hash(key) mod the number of reduce workers (lesson 87).
4. Reduce: each worker adds the values of each key and writes the result.

If a worker dies, its piece runs again on another machine. A map piece writes its output only when it is whole, so the rerun is safe. Many big SQL engines work this way when you write GROUP BY.

Files, rows and columns

Batch jobs read files in object storage (lesson 40). A row-oriented file stores one row after another. A column-oriented (columnar) file stores all values of one column together. An analytics query reads a few columns of many rows. A columnar file lets the job skip the other columns. Values in one column look alike, so they also compress better. For example, "Pune" repeated 1,000 times is stored as "Pune x 1000".

Parquet is a common columnar file. It cuts the rows into row groups, and each row group holds one chunk for each column. The cost: reading or changing one whole row touches every column chunk, and files are written once and replaced whole (lesson 40). Columns suit big reads. They do not suit single-row work.

The data warehouse

The operational database is the one that serves users: orders, payments, logins. A data warehouse is a database built for large reads and summaries over history. Keep it apart from the operational database for three reasons.

• A report scans a lot of data. It takes the disk and the CPU, and the users wait.
• The shapes differ. The operational database keeps many small normalised tables for writes (lesson 34). The warehouse keeps a few tables arranged for reading.
• The warehouse keeps history that the operational database may change or delete.

Some warehouses keep storage and compute apart, so teams run queries on separate machines without disturbing each other.

The star schema

A fact table stores events: each sale or each click. Each row has keys that point to descriptions, and numbers to add up, such as the amount. A dimension table describes one kind of thing around the events: customer, restaurant, date. Say what one fact row is, for example one order. A fact table is large and grows. Dimension tables are small. Drawn with the fact table in the middle, the shape is a star.

A star schema. In the middle is a fact table of orders, with one row for each order. Around it are three small dimension tables that describe the orders: customer, restaurant and date. A report joins the fact table to a few dimension tables and then groups.

A report joins the fact table to a few small dimension tables and then groups. A dimension row may repeat details, such as the city name in every restaurant row. This is fine because the table is small. Lesson 34 would split it, but here the aim is fewer joins on reads. One care: a dimension row can change, for example a restaurant moves to another city. Decide whether old facts show the old city or the new one.

Loading: ETL or ELT

Data reaches the warehouse in three steps: extract (read from the source), transform (clean, join, compute) and load (write). ETL transforms before the load, for example to remove personal data first. ELT loads the raw data first and transforms it inside the warehouse. ELT keeps the raw copy, so you can run a changed rule again over old data.

Partitions, safe reruns and the scheduler

Partition the files by date: one folder for each day. A report for one month reads 30 folders and skips the rest. A daily job for date D should overwrite the partition of D. It writes the new files to a temporary place and makes them the partition of D only when all are complete. On plain object storage, renaming a folder is not one step. So many jobs write a small "done" file last, and readers ignore a partition without it. Run it twice and the result is the same (lesson 89). A job that appends rows doubles them on a rerun.

A scheduler is a program that runs jobs at set times and in order. A job starts only when the jobs it depends on have succeeded, and a failed job is tried again. It can also run a job for a range of past dates.

When batch is enough

Batch is enough when the reader can wait for the next run. A report read next morning needs no stream job (lesson 154). Data is as old as the run interval plus the run time.

SwadKart wants revenue by city and month for the last 12 months.

Assumptions (made up to teach with): 1,000,000 orders (10 lakh) a day. A row has 20 columns of 10 bytes, so 200 bytes. A worker reads 100 MB per second, and we use 10 workers. The main database disk scans 500 MB per second. Compression is 3 to 1. An average order is Rs 400.

Step 1: the size. Rows: 1,000,000 x 365 = 365,000,000. Bytes: 365,000,000 x 200 = 73,000,000,000 = 73 GB.

Step 2: on the main database. 73,000 MB / 500 MB per second = 146 seconds with the disk fully busy. Users wait.

Step 3: row files in the warehouse. 73,000 MB / (10 x 100 MB per second) = 73 seconds.

Step 4: columnar files. The report needs 2 of 20 columns: 2 / 20 = 10%. This answers the predict box. Bytes: 365,000,000 x 10 x 2 = 7,300 MB. Time: 7,300 / 1,000 = 7.3 seconds. With compression: 7,300 / 3 = 2,433 MB, which is 2.4 seconds. That is 73,000 / 2,433 = 30 times less data than row files.

Step 5: split. 73,000 MB / 64 MB = 1,141 pieces (rounded up). One failed piece reruns 64 MB at 100 MB per second: 0.64 seconds. Map writes pairs. Piece 1 gives (Pune, 400), (Pune, 350), (Delhi, 500). Piece 2 gives (Delhi, 300), (Pune, 250). Piece 3 gives (Delhi, 200). After the shuffle, reduce adds: Pune = 400 + 350 + 250 = 1,000 and Delhi = 500 + 300 + 200 = 1,000.

Step 6: the star. The fact table fact_orders has date_id, restaurant_id and amount. dim_date has 3,650 rows for 10 years, with the month in each row. dim_restaurant has the city in each row.

SELECT d.month, r.city, SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_date d ON d.date_id = f.date_id
JOIN dim_restaurant r ON r.restaurant_id = f.restaurant_id
GROUP BY d.month, r.city;

Step 7: partitions. One day is 1,000,000 x 200 = 200 MB. A one-month report reads 30 of 365 partitions, which is 8.2%. In columnar files: 30 x 1,000,000 x 10 x 2 = 600 MB, and 600 / 3 = 200 MB after compression.

Step 8: a rerun. One day brings 1,000,000 x Rs 400 = Rs 400,000,000 (Rs 40 crore; 1 crore = 10 million). A job that appends and then runs twice shows Rs 80 crore. A job that overwrites its partition shows Rs 40 crore every time.

Step 9: how old. The job runs every hour and takes 10 minutes. The data is up to 60 + 10 = 70 minutes old. This is fine for a morning report. It is not fine for an alert within seconds.

What a company wrote

Razorpay wrote on 14 Jul 2026 that its warehouse tables are wide, pre-joined tables. Each one joins 10 to 30 service tables that arrive in files on object storage. They come through change data capture (lesson 92), a stream of every change in a database. The post says they are closer to a precomputed view than a classic fact table. A full refresh of its Payments table took 8 to 10 hours (the company's figure), so the team moved to refreshing only the changes. Link: https://engineering.razorpay.com/how-we-refresh-razorpays-data-warehouse-10x-faster-with-graphs-and-indexes-538abc244703 (opens in a new tab)

A warehouse with columnar files solves slow reports and users who wait (146 seconds of busy disk against 2.4 seconds of reading). It costs a second copy of the data, a loading pipeline, a model to design, and data that is as old as the last run. I would change to a stream job when the answer must be fresh within seconds. I would change to the main database, or a copy of it, when the data is a few gigabytes.

"A data warehouse is just a bigger copy of the main database." The shape differs: columns, few wide tables, history kept. In step 4 the shape and the compression cut the bytes read 30 times.

"Columnar files are always faster." They are faster for big reads of a few columns. For one whole row, or one changed row, they must touch every column chunk.

"If a batch job fails, I just run it again." Only if it is idempotent. In step 8 an appending job showed Rs 80 crore. Overwrite one partition.

1. What is a batch job, and why can a failed piece be run again? What is the difference between a fact table and a dimension table?
2. A table has 500,000,000 rows of 100 bytes, in 25 columns of equal size. A report needs 2 columns. Five workers read 100 MB per second each. (a) How long does the scan take in row files? (b) In columnar files, before compression? (c) With compression of 4 to 1?
3. A daily job appends yesterday's sales to a table. It failed halfway at 3 am and was started again. What is wrong, and how do you fix it?