9. Databases, SQL and Scalable Data Access

Introduction and Context

Data science rarely operates on isolated files. In professional settings, data is distributed across transactional databases, analytical warehouses, application programming interfaces, document stores, object storage systems and cloud platforms. A data scientist may need to identify suitable records, combine information from several systems, preserve data quality, control access, and deliver results within practical limits of time and cost. The ability to write a correct model is therefore insufficient unless the practitioner can also obtain data efficiently and responsibly.

Databases provide the persistence, organization and governance required for reliable data work. SQL remains the principal language for relational data, while Python and R provide programming environments for analysis, automation and statistical modelling. Modern practice also involves NoSQL systems, distributed query engines, cloud warehouses, connectors, object-relational mapping and query optimization. These technologies are related, but they solve different problems. Choosing among them requires attention to data structure, workload, scale, consistency, latency, security and organizational requirements.

This chapter develops an integrated view of database access for data science. It begins with relational concepts and SQL, then examines transactions, schemas, performance, Python and R connectivity, ORM concepts, NoSQL and cloud platforms. It concludes with an applied case showing how technical choices affect analytical outcomes.

Learning Objectives

By the end of this chapter, the learner should be able to:

1. Explain relational, NoSQL and cloud data-platform concepts.

2. Design and interpret tables, keys, constraints and relationships.

3. Write SQL queries using filtering, joins, aggregation, subqueries and window functions.

4. Explain transactions, isolation, normalization and schema evolution.

5. Connect Python and R to databases securely and reproducibly.

6. Distinguish direct SQL access from ORM-based access.

7. Evaluate query performance, data locality, partitioning and indexing.

8. Select appropriate storage and access strategies for analytical workloads.

9. Identify security, governance, ethical and operational risks.

10. Apply scalable data-access principles to a realistic data-science problem.

The Database as an Analytical System

A database is an organized system for storing, managing and retrieving data. A database management system (DBMS) provides mechanisms for defining structures, executing queries, enforcing constraints, managing concurrent users and recovering from failures. The database is the data resource; the DBMS is the software that controls it.

A relational database represents data in relations, commonly visualized as tables. Each row is a record and each column represents an attribute with a defined data type. Tables are linked through keys. A primary key uniquely identifies a row, while a foreign key refers to a key in another table. For example, a customer table may have `customer_id` as its primary key, and an orders table may contain `customer_id` as a foreign key.

Relational design is valuable because it separates facts into logically coherent entities and makes relationships explicit. It also supports declarative querying: the analyst specifies what data is required, while the query optimizer decides how to obtain it. This abstraction is one reason SQL remains important even when data volumes and infrastructure become highly sophisticated.

A schema describes the structure and rules of a database. It includes tables, columns, types, keys, constraints, views, indexes and permissions. A well-designed schema reduces ambiguity. Constraints such as `NOT NULL`, `UNIQUE`, `CHECK` and foreign-key rules prevent many forms of invalid data from entering the system. However, constraints must be designed with the workload in mind. A highly normalized operational schema may be appropriate for transaction processing but inconvenient for repeated analytical queries.

Normalization and Analytical Structure

Normalization organizes data to reduce redundancy and update anomalies. In a normalized design, customer details are stored once rather than repeated in every order. This improves consistency, but queries may require several joins. Operational systems commonly favor normalized schemas because they support frequent inserts and updates.

Analytical systems often use dimensional models. A fact table records measurable events, such as sales transactions, while dimension tables describe entities such as products, customers and dates. A star schema places the fact table at the center and connects it to relatively denormalized dimensions. A snowflake schema normalizes some dimensions further. These designs can improve query readability and reporting performance, particularly when aggregating large event tables.

The distinction between online transaction processing (OLTP) and online analytical processing (OLAP) is central. OLTP systems emphasize short, concurrent, consistent transactions. OLAP systems emphasize scanning, joining and aggregating large datasets. A data warehouse, lakehouse or analytical database may receive data from operational systems through batch or streaming pipelines rather than serving as the primary transaction system.

SQL as a Language for Data Reasoning

SQL is declarative. A query describes a result rather than a sequence of machine instructions. A typical query uses the clauses `SELECT`, `FROM`, `WHERE`, `GROUP BY`, `HAVING` and `ORDER BY`.

```sql

SELECT product_category,

COUNT(*) AS orders,

SUM(order_amount) AS revenue

FROM orders

WHERE order_date >= DATE '2025-01-01'

GROUP BY product_category

HAVING SUM(order_amount) > 100000

ORDER BY revenue DESC;

```

The logical processing order differs from the written order. Conceptually, the database identifies the source rows, filters them with `WHERE`, groups them, computes aggregates, filters groups with `HAVING`, selects output expressions and orders the result. Understanding this order prevents errors such as attempting to use an aggregate in `WHERE` when `HAVING` is required.

Joins combine rows from related tables. An inner join returns matching rows from both tables. A left join preserves every row from the left table and adds matching values from the right table, using nulls where no match exists. Analysts must examine join cardinality carefully. Joining a customer table to an orders table creates one row per matching order, not one row per customer. If a second one-to-many table is added without pre-aggregation, the result may multiply rows and inflate totals.

```sql

SELECT c.customer_id,

c.segment,

COALESCE(SUM(o.order_amount), 0) AS total_spend

FROM customers AS c

LEFT JOIN orders AS o

ON c.customer_id = o.customer_id

GROUP BY c.customer_id, c.segment;

```

Subqueries and common table expressions (CTEs) make complex reasoning clearer. A CTE can isolate an intermediate result such as each customer's first purchase, then use it in a later query. Window functions calculate values across related rows without collapsing them into one row per group. For example, `ROW_NUMBER()` can identify the first order per customer, while `SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date)` computes a running total.

Null is not equivalent to zero or an empty string. It represents an unknown, missing or inapplicable value. Comparisons with null use three-valued logic, so `column = NULL` is not the correct test; `IS NULL` must be used. Functions such as `COALESCE` can provide a default, but replacing missing values without understanding their meaning may distort analysis.

SQL dialects differ in date functions, string operations, quoting rules and analytic features. Portable SQL is useful, but production queries should be tested against the actual DBMS. A query that is syntactically valid in one platform may behave differently in another, especially with timestamps, null handling or implicit type conversion.

Transactions, Consistency and Schema Change

A transaction is a logical unit of work. The widely used ACID properties are atomicity, consistency, isolation and durability. Atomicity means that a transaction succeeds completely or is rolled back. Consistency means that committed transactions preserve defined rules. Isolation controls how concurrent transactions observe one another. Durability means that committed data survives an appropriate system failure.

Isolation involves trade-offs. Strong isolation reduces anomalies but may increase locking or waiting. Weaker levels can improve concurrency but allow phenomena such as dirty reads, non-repeatable reads or phantom rows, depending on the DBMS. Data scientists working with production systems should avoid unnecessary updates and understand whether a read uses a consistent snapshot.

Python and R workflows often contain both database operations and analytical transformations. A reliable workflow commits only after the required operations succeed and rolls back when an exception occurs. Long-running analytical transactions should be treated cautiously because they can hold resources, delay maintenance or prevent effective cleanup.

Schema evolution is the controlled modification of data structures over time. Adding a nullable column is generally less disruptive than renaming a column or changing its type. Good practice includes versioned migrations, backward-compatible changes where possible, documentation, testing against representative data and communication with downstream users. A schema is an interface: changing it can break dashboards, models and automated pipelines even when the database itself remains available.

Access from Python and R

A database connector translates commands between a programming language and a DBMS. In Python, commonly used approaches include DB-API-compatible drivers, SQLAlchemy and data-frame libraries such as pandas. In R, the DBI and dbplyr ecosystems provide standard interfaces and, where appropriate, translation of data-manipulation operations into SQL.

A basic Python pattern is:

```python

from sqlalchemy import create_engine, text

import pandas as pd

engine = create_engine(connection_url, pool_pre_ping=True)

query = text("""

SELECT customer_id, SUM(order_amount) AS total_spend

FROM orders

WHERE order_date >= :start_date

GROUP BY customer_id

""

)

with engine.connect() as connection:

result = pd.read_sql(query, connection,

params={"start_date": "2025-01-01"})

```

The parameterized query is important. Values are passed separately from SQL text, reducing SQL-injection risk and avoiding fragile string construction. Credentials should not be embedded in source code. Secrets should be supplied through a secret manager, protected environment configuration or an approved identity mechanism. Connection pools reuse connections and can improve performance, but pool size must reflect database capacity.

An R workflow may use DBI and a database-specific driver:

```r

library(DBI)

con <- dbConnect(RPostgres::Postgres(),

dbname = Sys.getenv("DB_NAME"),

host = Sys.getenv("DB_HOST"))

result <- dbGetQuery(

con,

"SELECT customer_id, SUM(order_amount) AS total_spend

FROM orders

GROUP BY customer_id"

)

dbDisconnect(con)

```

For large datasets, loading everything into memory is often inappropriate. A better strategy is to filter and aggregate in the database, retrieve only required columns, process data in chunks or use an engine capable of lazy execution. In R, a `tbl()` object may represent a remote table; transformations can remain remote until `collect()` is called. In Python, chunked reads, server-side cursors or distributed data frameworks can serve similar purposes.

The principle is simple: move computation to the data when the database is better positioned to perform it, and move data to the computation only when the transferred volume is justified. This is the issue of data locality. Transferring a large table across a network can be slower and more expensive than executing a selective query near the storage system.

ORM Concepts and Direct SQL

Object-relational mapping (ORM) represents database tables and relationships as programming-language objects. It can reduce repetitive CRUD code, support migrations and make application code more expressive. ORM abstractions are useful for domain-driven applications in which objects are created and updated frequently.

For analytical work, direct SQL is often preferable when the query involves complex joins, window functions, database-specific features or performance-sensitive transformations. ORMs can generate inefficient queries, hide the number of database calls or create accidental row-by-row processing. A hybrid strategy is often strongest: use ORM facilities for ordinary application operations and explicitly written, parameterized SQL for complex analytical queries.

Regardless of access style, inspect generated SQL, measure execution time and understand transaction boundaries. Abstraction is valuable only when it does not prevent informed control over the system.

Query Performance and Scalable Access

Performance is not determined by query text alone. It depends on data volume, statistics, indexes, storage format, concurrency, network distance, partitioning and the execution plan selected by the optimizer.

An index is an auxiliary structure that helps locate rows without scanning an entire table. Indexes are useful for selective filters, join keys and ordering patterns, but they require storage and increase write cost. A low-cardinality column, such as a binary status field, may not benefit from an ordinary index unless combined with other conditions or supported by a specialized design.

The `EXPLAIN` or `EXPLAIN ANALYZE` facility reveals an execution plan. Important indicators include sequential scans, index scans, join algorithms, estimated versus actual row counts, sorting, spilling to disk and data movement between distributed nodes. Large differences between estimates and actual counts may indicate stale statistics or correlated data, causing poor plan choices.

Common performance practices include:

• selecting only required columns rather than using `SELECT *`;
• filtering early, especially on partitioning columns;
• pre-aggregating before a many-to-many join;
• avoiding functions on indexed filter columns when they prevent index use;
• using appropriate data types;
• collecting statistics and monitoring query plans;
• separating workloads that compete for the same resources.

Partitioning divides a large table according to a key such as date or region. Partition pruning allows a query to read only relevant partitions. Partitioning is not a substitute for good query design: a query that omits the partition key may still scan the entire dataset. In distributed systems, data distribution should also minimize network shuffling. Joining tables on a common distribution key can reduce data movement.

Columnar storage keeps values from the same column together and is particularly effective for analytical scans that select a small number of columns from many rows. Compression is often efficient because adjacent values share characteristics. Row-oriented storage is generally better for retrieving or updating complete records. The correct choice depends on workload rather than fashion.

NoSQL and Cloud Data Platforms

NoSQL is not a single technology. Document databases store semi-structured records, key-value systems optimize lookup by key, wide-column systems support distributed high-volume access patterns, and graph databases represent entities and relationships as nodes and edges. These systems often relax relational constraints or joins to achieve flexible schemas, low latency or horizontal scalability.

Document databases are useful when records have variable attributes and are commonly retrieved as complete documents. However, embedding related data can create duplication and update challenges. Referencing separate documents may preserve flexibility but require additional reads. The data-access pattern should guide the design.

Cloud platforms commonly separate storage from computation. Object storage can hold inexpensive, durable files, while query engines process selected data when needed. Cloud warehouses provide managed analytical storage, elastic compute and governance features. Lakehouse architectures attempt to combine low-cost flexible storage with table management, transactions and analytical performance.

Cloud elasticity does not remove the need for cost management. Scanning unnecessary columns or partitions, creating excessive concurrent queries and moving data between regions can increase expense. Data residency, backup location, retention rules and service availability must also be considered. A scalable architecture is one that meets performance and governance requirements at sustainable cost.

Security, Governance and Responsible Access

Database security is layered. Authentication establishes identity; authorization determines permitted actions. Least privilege means granting only the access needed for a role or task. A reporting user may need `SELECT` on a view but not direct access to sensitive base tables. Role-based access control, row-level security, column masking and network restrictions can enforce this separation.

Sensitive information requires classification and appropriate controls. Personally identifiable information, financial records and health information may be subject to contractual, organizational or legal requirements. Analysts should use de-identified or minimized data where possible, retain data only as long as justified and record the purpose of access. Audit logs support accountability, but they must also be protected because they may contain sensitive identifiers.

SQL injection remains a major risk when untrusted input is concatenated into a query. Parameterization, input validation, secure drivers and restricted database privileges reduce the risk. Other common weaknesses include credentials in notebooks, publicly exposed endpoints, shared accounts, unencrypted connections and uncontrolled export of query results.

Governance also concerns meaning and quality. A technically available field may have ambiguous definitions, historical changes or known biases. Data catalogs, lineage, ownership, quality checks and documented metric definitions help prevent false confidence. Reproducibility requires recording query versions, source systems, extraction times, transformations and relevant software environments.

Applied Case: Customer Retention Analytics at Northstar Retail

Background

Northstar Retail operates physical and online stores. Its operational PostgreSQL database contains customers, orders, payments and products. The analytics team wants to identify customers whose purchasing activity is declining and produce a weekly retention list. The first proposal is to copy all operational tables into analysts' laptops and calculate features locally.

Situation and Problem

The proposal creates several risks. The tables contain personal information, the dataset is too large for reliable laptop processing, and copying data introduces uncontrolled versions. A preliminary query joining orders, order items and customer events produces inflated revenue because one order is repeated once for every item and event record.

Analysis

The team first defines the analytical grain: one row per customer per calendar week. It creates a warehouse fact table for order-level measures and dimensions for customers and dates. Item quantities are aggregated to the order grain before joining to customer-level data. Sensitive contact fields are excluded from the analytical mart.

The team uses a parameterized SQL transformation:

```sql

WITH weekly_orders AS (

SELECT customer_id,

DATE_TRUNC('week', order_date) AS week_start,

COUNT(DISTINCT order_id) AS order_count,

SUM(order_amount) AS revenue

FROM orders

WHERE order_date >= :window_start

GROUP BY customer_id, DATE_TRUNC('week', order_date)

)

SELECT customer_id,

week_start,

order_count,

revenue,

SUM(revenue) OVER (

PARTITION BY customer_id

ORDER BY week_start

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

) AS four_week_revenue

FROM weekly_orders;

```

The transformation runs near the warehouse rather than transferring raw orders. The table is partitioned by week, and queries are required to filter the partition range. The team examines the execution plan and discovers that a dashboard query is scanning historical partitions because its date predicate applies a function that prevents pruning. The predicate is rewritten to use explicit boundaries.

Python is used to train the retention model from the curated feature table. The connection uses a read-only role, credentials are supplied through a secret manager, and data is retrieved in batches. R is used by a marketing analyst through a remote table interface; only the final, limited customer segment is collected locally.

Outcome

The resulting pipeline produces reproducible weekly features, avoids the revenue duplication error and reduces network transfer. Role separation prevents the modelling account from modifying source data. The retention model is not treated as an automatic decision-maker: marketing staff review segments, and the team monitors whether recommendations disadvantage particular customer groups.

Learning Points

The case demonstrates that scalable access is a modelling concern, not merely an infrastructure concern. Defining grain prevents incorrect aggregation. Data locality reduces transfer and cost. Query plans reveal hidden inefficiencies. Secure roles and minimized data reduce exposure. Finally, technical performance must be accompanied by semantic validation and responsible use.

Challenges and Common Mistakes

Several recurring mistakes undermine database-based analysis. Treating a database as a file encourages unnecessary extraction and weak version control. Failing to define row grain leads to double counting. Assuming that a successful query is efficient ignores execution plans and resource consumption. Using `SELECT *` creates fragile dependencies and transfers irrelevant data. Building SQL through string concatenation exposes systems to injection and quoting errors.

Other problems arise from mixing time zones, interpreting null as zero, ignoring duplicate business keys, and using floating-point values carelessly for financial measures. Long-lived connections may become stale, while excessive connection pools can overwhelm a database. A data scientist should also distinguish development, testing and production environments and avoid experimenting with write operations on live systems.

No database design is universally optimal. Strong normalization may increase join complexity; denormalization may improve reads but create consistency obligations. NoSQL flexibility can accelerate development but shift validation responsibility into application code. Cloud elasticity can improve responsiveness but introduce unpredictable cost. Every choice should be evaluated against a clearly described workload.

Emerging Directions

Data access is increasingly influenced by lakehouse tables, open table formats, serverless query engines, real-time analytical stores and federated query systems. These approaches seek to combine flexible storage with transactionality, governance and interactive performance. Data catalogs and semantic layers are becoming more important as organizations attempt to standardize metrics across dashboards and machine-learning systems.

AI-assisted query tools can translate natural-language requests into SQL, explain query plans or suggest optimizations. They can improve productivity but may generate incorrect joins, unsafe filters or queries that expose sensitive data. Generated SQL requires the same review, testing and authorization as manually written SQL. A future-oriented data scientist therefore needs both tool fluency and the judgment to verify results.

Best Practices for Professional Data Access

A disciplined workflow generally follows these principles:

1. Define the business question, data grain and acceptable freshness before querying.

2. Identify the system of record and document source semantics.

3. Use least-privilege identities and parameterized queries.

4. Push selective filtering and aggregation toward the data.

5. Retrieve only necessary columns and rows, using batches for large results.

6. Inspect execution plans and monitor cost, latency and resource use.

7. Validate joins, nulls, duplicates and totals against trusted controls.

8. Separate exploratory, development and production access.

9. Version SQL, schema migrations and data transformations.

10. Record lineage, assumptions, extraction time and data-quality limitations.

11. Treat ORM abstractions as tools, not substitutes for understanding SQL.

12. Review automated or AI-generated queries before execution.

Chapter Summary

Databases provide the persistence, structure, integrity and controlled access required for professional data science. Relational systems use tables, keys, constraints and SQL; normalization supports consistent operations, while dimensional models support analytics. SQL enables filtering, joining, aggregation, CTEs and window-based reasoning, but correct results depend on grain, null handling and join cardinality. Transactions protect units of work, and schema evolution must be managed as an interface change.

Python and R connect to databases through drivers and interfaces, with parameterization, secure credentials, pooling and chunked retrieval forming essential practice. ORMs improve application abstraction but should be combined with direct SQL where analytical complexity or performance demands it. Scalable access depends on locality, indexing, partitioning, statistics, execution plans and suitable storage formats. NoSQL and cloud platforms provide alternative designs for flexible schemas, distributed workloads and elastic analytics, but introduce trade-offs involving consistency, cost and governance. Security, privacy, lineage, minimization and responsible interpretation are inseparable from technical database work.

Key Terms

Database management system: Software that stores, retrieves, protects and manages data.

Relational database: A database that organizes data into related tables.

Schema: The defined structure, types, constraints and access objects of a database.

Primary key: An attribute or set of attributes that uniquely identifies a row.

Foreign key: An attribute that references a key in another table.

Normalization: The organization of data to reduce redundancy and update anomalies.

Dimensional model: An analytical design using fact and dimension tables.

OLTP: Transaction-oriented processing involving frequent, concurrent operational updates.

OLAP: Analytical processing involving complex queries over substantial data volumes.

Transaction: A logical unit of database work governed by commit or rollback.

ACID: Atomicity, consistency, isolation and durability properties of transactions.

Join cardinality: The number and pattern of rows produced when relations are joined.

Window function: A function that calculates across related rows without collapsing them into groups.

Index: An auxiliary structure that can accelerate data retrieval.

Partition pruning: Reading only relevant partitions instead of scanning a complete partitioned table.

ORM: A technique that maps database structures to programming-language objects.

NoSQL: A broad category of non-relational database technologies.

Data locality: Performing computation near the location where data is stored.

Least privilege: Granting only the permissions required for an approved task.

Query plan: The execution strategy selected by a database optimizer.

Review Questions

Short Answer Questions

1. Distinguish a primary key from a foreign key.

2. Why is `HAVING` used instead of `WHERE` for filtering aggregate results?

3. State the four ACID properties.

4. What is the purpose of parameterized SQL queries?

5. Define data locality in the context of analytics.

Long Answer Questions

1. Explain how normalization and dimensional modelling serve different database workloads.

2. Discuss joins, join cardinality and the causes of double counting in analytical SQL.

3. Compare direct SQL access with ORM-based access for data-science applications.

4. Explain how indexes, partitioning, statistics and execution plans influence query performance.

5. Discuss security and governance practices for Python- and R-based database workflows.

Analytical / Application Questions

1. A query joining customers, orders and order items reports revenue three times higher than the finance system. Diagnose the likely cause and propose a corrected analytical approach.

2. An R analyst collects a 200 GB remote table into local memory to calculate a monthly average. Recommend a more scalable workflow and justify each step.

3. An organization must support both low-latency order processing and large historical analytics. Propose an architecture and discuss the trade-offs involving OLTP, OLAP, cloud storage, security and data freshness.

22. Multiple Choice Questions with Answers

Question 1

A data scientist joins customers to orders and then aggregates customer revenue. Which issue is most important to check first?

A. Whether the customer names are alphabetically sorted

B. Whether the join changes the intended row grain and duplicates orders

C. Whether the query uses uppercase SQL keywords

D. Whether every customer has an email address

Correct Answer: B. Whether the join changes the intended row grain and duplicates orders

Question 2

Which practice most directly reduces SQL-injection risk?

A. Concatenating user input into a longer query

B. Using SELECT * for consistency

C. Using parameterized queries

D. Increasing the connection-pool size

Correct Answer: C. Using parameterized queries

Question 3

Why might a data scientist use a LEFT JOIN rather than an INNER JOIN when calculating customer spending?

A. To exclude customers with no orders

B. To preserve customers with no matching orders

C. To guarantee that all revenue is positive

D. To eliminate the need for aggregation

Correct Answer: B. To preserve customers with no matching orders

Question 4

Which operation is generally most appropriate in HAVING rather than WHERE?

A. Filtering rows by order date

B. Filtering rows by customer identifier

C. Filtering groups whose total revenue exceeds a threshold

D. Filtering rows whose status is active

Correct Answer: C. Filtering groups whose total revenue exceeds a threshold

Question 5

What is the main purpose of examining EXPLAIN ANALYZE?

A. To encrypt database credentials

B. To inspect the actual execution behavior of a query

C. To convert a relational database into a NoSQL database

D. To create a primary key automatically

Correct Answer: B. To inspect the actual execution behavior of a query

Question 6

A large table is partitioned by order date, but a query scans every partition. Which explanation is most plausible?

A. The query does not restrict the partitioning key in a usable way

B. The table has too many primary keys

C. The query uses a LEFT JOIN

D. The database contains null values

Correct Answer: A. The query does not restrict the partitioning key in a usable way

Question 7

Which statement best describes an ORM?

A. A storage format that always replaces SQL

B. A mapping between database structures and programming-language objects

C. A security protocol for encrypting backups

D. A method for partitioning cloud regions

Correct Answer: B. A mapping between database structures and programming-language objects

Question 8

Why should large analytical results often be aggregated or filtered in the database before retrieval?

A. Databases cannot perform aggregation

B. It reduces network transfer and local memory requirements

C. It guarantees that the result contains no missing values

D. It removes the need to validate joins

Correct Answer: B. It reduces network transfer and local memory requirements

Question 9

Which control best reflects the principle of least privilege?

A. Giving every analyst administrator access

B. Using a shared account for convenience

C. Giving a reporting role access only to approved views

D. Allowing notebooks to store permanent passwords

Correct Answer: C. Giving a reporting role access only to approved views

Question 10

In the Northstar Retail case, why were order-level measures aggregated before joining customer-level data?

A. To ensure that product names were sorted

B. To prevent one-to-many joins from inflating revenue

C. To remove the need for a database connection

D. To convert SQL into Python

Correct Answer: B. To prevent one-to-many joins from inflating revenue