SQL stands for Structured Query Language. It is a language used to store, manage, and retrieve information from relational databases.

In other words, SQL allows you to write instructions to a database. You can use it to store new data, update existing data, delete data, search for specific information, and retrieve results.

SQL was developed in the 1970s, based on the relational data model. Oracle was one of the first vendors to offer a commercial SQL-based relational database management system.

What is an SQL system?

SQL is used by relational database management systems, also called RDBMS. An RDBMS is software that stores data in relational databases and lets users interact with that data using SQL.

Examples of RDBMS include:

• PostgreSQL

• MySQL

• Oracle Database

• Microsoft SQL Server

• Microsoft Access

• SQLite

These systems can be slightly different from each other, but they all share the same general idea: they store structured data in tables and allow users to query that data with SQL.

SQL tables

A table is one of the basic elements of a relational database. It consists of rows and columns.

• A row represents one record. For example, one customer, one order, or one product.

• A column represents one type of information. For example, a customer name, an email address, a date, or a price.

Here is a simple example of a customers table:

Database engineers can create relationships between multiple tables to organize data better and avoid repeating the same information everywhere.

For example, instead of storing all customer information inside every order, we can have one customers table and one orders table. The two tables can then be connected using keys.

SQL statements

SQL statements, also called SQL queries, are valid instructions that a relational database management system can understand.

A SQL statement is built using different SQL elements, such as keywords, table names, column names, conditions, and values.

For example:

SELECT *

FROM customers;

This means: “Show me all columns from the customers table.”

SQL statements must follow correct syntax. If the syntax is wrong, the database will not understand the instruction.

Stored procedures

A stored procedure is a saved collection of one or more SQL statements. Instead of writing the same SQL logic again and again, you can save it inside the database and call it when needed.

For example, a company could create a stored procedure that calculates monthly sales, updates a reporting table, or checks customer activity.

How does SQL work?

When you write and run a SQL query, the database system does several things behind the scenes.

The exact process depends on the database system, but the general idea looks like this:

1. The parser checks the query

First, the database reads the SQL statement. The parser breaks the query into smaller parts and checks whether the syntax is correct.

For example, it checks whether the SQL keywords are used properly, whether the table exists, and whether the columns mentioned in the query are valid. It may also check permissions. 

2. The query processor creates a plan

Then, the query processor decides how to execute the query. This is important because there can be several ways to get the same result.

For example, if you ask for all customers in Italy, the database has to decide how to find those rows efficiently. It might scan the full table, or it might use an index if one exists. The database creates an execution plan, which is basically its strategy for answering the query.

3. The storage engine reads or writes the data

Finally, the storage engine interacts with the actual data. It reads data from storage, writes new data, updates existing data, or deletes data depending on the SQL statement.

Once the work is done, the database returns the result to the application or user.

For a SELECT query, the result might be a table of rows.

For an UPDATE or DELETE query, the result might simply confirm that the operation was completed.

What are SQL commands?

SQL commands are specific instructions used to work with data and database structures. They are often grouped into categories:

Data Definition Language, or DDL

DDL commands are used to define or change the structure of a database.

For example, you can create, modify, or delete tables.

Example:

CREATE TABLE customers (

  customer_id INT,

  name VARCHAR(100),

  email VARCHAR(100)

);

This creates a new table called customers. Other DDL commands include ALTER and DROP.

Data Query Language, or DQL

DQL commands are used to retrieve data from a database. The main example is SELECT.

Example:

SELECT name, email

FROM customers;

This retrieves the name and email columns from the customers table.

Data Manipulation Language, or DML

DML commands are used to add, update, or delete data inside tables. Examples include INSERT, UPDATE, and DELETE.

Example:

INSERT INTO customers (customer_id, name, email)

VALUES (1, 'Maya', 'maya@example.com');

This adds a new customer to the customers table.

Data Control Language, or DCL

DCL commands are used to manage access and permissions.

Examples include GRANT and REVOKE.

Example:

GRANT SELECT ON customers TO analyst_user;

This gives a user permission to read data from the customers table.

Transaction Control Language, or TCL

TCL commands are used to manage transactions. A transaction is a group of database operations that should be treated as one unit.

For example, when transferring money between two bank accounts, you do not want only half of the operation to happen. You want both updates to succeed, or both to fail.

Example:

ROLLBACK;

This cancels changes made during the current transaction.

Other TCL commands include COMMIT and SAVEPOINT.

Other Concepts

SQL dialects

SQL is a standard language, but not every database system uses it in exactly the same way. Different database systems have their own versions of SQL. These versions are called SQL dialects.

For example, PostgreSQL, MySQL, Oracle Database, and Microsoft SQL Server all use SQL, but they may have small differences in syntax, functions, data types, and features.

A simple query like this will work in many SQL systems:

SELECT name, email

FROM customers;

But more specific operations may look different depending on the database. For example, limiting the number of results can vary.

In PostgreSQL and MySQL, you can write:

SELECT *

FROM customers

LIMIT 10;

In Microsoft SQL Server, you might write:

SELECT TOP 10 *

FROM customers;

Indexes

An index helps a database find data faster.

Think of an index in a book. Without an index, you might need to read many pages to find a specific topic. With an index, you can jump more quickly to the right page.

A database index works in a similar way. It helps the database locate rows without scanning the entire table every time.

Joins

A join allows you to combine data from multiple tables. In relational databases, data is often separated into different tables to avoid repetition. For example, you might have one table for customers and another table for orders.

The customers table could look like this:

And the orders table could look like this:

If we want to know which customer made which order, we need to combine the two tables. That is where a join comes in.

SELECT customers.name, orders.order_id, orders.amount

FROM customers

JOIN orders

ON customers.customer_id = orders.customer_id;

This query connects the two tables using the customer_id column. The result would look like this:

This makes the data much easier to understand. Instead of only seeing a customer ID in the orders table, we can see the customer name too.

There are different types of joins.

• An INNER JOIN returns only the rows where there is a match in both tables.

• A LEFT JOIN returns all rows from the first table, even if there is no matching row in the second table.

Conclusion

SQL is one of the most important languages in data management. 

It allows users to communicate with relational databases using clear instructions. With SQL, you can create tables, insert data, update records, delete information, manage permissions, and retrieve exactly the data you need.

You have probably heard of structured and unstructured data, but the difference can still feel a bit confusing.

Let’s tackle it together.

What is structured data?

Structured data is organized data. It usually exists in a tabular format, with rows and columns.

A simple example would be a spreadsheet, like Google Sheets or Microsoft Excel. You have columns with clear labels, such as “Customer ID,” “Name,” “Email,” or “Purchase Date,” and each row contains one record.

Structured data is easy to search, sort, filter, update, and analyze because everything follows a clear structure.

For example, imagine a table of customers:

Each column has a specific meaning, and each row represents one customer.

Structured data is often stored in relational databases. These databases use SQL, which stands for Structured Query Language. SQL is used to create, read, update, and delete data stored in tables.

What is unstructured data?

Unstructured data does not follow a predefined tabular structure.

Think of images, text documents, videos, audio files, emails, PDFs, or social media content. These files can contain a lot of useful information, but that information is not organized neatly into rows and columns.

For example, a customer review is unstructured data. It may contain opinions, emotions, complaints, or suggestions, but those insights are hidden inside free text.

Unstructured data is easy to collect, but harder to analyze directly. To use it properly, we often need extra techniques such as tagging, text analysis, natural language processing, image recognition, or machine learning.

Structured vs Unstructured

The main difference is the way the data is organized.

• Structured data follows a clear format. It is easier to search, group, sort, and analyze because the structure is already defined.

• Unstructured data does not follow a fixed table-like format. It can contain rich information, but that information needs more processing before it can be analyzed.

Structured data is often associated with quantitative data, because it commonly contains numbers or clearly categorized values. For example: prices, dates, IDs, quantities, or product categories.

Unstructured data is often associated with qualitative data, because it can contain opinions, descriptions, images, speech, or other forms of less organized information.

That said, the difference is not always perfect. A text field in a database can contain qualitative information, and an image file can still be analyzed to extract measurable information.

Databases for different types of data

Structured data often lives in relational databases, also called relational database management systems, or RDBMS.

Relational Database

A relational database organizes data into tables. Each table contains rows and columns.

• Rows can also be called records. A row usually represents one item, person, transaction, or event.

• Columns can also be called fields. A column describes the type of information stored in the table, such as a name, date, price, email address, or status.

The configuration of tables, columns, data types, and relationships makes up the schema of the database.

For example, a company might have one table for customers and another table for orders.

Primary keys

A primary key is a column, or a combination of columns, that uniquely identifies each row in a table.

In simple terms, it is like the official ID of a record.

For example, in a customer table, the column customer_id could be the primary key:

Here, each customer has a different customer_id.

That matters because names and emails can sometimes change, but the ID gives the database a stable way to identify each customer.

A primary key should be:

• Unique.

• Not empty.

• Stable.

Foreign keys

A foreign key is a column in one table that refers to the primary key of another table.

In simple terms, a foreign key creates a link between two tables.

For example, imagine we have a second table called orders:

In this orders table, order_id is the primary key because it uniquely identifies each order.

But customer_id is a foreign key because it refers to the customer_id in the customer table.

This tells us which customer made each order.

So if order 101 has customer_id = 1, we can go back to the customer table and see that this order belongs to Maya.

Relational databases and SQL

Relational databases use SQL to access, query, and manipulate stored data.

SQL stands for Structured Query Language. It is used to perform actions such as creating tables, inserting data, updating records, deleting records, and asking questions about the data.

SQL syntax is relatively close to English, which makes it easier to read and understand than many programming languages.

For example, a SQL query can look like this:

SELECT first_name, email FROM customers WHERE country = 'France';

This simply means: “Show me the first name and email of customers who are based in France.”

NoSQL Database

Examples of relational databases

There are many relational database management systems. They all follow the same general idea: storing structured data in tables and using SQL to interact with it. 

• PostgreSQL is a free and open-source relational database. It is known for being powerful, reliable, and rich in features. It supports SQL, but it also supports more advanced features such as JSON querying, full-text search, extensions, and custom data types.

• MySQL is also a very popular open-source relational database. It is known for being fast, reliable, and widely used in web development. Many websites and applications use MySQL because it is easy to start with, has a large community, and works well with common web technologies.

You might have heard terms like data lake, data warehouse, and data mart and wondered what to do with all of them. They sound similar, but they do not play the exact same role.

Before comparing everything, let’s take it one concept at a time. Today, we are tackling data lakes.

The term “data lake” began to gain traction around 2010 as a new way to think about storing, managing, and analyzing large volumes of data.

What is a data lake?

In simple terms, a data lake is a central repository that can hold large volumes of data in its original format.

That data can be:

• structured, like tables from a database;

• semi-structured, like JSON, XML, or log files;

• unstructured, like images, videos, PDFs, emails, or text documents.

The main idea is that you do not always need to know exactly how the data will be used before storing it. A data lake gives you a place to collect different types of data first, then explore, transform, or analyze it later.

This is why data lakes are often described as using a schema-on-read approach: the structure is applied when the data is read or analyzed, not necessarily before it is stored.

Modern data lakes are often built on object storage. Instead of putting everything into rigid tables from day one, data is stored as files or objects. Then, metadata, catalogs, access rules, partitions, and data engineering practices help people find, organize, secure, and use the data properly.

Why use a data lake?

A data lake can be useful when an organization has a lot of different data coming from many sources.

For example, a company might want to keep customer transactions, website clicks, application logs, IoT sensor data, and support messages in one place. Some of that data may be useful for dashboards. Some of it may be useful for machine learning. Some of it may simply need to be stored for future analysis.

Data lakes are especially helpful for organizations that want to build a strong analytics culture, experiment with data, train machine learning models, or support research and advanced insights.

That said, a data lake is not just a place to dump everything and hope for the best. Without governance, metadata, quality checks, and clear ownership, it can quickly become messy. People sometimes call this a data swamp: the data is there, but nobody can easily understand, trust, or use it.

What is the difference between a data lake and a data warehouse?

A data warehouse is usually more structured. Before data is loaded, it is cleaned, transformed, and organized into a defined schema. This makes data warehouses very useful for reporting, dashboards, and business intelligence, because the data is already prepared for analysis.

This approach is often called schema-on-write: the structure is defined before or during the loading process.

A data lake, on the other hand, is more flexible. It can store structured, semi-structured, and unstructured data in its raw or near-raw format. The data does not always need to be transformed before it lands in the lake.

This approach is often called schema-on-read: the structure is applied later, when someone needs to query or analyze the data.

You may also hear about data marts. A data mart is a smaller, more focused repository built for a specific team, department, or business area. For example, a finance team might have a finance data mart, while a marketing team might have a marketing data mart.

So, very simply:

• a data lake stores many types of data, often in raw form;

• a data warehouse stores structured, cleaned data for analysis and reporting;

• a data mart is a smaller, focused subset of data for a specific business group.

How does a data lake work?

First, it depends a lot on the organization. You can decide to have storage and compute resources on-premises, in the cloud, in a hybrid configuration, and so on.

Let’s start with the data sources.

Data Sources

As mentioned before, you usually deal with three main types of data:

• Structured data sources: this data comes from relational databases and tables. Examples include Google Cloud SQL and Azure SQL Database.

• Semi-structured data sources: this data has some organization, but it does not fit neatly into a tabular structure. It might have tags, keys, or a hierarchy, but it still needs some processing before being fully structured. Examples include JSON files or XML file.

• Unstructured data sources: this includes a wide range of data types without a predefined structure. Examples include media files or IoT data.

The second step is ingesting this data. This leads us to data ingestion.

Data ingestion

Data ingestion is the process of importing data into the data lake from different sources. Think of it as the gateway through which data enters the lake before being processed.

There are two main modes:

• Batch ingestion: this is a scheduled, interval-based method. Large chunks of data are ingested at a time. Examples of tools include AWS Glue and Azure Data Factory.

• Real-time ingestion: this brings data into your data lake as it is generated. This is important for time-sensitive applications, like fraud detection. Examples of tools include Amazon Kinesis Data Streams and Azure Event Hubs.

For this, you can use different protocols, APIs, or connection methods to link internal and external data sources. Having the right connectors helps ensure smooth data flows.

Once the data is in, we have to process and store it.

Data storage and processing

Once the data is ingested, it needs to be stored.

First, ingested data lands in the raw data store section, also called the landing zone. The data is kept in its native format, and the raw data store acts as a repository where data is staged before anything is done to it. Examples of solutions include Amazon S3 and Azure Data Lake Storage Gen2.

Then comes the transformation section. This is where we can transform the data; we can cleanse it by removing or correcting inaccurate records, discrepancies, and inconsistencies. We can also enrich the data by adding information or context, normalize it, structure it, and prepare it for future use.

Once that is done, the data becomes trusted data. It is reliable, clean, and suitable for analytics and machine learning models.

Now that the data has been transformed, it is moved to the refined or conformed data zone. More transformations may still be possible depending on the use case, but refined data is usually what analysts will interact with.

Tools like Amazon Athena or Google BigQuery may be used for querying this refined data.

Analytical Sandboxes

Analytical sandboxes are isolated environments used for data exploration. They allow activities like machine learning, predictive modeling, and data analysis without affecting the main storage and transformation layers.

This separation is important because analysts and data scientists can experiment freely without compromising the integrity or quality of the data in other zones.

Both raw and processed data can be used in these sandboxes. Raw data is useful for exploratory work, especially when the original context matters. Processed data is better suited for refined analytics, machine learning models, and business-ready analysis.

A few activities that can happen in analytical sandboxes include:

• Data discovery: this is when analysts and data scientists explore data to understand its structure, quality, and potential value. They might use statistics, summaries, or data visualization to get a first understanding of what the data contains.

• Machine learning and predictive modeling: this is where teams use data to train models, make predictions, or identify patterns. Examples of platforms that can support this kind of work include Amazon SageMaker and Google Vertex AI.

• Exploratory data analysis, or EDA: this is when graphs, plots, and summary tables are used to analyze the data and understand relationships between variables, patterns, or anomalies, without starting with strict assumptions.

Sandbox environments can be created using notebook or machine learning platforms such as Google Vertex AI Workbench and Oracle Machine Learning Notebooks. These tools allow users to write code, create visualizations, test ideas, and document their work in the same environment.

Data consumption

Finally, the consumption layer is where reliable data is used by people, applications, or reporting tools.

This is where business users, analysts, and decision-makers interact with the data through dashboards, reports, APIs, or analytics platforms. Examples of tools used at this stage include Microsoft Power BI and Google Looker.

At this point, the goal is no longer just to store or prepare the data. The goal is to turn it into something useful: insights, reports, predictions, and decisions.

Governance, security, and monitoring

To make a data lake work properly, we need governance, security, monitoring, and stewardship.

Without them, a data lake can quickly become difficult to use. The data might be stored somewhere, but people may not know what it means, where it came from, who owns it, or whether they can trust it.

Data governance

Data governance establishes the rules, policies, and procedures used to manage data. It helps answer questions like:

1. Who can access this data?

2. What does this data mean?

3. Where did it come from?

4. Is it sensitive?

5. Can it be used for reporting, analytics, or machine learning?

Governance also helps ensure data quality. This means checking whether the data is complete, accurate, consistent, and reliable enough for use.

Tools like Collibra can help add this governance layer.

Collibra is a data governance and data intelligence platform. In simple terms, it helps organizations understand, organize, and control their data. It can be used as a data catalog, meaning it gives users a searchable inventory of data assets across the organization.

For example, instead of asking, “Where is the customer data?” or “Can I trust this table?”, users can search in Collibra to find the right dataset, see its definition, understand who owns it, check its lineage, and review any policies attached to it.

Security protocols

Security protocols protect the data lake from unauthorized access and help organizations comply with data protection regulations.

A few important security controls include:

• Authentication: this checks who the user is. For example, a company can require users to log in through identity services before accessing the data lake.

• Authorization: this checks what the user is allowed to do. Someone might be allowed to read a dataset but not edit it, delete it, or share it.

• Role-based access control, or RBAC: this gives permissions based on roles. For example, a data engineer, a data analyst, and a business user may all have different levels of access.

• Access control lists, or ACLs: these define more detailed permissions for specific files, folders, or objects.

• Encryption: this protects data by making it unreadable without the right key. Encryption can be used when data is stored, also called encryption at rest, and when data is moving between systems, also called encryption in transit.

• Audit logging: this records activity in the data lake. It helps track who accessed what, when they accessed it, and what actions they performed.

For example, AWS Lake Formation can help manage permissions and secure access to data stored in a data lake. Azure Data Lake Storage Gen2 can use Azure role-based access control and access control lists to manage who can access specific resources.

Security is not just about blocking people. It is about giving the right people the right access to the right data at the right time.

Monitoring and ELT processes

Monitoring helps ensure that the data lake keeps working correctly. It tracks data pipelines, ingestion jobs, storage usage, processing tasks, access patterns, and possible failures.

For example, monitoring can help detect if a data pipeline stopped running, if data arrived late, or if a transformation produced unexpected results.

ELT processes help move data from raw form into more usable formats. In an ELT approach, data is first extracted and loaded into the data lake, then transformed later depending on the use case.

This works well with data lakes because they are designed to store large amounts of raw or semi-raw data before all transformations are known.

Data stewardship

Data stewardship involves the active management and oversight of data. It is often performed by specialized teams or designated data owners.

A data steward helps make sure that data is properly defined, documented, protected, and maintained. They may review data quality issues, clarify business definitions, approve access requests, or make sure that governance rules are followed.

Conclusion

So, what should we remember?

A data lake is a flexible way to store large amounts of different types of data. It can hold structured, semi-structured, and unstructured data, often in its raw format, so organizations can use it later for analytics, machine learning, reporting, or research.

But a data lake is not magic storage. It needs good architecture, clear zones, ingestion processes, transformation logic, governance, security, monitoring, and stewardship.

When done well, a data lake can become a powerful foundation for data-driven work. It gives teams the freedom to explore data, build models, create dashboards, and generate insights.