Describe Core Data Concepts · Topic 2 of 4
Data Storage Options and File Formats
DP-900 storage options: delimited text, JSON, XML, blob, Avro, ORC and Parquet formats, relational and non relational databases, and the Azure store for each use.
- 5 min
- CSV is delimited text, and each line is one row.
- Parquet and ORC are columnar and suit analytics. Avro is row based.
- A BLOB is a binary large object such as an image or video.
What it is
Data is kept either in files or in databases. A file store holds data as whole files in a chosen format. A database manages the data for you, with rules for reading, writing and protecting it.
The exam asks you to recognise the common file formats, the main kinds of database, and which Azure service suits each use.
How it works
Common file formats
| Format | What it is | Good for |
|---|---|---|
| Delimited text (CSV, TSV) | Plain text, one row per line, values split by a comma or tab | Simple tables, easy exchange |
| JSON | Text with named properties, nested objects and arrays | Web data, flexible records |
| XML | Text with opening and closing tags and attributes | Older systems, documents with structure |
| BLOB | Binary large object: images, video, audio, application files | Unstructured content |
| Avro | Compact binary format that stores rows, with its schema in a JSON header | Streaming and record by record writes |
| ORC | Columnar binary format, built for Hive reads | Large analytical reads |
| Parquet | Columnar binary format with strong compression | Analytics over big datasets |
A columnar format stores each column together instead of each row together. A query that needs only two columns of fifty can then skip the rest, which is why Parquet and ORC are popular for analytics.
Types of database
Relational databases store structured data in tables with rows and columns. Relationships between tables are defined with keys, and you query with SQL.
Non relational databases (also called NoSQL) store data in other shapes and scale out easily.
| Type | Stores data as | Example use |
|---|---|---|
| Key value | A unique key that points to a value | Shopping carts, session data |
| Document | Self describing JSON style documents | Product catalogues, user profiles |
| Column family | Rows holding columns grouped into families | Large time based data, sensor logs |
| Graph | Nodes joined by edges | Social networks, recommendations |
Azure data stores by use case
| Need | Azure service |
|---|---|
| Relational data, transactions, SQL | Azure SQL Database or Azure SQL Managed Instance |
| Open source relational data | Azure Database for PostgreSQL or MySQL |
| Flexible documents with global reach | Azure Cosmos DB |
| Unstructured files and images | Azure Blob Storage |
| A data lake for analytics | Azure Data Lake Storage Gen2 |
| File shares reached over SMB or NFS | Azure Files |
| Simple key and attribute data at low cost | Azure Table Storage |
Azure Data Lake Storage Gen2 is built on Blob Storage with a hierarchical namespace added, so folders are real directories with permissions.
A worked scenario
A company records sensor readings every second. The team writes them as Avro files because Avro handles a stream of records well. Each night a job converts the day into Parquet, because analysts read a few columns across billions of rows. Customer orders sit in Azure SQL Database, where transactions need guaranteed accuracy. Equipment photos go to Blob Storage. Each need uses the format or store that fits it best.
File or database?
| Choose a file store when | Choose a database when |
|---|---|
| Data is written once and read in bulk | Many users read and write small pieces at once |
| The content is unstructured, such as video | You need queries, indexes and constraints |
| You want the lowest storage cost | You need transactions and guaranteed accuracy |
| Other tools will process the files later | The application depends on fast, repeatable lookups |
Why a columnar file reads less
A table of fifty columns and a billion rows has to be scanned to add up one column. A row based file must read every row in full, so most of the bytes read are not needed. A columnar file keeps each column together, so the query reads only the one column it needs, and values in a single column compress well because they look alike. That is why analytics favours Parquet and ORC, while record by record writes favour Avro.
One item in each non relational type
| Type | An example item |
|---|---|
| Key value | Key cart:4411, value is the contents of the cart |
| Document | A JSON document for a product with its own list of attributes |
| Column family | A row key for a sensor, with a group of columns for readings and another for settings |
| Graph | Two nodes, Ana and Ben, joined by an edge labelled "friend of" |
Limits of delimited text
A comma separated file has no data types, no way to mark a missing value from an empty string, and trouble when a value itself contains a comma. Quoting rules help, but different tools handle them differently. That is a main reason that larger analytical systems prefer typed formats such as Parquet.
What to remember for the exam
- CSV is delimited text, and each line is one row.
- Parquet and ORC are columnar and suit analytics. Avro is row based.
- A BLOB is a binary large object such as an image or video.
- The four non relational types are key value, document, column family and graph.
- Azure Cosmos DB holds documents and other non relational models. Azure Blob Storage holds unstructured objects.
- Data Lake Storage Gen2 adds a hierarchical namespace to Blob Storage.
Do not confuse a data lake with a database. A lake holds files in their raw form and has no query engine of its own.
How the exam asks about this
- "Which format stores data by column and suits analytics?" Parquet.
- "Store images and video cheaply." Blob Storage.
- "A key to look up a value, nothing else." A key value store.
- "Data about relationships between people." A graph database.
Common mistakes
Choosing a relational database for every need. Documents, graphs and files each have a better home.
Thinking CSV carries data types. It is only text. The reader decides whether a value is a number or a date.
Mixing up columnar and column family. Columnar describes a file layout. Column family is a type of non relational database.
Treating Table Storage as relational. It stores entities by key and attribute, with no joins or foreign keys.
Check yourself
Which file formats are columnar?
Parquet and ORC.
Which kind of database stores nodes and the edges between them?
A graph database.
Which Azure service stores unstructured objects such as images and backups?
Azure Blob Storage.
Topic 2 of 4 in Describe Core Data Concepts
Test yourself on Describe Core Data Concepts
The full Microsoft DP-900 question bank has 503 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.