Let’s Learn Data Engineering

What Does a Data Engineer Do?

Data engineers are responsible for designing, building, and maintaining systems that collect, process, and store data. They ensure that data is accessible, reliable, and prepared for analysis by data scientists and analysts. Key responsibilities include:

• Developing and managing data pipelines
• Implementing ETL (Extract, Transform, Load) processes
• Ensuring data quality and integrity
• Collaborating with cross-functional teams to understand data needs

Steps to Become a Data Engineer

Educational Background

While a bachelor’s degree in computer science, software engineering, or information technology is common, it’s not mandatory. Many professionals transition into data engineering through self-study, bootcamps, or specialized courses. The focus should be on acquiring relevant skills and practical experience.

Develop Essential Skills

Key skills for data engineers include:

• Programming Languages: Proficiency in languages like Python, Java, or Scala is crucial.
• Database Management: Strong understanding of SQL and familiarity with NoSQL databases.
• Data Warehousing Solutions: Experience with tools like Amazon Redshift, Google BigQuery, or Snowflake.
• ETL Tools: Knowledge of ETL frameworks such as Apache Airflow or Apache NiFi.
• Cloud Platforms: Familiarity with cloud services like AWS, Azure, or Google Cloud Platform.
• Big Data Technologies: Understanding of Hadoop, Spark, and Kafka.

Continuous learning is vital, as the data engineering landscape evolves rapidly.

Hands-On Experience

Practical experience is invaluable. Engage in projects that involve building data pipelines, setting up databases, or processing large datasets. Platforms like GitHub can showcase your work to potential employers.

Lets Learn SQL as an essential step!

Data Definition Language (DDL)

CREATE TABLE

Create a new table with specified columns:

CREATE TABLE employees (
emp_id INT,
name VARCHAR(100),
salary DECIMAL(10,2),
dept VARCHAR(50)
);

ALTER TABLE

Modify table structure, e.g. add/drop column:

ALTER TABLE employees
ADD hire_date DATE;

DROP TABLE

Delete a table and its data:

DROP TABLE employees;

Data Manipulation Language (DML)

INSERT

INSERT INTO employees (emp_id, name, salary, dept)
VALUES (1, ‘Alice’, 70000, ‘HR’);

*For multiple rows:

INSERT INTO employees (emp_id, name, salary)
VALUES (2,’Bob’,80000), (3,’Carol’,90000);

UPDATE

UPDATE employees
SET salary = salary * 1.1
WHERE dept = ‘Engineering’;

*To delete all rows:

DELETE FROM employees;

Data Query Language (DQL)

SELECT (basic retrieval)

SELECT * FROM employees;

SELECT name, salary FROM employees;

WHERE (filter rows)

SELECT * FROM employees
WHERE salary > 60000 AND dept = ‘HR’;

DISTINCT (unique values)

SELECT DISTINCT dept FROM employees;

ORDER BY (sort results)

SELECT name, salary
FROM employees
ORDER BY salary DESC;

LIMIT / TOP / OFFSET-FETCH (limit rows)

SELECT * FROM orders LIMIT 10; — MySQL/PostgreSQL

SELECT TOP 5 * FROM orders; — SQL Server

SELECT * FROM orders ORDER BY order_date

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; — Pagination

LIKE, IN, BETWEEN (pattern & range filtering)

SELECT name FROM employees WHERE name LIKE ‘A%’;

SELECT * FROM products WHERE category IN (‘Books’,’Toys’);

SELECT * FROM orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

Aggregation & Grouping

Aggregate functions: COUNT, SUM, AVG, MIN, MAX

SELECT COUNT(*) FROM employees WHERE dept = ‘Sales’;

SELECT SUM(salary), AVG(salary) FROM employees;

GROUP BY + HAVING

SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 50000;

Joins & Subqueries

JOIN (combine tables)

SELECT c.customer_name, o.order_date
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;

Subqueries (nested queries)

SELECT * FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);

Correlated subquery example:

SELECT emp_id, name FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees
WHERE dept = e.dept
);

Let’s Learn Excel

Function	Syntax
UNIQUE	UNIQUE(array, [by_col], [exactly_once])
XLOOKUP	XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])
Logical
AND	AND(logical1,logical2)
IF	IF(logical_test,value_if_true,value_if_false)
IFERROR	IFERROR(VALUE,value_if_error)
NOT	NOT(logical)
OR	OR(logical1,logical2)
XOR	XOR(logical1,logical2)
Lookup & Reference
ADDRESS	ADDRESS(row_num,column_num,abs_num,C1,sheet_text)
AREAS	AREAS(reference)
CHOOSE	CHOOSE(index_num,value1,value2)
COLUMN	COLUMN(reference)
COLUMNS	COLUMNS(array)
HLOOKUP	HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
HYPERLINK	HYPERLINK(link_location,friendly_name)
INDEX	INDEX(array,row_num,column_num)
INDIRECT	INDIRECT(ref_text,C1)
LOOKUP	LOOKUP(lookup_value,lookup_vector,result_vector)
MATCH	MATCH(lookup_value,lookup_array,match_type)
OFFSET	OFFSET(reference,rows,cols,height,width)
ROW	ROW(reference)
ROWS	ROWS(array)
TRANSPOSE	TRANSPOSE(array)
VLOOKUP	VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Date & Time
DATE	DATE(year,month,day)
DATEDIF	DATEDIF
DATEVALUE	DATEVALUE(date_text)
DAY	DAY(serial_number)
DAYS	DAYS(end_date,start_date)
DAYS360	DAYS360(start_date,end_date,method)
EDATE	EDATE(start_date,months)
EOMONTH	EOMONTH(start_date,months)
HOUR	HOUR(serial_number)
MINUTE	MINUTE(serial_number)
MONTH	MONTH(serial_number)
NETWORKDAYS	NETWORKDAYS(start_date,end_date,holidays)
NETWORKDAYS.INTL	NETWORKDAYS.INTL(start_date,end_date,weekend,holidays)
NOW	NOW()
SECOND	SECOND(serial_number)
TIME	TIME(hour,minute,second)
TIMEVALUE	TIMEVALUE(time_text)
TODAY	TODAY()
WEEKDAY	WEEKDAY(serial_number,return_type)
WEEKNUM	WEEKNUM(serial_number,return_type)
WORKDAY	WORKDAY(start_date,days,holidays)
WORKDAY.INTL	WORKDAY.INTL(start_date,days,weekend,holidays)
YEAR	YEAR(serial_number)
YEARFRAC	YEARFRAC(start_date,end_date,basis)
Engineering
CONVERT	CONVERT(number,from_unit,to_unit)
Financial
FV	FV(rate,nper,pmt,pv,type)
PV	PV(rate,nper,pmt,fv,type)
NPER	NPER(rate,pmt,pv,fv,type)
PMT	PMT(rate,nper,pv,fv,type)
RATE	RATE(nper,pmt,pv,fv,type,guess)
NPV	NPV(rate,value1,value2)
IRR	IRR(values,guess)
XIRR	XIRR(values,dates,guess)
PRICE	PRICE(settlement,maturity,rate,yld,redemption,FREQUENCY,basis)
YIELD	YIELD(settlement,maturity,rate,pr,redemption,FREQUENCY,basis)
INTRATE	INTRATE(settlement,maturity,investment,redemption,basis)
Information
CELL	CELL(info_type,reference)
ERROR.TYPE	ERROR.TYPE(error_val)
ISBLANK	ISBLANK(VALUE)
ISERR	ISERR(VALUE)
ISERROR	ISERROR(VALUE)
ISEVEN	ISEVEN(number)
ISFORMULA	ISFORMULA(reference)
ISLOGICAL	ISLOGICAL(VALUE)
ISNA	ISNA(VALUE)
ISNONTEXT	ISNONTEXT(VALUE)
ISNUMBER	ISNUMBER(VALUE)
ISODD	ISODD(number)
ISREF	ISREF(VALUE)
ISTEXT	ISTEXT(VALUE)
N	N(VALUE)
NA	NA()
TYPE	TYPE(VALUE)
Math
ABS	ABS(number)
AGGREGATE	AGGREGATE(function_num,options,array,k)
CEILING	CEILING(number,significance)
COS	COS(number)
DEGREES	DEGREES(angle)
DSUM	DSUM(database,field,criteria)
EVEN	EVEN(number)
EXP	EXP(number)
FACT	FACT(number)
FLOOR	FLOOR(number,significance)
GCD	GCD(number1,number2)
INT	INT(number)
LCM	LCM(number1,number2)
LN	LN(number)
LOG	LOG(number,base)
LOG10	LOG10(number)
MOD	MOD(number,divisor)
MROUND	MROUND(number,multiple)
ODD	ODD(number)
PI	PI()
POWER	POWER(number,power)
PRODUCT	PRODUCT(number1,number2)
QUOTIENT	QUOTIENT(numerator,denominator)
RADIANS	RADIANS(angle)
RAND	RAND()
RANDBETWEEN	RANDBETWEEN(bottom,top)
ROUND	ROUND(number,num_digits)
ROUNDDOWN	ROUNDDOWN(number,num_digits)
ROUNDUP	ROUNDUP(number,num_digits)
SIGN	SIGN(number)
SIN	SIN(number)
SQRT	SQRT(number)
SUBTOTAL	SUBTOTAL(function_num,REH1)
SUM	SUM(number1,number2)
SUMIF	SUMIF(range,criteria,sum_range)
SUMIFS	SUMIFS(sum_range,criteria_range,criteria)
SUMPRODUCT	SUMPRODUCT(array1,array2,array3)
TAN	TAN(number)
TRUNC	TRUNC(number,num_digits)
Stats
AVERAGE	AVERAGE(number1,number2)
AVERAGEA	AVERAGEA(value1,value2)
AVERAGEIF	AVERAGEIF(range,criteria,average_range)
AVERAGEIFS	AVERAGEIFS(average_range,criteria_range,criteria)
CORREL	CORREL(array1,array2)
COUNT	COUNT(value1,value2)
COUNTA	COUNTA(value1,value2)
COUNTBLANK	COUNTBLANK(range)
COUNTIF	COUNTIF(range,criteria)
COUNTIFS	COUNTIFS(criteria_range,criteria)
FORECAST	FORECAST(x,known_y’s,known_x’s)
FREQUENCY	FREQUENCY(data_array,bins_array)
GROWTH	GROWTH(known_ys,known_x,new_x,const)
INTERCEPT	INTERCEPT(known_ys,known_xs)
LARGE	LARGE(array,k)
LINEST	LINEST(known_ys,known_xs,const,stats)
MAX	MAX(number1,number2)
MEDIAN	MEDIAN(number1,number2)
MIN	MIN(number1,number2)
MODE	MODE(number1,number2)
PERCENTILE	PERCENTILE(array,k)
PERCENTILE.INC	PERCENTILE.INC(array,k)
PERCENTILE.EXC	PERCENTILE.EXC(array,k)
QUARTILE	QUARTILE(array,quart)
QUARTILE.INC	QUARTILE.INC(array,quart)
QUARTILE.EXC	QUARTILE.EXC(array,quart)
RANK	RANK(number,ref,order)
RANK.AVG	RANK.AVG(number,ref,order)
RANK.EQ	RANK.EQ(number,ref,order)
SLOPE	SLOPE(known_ys,known_xs)
SMALL	SMALL(array,k)
STDEV	STDEV(number1,number2)
STDEV.P	STDEV.P(number1,number2)
STDEV.S	STDEV.S(number1,number2)
STDEVP	STDEVP(number1,number2)
TREND	TREND(known_ys,known_xs,new_xs,const)
Text
CHAR	CHAR(number)
CLEAN	CLEAN(text)
CODE	CODE(text)
CONCATENATE	CONCATENATE(text1,text2)
DOLLAR	DOLLAR(number,decimals)
EXACT	EXACT(text1,text2)
FIND	FIND(find_text,within_text,start_num)
LEFT	LEFT(text,num_chars)
LEN	LEN(text)
LOWER	LOWER(text)
MID	MID(text,start_num,num_chars)
PROPER	PROPER(text)
REPLACE	REPLACE(old_text,start_num,num_chars,new_text)
REPT	REPT(text,number_times)
RIGHT	RIGHT(text,num_chars)
SEARCH	SEARCH(find_text,within_text,start_num)
SUBSTITUTE	SUBSTITUTE(text,old_text,new_text,instance_num)
TEXT	TEXT(VALUE,format_text)
TRIM	TRIM(text)
UPPER	UPPER(text)
VALUE	VALUE(text)