1. Outer query = the main query you run.
2. Inner query = the SELECT written inside it.
3. SQL evaluates the inner query first, then uses its result to finish the outer query.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name    VARCHAR(50) NULL
) ENGINE=InnoDB;

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product    VARCHAR(50) NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  user_id    INT NOT NULL,
  product_id INT NULL,
  amount     DECIMAL(10,2) NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(user_id),
  CONSTRAINT fk_orders_product
    FOREIGN KEY (product_id)
    REFERENCES products(product_id)
) ENGINE=InnoDB;

INSERT INTO users (user_id, name) VALUES
(1, 'Ayaan'),
(2, 'Sneha'),
(3, 'Rohit'),
(4, 'Kavya'),
(5, 'Zoya'),
(6, 'Arjun'),
(7, ''),
(8, NULL);

INSERT INTO products (product_id, product) VALUES
(10, 'Tablet'),
(11, 'Headphones'),
(12, 'Keyboard');

INSERT INTO orders (order_id, user_id, product_id, amount) VALUES
(101, 1, 10, 1200.00),
(102, 1, 11,  300.00),
(103, 1, 12,  150.00),
(104, 3, 10, 1100.00),
(105, 3, 11,  350.00),
(106, 4, 11,  400.00),
(107, 1, 11,  250.00),
(108, 1, 12,  NULL),
(109, 3, 11,  349.00),
(110, 3, 11,   50.00),
(111, 4, NULL,  50.00),
(112, 4, 11,   10.00);

So, here is a basic example on Subquery how to write it and read it

Question: Fetch each order along with the user’s name

SELECT
  o.order_id,
    (SELECT u.name 
     FROM users u 
     WHERE u.user_id = o.user_id) as name
From orders o

1) Outer query picks orders

The outer query reads each row from the orders table (o) and outputs:

• o.order_id

So it produces one output row per order.

2) Subquery fetches user name for that order

For every order row, this subquery runs:

SELECT u.name
FROM users u
WHERE u.user_id = o.user_id

• It uses the current order’s o.user_id.
• It finds the matching row in users.
• It returns the name of that user.

Question 2: Find users who bought all products

SELECT
  u.user_id,
  u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM products p
  WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
      AND o.product_id = p.product_id
  )
);

For each user, the query checks every product in products. If the user is missing even one product (no order for it), the user is removed. Only users who have bought every product remain.

Now you may be thinking is it the only way to write down query?
But No, this query can be written down in many forms

1. Standard Inner Join (Recommended)

This is the most common, readable, and generally highest-performing way to write this query. It fetches orders that have a matching user.

SELECT 
    o.order_id, 
    u.name 
FROM orders o
JOIN users u ON o.user_id = u.user_id;

2. Left Join (Preserves all orders)

If your original query could return NULL for the name when an order has no matching user (like a LEFT JOIN), use this to ensure all orders are still listed even if the user record is missing.

SELECT 
    o.order_id, 
    u.name 
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;

3. Using the USING Clause

If both tables share the exact same column name (user_id), you can shorten the ON condition using USING.

SELECT 
    o.order_id, 
    u.name 
FROM orders o
JOIN users u USING (user_id);

4. Implicit Join (Legacy Syntax)

An older, comma-separated syntax (though explicit JOIN is modern industry standard).

SELECT 
    o.order_id, 
    u.name 
FROM orders o, users u
WHERE o.user_id = u.user_id;

The NOT IN Trap

So, I have a question what will this query return based on below table

SELECT name
FROM employees
WHERE salary NOT IN (
 SELECT salary
 FROM employees
 WHERE dept_id = 10
);

If you says: Neha, Pooja and Karan then you are wrong. So, let’s understand how NOT IN actually work

The output of this query depends entirely on whether any employee in dept_id = 10 has a NULL salary.

Because of how SQL handles NULL values with NOT IN, this query can behave in two completely different ways:

Scenario A: If there are NO NULL salaries in Department 10

If all employees in dept_id = 10 have valid, non-null salary values (e.g., 50000, 60000), the query will successfully return:

• The names of all employees whose salary does NOT match any salary in department 10.

Scenario B: If even ONE salary in Department 10 is NULL (The NOT IN Trap)

If the subquery returns even a single NULL value, the query will return 0 rows (an empty result set), no matter what.

Why does this happen?

When SQL evaluates NOT IN, it translates to a series of AND conditions with != (not equal):

-- Conceptual evaluation for an employee with a salary of 70000, 
-- if department 10 has salaries (50000, NULL):
WHERE salary != 50000 AND salary != NULL

• 70000 != 50000 evaluates to TRUE.
• 70000 != NULL evaluates to UNKNOWN (because comparisons with NULL in SQL always result in unknown/null).
• TRUE AND UNKNOWN evaluates to UNKNOWN.

Since a WHERE clause only keeps rows where the condition evaluates to strictly TRUE, an UNKNOWN result causes the row to be discarded. One single NULL breaks the entire query for every row.

How to Write It Safely

To avoid the NULL trap entirely, it is much safer to use NOT EXISTS (which you looked at previously) or a LEFT JOIN:

SELECT e1.name
FROM employees e1
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e2 
    WHERE e2.dept_id = 10 
      AND e2.salary = e1.salary
);

NOT EXISTS handles NULL values safely because it checks for the existence of rows rather than performing a list comparison.

Performance and Safety

In many database systems, EXISTS is faster than IN. Why? Because IN requires the database to build a complete list of all possible values from the inner query first. EXISTS just needs to find one single match and can stop looking immediately. This “short-circuit” behavior makes it very efficient.

Even more importantly, NOT EXISTS is much safer than NOT IN. If a subquery used with NOT IN returns even one NULL value, the entire query can fail or return zero rows. NOT EXISTS does not have this problem and handles NULLs logically.

The Hidden Cost

Correlated subqueries are incredibly flexible, but they come at a high performance cost. Because they run in a loop (once for every row of the outer table), they can be extremely slow on large datasets.

• If your outer table has 1,000 rows, the inner query runs 1,000 times.
• If your outer table has 1,000,000 rows, the inner query runs 1,000,000 times.

In many modern databases, the optimizer is smart enough to rewrite these as a faster JOIN. However, in older systems or very complex queries, they can become a major bottleneck. Always test your query performance.

Common Mistakes to Avoid

• Selecting Columns in EXISTS: Beginners often write SELECT * or SELECT column inside an EXISTS clause. This is a waste of resources. The database only cares if a row is returned, not what is in it. Always use SELECT 1 for better performance.
• The NOT IN Trap: As mentioned, if the subquery for NOT IN contains a NULL, your query will break. This is the number one reason developers prefer NOT EXISTS for finding missing relationships.
• Forgetting the Correlation: The subquery inside EXISTS must be “Correlated,” meaning it must link back to the outer query (e.g., WHERE o.user_id = u.user_id). If you forget this, the subquery will check the same thing for every row, giving you the wrong answer.

Sub Queries and NOT IN Trap in MySQL was originally published in Towards Data Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

DATA ENGINEERING

Why lower latency isn’t always better, when batch processing wins, and how to choose the right architecture.

1. Does Every Data Pipeline Really Need Real-Time Processing?

Imagine two companies.

The first runs a payment platform. When a suspicious transaction occurs, its fraud detection system may need to identify it within seconds. Waiting until tomorrow’s report could mean discovering fraud after the damage is done.

The second runs an online retailer. Every night, it calculates total sales, identifies best-selling products, and prepares revenue reports. Does it need to process every sale the instant it happens?

Probably not.

Yet real-time processing is often treated as the definition of modern data engineering. Lower latency sounds automatically better, as if processing an event in milliseconds must be superior to processing it in minutes or hours.

Streaming can introduce additional infrastructure costs, operational complexity, monitoring demands, and failure scenarios. If the business gains little from receiving results sooner, those costs may deliver little value.

A good data engineer doesn’t choose streaming simply because the technology is available. They understand the problem, evaluate the trade-offs, and select an architecture that meets the requirement without unnecessary complexity.

That decision starts with understanding how batch and streaming actually work. That is the choice this article examines.

“The real question isn’t how quickly we can process data. It’s how quickly the business needs the results.”

2. Batch vs. Streaming: How Do They Actually Work?

2.1 Batch Processing: Process Data in Groups

Batch processing collects data over a period or from a bounded dataset, then processes that collection as a job.

Imagine an e-commerce company calculating yesterday’s sales. Its pipeline might:

• Read order records from a database.
• Validate and clean the data.
• Calculate total revenue and best-selling products.
• Write the results to a data warehouse.

The job runs, processes the available data, and finishes. The next scheduled run handles newly collected records.

Batch processing works well for daily reports, historical analysis, payroll calculations, and large-scale data transformations. Its defining characteristic is bounded input: the job processes a specific collection of data.

Batch doesn’t automatically mean slow; a well-designed job can process millions of records efficiently.

2.2 Streaming: Process Data as It Arrives

Streaming processing handles incoming data continuously, allowing systems to update results without waiting for a scheduled batch.

Consider a payment platform publishing transaction events to Apache Kafka. A streaming engine can consume those events, validate transactions, and update metrics as new data arrives. Instead of waiting until midnight, the system can identify suspicious activity much sooner.

Streaming is useful when a business needs fresh information for fraud detection, application monitoring, live dashboards, or operational alerts.

Its defining characteristic is unbounded input: new events may continue arriving indefinitely, so the pipeline must manage ongoing processing and recovery.

from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .appName("TransactionStream") \
    .getOrCreate()

transactions = (
    spark.readStream
    .format("kafka")
    .option("kafka.bootstrap.servers", "localhost:9092")
    .option("subscribe", "transactions")
    .load()
)

results = transactions.selectExpr(
    "CAST(value AS STRING) AS transaction_event"
)

query = (
    results.writeStream
    .format("console")
    .outputMode("append")
    .start()
)

query.awaitTermination()

This example demonstrates event ingestion, not fraud detection itself. It requires a running Kafka broker, the specified topic, and a compatible Spark Kafka connector.

2.3 Does Streaming Mean Instant Processing?

Not necessarily.

Streaming systems can buffer events or process them in small groups called micro-batches. For example, Spark Structured Streaming commonly uses micro-batch execution by default.

Some events may also arrive late, so the system must handle event time, state, and recovery correctly.

Both approaches need validation, error handling, monitoring, and duplicate management. Batch pipelines must also handle schedules, reruns, and backfills.

The simplest way to remember the difference:

• Batch: What should we calculate from the data collected for this period?
• Streaming: How can we update results as new events arrive?

Figure 1: Batch vs. Streaming Processing: How Data Is Processed, Stored, and Used

Neither approach is universally better. The right choice depends on how fresh the results need to be and what the business gains from receiving them sooner.

3. Latency, Cost and Complexity: The Real Trade-Offs

Choosing between batch and streaming means balancing three things: how quickly results are needed, how much they cost, and how difficult the pipeline is to maintain.

3.1. Latency: How Quickly Do Results Matter?

Batch pipelines process data on a schedule, while streaming pipelines can make results available much sooner.

For fraud detection, seconds may matter. For monthly financial reports, waiting until the next scheduled run may be perfectly acceptable.

Lower latency is valuable only when the business benefits from acting sooner.

3.2. Cost: Are You Paying for Unnecessary Speed?

Streaming may require continuously running processing resources, monitoring, and infrastructure to handle incoming events. Batch jobs can often use compute resources only when processing is needed.

However, neither approach is automatically cheaper. Costs depend on data volume, workload, infrastructure, and operational requirements.

The goal isn’t to minimize cost at any price. It’s to avoid paying for capabilities the business doesn’t need.

3.3. Complexity: What Must You Keep Under Control?

Batch pipelines must handle failed jobs, reruns, and historical backfills. Streaming adds challenges such as late-arriving events, state management, duplicate handling, and continuous monitoring.

These problems are solvable, but they demand careful engineering.

The Real Trade-Off

Before choosing streaming, ask yourself:

• How quickly must the result be available?
• What value does that speed create?
• Can the team manage the additional complexity?

The best architecture isn’t necessarily the fastest. It’s the one that delivers the required freshness at a justified cost and manageable complexity.

“The best architecture isn’t necessarily the fastest.”

4. Practical Examples: When Each Approach Wins

The best way to understand batch and streaming is to see how businesses use them.

Example 1: E-Commerce Sales Analytics — Batch Wins

An online retailer wants to calculate daily revenue, identify best-selling products, and compare regional sales.

A batch pipeline can process the day’s orders overnight, clean and transform the records, aggregate sales, and load the results into a data warehouse.

Because daily reports don’t require instant updates, continuous processing may introduce unnecessary infrastructure and operational complexity.

Why batch? Scheduled reporting and efficient processing of accumulated data.

Example 2: Payment Fraud Detection — Streaming Wins

A payment platform receives thousands of transactions continuously. Waiting until the end of the day to identify suspicious activity could expose the business to financial losses.

A streaming pipeline can consume transaction events from Apache Kafka, process them using Spark Structured Streaming, and evaluate patterns such as repeated transactions or unusual activity within short time windows.

Figure 2: Real-Time Fraud Detection Pipeline Using Apache Kafka and Spark Structured Streaming.

Suspicious events can then be sent to a detection system for further investigation. Actual detection speed depends on the processing logic and supporting services.

Why streaming? Faster detection enables earlier action.

Example 3: Historical Data Analysis — Batch Wins

Suppose a company wants to analyse five years of customer purchases to identify seasonal trends and changes in buying behaviour.

Since the historical data already exists, the company can process the bounded dataset using PySpark, transform millions of records, and generate analytical datasets for reporting or machine learning.

The pipeline can also be rerun when business rules change or historical data needs correction.

Why batch? Efficient processing of historical datasets with support for repeatable analysis.

Example 4: Application Monitoring — Streaming Wins

A production application generates logs and metrics continuously. If error rates suddenly increase, engineers need to identify the problem before it affects more users.

A streaming pipeline can calculate error rates over short time windows and trigger alerts when predefined thresholds are exceeded.

Instead of discovering the issue in a report the next morning, the engineering team can investigate while the incident is unfolding.

Why streaming? Faster detection supports quicker incident response.

What Happens in Real Production Systems?

Many organisations combine both approaches.

A retailer might stream incoming orders to update operational dashboards while running nightly batch jobs to reconcile transactions and produce financial reports.

The same data can support different workloads, each with its own freshness requirements.

The lesson is simple: choose the processing model based on the business problem, not the popularity of the technology.

5. Why Modern Data Pipelines Often Combine Both

Batch and streaming aren’t competing approaches where one must replace the other. In many production systems, they work together to meet different business requirements.

One System, Different Processing Needs

Consider a logistics company tracking thousands of shipments across multiple locations.

Its operations team needs timely shipment updates, while management needs daily reports on delivery performance, delays, and operational costs.

A streaming pipeline can process incoming shipment events and update tracking dashboards as new events arrive. Meanwhile, a batch pipeline can analyse historical records to calculate average delivery times, identify recurring delays, and generate performance reports.

Figure 3: Hybrid Data Architecture — Combining Real-Time Processing and Batch Analytics for Logistics.

Streaming supports timely operational decisions, while batch supports historical analysis and reconciliation.

The Challenge: Keeping Results Consistent

Combining both approaches introduces an important engineering challenge: ensuring that their results remain consistent.

For example, a shipment might initially appear delayed because its delivery confirmation hasn’t arrived. When that event eventually arrives, the system must update its records.

Engineers need clear rules for handling late events, duplicate records, corrections, and reconciliation. Without them, operational dashboards and historical reports may show conflicting results.

Running two pipelines isn’t enough. Their processing rules and data quality checks must work together.

The Bigger Picture

Modern data engineering isn’t about choosing batch or streaming everywhere. It’s about matching processing patterns to business requirements.

Some workloads need fresh information. Others need complete historical datasets. Many organisations need both.

“The goal isn’t to make every dataset real-time. It’s to deliver reliable information at the right time for the decision being made.”

6. How to Choose Between Batch and Streaming

6.1 Start With Business Requirements

Ask two questions: How quickly are results needed, and what happens if they’re delayed?

A daily sales report can usually wait for a scheduled batch job. A fraud detection system may need to identify suspicious transactions within seconds.

The acceptable delay should determine the required processing speed — not the popularity of a technology.

6.2 Evaluate Data and Operational Complexity

Consider whether you’re processing a fixed historical dataset or continuously arriving events. Batch naturally suits bounded datasets, while streaming supports ongoing event processing.

Then evaluate what your team can reliably operate. Streaming requires attention to continuous processing, late-arriving events, recovery, and monitoring. Batch also needs scheduling, retries, and backfills.

6.3 Balance Cost Against Value

Compare infrastructure, compute resources, monitoring, and maintenance costs against the benefits of faster results.

If streaming enables earlier decisions that prevent losses or improve operations, the additional investment may be worthwhile. If the business gains little from lower latency, batch may be the more practical choice.

When different workloads have different freshness requirements, combining both approaches may work best.

The principle is simple: choose the architecture that meets business requirements reliably, at a justified cost — not the one that merely sounds more advanced.

7. Final Takeaway: Build for Business Needs, Not Just Speed

Batch and streaming aren’t competing technologies where one must replace the other. They are different approaches designed to solve different data processing problems.

Batch helps process accumulated data efficiently. Streaming enables faster insights from continuously arriving events. Combining them can provide both timely updates and reliable historical analysis when the business needs both.

As a data engineer, resist the temptation to choose an architecture simply because it sounds modern. Understand the required freshness, evaluate the costs, and consider the complexity your team can manage.

“The best data pipeline isn’t the one that processes data the fastest. It’s the one that delivers the right information, at the right time, with the right level of reliability.”

That is the difference between building a technically impressive pipeline and building one that creates real business value.

Batch vs. Streaming: The Real-Time Trap Data Engineers Need to Understand was originally published in Towards Data Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Excel file arrives on Monday. It has a title, a few explanatory rows, two tables next to each other, and a third table farther down. One table even has a note immediately below its header.

Next month, somebody moves everything two columns to the right.

If your ingestion code depends on skiprows=3, usecols="C:D", or sheet_name=0, the layout is now part of your application's logic. Every change to the report can mean another code change.

The tricky part isn’t reading the cells. It’s finding the actual tables and deciding which rows belong to them.

That’s the problem I built chooong.datasift to address. Its core capability is automatic table discovery, backed by a native Rust processing engine and exposed through Python.

One workbook. Several tables. No fixed coordinates.

Here is our sample workbook, messy-sales.xlsx, on the Operations worksheet:

The workbook contains three tables on Operations: two adjacent tables starting in row 4, and a third table starting in row 10. The orange row is a note, not a transaction.

The same workbook also contains a Regional worksheet, where another Customer/Amount table starts at a different position.

The fourth table is on another worksheet. Our extraction code won’t refer to either worksheet by name or index.

A conventional extraction might hardcode a starting row and column range, then repeat the process for each region. That works for stable templates, but it is brittle when spreadsheet layouts change.

DataSift lets us describe the columns we recognize instead of their physical coordinates.

Discover tables across all worksheets

from chooong.datasift import iter_matching_rows

mask = {
    "sheet": None,
    "header": {
        "anchors": ["Customer", "Amount"]
    },
    "row_policy": {
        "rules": [{
            "classification": "comment",
            "where": {
                "column": "Customer",
                "op": "starts_with",
                "value": "NOTE:"
            }
        }]
    },
    "select": ["Customer", "Amount"]
}
rows = [
    dict(row)
    for row in iter_matching_rows("messy-sales.xlsx", mask)
]
print(rows)

For the workbook shown above, the expected matching rows are:

[
    {"Customer": "Bob", "Amount": 240},
    {"Customer": "Alice", "Amount": 120},
    {"Customer": "Charlie", "Amount": 360},
    {"Customer": "Dana", "Amount": 480},
]

There are no header row numbers, no A1 assumptions, no column letters, and no sheet index in that mask.

Three things are happening:

1. Named-column Discovery finds the table regions. The anchors identify complete Customer/Amount header groups even when they appear side by side or farther down the worksheet.

2. sheet=None scans every worksheet. DataSift processes both Operations and Regional without us specifying either name or position. This also includes hidden worksheets, so choose a sheet explicitly when that's not what you want.

3. The note is excluded by a content-based rule. The line NOTE: preliminary figures sits directly under the first header. The row_policy rule classifies it as a comment, so it isn't returned as a matching transaction. Importantly, DataSift doesn't guess that the English sentence is a comment: we define how to recognize it. The rule is based on content, not on row 5.

This combination is more useful than simply reading all worksheets. The important difference is discovering several independent table regions within those worksheets.

Inspect what the engine actually found

DataSift can report the discovered schemas and scan statistics. This is useful when debugging unpredictable files:

from chooong.datasift import iter_matching_batches

with iter_matching_batches("messy-sales.xlsx", mask) as scan:
    rows = [dict(row) for batch in scan for row in batch]
    stats = scan.statistics
assert stats is not None and stats.complete
assert len(stats.tables) == 4

The scan must complete before its final statistics can be trusted. A partial iterator result isn’t proof that the whole workbook was processed successfully.

Unknown column names? Free Discovery.

What if you don’t even know the schema ahead of time?

DataSift also supports Free Discovery: no column anchors, no filter expression, no table coordinates. Here’s a CSV file with two neighboring tables and a third table below them:

Sales report
,Customer,Amount,,Code,Count
,Alice,120,,X-01,2
,,,,,
,,City,Population
,,Berlin,3

Read it without describing any columns:

from chooong.datasift import FilterMask, iter_matching_rows

rows = [
    dict(row)
    for row in iter_matching_rows(
        "discovery.csv", FilterMask(sheet=None)
    )
]
print(rows)

Expected result:

[
    {"Customer": "Alice", "Amount": "120"},
    {"Code": "X-01", "Count": "2"},
    {"City": "Berlin", "Population": "3"},
]

Free Discovery uses structural evidence to confirm candidate headers. In this example, the immediately following records contain numeric text, which helps confirm each region. This isn’t semantic AI inference: purely textual tables and ambiguous layouts can require named anchors or explicit sources. An explicit empty-field record (,,,,,) separates the two CSV regions; a blank physical line is not equivalent.

Notice that CSV numeric values remain strings. DataSift doesn’t silently convert them just because the discovery engine recognized their structure.

CSV isn’t a second-class input

The same rule system works with CSV files, including files where the header starts after a preamble. For example, given transactions.csv:

Monthly transactions
,Country,Amount,Reference
,DE,1500,INV-001
,AT,2000,INV-002
,DE,900,INV-003
,DE,2500,INV-004

We can discover the shifted table, convert Amount to an integer, filter German transactions worth at least 1,000, and select only two output columns:

from chooong.datasift import iter_matching_rows

mask = {
    "sheet": None,
    "header": {"anchors": ["Country", "Amount"]},
    "types": {
        "Amount": {"type": "integer", "convert": True}
    },
    "where": {"all": [
        {"column": "Country", "op": "eq", "value": "DE"},
        {"column": "Amount", "op": "gte", "value": 1000}
    ]},
    "select": ["Reference", "Amount"]
}
rows = [dict(row) for row in iter_matching_rows("transactions.csv", mask)]
print(rows)

Result:

[
    {"Reference": "INV-001", "Amount": 1500},
    {"Reference": "INV-004", "Amount": 2500},
]

CSV supports configurable delimiters, encodings, quoted fields, and embedded newlines. Delimiters and encodings are configured explicitly rather than guessed.

Where Rust fits in

Discovery is the reason to look at DataSift. The Rust core takes care of parsing, row filtering, and native NDJSON/Parquet export; the Python API provides typed configuration and streaming iteration. DataSift doesn’t build a full pandas DataFrame to run this pipeline.

That doesn’t mean every Excel structure can be processed in constant memory: workbook metadata and shared strings still consume memory. And performance claims should be based on reproducible benchmarks, not the choice of programming language alone.

The aim is to make messy input discovery and validation predictable, before pandas, Polars, or other tools handle downstream analysis and transformation.

Where automatic discovery stops

DataSift doesn’t understand the business meaning of arbitrary prose. A comment directly below a header can be excluded through a rule, as shown above, but it isn’t magically identified by its wording.

Free Discovery needs structural evidence. Complex text-only sheets, ambiguous regions, and some multirow headers require anchors, explicit configuration, or manual table sources. XLS/XLSB, encrypted workbooks, and formula calculation are not supported.

If a layout cannot be identified reliably, reporting ambiguity is preferable to silently extracting the wrong data.

Try chooong.datasift

pip install chooong.datasift

DataSift v0.1.0 is open source under Apache 2.0 and supports CPython 3.12–3.14.

• GitHub repository
• Documentation
• Automatic table discovery guide
• PyPI

If you have a particularly awkward Excel or CSV export, I’d like to hear about it — especially layouts that the current discovery modes cannot handle yet.

Thanks for reading!

How to Automatically Discover Tables in Messy Excel and CSV Files with Python was originally published in Towards Data Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Now available: the e-book

I have just published an e-book, The Journey Practice: Building an AI-Native, Repeatable Databricks Delivery Engine. It runs to 95 pages with 24 diagrams, and it is the long version of this article.

Download it here:

The art of Building... with Akash A Wadhankar | Topmate

Inside you will find:

• The blue ocean logic for a Databricks services practice, and the operating model behind it
• Eight steps in three acts, followed through one composite customer, each with architecture, implementation and measurable outcomes
• An in-depth chapter on the governed lakehouse, including a control catalogue
• A delivery playbook: lifecycle, roles, a day-one kit, quality gates, estimating and week-by-week plans

Every Databricks partner looks the same

Open the websites of ten Databricks partners and you will find one story: a partner tier, a count of certified engineers, a list of accelerators and photographs from the same conferences. Customers struggle to tell the partners apart, so the decision falls to price, relationships or whoever can staff the team first.

That is a red ocean. Everyone is fighting for the same thing, the first project, which is the most contested and most commoditised stage of the relationship. Conference budgets and demo-grade accelerators become an arms race with no clear return.

The customer already told us the answer

Here is the observation that changed how I think about the practice. Customers judge a partner by the foundation work done for them. If it was solid, they ask the same partner to lead the next stage.

The first project is the audition. The journey is the business. Yet very few partners are built deliberately for the second and third act: they win the foundation, deliver it and leave.

A customer’s data and AI programme moves through recognisable stages: a platform they can trust, data that is understood, answers in the hands of business users, and data products that move a number the business cares about. Each question can only be answered once the previous one has been. That dependency is what turns separate projects into one journey.

Four choices that create a blue ocean

Blue ocean strategy asks which accepted factors of competition to eliminate, reduce, raise and create. For a Databricks practice, my answers are:

• Eliminate the conference-and-accelerator arms race, and pitching on certified headcount alone. Competitors match both.
• Reduce one-off projects sold as ends in themselves, and delivery that depends on a few heroes.
• Raise the quality and visibility of the foundation, so it becomes an auditable asset with scores, standards and a reference architecture the customer owns.
• Create productised journey offerings, AI-native delivery, and commercial terms tied to stages and outcomes.

A caution: “journey partner” is already on many websites. It only becomes a blue ocean when it is concrete and measurable, with defined stages, artefacts, outcomes and terms.

Three acts, eight steps

The book follows one composite customer through eight steps in three acts. Each act hands a named artefact to the next, so skipping ahead is how pilots fail to reach production.

Act I, Trusted Foundation: can we trust our platform, data and costs?

1. AI-led assessment. Agents collect evidence and score the estate; consultants validate it. The result is a baseline, not an opinion.
2. Governed lakehouse. Every asset has an owner, a classification and an access policy, all created through code.
3. Ingestion engine. Standard, monitored patterns for batch, change capture and streaming, chosen by business need.
4. AI-driven FinOps and administration. Agents observe, attribute and recommend; people approve every change.

Act II, Answer Engine: can people simply ask, and trust the answer?

1. Context engine. Business definitions, metrics and relationships, so the platform understands the business.
2. Genie and chatbots. Self-service answers, released only after accuracy is proven on questions the business wrote.

Act III, Proven Value: is any of this moving a business number?

1. Data products. Governed data with a named owner, service levels and a metric it must move.
2. Experience layer in Databricks Apps. The product, in front of the person who acts on it, inside the platform’s governance.

Half-ready on day one, and AI-native by design

The operating principle is simple to state. When a new customer signs, at least half of the work that is normally invented from scratch should already exist as a tested asset, a trained person or a standard process. That is what “50% ready on day one” means, and it is measured, not felt.

The readiness comes from five layers: blueprints, reusable code and automation, delivery playbooks, people, and commercial and governance assets. Each asset is scored from Level 0 (nothing exists) to Level 3 (proven on two or more engagements). A practice becomes “day-one ready” when half its kit sits at Level 2 or above.

AI-native means two things. AI is part of what you deliver, and it is part of how you deliver. The mechanism is a library of skills: narrow, versioned, tested units of work that agents and people run together. Each skill has an owner, evaluation tests and an autonomy level (assistive, supervised or autonomous), and moves up only on evidence. If you cannot explain to a customer how an AI-generated deliverable was checked, it is not ready to be delivered.

Why the governed lakehouse is the centre of gravity

Everything in Acts II and III depends on it. An assistant or a data product can never be more trustworthy than the foundation beneath it. So the book gives this step the most depth, and the idea behind it is govern once, apply everywhere.

On Databricks, the control point is Unity Catalog. Data Classification finds sensitive data and proposes tags, stewards approve them as governed tags, and attribute-based access control policies apply row filters and column masks to every matching column, including data that does not exist yet. Access is evaluated at query time against the identity of the person asking, and the evidence lands in system tables, so an audit becomes a query and not a three-week project.

The second half of the idea is everything as code. Account infrastructure goes through Terraform, workspace resources through Declarative Automation Bundles (formerly Databricks Asset Bundles), and every production change through CI/CD. That is what makes a foundation repeatable for the second region, the second business unit or the second customer.

A practical note: some of these capabilities are still in Preview, so build the controls your security team relies on on generally available features, and check each name and status against current Databricks documentation.

Measure the journey, not the project

Each step in the book ends with a table of measures: baseline, target, result and how it is measured. The customer’s own CFO, CISO and head of data platforms then read one scorecard. These figures come from the book’s composite customer, “Meridian Manufacturing”. They are illustrative, to show how a result is defined and how large it can plausibly be, not claims about a real engagement.

The habit matters more than the numbers: agree the baseline before work starts, name an owner for every line, and let finance validate any benefit you report.

What to do on Monday

You do not need the whole model to start. Five moves will change the conversation with your next customer:

• Look at your last three engagements and list what you rebuilt every time. That is your first library.
• Score your readiness honestly across the five layers, and pick the three assets that would save the most effort.
• Turn your assessment into a fixed-scope, evidence-based front door, and repeat it every six to twelve months.
• Give every engagement a named hand-off artefact that makes the next step obvious.
• Reward expansion, reuse and customer outcomes, not only new logos and project revenue.

The red ocean will not go away, and conferences and accelerators still have their place. But the practice that is more ready, more repeatable and more accountable for what happens next is the one customers take along on the journey.

The full model, the diagrams, the control catalogue and the playbook are in the e-book.

Download The Journey Practice here:

The art of Building... with Akash A Wadhankar | Topmate

I would be glad to hear what you build with it.

The Journey Practice: Building a Databricks Practice Around the Customer’s Journey, Not the First… was originally published in Towards Data Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

A profile has a name. An order has a total. A person has friends.

We naturally describe information through relationships. .me gives those relationships a consistent shape in code: paths hold values, links connect branches, and formulas describe how values depend on one another.

Start with a path:

me.profile.name("Ana");

Read it back:

me.profile.name(); // "Ana"

That is the starting point: one tree, with a small grammar for working with it.

Start with your first branch

Here is a minimal example using the JavaScript package:

import Me from "this.me";

const me = Me();

me.profile.name("Ana");
me.profile.bio("Building the semantic web.");

console.log(me("profile.name")); // "Ana"

You build the hierarchy as you write. Here, profile is a branch, and name and bio are values beneath it.

The labels are yours. .me does not need a built-in definition of “profile” to organize that information.

You can read through the property interface or pass a path to the kernel:

me.profile.name();
me("profile.name");

Both address the same value.

Give the tree a rule

Some values should be calculated rather than entered manually.

Consider an order:

me.order.price(100);
me.order.quantity(5);

me.order["="]("total", "price * quantity");

console.log(me("order.total")); // 500

The = operator declares a derived value. The expression uses the other values in that branch.

Now change the price:

me.order.price(200);

console.log(me("order.total")); // 1000

You update the input. The total follows its declared relationship.

The formula remains available for inspection:

const explanation = me.explain("order.total");

console.log(explanation.expr);
// "price * quantity"

console.log(explanation.meta.dependsOn);
// ["order.price", "order.quantity"]

That gives the result context: you can inspect both its value and the dependencies behind it.

GitHub - neurons-me/.me: Here we're codependently creating .me while it concurrently creates us.

Connect information without copying it

A tree gives information a location. Links let you refer to it from another location.

me.users.ana.name("Ana");

me.friends.ana["->"]("users.ana");

console.log(me("friends.ana.name")); // "Ana"

Here, friends.ana points to users.ana. You have expressed a relationship instead of manually maintaining another copy of Ana’s profile.

This is how a hierarchy can also describe a graph: branches organize information, while pointers connect it.

Apply a rule across a collection

The same grammar extends to multiple members.

me.people.ana.age(24);
me.people.pablo.age(17);

me.people["[i]"]["="]("isAdult", "age >= 18");

console.log(me("people.ana.isAdult"));   // true
console.log(me("people.pablo.isAdult")); // false

[i] broadcasts the declaration across the collection. Each member evaluates the rule in its own context.

You describe the relationship once, rather than writing a separate formula for every person.

The labels do not have to be English

These examples express the same calculation:

me.shop.price(100);
me.shop["="]("total", "price * 1.16");

me.tienda.precio(100);
me.tienda["="]("total", "precio * 1.16");

The kernel evaluates paths and expressions. It does not require English labels, nor does it automatically translate between them. You choose the vocabulary and use it consistently.

A small grammar to remember

You can begin with just five ideas:

• . builds a hierarchy.
• () reads or writes a value.
• ["="] declares a derived value.
• ["->"] creates a pointer.
• ["[i]"] broadcasts across members.

The operators appear in brackets because this is JavaScript: names such as = and -> cannot use ordinary dot notation.

You do not need to start with a visual editor, a network deployment, or a shared ledger. Those integrations can build on the same tree; persistence and network access require their own runtime configuration.

Start with something familiar — a profile, an order, a collection of people. Give it values. Connect its branches. Add a rule.

me.order["="]("total", "price * quantity");

Now your data carries more than a result. It carries a relationship the program can evaluate and you can inspect.

me.love(you)

MIT License · Made in Veracruz, Mexico.

Using .me: A Tree You Can Read, Write, and Teach Rules was originally published in Towards Data Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Why enterprises need a centralized metric definition, and how dbt is changing the way we engineer business semantics.

We have built data warehouses. We have built lakehouses. We have built transformation pipelines and enterprise data models.

But one problem continues to exist across organizations.

Why does the same business metric produce different answers in different reporting systems?

Imagine an organization calculating revenue across five different dashboards.

Each dashboard has its own SQL logic. Each team applies slightly different filters. Some include cancelled orders, others exclude them. Some calculate gross revenue, while others calculate net revenue. Now introduce AI into this environment.

How can an AI agent reliably answer business questions if the enterprise itself has not standardized its metrics?

This is where the Semantic Layer becomes critical.

Introducing our new white paper: The dbt Semantic Layer

We have added a detailed white paper exploring:

The dbt Semantic Layer: Engineering Reusable Business Metrics with MetricFlow

The objective is to understand how enterprises can define metrics once and make them reusable across analytics tools, applications and AI experiences.

What does the white paper cover?

1. Understanding the dbt Semantic Layer

Explore how dbt separates business metric definitions from individual reporting queries.

2. Understanding MetricFlow

Learn how MetricFlow understands semantic models, entities, dimensions and metrics, and generates SQL for business questions.

3. Building semantic models

Explore how to define:

• Semantic models
• Entities and relationships
• Dimensions
• Measures
• Metrics

4. Practical retail implementation

We have included a practical example demonstrating how to build reusable Net Sales metrics on top of retail Gold-layer models.

5. dbt Semantic Layer vs Snowflake Semantic Views

One of the most interesting parts of this white paper is the comparison between the two approaches.

We explore:

• Where semantic definitions are maintained.
• How metrics are queried.
• How joins and relationships are resolved.
• How the two approaches support AI.
• Cross-platform portability.
• Governance and deployment.

We also explore an interesting third option: managing Snowflake Native Semantic Views through a dbt project and the Snowflake Semantic View dbt package.

6. AI and MCP integration

Understand how governed metric definitions can be exposed to AI applications and agents through supported dbt interfaces and MCP integrations.

Why should Data Engineers pay attention?

The role of the Data Engineer is changing.

Building pipelines and Gold tables is no longer the complete responsibility. We need to understand business definitions, engineer reusable metrics, establish semantic relationships and make business meaning accessible to AI.

This is the evolution:

Data Engineering → Analytics Engineering → Metric Engineering → Context Engineering

The dbt Semantic Layer is an important part of this journey.

The bigger picture

Our Context Engineering initiative is exploring how to combine:

• Data Engineering
• Business Ontology
• Knowledge Graphs
• Semantic Layers
• Metric Engineering
• Context Intelligence
• AI Agents

The objective is to build an enterprise architecture where every dashboard, analyst and AI agent works with consistent business definitions.

Continue learning

The new dbt Semantic Layer white paper is now part of our Context Engineering learning collection on Topmate.

Explore the complete learning material here:

Databricks AI Context... with Akash A Wadhankar | Topmate

We will continue adding practical implementation guides, architecture papers and enterprise use cases.

Stop building metrics separately in every dashboard. Start engineering reusable business meaning.

#dbt #MetricFlow #dbtSemanticLayer #ContextEngineering #Snowflake #SemanticLayer #AnalyticsEngineering #DataEngineering #EnterpriseAI #MCP

Context Engineering: Introducing the dbt Semantic Layer — Engineering Reusable Business Metrics… was originally published in Towards Data Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.