For an enterprise Azure analytics platform, I would use a polyglot data architecture: each storage technology handles the workload it is best suited for, while ADLS Gen2 becomes the central analytical system of record. Microsoft’s current guidance supports ADLS-based medallion/lakehouse architectures and emphasizes storage layout, file formats, partitioning, and parallelism for analytical performance. (Microsoft Learn)
Reference architecture
DATA SOURCES
|
+----------------------+----------------------+
| | |
Applications Enterprise DBs Files / APIs
| | |
v v v
Cosmos DB Azure SQL / ADF / Event
operational DB SQL Server Hubs
| | |
+----------------------+----------------------+
|
v
Azure Data Factory
/ Event Hubs / Databricks
|
v
+--------------------------------+
| Azure Blob / ADLS Gen2 |
| |
| BRONZE - Raw |
| SILVER - Cleaned |
| GOLD - Curated |
+---------------+----------------+
|
Delta / Parquet
|
Azure Databricks
Spark / SQL
|
+--------------+-------------+
| |
v v
Azure SQL DB / AI / ML
SQL serving layer workloads
|
v
Power BI / APIs /
Analytics applications
Azure Data Factory can directly copy and transform data into ADLS Gen2, making it appropriate as the orchestration and ingestion layer. (Microsoft Learn)
1. Azure Blob Storage / ADLS Gen2
This should contain the largest volume of analytical data.
Conceptually:
Storage Account│├── bronze/│ ├── cosmos/│ ├── sql/│ ├── api/│ └── documents/│├── silver/│ ├── customers/│ ├── transactions/│ └── products/│└── gold/ ├── customer_360/ ├── revenue/ ├── risk/ └── ai_training/
I would normally enable the hierarchical namespace, effectively using Azure Data Lake Storage Gen2 capabilities on Azure Blob Storage. ADLS Gen2 is designed for large-scale analytical workloads. (Microsoft Learn)
Bronze — immutable/raw
Keep data as close to the source as possible:
Cosmos JSONSQL extractsCSVJSONAvroapplication logsdocuments
Example:
/bronze/cosmos/orders/year=2026/month=08/day=11//bronze/sql/customer/year=2026/month=08/day=11/
Do not let consumers directly modify Bronze.
2. Silver — analytical processing
Databricks transforms Bronze into standardized datasets:
Bronze | vAzure Databricks | +-- schema enforcement +-- data cleaning +-- deduplication +-- joins +-- standardization +-- quality checks +-- PII handling | vSilver
For example:
bronze:customer JSON + transactions + CRM ↓silver:CustomerTransactionAccountProduct
I’d generally use Delta Lake/Parquet rather than CSV for analytical datasets. A lakehouse architecture combines scalable object storage with data-management and warehouse-style capabilities. (Microsoft Learn)
3. Gold — business-ready analytics
Gold contains datasets optimized for consumption rather than raw ingestion.
Silver | +----------+ | | v vCustomer360 SalesMetrics | | +----+-----+ | GOLD
Example:
/gold/customer360//gold/monthly_revenue//gold/risk_analysis//gold/executive_reporting/
The Gold layer can then feed:
Power BIAzure SQLAPIsDatabricks SQLMachine learningAI/RAG
This Bronze → Silver → Gold medallion structure is a common Azure lakehouse design; Microsoft’s architecture guidance also shows ADF ingestion, Databricks processing and Azure SQL as a downstream serving store. (Microsoft Learn)
4. Where Cosmos DB fits
This distinction matters in interviews:
Cosmos DB should generally not become your primary enterprise data lake.
Use Cosmos DB for:
High transaction volume +Low-latency access +Semi-structured JSON +Global distribution +Application operational data
Example:
{ "customerId": "C10001", "eventType": "purchase", "productId": "P889", "amount": 142.75, "timestamp": "2026-08-11T18:05:03"}
An application might use:
Web/Mobile App | v API | v Cosmos DB latency: milliseconds
Analytics should normally be separated:
OLTP Analytics
Application
|
v
Cosmos DB
|
+---------------------> Analytics platform
|
v
ADLS/Lakehouse
That prevents heavy analytical queries from competing with production application traffic.
5. Cosmos DB analytics: an important current change
This is worth knowing for a 2026 interview.
Historically, you would often hear:
Cosmos DB |Analytical Store |Synapse Link |Synapse Analytics
But Microsoft now states that Azure Synapse Link for Cosmos DB isn’t supported for new projects and recommends Azure Cosmos DB Mirroring for Microsoft Fabric for new implementations. (Microsoft Learn)
So for a new Fabric-centric design:
Cosmos DB | | zero-ETL mirroring vMicrosoft Fabric |OneLake |Analytics / Power BI
For an existing estate that already uses Synapse Link, analytical store can still separate analytical access from the transactional workload and provide near-real-time analytical data without traditional ETL. (Microsoft Learn)
That’s a very useful distinction to make in an interview because many older architecture diagrams still recommend Synapse Link as the default for new solutions.
6. Where Azure SQL fits
I would use Azure SQL primarily for structured relational workloads and analytical serving, rather than storing petabytes of raw lake data.
For example:
ADLS Gold
|
Databricks
|
v
Azure SQL
Serving DB
|
+--------+--------+
| |
Power BI APIs
Azure SQL could contain:
DimCustomerDimProductDimDateFactSalesFactOrdersFactRevenue
Conceptually:
FactSales
|
+-------+-------+
| | |
Customer Product Date
That structure works well when consumers expect SQL semantics, relational joins, predictable schemas and conventional BI integration.
Microsoft’s medallion reference architecture explicitly describes Azure SQL as a persisted downstream serving store after ADF ingestion and Databricks processing. (Microsoft Learn)
7. Don’t query operational SQL for every report
This is an important architecture rule.
Avoid:
Power BI | vProduction Azure SQL
when complex BI queries could impact application traffic.
Instead:
Production SQL | | CDC / incremental ingestion v ADF | vADLS Bronze |Databricks |ADLS Gold | +----> BI serving layer | +----> Power BI
This creates separation between:
OLTP
many smallINSERT/UPDATE/SELECT
and OLAP
SELECT SUM(...)FROM billions_of_rowsGROUP BY ...
8. File format optimization
For analytics, the storage format matters considerably.
I would prefer:
RAWCSV / JSON / original source | v Bronze transform ↓Silver / GoldParquet / Delta
Why?
Parquet is column-oriented.
Imagine:
customer_idnamecountryagesalarytransaction_total
If a query needs only:
SELECT country, SUM(transaction_total)FROM salesGROUP BY country;
a columnar format can avoid unnecessarily reading all unrelated columns.
ADLS performance guidance emphasizes that file format, file size and directory structure affect both performance and cost, and recommends parallelizing ingestion and access. (Microsoft Learn)
9. Partitioning
Don’t create:
/data/everything.parquet
and don’t create millions of tiny files either.
Use useful business/query partitions:
/sales/ year=2026/ month=08/ day=11/ part-00001.parquet part-00002.parquet
Then:
WHERE year = 2026AND month = 8
allows the processing engine to avoid scanning irrelevant partitions.
Possible partition keys:
dateregionsource_systembusiness_domain
But avoid partitioning on very high-cardinality fields such as:
customer_idtransaction_idGUID
because that can create a huge number of directories/files.
10. Handle the small-file problem
An enterprise ingestion system can easily produce:
10 million × 10 KB JSON files
which is inefficient for analytical processing.
I’d periodically compact them:
10,000 tiny files ↓Databricks ↓10–100 appropriately sizedParquet / Delta files
Microsoft’s ADLS guidance notes that larger files generally provide better analytical performance and lower transaction overhead compared with large numbers of tiny files. (Microsoft Learn)
11. Data flow between the three storage systems
A good architecture might look like this:
OPERATIONAL SYSTEMS
┌──────────────────┐
│ Cosmos DB │
│ JSON/events │
└────────┬─────────┘
|
|
┌─────────────┐ | ┌─────────────┐
│ Azure SQL │ | │ APIs/Files │
│ OLTP │ | │ SaaS │
└──────┬──────┘ | └──────┬──────┘
| | |
+---------------+----------------+
|
ADF / Streaming
|
v
┌─────────────────────┐
│ Azure Blob/ADLS G2 │
│ │
│ BRONZE │
│ SILVER │
│ GOLD │
└──────────┬──────────┘
|
Databricks
Spark / Delta
|
+------------+------------+
| |
v v
Azure SQL AI / ML
analytical serving Models
|
v
Power BI
12. Security architecture
For an enterprise deployment:
Azure Landing Zone
|
VNet
|
Private Endpoints
|
+---------------+---------------+
| | |
ADLS Cosmos DB Azure SQL
I would add:
Microsoft Entra ID |Managed Identity |Azure RBAC
and:
Key Vault |CMK/secrets/certificates
Plus:
- disable public network access where appropriate;
- use private endpoints;
- use managed identities rather than embedded account keys;
- encrypt data at rest and in transit;
- apply least-privilege RBAC;
- classify sensitive datasets;
- maintain audit logs and lineage.
13. Analytics performance design
My high-level optimization rules would be:
| Layer | Optimization |
|---|---|
| ADLS | Parquet/Delta |
| ADLS | sensible partitioning |
| ADLS | avoid tiny files |
| Databricks | distributed Spark |
| Cosmos DB | good partition key |
| Cosmos DB | isolate operational from analytical workloads |
| SQL | indexes appropriate to serving queries |
| SQL | dimensional/star schemas where appropriate |
| Pipelines | incremental loads |
| Pipelines | parallel ingestion |
| Gold | pre-aggregated datasets |
Microsoft specifically recommends maximizing parallel reads/writes for ADLS analytical workloads rather than serializing large ingestion jobs. (Microsoft Learn)
Interview architecture to memorize
SOURCES
|
+----------+----------+
| |
Azure SQL Cosmos DB
OLTP Operational JSON
| |
+----------+----------+
|
ADF / Events
|
v
ADLS Gen2
┌─────────────┐
│ BRONZE │
│ raw │
└──────┬──────┘
↓
Databricks
↓
┌─────────────┐
│ SILVER │
│ clean │
└──────┬──────┘
↓
Databricks
↓
┌─────────────┐
│ GOLD │
│ curated │
└──────┬──────┘
|
+-------+-------+
| |
Azure SQL AI/ML
Serving
|
Power BI
90-second interview answer
“I would use a polyglot storage architecture. Azure Blob Storage with ADLS Gen2 would be the central analytical data lake because it provides low-cost scalable storage for structured, semi-structured and unstructured data.
I would organize it using Bronze, Silver and Gold zones. Raw data from Azure SQL, Cosmos DB, APIs and other systems lands unchanged in Bronze. Databricks then performs schema enforcement, quality checks, deduplication and transformations, storing Silver and Gold datasets primarily in Delta or Parquet formats.
Cosmos DB remains the operational store for low-latency, high-volume JSON workloads rather than becoming the primary data lake. Azure SQL handles relational transactional workloads and can also provide a curated SQL serving layer for BI applications.
For performance, I’d use incremental ingestion, parallel processing, appropriate partitioning, columnar storage and file compaction. From a security perspective, all three platforms would use Entra ID, managed identities, RBAC and private endpoints.
Finally, I keep operational and analytical workloads isolated so large BI or ML queries never affect production databases.”
One particularly good 2026 interview point is to mention that although Cosmos DB Synapse Link appears in many older architectures, Microsoft now recommends Cosmos DB Mirroring for Microsoft Fabric for new projects rather than starting a new Synapse Link implementation. (Microsoft Learn)