DP-900: how to understand data formats and compression in analytical warehouses

I will teach the skill: understanding data formats (row vs column) and compression techniques in analytical workloads on Azure. This is important for the DP-900 exam because you demonstrate knowledge about performance and costs in analytical solutions (for example, Azure Synapse, Azure Data Lake). In practice, choosing the right format and compression reduces I/O, speeds up queries and lowers storage costs.

What you need to know

There are two basic models for storing data: row-based and columnar. Transactional databases normally use row-based (e.g., OLTP) because they write/read individual rows. Analytical workloads (OLAP) benefit from columnar formats because queries aggregate/scan specific columns.

Common file formats in the Azure/Data Lake environment:

• CSV/JSON: simple, row-oriented, no native compression (easy for ingestion, expensive for large-scale analytics).
• Parquet/ORC: columnar, optimized for analytics, allow per-column compression, predicate pushdown and partial column reads.
• Avro: row-oriented but with schema and compression; good for messaging and streaming.

Compression reduces space and I/O. There are two main types:

• Block/column-wise compression (e.g., Snappy, Zlib, Zstandard): applied within columnar files, very efficient for columns with repeated values.
• Transport compression (e.g., Gzip): applied to entire files; good for transfer, less efficient for analytical reads because it prevents partial reads.

Practical outcomes: using Parquet + columnar compression (Snappy or Zstandard) usually reduces data read and improves query speed in Synapse/Databricks/Power BI that consume files in the Data Lake.

How it works in practice

Imaginary practical example: you have telemetry logs with 50 columns and 1 TB of data in CSV format. A typical analytical query reads only 4 columns. With CSV you will read entire files (1 TB). Converting to Parquet with compression, the size may drop to 100–200 GB and the engine will read only the necessary columns, reducing I/O and cost.

Recommended transformation flow for Data Lake/analytics:

1. Ingest the raw data as received (CSV/JSON) into a raw zone in Azure Data Lake.
2. Cleanse and transform with a tool (Azure Data Factory, Synapse Pipelines, Databricks).
3. Write the result in Parquet/ORC with appropriate compression into a curated/analytics zone.
4. Configure partitioning (for example, data=YYYY-MM-DD) to reduce the amount of reading and ease range queries.

# Exemplo conceptual em PySpark: escrever Parquet com Snappy
df.write
  .mode('overwrite')
  .partitionBy('date')
  .option('compression','snappy')
  .parquet('/mnt/datalake/analytics/telemetry/')

Notes on compression options:

• Snappy: balance between compression and speed; a good default for Parquet in many engines.
• Zstandard (zstd): better compression than Snappy with good speed, useful when storage is critical.
• Gzip: high compression, but CPU costs and prevents efficient partial reads; avoid for interactive analytics.

Common mistakes

1) Choosing CSV/JSON for large-scale analytical data — increases I/O and costs. CSV/JSON are great for ingestion but not for large-scale analytical querying.

2) Excessive compression without evaluating CPU — choosing very heavy codecs (e.g., Gzip at maximum) can reduce space but increase latency in interactive queries and the CPU cost to decompress.

3) Not partitioning when appropriate — Parquet files do not solve everything: without partitioning, even with columnar, you may read many unnecessary files. Partition by fields frequently used in filters (date, region, id_de_cliente when applicable).

How to practice

Practice by converting sample data to Parquet and testing queries in Synapse, Databricks or Azure Data Explorer (when supported). Microsoft provides official resources for study: use the official Microsoft Practice Assessment free for DP-900 and the official study guide (both free) to validate knowledge and see the scope of the skills measured. Do not use dumps; rely on these official resources to prepare.

Suggested practical exercise:

1. Upload an open dataset (e.g., logs or a public dataset) to an Azure Data Lake container.
2. Use a notebook in Synapse or Databricks to read the CSV, transform and write partitioned Parquet with Snappy.
3. Run two queries: one that reads multiple columns and another that reads only one column; compare times and bytes read.

In summary

• Parquet/ORC (columnar formats) are preferable for analytical workloads: they reduce I/O and speed up aggregations.
• Choose balanced compression codecs (Snappy or Zstandard) to optimize space and latency.
• Adequate partitioning (by date/region) reduces unnecessary reads and improves performance.
• Practice with Microsoft's official resources: the free Practice Assessment and the study guide for DP-900.