Before you start
This course is independent and is not made by or affiliated with Microsoft. It cannot promise you a pass. The official syllabus is Microsoft's Study guide for Exam DP-900, which is free on Microsoft Learn. We do not reproduce it here, but we teach to it. Microsoft gives you 45 minutes for the exam, and you need a score of 700 or greater to pass.
This level covers one bullet under Describe core data concepts, "Describe common formats for data files." It builds on Level 1, where you learned the difference between structured, semi-structured and unstructured data.
Why file formats matter
Organizations keep a lot of data in files, more and more often in central cloud storage. Microsoft Learn says three factors decide which format is used:
- The type of data: structured, semi-structured or unstructured.
- The applications and services that need to read, write and process the data.
- Who reads it: whether people need to read the file, or whether it should be optimized for efficient storage and processing.
Do: for every format below, ask yourself two things. Can a person read it? What kind of data is it good for?
Format 1: Delimited text (CSV)
Data is stored as plain text. A delimiter (separator character) splits the fields, and a row terminator ends each row. The most common type is comma-separated values (CSV). Fields are separated by commas, and each row ends with a new line. The first line can hold the field names.
FirstName,LastName,Email
Joe,Jones,joe@litware.com
Samir,Nadoy,samir@northwind.com
Other types of delimited text:
- TSV: tab-separated values
- Space-delimited: fields separated by spaces
- Fixed-width: every field takes up a fixed number of characters
Good for: structured data that many different applications and services need to open in a human-readable form.
Business example: a finance team exports monthly sales from its accounting system as a CSV file so that anyone can open it in a spreadsheet.
Format 2: JSON (JavaScript Object Notation)
JSON uses a hierarchical (nested) document schema to describe objects that have attributes. An attribute can hold another object or a collection of objects, so JSON works well for both structured and semi-structured data.
How to recognise JSON:
- Objects are inside braces
{ }. - Collections are inside square brackets
[ ]. - Attributes are written as name:value pairs, separated by commas.
{
"firstName": "Joe",
"contact": [
{ "type": "email", "address": "joe@litware.com" }
]
}
Business example: a web API sends an online order to a mobile app. One order holds a list of line items, which a flat table cannot show neatly.
Format 3: XML (Extensible Markup Language)
XML is a human-readable format that was popular in the 1990s and 2000s. JSON, which is less verbose, has largely replaced it, but some systems still use XML. XML uses tags in angle brackets < > to define elements and attributes.
<Customer name="Joe" lastName="Jones">
<Contact type="email" address="joe@litware.com"/>
</Customer>
Business example: an older supplier system that still sends invoices as XML.
Format 4: BLOB (Binary Large Object)
All files are binary (1s and 0s) in the end. In the text formats above, though, the bytes map to printable characters through an encoding such as ASCII or Unicode. Other files, mostly unstructured data, are stored as raw binary that an application has to interpret and render. Examples are images, video, audio and application-specific documents. Data professionals call these files BLOBs.
Business example: product photos for an online shop, or recorded calls from a contact centre.
Format 5: Optimized file formats
Human-readable formats are easy to use, but they are not optimized for storage space or processing. Specialized formats add compression, indexing and efficient storage and processing, which matters when you analyse very large amounts of data.
- Parquet (an Apache project) is a columnar format that is widely used in data lakes and lakehouses:
- A Parquet file contains row groups, and the data for each column is stored together within a row group.
- Metadata in the file lets an application find the right chunk of data quickly and read only the columns it needs.
- Parquet handles nested data types efficiently and supports efficient compression and encoding.
- Avro (an Apache project) is a row-based format:
- Each file has a header, stored as JSON, that describes the structure of the data.
- The data itself is stored as binary in blocks of records. An application reads the header first, then uses it to parse the binary data.
- Avro is good for compressing data and keeping storage and network bandwidth needs low.
- ORC (Optimized Row Columnar, an Apache project) is another columnar format, designed for Hadoop workloads and first built to speed up Apache Hive.
- Delta Lake is an open-source storage format that builds on Parquet and adds a transaction log. The log enables ACID transactions (database-style reliable updates), data versioning and reliable updates on files in a data lake.
Why columnar helps analytics: suppose a report asks for "total revenue by region" across a billion sales rows. A columnar file reads only the Revenue and Region columns and skips all the others. Similar values sit next to each other, so they also compress well.
How to choose a format
Do: work through these questions in order.
- Is it an image, video, audio or app-specific document? Use a BLOB.
- Is it simple tabular data that people and many tools need to open? Use CSV (delimited text).
- Is it nested or flexible data passing between applications, such as an API payload? Use JSON. If an older system requires it, use XML.
- Is it large-scale analytics in a data lake? Use Parquet, or ORC (both columnar). If you need reliable updates and versioning on top of Parquet, use Delta Lake.
- Are you moving compact records with their schema attached, keeping storage and bandwidth low? Use Avro (row-based).
What the exam asks about this
- Recognising a format from a short sample: commas, braces, or angle-bracket tags.
- Matching a format to a scenario: a spreadsheet export, an API payload, media files, a data lake.
- Knowing that Parquet is columnar and Avro is row-based with a JSON header.
- Knowing that XML is human-readable but more verbose than JSON.
Key takeaways
- CSV: human-readable structured data with delimited fields; opens almost anywhere.
- JSON: braces, brackets and name:value pairs; hierarchical; handles structured and semi-structured data.
- XML: angle-bracket tags; human-readable but verbose; largely replaced by JSON.
- BLOB: raw binary for unstructured data such as images, video and audio.
- Parquet and ORC: columnar formats for large-scale analytics. Avro: row-based, JSON header, binary data. Delta Lake: Parquet plus a transaction log.