Associate Data Practitioner — Quick review

Memory hook: Shape, clean, move, store.

Must remember

• ETL transforms before loading; ELT loads before transforming in the destination; ETLT can split work across both. Choose based on privacy, target capabilities and transformation cost.
• Profile nulls, duplicates, invalid types, outliers and inconsistent units. Preserve raw data and record transformation rules so cleaning is reproducible.
• CSV is simple but weakly typed; JSON handles nested records; Avro is row-oriented with a schema; Parquet is columnar and efficient for analytical column scans.
• Cloud Storage holds objects; BigQuery serves analytics; Cloud SQL serves conventional relational applications; Spanner serves horizontally scalable relational workloads; Firestore serves document applications; Bigtable serves high-throughput key-based access.
• Storage Transfer Service moves supported object/file sources; BigQuery Data Transfer Service schedules supported analytics imports; Database Migration Service handles supported database migrations. A physical Transfer Appliance addresses very large transfers with constrained bandwidth.
• Use Dataflow for Beam processing, Data Fusion for visual integration, and SQL for warehouse transformations. Match locations across storage, datasets and processing to residency, availability and transfer-cost requirements.

Choose under exam pressure

Traps

• BigQuery is not a drop-in low-latency transactional database.
• Moving bytes successfully does not prove the data is complete or correctly typed.

Practise this topic

Memory hook: Question, grain, query, chart.

Must remember

• Define the business question and row grain before writing SQL. Joins can multiply rows; aggregate at the intended grain and validate totals against source data.
• Use SELECT, WHERE, JOIN, GROUP BY and window functions deliberately. Filter partition columns and select required fields to reduce scanned data; inspect the query plan and bytes estimate.
• Jupyter or Colab Enterprise notebooks combine code, explanation and charts for exploration. Keep credentials out of cells, pin dependencies and separate experiments from scheduled production work.
• Looker uses a governed semantic model expressed through LookML; dimensions describe attributes, measures define aggregates and Explores expose joins. Correct join relationships help avoid double counting.
• Looker Studio provides accessible dashboards and connectors; Looker suits governed reusable metrics and embedded enterprise analytics. Neither fixes incorrect source definitions.
• Choose charts by question: lines for change over time, bars for category comparisons, scatterplots for relationships. Label units and time zones; share dashboards only with appropriate data permissions.

Review details

SQL recall: WHERE filters input rows, GROUP BY sets aggregation grain, HAVING filters aggregate results, and window functions calculate across related rows without collapsing every row. COUNT(*) counts rows; COUNT(column) skips nulls. A left join preserves unmatched left rows, but a right-side filter in WHERE can unintentionally remove them. Check cardinality before blaming the chart for inflated revenue.

Read-only pattern: SELECT department, COUNT(*) AS n FROM employees GROUP BY department HAVING COUNT(*) > 5. Explain which records are grouped and why the aggregate condition belongs in HAVING. In LookML, views/dimensions/measures define reusable structure; an Explore exposes relationships. Correct join declarations and distinct primary keys matter for trustworthy aggregates.

Choose under exam pressure

Traps

• A dashboard filter is not a substitute for enforced row access.
• A visually convincing chart can still contain duplicated join totals.

Practise this topic

Memory hook: Train on history; test on unseen data.

Must remember

• Choose classification for categories, regression for numeric targets, forecasting for future time values and clustering for unlabeled groups. A deterministic SQL rule may be sufficient before introducing ML.
• BigQuery ML lets analysts create and use models through SQL. CREATE MODEL trains supported model types; ML.EVALUATE measures quality; ML.PREDICT runs inference for applicable models.
• Separate training, validation and test data. Split time-series data chronologically and avoid features that reveal the target or would not exist at prediction time.
• Accuracy can mislead on rare events: compare precision, recall and the cost of false positives versus false negatives. Regression needs magnitude-sensitive error metrics; forecasts need time-aware evaluation.
• AutoML automates parts of model training; pretrained models avoid training a general capability from scratch. BigQuery remote models use connections and authorized access to supported external model services.
• Register models with versions and lineage so a deployment can be traced to its training data and evaluation. Monitor performance after deployment and plan controlled retraining.

Review details

Precision = TP/(TP+FP) asks how many predicted positives were correct; recall = TP/(TP+FN) asks how many actual positives were found. Missing rare fraud may demand recall; wasting analyst time may demand precision. A 99% accurate model that predicts “not fraud” for every row can still be useless on a 1% fraud dataset.

Model lifecycle in SQL: train with CREATE MODEL, evaluate on separate data, then call the appropriate inference function. ML.PREDICT serves supported prediction models; ML.FORECAST serves supported forecasting models. A remote generative model uses its supported function/API and authorized connection, not an assumption that every model supports the same SQL operation.

Choose under exam pressure

Traps

• Excellent training accuracy is not evidence of generalization.
• AutoML still needs suitable data, target definitions and evaluation.

Practise this topic

Memory hook: Transform work; orchestrate dependencies.

Must remember

• Dataflow executes Beam batch/stream pipelines; Dataproc runs Spark/Hadoop workloads; Data Fusion provides visual integration; Dataform manages SQL transformation dependencies and assertions in BigQuery.
• An orchestrator coordinates work rather than replacing every processing engine. Cloud Composer provides managed Airflow; Workflows coordinates service/API steps; scheduled queries fit simple recurring SQL; Dataproc workflow templates coordinate supported cluster jobs.
• Cloud Scheduler supplies time-based triggers. Eventarc routes matching events to destinations; Pub/Sub decouples producers and consumers. A Pub/Sub BigQuery subscription can deliver supported messages without a custom transformation service.
• Make retries safe with idempotent writes, stable event identifiers and checkpoints. Route malformed records to a review path rather than silently losing them or blocking all progress.
• Monitor freshness, failed runs, input/output counts, backlog and processing latency. Dataflow job views expose pipeline progress; Cloud Logging supplies event details and Cloud Monitoring alerts on actionable symptoms.
• Separate development and production identities, configuration and data. Backfills must handle historical partitions without corrupting current output.

Choose under exam pressure

Traps

• Cloud Scheduler does not guarantee that downstream business processing happened exactly once.
• A green job can still produce stale or empty data.

Practise this topic

Memory hook: Permission, retention, recovery, keys.

Must remember

• IAM permissions are bundled into roles. Prefer narrowly scoped predefined roles over broad basic roles; separate permission to run BigQuery jobs from permission to read particular datasets.
• Uniform bucket-level access centralizes Cloud Storage authorization through IAM rather than object ACLs. Public access prevention blocks accidental public exposure; signed sharing needs a deliberate expiry and audience.
• Analytics Hub, also described as BigQuery sharing, distributes governed data listings without requiring each subscriber to maintain unmanaged exports. Verify the permissions and region of shared datasets.
• Choose storage class by access pattern and minimum storage duration, not headline storage price alone. Lifecycle rules can transition or delete objects; table and partition expiration remove aged analytical data.
• Replication improves availability; independent backups and point-in-time recovery address corruption or deletion. Define RPO and RTO, choose regional/dual-region/multi-region placement appropriately, and test recovery.
• Google-managed keys are the default for many services; CMEK gives control through Cloud KMS; customer-supplied keys require the customer to supply key material for supported operations. At-rest encryption does not replace TLS, authorization or privacy controls.

Choose under exam pressure

Traps

• Deleting a key can make retained data unusable.
• An archive class can charge retrieval and early-deletion fees.

Practise this topic