Skip to main content

2.a flattening the JSON File.


Think of JSON processing as this journey:

Raw JSON → Python objects → Flatten → Clean → DataFrame → Spark → Production pipeline

GoalIn Local PythonIn PySpark (Databricks)In Cloud SQL (Snowflake/BigQuery)
Read a filejson.load(f)spark.read.json()COPY INTO / Storage Integration
Go inside an objectdata["key"]["subkey"]df.select("key.subkey")SELECT column:key.subkey
Turn a list into rowsfor item in my_list:explode(col("my_list"))LATERAL FLATTEN() / UNNEST()

Phase 1 — JSON Fundamentals

1. JSON Data Types

You need to immediately recognize how JSON maps to Python.

JSONPythonExample
Objectdict{"name":"John"}
Arraylist[1,2,3]
Stringstr"London"
Numberint/float100
Booleanbooltrue
NullNonenull

Example:

{
 "employee":{
    "id":100,
    "name":"John"
 },
 "skills":[
    "Python",
    "Spark"
 ]
}

Python sees this as:

{
 "employee":{
    "id":100,
    "name":"John"
 },

 "skills":[
    "Python",
    "Spark"
 ]
}

Your brain should immediately think:

  • {} → dictionary

  • [] → list

Phase 2 — Python JSON Library

The first tool every Data Engineer uses.

import json

Interview:
FunctionInputOutputPurposeMemory Trick
json.load()FilePython object (dict, list, etc.)Read JSON from a file and convert it into a Python objectLoad from a file
json.loads()JSON stringPython object (dict, list, etc.)Parse a JSON string and convert it into a Python objectLoad from a string (s = string)
json.dump()Python objectWrites JSON to a fileSave a Python object as JSON in a fileDump to a file
json.dumps()Python objectJSON stringConvert a Python object into a JSON string (for APIs, Kafka, etc.)Dump to a string (s = string)

json.load()

Read JSON file.

Example:

employee.json

{
"id":1,
"name":"John"
}

Python:

with open("employee.json") as f:

    data=json.load(f)


print(data)

Output:

{
'id':1,
'name':'John'
}

Use when:

file → python dictionary

json.loads()

String → dictionary

Example:

json_string='{"name":"John"}'


data=json.loads(json_string)


print(data["name"])

Output:

John

Use:

JSON text → python object

json.dump()

Dictionary → JSON file

employee={
"name":"John"
}


with open("output.json","w") as f:

    json.dump(employee,f)

Creates:

{
"name":"John"
}

json.dumps()

Dictionary → JSON string

json.dumps(employee)

Output:

'{"name":"John"}'

Python Data Structures Cheat Sheet

Data TypeOrdered?Mutable?Allows Duplicates?StoresExampleMain Use
List✅ Yes✅ Yes✅ YesValues[1, 2, 3]General data, JSON arrays, ETL records
Tuple✅ Yes❌ No✅ YesValues(1, 2, 3)Fixed data, constants, safe records
Dictionary❌ No (key-based order in modern Python)✅ YesKeys ❌ / Values ✅Key → Value pairs{"name": "John"}JSON objects, APIs, lookup tables
Set❌ No✅ Yes❌ No (unique only)Unique values{1, 2, 3}Remove duplicates, fast search

Phase 3 — Python Dictionary Mastery

MethodWhat it doesExample
keys()Returns all keysperson.keys() → dict_keys(['name', 'age'])
values()Returns all valuesperson.values() → dict_values(['John', 30])
items()Returns key-value pairsperson.items() → ('name', 'John')
get()Safely gets the value for a keyperson.get("age")
update()Adds or updates one or more key-value pairsperson.update({"city": "London"})
pop(key)Removes a specific key and returns its valueperson.pop("age")
popitem()Removes the last inserted key-value pairperson.popitem()
delDeletes a specific key (or the whole dictionary if used on the dictionary variable)del person["age"]
clear()Removes all key-value pairs, leaving an empty dictionaryperson.clear()
copy()Creates a shallow copy of the dictionaryperson2 = person.copy()
setdefault()Returns the value for a key; if the key doesn't exist, inserts it with a default valueperson.setdefault("city", "London")
fromkeys()Creates a new dictionary from a list of keysdict.fromkeys(["id", "name"], None)

For Data Engineering interviews, these are the most frequently used methods:

  1. ⭐ get() – Safe access to values (especially JSON/API data)
  2. ⭐ items() – Iterate over keys and values
  3. ⭐ update() – Modify or merge dictionaries
  4. ⭐ pop() – Remove a specific key
  5. ⭐ setdefault() – Grouping and handling missing keys
  6. ⭐ copy() – Avoid modifying the original dictionary

Because JSON objects become dictionaries.

Example:

employee={
"name":"John",
"salary":5000
}

Access:

employee["name"]

Output:

John

Problem:

employee["age"]

Error:

KeyError

Better:

employee.get("age")

Output:

None

Important functions:

keys()

employee.keys()

Output:

name salary

values()

employee.values()

items()

Used in loops.

for k,v in employee.items():

    print(k,v)

Output:

name John
salary 5000

Phase 4 — Lists (JSON Arrays)


MethodPurposeExample
append()Add one item at endnums.append(5) → [1,2,3,5]
extend()Add multiple itemsnums.extend([4,5]) → [1,2,3,4,5]
insert()Add at specific positionnums.insert(1, 10) → [1,10,2,3]
remove()Remove by valuenums.remove(2) → removes first 2
pop()Remove by indexnums.pop(1) → removes index 1
clear()Remove all itemsnums.clear() → []
index()Find position of valuenums.index(3) → returns 2
count()Count occurrencesnums.count(2) → 2
sort()Sort list ascendingnums.sort() → [1,2,3]
reverse()Reverse list ordernums.reverse() → [3,2,1]

Great — this is where real Data Engineering interview questions start.

🚀 Phase 5 — List of Dictionaries (REAL ETL Patterns)

This is the MOST IMPORTANT structure in Python for Data Engineers.

1. What is “List of Dictionaries”?

It is just a collection of records (like rows in a table).

Example:

employees = [
    {"id": 1, "name": "John", "dept": "IT"},
    {"id": 2, "name": "Alice", "dept": "HR"},
    {"id": 3, "name": "Bob", "dept": "IT"}
]

Think like a table:

idnamedept
1JohnIT
2AliceHR
3BobIT

2. Access Data

Get first record

employees[0]

{'id': 1, 'name': 'John', 'dept': 'IT'}

Get specific field

employees[0]["name"]

John

3. Loop Through Records

for emp in employees:
    print(emp["name"])

Output:

John
Alice
Bob

4. FILTERING (Very Important ⭐)

Get only IT employees

it_employees = []

for emp in employees:
    if emp["dept"] == "IT":
        it_employees.append(emp)

print(it_employees)

Output:

[
    {'id': 1, 'name': 'John', 'dept': 'IT'},
    {'id': 3, 'name': 'Bob', 'dept': 'IT'}
]

Same using list comprehension (INTERVIEW FAVORITE ⭐)

it_employees = [emp for emp in employees if emp["dept"] == "IT"]

5. TRANSFORMATION (ETL concept ⭐)

Add new field (salary)

for emp in employees:
    emp["salary"] = 50000

Result:

[
    {'id': 1, 'name': 'John', 'dept': 'IT', 'salary': 50000},
    ...
]

Create new structure (mapping)

names = [emp["name"] for emp in employees]

Output:

['John', 'Alice', 'Bob']

6. GROUP BY (VERY IMPORTANT ⭐⭐⭐)

Group employees by department

grouped = {}

for emp in employees:
    dept = emp["dept"]

    if dept not in grouped:
        grouped[dept] = []

    grouped[dept].append(emp["name"])

print(grouped)

Output:

{
    "IT": ["John", "Bob"],
    "HR": ["Alice"]
}

Cleaner version (using setdefault)

grouped = {}

for emp in employees:
    grouped.setdefault(emp["dept"], []).append(emp["name"])

7. COUNTING (Very common in interviews)

Count employees per department

count = {}

for emp in employees:
    dept = emp["dept"]
    count[dept] = count.get(dept, 0) + 1

print(count)

Output:

{'IT': 2, 'HR': 1}

8. SORTING DATA

Sort by name

sorted_employees = sorted(employees, key=lambda x: x["name"])

Sort by id descending

sorted(employees, key=lambda x: x["id"], reverse=True)

9. FILTER + TRANSFORM (REAL PIPELINE)

Get IT employees names in uppercase

result = [emp["name"].upper() for emp in employees if emp["dept"] == "IT"]

Output:

['JOHN', 'BOB']

10. JSON ↔ List of Dictionaries (VERY IMPORTANT ⭐)

JSON from API:

[
    {"id":1,"name":"John"},
    {"id":2,"name":"Alice"}
]

Python:

import json

data = json.loads(json_string)

Now:

list of dictionaries

🚀 MOST IMPORTANT INTERVIEW CONCEPTS

You MUST know these:

1. Filtering

[emp for emp in employees if condition]

2. Grouping

setdefault()

3. Counting

dict.get(key, 0) + 1

4. Transformation

[emp["name"] for emp in employees]

5. Sorting

sorted(data, key=lambda x: x["field"])

🎯 REAL DATA ENGINEERING THINKING

Every ETL job looks like:

JSON/API → List of Dicts → Transform → Filter → Group → Output

💡 FINAL INTERVIEW SUMMARY

If you understand:

  • Dictionary basics

  • List basics

  • List of dictionaries

  • JSON conversion

  • Grouping + counting + filtering


Phase 6 — Loop Nested JSON

Real example:

orders={

"orders":[

{
"id":1,
"price":100
},

{
"id":2,
"price":200
}

]

}

Loop:

for order in orders["orders"]:

    print(order["price"])

Output:

100
200

Phase 7 — isinstance() (Critical)

This is the heart of dynamic flattening.

Example:

value={
"name":"John"
}


isinstance(value,dict)

Output:

True

Why?

Because flattening logic is:

If dictionary:
    go deeper

If list:
    expand

Otherwise:
    save value

Phase 8 — Recursive Flattening

Here’s a complete end-to-end recursion summary cheat sheet you can revise before a Data Engineer interview.

🚀 Recursion — Interview Revision Cheat Sheet (DE Focus)

🧠 1. What is Recursion?

Recursion is when a function calls itself to solve a problem by breaking it into smaller sub-problems.

🔹 2. Structure of Recursion (VERY IMPORTANT)

Every recursive function has:

PartMeaning
✅ Base CaseStops recursion
🔁 Recursive CaseFunction calls itself

🔥 Template

def func(x):
    if base_case:
        return result   # STOP

    return func(smaller_x)  # RECURSION

🧠 3. How Recursion Works (Stack Concept)

Call Stack:
func(5)
func(4)
func(3)
func(2)
func(1)
STOP

Then returns back:
1 → 2 → 3 → 4 → 5

📌 4. Must-Know Examples

1. Factorial (MOST COMMON)

def factorial(n):
    if n == 1:
        return 1

    return n * factorial(n - 1)

2. Sum of Numbers

def sum_n(n):
    if n == 0:
        return 0

    return n + sum_n(n - 1)

3. Fibonacci (classic interview)

def fib(n):
    if n <= 1:
        return n

    return fib(n-1) + fib(n-2)

📦 5. Data Engineering Use Cases (VERY IMPORTANT ⭐)

Recursion is used when data is:

Data TypeExample
JSONNested API responses
ListNested arrays
DictNested objects
FilesFolder structure
TreeOrg charts, categories

🌐 6. Flatten Nested List (TOP DE QUESTION)

def flatten(data):
    result = []

    for item in data:
        if isinstance(item, list):
            result.extend(flatten(item))  # recursion
        else:
            result.append(item)

    return result

🗂 7. Flatten Nested Dictionary (VERY IMPORTANT)

def flatten_dict(d, parent_key="", result=None):
    if result is None:
        result = {}

    for k, v in d.items():
        new_key = parent_key + "." + k if parent_key else k

        if isinstance(v, dict):
            flatten_dict(v, new_key, result)
        else:
            result[new_key] = v

    return result

🌳 8. Tree / Hierarchy Traversal

Used for:

  • org charts

  • categories

  • file systems

def traverse(node):
    print(node["name"])

    for child in node

Phase 9 — Handling Arrays

Example:

{
"name":"John",

"skills":[
"Python",
"Spark"
]

}

Need output:

nameskill
JohnPython
JohnSpark

This requires:

  • loop

  • enumerate

  • explode concept

Phase 10 — Pandas JSON Processing

Here is your Phase 10 — Pandas JSON Processing Cheat Sheet (Notes Ready + Outputs + Table Format)

🚀 Pandas JSON Processing (Data Engineering Cheat Sheet)

📊 1. Create DataFrame from JSON

Code:

import pandas as pd

data = [
    {"id": 1, "name": "John", "dept": "IT"},
    {"id": 2, "name": "Alice", "dept": "HR"},
    {"id": 3, "name": "Bob", "dept": "IT"}
]

df = pd.DataFrame(data)
print(df)

Output:

   id   name dept
0   1   John   IT
1   2  Alice   HR
2   3    Bob   IT

📊 2. Read JSON File

Code:

df = pd.read_json("data.json")
print(df)

Output (same structure):

   id   name dept
0   1   John   IT
1   2  Alice   HR
2   3    Bob   IT

📊 3. JSON String → DataFrame

Code:

import json
import pandas as pd

json_str = '[{"id":1,"name":"John"},{"id":2,"name":"Alice"}]'

data = json.loads(json_str)
df = pd.DataFrame(data)

print(df)

Output:

   id   name
0   1   John
1   2  Alice

📊 4. Column Selection

Code:

print(df["name"])

Output:

0     John
1    Alice
Name: name, dtype: object

📊 5. Filter Rows

Code:

print(df[df["id"] > 1])

Output:

   id   name
1   2  Alice

📊 6. Add New Column

Code:

df["salary"] = 50000
print(df)

Output:

   id   name  salary
0   1   John   50000
1   2  Alice   50000

📊 7. Modify Column

Code:

df["name"] = df["name"].str.upper()
print(df)

Output:

   id   name
0   1   JOHN
1   2  ALICE

📊 8. Group By (VERY IMPORTANT ⭐)

Code:

df = pd.DataFrame([
    {"dept": "IT", "salary": 50000},
    {"dept": "HR", "salary": 40000},
    {"dept": "IT", "salary": 60000}
])

print(df.groupby("dept")["salary"].sum())

Output:

dept
HR    40000
IT   110000
Name: salary, dtype: int64

📊 9. Sort Values

Code:

print(df.sort_values("salary"))

Output:

   dept  salary
1    HR   40000
0    IT   50000
2    IT   60000

📊 10. Drop Column

Code:

df = df.drop("salary", axis=1)
print(df)

Output:

  dept
0   IT
1   HR
2   IT

📊 11. Fill Missing Values

Code:

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name": ["John", None, "Bob"]
})

df["name"] = df["name"].fillna("Unknown")
print(df)

Output:

      name
0     John
1  Unknown
2      Bob
 Deprecated Pandas Method Syntax
In Phase 11 (Missing Data Handling), the snippet uses method="ffill" and method="bfill" inside df.fillna().
Note: Passing the method argument to .fillna() is deprecated in modern Pandas versions.
Python
# ❌ Deprecated:
df.fillna(method="ffill")

# ✅ Modern Practice:
df.ffill()
df.bfill()

📊 12. Drop Missing Values

Code:

df = df.dropna()
print(df)

📊 13. Count Values

Code:

print(df["dept"].value_counts())

Output:

IT    2
HR    1
Name: dept, dtype: int64

📊 PANDAS COMMAND CHEAT TABLE (IMPORTANT FOR NOTES ⭐)

CommandPurposeExample
pd.DataFrame()Create DataFrame from JSONpd.DataFrame(data)
pd.read_json()Read JSON filepd.read_json("file.json")
df["col"]Select columndf["name"]
df.loc[]Select row by indexdf.loc[0]
df[df["col"]]Filter rowsdf[df["id"]>1]
df["new_col"] =Add columndf["salary"]=50000
df.drop()Remove columndf.drop("col", axis=1)
df.groupby()Group datadf.groupby("dept").sum()
df.sort_values()Sort datadf.sort_values("salary")
df.fillna()Replace missing valuesdf.fillna(0)
df.dropna()Remove missing valuesdf.dropna()
df.value_counts()Count occurrencesdf["dept"].value_counts()
df.str.upper()String transformdf["name"].str.upper()

🚀 FINAL DATA ENGINEERING SUMMARY

Pandas is used to convert JSON → DataFrame and perform ETL operations like filtering, grouping, transformation, and aggregation.

💡 EASY MEMORY TRICK

StepAction
ExtractJSON → DataFrame
Cleanfillna / dropna
Transformadd / modify columns
Filterconditions
Aggregategroupby

Phase 11 — Missing Data Handling

🚀 Phase 11 — Missing Data Handling (Data Engineering Cheat Sheet)

Missing data is one of the most common real-world problems in Data Engineering:

  • APIs return nulls

  • JSON fields missing

  • Database NULL values

  • Sensor/log failures

👉 Goal: Detect, handle, and clean missing data

🧠 1. What is Missing Data?

In Pandas, missing data is represented as:

  • NaN (Not a Number)

  • None

  • NaT (for datetime)

Example Dataset

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name": ["John", None, "Bob", "Alice"],
    "age": [25, 30, None, 28],
    "dept": ["IT", "HR", None, "IT"]
})

print(df)

Output:

    name   age dept
0   John  25.0   IT
1   None  30.0   HR
2    Bob   NaN None
3  Alice  28.0   IT

🔍 2. Detect Missing Data

Check NULL values

print(df.isnull())

Output:

    name    age   dept
0  False  False  False
1   True  False  False
2  False   True   True
3  False  False  False

Count missing values

print(df.isnull().sum())

Output:

name    1
age     1
dept    1
dtype: int64

❌ 3. Drop Missing Data

Drop rows with ANY missing value

df.dropna()

Output:

    name   age dept
0   John  25.0   IT
3  Alice  28.0   IT

Drop columns with missing data

df.dropna(axis=1)

🧹 4. Fill Missing Data (VERY IMPORTANT ⭐)

Fill with constant value

df.fillna("Unknown")

Output:

      name     age     dept
0     John    25.0       IT
1  Unknown    30.0       HR
2      Bob  Unknown  Unknown
3    Alice    28.0       IT

Fill numeric values with 0

df["age"].fillna(0)

Fill with mean (REAL DE USE CASE ⭐)

df["age"].fillna(df["age"].mean())

Output:

0    25.0
1    30.0
2    27.6667
3    28.0

🔁 5. Forward Fill (VERY IMPORTANT)

👉 Fill missing value using previous row

df.fillna(method="ffill")

Output:

    name   age dept
0   John  25.0   IT
1   John  30.0   HR
2    Bob  30.0   HR
3  Alice  28.0   IT

🔁 6. Backward Fill

df.fillna(method="bfill")

🔥 7. Replace Missing Values

df.replace(np.nan, "Missing")

📊 8. Interpolation (Advanced ⭐)

👉 Fills missing values logically (used in time-series)

df["age"].interpolate()

🧠 9. Real Data Engineering Patterns

Pattern 1: Replace NULL with default

df["dept"] = df["dept"].fillna("UNKNOWN")

Pattern 2: Drop bad records

df = df.dropna()

Pattern 3: Fill numeric with mean

df["age"] = df["age"].fillna(df["age"].mean())

Pattern 4: Forward fill streaming data

df.fillna(method="ffill")

📦 10. When to use what?

MethodWhen to use
dropna()Data is bad or incomplete
fillna(0)Default numeric value
fillna(mean)Statistical correction
ffillTime series / logs
bfillReverse filling
interpolate()Sensor / continuous data

🚀 11. Interview Summary (VERY IMPORTANT)

Missing data handling is a key step in Data Engineering.
We use methods like dropna, fillna, forward fill, backward fill, and interpolation to clean datasets before transformation and loading.

💡 EASY MEMORY TRICK

ProblemSolution
Missing rowsdropna
Missing valuesfillna
Time seriesffill
Reverse fillbfill
Smart guessinterpolate

🔥 REAL WORLD DE FLOW

API / DB / Kafka
        ↓
   Missing Data
        ↓
Clean (fill/drop)
        ↓
Transform
        ↓
Load (Warehouse)

Phase 12 — Exception Handling

Production code must not crash.

Example:

try:

    data=json.load(file)


except Exception as e:

    print(e)

Phase 13 — Large JSON Files

Problem:

10GB JSON:

json.load()

loads everything into memory.

Better:

  • streaming

  • chunks

  • Spark

Phase 14 — PySpark JSON (Databricks)

Senior Data Engineer level.

Read:

df=spark.read.json(
"path/file.json"
)

Nested column:

df.select(
"employee.name"
)

Flatten struct:

df.select(
"employee.*"
)

Arrays:

Example:

skills=[
Python,
Spark
]

Explode:

from pyspark.sql.functions import explode


df.withColumn(
"skill",
explode("skills")
)

Result:

Python
Spark

Phase 15 — Spark Functions to Master

For Azure Databricks interviews:

Must know:

spark.read.json()

from_json()

to_json()

explode()

explode_outer()

posexplode()

select()

select("struct.*")

withColumn()

col()

getField()

schema_of_json()


3 Small Additions to Make it Perfect
To truly secure a Senior Data Engineer title, add these edge cases to your checklist:
1. PySpark Explicit Schemas (Crucial for Production)
In Phase 14, you mentioned spark.read.json(). While this works, letting Spark infer the schema on a 10GB file forces it to scan the dataset twice, which kills performance.
  • Interview Tip: Always mention defining a StructType schema explicitly before reading, or using from_json(col, schema).
2. JSON Lines (ndjson) vs. Standard JSON
Standard JSON arrays require loading the entire file into memory as one object. Production pipelines usually use JSON Lines (.jsonl or newline-delimited JSON), where every single line is a valid independent JSON object.
  • Interview Tip: Spark processes JSON Lines inherently in parallel, whereas a massive nested standard JSON array is notoriously difficult to split across nodes.
3. Fleshing out the Cloud SQL Phase
Your introduction matrix mentions Snowflake (LATERAL FLATTEN) and BigQuery (UNNEST), but the phases don't dive into them.
  • Interview Tip: Be prepared to write a quick query showing how to parse a variant/JSON column using the colon syntax (column:nested_field) and flattening an array via a SQL join.

Final Skill Checklist for Senior JSON Processing

You should be able to do:

✅ Read JSON files
✅ Convert JSON ↔ Python objects
✅ Navigate nested dictionaries
✅ Handle arrays
✅ Flatten dynamic JSON
✅ Write recursive flatten functions
✅ Use pandas.json_normalize()
✅ Handle missing fields
✅ Process large JSON files
✅ Flatten JSON in PySpark
✅ Handle schema evolution

The Senior-Level Execution Code snippets

1. PySpark Explicit Schema Enforcements (Instead of InferSchema)

When an interviewer asks, "How do you optimize reading a massive JSON file in PySpark?", do not just say "use a schema." Show them how:

Python
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, ArrayType

# 1. Define schema explicitly to avoid a double-pass file scan
schema = StructType([
    StructField("id", IntegerType(), True),
    StructField("name", StringType(), True),
    StructField("skills", ArrayType(StringType()), True)
])

# 2. Apply it directly during the read phase
df = spark.read.schema(schema).json("hdfs:///data/large_employees.json")

2. JSON Lines (.jsonl) Production Standard

If they ask, "What is the difference between parsing a standard JSON array vs. Newline-Delimited JSON (NDJSON) in production?", highlight that standard JSON forces a single executor to parse the file, while JSON Lines allows distributed, parallel processing.

Note: When using spark.read.json(), PySpark assumes JSON Lines format by default (one JSON object per line). If you are reading a single, massive multi-line JSON array file, you must explicitly pass the multiLine option:

df = spark.read.option("multiLine", True).json("file.json")

3. The Cloud SQL Snippet (Snowflake & BigQuery)

If they pivot to the data warehouse phase and ask how to flatten semi-structured data directly in SQL, be ready to sketch these architectures:

Snowflake (LATERAL FLATTEN)

Assuming you have a table named employee_stage with a VARIANT column named json_data:

SQL
SELECT 
    json_data:id::INT as employee_id,
    json_data:name::STRING as employee_name,
    f.value::STRING as skill
FROM employee_stage,
LATERAL FLATTEN(input => json_data:skills) f;

BigQuery (UNNEST)

Assuming you have a table named employee_stage with a JSON column named json_data:

SQL
SELECT 
    LAX_INT64(json_data.id) as employee_id,
    LAX_STRING(json_data.name) as employee_name,
    skill
FROM employee_stage,
UNNEST(JSON_EXTRACT_STRING_ARRAY(json_data, '$.skills')) as skill;

🚀 Final Interview Cheat Sheet: The 3-Second Deflection Rules

When hit with an open-ended JSON question, default to these architectural architectural decisions instantly:

  • Memory constraint issue? Drop json.load() $\rightarrow$ Switch to ijson (streaming) or PySpark.

  • Performance bottleneck? Drop inferSchema $\rightarrow$ Provide explicit StructType.

  • Explode vs. Explode_outer? Use explode() if you want to drop rows with empty/null arrays. Use explode_outer() if you must retain the parent row even if its array payload is completely empty.


1. Schema Evolution & Variant Handling

Explicit schemas are great until an upstream API team changes a data type or adds a field. A senior engineer needs to mention how they handle this.

  • PySpark: Mentioning .option("mergeSchema", "true") or reading raw JSON text into a single column first, logging malformed records to a Dead Letter Queue (DLQ), and processing valid ones.

2. The explode Memory Trap (Out of Memory / OOM)

The guide heavily pushes explode(). In production, exploding deeply nested huge arrays causes data explosion (a single row with an array of 1,000 items becomes 1,000 separate rows). If you join that exploded DataFrame, you will trigger a massive data shuffle and crash your cluster.

  • Senior fix: Use higher-order array functions in Spark SQL (like transform(), filter(), or aggregate()) to manipulate arrays in place without exploding them into individual rows.

3. The json_normalize() Hidden Trick

Phase 10 mentions Pandas, but misses the holy grail of Python semi-structured processing: pandas.json_normalize(). It flat-out replaces complex recursive loops for standard nested dictionaries.

Python
import pandas as pd
# Automatically flattens nested dicts and creates 'employee.name' columns instantly
df = pd.json_normalize(orders_data, record_path=['orders'], meta=['metadata_field'])


Comments

Post a Comment

Popular posts from this blog

Entity Relationship (ER) Diagram Model with DBMS Example

Reference :   Entity Relationship (ER) Diagram Model with DBMS Example What is ER Diagram? ER Diagram  stands for Entity Relationship Diagram, also known as ERD is a diagram that displays the relationship of entity sets stored in a database. In other words, ER diagrams help to explain the logical structure of databases. ER diagrams are created based on three basic concepts: entities, attributes and relationships. ER Diagrams contain different symbols that use rectangles to represent entities, ovals to define attributes and diamond shapes to represent relationships. At first look, an ER diagram looks very similar to the flowchart. However, ER Diagram includes many specialized symbols, and its meanings make this model unique. The purpose of ER Diagram is to represent the entity framework infrastructure. Entity Relationship Diagram Example Table of Content: What is ER Diagram? What is ER Model? History of ER models Why use ER Diagrams? Facts about ER Diagram Model ER Diagram...
Post a Comment
Read more

SQL Joins and advanced joins and Subqueries

  Refernce :  Expert Guide to Advanced SQL Joins: What You Need to Know It's helpful to visualize how these different SQL joins work. Here's a breakdown in a table-like format, along with explanations: SQL Join Types Overview Join Type Description Key Characteristics Use Cases INNER JOIN Returns rows where there is a match in both tables. - Shows only matching records. - Excludes unmatched rows from both tables. - Retrieving related data that exists in both tables. - Finding records with corresponding entries in another table. LEFT OUTER JOIN (LEFT JOIN) Returns all rows from the left table, and the matched rows from the right table. - Includes all records from the left table. - Fills in NULL values for columns from the right table where there's no match. - Retrieving all records from one table and their related data from another, even if some records don't have matches. - Finding records in one table that don't have corresponding entries in another. RIGHT OUTER JO...
Post a Comment
Read more

GIT BASH

  Bash Shell: Git Bash uses the Bash (Bourne Again SHell) command-line interpreter. This means you can use many of the same commands you'd find in a Linux or macOS terminal. Git Integration: Git Bash is tightly integrated with Git, making it easy to execute Git commands Essential Commands: Navigation: pwd : Prints the current working directory. ls : Lists files and directories in the current directory. cd <directory> : Changes the current directory. cd .. : Moves to the parent directory. File Management: mkdir <directory> : Creates a new directory. touch <file> : Creates a new file. rm <file> : Removes a file. rmdir <directory> : Removes an empty directory. Git Commands: git init : Initializes a new Git repository. git clone <repository URL> : Clones an existing Git repository. git status : Displays the status of your working directory. git add <file> : Adds a file to the staging area. git commit -m "commit message" : Commits chan...
Post a Comment
Read more