Core data concepts · Lesson 2

From files to a store that fits the query

8 min read

Lesson objectives

  • Compare file, object and database storage from reading, update and query patterns
Reading tools

Choose a store from the operations it must support, not from the file extension. CSV is a sequence of text rows that is convenient to move and inspect in a batch, but a CSV file alone does not enforce a unique identifier, valid date, or relationship between tables. JSON can convey keyed, nested records; documents may vary, so a query against an absent key needs an explicit policy. Parquet is a column-oriented format suited to reading selected columns in analytics, whereas a plain text file may be clearer for a small exchange. These are representation formats rather than transaction systems. A database offers indexes, constraints, queries, and operational updates, with management and performance consequences. Not every CSV must enter a relational database, and JSON does not by itself demand a document database. Write down the reading and writing pattern first.

When an application retains images, invoices, or a large file archive and retrieves whole files by identifier or API, object storage such as Azure Blob Storage fits the access shape. An object holds content and metadata; it does not thereby become a relational table that can JOIN customers and orders. An application updating an order balance with every purchase and enforcing unique order IDs needs an appropriate transactional store. A relational database is often a fit when relationships and queries are central. A receipt image in Blob can be linked to an order row by a reference; an integrated application need not force one service to perform both jobs. Data volume, update rate, query pattern, availability, access control, and cost matter, but no performance value should be invented before measurement.

Build a decision table using four questions. Is the save unit a whole file or an individually updated record? Must queries filter on many field values, or mainly retrieve an object by a known identifier? Must several linked changes succeed or fail together? Is the consumer an application, a legacy file share, or a batch analytics pipeline? For a periodic report, distinguish the operational source from an analytical copy or landing files; avoid making a broad report compete with sensitive transactions without a reason. One file may enter a database and remain in an archive. Its format does not dictate its permanent home. Unstructured content does not imply that indexing is impossible: metadata and extracted results can be indexed while the original file remains intact. State which layer the index describes.

Test a storage choice with a small trial before generalizing it. One order without order_id should be rejected; two with the same identifier should expose duplication; a valid order pointing to a missing image should show a broken-reference state, not false success. These checks concern integrity as well as speed. If the application writes an order first and image upload fails, define an intermediate state or compensating action: the database and object store do not automatically share one atomic transaction. If the image uploads first and the order write fails, an orphaned image may need controlled cleanup. Changing file format does not solve that integration problem. For nightly reporting, record cutoff time, time zone, and a late-order rule. An order arriving after cutoff belongs to tomorrow or triggers recomputation under an explicit policy. In the option table, state expected updates, access method, relational needs, and reporting freshness; changing one assumption can change the decision. Raw file volume is not an invoice or service limit. Compression, copies, retention, and transfer require separate measurement. Storage selection is a testable hypothesis, not a slogan that databases always beat files or the reverse.

A colleague reports that an order exists but its attachment has disappeared. Examine the relationship before changing storage technology. The application may save the record first and then fail to upload its attachment during a network interruption. The reverse is also possible: upload succeeds but saving the link fails. Give every step an observable outcome. Use a stable attachment identifier and understandable processing states. Specify which component retries and how it recognises an upload that already succeeded. When deleting, review retention policy and old links. Reconciliation can detect both records without files and files without records. This complements choosing a transaction database and object store. Successful individual services do not establish a complete combined operation; test interruptions between the steps.

Lesson summary

Compare file, object and database storage from reading, update and query patterns.