Google Data Analytics Professional Certificate Process Data from Dirty to Clean Syllabus

What does the CONCATENATE function do in a spreadsheet?

CONCATENATE joins together two or more text strings (from different cells) into a single string, e.g., combining separate first-name and last-name columns into a full-name column.

What does the TRIM function do and when is it used?

TRIM removes leading, trailing, and repeated (extra) spaces from text in a cell. It is used to fix inconsistent spacing that can cause matching and sorting errors.

What is the difference between the SPLIT (Text to Columns) feature and the CONCATENATE function?

SPLIT / Text to Columns divides a single text string into separate columns based on a delimiter (a character such as a comma that separates items). CONCATENATE does the opposite: it combines strings from multiple cells into one.

What is data validation in spreadsheets and what can it enforce?

Data validation is a spreadsheet tool that controls what can and cannot be entered in a worksheet. It can add dropdown lists of allowed values, restrict entries to a data type or range, create checkboxes, and protect structured data by rejecting invalid inputs at entry time.

What is conditional formatting and how does it help with data cleaning?

Conditional formatting is a spreadsheet tool that changes how cells appear (color, style) when their values meet specified conditions. In cleaning, it visually flags problems such as blanks/nulls, duplicates, outliers, or values outside an expected range so they are easy to spot and fix.

Why might an analyst 'view only the fields that matter' during cleaning, and which conditional formatting rule helps find missing values?

Hiding or excluding irrelevant fields focuses attention on the data that affects the analysis. A conditional formatting rule such as 'format cells if cell is empty' highlights blanks/nulls in the relevant fields so missing data can be addressed.

Why is SQL preferred over spreadsheets for large datasets?

SQL works directly where the data lives in a database, can process huge datasets (millions or billions of rows) far faster than spreadsheets, allows many users to access and query the same data simultaneously, tracks queries so changes can be reviewed and repeated, and is the standard language of relational databases.

In SQL, what do the DISTINCT keyword and the COUNT function accomplish in data cleaning?

SELECT DISTINCT returns only unique values, removing duplicate rows from the result. COUNT returns the number of rows matching a criterion; COUNT(DISTINCT column) counts only the unique values, which helps detect duplicates.

What do the SQL functions LENGTH, SUBSTR, and TRIM do?

LENGTH returns the number of characters in a string (used to verify consistent field lengths). SUBSTR extracts a substring, taking the string, the starting position, and the number of characters to return. TRIM removes leading and trailing spaces from a string.

What is the SQL CAST function used for?

CAST converts data from one data type to another — a process called typecasting — e.g., CAST(purchase_price AS FLOAT64) converts a string field to a number so it can be sorted and calculated correctly.

Which SQL statements are used to add, change, and remove data, and what does CASE do?

INSERT INTO adds new rows to a table, UPDATE ... SET changes existing values, DELETE removes rows, and DROP TABLE removes a table entirely. The CASE statement returns values based on conditions (WHEN ... THEN ... ELSE ... END), commonly used to correct misspelled or inconsistent values in query results.

What is a text/string data type, and why must string variables be cleaned?

A text (string) data type is a sequence of characters and punctuation containing textual information. Strings must be cleaned because inconsistent capitalization, extra spaces, misspellings, and inconsistent lengths cause mismatches — e.g., 'CA', 'ca ', and 'California' would be treated as different values.

Which spreadsheet/SQL functions standardize the capitalization of strings?

UPPER converts all text to uppercase, LOWER converts all text to lowercase, and PROPER (spreadsheets) capitalizes the first letter of each word. Standardizing case ensures identical values match during analysis.

How can COUNTIF and LENGTH-type functions be used to validate string variables like ID codes?

If every ID should be, say, 6 characters, LEN/LENGTH reveals entries with the wrong length (e.g., in SQL: WHERE LENGTH(id) > 6, or in sheets: =COUNTIF(range, condition) to count strings failing the rule). This flags typos and malformed strings for correction.

What is a substring, and how would you extract just a state code from a string in SQL?

A substring is a subset of a text string. SUBSTR(string, start, length) extracts it — e.g., SUBSTR(customer_state, 1, 2) returns the first two characters, isolating a two-letter state code.

What is the difference between data merging and data mapping?

Data merging is the process of combining two or more datasets into a single dataset. Data mapping is the process of matching fields from one data source to fields in another (matching schemas, formats, and conventions), which is essential first so merged data remains compatible and accurate.

What is data compatibility and why does it matter when combining datasets?

Compatibility is how well two or more datasets are able to work together. Before merging, an analyst must confirm the datasets use the same conventions (units, date formats, naming, granularity); incompatible data merged together produces misleading or broken analysis.

What key questions should you ask before merging datasets?

Do I have all the data I need? Do the datasets use the same terminology, conventions, units, and formats? Does the data need to be cleaned or standardized first? Are there duplicate or conflicting records across the sources?

What are common file/format types and tools involved in transforming data?

Data often arrives as CSV (comma-separated values) files that must be imported and split into columns. Transformation tools include spreadsheet functions (SPLIT, CONCATENATE, CAST-like conversions), SQL (CAST, JOIN, UNION for combining tables), and dedicated cleaning tools like OpenRefine; converting data types (e.g., text to number or date) is a core transformation step.

What is verification in the data-cleaning process?

Verification is the process of confirming that a data-cleaning effort was properly and completely executed — checking the cleaned data against the original dirty data to confirm all identified errors were actually fixed and no new errors were introduced.

What does it mean to 'see the big picture' when verifying data cleaning?

It means taking three steps: consider the business problem (does the data still serve the original question?), consider the goal (what does the stakeholder need from the data?), and consider the data itself (has cleaning changed it in a way that affects the outcome?). Verification confirms the data is fit for purpose, not just error-free.

Which tools help verify cleaned data in spreadsheets and SQL?

Spreadsheets: Find and Replace to locate remaining errors, pivot tables and COUNTIF/SUMIF to summarize and spot anomalies, sorting and filtering to inspect extremes. SQL: CASE statements to check and correct conditional values, COUNT/GROUP BY to confirm expected distributions, and TRIM/LENGTH checks on strings.

What is a changelog and why is it important in data cleaning?

A changelog is a file containing a chronologically ordered list of modifications made to a project or dataset. It documents what was changed, when, by whom, and why — enabling the team to recover from mistakes, keep everyone informed, ensure the cleaning is reproducible, and hold the analyst accountable.

What information should each entry in a data-cleaning changelog include?

The changes made (what was modified, added, or removed), the date, the person who made the change, and the reason for the change — plus enough detail (e.g., number of rows affected) that someone else could understand or reverse the change.

How is documentation of cleaning done in spreadsheets versus SQL?

Spreadsheets have built-in version history (e.g., Google Sheets: File > Version history; per-cell edit history via right-click > Show edit history), plus a manually maintained changelog file. SQL keeps a record of executed queries (query history), and analysts document changes as comments within queries and in changelogs.

What are best practices for reporting clean data results to stakeholders?

Communicate honestly about what the data can and cannot show, including limitations and any remaining data issues; explain the cleaning steps taken; tailor the message to the audience's technical level; set realistic expectations about accuracy and margin of error; and remain transparent so stakeholders can trust the conclusions.

Why is it important to report data-cleaning problems and limitations rather than hide them?

Because decisions are made from the data: hiding limitations leads stakeholders to overtrust flawed conclusions. Reporting issues builds credibility, prevents costly downstream mistakes (dirty data costs businesses millions annually), lets stakeholders adjust the objective or timeline, and maintains the analyst's integrity.

What key data-cleaning skills should a data analyst be able to highlight to employers?

Technical skills: spreadsheets (functions, pivot tables, data validation), SQL (querying, cleaning, transforming large datasets), and familiarity with data-cleaning verification and documentation. Transferable skills: attention to detail, critical thinking, problem-solving, and clear communication with stakeholders.

What are the key formatting guidelines for a professional data analyst resume?

Keep it to one page with a clean, consistent layout; a hiring manager typically spends only a few seconds scanning it. Include contact information, a summary/objective, work experience with quantified accomplishments, skills (technical and transferable), and education. Tailor keywords to the specific job description.

How should accomplishments be phrased in the experience section of a data analyst resume?

Use the formula: strong action verb + task/accomplishment + quantified metrics + result/impact. Example: 'Cleaned and standardized a 500,000-row customer dataset using SQL, reducing duplicate records by $30\%$ and cutting report preparation time in half.' Focus on measurable outcomes, not just duties.