Associate Data Practitioner

Understand the Key Features of CSV and JSON

CSV (Comma-Separated Values) is a simple text format that stores data in rows and columns. Each line of a CSV file is a record, and commas separate the fields within that record. CSV is easy to create and read, and it works with many tools such as spreadsheets and BigQuery. However, CSV only supports basic data types like numbers and text. It cannot store nested structures, which limits its use for complex data.

JSON (JavaScript Object Notation) is a flexible format that can represent complex data using nested objects, arrays, and multiple data types such as strings, numbers, booleans, and nulls. JSON is human-readable and commonly used in web APIs because it is easy for both people and machines to understand. Within GCP, JSON is often used for loading data into BigQuery and for exchanging data between applications.

When choosing between CSV and JSON, consider the tradeoff. CSV is simpler and works well for flat, tabular data, especially when you need to move data between spreadsheets or databases. JSON is better for hierarchical data or when you need to preserve relationships between fields. In GCP, understanding these differences helps you pick the right format for your data ingestion and processing tasks.

Evaluate the Characteristics and Use Cases of Data Formats

Selecting the correct data format is important when you bring data into Google Cloud storage. Each format has its own strengths for storage, performance, and compatibility with GCP services. CSV is a flat text file that is easy to read but lacks a built-in schema and cannot hold nested data. JSON is a semi-structured format that supports nested records, but it can be more verbose and slower to parse than binary formats.

Apache Parquet and Apache Avro are binary formats designed for specific workloads. Parquet uses columnar storage, which groups similar data together. This makes it very efficient for analytical queries in BigQuery and Dataflow because only the needed columns are read. Avro uses row-based storage and embeds its schema inside the file. This makes Avro ideal for streaming data pipelines because it supports schema evolution—the ability to change the structure of the data over time without breaking existing processes.

Structured database tables use a relational model with enforced schemas and ACID properties. You load data into them using services like Datastream, Cloud Data Transfer, or Database Migration Service. These tables ensure data consistency and can be queried directly from BigQuery using federated queries. When planning ingestion, consider where each format works best: Cloud Storage for CSV and JSON files, BigQuery’s native support for Parquet and Avro, streaming pipelines in Dataflow, and direct database connectors for structured tables. By weighing these factors, you can reduce storage costs and improve query speed.

Differentiate Optimized Binary Formats and Structured Tables

Text-based formats like CSV and JSON often rely on schema auto-detection. When you load these files into BigQuery, the system scans a sample of rows to guess the data types. This is a best-effort approach that works well when the data is consistent. In contrast, optimized binary formats such as Apache Parquet, Apache Avro, and ORC are self-describing. They embed the schema definition directly inside the file, so BigQuery can automatically read the correct table structure without guessing. This eliminates the risk of incorrect type assignments and reduces manual configuration errors.

Structured database tables offer advantages beyond what file-based storage provides. BigQuery is a serverless data warehouse built for fast analytics over petabytes of data. It is cost-effective because you pay only for the data processed by each query. Spanner is a distributed relational database optimized for high availability and global scalability, making it suitable for transactional applications that require strong consistency. Structured tables include built-in features like machine learning integration and security that raw file storage does not offer.

The choice between these options depends on your project’s requirements. For analytics workloads, BigQuery is typically more cost-effective. For transactional applications that need strict consistency and global access, Spanner is a better fit. Both services automatically scale to handle large workloads, and they support a variety of data sources, including on-premises systems and other clouds.