Azure Data Lake Storage Interview Questions and Answers Part 6 - File Formats and Performance

ADLS Gen2 Interview Q&A · Part 6 of 10

In this post we are going to cover file formats and performance in Azure Data Lake Storage Gen2: Parquet versus CSV, what Delta Lake adds, compression codecs, block uploads and how to make reads fast. These are 7 real ADLS Gen2 and Blob Storage interview questions for data engineers, asked the way they come up on a real project, and the last one is a scenario: a daily job reads two years of CSV files and takes four hours.

The format you store and the way you lay out files decide how fast the lake feels. The same rows can cost a few requests or thousands of them, and a few hundred kilobytes or forty megabytes of reading. Interviewers ask about this because slow pipelines and rising storage bills usually come back to these choices, not to the storage itself.

VIDEO COMING SOON

Part 6 of the Real Time Azure Data Lake Storage Interview Questions and Answers series on TechBrothersIT.

On this page

1. The short answer
2. Let's set up the project
3. Question 1: A teammate asks why the team stores Parquet instead of CSV. What do you say?
4. Question 2: Someone asks about Delta Lake on ADLS. What does it add on top of Parquet files?
5. Question 3: Reads from the lake are slow. What affects storage throughput, and how do you speed it up?
6. Question 4: Someone asks about compression codecs for Parquet. Which do you pick?
7. Question 5: A teammate uploads a 50 GB file and it fails halfway. How do block uploads and parallelism help?
8. Question 6: Analysts read only three columns of a wide table. How do column pruning and predicate pushdown help?
9. Scenario 7: A daily job reads two years of CSV files and takes four hours. How do you make it fast?
10. The full script

The short answer

Say this first

Parquet is columnar, compressed and typed, and Delta Lake adds a transaction log on top. Speed comes from fewer, larger files, parallel block transfers and reading less: column pruning, predicate pushdown and date partitions.

Let's set up the project

We run Azurite, Microsoft's open-source Azure Storage emulator, locally, and small Python scripts with the azure-storage-blob client and pyarrow, so every output below is real. Features the emulator does not support, like Spark and Delta Lake, are shown as code. common.py connects to the lake container and counts every HTTP request we send, which is how we get the request numbers in this post.

common.py

import os, textwrap
from azure.storage.blob import BlobServiceClient

CONN = os.environ["AZURE_STORAGE_CONNECTION_STRING"]
calls = {"n": 0}


def count(request):                     # every HTTP request we send
    calls["n"] += 1


svc = BlobServiceClient.from_connection_string(
    CONN, raw_request_hook=count)
lake = svc.get_container_client("lake")
if not lake.exists():
    lake.create_container()


def show_err(e):
    code = getattr(e.error_code, "value", e.error_code)
    print(type(e).__name__, e.status_code, code)
    first = str(e).split("\n")[0]
    print(textwrap.indent(textwrap.fill(first, 64), "  "))

Question 1: A teammate asks why the team stores Parquet instead of CSV. What do you say?

Parquet is columnar, compressed and typed. CSV is plain text: every query reads every byte, and every reader guesses the types.

In Parquet, each column's values are stored together. Values of one column look alike, so they compress well, and an engine reads only the columns a query needs.

	CSV	Parquet
Layout	rows of text	columns, grouped in row groups
Size	large	compressed per column
Types	guessed by every reader	stored in the file
A query on 3 columns	reads every byte	reads only those columns

Let's prove it. make_orders.py writes the same 100,000 orders as CSV and as Parquet, and formats.py uploads both and reads them back. We run python make_orders.py, then python formats.py.

make_orders.py

import numpy as np, pandas as pd

rng = np.random.default_rng(42)
n = 100_000
days = pd.date_range("2026-01-01", "2026-10-31")
df = pd.DataFrame({
    "order_id": np.arange(1, n + 1),
    "order_date": np.sort(rng.choice(days, n)),
    "customer_id": rng.integers(1, 5000, n),
    "region": rng.choice(["east", "west", "north", "south"], n),
    "status": rng.choice(["paid", "shipped", "returned"], n),
    "amount": rng.integers(500, 50000, n) / 100,
})
df["order_date"] = df["order_date"].dt.date
df.to_csv("orders.csv", index=False)
df.to_parquet("orders.parquet")
print(f"{len(df):,} orders written as CSV and as Parquet")

formats.py

import io
import pandas as pd
import pyarrow.parquet as pq
from common import lake

for name in ["orders.csv", "orders.parquet"]:
    with open(name, "rb") as f:
        blob = lake.upload_blob(f"bronze/{name}", f, overwrite=True)
    print(f"{name:15} {blob.get_blob_properties().size:>10,} bytes")

csv = pd.read_csv(io.BytesIO(
    lake.download_blob("bronze/orders.csv").readall()))
print("csv     order_date:", csv["order_date"].dtype,
      "| amount:", csv["amount"].dtype)
schema = pq.read_schema(io.BytesIO(
    lake.download_blob("bronze/orders.parquet").readall()))
print("parquet order_date:", schema.field("order_date").type,
      "| amount:", schema.field("amount").type)

Terminal

$ python make_orders.py
100,000 orders written as CSV and as Parquet
$ python formats.py
orders.csv       4,119,594 bytes
orders.parquet   1,245,385 bytes
csv     order_date: str | amount: float64
parquet order_date: date32[day] | amount: double

The CSV is 4,119,594 bytes in the lake, about 4.1 MB. The Parquet file is 1,245,385 bytes, about 1.2 MB, less than a third. Now the types. Read back from the CSV, order_date is str, because the reader had to guess. Parquet carries its schema, so order_date is still date32[day] and amount is a double.

CSV still has its place at the edge, where partners send it. In a real project, I land it raw, as it arrives, and convert it once to Parquet for the curated zone.

Common mistake

Parquet with every column as a string. You keep the size win, but every query still casts types. Define the schema in the conversion.

Question 2: Someone asks about Delta Lake on ADLS. What does it add on top of Parquet files?

Delta Lake keeps your data in normal Parquet files and adds a transaction log, the _delta_log folder, next to them. That gives you ACID commits, time travel and schema enforcement.

Let's look at how the pieces work together:

• Writer - writes Parquet files, then commits
• _delta_log/ - one JSON commit per version
• Readers - see committed versions only

First, the problem it solves. plain_parquet_job.py writes four part files of 25,000 rows each with no log, and crashes after two. Then a reader lists the folder and reads whatever is there. We run python plain_parquet_job.py.

plain_parquet_job.py

import io
import pyarrow.parquet as pq
from common import lake

table = pq.read_table("orders.parquet")
folder = "silver/orders_plain/"
for i in range(4):                      # the job writes 4 part files
    if i == 2:
        print("job crashed while writing part-2")
        break
    buf = io.BytesIO()
    pq.write_table(table.slice(i * 25_000, 25_000), buf)
    lake.upload_blob(f"{folder}part-{i}.parquet", buf.getvalue(),
                     overwrite=True)

# a reader lists the folder and reads whatever is there
parts = [b.name for b in lake.list_blobs(name_starts_with=folder)]
rows = sum(pq.read_metadata(io.BytesIO(
    lake.download_blob(p).readall())).num_rows for p in parts)
print("reader sees:", [p.split("/")[-1] for p in parts])
print(f"rows: {rows:,} of {table.num_rows:,}",
      "- no error, just wrong totals")

Terminal

$ python plain_parquet_job.py
job crashed while writing part-2
reader sees: ['part-0.parquet', 'part-1.parquet']
rows: 50,000 of 100,000 - no error, just wrong totals

The reader sees only part-0.parquet and part-1.parquet: 50,000 rows out of 100,000. No error, just wrong totals.

With Delta, a crashed write never commits, so readers keep the last complete version. Time travel reads an older version, and schema enforcement rejects writes whose columns don't match. The emulator can't run Spark, so here is the code on Databricks: write with format delta to an abfss path, and read version 3 with versionAsOf.

delta_on_databricks.py

# PySpark on Databricks, path under a Unity Catalog external location
path = "abfss://silver@stlakeprod.dfs.core.windows.net/orders"

df.write.format("delta").mode("append").save(path)   # one commit

latest = spark.read.format("delta").load(path)
v3 = (spark.read.format("delta")
      .option("versionAsOf", 3).load(path))          # time travel

# what lands in the lake:
#   orders/part-00000-...-c000.snappy.parquet     the data
#   orders/_delta_log/00000000000000000003.json   commit 3

Pitfall

Never delete or rewrite files in a Delta folder with storage tools; use a Delta writer. With two engines on one table, use one engine's Delta writer and its concurrency control.

Question 3: Reads from the lake are slow. What affects storage throughput, and how do you speed it up?

Three things: the account limits, the size of each request, and the parallelism. But most slow lakes simply have too many tiny files.

Let's start with the limits of a standard account:

Standard account (Azure)	Limit
Requests per second	40,000 in large regions, 20,000 elsewhere
Ingress per account	60 Gbps in large regions, 25 Gbps elsewhere
Egress per account	200 Gbps in large regions, 50 Gbps elsewhere
Over the limit	503 Server Busy: back off and retry

Beyond those limits, storage answers 503 Server Busy, and the client has to back off and retry.

Now the tiny files. small_files.py writes 2,000 click events as 2,000 small JSON files, and the same rows as one compacted file. Then it reads each folder and counts the requests. We run python small_files.py.

small_files.py

import json
from concurrent.futures import ThreadPoolExecutor
from common import lake, calls

events = [{"id": i, "page": f"/p/{i % 40}", "ms": 100 + i % 900}
          for i in range(2000)]
def put(i):                             # one tiny file per event
    lake.upload_blob(f"raw/clicks/small/e{i:05}.json",
                     json.dumps(events[i]), overwrite=True)
with ThreadPoolExecutor(16) as pool:
    list(pool.map(put, range(len(events))))
lake.upload_blob("raw/clicks/compacted/events.jsonl",   # same rows
                 "\n".join(json.dumps(e) for e in events),
                 overwrite=True)

for prefix in ["raw/clicks/small/", "raw/clicks/compacted/"]:
    calls["n"] = 0; rows = size = 0
    for b in lake.list_blobs(name_starts_with=prefix):
        text = lake.download_blob(b.name).readall().decode()
        rows += len(text.splitlines()); size += b.size
    print(f"{prefix:22} rows {rows}  bytes {size:>6,}"
          f"  requests {calls['n']}")

Terminal

$ python small_files.py
raw/clicks/small/      rows 2000  bytes 78,390  requests 2001
raw/clicks/compacted/  rows 2000  bytes 80,389  requests 2

The small folder takes 2,001 requests: one list, then one read per file. The compacted folder takes 2 for the same 2,000 rows, at almost the same size, 80,389 bytes against 78,390. Each request has overhead, and operations are billed per 4 MiB, so a 1 KB file costs a full transaction. Microsoft's guidance is files of 256 MB to 100 GB.

So the fixes, in order:

• Compact - tiny files into large ones
• Bigger blocks - writes over 256 KiB use high-throughput block blobs
• More parallel requests - within the account limits
• Same region - compute next to the lake

Measure first

If the Transactions metric shows account throttling errors, you are at the account limits: spread the load over more accounts, or ask support for higher limits.

Question 4: Someone asks about compression codecs for Parquet. Which do you pick?

Snappy is the default in pandas and pyarrow: light on CPU, with a decent ratio. Zstd usually gives smaller files for a little more CPU, and gzip costs much more CPU. So it depends on how often the data is read, and I measure on our own data.

Let's measure. codecs_demo.py writes the same 100,000 orders four times, once per codec, uploads each file and prints the codec stored in the file. We run python codecs_demo.py.

codecs_demo.py

import pyarrow.parquet as pq
from common import lake

table = pq.read_table("orders.parquet")         # 100,000 orders
for codec in ["none", "snappy", "gzip", "zstd"]:
    name = f"orders_{codec}.parquet"
    pq.write_table(table, name, compression=codec)
    with open(name, "rb") as f:
        blob = lake.upload_blob(f"silver/codecs/{name}", f,
                                overwrite=True)
    chunk = pq.ParquetFile(name).metadata.row_group(0).column(0)
    print(f"{codec:7} {chunk.compression:13}"
          f" {blob.get_blob_properties().size:>10,} bytes")

Terminal

$ python codecs_demo.py
none    UNCOMPRESSED   1,802,413 bytes
snappy  SNAPPY         1,245,385 bytes
gzip    GZIP             889,845 bytes
zstd    ZSTD             860,185 bytes

Uncompressed is 1,802,413 bytes, about 1.8 MB. Snappy is 1,245,385. Gzip is 889,845, and zstd is 860,185, the smallest here.

Notice that even uncompressed Parquet is less than half the size of the 4.1 MB CSV. Parquet encodes each column before any codec runs, for example as a dictionary for repeated values like region and status.

Codec	When I use it
snappy	curated tables read all day
zstd	smallest here: data kept for years
gzip	smaller than snappy; zstd beat it
none	rarely: the biggest files

My rule: snappy or zstd for curated tables read all day, and zstd for data kept for years and rarely read. The codec works per column chunk, so the blob is still a normal Parquet file.

Pitfall

Gzipping whole CSV files. Many engines can't split one gzipped file, so a single task reads all of it, and your parallelism is gone.

Question 5: A teammate uploads a 50 GB file and it fails halfway. How do block uploads and parallelism help?

A block blob is uploaded in blocks. You stage them with Put Block, in parallel, then commit the list with Put Block List. After a failure, you resend only the missing blocks.

First, the limits:

Block blob (Azure)	Limit
Blocks per blob	50,000
Block size	up to 4,000 MiB
Block blob size	about 190.7 TiB
Uncommitted blocks kept	one week

Now let's upload. The Python SDK sends up to 64 MiB in one request by default, so upload_big.py lowers that to 4 MiB. A 20 MiB file stands in for the 50 GB one, and goes up over 4 connections with max_concurrency=4. Then the resume: resume.py stages 3 of 5 blocks, simulates a network drop, checks what is already staged, and sends only the rest. We run python upload_big.py, then python resume.py.

upload_big.py

import hashlib, os
from azure.storage.blob import BlobServiceClient
from common import CONN

MiB = 1024 * 1024
svc = BlobServiceClient.from_connection_string(
    CONN, max_single_put_size=4 * MiB,  # SDK default: 64 MiB
    max_block_size=4 * MiB)             # SDK default: 4 MiB
data = os.urandom(20 * MiB)             # stands in for the 50 GB file
blob = svc.get_blob_client("lake", "landing/clicks_big.json")
blob.upload_blob(data, overwrite=True, max_concurrency=4)
committed, _ = blob.get_block_list("committed")
print("blocks:", len(committed), "| sizes:",
      sorted({b.size for b in committed}))
back = blob.download_blob(max_concurrency=4).readall()
print("size:", len(back), "| md5 matches:",
      hashlib.md5(back).digest() == hashlib.md5(data).digest())

resume.py

import base64, os
from azure.storage.blob import BlobBlock
from common import lake, show_err

MiB = 1024 * 1024
blob = lake.get_blob_client("landing/clicks_resume.json")
parts = [os.urandom(4 * MiB) for _ in range(5)]
ids = [base64.b64encode(f"block-{i:06}".encode()).decode()
       for i in range(5)]
for i in range(3):                      # the network drops after 3
    blob.stage_block(ids[i], parts[i])
try:
    blob.download_blob()                # nothing committed yet
except Exception as e:
    show_err(e)
_, staged = blob.get_block_list("uncommitted")
done = {b.id for b in staged}
print("already staged:", len(done), "blocks")
for i in range(5):                      # resume: send only the rest
    if ids[i] not in done:
        blob.stage_block(ids[i], parts[i])
blob.commit_block_list([BlobBlock(block_id=b) for b in ids])
print("committed:", len(blob.get_block_list("committed")[0]),
      "blocks | size:", blob.get_blob_properties().size)

Terminal

$ python upload_big.py
blocks: 5 | sizes: [4194304]
size: 20971520 | md5 matches: True
$ python resume.py
ResourceNotFoundError 404 BlobNotFound
  The specified blob does not exist.
already staged: 3 blocks
committed: 5 blocks | size: 20971520

The first script shows 5 blocks of 4,194,304 bytes, a size of 20,971,520, and the MD5 matches. In the resume, the read after the drop gets ResourceNotFoundError 404 BlobNotFound: staged blocks are not a blob until they are committed. Three blocks are already staged, so we send the last two and commit: 5 blocks, 20,971,520 bytes.

Mind the block size

4 MiB blocks times 50,000 caps a blob at about 195 GiB, so raise max_block_size for huge files. AzCopy handles blocks and parallelism for you, with --block-size-mb.

Question 6: Analysts read only three columns of a wide table. How do column pruning and predicate pushdown help?

Column pruning means the reader fetches only the columns the query uses. Predicate pushdown means it reads the min and max statistics in the footer and skips row groups that can't match the filter.

Let's build a wide file. make_wide.py adds 40 attribute columns to our orders:

• 46 columns - 6 order fields + 40 attributes
• 10 row groups - 10,000 rows each, sorted by order_date
• Footer - schema + min/max statistics per column chunk

Then blobfile.py is a small reader class that turns every read into a ranged GET on the blob and counts the bytes. pruning.py reads the file three ways. We run python make_wide.py, then python pruning.py.

make_wide.py

import numpy as np
import pyarrow as pa, pyarrow.parquet as pq
from common import lake

table = pq.read_table("orders.parquet")     # sorted by order_date
rng = np.random.default_rng(7)
for i in range(1, 41):                      # 40 more attributes
    table = table.append_column(f"attr_{i:02}",
                                pa.array(rng.random(len(table))))
pq.write_table(table, "orders_wide.parquet", row_group_size=10_000)
with open("orders_wide.parquet", "rb") as f:
    lake.upload_blob("silver/orders_wide.parquet", f, overwrite=True)
meta = pq.ParquetFile("orders_wide.parquet").metadata
print("columns:", meta.num_columns, "| row groups:",
      meta.num_row_groups, "| rows:", f"{meta.num_rows:,}")

blobfile.py

import io


class BlobFile(io.RawIOBase):
    """A read-only file over a blob: every read is one ranged GET."""
    def __init__(self, blob):
        self.blob, self.pos, self.fetched = blob, 0, 0
        self.size = blob.get_blob_properties().size

    def readable(self): return True
    def seekable(self): return True
    def tell(self): return self.pos

    def seek(self, offset, whence=0):
        self.pos = {0: 0, 1: self.pos, 2: self.size}[whence] + offset
        return self.pos

    def read(self, n=-1):
        n = self.size - self.pos if n < 0 else n
        n = min(n, self.size - self.pos)
        if n <= 0:
            return b""
        data = self.blob.download_blob(offset=self.pos, length=n)
        self.pos += n; self.fetched += n
        return data.readall()

pruning.py

import datetime as dt
import pyarrow.parquet as pq
from blobfile import BlobFile
from common import lake, calls

blob = lake.get_blob_client("silver/orders_wide.parquet")
print(f"blob size: {blob.get_blob_properties().size:,} bytes")
cols = ["order_date", "region", "amount"]
october = [("order_date", ">=", dt.date(2026, 10, 1))]
for label, kw in [("all 46 columns", {}),
                  ("3 columns", {"columns": cols}),
                  ("3 columns, October", {"columns": cols,
                                          "filters": october})]:
    f = BlobFile(blob); calls["n"] = 0
    t = pq.read_table(f, use_threads=False, **kw)
    print(f"{label:18} rows {t.num_rows:>7,}  fetched"
          f" {f.fetched:>10,} bytes  requests {calls['n']}")

Terminal

$ python make_wide.py
columns: 46 | row groups: 10 | rows: 100,000
$ python pruning.py
blob size: 40,693,346 bytes
all 46 columns     rows 100,000  fetched 40,698,572 bytes  requests 3
3 columns          rows 100,000  fetched    732,291 bytes  requests 21
3 columns, October rows  10,133  fetched    198,869 bytes  requests 5

The blob is 40,693,346 bytes. Reading all 46 columns fetches the whole file, about 40 MB. Three columns fetch 732,291 bytes, under 2 percent, in more but much smaller requests: 21 instead of 3. Add a filter for October, and the statistics let the reader skip the earlier row groups: 198,869 bytes for 10,133 rows.

Pushdown only skips data when the file is sorted or partitioned by the filter column. With random order, every row group spans every date, and nothing is skipped.

On real projects

Sort by your common filter when you write, and use date folders for the coarse cut.

Scenario 7: A daily job reads two years of CSV files and takes four hours. How do you make it fast?

First I find where the time goes. Usually it is every file, every day, read as text. The fix: convert the history once to Parquet, partitioned by date, and read only the partitions you need.

• Find where the time goes - usually every CSV, every day, as text
• Convert once - Parquet, partitioned by year and month
• Read only what you need - the partitions of the report period

Let's run it. land_csv.py lands one CSV per day for two years. convert.py reads them once and writes Parquet by year and month, zero padded. report.py builds a March 2026 report the old way and the new way. We run python land_csv.py, python convert.py, then python report.py.

land_csv.py

import numpy as np, pandas as pd
from concurrent.futures import ThreadPoolExecutor
from common import lake

rng = np.random.default_rng(1)
days = pd.date_range("2024-11-01", "2026-10-31").strftime("%Y-%m-%d")
files = {day: pd.DataFrame({            # one CSV per day, two years
    "sale_date": day,
    "store": rng.choice(["s01", "s02", "s03"], 150),
    "amount": rng.integers(100, 9000, 150) / 100}).to_csv(index=False)
    for day in days}
def land(day):
    lake.upload_blob(f"raw/sales/daily/sales_{day}.csv", files[day],
                     overwrite=True)
with ThreadPoolExecutor(16) as pool:
    list(pool.map(land, days))
print("daily CSV files:", len(days), "|", days[0], "to", days[-1])

convert.py

import io, os
import pandas as pd, pyarrow as pa, pyarrow.dataset as ds
from common import lake

daily = "raw/sales/daily/"
names = list(lake.list_blob_names(name_starts_with=daily))
df = pd.concat(pd.read_csv(io.BytesIO(
    lake.download_blob(n).readall())) for n in names)
df["year"] = df["sale_date"].str[:4]
df["month"] = df["sale_date"].str[5:7]     # zero-padded: 01 to 12
ds.write_dataset(pa.Table.from_pandas(df, preserve_index=False),
                 "sales_out", format="parquet",
                 partitioning=["year", "month"],
                 partitioning_flavor="hive",
                 basename_template="part-{i}.parquet")
files = sorted(os.path.join(d, f)
               for d, _, fs in os.walk("sales_out") for f in fs)
for path in files:
    with open(path, "rb") as f:
        lake.upload_blob("curated/sales/" + path[10:], f,
                         overwrite=True)
print(len(names), "CSV files ->", len(files), "Parquet files")
print("first:", files[0][10:], "\nlast: ", files[-1][10:])

report.py

import io
import pandas as pd
from common import lake, calls

calls["n"] = 0                            # before: read every CSV
daily = lake.list_blobs(name_starts_with="raw/sales/daily/")
csv = pd.concat(pd.read_csv(io.BytesIO(
    lake.download_blob(b.name).readall())) for b in daily)
march = csv[csv["sale_date"].str.startswith("2026-03")]
print("CSV    :", march.groupby("store")["amount"].sum().round(2)
      .to_dict(), "| requests", calls["n"])

calls["n"] = 0                            # after: one partition
part = "curated/sales/year=2026/month=03/"
pq_df = pd.concat(pd.read_parquet(io.BytesIO(
    lake.download_blob(b.name).readall()))
    for b in lake.list_blobs(name_starts_with=part))
print("Parquet:", pq_df.groupby("store")["amount"].sum().round(2)
      .to_dict(), "| requests", calls["n"])

Terminal

$ python land_csv.py
daily CSV files: 730 | 2024-11-01 to 2026-10-31
$ python convert.py
730 CSV files -> 24 Parquet files
first: year=2024/month=11/part-0.parquet
last:  year=2026/month=10/part-0.parquet
$ python report.py
CSV    : {'s01': 68881.87, 's02': 73850.83, 's03': 69230.02} | requests 731
Parquet: {'s01': 68881.87, 's02': 73850.83, 's03': 69230.02} | requests 2

We have 730 daily CSV files, from 2024-11-01 to 2026-10-31, converted into 24 Parquet files, from year=2024/month=11 to year=2026/month=10. The old report reads every CSV: 731 requests. The new one reads one partition folder: 2 requests. The store totals match to the cent: 68881.87, 73850.83 and 69230.02.

	Before	After
March report reads	730 daily CSV	1 Parquet file
Requests	731	2
Format	text, types guessed	columnar, typed
Daily run	reads 2 years	append + compact

From then on, each day's CSV lands raw and is appended to its month, ideally as a Delta table, with regular compaction so the files stay large.

Before you promise

Keep the compute in the lake's region, watch the Transactions metric, and measure the new runtime before you give the business a number.

Key points to remember

• Parquet: columnar, compressed, typed; Delta Lake adds ACID commits and time travel
• Most slow lakes have tiny files: 2,000 files = 2,001 requests, one file = 2
• Default codec snappy; zstd was smallest; measure on your own data
• Big uploads: parallel blocks, resend only the missing ones, mind the block size
• Column pruning, pushdown and date partitions: read only what the query needs

The full script

This is a Python script that writes every file and runs every command above in order against Azurite: run pip install azure-storage-blob azure-storage-file-datalake pyarrow pandas first.

Show the full script that writes the files and runs every step

Full script - writes the files and runs every step against Azurite (pip install azure-storage-blob azure-storage-file-datalake pyarrow pandas), run it with Python

# ========================================================================
# ADLS Gen2 Interview Q&A #6 - File formats and performance
# TechBrothersIT. Names and sample data are invented for teaching.
# Every step below was run on Azurite 3.37.0 (Microsoft's open-source Azure Storage emulator, Blob service)
# with azure-storage-blob 12.31.1, pyarrow and pandas; the outputs in the video and the blog post are the real outputs.
# To run it yourself:
#   1. npm install -g azurite   then   azurite-blob --skipApiVersionCheck --location ./azurite-data
#      (or the Docker image mcr.microsoft.com/azure-storage/azurite), and leave it running on port 10000.
#      --skipApiVersionCheck lets a newer azure-storage-blob talk to it.
#   2. pip install azure-storage-blob azure-storage-file-datalake pyarrow pandas requests
#   3. Set AZURE_STORAGE_CONNECTION_STRING to "DefaultEndpointsProtocol=http;AccountName=devstoreaccount1;AccountKey=Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==;BlobEndpoint=http://127.0.0.1:10000/devstoreaccount1;"
#   then run python ADLS_Interview_QA_06_Code.py.
# Run it in an empty folder and on a freshly started emulator: it creates its files (and any local data
# files) there, and a second run on old data gives different output.
# Steps that restart the emulator say so in a comment: stop and start it by hand at that point.
# ========================================================================

import os, subprocess, time

def write(path, text):
    if text is None:
        if os.path.exists(path): os.remove(path)
        return
    os.makedirs(os.path.dirname(path) or ".", exist_ok=True)
    with open(path, "w", encoding="utf-8") as f:
        f.write(text.strip("\n") + "\n")

def run(cmd, expect_error=False, allow_error=False, env=None):
    print("\n$ " + cmd, flush=True)
    rc = subprocess.run(cmd, shell=True, env=dict(os.environ, **(env or {}))).returncode
    if rc != 0 and not (expect_error or allow_error):
        raise SystemExit("stopped: this command was expected to succeed")

# Step 0: the starting files
write('common.py', 'import os, textwrap\nfrom azure.storage.blob import BlobServiceClient\n\nCONN = os.environ["AZURE_STORAGE_CONNECTION_STRING"]\ncalls = {"n": 0}\n\n\ndef count(request):                     # every HTTP request we send\n    calls["n"] += 1\n\n\nsvc = BlobServiceClient.from_connection_string(\n    CONN, raw_request_hook=count)\nlake = svc.get_container_client("lake")\nif not lake.exists():\n    lake.create_container()\n\n\ndef show_err(e):\n    code = getattr(e.error_code, "value", e.error_code)\n    print(type(e).__name__, e.status_code, code)\n    first = str(e).split("\\n")[0]\n    print(textwrap.indent(textwrap.fill(first, 64), "  "))\n')

# Step 1: The same 100,000 orders as CSV and as Parquet: size in the lake and the types read back
write('make_orders.py', """\
import numpy as np, pandas as pd

rng = np.random.default_rng(42)
n = 100_000
days = pd.date_range("2026-01-01", "2026-10-31")
df = pd.DataFrame({
    "order_id": np.arange(1, n + 1),
    "order_date": np.sort(rng.choice(days, n)),
    "customer_id": rng.integers(1, 5000, n),
    "region": rng.choice(["east", "west", "north", "south"], n),
    "status": rng.choice(["paid", "shipped", "returned"], n),
    "amount": rng.integers(500, 50000, n) / 100,
})
df["order_date"] = df["order_date"].dt.date
df.to_csv("orders.csv", index=False)
df.to_parquet("orders.parquet")
print(f"{len(df):,} orders written as CSV and as Parquet")
""")
write('formats.py', """\
import io
import pandas as pd
import pyarrow.parquet as pq
from common import lake

for name in ["orders.csv", "orders.parquet"]:
    with open(name, "rb") as f:
        blob = lake.upload_blob(f"bronze/{name}", f, overwrite=True)
    print(f"{name:15} {blob.get_blob_properties().size:>10,} bytes")

csv = pd.read_csv(io.BytesIO(
    lake.download_blob("bronze/orders.csv").readall()))
print("csv     order_date:", csv["order_date"].dtype,
      "| amount:", csv["amount"].dtype)
schema = pq.read_schema(io.BytesIO(
    lake.download_blob("bronze/orders.parquet").readall()))
print("parquet order_date:", schema.field("order_date").type,
      "| amount:", schema.field("amount").type)
""")
run('python make_orders.py')
run('python formats.py')

# Step 2: Plain Parquet without a transaction log: a job crashes after two of four part files
write('plain_parquet_job.py', """\
import io
import pyarrow.parquet as pq
from common import lake

table = pq.read_table("orders.parquet")
folder = "silver/orders_plain/"
for i in range(4):                      # the job writes 4 part files
    if i == 2:
        print("job crashed while writing part-2")
        break
    buf = io.BytesIO()
    pq.write_table(table.slice(i * 25_000, 25_000), buf)
    lake.upload_blob(f"{folder}part-{i}.parquet", buf.getvalue(),
                     overwrite=True)

# a reader lists the folder and reads whatever is there
parts = [b.name for b in lake.list_blobs(name_starts_with=folder)]
rows = sum(pq.read_metadata(io.BytesIO(
    lake.download_blob(p).readall())).num_rows for p in parts)
print("reader sees:", [p.split("/")[-1] for p in parts])
print(f"rows: {rows:,} of {table.num_rows:,}",
      "- no error, just wrong totals")
""")
run('python plain_parquet_job.py')

# Step 3: 2,000 tiny JSON files vs one compacted file: count the storage requests
write('small_files.py', 'import json\nfrom concurrent.futures import ThreadPoolExecutor\nfrom common import lake, calls\n\nevents = [{"id": i, "page": f"/p/{i % 40}", "ms": 100 + i % 900}\n          for i in range(2000)]\ndef put(i):                             # one tiny file per event\n    lake.upload_blob(f"raw/clicks/small/e{i:05}.json",\n                     json.dumps(events[i]), overwrite=True)\nwith ThreadPoolExecutor(16) as pool:\n    list(pool.map(put, range(len(events))))\nlake.upload_blob("raw/clicks/compacted/events.jsonl",   # same rows\n                 "\\n".join(json.dumps(e) for e in events),\n                 overwrite=True)\n\nfor prefix in ["raw/clicks/small/", "raw/clicks/compacted/"]:\n    calls["n"] = 0; rows = size = 0\n    for b in lake.list_blobs(name_starts_with=prefix):\n        text = lake.download_blob(b.name).readall().decode()\n        rows += len(text.splitlines()); size += b.size\n    print(f"{prefix:22} rows {rows}  bytes {size:>6,}"\n          f"  requests {calls[\'n\']}")\n')
run('python small_files.py')

# Step 4: One table written with four Parquet codecs: file sizes in the lake
write('codecs_demo.py', """\
import pyarrow.parquet as pq
from common import lake

table = pq.read_table("orders.parquet")         # 100,000 orders
for codec in ["none", "snappy", "gzip", "zstd"]:
    name = f"orders_{codec}.parquet"
    pq.write_table(table, name, compression=codec)
    with open(name, "rb") as f:
        blob = lake.upload_blob(f"silver/codecs/{name}", f,
                                overwrite=True)
    chunk = pq.ParquetFile(name).metadata.row_group(0).column(0)
    print(f"{codec:7} {chunk.compression:13}"
          f" {blob.get_blob_properties().size:>10,} bytes")
""")
run('python codecs_demo.py')

# Step 5: A chunked parallel upload in 4 MiB blocks, then a manual resume after a failure
write('upload_big.py', """\
import hashlib, os
from azure.storage.blob import BlobServiceClient
from common import CONN

MiB = 1024 * 1024
svc = BlobServiceClient.from_connection_string(
    CONN, max_single_put_size=4 * MiB,  # SDK default: 64 MiB
    max_block_size=4 * MiB)             # SDK default: 4 MiB
data = os.urandom(20 * MiB)             # stands in for the 50 GB file
blob = svc.get_blob_client("lake", "landing/clicks_big.json")
blob.upload_blob(data, overwrite=True, max_concurrency=4)
committed, _ = blob.get_block_list("committed")
print("blocks:", len(committed), "| sizes:",
      sorted({b.size for b in committed}))
back = blob.download_blob(max_concurrency=4).readall()
print("size:", len(back), "| md5 matches:",
      hashlib.md5(back).digest() == hashlib.md5(data).digest())
""")
write('resume.py', """\
import base64, os
from azure.storage.blob import BlobBlock
from common import lake, show_err

MiB = 1024 * 1024
blob = lake.get_blob_client("landing/clicks_resume.json")
parts = [os.urandom(4 * MiB) for _ in range(5)]
ids = [base64.b64encode(f"block-{i:06}".encode()).decode()
       for i in range(5)]
for i in range(3):                      # the network drops after 3
    blob.stage_block(ids[i], parts[i])
try:
    blob.download_blob()                # nothing committed yet
except Exception as e:
    show_err(e)
_, staged = blob.get_block_list("uncommitted")
done = {b.id for b in staged}
print("already staged:", len(done), "blocks")
for i in range(5):                      # resume: send only the rest
    if ids[i] not in done:
        blob.stage_block(ids[i], parts[i])
blob.commit_block_list([BlobBlock(block_id=b) for b in ids])
print("committed:", len(blob.get_block_list("committed")[0]),
      "blocks | size:", blob.get_blob_properties().size)
""")
run('python upload_big.py')
run('python resume.py')

# Step 6: A 46-column Parquet file read over ranged GETs: all columns, 3 columns, 3 columns with a filter
write('make_wide.py', """\
import numpy as np
import pyarrow as pa, pyarrow.parquet as pq
from common import lake

table = pq.read_table("orders.parquet")     # sorted by order_date
rng = np.random.default_rng(7)
for i in range(1, 41):                      # 40 more attributes
    table = table.append_column(f"attr_{i:02}",
                                pa.array(rng.random(len(table))))
pq.write_table(table, "orders_wide.parquet", row_group_size=10_000)
with open("orders_wide.parquet", "rb") as f:
    lake.upload_blob("silver/orders_wide.parquet", f, overwrite=True)
meta = pq.ParquetFile("orders_wide.parquet").metadata
print("columns:", meta.num_columns, "| row groups:",
      meta.num_row_groups, "| rows:", f"{meta.num_rows:,}")
""")
write('blobfile.py', 'import io\n\n\nclass BlobFile(io.RawIOBase):\n    """A read-only file over a blob: every read is one ranged GET."""\n    def __init__(self, blob):\n        self.blob, self.pos, self.fetched = blob, 0, 0\n        self.size = blob.get_blob_properties().size\n\n    def readable(self): return True\n    def seekable(self): return True\n    def tell(self): return self.pos\n\n    def seek(self, offset, whence=0):\n        self.pos = {0: 0, 1: self.pos, 2: self.size}[whence] + offset\n        return self.pos\n\n    def read(self, n=-1):\n        n = self.size - self.pos if n < 0 else n\n        n = min(n, self.size - self.pos)\n        if n <= 0:\n            return b""\n        data = self.blob.download_blob(offset=self.pos, length=n)\n        self.pos += n; self.fetched += n\n        return data.readall()\n')
write('pruning.py', """\
import datetime as dt
import pyarrow.parquet as pq
from blobfile import BlobFile
from common import lake, calls

blob = lake.get_blob_client("silver/orders_wide.parquet")
print(f"blob size: {blob.get_blob_properties().size:,} bytes")
cols = ["order_date", "region", "amount"]
october = [("order_date", ">=", dt.date(2026, 10, 1))]
for label, kw in [("all 46 columns", {}),
                  ("3 columns", {"columns": cols}),
                  ("3 columns, October", {"columns": cols,
                                          "filters": october})]:
    f = BlobFile(blob); calls["n"] = 0
    t = pq.read_table(f, use_threads=False, **kw)
    print(f"{label:18} rows {t.num_rows:>7,}  fetched"
          f" {f.fetched:>10,} bytes  requests {calls['n']}")
""")
run('python make_wide.py')
run('python pruning.py')

# Step 7: Two years of daily CSV files converted once to Parquet partitioned by year and month
write('land_csv.py', """\
import numpy as np, pandas as pd
from concurrent.futures import ThreadPoolExecutor
from common import lake

rng = np.random.default_rng(1)
days = pd.date_range("2024-11-01", "2026-10-31").strftime("%Y-%m-%d")
files = {day: pd.DataFrame({            # one CSV per day, two years
    "sale_date": day,
    "store": rng.choice(["s01", "s02", "s03"], 150),
    "amount": rng.integers(100, 9000, 150) / 100}).to_csv(index=False)
    for day in days}
def land(day):
    lake.upload_blob(f"raw/sales/daily/sales_{day}.csv", files[day],
                     overwrite=True)
with ThreadPoolExecutor(16) as pool:
    list(pool.map(land, days))
print("daily CSV files:", len(days), "|", days[0], "to", days[-1])
""")
write('convert.py', 'import io, os\nimport pandas as pd, pyarrow as pa, pyarrow.dataset as ds\nfrom common import lake\n\ndaily = "raw/sales/daily/"\nnames = list(lake.list_blob_names(name_starts_with=daily))\ndf = pd.concat(pd.read_csv(io.BytesIO(\n    lake.download_blob(n).readall())) for n in names)\ndf["year"] = df["sale_date"].str[:4]\ndf["month"] = df["sale_date"].str[5:7]     # zero-padded: 01 to 12\nds.write_dataset(pa.Table.from_pandas(df, preserve_index=False),\n                 "sales_out", format="parquet",\n                 partitioning=["year", "month"],\n                 partitioning_flavor="hive",\n                 basename_template="part-{i}.parquet")\nfiles = sorted(os.path.join(d, f)\n               for d, _, fs in os.walk("sales_out") for f in fs)\nfor path in files:\n    with open(path, "rb") as f:\n        lake.upload_blob("curated/sales/" + path[10:], f,\n                         overwrite=True)\nprint(len(names), "CSV files ->", len(files), "Parquet files")\nprint("first:", files[0][10:], "\\nlast: ", files[-1][10:])\n')
write('report.py', """\
import io
import pandas as pd
from common import lake, calls

calls["n"] = 0                            # before: read every CSV
daily = lake.list_blobs(name_starts_with="raw/sales/daily/")
csv = pd.concat(pd.read_csv(io.BytesIO(
    lake.download_blob(b.name).readall())) for b in daily)
march = csv[csv["sale_date"].str.startswith("2026-03")]
print("CSV    :", march.groupby("store")["amount"].sum().round(2)
      .to_dict(), "| requests", calls["n"])

calls["n"] = 0                            # after: one partition
part = "curated/sales/year=2026/month=03/"
pq_df = pd.concat(pd.read_parquet(io.BytesIO(
    lake.download_blob(b.name).readall()))
    for b in lake.list_blobs(name_starts_with=part))
print("Parquet:", pq_df.groupby("store")["amount"].sum().round(2)
      .to_dict(), "| requests", calls["n"])
""")
run('python land_csv.py')
run('python convert.py')
run('python report.py')

Next in this series

Part 7: Loading and Moving Data

The sample data on this page is invented for teaching. Every terminal output is the real output of the command above it, run on Azurite 3.37.0, Microsoft's open-source Azure Storage emulator, with azure-storage-blob 12.31.1, pyarrow and pandas. Features the emulator does not support are shown as code. Part of the Real Time Azure Data Lake Storage Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the official Azure Storage documentation (learn.microsoft.com/azure/storage) for anything you quote in an interview.