CCNA Data Preparation And Ingestion Questions

Streaming to BigQuery is a canonical Dataflow pattern: an unbounded source feeds a pipeline that writes continuously into BigQuery, typically with windowing and streaming inserts. It matches the stem's request for common Dataflow pipeline patterns.

Enabling schema update options on the load job lets BigQuery extend the existing destination table schema with new fields discovered in the source data. Because the files are newline-delimited JSON and can gain top-level fields, this avoids load failures while preserving previously loaded rows. It matches the requirement to infer and evolve the schema automatically, though it only adds columns and does not remove or change existing ones.

Dataflow supports side outputs, which let a DoFn emit records to a separate PCollection based on a tag. By catching parse exceptions and emitting the bad records to a tagged side output, the pipeline can continue processing valid messages while routing failures to a dead-letter destination. This directly satisfies the requirement to isolate malformed records without stopping the pipeline.

Watermarks track event-time progress and trigger window emission, while Allowed Lateness lets the pipeline retain window state and reprocess records arriving after the watermark passes. Together they satisfy the ordering and late-arrival constraint in event-time streaming.

Fixed windows align with the 5-minute requirement. Allowed lateness of 10 minutes lets the pipeline accept events that arrive after the watermark passes, and ACCUMULATING mode re-emits the updated aggregate including late data. Together these features produce correct per-window results for late arrivals while allowing the pipeline to eventually discard state once the lateness horizon passes, controlling cost.

Partitioning physically divides a table by a column such as date, so queries filtering on that column scan only the relevant partitions rather than the whole table. This reduces bytes processed, directly lowering on-demand query cost and improving performance — satisfying the stem's requirement for the primary benefit.

A signed URL embeds a cryptographic signature and expiry in the link itself, granting time-limited read or write access to a specific object without requiring the third party to hold IAM credentials or a Google account. This satisfies the temporary-access constraint.

Bucket-level IAM policies bind roles such as Storage Object Viewer to specific principals, so only granted team members can access objects. This satisfies the stem's constraint of restricting a single bucket to a named team, since IAM evaluates the binding per bucket rather than project-wide.

BigQuery's CSV loader automatically handles quoted fields containing commas when the schema is provided or autodetected. By default, it respects double quotes as field enclosures, so embedded commas within quotes are parsed correctly without additional options.

Datastream is a serverless change data capture (CDC) and replication service that supports Oracle as a source and BigQuery as a destination. It can connect to on-premises databases securely using private connectivity such as VPN or Interconnect. It requires no code to set up and provides low-latency replication, making it ideal for this scenario.

Allowed lateness specifies how long Dataflow should wait for late data after the watermark has passed the end of a window. By setting allowed lateness to 10 minutes, you ensure that data arriving up to 10 minutes late is still included in the window's aggregation. This is the correct mechanism to handle late-arriving data in streaming pipelines.

Allowed lateness specifies how long after a window closes the pipeline will accept and process late data. Setting it to 30 minutes matches the maximum delay, ensuring that late events are included in the correct window. This is the standard way to handle out-of-order data in Dataflow.

BigQuery load jobs read only from Cloud Storage or local upload, so external sources must first be staged in GCS. Uploading then loading uses the native, fastest ingestion path and satisfies the constraint that the file is not already in GCS.

BigQuery Storage Write API accepts continuous record streams over gRPC, committing rows with exactly-once semantics, so it satisfies the stem's streaming ingestion requirement rather than batch loading. It supports both real-time appends and transactional writes, making it suitable for pipelines needing low-latency ingestion into BigQuery tables.

Dataprep's data profiling scans source data to compute column-level statistics such as value distributions, null counts and distinct counts, letting analysts assess quality before transformation. This satisfies the stem's requirement for a native Dataprep capability, since profiling is performed directly within the Dataprep by Trifacta interface without external tooling.

Cloud Monitoring ingests Dataflow's native metrics — system lag, watermark delay and worker CPU — through the Cloud Dataflow integration, satisfying the requirement to track pipeline performance. Unlike Cloud Logging, which captures execution events, it exposes time-series dashboards and alerting policies on those streaming and batch metrics.

BigQuery load jobs parallelize across input files, so splitting the 4 TB file into many smaller files lets BigQuery read them concurrently, dramatically reducing load time. Specifying the pipe delimiter and skipping the header row ensures correct parsing. This is the recommended approach for large files and keeps costs low because you use the same load job capacity more efficiently. Compressed CSV is supported and splitting preserves the format.

Cloud Composer is Google Cloud's managed Apache Airflow service, provisioning the scheduler, web server, workers and metadata database while handling upgrades and scaling. This satisfies the stem's requirement for a managed way to run Airflow workflows, unlike self-managed Airflow on Compute Engine.

Storage Transfer Service performs server-side transfer directly between Amazon S3 and Cloud Storage, so data never traverses your local network or compute instances. This satisfies the stem's efficiency constraint by eliminating bandwidth bottlenecks and intermediate staging, while providing managed scheduling, retries and integrity verification for large-scale object migrations.

Push subscriptions let Pub/Sub actively deliver each message to a registered HTTPS endpoint, rather than waiting for the subscriber to poll. This satisfies the stem's requirement for a genuine Pub/Sub feature, since push delivery is a native subscription type alongside pull, with automatic retry and configurable acknowledgement deadlines.

Allowed lateness specifies how long Dataflow waits for late data after the watermark passes the end of a window. Setting it to 1 hour ensures that events arriving up to 1 hour late are still processed and included in the correct window, meeting the requirement.

Error handling is essential because production pipelines must tolerate transient failures without losing data. Dataflow's retry semantics only cover certain exceptions; application-level failures, malformed records, and dead-letter routing require explicit handling. Without it, a single bad element can stall or fail the pipeline, violating the reliability requirement for production readiness.

Cloud DLP de-identifies sensitive data using techniques such as masking, tokenisation, and format-preserving encryption before data lands in BigQuery, satisfying the requirement to anonymise PII prior to loading. Its infoType detectors identify PII automatically, so redaction happens at the pipeline stage rather than after ingestion, when exposure would already have occurred.

Parallelising across workers lets Dataflow distribute the streaming load horizontally, so throughput scales with worker count instead of bottlenecking on a single thread. This directly satisfies the high-throughput ingestion constraint by matching processing capacity to the incoming event rate.

Datastream provides serverless change data capture, continuously replicating ongoing changes from the source database into BigQuery. This satisfies the zero-downtime constraint by keeping both systems synchronised during migration, so cutover occurs without halting writes or losing transactions.

CMEK wraps data encryption keys with keys held in Cloud KMS under your control, so Google cannot decrypt objects without them. This satisfies the stem's constraint that keys be organisation-managed rather than Google-managed, unlike default encryption.

Filtering with a WHERE clause on the partition column enables partition pruning, so BigQuery scans only the relevant daily partitions rather than the entire table. This directly satisfies the stem's efficiency requirement for a day-partitioned dataset, cutting bytes processed and query cost.

BigQuery load jobs have a maximum file size limit (5 TB for uncompressed CSV, but practical limits and performance suggest splitting). Splitting into multiple files allows parallel loading, which is faster and avoids hitting per-job limits. This is the recommended approach for very large files.

The Storage Write API exactly-once feature deduplicates writes based on stream and offset, so Dataflow retries during restarts or bundle retries do not create duplicate rows. This directly addresses the goal of reducing duplicates at the sink while preserving streaming and low latency. It is the recommended write path for exactly-once streaming ingestion into BigQuery.

A side input join broadcasts the entire dataset to every worker's memory, so a large dataset exhausts each worker's heap and triggers OOM errors. This directly violates the stem's constraint of joining two large datasets. Instead, use a co-group or shuffle-based join, which partitions both datasets across workers rather than replicating one.

The Dataflow Monitoring UI exposes job graphs, step-level throughput, watermarks, worker CPU and autoscaling metrics in real time. It satisfies the stem's requirement for a valid Dataflow performance monitoring method by surfacing pipeline bottlenecks directly.

Cloud Lifecycle Management applies age-based Object Lifecycle Management rules directly on the bucket, deleting objects automatically once they exceed 90 days. This satisfies the efficiency constraint, avoiding custom scripts or scheduled jobs that list and delete objects individually.

Autoscaling dynamically adjusts the number of worker instances to match pipeline throughput, adding workers during backlog spikes and removing them when demand falls. This directly improves performance under load while cutting costs during idle periods, satisfying both optimisation goals in the stem.

Cloud Audit Logs records Admin Activity and Data Access events, capturing the identity, timestamp and resource for every GCS bucket request. Data Access logs specifically satisfy the requirement to track who accessed which bucket, since bucket reads and writes are data-plane operations that only this audit source records.

Autoscaling dynamically adds and removes worker nodes based on YARN metrics such as pending memory and available compute, which directly addresses the growing data volume without code changes. It respects the configured minimum and maximum worker bounds, so the cluster can expand during the heavy shuffle and write phases and shrink afterward, reducing both runtime and idle cost for this recurring job.

In BigQuery, a RECORD column is the equivalent of a STRUCT, so defining `user_info` as RECORD lets the load job map the nested JSON object into a structured field. Analysts can then reference subfields directly, and BigQuery enforces the nested schema. This is the correct choice for preserving nested JSON as a queryable STRUCT in a BigQuery table.

Dataflow is fully managed, removing the need to provision or maintain Compute Engine instances for pipeline execution. This satisfies the stem's benefit criterion by letting teams run Apache Beam pipelines without cluster administration, as the service handles worker scaling, patching and resource lifecycle automatically.

A Cloud Storage subscription is a native Pub/Sub export sink: it writes each message to a GCS bucket automatically, satisfying the no-custom-code constraint. No subscriber client, Dataflow pipeline or Cloud Function is needed, since delivery is handled entirely by the managed subscription itself.

Malformed or unexpected records break parsing and schema validation inside the pipeline, causing exceptions that halt processing. Dataflow's DoFn code cannot coerce corrupt payloads, so a single bad element can fail the bundle and retry indefinitely. This directly satisfies the stem's requirement by naming a common, recurring cause of pipeline failure.

Exactly-once delivery ensures that each message is delivered and processed only once, even in the face of retries or failures. This is critical for scenarios where duplicate processing would cause incorrect results. It works in conjunction with message ordering to provide both guarantees.

Cloud Monitoring ingests Cloud Storage metrics such as `storage.googleapis.com/storage/total_bytes`, letting you build an alerting policy on a threshold condition. This directly satisfies the stem's requirement to trigger when bucket size exceeds a set limit, since the metric is evaluated per bucket and fires notifications automatically.

The Dataflow Monitoring UI surfaces per-stage watermarks, system lag and backlog metrics, exposing exactly where elements stall within the pipeline. Unlike logs alone, it visualises streaming progress against the constraint in the stem — diagnosing stuck elements and latency — letting you pinpoint the bottleneck stage directly.

A Dead Letter Queue captures messages that repeatedly fail processing, letting the pipeline route malformed records aside instead of throwing exceptions that halt the pipeline. This satisfies the requirement to handle bad messages without crashing.

The bq load command ingests data directly from Cloud Storage into a BigQuery table without charging for a separate compute service, and it is the native, simplest path for a one-time CSV load. BigQuery charges only for storage after the load, so this meets the lowest-cost and no-transformation requirements for a 200 GB file.

A ParDo transform can access a side input containing the pipeline start time and replace null timestamps. This ensures records are not dropped and are assigned a valid timestamp. Side inputs are the correct way to provide a runtime value like pipeline start time to all elements in a PCollection.

The Cloud Console provides a graphical interface for launching a Dataflow job, letting you pick a template or specify pipeline parameters and start execution directly. This is a supported triggering method, unlike writing pipeline code or invoking the Dataflow API programmatically from a client.

Dataprep's Export feature writes transformed datasets directly to BigQuery, satisfying the requirement to persist cleaned data there. It handles schema mapping and load jobs natively, unlike running recipes that only produce results within the flow. This is the designed output path for moving prepared data into BigQuery without manual extraction.

Salting appends a random value to each key before the shuffle, spreading otherwise identical hot keys across many workers so a single worker no longer processes the bulk of records. This relieves the write bottleneck into BigQuery caused by skewed key distribution.

The Dataflow Update command replaces the job's transform graph while preserving the existing pipeline state, including buffered elements and watermarks. This satisfies the stem's no-state-loss constraint during a rolling update, unlike stop-and-restart approaches that discard in-flight data and reset streaming state.

Parquet is columnar and compressed, so BigQuery can load it much faster and at lower cost than uncompressed CSV. Splitting the data into multiple files allows BigQuery to parallelize the load across slots and workers. Since the data is already clean and needs no transformation, a direct load of Parquet is the fastest and most economical path for this 4 TB dataset.

Archive suits data accessed once yearly because its minimum storage duration is 365 days, matching that retrieval frequency without early-deletion charges. At roughly $0.0012 per GB monthly, it undercuts Coldline and Nearline, satisfying the stem's cost-effectiveness constraint while Microsoft Entra ID remains irrelevant to GCS class selection.

The --sdk_container_image pipeline option tells Dataflow which custom container image to pull for the worker environment. Specifying it satisfies the requirement to run the job with your own container rather than the default SDK image.

An ExternalTaskSensor polls the Airflow metadata database for the state of a task in another DAG, so the downstream transformation only proceeds once the partner load task has actually succeeded. This encodes the real cross-DAG dependency, works across DAGs in the same environment, and fails or retries based on the real upstream state rather than a fixed time.

Nearline satisfies the stem's requirement for a valid GCS storage class, offering low-frequency access with a 30-day minimum storage duration and retrieval costs. It sits between Standard and Coldline on the availability-versus-cost axis, making it a legitimate class alongside Standard, Coldline and Archive.

Dataflow pipelines can incorporate the Cloud DLP API as a transform to de-identify data in motion. You can read the CSV from Cloud Storage, apply the DLP transform to obfuscate PII, and write the cleaned data to BigQuery. This is a scalable and automated approach to prepare data with PII obfuscation.

Rename Columns applies a transformation across all column headers at once, letting you strip special characters and enforce consistent casing globally. This satisfies the stem's requirement to standardise inconsistent header names before loading into BigQuery.

Datastream is a serverless change data capture (CDC) service that supports MySQL as a source and can replicate changes to BigQuery in near real-time. It reads the database log (binlog) with minimal impact on the source and requires no custom code. This meets the requirements for continuous replication and low impact.

Dataflow workers are billed per vCPU-hour, and each worker carries startup and shuffle overhead. Overprovisioning workers for a small dataset multiplies cost and coordination overhead without improving throughput, since parallelism is bounded by data size.

Exactly-once delivery on the Pub/Sub subscription prevents redelivery of acknowledged messages, and the BigQuery Storage Write API in exactly-once mode ensures each record is written once even if the pipeline retries. Together they provide end-to-end duplicate elimination for streaming insertions, which matches the requirement to avoid duplicate rows.

Cloud Storage stores an MD5 hash for each object, retrievable via the object's metadata, letting you compare it against the locally computed hash of the uploaded file to confirm integrity, which is the standard verification method the stem asks for.

Watermarks track event-time progress in the pipeline, defining when a window's results are considered complete. Configuring an allowed lateness window lets Dataflow retain state and process events arriving after the watermark, directly satisfying the requirement to handle out-of-order sensor data from Pub/Sub.

Retention requirements dictate whether data must remain immutable for compliance periods, which rules out classes lacking Object Lock support or lifecycle deletion flexibility. Standard, Nearline, Coldline and Archive differ in minimum storage duration and retrieval costs, so mandated retention windows directly determine which class avoids early-deletion penalties.

Cloud Storage acts as a GCP ingestion tool because it accepts raw data files that pipelines then load into BigQuery or Dataproc, satisfying the stem's requirement for a landing zone where external sources deposit data before processing.

The --autodetect flag tells BigQuery to infer the schema from the data, and --skip_leading_rows=1 tells it to ignore the first row, which is the header. Together, these flags allow a seamless load of CSV files with headers into a new table. This is the standard approach for ad-hoc loads when you do not want to define a schema manually.

Google Cloud Storage automatically encrypts every object at rest using AES-256 before writing to disk, with no configuration required. This default server-side encryption satisfies the stem's requirement to protect sensitive data at rest, applying uniformly across all storage classes and regions without additional keys or setup from you.

Dataflow provides a distributed Apache Beam runner that scales horizontally across many workers, satisfying the large-dataset scalability constraint. Unlike single-node sampling engines, it parallelises cleansing transforms across the dataset, so processing time does not degrade as volume grows. Dataprep uses Dataflow as its default execution engine for exactly this reason.

IAM roles grant identities granular permissions on buckets and objects, letting you restrict who may read, write or administer GCS data. This satisfies the question's requirement for an access-control method, operating at the identity and resource-policy layer rather than encrypting the stored bytes.

Pub/Sub is a fully managed, globally scalable messaging service that decouples mobile producers from downstream consumers, absorbing unpredictable device traffic bursts. It satisfies the streaming ingestion constraint by providing durable, at-least-once delivery with automatic scaling, unlike batch-oriented alternatives such as Cloud Storage or Dataflow itself.

Datastream is a serverless change data capture and replication service that reads the PostgreSQL write-ahead log, captures inserts, updates, and deletes with minimal latency, and writes them to BigQuery without custom extraction code. It handles the primary key updates and schema changes as part of replication, matching the continuous, low-latency requirement.

Object Versioning retains noncurrent object generations after deletion or overwrite, letting you restore the prior generation for as long as you keep it. Combined with a lifecycle rule that deletes noncurrent versions after 30 days, it satisfies the stem's 30-day restorability constraint for deleted files in Cloud Storage.

Selecting a higher memory machine type gives each worker more RAM, preventing the out-of-memory crashes observed. This directly addresses the excessive memory consumption constraint by increasing available memory per worker rather than altering pipeline logic.

Cloud Functions provides event-driven, serverless execution triggered by GCS object finalisation events, so sporadic file arrivals invoke ingestion only when data lands. Dataproc and Dataflow need persistent clusters or streaming jobs, which waste resources during idle gaps between arrivals.

Increasing the number of workers allows Dataflow to parallelize the processing across more machines, which can significantly speed up a batch pipeline. Using a larger machine type provides more CPU and memory per worker, which helps if the transformations are resource-intensive. This is a common and effective way to optimize Dataflow performance when the pipeline is slow due to insufficient resources.

Session windows group elements separated by gaps of inactivity, merging windows when events arrive within the configured gap duration. This directly satisfies the stem's session-based activity requirement, where bursts of user actions must be captured as discrete sessions rather than fixed time intervals.

Cloud Data Fusion database source plugins support an incremental mode where you specify a column such as a last-modified timestamp or an incrementing ID. The plugin stores the last processed value and uses it to filter subsequent runs, so only new or changed rows are ingested. This directly satisfies the daily incremental load requirement without custom code.