Coding environments: SQL, Python, R and notebooks

Why this matters

Data analysts do not all need to be programmers, but they all work with code: a query pasted into a BI tool, a formula in a spreadsheet, a script that cleans a file every morning. Knowing which language suits which job, and which kind of environment to write it in, decides whether an analysis can be repeated, checked and shared.

The exam asks you to identify coding environments and match them to tasks. This lesson covers the languages analysts use most, the environments they write them in, and version control, which turns code into a record of how an analysis was built.

The lesson

SQL as the language every data tool eventually speaks

SQL (Structured Query Language) is the standard language for relational databases, and the one language nearly every data analyst uses. It is declarative: you describe the result you want, such as "total sales by region for 2025", and the database decides how to compute it.

SQL covers several kinds of statement. Queries that read data use SELECT with clauses such as WHERE, JOIN and GROUP BY; statements that change data use INSERT, UPDATE and DELETE; and statements that define structure use CREATE, ALTER and DROP. Analysts spend almost all their time in the first group, and are often given access to nothing else.

SQL's reach is its strength. Every major database speaks it, most cloud warehouses use it, most BI tools generate it behind their drag-and-drop interfaces, and many data-lake engines accept it too. Each product adds its own dialect, with small differences in functions and syntax, such as how dates are handled or how many rows to return is limited, but the core carries over. Domain 2 of this course teaches SQL in depth.

Python and R for analysis, and the libraries each is known for

For work beyond querying, such as complex cleaning, statistics, modelling and automation, analysts use a general-purpose language.

Python is a general-purpose programming language with a large ecosystem for data. pandas provides the DataFrame, a table that can be filtered, joined, grouped and reshaped in code. NumPy provides fast numerical arrays. matplotlib and seaborn draw charts. scikit-learn provides machine learning, and SciPy and statsmodels provide statistical tests and models. Python's advantage is breadth: the same language cleans the data, calls an API, automates a report and builds a model.

R is a language designed specifically for statistics and graphics. Its strengths are the depth of its statistical methods and the quality of its charts. The tidyverse collection, including dplyr for data manipulation and ggplot2 for charts, is the most widely used set of packages, and R Markdown or Quarto produce reports that combine code, results and prose.

Both are free and open source, both are widely used, and both can do most analytical tasks. Python is more common where analysis must connect to other software and automation; R is more common in statistics, research and some specialist fields. Many teams use both, with SQL underneath.

Notebooks against scripts, and the hidden-state trap in notebooks

Code can be written as a script or in a notebook.

A script is a plain text file of code, run from start to finish. Running it twice does the same thing twice. Scripts suit production work: scheduled jobs, pipelines and anything that must run unattended.

A notebook, such as a Jupyter notebook, mixes code, output, charts and explanatory text in cells that can be run one at a time. Notebooks are excellent for exploration and for explaining an analysis, because each step's result appears beneath its code.

Notebooks have one serious trap: hidden state. Cells can be run in any order, edited and re-run, and the notebook remembers every variable from every cell run so far. A notebook can therefore show a result that the code as currently written would not produce: a cell was changed after it ran, or a cell that defined a variable was deleted, but the variable still exists in memory. The protection is simple: before trusting or sharing a notebook, restart it and run every cell from the top in order. If it still produces the same results, they are real.

Integrated development environments and the command line

An integrated development environment (IDE) is an editor built for writing code, with features such as syntax highlighting, error checking, debugging and integrated version control. Common ones for data work include Visual Studio Code, RStudio (now from Posit) for R, PyCharm for Python, and database tools for writing and running SQL. Many IDEs can also open notebooks.

The command line, or terminal, runs programs by typed commands. For an analyst it is where scripts are run, packages are installed, scheduled jobs are defined, and quick checks are made: counting the lines in a file, looking at its first rows, or comparing two outputs. It is also how most automated and remote environments are used, since a server running a pipeline has no desktop to click on.

The choice of environment matters less than the habit it supports: code kept in files, run the same way each time, rather than steps performed by hand.

Version control for analysis code

Version control records every change made to a set of files, with who made it, when and why. Git is by far the most widely used system, usually with a hosting service such as GitHub, GitLab or Bitbucket for sharing.

In Git, a snapshot of the files is saved as a commit with a message describing the change. Branches let work on a change proceed separately until it is ready, and a merge or pull request brings it back, often after someone has reviewed it. The history lets anyone see what changed between two versions and return to an earlier one.

For analysis, version control answers questions that otherwise have no answer. Why did last month's figure change when it was rerun? The history shows the query was edited. Which version of the cleaning logic produced the published report? The commit recorded at release says. Who approved a change to a key metric's definition? The review records it. Code should be versioned; large data files usually should not, but the scripts that produce them should, along with a record of which data they were run against.